如何在SQL中分析查询性能？EXPLAIN PLAN的解读方法-SQL-PHP中文网

EXPLAIN PLAN的核心指标包括操作类型、行数、成本和额外信息。操作类型揭示数据访问方式，如全表扫描或索引扫描；行数反映处理数据量，过高可能意味着效率问题；成本是资源消耗的相对估算，用于识别性能瓶颈；额外信息提示关键问题，如Using filesort或Using temporary，表明排序或临时表使用，需优化索引或查询结构。

如何在sql中分析查询性能？explain plan的解读方法

EXPLAIN PLAN

登录后复制

，在我看来，它是SQL性能调优的“X光片”。它能清晰地揭示数据库引擎是如何执行你的查询语句的，从数据读取方式、连接类型到排序操作，所有内部细节一览无余。理解它的输出，是识别查询瓶颈、进而优化SQL语句最直接、最有效的方法。

解决方案

要分析SQL查询性能，

EXPLAIN PLAN

登录后复制

无疑是我们的首选工具。它的核心作用就是模拟执行你的查询，然后输出一个“执行计划”——这就像是数据库内部的施工蓝图。你不需要实际运行查询，就能看到数据库打算怎么干活。

具体来说，当你对一个SQL语句执行

EXPLAIN PLAN

登录后复制

（不同数据库的语法略有差异，例如MySQL是

EXPLAIN SELECT ...

登录后复制

，PostgreSQL是

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

登录后复制

，Oracle是

EXPLAIN PLAN FOR SELECT ...

登录后复制

），它会返回一个表格或树状结构，里面包含了：

操作类型 (Operation Type)：数据库在每一步执行了什么动作，比如全表扫描（
```
TABLE ACCESS FULL
```
登录后复制
）、索引扫描（
```
INDEX SCAN
```
登录后复制
）、哈希连接（
```
HASH JOIN
```
登录后复制
）、嵌套循环连接（
```
NESTED LOOPS
```
登录后复制
）、排序（
```
SORT
```
登录后复制
）等等。这是理解查询行为的基础。
对象 (Object)：这个操作是针对哪个表或索引进行的。
行数 (Rows)：数据库估计此操作会处理的行数。这个数字非常关键，如果估计值与实际值相差甚远，可能意味着统计信息过时，或者查询计划选择有误。
成本 (Cost)：一个相对的数值，表示完成此操作所需的资源消耗。这个值本身不是绝对时间，但它能让你比较不同操作的相对开销，并识别出最耗时的部分。
访问方式 (Access Method)：是全表扫描、索引范围扫描还是唯一索引查找？这直接关系到效率。
额外信息 (Extra/Predicate Information)：这里通常会给出一些重要的提示，比如是否使用了临时表（
```
Using temporary
```
登录后复制
）、是否进行了文件排序（
```
Using filesort
```
登录后复制
）、或者哪些过滤条件被应用了。

解读时，我们通常会从顶层操作开始，层层深入，找出那些

Rows

登录后复制

高、

Cost

登录后复制

高，或者出现了

Using filesort

登录后复制

、

Using temporary

登录后复制

等“坏味道”的操作。这些往往就是优化的切入点。比如，看到一个大表的全表扫描，而你的

WHERE

登录后复制

条件本可以利用索引，那索引优化就呼之欲出了。或者，一个

ORDER BY

登录后复制

操作导致了

Using filesort

登录后复制

，那可能就需要为排序字段创建索引。

-- MySQL示例
EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND order_date > '2023-01-01' ORDER BY order_total DESC;

-- PostgreSQL示例
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 100 AND order_date > '2023-01-01' ORDER BY order_total DESC;

登录后复制

通过反复试验和观察

EXPLAIN PLAN

登录后复制

的输出，你会逐渐培养出一种直觉，能够快速定位问题并提出有效的优化方案。这就像医生看X光片，经验越多，诊断越准。

EXPLAIN PLAN的核心指标有哪些，它们意味着什么？

当我们拿到

EXPLAIN PLAN

登录后复制

的输出时，面对一堆数字和术语，初学者可能会感到有些迷茫。其实，有几个核心指标是我们在分析时必须抓住的，它们就像是侦探手中的关键线索。

首先是

type

登录后复制

(MySQL) 或
Node Type
登录后复制
(PostgreSQL/Oracle中的操作类型)。这直接告诉我们数据库在做什么。比如：

