mysql innodb 索引使用指南「建议收藏」

mysql innodb 索引使用指南

索引相关概念

1. 聚簇索引（clustered index）
   使用innodb引擎时，每张表都有一个聚簇索引，比如我们设置的主键就是聚簇索引
   特点：查询数据特别快，因为聚簇索引和行数据存储在磁盘的同一页，这样可以减少磁盘I/O操作次数（影响mysql性能的重要因素）
   注意：主键索引应该尽量简短
2. 二级索引（secondary index）
   除了聚簇索引外的其他索引叫做二级索引（辅助索引），比如我们给除主键外其他字段创建的索引
   特点：二级索引里面存储了聚簇索引，最后要通过聚簇索引找到行数据。可见，聚簇索引的效率会影响其他索引
3. 覆盖索引（covering index）
   索引包含了查询语句需要的所有数据，这种索引称为覆盖索引
   特点：索引的叶子节点中已经包含要查询的数据，不需要回表操作所以很快（减少了磁盘I/O操作次数）
4. 组合索引（multiple-column index）
   组合索引也称为复合索引（联合索引），是指把多个字段组合起来创建一个索引（最多16个字段）
   特点：遵循最左前缀匹配原则
5. 最左前缀匹配原则（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 = 'kevin@qq.com' group by age, name;

说明：分组字段不以索引最左前缀开始，但查询条件 email=’kevin@qq.com’ 提供了这部分常量

参考资料

How MySQL Uses Indexes
Multiple-Column Indexes
ORDER BY Optimization
GROUP BY Optimization
EXPLAIN Output Format

mysql innodb 索引使用指南