15个必知的Mysql索引失效场景，别再踩坑了

矫情吗；* 2023-09-30 12:50 8阅读 0赞

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16][]

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

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

-- 删除历史存储过程
    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;

执行结果：

![5c8c832d40777fdac1ad385d4bb50853.png][]

explain

可以看到上述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结果：

![368ab777f73aa7425a51eac5d943bb14.png][]

explain-01

通过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&

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/cfeca42d8df94f56a3cd5dab1cd86de5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Y2B5LiA5oqA5pyv5pap,size_20,color_FFFFFF,t_70,g_se,x_16
[5c8c832d40777fdac1ad385d4bb50853.png]: https://img-blog.csdnimg.cn/img_convert/5c8c832d40777fdac1ad385d4bb50853.png
[368ab777f73aa7425a51eac5d943bb14.png]: https://img-blog.csdnimg.cn/img_convert/368ab777f73aa7425a51eac5d943bb14.png