```
ALL
```
登录后复制
(MySQL) /
```
Seq Scan
```
登录后复制
(PostgreSQL) /
```
TABLE ACCESS FULL
```
登录后复制
(Oracle)：这通常意味着全表扫描。如果你的表很大，且
```
WHERE
```
登录后复制
条件筛选性强，这往往是个性能瓶颈，因为数据库不得不读取所有行来找到匹配项。
```
index_scan
```
登录后复制
(MySQL) /
```
INDEX SCAN
```
登录后复制
(PostgreSQL) /
```
INDEX RANGE SCAN
```
登录后复制
(Oracle)：这是索引扫描。通常比全表扫描高效，因为它只读取索引范围内的部分数据。
```
ref
```
登录后复制
(MySQL) /
```
Index Only Scan
```
登录后复制
(PostgreSQL) /
```
INDEX UNIQUE SCAN
```
登录后复制
(Oracle)：更高效的索引使用方式，可能只通过索引就能获取所有所需数据，或者通过唯一索引进行精确查找。
```
JOIN
```
登录后复制
类型，如
```
Nested Loop Join
```
登录后复制
,
```
HASH JOIN
```
登录后复制
,
```
Merge Join
```
登录后复制
：这些是表连接的方式。不同的连接类型在不同数据量和索引条件下表现各异。

其次是

Rows

登录后复制

(MySQL) /
Rows Removed by Filter
登录后复制
(PostgreSQL) /
Rows
登录后复制
(Oracle)。这个指标代表了数据库估计当前操作会处理的行数。如果一个操作的

Rows

登录后复制

值非常大，即使它的

type

登录后复制

看起来还行，也可能意味着它在处理大量数据，增加了整体开销。特别是当

Rows

登录后复制

与实际返回的行数相差巨大时，可能意味着统计信息不准确，或者查询优化器做出了错误的判断。

然后是

Cost

登录后复制

(MySQL/PostgreSQL) /
Cost
登录后复制
(Oracle)。这是一个估算的相对成本值，它考虑了I/O和CPU的开销。

Cost

登录后复制

值越低，通常意味着操作越高效。在复杂的查询计划中，我们应该特别关注那些

Cost

登录后复制

值异常高的节点，它们往往是性能热点。值得注意的是，

Cost

登录后复制

是累积的，子操作的

Cost

登录后复制

会累加到父操作上。

再来是

Extra

登录后复制

(MySQL) /
Actual Loops
登录后复制
,
Actual Time
登录后复制
(PostgreSQL
ANALYZE
登录后复制
输出) /
Predicate Information
登录后复制
(Oracle)。这部分提供了非常重要的附加信息，有时甚至是“警报”。

```
Using filesort
```
登录后复制
(MySQL)：这意味着数据库需要将结果集写入磁盘进行排序，而不是在内存中完成，通常因为缺少适当的索引。这是个严重的性能问题。
```
Using temporary
```
登录后复制
(MySQL)：数据库创建了临时表来处理查询，比如
```
GROUP BY
```
登录后复制
或
```
DISTINCT
```
登录后复制
操作。这也通常是效率低下的标志。
```
Actual Time
```
登录后复制
(PostgreSQL
```
ANALYZE
```
登录后复制
)：这是
```
EXPLAIN ANALYZE
```
登录后复制
独有的，它显示了实际执行该操作所花费的时间。这比估算的
```
Cost
```
登录后复制
更真实，能帮助我们验证优化效果。
```
Filtered
```
登录后复制
(MySQL)：表示此操作中，有多少百分比的行被
```
WHERE
```
登录后复制
条件过滤掉了。如果
```
Filtered
```
登录后复制
值很低，说明条件筛选性差，或者索引未能有效利用。

理解这些核心指标，就像掌握了数据库的“诊断语言”，能帮助我们快速定位到查询的症结所在。

面对复杂的查询计划，我应该从哪里入手进行优化？

复杂的查询计划，往往像一张错综复杂的地图，让人无从下手。但别急，我们可以遵循一些经验法则，逐步剥茧抽丝，找出优化的突破口。

我的习惯是，首先寻找最“重”的操作。这个“重”体现在几个方面：

蓝心千询

蓝心千询是vivo推出的一个多功能AI智能助手

查看详情

高成本节点：在计划树中，找到
```
Cost
```
登录后复制
值最高的那些节点。它们通常是瓶颈所在。
全表扫描（Full Table Scan）：如果你的查询涉及到大表，并且
```
EXPLAIN PLAN
```
登录后复制
显示
```
TABLE ACCESS FULL
```
登录后复制
或
```
Seq Scan
```
登录后复制
，这几乎总是第一个需要审视的地方。问问自己：这个全表扫描是必要的吗？我的
```
WHERE
```
登录后复制
条件能否通过索引来加速？
大行数处理：观察
```
Rows
```
登录后复制
指标。如果某个操作需要处理成千上万甚至上百万行数据，即使它最终只返回少量结果，也可能因为中间过程的数据量过大而效率低下。特别是嵌套循环连接（
```
NESTED LOOPS
```
登录后复制
）中，如果内层循环的驱动表行数非常大，那么整个连接的开销会呈指数级增长。

