加索引查询反而变慢了？——记一次慢 SQL 治理实践
Posted on :: 数据库 性能调优

在数据库性能优化领域，索引通常被视为提升查询效率的银弹。然而，在实际的生产环境中，有时会遇到一种反直觉的现象：给原本执行缓慢的查询添加索引后，查询性能不仅没有提升，反而变得更慢。本文将通过一个实际的业务案例，深入剖析 MySQL 查询优化器的决策逻辑，并探讨如何通过合理的索引设计解决复杂的性能问题。

MySQL 查询的执行流程

理解索引失效或性能下降的原因，首先需要明确 MySQL 执行一条查询语句的底层逻辑。MySQL 的架构可以分为 Server 层和存储引擎层。

  1. 连接器与缓存：客户端发起请求，通过连接器建立连接。虽然 MySQL 8.0 之后已经废弃了查询缓存，但在较旧版本中，如果命中了缓存则直接返回结果。
  2. 解析器与预处理器：解析器会对 SQL 语句进行词法和语法分析，确保语句符合语法规则。预处理器则会检查表名、列名是否存在。
  3. 优化器（Optimizer）：这是决定查询性能的关键环节。MySQL使用的是基于成本的优化器。优化器的任务是根据现有的统计信息，比如表的总行数、索引的基数、数据页的数量等，计算出各种可能的执行方案的成本。成本主要包括I/O成本（读取磁盘数据页）和CPU成本（数据比较、排序等）。优化器会选择一个成本最低的方案生成执行计划。我们在排查慢SQL时看到的explain结果，就是优化器经过计算后认为最优的执行路径。
  4. 执行器与存储引擎：执行器根据优化器生成的计划，调用存储引擎的接口获取数据。在这一步，存储引擎（通常是InnoDB）会利用索引结构或全表扫描的方式，将数据加载到内存中返回给执行器。

InnoDB 索引的设计

为了讲清楚后面的案例，我们需要先回顾一下InnoDB引擎的索引结构。InnoDB的数据存储是基于B+树的。

  • 聚簇索引与二级索引：一张表的数据是按照聚簇索引组织的，通常就是主键索引。聚簇索引的叶子节点里存的是完整的行数据。除了聚簇索引之外的索引，都叫二级索引。二级索引的叶子节点里存的是索引列的值和对应的主键值，不包含完整的行数据。
  • 回表操作：当我们通过二级索引查找数据时，如果查询需要的列不在当前索引中，引擎就必须拿着二级索引里找到的主键值，回到聚簇索引中去查找完整的行数据。这个过程叫做回表。
  • 索引的有序性：B+树的一个核心特性是有序性。索引树上的数据是严格按照定义的字段顺序排列的。这意味着如果我们按照索引字段进行排序查询，数据库可以直接按顺序读取，而不需要额外的排序操作。

案例背景：加了索引反而更慢？

业务场景描述

在系统中，存在一张订单箱体表 order_boxes。该表承载了大量的 return，业务需要频繁查询状态为“return_completed”且按“return_processed_at”倒序排列的数据。

原始 SQL 如下：

SELECT * FROM order_boxes 
WHERE status = 'return_completed' 
ORDER BY return_processed_at DESC;

优化尝试与意外结果

该表在初期并没有建立任何索引。随着数据量增长到百万级，查询变得极其缓慢。通过 EXPLAIN 发现，此时 MySQL 执行的是全表扫描，并且在内存或磁盘中进行了文件排序。

为了优化查询，我们针对排序字段创建了单列索引：

CREATE INDEX idx_return_time ON order_boxes (return_processed_at);

预期中，MySQL 应该利用索引的有序性直接获取数据。但实际测试发现，查询耗时从 3 秒增加到了 5 秒以上。

原因深度剖析

通过分析执行计划发现，加上索引后，优化器确实选择了 idx_return_time。然而，执行路径发生了变化：

  1. 方案 A：不使用索引（全表扫描）
    • 流程：顺序扫描聚簇索引的所有记录。
    • 处理：对每一行判断 status 是否匹配。
    • 排序：将匹配的行放入 sort_buffer 中进行 filesort。
    • 成本：高额的 CPU 排序成本，但 I/O 是顺序读取。
  2. 方案 B：使用单列索引（idx_return_time）
    • 流程：按照 return_processed_at 的倒序扫描二级索引。
    • 回表：每读到一个索引项，就必须立即根据主键回表读取整行数据，以判断 status 是否满足条件。
    • 成本：虽然省去了排序时间，但产生了大量的随机回表 I/O。

