MySQL索引副作用实战分析：用performance_schema定位生产环境性能痛点

在 MySQL 性能优化的世界里，索引常常被寄予厚望，被视为解决问题的“银弹”。然而，在真实的生产环境中，索引所带来的副作用有时比“没有索引”本身更为隐蔽和致命。你是否遇到过这些情况？

• 该用的没用：查询理应走索引，却进行了全表扫描。
• 不该用的乱用：虽然走了索引，但扫描的行数依然巨大，效率低下。
• 索引成为负担：过多的索引导致写入操作（INSERT/UPDATE）变慢，引发“写入放大”。

那么问题来了：如何用确凿的数据来证明，真的是索引出了问题？

凭感觉和经验是不够的，必须依赖数据。MySQL 内置的 performance_schema 正是那个能够提供量化证据的“显微镜”。本文将带领你构建一套可以在生产环境落地的索引副作用排查体系。

一、索引副作用的三种典型表现

类型	现象	本质问题
索引失效	查询执行计划显示 type=ALL（全表扫描）	查询条件不满足索引匹配规则（如隐式类型转换）
低效索引	使用了索引但扫描行数 (rows_examined) 巨大	索引设计不佳，过滤性差
索引过载	INSERT / UPDATE 等写操作性能显著下降	索引数量过多，存在冗余索引

二、行动前准备：确认仪表盘已开启

开始排查前，首先需要确认 performance_schema 功能已经启用：

SHOW VARIABLES LIKE 'performance_schema';

接下来，检查记录SQL摘要统计的关键 consumers 是否开启：

SELECT NAME, ENABLED
FROM performance_schema.setup_consumers
WHERE NAME LIKE '%statements%';

如果发现 statements_digest 为 NO，则需要手动开启：

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'statements_digest';

对于生产环境的配置，可以参考以下建议：

场景	建议
高并发 OLTP	开启 statements_digest，谨慎开启 events_statements_history_long
性能问题排查期	临时开启 events_statements_history_long 以捕获完整SQL
长期开启监控	关注 performance_schema 的内存占用

三、宏观定位：哪些SQL在进行全表扫描？

我们可以借助 sys 库中的视图快速定位那些频繁进行全表扫描的查询。sys 库是基于 performance_schema 的视图封装，能让查询变得更简单。

USE sys;

SELECT
    query,
    db,
    exec_count,
    total_latency,
    rows_examined_sent
FROM statements_with_full_table_scans
ORDER BY exec_count DESC
LIMIT 5;

解读逻辑：

• rows_examined_sent 远大于 1：说明扫描了大量行，但只返回很少数据，索引可能缺失或失效。
• exec_count 高：说明这是高频SQL，其全表扫描对整体性能影响大，应优先优化。
• exec_count 低但 total_latency 高：可能是报表类大查询，虽不频繁但单次影响严重。

四、中观定位：哪张表是“重灾区”？

定位到问题SQL后，下一步是看这些SQL具体作用于哪些表，以及这些表上的索引使用情况。

SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_STAR,
    COUNT_READ,
    SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = ‘your_db’
  AND OBJECT_NAME = ‘your_table’
ORDER BY INDEX_NAME IS NULL DESC;

关键观察点：

INDEX_NAME 的值	含义
NULL	表示操作未使用任何索引，即全表扫描。
PRIMARY	使用了主键索引。
idx_xxx	使用了名为 idx_xxx 的二级索引。

如果发现 INDEX_NAME = NULL 且对应的 COUNT_STAR 计数非常高，那这张表就是索引问题的重灾区，大量查询没有有效利用索引。

五、微观分析：哪些查询“用错了索引”？

有些查询虽然走了索引，但效率依然低下。我们可以通过分析SQL摘要的统计信息来找到它们。

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT / 1e12 AS avg_sec,
    SUM_ROWS_EXAMINED / COUNT_STAR AS avg_examined,
    SUM_ROWS_SENT / COUNT_STAR AS avg_sent,
    SUM_ROWS_EXAMINED / SUM_ROWS_SENT AS exam_sent_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE COUNT_STAR > 100
ORDER BY exam_sent_ratio DESC
LIMIT 10;

指标解读与风险信号：

指标	风险信号
avg_examined (平均扫描行数) 高	每次执行需要扫描大量数据行，索引过滤性可能不佳。
avg_sent (平均返回行数) 低	扫描了很多行，但只返回很少的结果。
exam_sent_ratio (扫描/返回比) 高	比值越大，说明索引的过滤效果越差，是典型的“低效索引”特征。

常见根因：

1. 低选择性索引：例如在性别(gender)、状态(status)等取值种类很少的列上建索引。
2. 联合索引顺序错误：联合索引的列顺序未按查询条件的使用频率和过滤性进行设计。
3. 范围查询导致前缀失效：在联合索引中，某一列使用了范围查询（>, <, BETWEEN， LIKE ‘%xx’），导致其后的索引列无法被使用。

六、典型索引失效场景（生产高发）

