索引失效（重点）

本教程中所使用的数据库的建表语句都在“MySQL高阶教程索引”这篇文章中，点击链接直达：索引&建表语句

摘要：本文主要介绍MySQL的索引优化模块的索引失效

 

索引失效

 

1、全职匹配我最爱

建立的索引 idx1（A，B，C）三个列全部都用上了

 

2、最佳左前缀法则

 

最左边的左前缀索引什么时候都不要丢，丢了后边两个就无效了，并且左前缀单独使用是有效地。

如果用A、B可以达到最佳效果

用A、C不能达倒最佳优化效果但也能优化

用B、C完全没有效果

看例子：

 

 

salary是A，manager_id是B，last_name是C

明显下面A，B要更好，它的key_len比A，C长，ref中也出现了两个常量，说明用到了两个索引，而A，C第二个C索引失效了。

 

如果建立索引index（c1，c2, c3，c4），那么从开始使用范围查找的第ai个索引之后，其它的就全部失效了。

比如 where c1 = 'a' and c4 = 'a' and c3 = 'a' and c2 > 'a';

那么c3和c4都不能再使用了。

相似的，中间空一个索引，也会使空的索引之后的索引失效，比如：

中间空了个c3，c4就失效了，排序时自然是没用上索引，所以显示using filesort。

 

如果是这种情况：

只有一个c1索引被用于查找，c2，和c3都用于排序了（也用上了，因为没有产生filesort）。

 

看看这个例子，瞅瞅order by有多坑爹：

跟上边对比，只是更改了c2、c3的顺序，联合索引就gg了，c2、c3全没用上，产生了filesort！

 

但and不用管这么多，它是可忽略索引顺序的，因为mysql会自动帮我们优化：

phone_number 就相当于 c4。

 

那如果我们非要用order by c3, c2呢？

答案如图，只要前面出现了c3之前的所有索引即可。

 

ok，坑爹玩意可不止order by一个，group by也是这个德行：

 

 

3、不在索引上做任何操作

 

例子：

在索引上加了个内置函数，查询结果一样，但性能gg了。

 

4、

 

第一个查询语句where中用到了idx1（A，B，C）的全部，而且索引都生效了，key_len也很长

但第二个查询语句，age的检索条件变成了范围，最后一个索引就失效了，只用了A和B。

 

5、

 

原因很简单：

 

6、

 

注意possible_keys 和 keys， 使用了不等号之后，没有实际上用索引。

 

7、

 

8、

 

注意type属性，我们已经为last_name创建了索引，like本来是range，但最左边的统配符会让它变成全表扫描，所以请不要把通配符放在最左边。除了最左边放在哪都好。

那如果生产中非要这样呢？

答案就是，为select后面的字段使用覆盖索引。

 

select后面的字段可以是某个组合索引里索引字段的任意组合，甚至还能出现该复合索引之外的主键或唯一索引。但不能出现其它的普通索引列，和或者不是索引的列。

 

注意select中语句如果只出现了后缀索引，也是生效的，这跟前面有些不太一样，前面好像说过前缀索引能单独使用，而后缀索引不可以，会无效，咋在这里就有效了呢，注意，前面说的是：不是like最左边放通配符的情况下，而是select为*，用等值的情况下。

 

 

9、

varchar类型一定不能失去单引号。如下，last_name是前缀索引：

这其实和第3条有些重复，它属于隐式的类型转换。

 

10、

 

可以看到，虽然department_id和job_id都有索引，但一用or就两个索引都用不上了，就连联合索引都救不了它：

 

 

11、小总结