使用索引翻车了，差点被辞退
共 3866字，需浏览 8分钟
·
2021-11-18 11:03
前一阵子生产环境出现了接口响应慢的问题，最终定位是由于一条慢 SQL 导致的。分析了一下这条慢 SQL，发现对应数据库表也加了索引，最终结论是某些场景下索引失效了。
索引失效导致全表扫描，执行速度非常慢，导致大量的 SQL 处于阻塞状态。
领导：你好好反省一下，为啥出这么低级的问题。
我：好的，今晚通宵整一篇总结出来，年底不让我扛绩效了吧？！
领导：……看你这篇文章点赞能不能超过 100
我：……没戏，他们喜欢白嫖
下面总结一下使用索引翻车的常见名场面，希望能帮助到大家，拿个好年终奖！
为方便演示，我们提前先建立一张数据库表。新建一个用户表，id 为主键，user_id 为唯一索引，name 为普通索引，address 为普通索引：
CREATE TABLE `t_user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`user_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_user_id` (`user_id`) USING BTREE,
KEY `index_name` (`name`) USING BTREE,
KEY `index_address` (`address`) USING BTREE
) ENGINE=InnoDB;
查询条件包含or，可能导致索引失效
（1）执行一条简单的 SQL
EXPLAIN
SELECT * FROM t_user WHERE user_id = 123456;
查看执行计划的结果，很显然走了索引：
（2）加一个 or 条件，把 age 字段加上
EXPLAIN
SELECT * FROM t_user WHERE user_id = 123456 or age = 18;
查看执行计划的结果，很显然是全表扫描：
分析结论：
对于条件中出现 or 的情况，user_id 列加了索引，age 列是没有加索引的，假设 MySQL 一定要走索引，可能需要三步：索引扫描+全表扫描+合并； 如果它一开始就走全表扫描，直接一遍扫描就完事了； mysql是有优化器的，处于效率与成本，遇到 or条件，索引 可能失效，看起来也合情合理。
敲黑板: 上面说的这种情况索引有可能失效，没有说一定会失效；如果 or 条件的列都加了索引，索引可能会走的，大家可以自己试一试。
like通配符可能导致索引失效
在业务中我们喜欢用模糊搜索的方式去做查询，举个例子，我们想搜索名称为 leixiaoshuai 小伙子：
EXPLAIN
SELECT * FROM t_user WHERE name = '%leixiaoshuai%';
查看执行计划的结果，发现是全表扫描，说明索引失效了：
我们试着把前面的%去掉，再查一遍：
EXPLAIN
SELECT * FROM t_user WHERE name = 'leixiaoshuai%';
执行计划告诉我们正常走索引了：
我们再把%加回来，这次不查询所有的字段，只查询 id 和 name：
EXPLAIN
SELECT id, name FROM t_user WHERE name = '%leixiaoshuai%';
你会惊奇的发现居然又走索引了：
惊不惊喜意不意外，这是因为覆盖索引的原因。
分析结论：
like查询以%开头，会导致索引失效。可以有两种方式优化：
使用覆盖索引 把%放后面
敲黑板 索引包含所有满足查询需要的数据的索引，称为覆盖索引(Covering Index)。
字符串列使用 where 一定用引号括起来，否则索引失效
从上面的建表语句中我们知道 name 列是 varchar 可变字符类型，如果我们在查询时忘了加引号会发生呢？
我们做一个简单的查询，此时 name 列没有加引号：
EXPLAIN
SELECT * FROM t_user WHERE name = 123;
name 列是建了普通索引的，理论上是应该走索引的，实际上是全表扫描：
惊不惊喜意不意外，吓得我赶紧把引号加起来了：
EXPLAIN
SELECT * FROM t_user WHERE name = '123';
这次果然乖乖走索引了：
分析结论：如果某一列是字符类型，但是使用 where 不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。
在索引列上使用mysql的内置函数，索引失效
name 列已经加了普通索引，但是在查询的时候给加了内置函数，作用是将 name 列变成全大写：
EXPLAIN
SELECT * FROM t_user WHERE UPPER(name) = 'LEIXIAOSHUAI';
索引直接 GG 了，套了一层壳子，我就不认识你了，我直接全表扫描：
索引字段上使用is null或is not null，可能导致索引失效
根据前面的建表语句我们知道 name 列，address 列都加了普通索引。
（1）查找 name 不为空的所有列
EXPLAIN
SELECT * FROM t_user WHERE name is not null;
意料之中，正常走了索引：
（2）查找 address 不为空的所有列
EXPLAIN
SELECT * FROM t_user WHERE address is not null;
意料之中，正常走了索引：
（3）查找 name 不为空或者 address 不为空
EXPLAIN
SELECT * FROM t_user WHERE name is not null or address is not null;
咦？？？索引好像失效了：
对索引列运算索引失效
对索引列进行四则运算（如，+、-、*、/），索引会失效。
user_id 是索引列，在查询的时候进行+1
EXPLAIN
SELECT * FROM t_user WHERE user_id+1= 456789;
索引直接迷路了：
联合索引ABC问题导致索引失效
我们重新建一张表，id 是主键，name 和 age 列加了一个联合索引。
CREATE TABLE `t_user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`user_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name_age` (`name`,`age`) USING BTREE
) ENGINE=InnoDB;
特别要注意一下联合索引的顺序，name 在前，age 在后。
（1）where 条件同时满足 name 和 age
EXPLAIN
SELECT * FROM t_user WHERE name = 'leixiaoshuai' AND age = 18;
毫无疑问肯定是走了索引：
（2）where 条件只有 name 列，这个能否走索引呢？
EXPLAIN
SELECT * FROM t_user WHERE name = 'leixiaoshuai';
看结果，依然是走了联合索引，开心~
（3）where 条件只有 age 列，这个应该也可以走索引吧？！
EXPLAIN
SELECT * FROM t_user WHERE age = 18;
完蛋，翻车了，没走索引……
分析结论：
当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是 最左匹配原则。联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。
总结：
以上就是使用索引时最常见的 7 种翻车场景，大家有没有中过招？
最后，我向领导也坦白了，我是因为联合索引 ABC 问题中招的，实在是草率了……
-- End --
这些文章你可得看：
👍🏻『死磕Java并发编程系列』 01 十张图告诉你多线程那些破事
『死磕Java并发编程系列』 02 面试官：说说什么是Java内存模型？
『死磕Java并发编程系列』 03 面试必问的CAS原理你会了吗？
作者简介：
博主从华中科技大学硕士毕业，是一个对技术有追求，对生活有激情的程序员。
几年间浪迹于多个一线互联网大厂，具有多年开发实战经验。
如果你热爱技术或者你也不满足现状喜欢搞事情，那你不妨关注我，让我们一路同行，一起分享技术干货、交流面试技巧，吐槽职场故事。
我有技术和故事，你来吗？
我该不会被辞退了吧