五分钟速览 MySQL的索引失效

摘要：大家好，我是半夏之沫，一名金融科技领域的JAVA系统研发我希望将自己工作和学习中的经验以最朴实，最严谨的方式分享给大家，共同进步写作不易，期待大家的关注和点赞技术探界】

大家好，我是半夏之沫，一名金融科技领域的JAVA系统研发
我希望将自己工作和学习中的经验以最朴实，最严谨的方式分享给大家，共同进步
写作不易，期待大家的关注和点赞
技术探界】使用MySQL时，明明数据没有很多，明明条件列添加了索引，但是为啥查询就越来越慢了呢。通常这就是出现了索引失效，为了让大家更能理解索引失效的场景，本文将基于一个简单的例子，对可能出现索引失效的场景进行分析，并给出相应解决方案，也许这才是面试官期望听到的索引失效。

MySQL版本：8.4.0

正文

一. 表数据准备

创建student表。

CREATE TABLE student ( id INT PRIMARY KEY auto_increment, stu_name varchar ( 255 ) NOT NULL, stu_age INT NOT NULL, stu_phone VARCHAR ( 20 ) DEFAULT NULL , stu_sex VARCHAR( 20 ) NOT NULL, stu_des VARCHAR( 255 ) NOT NULL);

插入若干条数据。

INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Bill", 20, "18888888888", "male", "Able to endure hardship" );INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Bob", 25, "17777777777", "male", "There will be endless bitterness to eat" );INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Mary", 21, "15555555555", "female", );INSERT INTO student ( stu_name, stu_age, stu_phone, stu_sex, stu_des ) VALUES ( "Bill", 22, "13333333333", "male", );

二. 函数计算导致索引失效

在一开始student表除了主键索引外没有其它任何索引，所以先执行下面语句为stu_name列添加一个普通索引，如下所示。

ALTER TABLE student ADD INDEX name_index ( stu_name );

此时我们再执行如下查询语句。

SELECT stu_name, stu_age, stu_phone FROM student WHERE SUBSTR( stu_name, 1, 1 ) = "B";

解释一下该条SQL语句，解释结果如下所示。

要解决这个问题也很简单，我们可以为stu_name列添加如下函数索引。

ALTER TABLE student ADD INDEX name_func_index ( (SUBSTR( stu_name, 1, 1 )) );

重新解释一下查询的SQL语句，结果如下所示。

在生成的执行计划中，显示MySQL决定使用name_func_index索引。

三. 隐式转换导致索引失效

有如下SQL语句。

SELECT stu_name, stu_age, stu_phone FROM student WHERE stu_name = 555;

注意到stu_name列类型是varchar，但是我们在进行条件查询时，将stu_name与数字进行了比较，此时解释一下上述SQL语句，结果如下。

要解决也很简单，就是查询条件类型与索引列保持一致即可。我们现在将查询SQL修改如下。

SELECT stu_name, stu_age, stu_phone FROM student WHERE stu_name = "555";

解释上述查询语句，结果如下。

此时因为没有发生隐式转换，所以执行计划使用了索引。

四. 最左匹配原则造成索引失效

在使用组合索引时，如果查询条件不满足从索引的最左边开始且不跳过索引中的列时，索引会失效，这就是我们背得较多的一种索引失效场景。但需要注意，如果是MySQL的8.x版本，因为MySQL引入了索引跳跃扫描这个功能，所以就算不满足最左匹配原则，索引也不一定会失效，具体还是要看MySQL优化器的最终选择。

现在为student表的stu_phone，stu_sex和stu_des列添加组合索引，语句如下。

ALTER TABLE student ADD INDEX phone_sex_des_index ( stu_phone, stu_sex, stu_des );

现在执行下面一条查询语句，如下所示。

SELECT stu_name, stu_age, stu_phone FROM student WHERE stu_des = "Able to endure hardship";

不用关心执行结果，下面解释一下这条SQL语句，结果如下。

可见没有使用索引，这里有两点需要关注。

不满足覆盖索引。查询的字段里面有stu_name和stu_age，这两个字段不在组合索引phone_sex_des_index中，因此如果使用组合索引，需要回表；不满足最左匹配原则。查询条件里面仅有stu_des，对于组合索引phone_sex_des_index来说，违反了最左匹配原则。因为MySQL在8.x版本引入了索引跳跃扫描，该机制让在组合索引场景下，就算不满足最左匹配原则，也可以使用组合索引，但是该机制相较于符合最左匹配原则时存在一定的性能开销，所以MySQL优化器对如下两种情况的开销做了判断。强行使用组合索引。索引跳跃扫描开销+回表开销；全表扫描。全表扫描开销。

最终判断出来是情况2开销小于情况1，故进行了全表扫描而没有使用组合索引。

现在再看一下下面这条语句。

SELECT stu_name, stu_age, stu_phone FROM student WHERE stu_phone = "18888888888"

解释信息如下。

结果显示是使用了组合索引。这是因为查询条件满足组合索引phone_sex_des_index的最左匹配原则，所以使用组合索引时没有了索引跳跃扫描的开销，最终优化器判断回表的性能开销小于全表扫描，故选择使用了组合索引。

再看下面这条SQL语句。

SELECT stu_phone, stu_sex, stu_desFROM student WHERE stu_des = "Able to endure hardship";

（注意到是满足覆盖索引的情况）解释上述SQL语句，结果如下。

最后看下面这条SQL语句。

SELECT stu_phone, stu_sex, stu_desFROM student WHERE stu_phone = "18888888888"AND stu_sex = "male";

这既符合覆盖索引，又符合最左匹配原则，此时肯定是会使用组合索引phone_sex_des_index的，解释信息如下。