大数跨境
首页
>
快速修复慢速 SQL
>

快速修复慢速 SQL

快速修复慢速 SQL 索引目录
2025-05-12
1
导读：在数据库管理领域，慢查询是臭名昭著的性能吸血鬼。但是，如何才能揭开这些隐藏的“面纱”，了解其背后的原因呢？

在数据库管理领域，慢查询是臭名昭著的性能吸血鬼。但是，如何才能揭开这些隐藏的“面纱”，了解其背后的原因呢？输入EXPLAIN命令——它就像放大镜，可以窥探数据库的查询执行策略。



“EXPLAIN” 是一个强大的 SQL 命令，它可以揭示查询的执行计划。该计划是数据库关于如何获取数据的详细路线图。它揭示了关键信息，例如将利用（或忽略）哪些索引、表的连接顺序、扫描表的方法等等。理解该计划是将缓慢的查询转变为运转良好、高效的数据检索机器的第一步。

当您EXPLAIN在 SQL 查询中添加内容时，数据库会提供大量信息，通常包括如下字段：

  • id：查询每个部分的标识符（尤其是在带有子查询或联合的复杂查询中）。

  • select_type：查询类型SELECT（例如SIMPLE，，，SUBQUERY）UNION。

  • table：正在访问的表。

  • partitions：如果使用分区，则显示涉及哪些分区。

  • type：这至关重要！它指示连接类型或表访问方法（例如，ALL全表扫描、index索引扫描、range索引范围扫描、ref使用非唯一键的索引查找、eq_ref使用唯一键的连接、const/system高度优化的查找）。

  • possible_keys：显示数据库可能使用的索引。

  • key：数据库决定使用的实际索引。如果为NULL，则表示此部分未有效使用索引。

  • key_len：所用密钥（索引部分）的长度。

  • ref：显示哪些列或常量与列中命名的索引进行比较key。

  • rows：数据库执行此部分查询时预计检查的行数估计值。

  • filtered：读取后根据表条件过滤的行数的预估百分比。

  • Extra：包含其他有价值的信息，例如“使用 filesort”（需要对结果进行排序）、“使用 temporary”（需要创建临时表）、“使用 index”（高效的仅索引扫描）或“使用 where”（检索后过滤行）。

让我们深入研究两个实际案例研究，以说明如何EXPLAIN指导您的 SQL 优化工作。

案例研究 1：优化简单计数查询

场景设置：

假设一个电商平台，有一个名为 ProductSales 的数据库表，用于记录每笔产品的销售情况。该表的结构大致如下：

  • sale_id（INT，主键）：销售的唯一标识符。

  • product_sku（VARCHAR）：所售产品的 SKU。

  • customer_id（INT）：进行购买的客户的 ID。

  • sale_timestamp（时间戳）：销售日期和时间。

  • quantity_sold（INT）：售出的单位数量。

  • sale_amount（小数）：本次销售的总金额。

问题：

我们需要找出“2025-03-01”之后的总销售数量。

原始 SQL 查询：

SELECT COUNT(*)
FROM ProductSales
WHERE sale_timestamp > '2025-03-01';

步骤 1：用于EXPLAIN分析查询

EXPLAIN SELECT COUNT(*)
FROM ProductSales
WHERE sale_timestamp > '2025-03-01';

第 2 步：分析EXPLAIN输出（假设的初始输出）

我们假设初始EXPLAIN输出如下所示（简化表格格式）：

+----+-------------+--------------+-------+-----------------+---------------+---------+------+--------+----------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key           | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+--------------+-------+-----------------+---------------+---------+------+--------+----------+--------------------------+
| 1  | SIMPLE      | ProductSales | range | idx_sale_time   | idx_sale_time | 5       | NULL | 150000 | 100.00   | Using where; Using index |
+----+-------------+--------------+-------+-----------------+---------------+---------+------+--------+----------+--------------------------+

步骤3：确定问题

从此EXPLAIN输出：

  • typeis range：这很好；这意味着数据库正在使用索引（idx_sale_timeon sale_timestamp）执行范围扫描，这比全表扫描（ ）要好得多ALL。

  • rows估计为150000：这表明查询仍然需要根据日期范围检查大量行。

  • Extra显示“Using where; Using index”：“Using index”通常表示查询的某些部分可以通过索引来满足。“Using where”表示sale_timestamp > '2025-03-01'正在应用条件。

步骤 4：优化 SQL（或者更确切地说，确保最佳条件）

在使用索引的情况下，我们能否更好地处理COUNT(*)？如果查询可以完全通过索引完成，而无需触及实际的表数据，则称为“仅索引扫描”（或“覆盖索引”）。对于COUNT(*)，如果存在一个包含 的相对较小的索引sale_timestamp，数据库可能会使用它。

