作者 | 践行精神自由 来源 | 互联网全栈架构
索引是数据库优化的利器，它能大大提高SQL的执行效率。然而，索引犹如一把双刃剑，如若使用不当，它不仅不能提升速度，反倒会降低数据库的效率，本来想利用索引进行优化，却遭到了反噬，你说气人不气人。
那么，今天我们就来梳理一下，有哪些情况会导致SQL查询不走索引，也就是说，即便我们创建了索引，但SQL语句在执行时依然无视索引的存在，执拗地进行了全表扫描。
一、准备工作
我们首先在数据库中创建测试用的数据表索引失效的几种情况，并创建索引，然后向表中插入测试数据以便进行验证。以下脚本在MySQL 5.7中验证通过，朋友们可以直接复制到自己的本地测试库中进行实验。说明一下，以下的测试基于常见的存储引擎，索引也是常用的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条件中，数据类型不匹配也会导致索引，这一点比较容易被忽视，可能也算是个高频的面试题了。我们知道，在测试表中，的数据类型为字符，如果查询条件为整型，那么索引也会不起作用。
EXPLAIN SELECT * FROM t_student WHERE first_name=20
六、联合索引没有使用第一列
在测试表中，我们针对字段和创建了联合索引，如果查询语句只匹配的数据，那么索引也是无效的。
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
九、总结
上面罗列了一些常见的不走索引的情况：
除了以上这几种情况索引失效的几种情况，还有一些场景也会导致索引失效，比如：!=、或者not in可能会导致索引失效；在索引字段使用is null或者is not null也可能不走索引；范围查询有可能不走索引；优化器认为全表扫描比使用索引更快，那也不走索引；使用MySQL的hint，也就是 INDEX强制不使用索引，等等。
笔者水平有限，虽然竭尽所能想要穷尽所有情况，但也难免存在漏网之鱼，那么，还有其他导致索引失效的情况吗？欢迎讨论。
好啦，今天的内容分享就到这，感觉不错的同学记得分享点赞哦！
PS：工作日早8:30，CSDN 企业招聘持续分享程序员学习、面试相关干货，不见不散！
点分享 点收藏 点点赞 点在看 声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。