第 54 期：使用 JSON 格式的执行计划优化 SQL
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
前面几期我们讲过，EXPLAIN 可以得到一条 SQL 语句的执行计划，获得以下问题的参考数据：
- 是否用了合适的索引？
- 是否需要使用文件排序？
- 是否需要使用语句级别临时表？
- 优化器改写后的样子
- 多表关联语句可以得到算法、顺序等，判断是否和预期一致？
但是 EXPLAIN 的结果相对比较单一，在可读性差的场景，则需要 DBA 结合自己的经验值提供参考。比如，输出结果中就不包含 SQL 的执行成本数据。
针对这样的困扰，我们可以使用 EXPLAIN 语句提供的 FROMAT 输出格式选项，来设置不同的输出格式，每种格式有自己独特的内容，这样就可以弥补传统执行计划结果输出单一的缺点。
EXPLAIN 有哪些输出格式？
目前 MySQL 支持的三种 EXPLAIN 输出格式：
- EXPLAIN FORMAT=TRADITIONAL 传统选项（默认），以表格形式展示。优点是输出结果易读，而且有独特的 EXTRA 栏提示，这是 MySQL 一直以来的默认输出格式。
- EXPLAIN FORMAT=JSON 以 JSON 格式输出执行计划信息。比传统执行计划结果更加清晰，更加细致。
- EXPLAIN FORMAT=TREE 以树状格式来输出执行计划，使得执行计划的结果更加清晰，层次感更强。
本篇我们来解读 JSON 格式的执行计划输出结果。
EXPLAIN FORMAT=JSON
下面是一条 SQL 语句的 JSON 格式执行计划输出结果，可以得到如下详细的输出数据：
- 执行 SQL 的成本
- 具体表名、列名
- 是否用到排序
- 是否用到索引
- 扫描的记录数
- 详细成本数据
- CPU 成本
- IO 成本
- 执行 SQL 需要扫描的数据量
- ……
示例：JSON 格式执行计划输出
代码语言：javascript代码运行次数：0运行复制mysql:ytt>desc format=json select count(log_date) from t2 group by log_date\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "40211.75"
},
"grouping_operation": {
"using_filesort": false,
"table": {
"table_name": "t2",
"access_type": "index",
"possible_keys": [
"idx_log_date"
],
"key": "idx_log_date",
"used_key_parts": [
"log_date"
],
"key_length": "4",
"rows_examined_per_scan": 398830,
"rows_produced_per_join": 398830,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "328.75",
"eval_cost": "39883.00",
"prefix_cost": "40211.75",
"data_read_per_join": "316M"
},
"used_columns": [
"id",
"log_date"
]
}
}
}
}
1 row in set, 1 warning (0.00 sec)
如何快速判断 SQL 执行成本？
对于传统执行计划输出，需要多次对比结果才能得到优化是否有效，而 JSON 格式执行计划除了传统执行计划提供的信息外，在对一些简单的 SQL 优化时，只需要对比成本即可了解优化是否有效。
示例：两条 SQL 的执行成本对比
代码语言：javascript代码运行次数：0运行复制SQL1： select count(*) from t1 where r1 in (select r1 from t1);
SQL2: select count(*) from t1 where 1;
这两条 SQL 都是对表 t1 求总记录数，相对简单。要判断哪条更优，只需要看成本信息即可。
mysql:ytt>pager grep -i "query_cost";
PAGER setto'grep -i "query_cost"'
mysql:ytt>descformat=jsonselectcount(*) from t1 \G
"query_cost": "199210.09"
1rowinset, 1warning (0.00 sec)
mysql:ytt>descformat=jsonselectcount(*) from t1 where r1 in (select r1 from t1)\G
"query_cost": "781454.78"
1rowinset, 1warning (0.00 sec)
从上面执行计划结果的筛选数据可以看出来，SQL1 的执行成本要低于 SQL2，所以 SQL1 要优于 SQL2。
从下面执行时间上来看，也是同样的预期。
代码语言：javascript代码运行次数：0运行复制mysql:ytt>select count(*) from t1;
+----------+
| count(*) |
+----------+
| 2560000 |
+----------+
1 row in set (0.26 sec)
mysql:ytt>selectcount(*) from t1 where r1 in (select r1 from t1);
+----------+
| count(*) |
+----------+
| 2560000 |
+----------+
1 row in set (1.89 sec)
JSON 格式到底能得到哪些额外数据？
代码语言：javascript代码运行次数：0运行复制SQL3：select count(*) from t1 a join t1 b on a.r1 = b.r1;
SQL3 也即简单的两表关联，不过有两个需要优化的点:
- 关联字段非主键或者唯一索引， 字段
r1在表t1里面的 NDV 值为 100。 - 除了关联字段，没有任何其他过滤条件。
以上两点导致这条 SQL 必定效率很差，来看下传统执行计划结果：
代码语言：javascript代码运行次数：0运行复制mysql:ytt>desc select count(*) from t1 a join t1 b on a.r1 = b.r1\G
*************************** 1.row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: index
possible_keys: idx_r1
key: idx_r1
key_len: 5
ref: NULL
rows: 2552706
filtered: 100.00
Extra: Usingwhere; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: idx_r1
key: idx_r1
key_len: 5
ref: ytt.a.r1
rows: 25527
filtered: 100.00
Extra: Using index
2 rows in set, 1warning (0.00 sec)
可以得到以下几点有用信息：
- 这条 JOIN 语句，走的是有索引 的NESTED LOOP JOIN（可读性不强，依赖既有经验判断）。
- 外表 type 栏为 index，扫描整个索引；内表的 type 栏为 ref，不够优化。
- 要执行完这条语句，必须扫描 2552706×25527 行记录才能出结果。
- 参与 JOIN 的两张表都走了索引，不过由于没有过滤条件，对外表来讲，只利用索引的有序性，过滤特性没有利用到。
再来看下 JSON 格式的执行计划：
代码语言：javascript代码运行次数：0运行复制mysql:ytt>desc format=json select count(*) from t1 a join t1 b on a.r1 = b.r1\G
*************************** 1.row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "6575362199.56"
},
"nested_loop": [
{
"table": {
"table_name": "a",
"access_type": "index",
"possible_keys": [
"idx_r1"
],
"key": "idx_r1",
"used_key_parts": [
"r1"
],
"key_length": "5",
"rows_examined_per_scan": 2552706,
"rows_produced_per_join": 2552706,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "9762.97",
"eval_cost": "255270.60",
"prefix_cost": "265033.57",
"data_read_per_join": "77M"
},
"used_columns": [
"r1"
],
"attached_condition": "(`ytt`.`a`.`r1` is not null)"
}
},
{
"table": {
"table_name": "b",
"access_type": "ref",
"possible_keys": [
"idx_r1"
],
"key": "idx_r1",
"used_key_parts": [
"r1"
],
"key_length": "5",
"ref": [
"ytt.a.r1"
],
"rows_examined_per_scan": 25527,
"rows_produced_per_join": 65163080620,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "58789103.96",
"eval_cost": "6516308062.04",
"prefix_cost": "6575362199.56",
"data_read_per_join": "1T"
},
"used_columns": [
"r1"
]
}
}
]
}
}
1rowinset, 1warning (0.00 sec)
从 JSON 格式执行计划结果，看到额外的数据：
- 结果有 KEY 直接为：nested_loop，简单易读。
- 总成本为：6575362199.56。
- 扫描数据量：外表 77M，内表 1T。
- 两表仅使用字段
r1。
从 JSON 格式的执行计划可以更加清晰的看到，这条 SQL 性能巨差。
所以对于以上 SQL3 的优化有以下两点建议：
- 和业务沟通，加额外关联条件或者加额外的过滤条件。
- 两表关联的 JOIN KEY 是否可以替换成主键或者唯一键。