假设idx_sale_time只是 上的单列索引sale_timestamp。数据库仍然会将其用于范围扫描，但如果可以进行更具体的优化，它可能会从索引中读取比严格必要的更多数据。然而，对于一个包含日期范围扫描的简单示例，如果是最佳可用索引，COUNT(*)那么这个计划通常已经相当不错了。idx_sale_time

一种常见的情况是，如果上没有合适的索引COUNT(*)，就会强制进行全表扫描，从而导致速度变慢。如果输出显示，则主要的优化措施如下：sale_timestamptype: ALL

-- Ensure an index exists:
CREATE INDEX idx_sale_timestamp ON ProductSales(sale_timestamp);

然后，重新运行EXPLAIN原始COUNT(*)查询可能会显示类似于我们上面的假设输出的改进计划。

步骤 5 和 6：重新解释和分析（假设索引刚刚创建或确认仅索引扫描）

如果我们遇到这样的情况，idx_sale_time即是可以完全满足的复合索引的一部分COUNT(*)（例如，如果查询是COUNT(sale_timestamp)并且sale_timestamp已被索引），则该Extra列可能只显示“使用索引”。

步骤7：评估优化效果

目标是确保类型高效（例如，范围或索引，而不是全部），并且 Extra 列指示最佳索引使用情况（例如，如果适用，则在仅索引扫描时显示“使用索引”）。行数估计值也应尽可能低。

案例研究 2：优化多表连接和聚合

让我们考虑一个涉及连接的更复杂的场景。

场景设置：

一个在线学习平台有这些表：

  • Users（存储用户信息）：

  • user_id（INT，主键）

  • user_name（VARCHAR）

  • registration_date（日期）

  • CourseCompletions（存储用户完成课程的记录）：

  • completion_id（INT，主键）

  • user_id（INT，用户的外键）

  • course_id（INT）

  • completion_date（日期）

问题：

我们需要查找所有用户的姓名以及他们在 2024 年完成的课程数量。

原始 SQL 查询：

SELECT
    u.user_name,
    COUNT(cc.course_id) AS courses_completed_2024
FROM
    Users u
JOIN
    CourseCompletions cc ON u.user_id = cc.user_id
WHERE
    cc.completion_date >= '2024-01-01' AND cc.completion_date <= '2024-12-31'
GROUP BY
    u.user_name;

步骤 1：用于EXPLAIN分析查询

EXPLAIN SELECT
    u.user_name,
    COUNT(cc.course_id) AS courses_completed_2024
FROM
    Users u
JOIN
    CourseCompletions cc ON u.user_id = cc.user_id
WHERE
    cc.completion_date >= '2024-01-01' AND cc.completion_date <= '2024-12-31'
GROUP BY
    u.user_name;

第 2 步：分析EXPLAIN输出（假设的初始输出）

+----+-------------+-------------------+------+-----------------------------------+-------------+---------+--------------+-------+----------+-------------------------------+
| id | select_type | table             | type | possible_keys                     | key         | key_len | ref          | rows  | filtered | Extra                         |
+----+-------------+-------------------+------+-----------------------------------+-------------+---------+--------------+-------+----------+-------------------------------+
| 1  | SIMPLE      | u                 | ALL  | PRIMARY                           | NULL        | NULL    | NULL         | 50000 | 100.00   | Using temporary; Using filesort |
| 1  | SIMPLE      | cc                | ref  | idx_user_id,idx_completion_date | idx_user_id | 4       | db.u.user_id | 10    | 5.00     | Using where                   |
+----+-------------+-------------------+------+-----------------------------------+-------------+---------+--------------+-------+----------+-------------------------------+

案例研究2：优化EXPLAIN输出（假设）

步骤3：确定问题

  • 表u（用户）：type是ALL。这是对表的全表扫描Users，效率非常低，尤其是在表很大的情况下。

  • 表cc(CourseCompletions)：type正在ref使用idx_user_id。这对于连接条件来说很好，但WHEREon 子句在连接之后cc.completion_date应用，可能会应用于许多行。 for的值还表明，在连接之后，只有 5% 的行符合日期条件，这意味着做了很多不必要的工作。filtered5.00cc

  • Extra对于u：“Using temporary;Using filesort”表示为其创建一个临时表，GROUP BY然后进行排序，这是昂贵的。

步骤4：优化SQL

我们可以通过以下方式进行优化：

  • CourseCompletions在将表与连接之前对其进行过滤Users。这大大减少了连接中涉及的行数。

  • CourseCompletions(completion_date)确保和Users(user_id)（已PRIMARY建立索引）和上有适当的索引CourseCompletions(user_id)。 上的复合索引CourseCompletions(completion_date, user_id, course_id)可能非常有益。

优化 SQL 查询（使用子查询/派生表进行早期过滤）：

