数据库服务器MySQL索引失效如何从执行计划定位原因并修复
MySQL查询突然变慢、CPU和磁盘负载上升,并不等于“索引失效”或索引文件已经损坏。多数情况下,真正发生的是优化器没有选择预期索引、索引只能过滤少量数据,或者SQL在到达MySQL前已经在线程池、连接池或网络链路上等待。定位时应先区分“索引未被使用”和“查询整体耗时增加”,再结合执行计划、实际扫描行数及服务器资源判断根因。

最有效的排查路径是:先确认慢在哪里,再按网络、系统、MySQL服务、应用SQL四个层级建立原因树,随后用执行计划验证候选原因。只有确认是谓词写法、联合索引顺序、统计信息或数据分布导致计划不合理后,才进行SQL改写、统计信息更新或索引调整,不能仅凭 type=ALL 就直接创建索引或强制走索引。
先确认“索引失效”到底表现在哪里
区分三类不同问题
生产环境中常见的“索引失效”至少包括三种情况:
- 执行计划没有使用索引:
EXPLAIN中key为NULL,或访问类型为ALL。 - 使用了索引,但扫描行数仍然很多:
key不为NULL,但rows很大,或者实际扫描行数远高于返回行数。 - SQL本身不慢,但请求整体变慢:应用等待连接、等待锁、等待网络响应,数据库执行时间并不长。
例如,下面是一个用于说明的执行计划片段,并不代表某个实际环境的结果:
id select_type table type possible_keys key rows filtered Extra
1 SIMPLE o ALL idx_orders_user_status_created NULL 860000 4.00 Using where; Using filesort
这个结果只能说明优化器没有选择 idx_orders_user_status_created,不能直接证明索引损坏。还需要回答几个问题:
possible_keys中是否出现了预期索引?key为NULL,是因为谓词无法使用索引,还是因为全表扫描成本更低?rows是估算值还是实际扫描行数?Using filesort是内存排序还是已经产生磁盘临时文件?- 应用报告的耗时是否包含连接池排队和网络传输时间?
先采集基线
排查前应记录同一条SQL在相同参数下的以下信息:
- 完整SQL及参数类型;
- 应用端总耗时;
- 从连接池获取连接所需时间;
- MySQL执行时间;
- 返回行数和应用实际处理时间;
- 执行计划;
- 查询发生时的CPU、磁盘、内存、锁等待和连接数。
如果只能看到一条脱敏后的SQL模板,而不知道实际参数,结论可能不可靠。相同SQL模板传入不同参数时,命中行数可能相差数百倍,优化器也可能选择不同计划。
MySQL慢查询日志或 performance_schema 可以用于定位高耗时语句。启用慢日志、缩短 long_query_time 或扩大采样范围前,应确认磁盘空间、日志轮转和额外开销,生产环境宜先在低流量时段进行,并设置恢复原配置的方案。
建立四层原因树
网络层:确认慢在数据库外部还是数据库内部
网络问题通常不会让MySQL改变索引选择,但会让应用感知到的查询延迟变长,容易被误判为索引问题。重点观察:
- 应用服务器到数据库服务器的往返延迟;
- TCP连接是否频繁建立和断开;
- 是否有丢包、重传或网卡错误;
- 查询结果是否较大,网络传输时间是否占比明显;
- 数据库连接是否跨地域、跨可用区或经过多级负载均衡。
可以在维护窗口使用以下命令观察连接和基础网络状态:
ss -tnp | grep ':3306'
ping -c 5 <数据库主机名或地址>
ip -s link
这些命令只能帮助判断链路状态,不能证明索引是否生效。如果应用端显示总耗时10秒,而MySQL慢日志中的执行时间只有几十毫秒,应优先检查连接池排队、网络传输和应用处理,而不是立即修改索引。
系统层:确认数据库是否被资源瓶颈拖慢
系统资源不足会放大任何查询的耗时,也可能使原本可接受的全表扫描变成严重故障。常见指标包括:
vmstat 1 5
iostat -xz 1 5
free -h
df -h
观察重点如下:
| 指标 | 观察依据 | 可能含义 |
|---|---|---|
| CPU user、system | 是否持续接近主机处理能力 | 大量扫描、排序、函数计算或并发过高 |
%iowait | 是否在查询高峰同步升高 | 数据文件、临时表或日志读写受限 |
| 内存与swap | 是否出现持续换入换出 | 缓存不足,查询延迟抖动 |
| 磁盘使用率 | util、await是否明显升高 | 存储队列积压 |
| 文件系统空间 | 数据目录、临时目录是否接近满 | 临时表、日志或DDL可能失败 |
这里的参考阈值只能作为排查起点。例如,CPU持续超过80%至90%、磁盘等待时间持续升高、出现明显swap,都值得进一步确认,但不能把某个固定百分比当成所有服务器的故障标准。低配实例、突发型云盘和高并发数据库的正常基线可能不同。
MySQL服务层:确认是执行、等待还是资源竞争
先查看当前连接和正在执行的线程:
SHOW FULL PROCESSLIST;
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW ENGINE INNODB STATUS\G
重点判断:
Threads_connected很高,但Threads_running不高:可能是连接池保留连接较多,不一定是执行压力。Threads_running持续升高:可能存在慢SQL、锁等待或并发失控。Created_tmp_disk_tables在高峰期明显增加:排序、分组或连接操作可能超出内存,产生磁盘临时表。Innodb_buffer_pool_reads增长很快:数据页未命中缓冲池,查询可能受到存储性能影响。SHOW ENGINE INNODB STATUS中出现大量等待:需要继续区分行锁、事务、IO或死锁。
缓冲池命中率可以用一段时间内的计数器增量估算:
命中率 ≈ 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
应使用两个时间点的差值计算,而不是直接用服务器启动以来的累计值。比如某个时间窗口内逻辑读为1亿次,物理读为50万次,估算命中率约为99.5%;这仍不能单独证明某条SQL一定高效,因为热点表命中和冷数据读取可能同时存在。
锁等待需要与索引问题分开。查询即使使用了正确索引,也可能因为另一个事务长时间未提交而等待。MySQL 8.0环境可结合 performance_schema 或 sys 库中的锁等待视图继续定位;如果环境没有对应视图,应以 SHOW ENGINE INNODB STATUS 和线程信息为准,不要直接套用不兼容版本的查询。
应用层:确认SQL、参数和连接池行为
应用层经常是索引问题的真正入口,重点核对:
- 查询条件中的参数类型是否与字段类型一致;
- 是否在字段上调用了函数、转换或表达式;
- 是否使用了前置通配符;
- 是否传入空值、默认值或范围过大的分页参数;
- ORM是否自动添加了排序、关联、软删除条件;
- 连接池是否在数据库执行前已经排队;
- 是否在一个事务中执行了大量无关操作;
- 缓存失效后是否出现瞬时查询洪峰。
如果应用采用预编译语句,还要记录具有代表性的参数组合。不能只拿一组返回少量数据的参数验证索引,然后把结论推广到所有请求。
用执行计划逐层排除
先看基础字段,不要只看 key
对目标查询执行:
EXPLAIN
SELECT id, status, created_at
FROM orders
WHERE user_id = 10086
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
示例中的表名、字段和参数仅用于说明。重点字段如下:
| 字段 | 判断方法 |
|---|---|
possible_keys | 优化器认为理论上可能使用的索引 |
key | 实际选择的索引,NULL表示没有选择索引 |
type | const、ref、range通常比ALL更值得关注,但不是绝对快慢标准 |
key_len | 实际使用了联合索引的多少字节,可辅助判断使用到哪一列 |
ref | 索引列与常量、字段或表达式的关联方式 |
rows | 优化器估算需要检查的行数 |
filtered | 经过条件过滤后预计保留的比例 |
Extra | 是否有回表、临时表、排序或其他额外操作 |
type=index表示全索引扫描,并不等于高效;如果索引覆盖了较少字段,优化器可能认为扫描整个索引比扫描整张表便宜,但当数据量增长后仍可能变慢。Using filesort也不一定代表使用了磁盘,真正需要结合临时表状态和执行时间判断。
MySQL 8.0.18及以上可以对只读查询使用:
EXPLAIN ANALYZE
SELECT id, status, created_at
FROM orders
WHERE user_id = 10086
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
该命令会实际执行查询,可能产生CPU、IO和锁访问压力。应使用脱敏但具有代表性的参数,在低流量时段运行,避免对包含副作用的语句使用。MySQL 5.7没有这一功能,可结合 EXPLAIN FORMAT=JSON、慢日志、Rows_examined和性能监控进行判断。
观察估算行数与实际行数是否偏离
如果 EXPLAIN 估算扫描1万行,但 EXPLAIN ANALYZE 显示实际扫描1000万行,优先怀疑:
- 统计信息过期;
- 数据分布高度倾斜;
- 某些字段相关性无法被准确估计;
- 参数值导致选择性与常见值不同;
- 查询条件经过函数或隐式转换;
- 表在近期发生大量写入、删除或批量导入。
如果估算和实际都很大,则更可能是索引选择性不足、SQL条件范围过宽或业务确实需要读取大量数据。此时单纯刷新统计信息通常不能解决根本问题。
判断 possible_keys 与 key 的区别
可以按以下方式理解:
possible_keys为NULL:优化器看不到可用索引,优先检查字段是否被函数包裹、类型是否不匹配、条件是否使用前置通配符,以及联合索引是否从第一列开始使用。possible_keys有值但key为NULL:优化器看到了索引,但估算全表扫描成本更低,优先检查选择性、统计信息和返回数据量。key有值但rows很大:索引确实被使用,但过滤能力有限,或查询仍需扫描大量索引记录。key有值且rows较小,但总耗时仍高:继续检查回表、排序、锁等待、磁盘和网络,不要再把问题归因于“索引失效”。
常见根因与对应修复
字段上使用函数或表达式
以下写法可能阻止普通B-tree索引直接用于范围定位:
SELECT *
FROM orders
WHERE DATE(created_at) = '2025-01-20';
可改写为时间范围:
SELECT *
FROM orders
WHERE created_at >= '2025-01-20 00:00:00'
AND created_at < '2025-01-21 00:00:00';
类似问题还包括:
LOWER(email) = 'a@example.com';CAST(user_id AS CHAR) = '10086';- 对索引列进行数学计算;
- 在连接条件两侧使用不同类型的表达式。
如果业务确实必须按表达式查询,可以评估生成列和对应索引,但要考虑写入成本、存储空间、版本支持和数据一致性。
隐式类型转换
字段为字符型,应用却传入数值型参数,或者字段为数值型而应用传入无法稳定转换的字符串,都可能导致索引利用率下降,还可能产生错误的比较结果。
例如,user_id定义为 VARCHAR(32) 时,应让驱动按字符串绑定参数,而不是在SQL中把字段转换为数值。修复时应同时核对:
- 数据库字段类型;
- ORM实体类型;
- JDBC、PHP、Go或其他驱动的绑定类型;
- 字符集和排序规则;
- 关联字段两侧的类型、长度和符号属性。
前置通配符和低选择性条件
下面的条件无法直接利用普通B-tree索引的左侧定位能力:
WHERE email LIKE '%example.com'
LIKE 'example%'通常可以使用索引范围扫描,但最终仍需结合执行计划验证。包含任意位置关键词的搜索,应根据业务需求评估全文索引、搜索引擎或其他专用检索方案,不要为了一个模糊查询给普通字段添加多个重复索引。
低选择性字段也容易让优化器放弃索引。例如状态字段只有“成功”和“失败”两种值,单独建立状态索引后,每次查询仍可能命中表中很大比例的数据。此时应结合高选择性字段设计联合索引,或调整查询范围。
联合索引顺序不匹配
假设存在索引:
CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at);
它更适合同时使用 user_id、status,并按 created_at排序或继续过滤的查询。以下查询只按 status筛选时,通常无法充分利用该索引的最左列:
WHERE status = 'paid'
联合索引设计通常需要综合考虑:
- 等值条件是否位于前面;
- 范围条件会不会截断后续列的使用;
ORDER BY是否可以复用索引顺序;- 是否需要回表读取大量字段;
- 写入、更新和删除是否会承担额外索引维护成本。
索引列越多不一定越好。覆盖索引可以减少回表,但会增加存储和写入开销,必须用实际查询模式验证。
统计信息过期或数据分布发生变化
如果近期进行过大量导入、删除、归档或状态批量更新,统计信息可能无法反映当前数据分布。确认执行计划和实际行数存在明显偏差后,可在备份、低峰和资源可控的前提下执行:
ANALYZE TABLE orders;
该操作会更新优化器统计信息,通常不改变业务数据,但可能导致后续查询采用不同执行计划,也会产生一定的表扫描和资源开销。执行前应保存原执行计划,确认磁盘和IO余量,并准备在计划变化不符合预期时恢复应用侧SQL、调整索引或使用经过验证的计划控制方案。不要把 ANALYZE TABLE 当成所有慢查询的固定修复动作。
参数分布不均或查询范围过大
同一条SQL可能存在明显的数据倾斜。例如,某个商户只有几百条订单,另一个商户有数千万条订单;同样的 WHERE merchant_id = ?,使用索引后的收益完全不同。
排查时应至少对比:
- 小结果集参数;
- 大结果集参数;
- 热门值和冷门值;
- 空范围、极大范围和正常范围;
- 不同分页位置。
如果某些参数天然会返回大量数据,优化方向可能是限制时间范围、拆分查询、调整分页方式、增加汇总表或使用缓存,而不是强制索引。
锁等待、排序和回表被误判为索引问题
即使计划显示 ref 或 range,仍可能因以下因素变慢:
- 事务持锁时间过长;
- 查询需要回表读取大量非索引字段;
ORDER BY与索引顺序不匹配;GROUP BY产生临时表;- 返回结果集过大;
- 应用逐行处理结果耗时较长。
对于大分页,LIMIT 100000, 50通常需要先扫描和跳过大量记录。可以结合稳定排序字段改用基于游标的分页,例如记录上一页最后的 created_at 和 id,再使用范围条件获取下一页。
从定位到修复的安全顺序
第一步:先固定可复现样本
使用同一版本数据库、同一SQL结构和代表性参数保存基线:
EXPLAIN FORMAT=JSON
SELECT id, status, created_at
FROM orders
WHERE user_id = 10086
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
同时记录执行耗时、返回行数、扫描行数和资源指标。不要在生产环境直接反复执行大查询进行测试,可先使用只读副本、脱敏数据或限制结果集验证。
第二步:优先改写可避免的谓词
先处理字段函数、隐式转换、前置通配符、无边界时间查询和过大分页。这类改动通常比盲目增加索引更容易验证,也更不容易增加写入成本。
改写后重新执行 EXPLAIN,确认:
key是否变为预期索引;key_len是否覆盖了预期列;rows是否明显下降;Extra中的排序和临时表是否减少;- 实际执行时间是否改善,而不是只看计划文本。
第三步:只有证据充分时才创建索引
创建索引前应检查已有索引,避免重复或高度重叠:
SHOW INDEX FROM orders;
新增索引会占用磁盘并增加插入、更新、删除成本,大表执行DDL还可能带来IO、元数据锁和复制延迟。应先备份或确认可恢复能力,在低峰期、具备监控和回滚窗口的条件下操作,并在与线上版本相同的测试环境验证。
示例索引如下:
CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at);
不要直接假设 ALGORITHM=INPLACE 或 LOCK=NONE 在所有版本、存储引擎和表结构上都可用。需要先核对MySQL版本、InnoDB能力和当前DDL限制。回滚通常是删除新增索引,但删除索引同样可能引发计划变化,应在确认没有其他SQL依赖后执行,并保留原执行计划和监控数据。
第四步:谨慎使用索引提示
FORCE INDEX、USE INDEX可以作为经过验证的临时措施,但不能替代根因修复。数据量、参数分布和统计信息变化后,原本有效的强制计划可能变成更差的计划。使用前应明确:
- 适用的SQL和参数范围;
- 失效条件;
- 监控指标;
- 移除提示的回滚方式。
如果只是为了“让计划看起来使用索引”而添加提示,可能掩盖统计信息、锁等待或返回数据过大的真实问题。
验证修复是否真正生效
修复后不要只执行一次查询就结束,应按照同一组参数和高峰场景进行对比:
| 验证项 | 关注结果 |
|---|---|
| 执行计划 | 是否使用预期索引,是否出现不必要的全表扫描 |
| 实际扫描行数 | 是否明显下降,估算与实际是否接近 |
| 查询耗时 | P50、P95、P99是否改善,而非只有单次平均值 |
| MySQL资源 | CPU、IO、临时表、线程数是否回到基线 |
| 锁等待 | 是否仍存在阻塞事务 |
| 应用耗时 | 连接池等待和结果处理时间是否同步下降 |
| 写入性能 | 新增索引是否造成写入延迟或复制延迟 |
可使用不同参数重复验证,尤其要覆盖大结果集、热门值和冷门值。若计划变好但应用总耗时没有变化,应回到网络、连接池和应用处理层排查;若应用耗时下降但写入延迟明显升高,则需要重新评估索引覆盖范围和列顺序。
建立防止复发的监控点
索引问题适合通过趋势监控提前发现,而不是等用户反馈后再处理。建议持续关注以下信号:
- 慢查询日志中的查询指纹、平均耗时和P95耗时;
Rows_examined与返回行数的比例;- 同一查询指纹的执行计划是否发生变化;
Threads_running、连接池等待数和连接获取耗时;Created_tmp_disk_tables及临时文件增长;- InnoDB缓冲池物理读增长速度;
- CPU、磁盘await、IO利用率和swap;
- 锁等待、长事务和死锁数量;
- 表数据量、索引大小及新增索引后的写入延迟。
告警阈值应以自身历史基线为准。例如,可以对“查询P95持续超过平时基线数倍”“扫描行数与返回行数比例突然扩大”“同一查询的key从索引变为NULL”“连接池等待时间持续增长”等组合条件告警,而不是仅对一次 type=ALL 触发故障。
当执行计划、实际扫描行数、资源指标和应用耗时能够同时对应起来时,所谓“MySQL索引失效”就能被拆解成可验证的问题:是条件无法利用索引、索引选择性不足、统计信息不准、锁和资源拖慢,还是应用在数据库之外等待。按照这个顺序处理,既能减少无效建索引,也能降低修复后再次出现计划漂移的风险。
MySQL查询的稳定运行离不开合适的计算、内存、存储与网络资源。A5数据(A5IDC)提供香港、美国、日本、韩国、中国台湾、新加坡和马来西亚等地区的物理服务器,涵盖Xeon Gold与AMD EPYC平台,并有配备NVMe存储的方案,可用于数据库、业务后台及接口服务等部署。不同地区还提供多种线路和存储配置,方便企业按业务访问区域与数据负载规划数据库运行环境。
本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/5079.html



