TDSQL SQL执行计划分析

SQL执行计划是数据库优化器根据SQL语句生成的具体执行方案，包含了数据库如何执行SQL语句的详细信息，例如访问表的顺序、访问表的方式、连接表的方式、使用的索引、数据过滤条件、排序和分组方式以及预估的行数和成本等。

分析SQL执行计划是优化SQL性能的关键步骤，可以帮助理解SQL语句的执行过程、识别性能瓶颈和问题、评估索引使用情况、验证查询优化效果以及指导SQL语句和表结构优化。

TDSQL执行计划具有支持标准的EXPLAIN语法、提供详细的执行计划信息、支持多种执行计划格式（TEXT、JSON、TREE等）、包含预估成本和实际执行统计以及支持分布式执行计划分析等特点。

获取执行计划

基本语法

sql

-- 基本形式
EXPLAIN SELECT ...;

-- 格式化输出
EXPLAIN FORMAT=JSON SELECT ...;
EXPLAIN FORMAT=TREE SELECT ...;

-- 显示执行统计信息
EXPLAIN ANALYZE SELECT ...;

-- 显示分区信息
EXPLAIN PARTITIONS SELECT ...;

-- 显示扩展信息
EXPLAIN EXTENDED SELECT ...;

执行计划类型

类型	特点	使用场景
EXPLAIN	显示预估执行计划	快速分析SQL执行路径
EXPLAIN ANALYZE	显示实际执行计划和统计信息	精确分析SQL性能
EXPLAIN FORMAT=JSON	以JSON格式显示详细执行计划	需要详细执行计划信息时
EXPLAIN FORMAT=TREE	以树状结构显示执行计划	直观查看执行计划层次
EXPLAIN PARTITIONS	显示分区使用情况	分析分区表查询

示例

sql

-- 基本执行计划
EXPLAIN SELECT * FROM users WHERE id = 1;

-- JSON格式执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 1;

-- 实际执行计划和统计
EXPLAIN ANALYZE SELECT COUNT(*) FROM users WHERE create_time > '2023-01-01';

执行计划解读

执行计划基本结构

TDSQL执行计划包含以下核心信息：

id：执行顺序标识符
select_type：查询类型
table：访问的表
partitions：使用的分区
type：访问类型
possible_keys：可能使用的索引
key：实际使用的索引
key_len：使用的索引长度
ref：索引引用的列或常量
rows：预估的行数
filtered：过滤后的数据百分比
Extra：额外信息

select_type详解

select_type	描述
SIMPLE	简单查询，没有子查询或UNION
PRIMARY	主查询，外层查询
SUBQUERY	子查询，结果不依赖外层查询
DEPENDENT SUBQUERY	依赖子查询，结果依赖外层查询
DERIVED	派生表，FROM子句中的子查询
UNION	UNION中的第二个或后续查询
UNION RESULT	UNION的结果集
MATERIALIZED	物化子查询
LATERAL	横向子查询

type详解

type列表示访问表的方式，是评估执行计划性能的重要指标，从最优到最差依次为：

type	描述	性能
system	表只有一行数据（系统表）	最优
const	使用主键或唯一索引查询，返回一行	优秀
eq_ref	连接查询中，使用主键或唯一索引匹配	优秀
ref	使用非唯一索引查询，返回多行	良好
fulltext	全文索引查询	良好
ref_or_null	类似ref，但包含NULL值查询	良好
index_merge	使用多个索引合并查询	一般
unique_subquery	子查询中使用唯一索引	一般
index_subquery	子查询中使用非唯一索引	一般
range	范围查询（BETWEEN, IN, >, <等）	一般
index	全索引扫描	较差
ALL	全表扫描	最差

Extra详解

Extra列包含执行计划的额外信息，常见的值有：

Extra	描述	性能影响
Using index	使用覆盖索引，不需要回表	正面
Using where	使用WHERE子句过滤	中性
Using index condition	使用索引条件下推	正面
Using temporary	使用临时表	负面
Using filesort	使用文件排序	负面
Using join buffer	使用连接缓冲	负面
Using MRR	使用多范围读取优化	正面
Using sort_union	使用排序合并优化	中性
Using union	使用索引合并优化	中性
Using intersect	使用索引交集优化	中性
Start temporary	开始临时表	负面
End temporary	结束临时表	负面

执行计划分析实战

示例1：简单查询

sql

-- SQL语句
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 执行计划输出
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

分析：

使用主键索引（PRIMARY）
访问类型为const，性能优秀
预估返回1行数据
没有额外的性能开销

示例2：索引扫描

