索引最佳实践具体实例

关于Explain的介绍可前往MySQL Explain执行计划详解查看

现有如下表：

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时 间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

根据这张表的查询来介绍一些优化原则（下面介绍的是一些常用的优化原则，但是不是100%覆盖所有的场景，有些特殊场景是不适用的）

一、全值匹配

有以下三条SQL：
均使用到了索引如下索引：

1.1 条件使用索引的一个字段

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei'; 

这条SQL语句使用了索引idx_name_age_position中的 name字段。
ken_len的计算方式：
1）name的类型为varchar
2）name字段的长度为24
3）name字段是not null
在上篇MySQL Explain执行计划详解中介绍了varchar的计算方式为：varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
所以，这条SQL语句的ken_len为：3*24+2=74

1.2 条件使用索引的两个字段

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22; 

这条SQL语句使用了索引idx_name_age_position中的 name字段和age字段。
ken_len的计算方式：
1）在1.1中计算name的ken_len为74
2）age字段的类型是int
3）age字段是not null
在上篇MySQL Explain执行计划详解中介绍了int的计算方式为：int：4字节
所以，这条SQL语句的ken_len为：74+4=78

1.3 条件使用索引的全部字段

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

这条SQL语句使用了索引idx_name_age_position中的 name字段、age字段和position字段。
ken_len的计算方式：
1）在1.2中计算name的ken_len为78
2）position字段的类型是varchar
3）position字段的长度是20
4）position字段是not null
在上篇MySQL Explain执行计划详解中介绍了varchar的计算方式为：varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
所以，这条SQL语句的ken_len为：78+20*3+2=140

ps:如果计算出的key_len的长度多了1，看下是否哪个字段是允许为空的，MySQL中如果字段允许为 NULL，需要1字节记录是否为 NULL

综上所述：
全值匹配针对联合索引来说就是尽可能多的使用索引包含的字段，字段使用的越多，效率会更高，扫描行数会更少
通过计算key_len可以判断是否索引使用到了哪些字段
所以，全值匹配优化就是尽可能多的使用索引的字段

二、最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
比如以下索引：


为什么age字段和position字段明明是在索引里面的，但是没有走索引，最终全表扫描？？？
      因为联合索引在查询的时候会先去比对第一个字段，第一个字段比对完成之后再去比对第二个字段…，以此类推。
      现在联合索引的第一个字段是name，但是上述SQL中直接是从第二个字段开始查询，根据后面的字段，无法精确定位到索引树的某个节点，导致无法根据索引树查找。
      现在相当于不知道第一个字段是什么，那么就需要把索引树中所有的节点都过一遍，其实就相当于是全表扫描了，索引用了和没用没有太大作用。
所以，如果使用了联合索引，查找条件必须要按照联合索引字段出现的顺序排列，否则会导致索引失效。

ps:如果查询条件是：以下SQL，那么会不会走索引呢？

SELECT * FROM employees WHERE age = 22 and name = 'HanMeimei' and position = 'manager';

可以执行看下结果。

那么为什么第一个字段不是name也走了索引呢？
因为mysql 在真正执行SQL之前，内部会进行优化（可以使用explain EXTENDED + show warnings查看），真正执行的时候会按照联合索引的顺序去执行。

三、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转 向全表扫描

比如有如下两个SQL：
直接根据name查询是会走索引的
将name字段left之后，不走索引了。


为什么会出现这种情况呢？
因为SQL中对取了name字段的前3位，索引树中没有执行函数之后对应的值，所以根本就走不了索引。
可以看下联合索引的底层存储结构，如果是从name字段中截取一部分来查询，整棵树就不再是有序的，是没有办法走索引的。
所以就需要考虑使用函数的结果是否可以在索引树中定位，而且还可以保持索引树依然有序。

四、存储引擎不能使用索引中范围条件右边的列

SQL走了索引全值匹配，ken_len为140，是name字段、age字段、position字段之和