在这个特定场景下，回表成本 > 全表扫描 + 排序成本。因为 status = 'return_completed' 的记录在表中占比约 20% 到 30%，这意味着如果扫描索引，有很大比例的记录需要执行回表操作。对于机械硬盘甚至是部分 SSD 而言，大量的随机回表是性能杀手。

优化器的选择不稳定

进一步观察发现，当 status 的过滤性较强（例如查询一个极少出现的状态）时，优化器会选择索引。而当数据分布达到某个临界点时，优化器的决策会变得不稳定，甚至选错执行计划。这是因为统计信息的更新存在滞后，且优化器对回表成本的评估模型相对固定。

最终方案：联合索引

单列索引无法同时兼顾“过滤”和“排序”的需求。在该案例中，由于需要先根据 status 过滤，再根据 return_processed_at 排序，最符合逻辑的优化方式是建立联合索引。

正确的索引设计

CREATE INDEX idx_status_return_time ON order_boxes (status, return_processed_at DESC);

这个联合索引的设计遵循了以下原则：

  1. 等值过滤在前：将 status 放在索引的第一列。由于索引是有序的，所有 status = 'return_completed' 的记录在 B+ 树中是紧凑排列在一起的。
  2. 排序字段在后：在 status 相同的前提下，return_processed_at 已经按照索引定义的顺序排好了。这意味着优化器可以直接从索引中按顺序读取数据。
  3. 避免文件排序：因为索引本身就是有序的，执行计划中的 Extra 列将不再出现 Using filesort，而是利用索引的自然顺序。
  4. 减少回表范围：只有在索引中完全匹配 status 的记录才需要回表（如果不是为了 SELECT *，甚至可以实现覆盖索引扫描）。

优化后的执行逻辑

使用联合索引后，MySQL 的执行路径变为：

  1. 在 idx_status_return_time 索引中直接定位到 status = 'return_completed' 的起始位置。
  2. 按照索引顺序向后扫描。
  3. 对扫描到的每一项进行回表（由于此时过滤出的数据量已经大幅减少，回表次数降至最低）。
  4. 直接返回结果，无需额外排序。

排查慢 SQL 的流程和方法

在治理慢 SQL 时，通常遵循一套标准化的排查流程：

  1. 慢日志采集：通过设置 long_query_time 参数，将执行时间超过阈值的 SQL 记录到慢查询日志中。
  2. 执行计划分析：使用 EXPLAIN 命令查看 SQL 的执行计划。重点关注 type（访问类型）、key（实际使用的索引）、rows（预估扫描行数）以及 Extra（额外信息，如 Using filesort、Using index 等）。
  3. 成本量化：通过 optimizer_trace 工具查看优化器在选择索引时的具体成本计算细节，对比不同路径的 total_cost。
  4. 数据分布检查：通过 SHOW INDEX FROM table_name 查看索引的基数（Cardinality），并分析表中数据的分布情况，判断是否存在数据倾斜。

总结和启发

这次慢 SQL 治理实践带来了几点深刻的启示：

  • 索引不是越多越好：索引虽然能加速查询，但也会增加维护成本和 I/O 负担。不合理的索引不仅浪费空间，还可能诱导优化器做出错误的决策。
  • 理解回表成本：在非覆盖索引的场景下，回表是影响性能的核心因素。当查询结果集占总记录比例较高时，全表扫描往往优于索引扫描，不要惧怕 filesort。
  • 联合索引的逻辑顺序：设计联合索引时，必须根据 SQL 的谓词逻辑进行排列。通常遵循“等值查询列在前，范围查询列在后，排序列在后”的原则。
  • 执行计划是唯一标准：在修改索引后，必须通过 EXPLAIN 验证优化器的实际行为，确保其确实按照预期的路径执行。
Table of Contents