第 54 期：使用 JSON 格式的执行计划优化 SQL

前面几期我们讲过，EXPLAIN 可以得到一条 SQL 语句的执行计划，获得以下问题的参考数据：

1. 是否用了合适的索引？
2. 是否需要使用文件排序？
3. 是否需要使用语句级别临时表？
4. 优化器改写后的样子
5. 多表关联语句可以得到算法、顺序等，判断是否和预期一致？

但是 EXPLAIN 的结果相对比较单一，在可读性差的场景，则需要 DBA 结合自己的经验值提供参考。比如，输出结果中就不包含 SQL 的执行成本数据。

针对这样的困扰，我们可以使用 EXPLAIN 语句提供的 FROMAT 输出格式选项，来设置不同的输出格式，每种格式有自己独特的内容，这样就可以弥补传统执行计划结果输出单一的缺点。

EXPLAIN 有哪些输出格式？

目前 MySQL 支持的三种 EXPLAIN 输出格式：

1. EXPLAIN FORMAT=TRADITIONAL

   传统选项（默认），以表格形式展示。优点是输出结果易读，而且有独特的 EXTRA 栏提示，这是 MySQL 一直以来的默认输出格式。

2. EXPLAIN FORMAT=JSON

   以 JSON 格式输出执行计划信息。比传统执行计划结果更加清晰，更加细致。

3. EXPLAIN FORMAT=TREE

   以树状格式来输出执行计划，使得执行计划的结果更加清晰，层次感更强。

本篇我们来解读 JSON 格式的执行计划输出结果。

EXPLAIN FORMAT=JSON

下面是一条 SQL 语句的 JSON 格式执行计划输出结果，可以得到如下详细的输出数据：

1. 执行 SQL 的成本
2. 具体表名、列名
3. 是否用到排序
4. 是否用到索引
5. 扫描的记录数
6. 详细成本数据
• CPU 成本
• IO 成本
• 执行 SQL 需要扫描的数据量
• ……

示例：JSON 格式执行计划输出

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 的执行成本对比

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。

从下面执行时间上来看，也是同样的预期。

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 格式到底能得到哪些额外数据？

SQL3：select count(*) from t1 a join t1 b on a.r1 = b.r1;

SQL3 也即简单的两表关联，不过有两个需要优化的点:

1. 关联字段非主键或者唯一索引， 字段 r1
    在表 t1
    里面的 NDV 值为 100。
2. 除了关联字段，没有任何其他过滤条件。

以上两点导致这条 SQL 必定效率很差，来看下传统执行计划结果：

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)

可以得到以下几点有用信息：

1. 这条 JOIN 语句，走的是有索引 的NESTED LOOP JOIN（可读性不强，依赖既有经验判断）。
2. 外表 type 栏为 index，扫描整个索引；内表的 type 栏为 ref，不够优化。
3. 要执行完这条语句，必须扫描 2552706×25527 行记录才能出结果。
4. 参与 JOIN 的两张表都走了索引，不过由于没有过滤条件，对外表来讲，只利用索引的有序性，过滤特性没有利用到。

再来看下 JSON 格式的执行计划：

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 格式执行计划结果，看到额外的数据：

1. 结果有 KEY 直接为：nested_loop，简单易读。
2. 总成本为：6575362199.56。
3. 扫描数据量：外表 77M，内表 1T。
4. 两表仅使用字段 r1
   。

从 JSON 格式的执行计划可以更加清晰的看到，这条 SQL 性能巨差。

所以对于以上 SQL3 的优化有以下两点建议：

1. 和业务沟通，加额外关联条件或者加额外的过滤条件。
2. 两表关联的 JOIN KEY 是否可以替换成主键或者唯一键。

往期内容