SELECT
    u.user_name,
    COUNT(filtered_cc.course_id) AS courses_completed_2024
FROM
    Users u
JOIN (
    SELECT user_id, course_id
    FROM CourseCompletions
    WHERE completion_date >= '2024-01-01' AND completion_date <= '2024-12-31'
) AS filtered_cc ON u.user_id = filtered_cc.user_id
GROUP BY
    u.user_name;

（确保在 和上*CourseCompletions* 有索引 以达到最佳效果。复合索引对于子查询来说是理想的）。*completion_date* *user_id* *(completion_date, user_id)*

步骤 5：重新运行EXPLAIN优化查询

EXPLAIN SELECT
    u.user_name,
    COUNT(filtered_cc.course_id) AS courses_completed_2024
FROM
    Users u
JOIN (
    SELECT user_id, course_id
    FROM CourseCompletions
    WHERE completion_date >= '2024-01-01' AND completion_date <= '2024-12-31'
) AS filtered_cc ON u.user_id = filtered_cc.user_id
GROUP BY
    u.user_name;

步骤6：分析优化EXPLAIN输出（假设）

+----+-------------+-------------------+--------+-----------------------------------+---------------------+---------+---------------------+------+----------+------------------------------------+
| id | select_type | table             | type   | possible_keys                     | key                 | key_len | ref                 | rows | filtered | Extra                              |
+----+-------------+-------------------+--------+-----------------------------------+---------------------+---------+---------------------+------+----------+------------------------------------+
| 1  | PRIMARY     | <derived2>        | ALL    | NULL                              | NULL                | NULL    | NULL                | 2000 | 100.00   | Using temporary; Using filesort    |
| 1  | PRIMARY     | u                 | eq_ref | PRIMARY                           | PRIMARY             | 4       | filtered_cc.user_id | 1    | 100.00   |                                    |
| 2  | DERIVED     | CourseCompletions | range  | idx_completion_date,idx_user_id   | idx_completion_date | 5       | NULL                | 2000 | 100.00   | Using where; Using index condition |
+----+-------------+-------------------+--------+-----------------------------------+---------------------+---------+---------------------+------+----------+------------------------------------+

（注意：派生表的具体计划可能会有所不同。关键是 *CourseCompletions* 首先进行过滤。）

步骤7：评估优化效果

  • 子查询（派生表filtered_cc）现在CourseCompletions使用idx_completion_date（range扫描）进行过滤，从而显著减少行数（rows: 2000而不是首先可能加入所有 500,000 个完成）。

  • Users( u) 和较小结果集之间的连接filtered_cc现在更有效率。u可以有效地使用其PRIMARY键 ( type: eq_ref)。

  • GROUP BY u.user_name由于ifu.user_name语句未建立索引，或者连接顺序导致分组数据未排序，因此“使用临时表；使用文件排序”错误可能仍然存在。进一步的优化可能涉及建立索引u.user_name或确保连接顺序允许GROUP BY使用索引。

通过这些步骤，我们分析并优化了原始查询，提升了查询效率。在实际应用中，通常需要根据具体的数据库结构和数据分布进行更多的迭代和微调。

使用 Chat2DB 简化 SQL 优化

理解EXPLAIN执行计划是一项至关重要的技能，但筛选复杂的输出并手动迭代优化可能非常耗时。这时，现代数据库工具就能大显身手了。

Chat2DB（https://chat2db.ai）是一个智能的、由人工智能驱动的数据库客户端，旨在简化您与各种数据库（如 MySQL、PostgreSQL、Oracle、SQL Server 等）的交互。

想象一下，你的 SQL 任务有一个副驾驶：

  • 人工智能查询辅助：从自然语言生成复杂的 SQL，获取优化现有查询的建议，甚至要求以更简单的术语解释查询计划。

  • 直观的EXPLAIN执行：EXPLAIN直接在界面中轻松运行查询并查看结果。（未来版本甚至可能提供可视化的计划分析！）

  • 无缝数据库管理：连接到多个数据库，管理模式，并以用户友好的体验执行查询。

通过集成 AI 辅助，Chat2DB 可以帮助您更有效地应用本文讨论的原则，更快地识别瓶颈，并最终编写出更优、更高效的 SQL。它能够帮助经验丰富的 DBA 和 SQL 优化新手开发人员提升数据库效率。



【声明】内容源于网络
0
0
索引目录
索引目录是一家专注于医疗、技术开发、物联网应用等领域的创新型公司。我们致力于为客户提供高质量的服务和解决方案，推动技术与行业发展。
内容 444
粉丝 0
索引目录 索引目录是一家专注于医疗、技术开发、物联网应用等领域的创新型公司。我们致力于为客户提供高质量的服务和解决方案，推动技术与行业发展。
总阅读12
粉丝0
内容444