索引失效（重点）

太过爱你忘了你带给我的痛 2023-01-17 07:45 108阅读 0赞

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

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

# 索引失效 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 1][]

## **1、全职匹配我最爱** ##

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

## 2、最佳左前缀法则 ##

![20210427191644886.png][]

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

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

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

**用B、C完全没有效果**

看例子：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 2][]

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都不能再使用了。

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

![20210427191723167.png][]

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

如果是这种情况：

![20210427191723336.png][]

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

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

![20210427191723400.png][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 3][]

phone\_number 就相当于 c4。

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

![2021042719174866.png][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 4][]

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

![20210427191748449.png][]

例子：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 5][]

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

## 4、 ##

![20210427191748413.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 6][]

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

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

## 5、 ##

![2021042719181527.png][]

原因很简单：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 7][]

## 6、 ##

![20210427191815365.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 8][]

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

## 7、 ##

## ![20210427191815428.png][] ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 9][]

## 8、 ##

![2021042719184692.png][]

![20210427191846225.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 10][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 11][]

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

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

![20210427191846325.png][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 12][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 13][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 14][]

## 9、 ##

## ![20210427191919753.png][] ##

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 15][]

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

## 10、 ##

![202104271919208.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 16][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 17][]

11、小总结

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 18][]

[Link 1]: https://blog.csdn.net/IT_TIfarmer/article/details/110521071
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70]: /images/20221021/761248122b694fddbbe4d8074e1a32c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 1]: /images/20221021/fca23c61f74c4201b46182c6eba2783f.png
[20210427191644886.png]: /images/20221021/6c5fea5b1ede4695b1c692207d593c6b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 2]: /images/20221021/231f40f720c34b748eda1538650a86d0.png
[20210427191723167.png]: /images/20221021/3fff11bb0c8342f99c71645ca7567e05.png
[20210427191723336.png]: /images/20221021/fd96a15a3727438abc2cdb8b049b92fb.png
[20210427191723400.png]: /images/20221021/b00f5e8417d146fe833d8eb68edd0f36.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 3]: /images/20221021/84743231e5a34865a193c6daae4df9de.png
[2021042719174866.png]: /images/20221021/c1ee4bb82c8146ffbdb98fc218d5a4b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 4]: /images/20221021/69b6967af3b349a1af7d86672982c1db.png
[20210427191748449.png]: /images/20221021/06e7db4012ff4061a5e09f1a4a83fc94.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 5]: /images/20221021/f3c5c79cf20a469dae1b90fc300ef3f1.png
[20210427191748413.png]: /images/20221021/510765e719424d4ca9d99e29c70f2514.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 6]: /images/20221021/7191a040029944d0a4338f4330ea188c.png
[2021042719181527.png]: /images/20221021/a1c9cb715c81426c9314419d1dbf11cd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 7]: /images/20221021/c8e134b5d8b84ff3a4631b51c13c6853.png
[20210427191815365.png]: /images/20221021/bdd1af5182dc489d817d93e4e008ef93.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 8]: /images/20221021/115daac71b404527a5ed9004fc9ed6f3.png
[20210427191815428.png]: /images/20221021/00577a4aeb7d40a0bb172b51f63e3582.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 9]: /images/20221021/4082c2c28ce7481d96f388e39fee8ad3.png
[2021042719184692.png]: /images/20221021/f21616a769b842d78fbe89738a65dbf3.png
[20210427191846225.png]: /images/20221021/a5189bd75732418380a39245796d02fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 10]: /images/20221021/2917ee1a8a504c4eb0ed5ff39ff73d5e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 11]: /images/20221021/58289170d5d64474be57c2e32006109c.png
[20210427191846325.png]: /images/20221021/8409772032cd48d18e14f98825c4339f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 12]: /images/20221021/a394836198e14cdaa98d72e1c11d7e65.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 13]: /images/20221021/b1d1e6ac0949465e8d8aba2b8d6c8a43.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 14]: /images/20221021/4342845801f349aeb1ed8bfcaef9c591.png
[20210427191919753.png]: /images/20221021/1495fc897f794c3884cb06150e9833ee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 15]: /images/20221021/59e90dd0d2a041a3aae263ca7f884520.png
[202104271919208.png]: /images/20221021/0deb93ab14f041adb25680446a228a07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 16]: /images/20221021/b287af63f1774cf69146ec9462f188a5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 17]: /images/20221021/1cbbfe46dcd1468580a4a6864da0c749.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 18]: /images/20221021/d08fa683054a4a0ba9ed02f824c1d6a9.png