数据库服务器MySQL索引失效如何从执行计划定位原因并修复

发布人:A5数据发布时间:2026-10-22 10:18阅读量:138

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

根据全文核心主题形成自然、专业、克制的技术文章主题场景

最有效的排查路径是:先确认慢在哪里,再按网络、系统、MySQL服务、应用SQL四个层级建立原因树,随后用执行计划验证候选原因。只有确认是谓词写法、联合索引顺序、统计信息或数据分布导致计划不合理后,才进行SQL改写、统计信息更新或索引调整,不能仅凭 type=ALL 就直接创建索引或强制走索引。

先确认“索引失效”到底表现在哪里

区分三类不同问题

生产环境中常见的“索引失效”至少包括三种情况:

  1. 执行计划没有使用索引:EXPLAIN 中 key 为 NULL,或访问类型为 ALL。
  2. 使用了索引,但扫描行数仍然很多:key 不为 NULL,但 rows 很大,或者实际扫描行数远高于返回行数。
  3. 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表示没有选择索引
typeconst、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

相关推荐