WEBKT

后端开发必备：用EXPLAIN诊断慢SQL，告别盲猜！

61 0 0 0

作为一名后端开发者，面对线上环境偶尔出现的慢查询，那种焦头烂额的感觉我太懂了！尤其是在没有专业 DBA 团队支持的情况下，SQL 语句执行慢却不知道具体是哪个环节出了问题，只能靠猜和试错，效率低下不说，还容易引入新的风险。今天，我就来分享一个后端开发必备的“照妖镜”—— EXPLAIN（以及其变种），教你如何告别盲猜，精准定位 SQL 性能瓶颈。

一、为什么你的 SQL 会慢？

在深入 EXPLAIN 之前，我们先快速回顾一下导致 SQL 慢查询的常见原因：

  1. 索引缺失或失效：这是最常见的原因，查询无法有效利用索引，导致全表扫描。
  2. 查询语句本身效率低下：例如，SELECT *、复杂的 JOIN 条件、子查询过多、WHERE 子句中使用函数或类型转换。
  3. 数据库设计不合理：表结构冗余、字段类型选择不当、缺乏范式化或反范式化考虑。
  4. 数据量过大：即使有索引，在千万级别的数据量下，查询效率也会受到影响。
  5. 服务器资源瓶颈：CPU、内存、磁盘 I/O 不足，但这种情况通常影响所有查询，而非特定 SQL。

我们的目标，就是通过工具准确找出是上述哪一种或哪几种情况在作祟。

二、EXPLAIN：慢查询的“CT”报告

EXPLAIN 命令（在 PostgreSQL 中是 EXPLAIN ANALYZE，SQL Server 中是 SET SHOWPLAN_ALL ON 或图形化执行计划）是数据库提供的查看 SQL 语句执行计划的强大工具。它能告诉你数据库是如何执行你的查询的，包括：

  • 数据访问方式：是全表扫描、索引扫描还是范围扫描？
  • 连接（JOIN）类型和顺序：多个表是如何连接的？
  • 排序（ORDER BY）和分组（GROUP BY）操作：是否使用了文件排序（filesort）或临时表？
  • 预估的行数和成本：数据库预估需要处理多少行数据，以及执行这个操作的相对成本。

理解这些信息，就等于拿到了诊断慢查询的第一手资料。

以 MySQL EXPLAIN 为例：

我们来分析几个关键的输出字段：

字段名 含义 关注点 优化方向
id 查询的序列号，一组数字，表示一次 SELECT 的查询。 越大越先执行，id 相同则从上到下执行。 了解执行顺序，调整子查询或 JOIN 顺序。
select_type 查询类型，如 SIMPLE, PRIMARY, SUBQUERY, UNION 等。 DEPENDENT SUBQUERY 和 UNCACHEABLE SUBQUERY 需要重点优化。 尽量避免依赖子查询，改用 JOIN。
table 当前操作的表名。 多个表时，关注表的连接顺序。 调整 JOIN 顺序，小表驱动大表。
type 访问类型，这是最重要的字段之一。 ALL (全表扫描) 最差，index (全索引扫描) 次之，range (范围扫描) 较好，ref, eq_ref, const 最好。 尽量避免 ALL 和 index，争取达到 range 或更好的级别，确保 WHERE 条件能利用索引。
possible_keys 可能使用的索引。 即使有索引，也不一定使用。 确保 WHERE 或 JOIN 条件包含在 possible_keys 中。
key 实际使用的索引。 如果为 NULL，表示没有使用索引。 没有使用索引是慢查询的常见原因，需要创建或优化索引。
key_len 使用的索引的长度。 越短越好，说明索引利用效率高。 关注复合索引的顺序和长度，保证最左前缀原则。
ref 哪个列或常数被用于查找索引列上的值。 关注常数与索引列的匹配。 确保 JOIN 字段类型一致，WHERE 条件值类型匹配。
rows 预估需要扫描的行数。 越大越糟糕。 这个值是判断查询效率的关键指标，应尽量减少。
Extra 额外信息，非常重要。 Using filesort (文件排序) 和 Using temporary (使用临时表) 是性能杀手，应尽量避免。 Using index 是好兆头。 优化 ORDER BY/GROUP BY 语句以避免文件排序；重写查询减少临时表使用。

