DBA 说"加个索引就行",索引加上了,订单查询接口却纹丝不动,P99 还是 3-5 秒。用 EXPLAIN 逐条分析才发现 SQL 里藏着一堆让索引"悄悄失效"的写法。几天索引专项治理:读懂 EXPLAIN 执行计划、修正函数/隐式转换/前导模糊等失效写法、联合索引最左前缀、回表与覆盖索引、索引设计原则、慢查询监控。
2024 年我们一个订单查询接口很慢,DBA 看了一眼说"加个索引就行"。索引加上了,接口却一点没变快。我们盯着那条 SQL 看了半天,字段明明有索引,为什么不走?后来用 EXPLAIN 一条条分析才发现,SQL 里藏着好几处让索引"悄悄失效"的写法。投了几天做索引专项治理,把一批慢查询接口的 SQL 全部过了一遍,本文复盘这次实战。
问题背景
业务:订单中心,MySQL 5.7,orders 表约 2000 万行
事故现象:
- 多个订单查询接口慢,P99 到 3-5 秒
- DBA 说"加索引",索引加了,接口纹丝不动
- 数据库慢查询日志里堆了大量同款 SQL
现场排查:
# 给慢 SQL 加 EXPLAIN,看执行计划
mysql> EXPLAIN SELECT * FROM orders
WHERE DATE(create_time) = '2024-05-20';
+----+-------+------+---------------+------+----------+-------------+
| id | type | key | rows | Extra |
+----+-------+------+---------------+------+----------+-------------+
| 1 | ALL | NULL | 19,800,000 | Using where |
+----+-------+------+---------------+------+----------+-------------+
# type=ALL -> 全表扫描!key=NULL -> 没用任何索引
# create_time 上明明建了索引,却没走
# 再看另一条:
mysql> EXPLAIN SELECT * FROM orders WHERE order_no = 660123456789;
# order_no 是 varchar,建了索引,但这里传的是数字
# type=ALL,又是全表扫描
根因:
1. DATE(create_time) —— 对索引列用了函数,索引直接失效
2. order_no 是 varchar 却传数字 —— 隐式类型转换,索引失效
3. 还有联合索引没遵守最左前缀、前导 % 模糊查询等多种写法
4. 团队普遍以为"建了索引 = 一定会走索引",这是最大的误区修复 1:先学会读 EXPLAIN 执行计划
-- 排查索引问题,EXPLAIN 是唯一可靠的依据。重点看这几列:
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;
-- === type:访问类型,性能从好到差 ===
-- system > const > eq_ref > ref > range > index > ALL
-- const : 主键/唯一索引等值查询,最快
-- ref : 普通索引等值查询,常见且不错
-- range : 索引范围扫描(between、>、IN),可接受
-- index : 扫描整个索引树,比 ALL 略好但仍要警惕
-- ALL : 全表扫描 —— 看到 ALL 基本就是没走索引,要优化
-- === key:实际使用的索引,NULL 表示没用索引 ===
-- === key_len:用到的索引长度,联合索引能看出"用到了几个字段" ===
-- === rows:预估要扫描的行数,越小越好 ===
-- === Extra:最关键的补充信息 ===
-- Using index : 覆盖索引,不用回表,很好
-- Using where : 存储引擎返回后还要再过滤
-- Using filesort : 排序没走索引,要在内存/磁盘额外排序,需优化
-- Using temporary : 用了临时表(常见于 group by),需优化
-- === possible_keys vs key ===
-- possible_keys 有索引,但 key 是 NULL ——
-- 说明"本来可以用索引,但优化器最终没用",
-- 要么是 SQL 写法让索引失效,要么是优化器认为全表更快。修复 2:索引失效的常见写法
-- === 失效 1:在索引列上用函数 / 表达式 ===
-- 错:对 create_time 套了 DATE() 函数,索引失效
SELECT * FROM orders WHERE DATE(create_time) = '2024-05-20';
-- 对:改成范围查询,让 create_time 保持"裸列"
SELECT * FROM orders
WHERE create_time >= '2024-05-20 00:00:00'
AND create_time < '2024-05-21 00:00:00';
-- 原理:索引存的是列的【原始值】,一旦套函数,
-- 优化器无法用列的原始值去索引树里二分查找。
-- === 失效 2:隐式类型转换 ===
-- order_no 是 varchar,传数字会触发隐式转换,索引失效
SELECT * FROM orders WHERE order_no = 660123456789; -- 错
SELECT * FROM orders WHERE order_no = '660123456789'; -- 对,传字符串
-- 隐式转换等价于对列做了 CAST 函数,和失效 1 同理。
-- === 失效 3:前导 % 的模糊查询 ===
SELECT * FROM orders WHERE order_no LIKE '%12345'; -- 错,前导 %
SELECT * FROM orders WHERE order_no LIKE '12345%'; -- 对,后置 %
-- 原理:B+ 树索引按值的前缀排序,'12345%' 能定位前缀,
-- '%12345' 不知道开头是什么,只能全扫。
-- 真要前导模糊,考虑全文索引或 ES。
-- === 失效 4:OR 连接的条件里有列没索引 ===
-- user_id 有索引,remark 没有 —— 整条 OR 都走不了索引
SELECT * FROM orders WHERE user_id = 1001 OR remark = 'x';
-- 对:给 remark 也建索引,或用 UNION 拆开
SELECT * FROM orders WHERE user_id = 1001
UNION
SELECT * FROM orders WHERE remark = 'x';
-- === 失效 5:对索引列做运算 ===
SELECT * FROM orders WHERE amount + 10 > 100; -- 错
SELECT * FROM orders WHERE amount > 90; -- 对,把运算挪到右边
-- === 失效 6:!= 、NOT IN、IS NOT NULL 常常用不上索引 ===
-- 这类"否定"条件,匹配的行往往很多,优化器倾向于全表扫,
-- 设计查询时尽量用"正向、能缩小范围"的条件。修复 3:联合索引与最左前缀原则
-- === 假设建了联合索引 idx(user_id, status, create_time) ===
ALTER TABLE orders ADD INDEX idx_u_s_c (user_id, status, create_time);
-- 联合索引像电话簿:先按 user_id 排,user_id 相同再按 status 排,
-- status 也相同再按 create_time 排。
-- === 最左前缀原则:必须从最左列开始连续使用 ===
-- 能用上索引:
WHERE user_id = 1 -- 用到 1 列
WHERE user_id = 1 AND status = 2 -- 用到 2 列
WHERE user_id = 1 AND status = 2 AND create_time > '...' -- 用到 3 列
-- 用不上 / 用不全索引:
WHERE status = 2 -- ✗ 跳过了最左的 user_id,失效
WHERE user_id = 1 AND create_time > '...' -- △ 只能用到 user_id 这 1 列
-- (中间断了 status)
-- === 范围查询会"中断"后续列 ===
WHERE user_id = 1 AND status > 2 AND create_time = '...'
-- status 用了范围(>),create_time 就用不上索引了。
-- 范围列之后的列,无法再走索引 —— 所以:
-- 联合索引设计时,把【等值查询】的列放左边,
-- 【范围查询】的列放右边。
-- === 联合索引列的顺序很关键,要按"区分度 + 使用频率"排 ===
-- 区分度高的(值很分散,如 user_id)放前面,
-- 几乎总会出现在 WHERE 里的列放前面。
-- 一个设计良好的联合索引,能顶替好几个单列索引。修复 4:回表与覆盖索引
-- === 什么是回表 ===
-- 普通(二级)索引的叶子节点存的是【主键值】,不是整行数据。
-- 走二级索引查到主键后,还要拿主键回【主键索引】里捞整行 ——
-- 这个二次查找就是"回表",它是额外开销。
-- 这条 SQL:idx(user_id) 找到主键,再回表取 * 的所有字段
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;
-- Extra: Using where(走了 idx,但要回表取完整行)
-- === 覆盖索引:索引本身就包含了要查的所有列,不用回表 ===
-- 如果只需要 user_id 和 status,而索引正好是 idx(user_id, status):
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 1001;
-- Extra: Using index <- 覆盖索引!所有字段索引里都有,免回表
-- === 实战优化:别习惯性 SELECT * ===
-- 错:SELECT * 把不需要的字段也查出来,几乎必然回表
SELECT * FROM orders WHERE user_id = 1001 AND status = 1;
-- 对:只查真正需要的列,若索引能覆盖就免回表
SELECT order_no, amount FROM orders
WHERE user_id = 1001 AND status = 1;
-- 配合联合索引 idx(user_id, status, order_no, amount),
-- 这条查询完全走覆盖索引,性能能再上一个台阶。
-- === 注意:覆盖索引不是让你把所有列都塞进索引 ===
-- 索引列越多,索引本身越大、写入越慢。
-- 针对高频核心查询,精准地建覆盖索引才划算。修复 5:索引设计与使用原则
-- === 原则 1:在区分度高的列上建索引 ===
-- 区分度 = 不同值的数量 / 总行数,越接近 1 越好
SELECT COUNT(DISTINCT status) / COUNT(*) FROM orders; -- 0.0000002,极低
SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM orders; -- 0.3,还不错
-- status 只有几个值,单独给它建索引几乎没用 ——
-- 走索引查出一半的行,优化器还不如直接全表扫。
-- 性别、状态、是否删除这类低区分度列,单列索引基本是浪费。
-- === 原则 2:索引不是越多越好 ===
-- 每个索引都要占空间,且每次 INSERT/UPDATE/DELETE 都要
-- 同步维护所有相关索引 —— 索引过多会拖慢写入。
-- 经验:单表索引控制在 5 个左右,优先用联合索引替代多个单列索引。
-- === 原则 3:用 force index 是最后手段,先查为什么不走 ===
-- 优化器偶尔会"误判",此时可以强制:
SELECT * FROM orders FORCE INDEX (idx_u_s_c) WHERE user_id = 1001;
-- 但 99% 的"不走索引"是 SQL 写法问题,不是优化器的错,
-- force index 治标不治本,先老老实实 EXPLAIN 找根因。
-- === 原则 4:及时清理无用索引 ===
-- 通过 sys.schema_unused_indexes 找出从来没被用过的索引
SELECT * FROM sys.schema_unused_indexes;
-- 无用索引只增加写入负担和存储,该删就删。
-- === 原则 5:大表加索引要注意 ===
-- 2000 万行的表直接 ALTER ADD INDEX 可能锁表很久,
-- 用 pt-online-schema-change 或 gh-ost 这类在线 DDL 工具。修复 6:慢查询监控
# 索引问题的外在表现就是慢查询,要持续监控
groups:
- name: mysql-slow-query
rules:
# 1. 慢查询数量突增
- alert: SlowQuerySurge
expr: rate(mysql_global_status_slow_queries[5m]) > 1
for: 5m
annotations:
summary: "MySQL 慢查询增多,排查是否有 SQL 未走索引"
# 2. 全表扫描行数过多(Handler_read_rnd_next 高 = 大量全表扫)
- alert: FullTableScanHigh
expr: rate(mysql_global_status_handlers_total{handler="read_rnd_next"}[5m]) > 100000
for: 10m
annotations:
summary: "全表扫描行数过高,排查缺失索引或索引失效"
# 3. 接口 RT 高(索引失效的业务侧表现)
- alert: QueryApiSlow
expr: http_server_requests_seconds{uri=~"/order/.*",quantile="0.99"} > 2
for: 5m
annotations:
summary: "{{ $labels.uri }} 查询接口 P99 > 2s,排查 SQL 执行计划"-- 开启慢查询日志,把慢 SQL 都记下来
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒记入慢日志
SET GLOBAL log_queries_not_using_indexes = ON; -- 没走索引的也记
-- 再用 pt-query-digest 分析慢日志,按耗时排序,逐条 EXPLAIN 优化优化效果
指标 治理前 治理后
=============================================================
订单查询接口 P99 3-5 秒 80-200ms
慢 SQL 执行计划 type=ALL 全表扫描 type=ref/range 走索引
日期查询 DATE() 套函数失效 改区间,走索引
order_no 查询 传数字隐式转换失效 传字符串,走索引
联合索引利用 常违反最左前缀 按最左前缀正确使用
SELECT * 习惯性全字段 按需取列 + 覆盖索引
低区分度无用索引 一批冗余索引 清理,写入更快
慢查询监控 无 慢日志 + 三类告警
治理过程:
- EXPLAIN 逐条分析慢 SQL(43 条):2 天
- 修正索引失效写法(函数/类型/模糊/OR):2 天
- 联合索引重新设计 + 覆盖索引优化:2 天
- 清理无用索引 + 在线加索引:1 天
- 慢查询监控接入:0.5 天避坑清单
- "建了索引"不等于"会走索引",一切以 EXPLAIN 执行计划为准
- EXPLAIN 重点看 type(出现 ALL 就是全表扫)、key(NULL 即没用索引)、Extra
- 在索引列上套函数、做运算会让索引失效,改成范围查询保持列裸值
- 隐式类型转换等于对列套了 CAST,varchar 字段必须传字符串
- 前导 % 的 LIKE 用不上索引,后置 % 才行,前导模糊考虑全文索引或 ES
- OR 条件里只要有一个列没索引,整条都可能走不了索引
- 联合索引必须遵守最左前缀,跳过最左列或中间断列都会失效
- 范围查询会中断联合索引后续列,等值列放左、范围列放右
- 避免 SELECT *,只取需要的列,让查询尽量走覆盖索引免回表
- 低区分度列(状态/性别)单独建索引几乎没用,索引也不是越多越好
总结
这次索引治理,起点是一个让人有点意外的现象:DBA 说"加个索引就行",索引老老实实加上了,接口却快不起来一丝一毫。这个反差暴露了我们整个团队一个根深蒂固的误区——以为"在某个字段上建了索引"和"查询这个字段时会用上索引"是同一件事。其实这两者之间隔着一道关键的关卡,那就是 MySQL 的查询优化器:索引只是一种"可能性",优化器在执行每条 SQL 时,会根据 SQL 的具体写法、统计信息来决定到底用不用这个索引,而我们写 SQL 时一个不经意的习惯,就足以让这个"可能性"变成"不可能"。所以这次复盘最核心的一条经验是:判断一条查询有没有走索引,唯一可靠的依据是 EXPLAIN 出来的执行计划,而不是"我建过索引所以它一定走"这种想当然。学会读 EXPLAIN——尤其是 type 这一列出现 ALL 就意味着全表扫描、key 这一列是 NULL 就意味着一个索引都没用上——是每个写 SQL 的人都该具备的基本功。在搞清楚"怎么看"之后,接下来就是认识那些会让索引"悄悄失效"的常见写法,它们大多隐蔽得很：在索引列外面套一个 DATE() 之类的函数、把一个 varchar 字段拿来和数字比较从而触发隐式类型转换、用前导百分号去做模糊匹配、在 OR 里混入一个没有索引的列——这些写法语法上都完全合法,SQL 跑起来结果也对,只是它们都让优化器没法再用索引列的原始有序值去 B+ 树里做高效查找,于是默默退化成了全表扫描。还有联合索引,它遵循最左前缀原则,像一本先按姓、再按名排序的电话簿,你必须从最左边的列开始连续地用,跳过最左列、或者中间断一列,索引就用不全甚至用不上;而范围查询会中断它后面的列,所以设计联合索引时要把等值查询的列放在左边、范围查询的列放在右边。最后还有回表和覆盖索引的取舍——一个习惯性的 SELECT * 往往就意味着一次回表,如果能只查需要的几个列、并让索引正好覆盖这些列,查询连回表都省了。说到底,索引是数据库给我们的一件强大工具,但它不是"建了就万事大吉"的银弹,真正决定查询快慢的,是我们有没有用优化器看得懂、用得上的方式去写每一条 SQL。把 EXPLAIN 当成写查询时的随身体检仪,让每一条上线的核心 SQL 都经过它的检验,这才是索引能真正发挥威力的前提。
—— 别看了 · 2026声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理 邮箱1846861578@qq.com。