sql

-- SQL语句
EXPLAIN SELECT username FROM users WHERE status = 1;

-- 执行计划输出
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_status    | idx_status | 1       | const |  100 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+--------------------------+

分析：

使用了status列的索引（idx_status）
访问类型为ref，性能良好
使用覆盖索引（Using index），不需要回表
预估返回100行数据

示例3：全表扫描

sql

-- SQL语句
EXPLAIN SELECT * FROM users WHERE username LIKE '%test%';

-- 执行计划输出
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

分析：

没有使用索引，进行全表扫描（ALL）
预估扫描1000行，过滤后返回100行
性能较差，需要优化

示例4：连接查询

sql

-- SQL语句
EXPLAIN SELECT u.username, o.order_no FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 1;

-- 执行计划输出
+----+-------------+-------+------------+--------+---------------+------------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key        | key_len | ref             | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+------------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | ref    | PRIMARY,idx_status | idx_status | 1       | const           |  100 |   100.00 | NULL        |
|  1 | SIMPLE      | o     | NULL       | eq_ref | idx_user_id   | idx_user_id| 8       | test.u.id       |    5 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+------------+---------+-----------------+------+----------+-------------+

分析：

users表使用status索引，访问类型为ref
orders表使用user_id索引，访问类型为eq_ref
orders表使用覆盖索引（Using index）
预估返回100*5=500行数据

示例5：使用临时表和文件排序

sql

-- SQL语句
EXPLAIN SELECT u.username, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.username ORDER BY COUNT(o.id) DESC;

-- 执行计划输出
+----+-------------+-------+------------+--------+---------------+------------+---------+-----------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key        | key_len | ref             | rows | filtered | Extra                           |
+----+-------------+-------+------------+--------+---------------+------------+---------+-----------------+------+----------+---------------------------------+
|  1 | SIMPLE      | u     | NULL       | index  | PRIMARY       | PRIMARY    | 8       | NULL            | 1000 |   100.00 | Using index; Using temporary    |
|  1 | SIMPLE      | o     | NULL       | ref    | idx_user_id   | idx_user_id| 8       | test.u.id       |    5 |   100.00 | Using index; Using filesort     |
+----+-------------+-------+------------+--------+---------------+------------+---------+-----------------+------+----------+---------------------------------+

分析：

使用了临时表（Using temporary）存储分组结果
使用了文件排序（Using filesort）对结果排序
性能较差，需要优化

执行计划优化建议

1. 优化访问类型

避免全表扫描：为查询条件添加合适的索引
优化索引使用：确保查询条件能够使用索引
使用覆盖索引：减少回表操作

2. 优化连接查询

小表驱动大表：将小表放在JOIN的左边
使用合适的连接类型：根据数据量选择嵌套循环、哈希连接或合并连接
为连接列添加索引：提高连接效率

3. 优化排序和分组

避免不必要的排序：如果结果不需要排序，移除ORDER BY
使用索引排序：利用索引的有序性避免排序
优化GROUP BY：确保分组列有索引
避免在大结果集上排序：考虑分页或限制结果集大小

4. 优化子查询

考虑将子查询转换为连接查询
使用物化子查询：对于重复执行的子查询
避免相关子查询：相关子查询会逐行执行，性能较差

5. 优化临时表使用

减少临时表的使用：优化查询逻辑

调整临时表参数：

sql

-- 调整临时表内存大小
SET tmp_table_size = 64M;
SET max_heap_table_size = 64M;

使用内存临时表：避免磁盘临时表

6. 优化文件排序

使用索引排序：利用索引的有序性
减少排序数据量：只选择必要的列

调整排序参数：

sql

-- 调整排序缓冲区大小
SET sort_buffer_size = 8M;

执行计划高级分析

分布式执行计划

对于TDSQL分布式集群，执行计划会显示分布式执行的详细信息：

sql

EXPLAIN SELECT * FROM users WHERE id > 1000;

分布式执行计划包含以下额外信息：

数据分片信息
分布式算子（GATHER、SCATTER等）
网络传输成本
节点间数据交换

JSON格式执行计划

JSON格式提供更详细的执行计划信息：

sql

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;

JSON执行计划包含：

详细的成本估算
每个节点的执行信息
索引使用情况
过滤条件
执行顺序

实际执行统计

使用EXPLAIN ANALYZE获取实际执行统计：

sql

EXPLAIN ANALYZE SELECT * FROM users WHERE status = 1;

实际执行统计包含：

实际执行时间
实际扫描行数
实际返回行数
每个阶段的执行时间
内存使用情况