SQL走了部分字段，key_len为78是name字段和age字段之和，position字段并没有走索引。
为什么不走position字段的索引呢？？
SQL在查询name字段的时候是等值，走索引；查询age的时候是查询大于，范围查找也是走索引的。
在第一个字段相等的情况下，第二个字段肯定还是有序的。
SQL查询的时候其实是先根据第一个条件name=“LiLei”，把结果集加载出来，这个时候数据还是有序的，然后根据第二个条件age>22查询，数据就不一定有序了，所以在查询第三个条件的时候，没办法利用索引的有序性，所以就不会走索引了

所以总的来说：存储引擎不能使用索引中范围条件右边的列（范围后面的字段都不会再走索引）

五、尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句

查询尽量不要用select *，建议指明具体的字段，而且这些字段尽量能被联合索引覆盖（如果不能全部覆盖，尽量多覆盖），要是实在覆盖不到，数据量有非常大，对效率要求又很高，这个时候就需要使用到搜索引擎 。

六、mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

看上去name列是应该要走索引的（possible_keys是有值的），但是其实效率和全表扫描没有什么太大的区别，所以就直接走了全表扫描（key列是空的）

不等于的结果集有可能太多了，走索引总的来说没有什么太大的用处

七、is null,is not null 也无法使用索引

因为null值比较特殊，在索引树中的字段不好比对，它和某些字段比较还是null值，所以没办法很方便的走索引。
那么怎么优化呢？
一般建议SQL语句不要这么写，大部分公司要求字段要设置为not null,SQL中是不会出现这种条件的，即便字段没有值，也要设置一个默认值，方便走索引，提高效率。

八、like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

为什么%在前面不走索引，在后面走索引？？
因为%在前面，意味着LiLei前面可能还有一些其他的字符，相当于查询是跳过了前面的部分字符，截取部分去查询，这剩下的部分已经不再有序了，所以不会用到索引
而%在后面，也就相当于最左前缀（使用了name字段的最左前缀），从最开始的字符开始匹配，在索引树中的数据还是有序的，所以可以使用索引

问题：上面说过的left(name,3)其实是和name%是类似的，为什么后者走了索引，而前者没有走呢？
因为mysql底层中会进行判断，如果对字段加了函数，就不会走索引，尽管可以很好的利用索引，也不会走。


问题：解决like’%字符串%'索引不被使用的方法？
a）使用覆盖索引，查询字段必须是建立覆盖索引字段

b）如果不能使用覆盖索引则可能需要借助搜索引擎

九、字符串不加单引号索引失效

为什么类型不一样，就不会走索引？？
因为mysql在真正执行SQL语句的时候，检测到类型不匹配，底层可能会强转类型或者使用函数从而导致不走索引（虽然有的时候会做优化不一定会转类型，但是大部分时候还是会去做转换的）
所以查询的时候尽量使用相同的类型

十、少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、 表大小等多个因素整体评估是否使用索引，详见范围查询优化

十一、范围查询优化

给年龄添加单值索引

ALTER TABLE `employees` 2 ADD INDEX `idx_age` (`age`) USING BTREE ;

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索 引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引


优化方法：可以将大的范围拆分成多个小范围

索引使用总结

假设index(a,b,c)

where语句	索引是否使用
where a = 3	Y,使用到a
where a = 3 and b= 5	Y,使用到a，b
where a = 3 and b= 5 and c= 4	Y,使用到a，b，c
where b = 3 或者 where b= 3 and c= 4 或者 where c=4	N
where a = 3 and c= 5	Y,使用到a，但是不可以使用c，b中间断了
where a = 3 and b>4 and c= 5	Y,使用到a和b，c不能用在范围之后，b断了
where a = 3 and b like ‘kk%’ and c= 4	Y,使用到a，b，c
where a = 3 and b like ‘%kk’ and c= 4	Y,只用到a
where a = 3 and b like ‘%kk%’ and c= 4	Y,只用到a
where a = 3 and b like ‘k%kk%’ and c= 4	Y,使用到a，b，c

更多内容可前往MySQL是如何选择索引的？查看。