三、如何精准“照妖”：实践步骤

  1. 复现慢查询：首先，在开发或测试环境中复现你的慢查询 SQL 语句。确保数据量和生产环境接近，或者模拟出足够的数据。

  2. 执行 EXPLAIN：
    在你的 SQL 语句前加上 EXPLAIN 关键字。

    EXPLAIN SELECT column1, column2 FROM your_table WHERE condition_column = 'value' ORDER BY another_column;

    对于 PostgreSQL，可以使用 EXPLAIN ANALYZE 来获取实际执行时间，而不是预估时间。

  3. 分析 EXPLAIN 结果：

    • 看 type：是不是 ALL？如果是，说明你在进行全表扫描。
      • 解决方案：检查 WHERE 条件中是否包含索引列，或者是否对索引列进行了函数操作导致索引失效。考虑在 condition_column 上创建索引。
    • 看 key：是不是 NULL？如果是，说明没有使用索引。
      • 解决方案：根据 WHERE 和 JOIN 条件，在相关列上创建合适的索引。
    • 看 rows：这个值是不是非常大？如果你的表只有 1000 条数据，rows 却显示 900 条，那说明扫描的效率很低。
      • 解决方案：这是 type 和 key 问题的直接体现，解决它们后 rows 会自然下降。
    • 看 Extra：
      • Using filesort：糟糕！这表示数据库需要对结果进行额外的文件排序操作，通常发生在 ORDER BY 或 GROUP BY 的列上没有索引，或者索引不适用于排序时。
        • 解决方案：在排序字段上创建索引，或调整查询使索引可以覆盖排序。
      • Using temporary：更糟糕！这表示数据库需要创建临时表来处理查询，通常发生在 GROUP BY 或 UNION 操作上。
        • 解决方案：优化 GROUP BY 字段的索引，或者尝试重写 UNION 为 UNION ALL (如果业务允许且可以去重)。
      • Using index：完美！这表示查询完全可以在索引中完成，不需要回表查询数据。
        • 解决方案：尽量优化查询达到这个状态，即“覆盖索引”。

  4. 调整与优化：根据分析结果，采取相应的优化措施：

    • 创建/优化索引：这是最直接也最有效的方法。考虑复合索引、覆盖索引。
    • 重写 SQL 语句：
      • 避免 SELECT *，只查询需要的字段。
      • 简化 WHERE 条件，避免在索引列上使用函数、OR 条件（有时会使索引失效）、LIKE %关键字（通配符在前）。
      • 优化 JOIN 语句，确保 JOIN 字段有索引，并注意 JOIN 的顺序。
      • 避免过多子查询，尝试改写为 JOIN。
      • 使用 LIMIT 限制返回结果集，特别是做分页查询时。
    • 调整数据库结构：如果上述方法无效，可能需要考虑调整表结构，例如字段类型、范式化/反范式化。

四、不仅仅是 EXPLAIN：辅助工具

  • 慢查询日志（Slow Query Log）：数据库服务器通常会记录执行时间超过阈值的 SQL 语句。这是发现慢查询的入口。
  • 数据库性能监控工具：许多数据库都有自己的性能监控工具（如 MySQL Workbench 的 Performance Report，Percona Toolkit 等），可以提供更全面的性能指标。

总结

EXPLAIN 是后端开发者定位慢 SQL 瓶颈的利器。它就像一个 X 光机，让你能看透 SQL 的执行过程。掌握它，你就能从过去的盲目猜测中解脱出来，每次遇到慢查询都能有条不紊地分析和解决。多练习，多实践，你也能成为团队中的“数据库性能诊断高手”！

码农小黑 SQL优化EXPLAIN数据库性能

评论点评