本文介绍了MySQL中索引失效的常见原因，如不满足最左匹配原则、范围查询过大、索引列参与运算或使用函数、错误的LIKE使用、隐式类型转换、OR条件、非主键列的NOT操作和ORDERBY可能导致的失效。通过Explain命令分析查询计划，可以识别这些问题并采取相应优化措施，如调整索引、避免全表扫描和不必要的运算。
一、前言
在日常开发中，我们往往会给表加各种索引，来提高 MySQL 的检索效率。
但我们有时会遇到明明给字段加了索引，并没有走索引的Case。 进而导致 MySQL 产生慢查询。
严重场景下，甚至出现主从延迟、数据库拖垮的极端事故。
本文梳理出索引失效的几种常见场景给大家参考。
二、技术基础
Explain 命令使用
只要我们在 SQL 前加上 explain，就可以分析出，当前环境下 MySQL 的“查询方式”以及“索引选择”。
首先大致看下每个字段的含义：
| 列名 | 含义 |
|---|---|
| id | 每个select操作的唯一标识 |
| select_type | 查询的类型，我们可以根据该字段判断查询的性质，包括查询是简单/复杂查询类型 |
| table | 查询访问表的别名 |
| type | 关联的类型，mysql把查询过程都视为关联，不管是单表/多表。这个字段也是衡量查询性能的关键字段之一 |
| possible_keys | 查询可能会使用哪些索引，这列是基于查询访问的列来判断的 |
| key | mysql最终决定使用哪个索引（这个索引不一定出现在possible_keys中） |
| key_len | mysql在索引里使用的字节数，我们可以根据它推断具体使用了索引中的哪些字段 |
| ref | 查找所用的列/常量 |
| rows | mysql估算的预计扫描行数，这个数字和实际扫描的行数可能相差甚远，包括limit语句对于这个估算值也是不起作用的 |
| filtered | 表里符合条件的记录数的百分比的估计，我们可以用这个字段大致估计表关联时关联的记录数 |
| extra | 包含一些额外信息，也是我们优化时需要重点关注的字段 |
Type（重点看）
type 列表示了 MySQL 关联的类型，它代表了mysql是如何在表里找数据的。
下面按性能从高到低的顺序介绍type类型： 以下四种类型，说明 “性能很好，一般无需优化” ：
system：表里就一条数据const：一般是针对主键/唯一键的等值查询，mysql可以把这类查询优化为一个常量表达式eq_ref：一般出现在多表join时，针对主键/唯一键的等值查询，mysql知道只需要返回一条记录ref：多表 join 时，针对索引字段的查询
以下几种类型，需要 “看具体情况，决定是否要优化” ：
fulltext：关联使用了全文索引ref_or_null：查询走了索引，但是除此之外还要判断字段是不是null，如果出现这种类型，可以考虑这个字段是否有为空的必要index_merge：使用了索引合并优化，如果高频出现，可以考虑是不是索引设计有问题。unique_subquery：in 子句中的子查询，如果只访问主键/唯一键可能会出现这种 type，并不常见index_subquery：同样是 in 里的子查询，访问了索引列，并不常见range：对索引字段的范围扫描，一般出现在带有比较的查询语句中，一些in和or的查询也会导致这种类型的扫描
以下两种类型，需要 “优化 & 避免出现” ：
index：按索引进行全表扫描，如果查询不是覆盖索引的，可能会产生很大量的随机IOall：全表扫描
三、准备工作
- 建一张
user表
-
CREATE
TABLE `user` (
-
`id` bigint(
20) unsigned
NOT
NULL AUTO_INCREMENT COMMENT
'主键',
-
`user_id` bigint(
20)
NOT
NULL COMMENT
'用户uuid',
-
`user_name` varchar(
64)
DEFAULT
'' COMMENT
'用户昵称',
-
`email` varchar(
64)
DEFAULT
'' COMMENT
'邮箱',
-
`age` tinyint(
4)
DEFAULT
'1' COMMENT
'年龄',
-
`create_
time` datetime
NOT
NULL
DEFAULT CURRENT_TIMESTAMP COMMENT
'创建日期',
-
`update_
time` datetime
NOT
NULL
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP COMMENT
'更新日期',
-
PRIMARY
KEY (`id`),
-
UNIQUE
KEY `uk_userid` (`user_id`),
-
KEY `idx_username_email_age` (`user_name`,`email`,`age`)
-
) ENGINE
=InnoDB
DEFAULT CHARSET
=utf
8mb
4 COMMENT
=
'用户表';
-
复制代码
- 初始化一些数据
-
-- 创建存储过程
-
delimiter $
-
-
CREATE
PROCEDURE insert_user(
IN
limit_num int)
-
BEGIN
-
DECLARE i INT
DEFAULT
10;
-
DECLARE user_id bigint(
20) ;
-
DECLARE username varchar(
64) ;
-
DECLARE email varchar(
64) ;
-
DECLARE age TINYINT(
4)
DEFAULT
1;
-
WHILE i
<
limit_num DO
-
SET user_id
= FLOOR(RAND()
*
100000000);
-
SET username
= CONCAT(
"647-",i);
-
SET email
= CONCAT(username,
"@163.com");
-
SET age
= FLOOR(RAND()
*
100);
-
INSERT
INTO `user`
VALUES (
NULL, user_id, username, email, age, NOW(), NOW());
-
SET i
= i
+
1;
-
END WHILE;
-
-
END $
-
-- 调用存储过程
-
call insert_user(
100);
-
复制代码
四、几种常见的索引失效场景
1. 联合索引不满足最左匹配原则
- 错误示例：
-
explain
select
*
from user where age
=
20
and email
=
"647@163.com";
-
复制代码
- 分析结果：
- 优化思路：
根据业务场景，合理的建立相应的联合索引。
2. 范围查询，数量级过大，默认走全表扫描
一般来说，MySQL 判断数量级返回超过全数的 10% ~ 30%（或者达到某个阈值），默认会走全表扫描。
- 错误示例：
-
explain
select
*
from user where user_id
>
10;
-
复制代码
- 分析结果：
-
产生原因：MySQL 优化器判断走索引&回表带来的消耗，比走全表还要多。因此，会走全表扫描。
-
优化思路：
根据业务场景，预估返回数量级。如果数量级过大，可以分批拉取。
反之，可以加limit或者force index走索引。
3. 索引列参与运算
- 错误示例：
-
explain
select
*
from user where id
+
1
=
2;
-
复制代码
- 分析结果：
- 优化思路：
不要用数据库做运算，不浪费宝贵的数据库资源。
4. 索引列使用了函数
- 错误示例：
-
explain
select
*
from user where SUBSTR(user_id,
1,3)
=
'100';
-
复制代码
- 分析结果：
- 优化思路：
不要用数据库做函数运算，不浪费宝贵的数据库资源。
5. 错误的 like 使用
- 错误示例：
-
explain
select
*
from
user
where user_name
like
'%00%';
-
复制代码
- 分析结果：
- 优化思路：
严禁使用左%匹配，要用只能用右%匹配。
如果实在有业务场景，可以使用ES做。
6. 隐式类型转换
- 错误示例：
-
explain
select
*
from user where user_name
=
647;
-
复制代码
- 分析结果：
user_name 是 varchar 类型，传入 INT 比较，会产生 INT -> varchar 的隐式类型转换导致索引失效。
- 特殊 Case：
-
explain
select
*
from user where user_id
=
"647";
-
复制代码
- 分析结果：
user_id 是 bigint 类型，如果传入字符串比较。虽然产生隐式转换，但不会导致索引失效。
- 优化思路：
注意字段类型，避免隐式转换。
7. OR 使用不当
- 错误示例：
-
explain
select
*
from user where user_name
=
"647"
or email
=
"647@163.com";
-
复制代码
- 分析结果：
- 优化思路：
确保
or的两边都要有索引。
8. 两个索引列做比较
- 错误示例：
-
explain
select
*
from user where user_id
> id;
-
复制代码
- 分析结果：
- 优化思路：
不要对两个列做比较。
9. 非主键列，加上 not，索引失效
in 会走索引，not in 不会走索引 exists 会走索引，not exists 不会走索引 is null 会走索引，is not null 不会走索引
- 错误示例：
-
explain
select
*
from user where user_id
not
in (
647)
-
复制代码
- 分析结果：
- 优化思路：
不要用 not。
10. 非主键列，order by 可能导致索引失效
具体是否失效，和使用的 MySQL 版本也有一定关系。 具体需要根据 explain 分析。
如果 MySQL 版本支持，需要注意满足“最左原则”。
- 错误示例：
-
explain
select
*
from user
order
by user_id;
-
复制代码
- 分析结果：
- 优化思路：
非主键列，尽量不要用 order by。实在要用，需要先用 explain 分析是否可以走索引。
如果条件允许，可以用 ES 代替。
扫一扫
1501
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