其次，警惕“额外信息”中的红旗。

Extra

登录后复制

字段（MySQL）或类似的提示（PostgreSQL/Oracle）常常会直接指出问题：

Using filesort
登录后复制
：这是个明确的信号，表明数据库在磁盘上进行了排序。通常，为
```
ORDER BY
```
登录后复制
或
```
GROUP BY
```
登录后复制
涉及的列创建复合索引，可以避免这种情况。
Using temporary
登录后复制
：这意味着数据库创建了临时表。这通常发生在
```
GROUP BY
```
登录后复制
、
```
DISTINCT
```
登录后复制
或某些复杂的子查询中。优化思路通常是尝试重写查询，或者确保相关列有索引。
Using where
登录后复制
但没有
Using index
登录后复制
或
Using index condition
登录后复制
：这表示数据库使用了
```
WHERE
```
登录后复制
条件来过滤数据，但没有通过索引来加速这个过滤过程，可能仍然进行了全表扫描或者索引扫描后的大量数据过滤。

再次，关注连接（JOIN）操作。连接的顺序和类型对性能影响巨大。

驱动表选择：在多表连接中，选择一个经过
```
WHERE
```
登录后复制
条件筛选后行数最少的表作为驱动表（通常是外层循环的表），可以有效减少内层循环的执行次数。
连接类型：
- ```
NESTED LOOPS
```
  登录后复制
  ：对于小数据集或索引良好的连接非常高效，但如果外层表或内层表行数过多，性能会急剧下降。
- ```
HASH JOIN
```
  登录后复制
  ：适用于连接大数据集，特别是当其中一个表（构建表）能完全放入内存时。
- ```
Merge Join
```
  登录后复制
  ：要求连接列已排序，如果数据已经排序，则非常高效。

最后，考虑索引的优化。这通常是最直接且有效的优化手段。

缺失的索引：通过
```
EXPLAIN PLAN
```
登录后复制
发现的全表扫描、
```
Using filesort
```
登录后复制
等，往往暗示着某些关键列缺少索引。
复合索引：对于
```
WHERE
```
登录后复制
条件中包含多个列，或者
```
WHERE
```
登录后复制
与
```
ORDER BY
```
登录后复制
/
```
GROUP BY
```
登录后复制
同时存在的场景，复合索引能发挥巨大作用。但要注意索引列的顺序，通常将等值查询的列放在前面，范围查询的列放在后面。
覆盖索引（Covering Index）：如果一个索引包含了查询所需的所有列（包括
```
SELECT
```
登录后复制
列表和
```
WHERE
```
登录后复制
条件中的列），那么数据库就无需回表查询数据，直接从索引中获取结果，效率极高。

优化是一个迭代的过程，每次修改后都要重新运行

EXPLAIN PLAN

登录后复制

来验证效果。

不同数据库系统中的EXPLAIN PLAN有什么细微差别？

尽管

EXPLAIN PLAN

登录后复制

的核心理念——揭示查询执行计划——在所有关系型数据库中是共通的，但具体到语法、输出格式和某些特有指标上，不同数据库系统确实存在一些细微的差别，这要求我们进行区分。

MySQL的

EXPLAIN

登录后复制

：

MySQL的

EXPLAIN

登录后复制

输出通常是一个表格，相对简洁直观。

语法：
```
EXPLAIN SELECT ...
```
登录后复制
核心列：
- ```
id
```
  登录后复制
  ：查询中每个操作的唯一标识符。
- ```
select_type
```
  登录后复制
  ：查询类型（
```
SIMPLE
```
  登录后复制
  ,
```
PRIMARY
```
  登录后复制
  ,
```
SUBQUERY
```
  登录后复制
  ,
```
DERIVED
```
  登录后复制
  等）。
- ```
table
```
  登录后复制
  ：操作的表名。
- ```
type
```
  登录后复制
  ：连接类型/访问类型（
```
ALL
```
  登录后复制
  ,
```
index
```
  登录后复制
  ,
```
range
```
  登录后复制
  ,
```
ref
```
  登录后复制
  ,
```
eq_ref
```
  登录后复制
  ,
```
const
```
  登录后复制
  等）。
```
ALL
```
  登录后复制
  代表全表扫描，
```
const
```
  登录后复制
  代表常量查找，效率最高。
- ```
possible_keys
```
  登录后复制
  ：可能用到的索引。
- ```
key
```
  登录后复制
  ：实际使用的索引。
- ```
key_len
```
  登录后复制
  ：使用索引的长度。
- ```
ref
```
  登录后复制
  ：表示哪些列或常量被用于查找索引值。