执行计划分析工具

内置工具

TDSQL监控控制台：提供可视化的执行计划分析
EXPLAIN命令：直接在命令行查看执行计划
performance_schema：分析SQL执行统计信息

第三方工具

MySQL Workbench：可视化执行计划分析
Navicat：提供执行计划可视化
Percona Toolkit：包含pt-visual-explain等工具
SQL诊断工具：例如SQL Advisor、SQL Tuning Advisor等

常见问题与解决方案

问题1：索引未被使用

原因：

查询条件不匹配索引前缀
索引选择性差
使用了函数或表达式
优化器认为全表扫描更高效

解决方案：

调整查询条件，匹配索引前缀
优化索引，提高选择性
避免在索引列上使用函数
使用FORCE INDEX提示（谨慎使用）

问题2：使用了临时表

原因：

GROUP BY或DISTINCT操作
UNION操作
复杂的JOIN查询

解决方案：

优化GROUP BY或DISTINCT逻辑
考虑使用索引避免临时表
调整临时表参数

问题3：使用了文件排序

原因：

ORDER BY列没有索引
ORDER BY与索引顺序不一致
复杂的排序条件

解决方案：

为ORDER BY列添加索引
调整查询顺序，匹配索引顺序
减少排序数据量

问题4：全表扫描

原因：

没有合适的索引
查询条件使用了LIKE '%xxx%'
查询条件使用了OR连接
优化器认为全表扫描更高效

解决方案：

添加合适的索引
优化LIKE查询，使用前缀匹配
考虑使用UNION替代OR
分析数据分布，调整优化器参数

最佳实践

1. 定期分析执行计划

对核心业务SQL定期分析执行计划
监控慢查询的执行计划
在版本升级或架构变更后重新分析

2. 结合实际执行统计

使用EXPLAIN ANALYZE获取实际执行情况
比较预估行数和实际行数
分析实际执行时间和成本

3. 考虑数据分布

了解表的数据分布情况
分析索引选择性
考虑数据倾斜问题

4. 综合优化

结合表结构、索引和SQL语句优化
考虑业务需求和数据增长
平衡读写性能
测试优化效果

5. 持续监控和调整

监控SQL性能变化
定期更新统计信息
根据业务变化调整索引
优化执行计划

常见问题（FAQ）

Q1: 如何判断执行计划的好坏？

A1: 判断执行计划好坏的主要依据包括：

访问类型（type）：从system到ALL，性能依次降低
扫描行数（rows）：扫描的行数越少越好
过滤比例（filtered）：过滤比例越高越好
额外信息（Extra）：避免Using temporary、Using filesort等
预估成本：成本越低越好

Q2: 为什么有时索引未被使用？

A2: 索引未被使用的原因可能包括：

查询条件不匹配索引前缀
索引选择性差，优化器认为全表扫描更高效
查询条件使用了函数或表达式
表数据量太小，全表扫描成本更低
统计信息过时，优化器做出错误判断

Q3: 如何优化GROUP BY和ORDER BY？

A3: 优化GROUP BY和ORDER BY的方法：

为GROUP BY和ORDER BY列添加索引
确保索引顺序与GROUP BY/ORDER BY顺序一致
避免在大结果集上排序
考虑使用覆盖索引
调整相关参数，如sort_buffer_size

Q4: 如何分析分布式执行计划？

A4: 分析分布式执行计划需要关注：

数据分片情况
分布式算子的使用
网络传输成本
节点间数据交换
每个节点的执行情况
整体执行时间分布

Q5: EXPLAIN和EXPLAIN ANALYZE有什么区别？

A5: EXPLAIN显示的是预估执行计划，包含优化器预估的行数和成本；而EXPLAIN ANALYZE显示的是实际执行计划，包含实际执行时间、扫描行数和其他统计信息。EXPLAIN ANALYZE会实际执行SQL语句，因此更准确，但会消耗资源。

Q6: 如何优化JOIN查询的执行计划？

A6: 优化JOIN查询执行计划的方法：

小表驱动大表，将小表放在JOIN左边
为连接列添加索引
选择合适的连接类型
减少JOIN的表数量
考虑使用子查询或临时表优化

Q7: 如何处理执行计划中的Using temporary和Using filesort？

A7: 处理Using temporary和Using filesort的方法：

优化查询逻辑，避免不必要的GROUP BY和ORDER BY
为相关列添加索引
调整临时表和排序缓冲区参数
考虑使用覆盖索引
减少排序和分组的数据量

Q8: 如何更新统计信息以获得准确的执行计划？

