学习分析explain执行查询SQL的报告（MySQL&PostgreSQL为例）

原创已于 2024-01-19 17:22:16 修改

· 1.2k 阅读

26 ·

版权

文章标签：

#学习 #sql #mysql #postgresql

于 2024-01-19 17:12:43 首次发布

通过分析SQL查询，可以发现和解决导致查询性能瓶颈的问题，优化索引使用、减少资源消耗，提高用户体验，避免潜在瓶颈，实现成本效益和系统可扩展性，确保数据一致性和准确性。这使得数据库系统更高效、稳定、易维护，为长期项目的成功提供基础。本篇文章会从MySQL和PostgreSQL去学习阅读SQL分析报告。

MySQL

在MySQL中，使用EXPLAIN语句可以分析MySQL查询语句的执行计划，以便优化查询性能。EXPLAIN语句的一般语法如下：

EXPLAIN SELECT * FROM your_table WHERE your_condition;

执行EXPLAIN语句后，MySQL会返回一张表，该表包含了关于查询执行计划的详细信息，其中表的行数代表了查询执行计划中的不同步骤或操作，每一行都代表了查询中的一个特定部分，如一个表的扫描、一个连接操作，或是一个子查询的处理。以下是一些常见的列及其含义：

id: 查询的唯一标识符
- 在单个简单查询中，通常这个值为1。在复杂查询中，如包含子查询、联合查询（UNION）或是多表连接的查询，id的值可以帮助你理解查询的执行顺序和结构
- 相同的id值表示这些操作是在同一个级别执行的。例如，在JOIN操作中，参与相同JOIN的表会有相同的id值。
select_type: 查询的类型（如 SIMPLE, PRIMARY, SUBQUERY 等）
- SIMPLE 表示一个简单的SELECT（不使用UNION或子查询的情况）
- PRIMARY 一个复杂的查询中（比如涉及到 UNION 或子查询的情况），最外层的查询被标记为 PRIMARY
- SUBQUERY 查询中包含子查询时，子查询的 select_type 是 SUBQUERY
table: 正在访问的表。
type: 表示连接类型（如ALL、index、range等）。
- system 表只有一行（等同于系统表）。这是可能的最好的 type 值，查询速度非常快
- const 表示通过索引一次就能找到一行数据，通常用于比较主键或唯一索引的等值查询
- eq_ref 在使用主键或唯一索引作为连接条件时
- ref 访问类型只返回匹配某个单个值的行，它使用非唯一或非主键索引
- fulltext 全文索引
- unique_subquery 在 IN 子句中使用的子查询将被优化为一个唯一查询
- index_subquery 类似于 unique_subquery，但用于非唯一索引
- range 只检索给定范围内的行，使用索引来选择行
- index 表示全索引扫描，比全表扫描快，但不如 range 类型
- all 表示全表扫描，这通常是性能最差的情况，应尽可能避免
possible_keys: 可能用于查询的索引。
key: 实际使用的索引。
key_len: 索引使用的字节数。
ref: 显示索引如何被使用，如列名或常量。
rows: 估计查询需要检查的行数。
Extra: 额外的信息（如Using where、Using index等）
- Using filesort 表明 MySQL 将使用一个外部索引排序，而不是按索引顺序进行读取。这通常发生在 ORDER BY 查询中，指定的排序无法通过索引直接完成
- Using temporary 表示 MySQL 需要使用临时表来存储结果，这通常发生在排序和分组查询中（例如，含有 GROUP BY、DISTINCT、ORDER BY 或多表 JOIN）
- Using index 查询能够通过只访问索引来获取所需的数据，无需读取实际的表行。这通常是性能较好的情况
- Using where 指 MySQL 在存储引擎层面之外进行了额外的过滤

PostgreSQL

在 PostgreSQL 中，可以使用 EXPLAIN 和 EXPLAIN ANALYZE 语句来分析 SQL 查询。这些工具对于理解查询如何执行以及如何优化查询性能至关重要。
使用 EXPLAIN 和 EXPLAIN ANALYZE

EXPLAIN：显示查询的执行计划，即数据库如何执行查询。但它不会实际执行查询。语法：

EXPLAIN SELECT * FROM schema.your_table WHERE your_condition;

EXPLAIN ANALYZE：这不仅显示执行计划，还会实际执行查询v，并报告每一部分的执行时间和其他详细信息。语法：

EXPLAIN ANALYZE SELECT * FROM schema.your_table WHERE your_condition;

操作类型
执行计划的每一行通常代表一个单独的操作或步骤。常见的操作类型包括：

Seq Scan（顺序扫描）：数据库对表进行了全扫描；
Index Scan（索引扫描）：数据库使用索引来找到行；
Index Only Scan（仅索引扫描）：查询只需要访问索引，不需要访问表数据；
Bitmap Heap Scan：使用位图索引扫描，先通过索引找到行的位置，然后再访问表；
Nested Loop（嵌套循环）：一种连接表的方式，对于每个外表的行，遍历内表；
Hash Join（哈希连接）：使用哈希表进行表连接；
Sort（排序）：数据根据某些列进行排序；
成本和时间
每个操作会有一个成本估计（cost），通常有两个数字，第一个数字是启动成本，第二个是完成操作的总成本；实际时间（actual time）是操作实际执行的时间。
行数和循环次数
rows：预计（或实际）操作处理的行数。
loops：操作执行的次数，通常用于循环结构。
其他关键指标
Buffers：显示了查询过程中缓冲区的使用情况，有助于理解内存使用和 I/O 操作。
I/O 时间和 CPU 时间：有些执行计划还会显示 I/O 和 CPU 的使用时间。