MySQL高级——索引 （内附索引高频面试题）
目录
2.3 MySQL中的常用的存储引擎 MyISAM与InnoDB的区别？
2.5 MyISAM与InnoDB实现BTree索引的方式不同在哪？
2.8 既然索引这么多好处，为什么不给表中的每一列都创建索引？(索引的缺点？)
2.9 使用索引有哪些注意事项？（使用索引时，SQL语句怎么优化？）
一、索引介绍以及使用
1.1 什么是索引？
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
从官方定义中，我们可以知道索引本质是一种数据结构。
详细一点的解释就是：
数据库除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构通过某种方式指向数据。如此一来，就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是 索引。
简单一点的解释就是：
索引是排好序的可快速查找的数据结构。
形象一点的解释就是：
索引就好像是新华字典的查找目录，可以帮助你快速查找到字典中的指定内容。
索引也像是图书馆的书籍查找目录，可以帮你快速查询到你要的书在哪。
1.2 索引的分类？
- 单值索引：一个索引只包含一个列，一个表可以有多个单列索引。
- 复合索引：一个索引包含多个列。
- 唯一索引：索引列的值必须是唯一的，但允许有多个空值。
1.3 索引的创建、查询、删除 的方式？
索引的创建有两种方法：CREATE 和 ALTER 。
/*ALTER*/
ALTER TABLE tableName ADD [UNIQUE] INDEX idx_name1 (column_name1);
/*CREATE*/
CREATE [UNIQUE] INDEX idx_name1 on tableName(colum_name1);查询索引：
/*查询索引*/
SHOW INDEX FROM tableNmae删除索引：
/*删除索引*/
DROP INDEX [indexName] ON tableName;
下面示范一下：
用create创建索引：
用alter创建单值索引：
创建多值索引：
用alter创建多值索引也类似，这里不演示了。
这里顺便说一下命名规范：
非唯一索引的命名(单值或多值)：idx_字段名 （多值：idx_字段名1_字段名2_字段名3.....）
唯一索引命名：uni_字段名
二、高频面试题（通过面试题更能了解索引）
2.1 什么是索引？
索引（Index）是帮助MySQL高效获取数据的数据结构。
（具体的可看上面的索引介绍。）
2.2 MySQL索引是什么样的数据结构？
MySQL索引主要使用的两种数据结构：
- 哈希索引
哈希索引底层的数据结构就是哈希表，查询很快。
所以在数据库的大部分需求为单条记录查询的时候，可以选择哈希索引。
而其他大部分场景，一般选则BTree索引。
- BTree索引
MySQL的BTree索引用的是BTree中的B+树。
不过对于不同的存储引擎，实现的方式不同。
比如主要的存储引擎MyISAM和InnoDB中的实现方法就不同。
（提到了存储引擎MyISAM和InnoDB之后，可能会有下面三个“追命连击”）
2.3 MySQL中的常用的存储引擎 MyISAM与InnoDB的区别？
- InnoDB支持事务，而MyISAM不支持。
- InnoDB支持外键，而MyISAM不支持。
- InnoDB不支持全文索引，而MyISAM支持。
- InnoDB使用的是行级锁，MyISAM使用的是表级锁。
- InnoDB count(*) 时需要全表扫描，消耗资源多且速度慢；
而MyISAM 是用一个变量保存了整个表的行数，count(*)时只需读取该变量即可，消耗资源很少且速度很快。
2.4 如何选择存储引擎 MyISAM与InnoDB？
- InnoDB适合写密集的的表，MyISAM适合读密集的表。
- 数据库做主从分离时，一般选择MyISAM作为主库的存储引擎，InnoDB作为从库的存储引擎。
- 需要支持事务，且需要支持较高并发时，选择InnoDB。
不需要支持事务时，选择MyISAM。
2.5 MyISAM与InnoDB实现BTree索引的方式不同在哪？
- MyISAM: B+树叶子节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+树搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
- InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+树组织的一个索引结构，树的叶子节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
2.6 为什么要使用索引？（索引的优点？）
-
可以大幅加快 数据的检索速度（因为大幅减少了检索的数据量）, 这也是创建索引的最主要的原因。
（就像使用新华字典找一个字，如果没有目录，我们就需要一页一页的来查找；而如果有目录，我们只需查询一下目录就可以快速找到。） -
通过索引列对数据进行排序，降低了数据排序的成本。使服务器避免了排序与创建临时表。
-
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
-
将随机IO变为了顺序IO。
-
可以加速表和表之间的连接，在实现数据的参考完整性方面特别有意义。
2.7 索引是怎么提高查询速度的？
- 索引将无序的数据进行了排序，我们查询时只需查询索引即可快速查询到所需数据（就像使用字典的目录来查找）。
2.8 既然索引这么多好处，为什么不给表中的每一列都创建索引？(索引的缺点？)
因为索引也有一些缺点：
- 实际上索引也是一张表，该表保存了主键与索引，并指向实体表的记录，所以索引也要占用空间。
- 索引虽然大幅提升了查询速度，但是会降低更新表的速度。因为在对表中数据进行增删改时，索引也要动态维护。这就会降低数据的维护速度(也就是降低更新表的速度)。
- 创建索引和维护索引也会耗费时间，并且数据量越大，耗费的时间越多。
2.9 使用索引有哪些注意事项？（使用索引时，SQL语句怎么优化？）
- 要遵循最左前缀原则。
- 少在索引列上进行操作。
- 范围条件之后的索引会失效。
- 使用模糊查询时，%写在最右边，不然后面的索引会失效。
- 不等，空值，or ，这几个会使索引失效，要少用。
- VARCHAR类型的数据，一定要记得写引号，不然会使索引失效。
- 覆盖索引查询很快 ( 覆盖索引不要写 select * )。
- 单行访问很慢。
不要为了从存储中读取一个数据块而去获取其中的一行。
最好读取的数据块中包含尽可能多的所需要的行。
2.10 什么是最左前缀原则？
使用多值索引时，要按索引字段的顺序使用。
例如：
user表里的属性字段a、b、c 有一个多值索引 (a,b,c) idx_a_b_c 。
select * from user where a=1 and b=1 and c=1 ; ／／a,b,c可以使用索引
select * from user where a=1 ; // a可以使用索引
select * from user where a=1 and c=1; // a可以使用索引,c不能使用索引，因为前面的b断了。
select * from user where b=1 and c=1; // b,c 都不能使用索引，因为前面的a断了。
#注意
select * from user where c=1 and b=1 and a=1;
//这个a,c,c都能使用索引，因为虽然顺序不对，但是MySQL会帮我们调整顺序。
2.11 什么是覆盖索引？
覆盖索引，就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不用根据索引再去读取数据文件。也就是说 查询列要被所建立的索引覆盖。
比如：user表有字段id、name、age、address，我们用字段name和age 建立索引 idx_name_age 。
那么查询时，
如果我们SQL是
select name,age from user where username='猿兄' and age = 99;
这就是覆盖索引，要查的 name 和 age 都在索引里，
所以我们根据索引查询数据的时候，就可以直接从索引中获得，而不用再去读取数据库。
而如果SQL是
select name,address from user where username='猿兄' and age = 99;
这样的话，因为address不在索引里
当我们根据索引查询数据时，还要再去读取一下数据库才能获取address这个数据。
这样就比较慢。
查看40道真题和解析
