跳到主要内容

执行计划分析

在数据库查询优化中，执行计划是数据库管理系统（DBMS）为执行 SQL 查询而生成的详细步骤。通过分析执行计划，我们可以了解数据库如何执行查询，从而发现潜在的性能瓶颈并进行优化。

什么是执行计划？​

执行计划是数据库引擎为执行 SQL 查询而生成的操作序列。它描述了数据库如何访问数据、使用索引、连接表以及执行其他操作。通过查看执行计划，我们可以了解查询的执行成本、资源消耗以及潜在的性能问题。

为什么需要分析执行计划？​

  1. 性能优化：通过分析执行计划，可以发现查询中的低效操作，例如全表扫描或不必要的排序。
  2. 索引使用：了解数据库是否使用了正确的索引，或者是否需要创建新的索引。
  3. 资源消耗：评估查询对 CPU、内存和 I/O 的影响，避免资源浪费。

如何获取执行计划？​

大多数数据库管理系统都提供了获取执行计划的工具或命令。以下是一些常见数据库的示例：

  • MySQL：使用 EXPLAIN 命令。
  • PostgreSQL：使用 EXPLAIN 或 EXPLAIN ANALYZE 命令。
  • SQL Server：使用 SET SHOWPLAN_ALL ON 或 SET STATISTICS PROFILE ON。

示例：MySQL 中的执行计划​

假设我们有一个简单的查询：

sql
SELECT * FROM users WHERE age > 30;

我们可以使用 EXPLAIN 命令来查看其执行计划：

sql
EXPLAIN SELECT * FROM users WHERE age > 30;

输出可能如下：

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEusersrangeage_indexage_index4NULL100Using where

解读执行计划​

  • id：查询的标识符。如果查询包含子查询，每个子查询会有不同的 id。
  • select_type：查询的类型，例如 SIMPLE 表示简单查询。
  • table：查询涉及的表。
  • type：访问类型，例如 range 表示范围扫描。
  • possible_keys：可能使用的索引。
  • key：实际使用的索引。
  • key_len：使用的索引长度。
  • ref：与索引比较的列或常量。
  • rows：估计需要扫描的行数。
  • Extra：额外的信息，例如 Using where 表示使用了 WHERE 条件。

执行计划中的关键概念​

1. 访问类型（Access Type）​

访问类型描述了数据库如何访问表中的数据。常见的访问类型包括：

  • ALL：全表扫描，性能最差。
  • index：索引扫描，比全表扫描稍好。
  • range：范围扫描，使用索引查找特定范围的数据。
  • ref：使用非唯一索引查找数据。
  • eq_ref：使用唯一索引查找数据。
  • const：使用常量查找数据，性能最佳。

2. 索引使用​

索引是提高查询性能的关键工具。执行计划中的 key 列显示了实际使用的索引。如果 key 列为空，表示查询没有使用索引，可能需要创建新的索引。

3. 行数估计（Rows）​

rows 列显示了数据库估计需要扫描的行数。如果这个数字很大，可能需要优化查询或索引。

4. 额外信息（Extra）​

Extra 列提供了额外的执行信息，例如：

  • Using where：使用了 WHERE 条件过滤数据。
  • Using index：查询只使用了索引，没有访问表数据。
  • Using temporary：使用了临时表，可能会影响性能。
  • Using filesort：使用了文件排序，可能会影响性能。

实际案例：优化查询​

假设我们有一个包含 100 万条记录的 orders 表，我们需要查询所有金额大于 1000 的订单：

sql
SELECT * FROM orders WHERE amount > 1000;

初始执行计划​

sql
EXPLAIN SELECT * FROM orders WHERE amount > 1000;

输出可能如下：

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEordersALLNULLNULLNULLNULL1000000Using where

分析​

  • type 为 ALL，表示进行了全表扫描。
  • rows 为 1000000，表示需要扫描所有行。
  • key 为 NULL，表示没有使用索引。

优化步骤​

  1. 创建索引：在 amount 列上创建索引。
sql
CREATE INDEX idx_amount ON orders(amount);
  1. 再次查看执行计划
sql
EXPLAIN SELECT * FROM orders WHERE amount > 1000;

输出可能如下：

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEordersrangeidx_amountidx_amount4NULL50000Using where

优化结果​

  • type 变为 range，表示使用了范围扫描。
  • rows 减少到 50000，表示只需要扫描部分行。
  • key 为 idx_amount，表示使用了新创建的索引。

总结​

执行计划分析是数据库查询优化的重要工具。通过理解执行计划中的各个部分，我们可以发现查询中的性能瓶颈，并通过创建索引、优化查询等方式提高查询效率。

附加资源​

练习​

  1. 在你的数据库中运行一个查询，并使用 EXPLAIN 查看其执行计划。
  2. 尝试创建一个索引，并观察执行计划的变化。
  3. 分析一个复杂查询的执行计划，找出可能的优化点。

通过不断实践，你将能够熟练地使用执行计划分析工具，优化数据库查询性能。