A8: 更新统计信息的方法：

使用ANALYZE TABLE命令：`ANALYZE TABLE table_name;
配置自动更新统计信息：`SET GLOBAL innodb_stats_auto_recalc = ON;
调整统计信息采样率：`SET GLOBAL innodb_stats_persistent_sample_pages = 20;

Q9: 如何使用索引提示影响执行计划？

A9: 使用索引提示的方法：

FORCE INDEX：强制使用指定索引
USE INDEX：建议使用指定索引
IGNORE INDEX：忽略指定索引

示例：`SELECT * FROM users FORCE INDEX (idx_status) WHERE status = 1;

注意：索引提示应谨慎使用，因为优化器通常比人工判断更准确。

Q10: 如何分析执行计划的成本估算？

A10: 分析执行计划成本估算的方法：

查看JSON格式执行计划中的cost_info部分
比较不同执行计划的成本差异
结合实际执行时间分析
调整成本模型参数（谨慎使用）

成本估算包含：

查询总成本（query_cost）
数据读取成本（read_cost）
执行成本（eval_cost）
网络传输成本（对于分布式查询）

TDSQL SQL执行计划分析 ​

获取执行计划 ​

基本语法 ​

执行计划类型 ​

示例 ​

执行计划解读 ​

执行计划基本结构 ​

select_type详解 ​

type详解 ​

Extra详解 ​

执行计划分析实战 ​

示例1：简单查询 ​

示例2：索引扫描 ​

示例3：全表扫描 ​

示例4：连接查询 ​

示例5：使用临时表和文件排序 ​

执行计划优化建议 ​

1. 优化访问类型 ​

2. 优化连接查询 ​

3. 优化排序和分组 ​

4. 优化子查询 ​

5. 优化临时表使用 ​

6. 优化文件排序 ​

执行计划高级分析 ​

分布式执行计划 ​

JSON格式执行计划 ​

实际执行统计 ​

执行计划分析工具 ​

内置工具 ​

第三方工具 ​

常见问题与解决方案 ​

问题1：索引未被使用 ​

问题2：使用了临时表 ​

问题3：使用了文件排序 ​

问题4：全表扫描 ​

最佳实践 ​

1. 定期分析执行计划 ​

2. 结合实际执行统计 ​

3. 考虑数据分布 ​

4. 综合优化 ​

5. 持续监控和调整 ​

常见问题（FAQ） ​

Q1: 如何判断执行计划的好坏？ ​

Q2: 为什么有时索引未被使用？ ​

Q3: 如何优化GROUP BY和ORDER BY？ ​

Q4: 如何分析分布式执行计划？ ​

Q5: EXPLAIN和EXPLAIN ANALYZE有什么区别？ ​

Q6: 如何优化JOIN查询的执行计划？ ​

Q7: 如何处理执行计划中的Using temporary和Using filesort？ ​

Q8: 如何更新统计信息以获得准确的执行计划？ ​

Q9: 如何使用索引提示影响执行计划？ ​

Q10: 如何分析执行计划的成本估算？ ​

TDSQL SQL执行计划分析

获取执行计划

基本语法

执行计划类型

示例

执行计划解读

执行计划基本结构

select_type详解

type详解

Extra详解

执行计划分析实战

示例1：简单查询

示例2：索引扫描

示例3：全表扫描

示例4：连接查询

示例5：使用临时表和文件排序

执行计划优化建议

1. 优化访问类型

2. 优化连接查询

3. 优化排序和分组

4. 优化子查询

5. 优化临时表使用

6. 优化文件排序

执行计划高级分析

分布式执行计划

JSON格式执行计划

实际执行统计

执行计划分析工具

内置工具

第三方工具

常见问题与解决方案

问题1：索引未被使用

问题2：使用了临时表

问题3：使用了文件排序

问题4：全表扫描

最佳实践

1. 定期分析执行计划

2. 结合实际执行统计

3. 考虑数据分布

4. 综合优化

5. 持续监控和调整

常见问题（FAQ）

Q1: 如何判断执行计划的好坏？

Q2: 为什么有时索引未被使用？

Q3: 如何优化GROUP BY和ORDER BY？

Q4: 如何分析分布式执行计划？

Q5: EXPLAIN和EXPLAIN ANALYZE有什么区别？

Q6: 如何优化JOIN查询的执行计划？

Q7: 如何处理执行计划中的Using temporary和Using filesort？

Q8: 如何更新统计信息以获得准确的执行计划？

Q9: 如何使用索引提示影响执行计划？

Q10: 如何分析执行计划的成本估算？