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会一直向右匹配直到遇到范围查询(>、<、between、like)才停止匹配
   举例：我们使用a,b,c四个字段创建一个组合索引（a,b,c），那么where条件中必须匹配字段a，mysql优化器才会用到这个索引
   首先匹配字段a，才能匹配字段b
   首先匹配字段a，其次匹配字段b，才能匹配字段c

准备工作

创建teacher表，使用mobile，name，birthday字段建立组合索引，注意这三个字段在索引中的顺序

CREATE TABLE `teacher` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `birthday` timestamp NULL DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `mobile` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_one` (`mobile`,`name`,`birthday`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

使用explain查看mysql执行计划

Type列：MySQL在表里找到所需行的方式（由左至右，由最差到最好）：

| All | index | range | ref | eq_ref | const| system | null |

Extra列: 额外的信息（由左至右，由差到好）：

|Using index condition|Using where; Using index|Using index|

使用索引的场景

场景一：mobile是索引的第一列

explain select * from teacher where mobile = '18600660088';

ref列只出现了一个const，说明只用到索引的第一列

场景二：mobile和name是索引中从左到右连续的两列

explain select * from teacher where mobile = '18600660088' and name = 'kevin chen';

ref列出现了两个const，说明用到索引的第一列和第二列

场景三：mobile、name和birthday是索引中从左到右连续的三列

explain select * from teacher where mobile = '18600660088' and name = 'kevin chen' and birthday = '2019-01-01';

ref列出现了三个const，说明用到索引的第一列、第二列和第三列

场景四：因为跳过了索引中第二列（name），所以只用到了索引的第一列

explain select age from teacher where mobile = '18600660088' and birthday = '2019-01-01';

ref列只出现了一个const，说明只用到索引的第一列

场景五：

explain select * from teacher where mobile = '18600660088' and name like 'kevin chen';

场景六：mobile使用索引

explain select * from teacher where mobile = '18600660088' and name like '%kevin chen';

场景七：mobile和name使用索引

explain select * from teacher where mobile = '18600660088' and name = 'kevin chen' and birthday > '2019-01-01';

场景八：mobile使用索引

explain select * from teacher where mobile like '18600660088%';

不使用索引的场景

场景一：缺少最左边的mobile字段

explain select * from teacher where name = 'kevin chen';

场景二：缺少最左边的mobile字段和第二个name字段

explain select * from teacher where birthday = '2019-01-01';

场景三：like模糊匹配%位于最左边

explain select * from teacher where mobile like '%18600660088';

覆盖索引

场景一：使用覆盖索引

explain select mobile,name,birthday from teacher where mobile = '18600660088';

Extra列表示附加信息，Using index使用了覆盖索引

场景二：查询的age字段不在索引中，不能使用覆盖索引

explain select age from teacher where mobile = '18600660088';