关于mysql:15个必知的Mysql索引失效场景别再踩坑了

背景

无论你是技术大佬，还是刚入行的小白，时不时都会踩到Mysql数据库不走索引的坑。常见的景象就是：明明在字段上增加了索引，但却并未失效。

前些天就遇到一个略微非凡的场景，同一条SQL语句，在某些参数下失效，在某些参数下不失效，这是为什么呢？

另外，无论是面试或是日常，Mysql索引生效的通常状况都应该理解和学习。

为了不便学习和记忆，这篇文件将常见的15种不走索引状况进行汇总，并以实例展现，帮忙大家更好地防止踩坑。倡议珍藏，以备不时之需。

数据库及索引筹备

创立表构造

为了逐项验证索引的应用状况，咱们先筹备一张表t_user：

CREATE TABLE `t_user` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',  `id_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份编号',  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',  `age` int(11) DEFAULT NULL COMMENT '年龄',  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创立工夫',  PRIMARY KEY (`id`),  KEY `union_idx` (`id_no`,`username`,`age`),  KEY `create_time_idx` (`create_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

在上述表构造中有三个索引：

id：为数据库主键；
union_idx：为id_no、username、age形成的联结索引；
create_time_idx：是由create_time形成的一般索引；

初始化数据

初始化数据分两局部：根底数据和批量导入数据。

根底数据insert了4条数据，其中第4条数据的创立工夫为将来的工夫，用于后续非凡场景的验证：

INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1001', 'Tom1', 11, '2022-02-27 09:04:23');INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1002', 'Tom2', 12, '2022-02-26 09:04:23');INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1003', 'Tom3', 13, '2022-02-25 09:04:23');INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1004', 'Tom4', 14, '2023-02-25 09:04:23');

除了根底数据，还有一条存储过程及其调用的SQL，不便批量插入数据，用来验证数据比拟多的场景：

-- 删除历史存储过程DROP PROCEDURE IF EXISTS `insert_t_user`-- 创立存储过程delimiter $CREATE PROCEDURE insert_t_user(IN limit_num int)BEGIN    DECLARE i INT DEFAULT 10;    DECLARE id_no varchar(18) ;    DECLARE username varchar(32) ;    DECLARE age TINYINT DEFAULT 1;    WHILE i < limit_num DO        SET id_no = CONCAT("NO", i);        SET username = CONCAT("Tom",i);        SET age = FLOOR(10 + RAND()*2);        INSERT INTO `t_user` VALUES (NULL, id_no, username, age, NOW());        SET i = i + 1;    END WHILE;END $-- 调用存储过程call insert_t_user(100);

对于存储过程的创立和存储，可临时不执行，当用到时再执行。

数据库版本及执行打算

查看以后数据库的版本：

select version();8.0.18

上述为自己测试的数据库版本：8.0.18。当然，以下的所有示例，大家可在其余版本进行执行验证。

查看SQL语句执行打算，个别咱们都采纳explain关键字，通过执行后果来判断索引应用状况。

执行示例：

explain select * from t_user where id = 1;

执行后果：

能够看到上述SQL语句应用了主键索引（PRIMARY），key_len为4；

其中key_len的含意为：示意索引应用的字节数，依据这个值能够判断索引的应用状况，特地是在组合索引的时候，判断该索引有多少局部被应用到十分重要。

做好以上数据及常识的筹备，上面就开始解说具体索引生效的实例了。

1 联结索引不满足最左匹配准则

联结索引听从最左匹配准则，顾名思义，在联结索引中，最左侧的字段优先匹配。因而，在创立联结索引时，where子句中应用最频繁的字段放在组合索引的最左侧。

而在查问时，要想让查问条件走索引，则需满足：最右边的字段要呈现在查问条件中。

实例中，union_idx联结索引组成：

KEY `union_idx` (`id_no`,`username`,`age`)

最右边的字段为id_no，个别状况下，只有保障id_no呈现在查问条件中，则会走该联结索引。

示例一：

explain select * from t_user where id_no = '1002';

explain后果：

通过explain执行后果能够看出，上述SQL语句走了union_idx这条索引。

这里再遍及一下key_len的计算：

id_no 类型为varchar(18)，字符集为utf8mb4_bin，也就是应用4个字节来示意一个残缺的UTF-8。此时，key_len = 18* 4 = 72；
因为该字段类型varchar为变长数据类型，须要再额定增加2个字节。此时，key_len = 72 + 2 = 74；
因为该字段运行为NULL（default NULL），须要再增加1个字节。此时，key_len = 74 + 1 = 75；

下面演示了key_len一种状况的计算过程，后续不再进行逐个推演，晓得根本组成和原理即可，更多状况大家可自行查看。

示例二：

explain select * from t_user where id_no = '1002' and username = 'Tom2';

explain后果：

很显然，仍旧走了union_idx索引，依据下面key_len的剖析，大胆猜想，在应用索引时，不仅应用了id_no列，还应用了username列。

示例三：

explain select * from t_user where id_no = '1002' and age = 12;

explain后果：

走了union_idx索引，但跟示例一一样，只用到了id_no列。

当然，还有三列都在查问条件中的状况，就不再举例了。下面都是走索引的正向例子，也就是满足最左匹配准则的例子，上面来看看，不满足该准则的反向例子。

反向示例：

explain select * from t_user where username = 'Tom2' and age = 12;

explain后果：

此时，能够看到未走任何索引，也就是说索引生效了。

同样的，上面只有没呈现最左条件的组合，索引也是生效的：

explain select * from t_user where age = 12;explain select * from t_user where username = 'Tom2';

那么，第一种索引生效的场景就是：在联结索引的场景下，查问条件不满足最左匹配准则。

2 应用了select *

在《阿里巴巴开发手册》的ORM映射章节中有一条【强制】的标准：

【强制】在表查问中，一律不要应用 * 作为查问的字段列表，须要哪些字段必须明确写明。阐明:1)减少查问分析器解析老本。2)增减字段容易与 resultMap 配置不统一。3)无用字段减少网络耗费，尤其是 text 类型的字段。

尽管在标准手册中没有提到索引方面的问题，但禁止应用select * 语句可能会带来的附带益处就是：某些状况下能够走笼罩索引。

比方，在下面的联结索引中，如果查问条件是age或username，当应用了select * ，必定是不会走索引的。

但如果心愿依据username查问出id_no、username、age这三个后果（均为索引字段），明确查问后果字段，是能够走笼罩索引的：

explain select id_no, username, age from t_user where username = 'Tom2';explain select id_no, username, age from t_user where age = 12;

explain后果：

无论查问条件是username还是age，都走了索引，依据key_len能够看出应用了索引的所有列。

第二种索引生效场景：在联结索引下，尽量应用明确的查问列来趋向于走笼罩索引；

这一条不走索引的状况属于优化项，如果业务场景满足，则进来促使SQL语句走索引。至于阿里巴巴开发手册中的标准，只不过是两者撞到一起了，标准自身并不是为这条索引规定而定的。

3 索引列参加运算

间接来看示例：

explain select * from t_user where id + 1 = 2 ;

explain后果：

能够看到，即使id列有索引，因为进行了计算解决，导致无奈失常走索引。

针对这种状况，其实不单单是索引的问题，还会减少数据库的计算累赘。就以上述SQL语句为例，数据库须要全表扫描出所有的id字段值，而后对其计算，计算之后再与参数值进行比拟。如果每次执行都经验上述步骤，性能损耗可想而知。

倡议的应用形式是：先在内存中进行计算好预期的值，或者在SQL语句条件的右侧进行参数值的计算。

针对上述示例的优化如下：

-- 内存计算，得悉要查问的id为1explain select * from t_user where id = 1 ;-- 参数侧计算explain select * from t_user where id = 2 - 1 ;

第三种索引生效状况：索引列参加了运算，会导致全表扫描，索引生效。

4 索引列参应用了函数

示例：

explain select * from t_user where SUBSTR(id_no,1,3) = '100';

explain后果：

上述示例中，索引列应用了函数（SUBSTR，字符串截取），导致索引生效。

此时，索引生效的起因与第三种状况一样，都是因为数据库要先进行全表扫描，取得数据之后再进行截取、计算，导致索引索引生效。同时，还随同着性能问题。

示例中只列举了SUBSTR函数，像CONCAT等相似的函数，也都会呈现相似的状况。解决方案可参考第三种场景，可思考先通过内存计算或其余形式缩小数据库来进行内容的解决。

第四种索引生效状况：索引列参加了函数解决，会导致全表扫描，索引生效。

5 谬误的Like应用

示例：

explain select * from t_user where id_no like '%00%';

explain后果：

针对like的应用十分频繁，但使用不当往往会导致不走索引。常见的like应用形式有：

形式一：like '%abc'；
形式二：like 'abc%'；
形式三：like '%abc%'；

其中形式一和形式三，因为占位符呈现在首部，导致无奈走索引。这种状况不做索引的起因很容易了解，索引自身就相当于目录，从左到右一一排序。而条件的左侧应用了占位符，导致无奈依照失常的目录进行匹配，导致索引生效就很失常了。

第五种索引生效状况：含糊查问时（like语句），含糊匹配的占位符位于条件的首部。

6 类型隐式转换

示例：

explain select * from t_user where id_no = 1002;

explain后果：

id_no字段类型为varchar，但在SQL语句中应用了int类型，导致全表扫描。

呈现索引生效的起因是：varchar和int是两个种不同的类型。

解决方案就是将参数1002增加上单引号或双引号。

第六种索引生效状况：参数类型与字段类型不匹配，导致类型产生了隐式转换，索引生效。

这种状况还有一个特例，如果字段类型为int类型，而查问条件增加了单引号或双引号，则Mysql会参数转化为int类型，尽管应用了单引号或双引号：

explain select * from t_user where id = '2';

上述语句是依旧会走索引的。

7、应用OR操作

OR是日常应用最多的操作关键字了，但使用不当，也会导致索引生效。

示例：

explain select * from t_user where id = 2 or username = 'Tom2';

explain后果：

看到上述执行后果是否是很惊奇啊，明明id字段是有索引的，因为应用or关键字，索引居然生效了。

其实，换一个角度来想，如果独自应用username字段作为条件很显然是全表扫描，既然曾经进行了全表扫描了，后面id的条件再走一次索引反而是节约了。所以，在应用or关键字时，切记两个条件都要增加索引，否则会导致索引生效。

但如果or两边同时应用“>”和“<”，则索引也会生效：

explain select * from t_user where id  > 1 or id  < 80;

explain后果：

第七种索引生效状况：查问条件应用or关键字，其中一个字段没有创立索引，则会导致整个查问语句索引生效； or两边为“>”和“<”范畴查问时，索引生效。

8 两列做比拟

如果两个列数据都有索引，但在查问条件中对两列数据进行了比照操作，则会导致索引生效。

这里举个不失当的示例，比方age小于id这样的两列（实在场景可能是两列同维度的数据比拟，这里迁就现有表构造）：

explain select * from t_user where id > age;

explain后果：

这里尽管id有索引，age也能够创立索引，但当两列做比拟时，索引还是会生效的。

第八种索引生效状况：两列数据做比拟，即使两列都创立了索引，索引也会生效。

9 不等于比拟

示例：

explain select * from t_user where id_no <> '1002';

explain后果：

当查问条件为字符串时，应用”<>“或”!=“作为条件查问，有可能不走索引，但也不全是。

explain select * from t_user where create_time != '2022-02-27 09:56:42';

上述SQL中，因为“2022-02-27 09:56:42”是存储过程在同一秒生成的，大量数据是这个工夫。执行之后会发现，当查问后果集占比比拟小时，会走索引，占比比拟大时不会走索引。此处与后果集与总体的占比无关。

须要留神的是：上述语句如果是id进行不等操作，则失常走索引。

explain select * from t_user where id != 2;

explain后果：

第九种索引生效状况：查问条件应用不等进行比拟时，须要谨慎，一般索引会查问后果集占比拟大时索引会生效。

10 is not null

示例：

explain select * from t_user where id_no is not null;

explain后果：

第十种索引生效状况：查问条件应用is null时失常走索引，应用is not null时，不走索引。

11 not in和not exists

在日常中应用比拟多的范畴查问有in、exists、not in、not exists、between and等。

explain select * from t_user where id in (2,3);explain select * from t_user where id_no in ('1001','1002');explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);explain select * from t_user where id_no between '1002' and '1003';

上述四种语句执行时都会失常走索引，具体的explain后果就不再展现。次要看不走索引的状况：

explain select * from t_user where id_no not in('1002' , '1003');

explain后果：

当应用not in时，不走索引？把条件列换成主键试试：

explain select * from t_user where id not in (2,3);

explain后果：

如果是主键，则失常走索引。

第十一种索引生效状况：查问条件应用not in时，如果是主键则走索引，如果是一般索引，则索引生效。

再来看看not exists：

explain select * from t_user u1 where not exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);

explain后果：

当查问条件应用not exists时，不走索引。

第十二种索引生效状况：查问条件应用not exists时，索引生效。

12 order by导致索引生效

示例：

explain select * from t_user order by id_no ;

explain后果：

其实这种状况的索引生效很容易了解，毕竟须要对全表数据进行排序解决。

那么，增加删limit关键字是否就走索引了呢？

explain select * from t_user order by id_no limit 10;

explain后果：

后果仍旧不走索引。在网络上看到有说如果order by条件满足最左匹配则会失常走索引，在以后8.0.18版本中并未呈现。所以，在基于order by和limit进行应用时，要特地注意。是否走索引不仅波及到数据库版本，还要看Mysql优化器是如何解决的。

这里还有一个特例，就是主键应用order by时，能够失常走索引。

explain select * from t_user order by id desc;

explain后果：

能够看出针对主键，还是order by能够失常走索引。

另外，笔者测试如下SQL语句：

explain select id from t_user order by age;explain select id , username from t_user order by age;explain select id_no from t_user order by id_no;

上述三条SQL语句都是走索引的，也就是说笼罩索引的场景也是能够失常走索引的。

当初将id和id_no组合起来进行order by：

explain select * from t_user order by id,id_no desc;explain select * from t_user order by id,id_no desc limit 10;explain select * from t_user order by id_no desc,username desc;

explain后果：

上述两个SQL语句，都未走索引。

第十三种索引生效状况：当查问条件波及到order by、limit等条件时，是否走索引状况比较复杂，而且与Mysql版本无关，通常一般索引，如果未应用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其余状况，倡议在应用时进行expain验证。

13 参数不同导致索引生效

此时，如果你还未执行最开始创立的存储过程，倡议你先执行一下存储过程，而后执行如下SQL：

explain select * from t_user where create_time > '2023-02-24 09:04:23';

其中，工夫是将来的工夫，确保可能查到数据。

explain后果：

能够看到，失常走索引。

随后，咱们将查问条件的参数换个日期：

explain select * from t_user where create_time > '2022-02-27 09:04:23';

explain后果：

此时，进行了全表扫描。这也是最开始提到的奇怪的景象。

为什么同样的查问语句，只是查问的参数值不同，却会呈现一个走索引，一个不走索引的状况呢？

答案很简略：上述索引生效是因为DBMS发现全表扫描比走索引效率更高，因而就放弃了走索引。

也就是说，当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，主动变成全表扫描。某些场景下即使强制SQL语句走索引，也同样会生效。

相似的问题，在进行范畴查问（比方\>、< 、>=、<=、in等条件）时往往会呈现上述情况，而下面提到的临界值依据场景不同也会有所不同。

第十四种索引生效状况：当查问条件为大于等于、in等范畴查问时，依据查问后果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描。

14 其余

当然，还有其余一些是否走索引的规定，这与索引的类型是B-tree索引还是位图索引也有关系，就不再具体开展。

这里要说的其余，能够总结为第十五种索引生效的状况：Mysql优化器的其余优化策略，比方优化器认为在某些状况下，全表扫描比走索引快，则它就会放弃索引。

针对这种状况，个别不必过多理睬，当发现问题时再定点排查即可。

小结

本篇文章为大家总结了15个常见的索引生效的场景，因为不同的Mysql版本，索引生效策略也有所不同。大多数索引生效状况都是明确的，有少部分索引生效会因Mysql的版本不同而有所不同。因而，倡议珍藏本文，当在实际的过程中进行对照，如果没方法精确把握，则可间接执行explain进行验证。

博主简介：《SpringBoot技术底细》技术图书作者，热爱钻研技术，写技术干货文章。
公众号：「程序新视界」，博主的公众号，欢送关注~
技术交换：请分割博主微信号：zhuan2quan