mysql innodb 索引使用指南
索引相关概念
- 聚簇索引（clustered index）
使用innodb引擎时，每张表都有一个聚簇索引，比如我们设置的主键就是聚簇索引
特点：查询数据特别快，因为聚簇索引和行数据存储在磁盘的同一页，这样可以减少磁盘I/O操作次数（影响mysql性能的重要因素）
注意：主键索引应该尽量简短 - 二级索引（secondary index）
除了聚簇索引外的其他索引叫做二级索引（辅助索引），比如我们给除主键外其他字段创建的索引
特点：二级索引里面存储了聚簇索引，最后要通过聚簇索引找到行数据。可见，聚簇索引的效率会影响其他索引 - 覆盖索引（covering index）
索引包含了查询语句需要的所有数据，这种索引称为覆盖索引
特点：索引的叶子节点中已经包含要查询的数据，不需要回表操作所以很快（减少了磁盘I/O操作次数） - 组合索引（multiple-column index）
组合索引也称为复合索引（联合索引），是指把多个字段组合起来创建一个索引（最多16个字段）
特点：遵循最左前缀匹配原则 - 最左前缀匹配原则（leftmost prefix principle）
mysql会从左向右匹配直到遇到不能使用索引的条件（>、<、!=、not、like模糊查询的%前缀）才停止匹配
设想用a,b,c字段创建一个组合索引（a,b,c）
由于a是索引的最左边前缀，所以where条件中必须匹配字段a，mysql优化器才会用到这个索引
在匹配字段a的前提下，才能匹配字段b
在匹配字段a的前提下，并且匹配字段b，然后才能匹配字段c
使用explain查看执行计划
explain命令用来查看select语句执行计划，确认该SQL语句有没有使用索引，是否做全表扫描，是否使用覆盖索引等
type：代表数据访问类型（由左至右，由最差到最好）
| All | index | range | ref | eq_ref | const | system | null |
possible_keys：表示哪些索引可能有利于高效的查找
key：显示mysql决定采用哪个索引来优化查询
key_len：显示mysql在索引里使用的字节数
ref：显示了之前的表在key列记录的索引中查找值所用的列或常量
rows：为了找到所需的行大致需要读取的行数
extra：表示额外的信息（左边较差，右边较好）
| Using filesort| Using temporary | Using where | Using index condition | Using index |
Using index：使用了覆盖索引，速度很快，限于查询字段都位于同一个索引中的场景
Using index condition：表示使用了ICP优化（Index Condition Pushdown)，能减少引擎层访问基表的次数和MySQL Server访问存储引擎的次数
Using where：表示在存储引擎检索行后mysql服务器再进行过滤
Using filesort：返回结果前需要做一次外部排序（内存或硬盘），速度慢应该尽量避免
Using temporary：在对查询结果排序时会使用一个临时表，速度慢
创建一张测试表
使用InnoDB引擎创建teacher表，id设为自增主键
mobile、name和birthday建立第一个组合索引idx_one（注意三个字段在索引中顺序）
email、age和name字段建立第二个组合索引idx_two（同样注意顺序）
CREATE TABLE `teacher` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(64) DEFAULT NULL, `birthday` timestamp NULL DEFAULT NULL, `email` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, `mobile` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_one` (`mobile`,`name`,`birthday`), KEY `idx_two` (`email`,`age`,`name`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
符合最左前缀场景
场景一：mobile是索引的左前缀
explain select * from teacher where mobile = '18600660088';
说明：ref列只出现了一个const，说明使用索引的第一列
场景二：mobile和name加起来是索引的左前缀
explain select * from teacher where mobile = '18600660088' and name = 'kevin';
说明：ref列出现了两个const，说明使用索引的前缀mobile和name
场景三：mobile、name和birthday加起来是索引的左前缀
explain select * from teacher where birthday = '2019-01-01' and name = 'kevin' and mobile = '18600660088';
说明：ref列出现了三个const，说明使用索引的第一列、第二列和第三列
注意：mysql优化器会自动调整mobile、name、birthday在查询条件中出现的顺序以匹配索引
场景四：只有mobile是前缀，中间跳过了索引中第二列（name），birthday不使用索引
explain select age from teacher where mobile = '18600660088' and birthday = '2019-01-01';
说明：ref列只出现了一个const，说明使用索引的前缀mobile部分
场景五：mobile和name加起来是索引的前缀，并且%位于模糊查询后缀
explain select * from teacher where mobile = '18600660088' and name like 'kevin%';
说明：key_len是246与场景二一致，只使用了索引的前缀部分
场景六：mobile和name加起来是索引的前缀，并且%位于模糊查询的前缀
explain select * from teacher where mobile = '18600660088' and name like '%kevin';
说明：mobile字段用到了索引，name不使用索引
场景七：mobile是索引的最左前缀，并且使用了范围查询
explain select * from teacher where mobile > '18600660088' and name = 'kevin' and birthday = '2019-01-01';
说明：key_len是51与场景六一致，只使用了索引前缀mobile，name和birthday不使用索引
结论：索引从左往右匹配，遇到范围查询后停止匹配
场景八：name位于组合索引的中间，并且%位于模糊查询后缀
explain select * from teacher where mobile = '18600660088' and name like 'kevin%' and birthday = '2019-01-01';
说明：key_len显示251说明跟场景三一致，使用到了整个组合索引
结论：%位于模糊查询后缀不影响索引的使用，如果是组合索引可以继续往右匹配
不使用索引的场景
场景一：缺少前缀mobile字段
explain select * from teacher where name = 'kevin chen';
说明：type列显示ALL表示全表扫描，MySQL 从头到尾扫描整张表查找行
场景二：缺少mobile和name组合的前缀字段
explain select * from teacher where birthday = '2019-01-01';
说明：type列显示ALL表示全表扫描，MySQL从头到尾扫描整张表查找行
场景三：like模糊匹配%位于前缀
explain select * from teacher where mobile like '%18600660088';
说明：type列显示ALL表示全表扫描，MySQL从头到尾扫描整张表查找行
场景四：索引列进行了函数运算
explain select * from teacher where trim(mobile) = '18600660088';
说明：正确的做法是在等号的右边做数据运算或函数运算
场景五：字段类型不匹配
explain select * from teacher where mobile = 18600660088;
说明：mobile是varchar类型，18600660088是整数
覆盖索引
场景一：需要的数据都在索引中，走覆盖索引
explain select mobile,name,birthday from teacher where mobile = '18600660088';
说明：Extra列显示附加信息，Using index表示使用覆盖索引
场景二：查询的age字段不在索引中，不能使用覆盖索引
explain select age from teacher where mobile = '18600660088';
使用索引排序
场景一：查询字段和排序字段是同一个索引
explain select id,mobile,name,birthday from teacher order by mobile,name,birthday;
说明：extra显示Using index表示使用了覆盖索引，二级索引中隐含了聚簇索引（主键）
场景二：多个排序字段位于多个不同索引
explain select * from teacher order by mobile, email;
说明：mobil和email属于不同索引，Using filesort说明使用外部排序，不能用索引排序
场景三：多个排序字段不符合最左前缀匹配原则
explain select id,mobile,name,birthday from teacher order by mobile, birthday;
说明：查询用了索引，排序跳过了组合索引中间字段name，extra显示Using filesort
场景四：查询含索引外字段，用索引扫描后再回表查询比直接扫表成本更高，所以没使用索引
explain select * from teacher order by mobile,name,birthday;
说明：extra显示Using filesort表示使用了外部排序
场景五：查询条件字段和排序字段组合起来符合索引最左前缀
explain select * from teacher where mobile='18600660088' order by name;
使用索引分组
场景一：分组字段（多字段组合）属于索引最左前缀
explain select email, age, name from teacher group by email, age, name;
说明：email、age和name组合起来符合最左前缀，使用索引idx_two，extra显示Using index
explain select distinct email, age, name from teacher;
说明：这里distinct字段组合起来同样符合索引最左前缀，使用索引idx_two
场景二：min()/max()函数作用于同一列，并且紧跟属于同一索引的分组字段
explain select email, min(age), max(age) from teacher group by email;
说明：email是分组字段，age是函数作用字段，email和age组合起来符合idx_two最左前缀
场景三：count(distinct)、avg(distinct)和sum(distinct)组合起来符合最左前缀
explain select count(distinct email), sum(distinct age) from teacher;
说明：avg(distinct)和sum(distinct)中distinct只适用单个字段
场景四：count(distinct)，distinct适用于多个字段
explain select count(distinct email, age) from teacher;
说明：extra显示Using index for group-by说明使用松散索引扫描（Loose Index Scan）
场景五：缺少组合索引中间部分，不能使用索引排序
explain select email, name from teacher group by email, name;
说明：分组字段缺少idx_two索引age部分，extra显示Using filesort说明使用外部排序
场景六：多个分组字段不属于同一个索引
explain select email, age, birthday from teacher group by email, age, birthday;
说明：birthday不属于idx_two索引，显示Using filesort
场景七：紧凑索引扫描（Tight Index Scan）
explain select email, age, name from teacher where age = 18 group by email, name;
说明：分组字段缺少了完整索引中间部分，但由查询条件 age = 18 补充了这部分常量
场景八：紧凑索引扫描（Tight Index Scan）
explain select email, age, name from teacher where email = '[email protected]' group by age, name;
说明：分组字段不以索引最左前缀开始，但查询条件 email='[email protected]' 提供了这部分常量
参考资料
How MySQL Uses Indexes
Multiple-Column Indexes
ORDER BY Optimization
GROUP BY Optimization
EXPLAIN Output Format