找到可疑SQL后，需要检查其是否触发了常见的索引失效规则。

❌ 场景1：隐式类型转换

WHERE phone = 13800138000 -- `phone` 字段是 VARCHAR 类型

👉 索引失效。字符串字段与数字比较，MySQL会将字符串转为数字，导致索引失效。
✅ 正确写法：

WHERE phone = ‘13800138000’

❌ 场景2：对索引列使用函数

WHERE DATE(create_time) = ‘2026-02-13’

👉 索引失效。create_time 上的索引无法用于 DATE(create_time) 函数计算后的结果。
✅ 改写：

WHERE create_time >= ‘2026-02-13 00:00:00’
  AND create_time <  ‘2026-02-14 00:00:00’

❌ 场景3：联合索引未满足最左前缀匹配原则
假设存在联合索引 idx_a_b_c (a, b, c)。

WHERE b = ?

👉 不走该联合索引。查询条件没有包含索引的最左列 a。

七、最终裁定：使用 EXPLAIN 验证执行计划

在基于 performance_schema 的数据分析后，最终需要通过 EXPLAIN 来查看具体SQL的执行计划，这是诊断索引问题的“金标准”。

EXPLAIN SELECT …;

重点关注 EXPLAIN 结果中的以下几列：

列	风险信号
type	值为 ALL 时，表示全表扫描。
possible_keys 与 key	possible_keys 列出了可用的索引，但如果 key 为 NULL，说明优化器最终没有使用任何索引。
rows	表示MySQL预估需要扫描的行数，数值巨大则意味着高成本。

八、MySQL 8 的进阶优化武器（强烈推荐）

MySQL 8.0 提供了更强大的工具来辅助索引管理和优化。

✅ 1. 隐形索引
在决定删除一个可能无用的索引前，可以先将它设为“隐形”，观察业务和性能是否受影响，这是一种安全的验证手段。

ALTER TABLE t ALTER INDEX idx_xxx INVISIBLE;

观察期内，重点关注 QPS、查询延迟、rows_examined 等指标。确认无负面影响后，再执行 DROP INDEX。

✅ 2. 直方图统计信息
优化器选择错误索引的一个常见原因是表数据的统计信息不准确，特别是数据分布严重倾斜时。直方图提供了列数据分布的详细信息。

ANALYZE TABLE t UPDATE HISTOGRAM ON col_x;

适用场景： 数据分布不均匀的列、没有索引但频繁用于 WHERE 条件过滤的列。

✅ 3. 优化器追踪
当你疑惑“为什么MySQL不选择我预想的那个索引”时，Optimizer Trace 可以揭示优化器内部的决策过程。

SET optimizer_trace=“enabled=on”;
-- 执行你的查询
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

九、识别“索引过载”（写入性能的元凶）

索引并非越多越好。每个索引在 INSERT、UPDATE、DELETE 时都需要维护，会导致写入放大。通过以下查询可以识别写压力大的表：

SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY COUNT_WRITE DESC;

如果某张表同时满足：

• INSERT/UPDATE 操作变慢。
• COUNT_WRITE 计数非常高。
• 通过 SHOW INDEX FROM your_table; 查看发现索引数量众多。

那么，索引过载很可能就是写入性能的瓶颈。你需要评估这些索引的必要性。

十、查找冗余与重复索引

冗余索引会白白占用空间并降低写性能。sys 库提供了方便的视图来查找它们。

SELECT *
FROM sys.schema_redundant_indexes;

典型冗余示例：

索引1: idx_a (a)
索引2: idx_a_b (a,b)

此时，单列索引 idx_a 就是冗余的，因为查询 WHERE a=? 同样可以使用联合索引 idx_a_b 的最左前缀 (a)。可以考虑删除 idx_a。

十一、真实生产经验总结

类别	最佳实践
索引设计	优先在区分度高（高选择性）的列上建立索引。
联合索引设计	遵循 WHERE 条件列 → ORDER BY 列 → GROUP BY 列的顺序。
慢SQL排查	第一时间关注 EXPLAIN 中的 rows_examined（扫描行数）而非执行时间。
删除索引	删除前务必使用 INVISIBLE 特性进行灰度验证。
写入密集型表	严格控制索引数量，遵循“最少索引”原则。
长期监控	结合 performance_schema 与 sys 视图建立常态化监控。

✅ 核心结论

使用 performance_schema 排查索引问题的本质，是让数据库自身用数据来证明：索引究竟是提升性能的资产，还是拖累系统的负债。

推荐的标准化排查路径如下：

发现全表扫描SQL → 定位受害数据表 → 分析具体问题SQL → EXPLAIN验证 → 实施索引优化或删除

掌握这套基于数据的分析方法，你就能在复杂的生产环境中，精准地揪出那些潜伏的索引副作用，让数据库性能优化真正做到有的放矢。对于更深入的数据库性能调优和系统设计知识，你也可以在云栈社区的技术论坛中找到丰富的实战案例和同行交流。