- ```
Rows
```
  登录后复制
  ：MySQL估计的为找到所需行而必须读取的行数。
- ```
Filtered
```
  登录后复制
  ：表示通过表条件过滤的行百分比。
- ```
Extra
```
  登录后复制
  ：附加信息，如
```
Using filesort
```
  登录后复制
  ,
```
Using temporary
```
  登录后复制
  ,
```
Using where
```
  登录后复制
  ,
```
Using index
```
  登录后复制
  (覆盖索引)等。
特点：
```
Extra
```
登录后复制
列提供了大量关键的优化线索。
```
Rows
```
登录后复制
和
```
Filtered
```
登录后复制
是估算值，可能与实际不符。

PostgreSQL的

EXPLAIN

登录后复制

：

PostgreSQL的

EXPLAIN

登录后复制

输出通常是树状结构，更详细地展示了操作的嵌套关系。它有更强大的选项来获取实际执行信息。

语法：
- ```
EXPLAIN SELECT ...
```
  登录后复制
  ：只显示估算计划。
- ```
EXPLAIN (ANALYZE) SELECT ...
```
  登录后复制
  ：实际执行查询并显示实际的执行时间、行数等。这对于验证估算是否准确至关重要。
- ```
EXPLAIN (ANALYZE, BUFFERS) SELECT ...
```
  登录后复制
  ：在
```
ANALYZE
```
  登录后复制
  的基础上，额外显示缓冲区使用情况，有助于分析I/O瓶颈。
- ```
EXPLAIN (FORMAT JSON) SELECT ...
```
  登录后复制
  ：以JSON格式输出，便于程序解析。
核心指标（
```
ANALYZE
```
登录后复制
模式下）：
- ```
Node Type
```
  登录后复制
  ：操作类型（
```
Seq Scan
```
  登录后复制
  ,
```
INDEX SCAN
```
  登录后复制
  ,
```
HASH JOIN
```
  登录后复制
  ,
```
Nested Loop
```
  登录后复制
  等）。
- ```
Plan Rows
```
  登录后复制
  ：优化器估算的行数。
- ```
Actual Rows
```
  登录后复制
  ：实际处理的行数。
- ```
Actual Time
```
  登录后复制
  ：实际花费的时间（启动时间..总时间）。
- ```
Loops
```
  登录后复制
  ：该节点实际执行的次数。
- ```
Buffers
```
  登录后复制
  ：缓冲区使用情况（共享命中、读、写等）。
- ```
Filter
```
  登录后复制
  /
```
Join Filter
```
  登录后复制
  ：具体的过滤或连接条件。
特点：
```
ANALYZE
```
登录后复制
选项是其亮点，能提供真实的运行时数据，帮助我们对比估算与实际情况的差异，从而判断统计信息是否需要更新。
```
Buffers
```
登录后复制
信息对于分析I/O瓶颈非常有帮助。

Oracle的

EXPLAIN PLAN

登录后复制

：

Oracle的

EXPLAIN PLAN

登录后复制

通常需要两步操作：先生成计划，再查询计划表。

语法：
- ```
EXPLAIN PLAN FOR SELECT ...;
```
  登录后复制
  ：将查询计划存储到
```
PLAN_TABLE
```
  登录后复制
  中。
- ```
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
```
  登录后复制
  ：从
```
PLAN_TABLE
```
  登录后复制
  中格式化输出计划。
- ```
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'ALLSTATS LAST'));
```
  登录后复制
  ：在查询实际运行后，查看其统计信息。
核心列：
- ```
id
```
  登录后复制
  ：操作的唯一标识符。
- ```
Operation
```
  登录后复制
  ：操作类型（
```
TABLE ACCESS FULL
```
  登录后复制
  ,
```
INDEX RANGE SCAN
```
  登录后复制
  ,
```
NESTED LOOPS
```
  登录后复制
  ,
```
HASH JOIN
```
  登录后复制
  等）。
- ```
Name
```
  登录后复制
  ：操作的对象名（表名、索引名）。
- ```
Rows
```
  登录后复制
  ：估算的行数。
- ```
Bytes
```
  登录后复制
  ：估算的字节数。
- ```
Cost
```
  登录后复制
  ：估算的成本。
- ```
Time
```
  登录后复制
  ：估算的时间。
- ```
Predicate Information
```
  登录后复制
  ：详细的过滤条件和访问路径。
特点：Oracle的
```
EXPLAIN PLAN
```
登录后复制
与
```
DBMS_XPLAN
```
登录后复制
包结合使用，功能强大且输出详细。
```
ALLSTATS LAST
```
登录后复制
选项可以查看最后一次执行的真实统计信息，这与PostgreSQL的
```
ANALYZE
```
登录后复制
类似，对于深入分析至关重要。