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

48次阅读

共计 9015 个字符，预计需要花费 23 分钟才能阅读完成。

背景

无论你是技术大佬，还是刚入行的小白，时不时都会踩到 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 为 1
explain 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

正文完
mysql
发表至： mysql
2022-02-28
 0
转载BATJTMD-面试必问的-MySQL你懂了吗
关于mysql:如何查看-MySQL-数据库容量大小表容量大小索引容量大小找到占用空间最大的表
关于java:洋洋洒洒一万二千字彻底讲清楚MySQL的优化原理看不完先收藏
关于mysql:关于数据管理的一些思考