一网打尽：MySQL索引失效的场景大搜罗

索引是数据库优化的利器，它能大大提高SQL的执行效率。然而，索引犹如一把双刃剑，如若使用不当，它不仅不能提升速度，反倒会降低数据库的效率，本来想利用索引进行优化，却遭到了反噬，你说气人不气人。

那么，今天我们就来梳理一下，有哪些情况会导致SQL查询不走索引，也就是说，即便我们创建了索引，但SQL语句在执行时依然无视索引的存在，执拗地进行了全表扫描。

一、准备工作

我们首先在数据库中创建测试用的数据表，并创建索引，然后向表中插入测试数据以便进行验证。以下脚本在MySQL 5.7中验证通过，朋友们可以直接复制到自己的本地测试库中进行实验。说明一下，以下的测试基于常见的存储引擎InnoDB，索引也是常用的BTREE类型。

1. 创建表。

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `first_name` varchar(10) NOT NULL COMMENT '名字',
  `last_name` varchar(32) NOT NULL COMMENT '姓氏',
  `gender` varchar(10) NOT NULL COMMENT '性别',
  `grade` tinyint(1) NOT NULL COMMENT '年级',
  `enroll_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 在表中创建索引，一个是针对名字的索引，一个是名字和姓氏的复合索引。

ALTER TABLE `t_student`
ADD INDEX `idx_first_name` (`first_name`) USING BTREE ,
ADD INDEX `idx_full_name` (`first_name`, `last_name`) USING BTREE ;

3. 向表中插入10W行数据，姓名由随机生成的字符组成（插入速度较慢，测试的时候，也可以先插入较少的数据，直接修改第一个循环的数值即可）。

DROP PROCEDURE IF EXISTS PROC_INSERT_STUDENT;
 DELIMITER //
 CREATE PROCEDURE PROC_INSERT_STUDENT()
 BEGIN
  DECLARE i,j,k INT DEFAULT 1;
  DECLARE firstName,lastName,randomChar VARCHAR(64) DEFAULT '';
  WHILE(i<=100000) DO
   -- 随机生成四个字符的名字
   SET firstName='';
   SET j=1;
   WHILE(j<=4) DO
    SET randomChar = CHAR(FLOOR(RAND()*26)+97);
    SET firstName = CONCAT(firstName, randomChar);
    SET j=j+1;
   END WHILE;
   -- 随机生成六个字符的姓氏
   SET lastName='';
   SET k=1;
   WHILE(k<=6) DO
    SET randomChar = CHAR(FLOOR(RAND()*26)+97);
    SET lastName = CONCAT(lastName, randomChar);
    SET k=k+1;
   END WHILE;
   
   INSERT INTO t_student(first_name, last_name, gender, grade, enroll_time)
    VALUES(firstName, lastName, 
     CASE FLOOR((RAND()*100))%2 WHEN 0 THEN 'male' ELSE 'female' END, 
     FLOOR((RAND()*100))%6+1,
     now());
   SET i=i+1;
  END WHILE;
 END;
 //
CALL PROC_INSERT_STUDENT();

4. 我们先验证一下索引可以正常工作。从下图可以看出，普通的等值查询走了索引。

接下来我们看看，到底有哪些情况会导致：即使创建了索引，但查询语句依然进行了全表扫描。

二、模糊查询like以%开头

这个相对比较容易理解，因为%是通配符，可以匹配零个或者多个的任意字符，如果模糊查询时以%开头，当然就不走索引了。

EXPLAIN SELECT * FROM t_student WHERE first_name LIKE '%tom'

可以看出，执行计划的type字段的值为ALL，它进行了全表扫描。

三、在索引字段上进行计算操作

我们在主键索引上进行测试，比如，我们想查询主键值+1后等于1024的记录，如果WHERE语句的条件写成id+1=1024，这样是不走索引的。当然，把它写成id=1024-1这样的形式是可以走索引的。

EXPLAIN SELECT * FROM t_student WHERE id+1=1024

四、在索引字段上使用函数

在索引上使用函数也会导致它失效，包括字符串函数、数学函数等，比如，我们想查询名字中第二个字符为【i】的数据，SQL和执行计划结果如下。

EXPLAIN SELECT * FROM t_student WHERE SUBSTR(first_name,2,1)='i'

它还是走了全表扫描，索引又失效了。当然，函数有很多，这种场景算是针对函数这一类的情况而言。

五、数据类型不匹配

在WHERE条件中，数据类型不匹配也会导致索引，这一点比较容易被忽视，可能也算是个高频的面试题了。我们知道，在测试表中，first_name的数据类型为字符，如果查询条件为整型，那么索引也会不起作用。

EXPLAIN SELECT * FROM t_student WHERE first_name=20

六、联合索引没有使用第一列

在测试表中，我们针对字段first_name和last_name创建了联合索引，如果查询语句只匹配last_name的数据，那么索引也是无效的。

EXPLAIN SELECT * FROM t_student WHERE last_name='tom'

七、字符串编码不同

这种情况相对比较偏门，在字段上创建了索引，但因为WHERE条件中等号两边的编码不同，就导致了索引失效的情况，略微有点“坑爹”。

EXPLAIN SELECT * FROM t_student WHERE CONVERT(first_name using utf8mb4)=CONVERT('tom' USING latin1)

八、查询条件OR包含非索引的列

在查询条件中，如果有OR运算符且它包含了没有创建索引的列，也会导致查询语句不走索引。

EXPLAIN SELECT * FROM t_student WHERE first_name='tom' OR grade=5

九、总结

上面罗列了一些常见的不走索引的情况：

模糊查询like以%开头
在索引字段上进行计算操作
在索引字段上使用函数
数据类型不匹配
联合索引没有使用第一列
字符串编码不同
查询条件OR包含非索引的列

除了以上这几种情况，还有一些场景也会导致索引失效，比如：!=、<>或者not in可能会导致索引失效；在索引字段使用is null或者is not null也可能不走索引；范围查询有可能不走索引；优化器认为全表扫描比使用索引更快，那也不走索引；使用MySQL的hint，也就是IGNORE INDEX强制不使用索引，等等。

笔者水平有限，虽然竭尽所能想要穷尽所有情况，但也难免存在漏网之鱼，那么，还有其他导致索引失效的情况吗？欢迎讨论。