索引效率对比：查询执行统计与SQL耗时分析

如何利用查询执行统计来对比不同索引的效率

在数据库性能调优中，对比不同索引的效果是个高频操作。但直接比较执行时间，往往会掉进一个常见的陷阱：单次执行的耗时波动太大，容易导致误判。问题的核心在于，你需要对比的是稳定状态下的执行路径和资源消耗，而不是某一次执行的快慢。

EXPLAIN ANALYZE 耗时不准因受缓存、锁和系统负载干扰，应取后两次 Execution Time 平均值，并关注 Buffers（PG）或 Handler_read_*（MySQL）等真实 I/O 指标，而非单次耗时。

查 EXPLAIN ANALYZE 时为什么耗时不准

原因很简单，单次执行会受到缓存、锁、系统负载等多种因素的干扰。直接盯着 execution time 看，很容易被表象迷惑，从而对索引的优劣做出错误判断。

• 测试环境要一致：务必在相同数据量、相同连接会话下进行，并记得关闭查询缓存（例如在MySQL中执行 SET SESSION query_cache_type = OFF）。
• 预热与稳定取值：首次执行通常会触发磁盘读和缓冲池加载，这不能代表常态。至少执行3次，然后取后两次的 Execution Time 平均值作为参考。
• 关注核心指标：EXPLAIN ANALYZE 输出里的 Buffers 行（PostgreSQL）或 Handler_read_* 状态（MySQL）比单纯的耗时更能反映索引的实际效率。

MySQL 下怎么让 EXPLAIN 显示真实 I/O 开销

默认情况下，EXPLAIN 命令不会告诉你磁盘读了多少数据。这时候，就需要请出 SHOW STATUS 这个好帮手了。通过配合状态清零再执行SQL，你能清晰地看到索引实际扫描了多少行、回表了几次。

• 操作步骤：执行目标SQL前，先运行 FLUSH STATUS 清零计数器，然后执行SQL。
• 关键指标解读：立刻查询 SHOW STATUS LIKE 'Handler_read%'。重点关注 Handler_read_next（索引顺序扫描的行数）和 Handler_read_rnd（随机读取/回表的次数），这两个值自然是越小越好。
• 一个危险信号：如果 Handler_read_rnd 的数值远高于 Handler_read_next，那就要警惕了。这通常意味着索引没有覆盖查询所需的全部字段，导致大量随机IO回表，性能杀手就此现身。

PostgreSQL 中 EXPLAIN (ANALYZE, BUFFERS) 关键看哪几行

在PostgreSQL里，使用 EXPLAIN (ANALYZE, BUFFERS) 能获得更详细的信息。但别只盯着最上面的总耗时，真正有价值的信息藏在每层节点的细节里，特别是 Buffers 和 Rows Removed by Filter。

• Buffers: shared read=：这个值代表真实的磁盘读取块数。只要它大于0，就说明对应的索引或表数据没有被缓存住。在反复测试中，这个值应该逐渐趋近于0，才说明达到了稳定的缓存状态。
• Rows Removed by Filter：这是一个效率“照妖镜”。如果看到类似 Rows Removed by Filter: 9990/10000 的输出，那就意味着索引选型可能出了问题——它扫描了1万行，却只留下了10行，过滤成本已经远远超过了索引带来的收益。
• 留意索引条件与过滤：注意观察类似 Index Scan using idx_a_b on t 后面是否同时出现了 Index Cond: (a = $1) 和 Filter: (b > $2)。如果两者并存，说明 b 字段的条件没有被索引有效利用，而是在索引扫描后进行了额外的过滤，这会影响效率。

SQL 相同但执行计划突变？检查这三处隐性干扰

有时候，明明是同一条SQL语句，执行计划却突然变了，导致之前的索引对比完全失效。这背后，往往是以下三种隐性干扰在作祟：

• 隐式类型转换：用 SELECT pg_typeof(col) 这类命令确认WHERE条件中的字段和传入参数的类型是否严格一致。像 WHERE id = '123' 这样的写法，如果id是整数类型，就会触发隐式转换，可能导致索引失效。
• 统计信息过期：数据分布变化后，优化器的判断依据可能已经过时。记得定期执行 ANALYZE table_name（PostgreSQL）或 ANALYZE TABLE table_name（MySQL），确保优化器基于最新的数据分布做出决策。
• 绑定变量窥探：在MySQL中，如果使用了预处理语句，第一次执行生成的执行计划可能会被后续请求复用。为了获得准确的对比，可以尝试在语句中添加 /*+ RECOMPILE */ 提示，或者暂时改用普通语句进行测试。

说到底，在实际对比索引效率时，最需要关注的其实是「索引是否真正减少了物理I/O」，而不是「执行时间少了多少毫秒」。磁盘IO的波动性很大，而缓冲命中率（buffer hit rate）和过滤效率（filter efficiency）这些指标，才是跨环境、可复现的黄金判断依据。