15个必备的Mysql索引失败场景，别再陷入那个陷阱了！制作人：

terry 2年前 (2023-09-26) 阅读数 48 #数据库

|一种看待编程的新方式（ID：ershixiong_see_world）

背景

无论你是技术大佬还是职场初学者，时不时就会踏入不使用索引的Mysql数据库的陷阱。明明在场上加了索引，但没有生效，这是一个常见的现象。

几天前，我遇到了一个有点不寻常的场景。同一条 SQL 语句对某些参数有效，但对其他参数无效。为什么是这样？

还有，无论是面试还是日常生活，都需要了解和学习Mysql索引失败的常见情况。

为了方便学习和记忆，本文档总结了15种常见的不按照索引的情况，并给出了例子，以便大家更好的避免误入陷阱。建议保留以备不时之需。数据库和索引准备 id：数据库主键；

union_idx：这是由id_no、username和age组成的联合索引；

create_time_idx：这是通常由create_time组成的索引；

初始化数据

初始化数据分为两部分：基础数据和批量导入数据。

基础数据中插入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），

❀的值为4；  之间 key_len的含义：表示索引使用的字节数。这个值可以用来判断索引的使用情况。尤其是在组合索引时，判断索引的使用量非常重要。  准备好以上数据和知识。让我们首先解释索引失败的具体示例。 
1 合并索引不符合最左对齐原则 
合并索引遵循最左对齐原则。顾名思义，联合索引中最左边的字段首先匹配字符。因此，在创建联合索引时，会将最常用的字段移至where子句中联合索引的左边缘。 
查询时如果希望查询条件被索引，需要满足以下要求：最左边的字段必须出现在查询条件中。在示例 
 中，联合索引  union_idx 由以下内容组成： 
KEY `union_idx` (`id_no`,`username`,`age`)
最左边的字段是 id_no。一般情况下，只要查询条件中出现id_no，就使用联合索引。 ？  这是索引。 
这里是key_len的计算： 
id_no类型是varchar(18)，字符集是utf8mb4_bin，这意味着我们用4个字节来表示完整的UTF-8。目前 key_len = 18* 4 = 72; 
 由于 varchar 字段类型是可变长度数据类型，因此必须添加 2 个额外字节。当前 key_len = 72 + 2 = 74； 
 由于该字段作为 NULL（默认 NULL）运行，因此必须添加 1 个附加字节。当前 key_len = 74 + 1 = 75； 
上面展示了一种情况下key_len的计算过程。将来，您无需单独下载它们。了解基本组成和原理就足够了。您可以自己控制更多情况。示例2 索引，根据上面key_len的分析，粗体提示，在使用索引时，我们不仅使用id_no列，还使用username列。示例 3 与第一个示例类似，仅使用 Arrivedid_no 列。 
 当然也有三列都包含在查询条件中的情况，就不举例了。以上都是前瞻性的索引示例，即符合最左匹配原则的示例。我们来看看不遵循这个原则的逆向例子。 
反转示例：
explain select * from t_user where username = 'Tom2' and age = 12;
解释结果：explain-04
目前可以看到没有使用索引，也就是说索引无效。 
同样，只要不出现下面最左边的条件组合，索引也会失效： 
explain select * from t_user where age = 12;
explain select * from t_user where username = 'Tom2';
那么第一种索引失败场景： 普通索引场景中，查询条件不匹配最左边的匹配原则。 
2 使用 select *
 《阿里巴巴开发手册》 的 ORM 映射  在[强制]表]中必须指定：在查询的情况下，不要使用 * 作为列表必须明确指出查询字段的数量，哪些字段必须填写。说明：1）增加查询分析器的分析成本。 2）增减字段很容易与resultMap配置不兼容。 3）无用字段会增加网络消耗，尤其是文本字段。 
虽然规范手册中没有讨论索引问题，但使用 select * 语句可以有一个附带的好处：在某些情况下co 。 
 比如上面组合索引中的查询条件是年龄或者用户名，那么使用select *时，肯定不会使用该索引。
但是如果你想根据username（所有索引字段）查询id_no、username、age这三个结果，并明确查询结果字段，可以使用覆盖索引: 解释结果：  覆盖索引 
 无论查询条件是否为用户名
explain select * from t_user where id not in (2,3);
，都会使用索引。根据key_len可以看到索引的所有列都在使用中。 
第二种索引失败场景：在合并索引下，尝试使用干净的查询列来替代使用覆盖索引； 
 这种情况，当你不使用索引时，是一个优化元素。如果满足业务If场景，则继续请求对SQL语句进行索引。至于阿里巴巴开发手册中的规范，只是两者有冲突。规范本身并未为此索引规则定义。 ？转到索引。 
这样的话，不仅是索引问题，还会增加数据库的计算负载。以上面的SQL语句为例，数据库必须扫描整个表找出所有id字段的值，然后计算它们，然后与参数值进行比较。如果所有执行都经过上述步骤，性能下降是可想而知的。 
推荐的使用方式是先计算内存中的期望值或者计算SQL语句条件右侧的参数值。 
上例优化如下： 
-- 内存计算，得知要查询的id为1
explain select * from t_user where id = 1 ;
-- 参数侧计算
explain select * from t_user where id = 2 - 1 ;
第三种索引错误情况：索引列参与导致全表扫描，索引错误。 ？ 。 
目前索引失败的原因与第三种情况相同。这是因为数据库要先进行全表扫描，获取到数据之后再进行接受和计算，从而导致索引失效。然而，也存在性能问题。 
示例仅列出了 SUBSTR 函数。类似的情况也发生在类似的函数如 CONCAT 上。解决方法可以参考第三种情况。您可以考虑使用内存计算或其他内容处理方法来减少数据库。 
第四种索引错误情况：索引列参与函数处理，导致全表扫描，索引错误。
5不正确使用类似的示例：explain select * from t_user where id_no like '%00%';
解释结果：indexlike
i真的很喜欢
❙频繁但不正确的使用通常会导致无索引。常见的使用方法有：
1。方法：如'%abc'； 
2。方法：如“abc%”； 
3。方法：如：“%abc%”； 
1。方法和方法3一样，由于占位符出现在头部，所以不能使用索引。在这种情况下缺乏索引的原因很容易理解。索引本身对应一个目录，从左到右一一排列。占位符位于条件的左侧，这使得无法通过正常目录进行匹配，这对于索引失败来说是正常的。 
第五个索引错误：在模糊查询（如指令）过程中，模糊匹配占位符位于条件的头部。 ？扫描。 
索引错误的原因：varchar和int是两种不同的类型。解决
的方法是用单引号或双引号将参数1002括起来。 
第六种索引错误情况：参数的类型与字段的类型不匹配，导致类型隐式转换，索引失败。 
 这种情况有一个特殊情况。如果字段类型为int类型，并且查询条件添加了单引号或双引号，Mysql会将参数转换为int类型，即使它们使用单引号或双引号： 
explain select * from t_user where id = '2';
 上述语句仍然会被索引。 
7。使用OR运算 
OR 日常生活中最常用的运算关键字，但使用不当也会导致索引失败。 ？ ，索引确实失败了。 
而且，如果我们换个角度想，如果只用用户名字段作为条件的话，显然是一个全表扫描的问题。由于进行了全表扫描，之前的条件id如果再去一遍索引就太可惜了。因此，在使用or关键字时，记得给两个条件都添加索引，否则索引会失效。
但是如果or两边都有“">”和“