技术译文 | EXPLAIN 迎来全新 JSON 格式

1EXPLAIN 的格式

MySQL 提供了两个用于分析查询计划的强大工具：EXPLAIN 和 EXPLAIN ANALYZE^[1]。EXPLAIN 会显示优化器选择的执行计划，并在执行前停止；而 EXPLAIN ANALYZE 实际上会执行查询，并收集关于处理时间以及每个操作返回的行数的统计信息。

输出格式可以是表格形式（传统格式）、树形或 JSON 格式。前两种格式是为人类设计的，而 JSON 格式主要针对机器，但仍然具有人类可读性。JSON 格式应该是自动查询分析的理想格式，但自从我们切换到基于迭代器的执行计划以来，它一直未能正确表示计划结构。这使得无法将 JSON 格式与 EXPLAIN ANALYZE 一起使用，因为 EXPLAIN ANALYZE 与计划的迭代器结构直接相关。

在 MySQL 8.3 社区版中，我们为 EXPLAIN 和 EXPLAIN ANALYZE 引入了一种新的 JSON 格式，并提供了系统变量 explain_json_format_version={1,2} 来在不同格式之间进行切换。新的 JSON 格式反映了迭代器的构建方式，并且与树形格式直接匹配。JSON 格式中的每个对象对应于树形格式中的一行，但 JSON 格式以机器可读的格式包含更多信息，例如可以通过 EXPLAIN INTO 或客户端应用程序进行访问。这对于新 JSON 格式支持的 EXPLAIN ANALYZE 以及最近添加的 EXPLAIN INTO 和 EXPLAIN FOR SCHEMA 功能^[2] 特别有用。因此，EXPLAIN 树中的所有值都可以通过 JSON 格式进行访问，并通过编程进行分析。

2如何使用新的 JSON 格式

如果没有一个例子，我们就无法谈论一个新特性，所以让我们来看一个简单的 SELECT 查询。

mysql> EXPLAIN FORMAT=TREE SELECT name, quantity FROM orders JOIN items i ON item_id = i.id WHERE quantity > 1000;
-> Nested loop inner join  (cost=49828 rows=76470)
    -> Filter: ((orders.quantity > 1000) and (orders.item_id is not null))  (cost=23063 rows=76470)
        -> Table scan on orders  (cost=23063 rows=229432)
    -> Single-row index lookup on i using PRIMARY (id=orders.item_id)  (cost=0.25 rows=1)

树形格式虽然紧凑且易于人类读者阅读和理解，但在代码中进行分析时需要手动解析。使用 EXPLAIN FORMAT=JSON，你可以得到一个 JSON 对象，它可以在你喜欢的编程语言的客户端使用 JSON 函数进行处理，或者在 MySQL 中使用 EXPLAIN INTO 进行处理。这种 JSON 格式在 MySQL 5.6 中被引入，并且反映了当时的计划结构。随着向基于迭代器的计划的转换，这种旧的 JSON EXPLAIN 格式不再代表内部计划结构。

# Old JSON EXPLAIN format, default
mysql> SET explain_json_format_version=1;
mysql> EXPLAIN FORMAT=JSON SELECT name, quantity FROM orders JOIN items i ON item_id = i.id WHERE quantity > 1000\G
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "49827.84"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "orders",
          "access_type": "ALL",
          "possible_keys": [
            "fk_item_id"
          ],
          "rows_examined_per_scan": 229432,
          "rows_produced_per_join": 76469,
          "filtered": "33.33",
          "cost_info": {
            "read_cost": "15416.48",
            "eval_cost": "7646.97",
            "prefix_cost": "23063.45",
            "data_read_per_join": "1M"
          },
          "used_columns": [
            "item_id",
            "quantity"
          ],
          "attached_condition": "((`customer`.`orders`.`quantity` > 1000) and (`customer`.`orders`.`item_id` is not null))"
        }
      },
      {
        "table": {
          "table_name": "i",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "4",
          "ref": [
            "customer.orders.item_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 76469,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "19117.42",
            "eval_cost": "7646.97",
            "prefix_cost": "49827.84",
            "data_read_per_join": "19M"
          },
          "used_columns": [
            "id",
            "name"
          ]
        }
      }
    ]
  }
}

从这个例子中我们可以看到，树形格式中显示的实际执行计划的迭代器结构在旧的 JSON 格式中没有得到体现。过滤迭代器在订单表的表扫描中更像是一个脚注，并且关于连接的行数和成本的所有信息都在两个表对象之下。虽然值是正确的，但结构是错误的，这使得使用这些信息来优化你的查询变得更加困难。

随着新的 MySQL 优化器 hypergraph^[3] 的开发（在 HeatWave MySQL 中可用），引入了一种新的 JSON EXPLAIN 格式，因为旧格式与传统、规划过程或优化器创建的最终计划都没有关系。在使用超图优化器并设置 EXPLAIN FORMAT=JSON 时，将始终生成这种新的 JSON 格式。如果你正在使用旧的优化器并且运行 MySQL 8.3 或更高版本，可以通过将系统变量 explain_json_format_version 设置为 2 来访问新的 JSON 格式。

# New JSON EXPLAIN format
mysql> SET explain_json_format_version=2;
mysql> EXPLAIN FORMAT=JSON SELECT name, quantity FROM orders JOIN items i ON item_id = i.id WHERE quantity > 1000;
{
  "query": "/* select#1 */ select `i`.`name` AS `name`,`customer`.`orders`.`quantity` AS `quantity` from `customer`.`orders` join `customer`.`items` `i` where ((`i`.`id` = `customer`.`orders`.`item_id`) and (`customer`.`orders`.`quantity` > 1000))",
  "inputs": [
    {
      "inputs": [
        {
          "operation": "Table scan on orders",
          "table_name": "orders",
          "access_type": "table",
          "schema_name": "customer",
          "used_columns": [
            "item_id",
            "quantity"
          ],
          "estimated_rows": 229432,
          "estimated_total_cost": 23063.45
        }
      ],
      "condition": "((orders.quantity > 1000) and (orders.item_id is not null))",
      "operation": "Filter: ((orders.quantity > 1000) and (orders.item_id is not null))",
      "access_type": "filter",
      "estimated_rows": 76469.68433094025,
      "estimated_total_cost": 23063.45
    },
    {
      "alias": "i",
      "covering": false,
      "operation": "Single-row index lookup on i using PRIMARY (id = orders.item_id)",
      "index_name": "PRIMARY",
      "table_name": "items",
      "access_type": "index",
      "schema_name": "customer",
      "used_columns": [
        "id",
        "name"
      ],
      "estimated_rows": 1,
      "lookup_condition": "id = orders.item_id",
      "index_access_type": "index_lookup",
      "estimated_total_cost": 0.25000130770776513
    }
  ],
  "join_type": "inner join",
  "operation": "Nested loop inner join",
  "access_type": "join",
  "estimated_rows": 76469.68433094025,
  "join_algorithm": "nested_loop",
  "estimated_total_cost": 49827.83951582908
}

我们不会详细介绍新 JSON 格式中的每个字段，因为它们中的大多数与旧格式相同或是旧格式的重命名版本，或者很容易理解，但我会提到其中的几个。

如前所述，新的 JSON 格式与树形格式直接匹配。树形格式的顶级迭代器是 JSON 格式中的顶级对象，子迭代器可以在 JSON 对象的 inputs 字段中找到。要将每个迭代器映射到相关的 JSON 对象，可以查看 JSON 中的 operation 字段，这就是在树形格式中打印的内容。

旧 JSON 格式中一个现在行为略有变化的字段是 table_name。在旧格式中，table_name 实际上是表的别名，而不是底层表的名称。在新格式中，这一点已经改变，现在 table_name 是基表名称，并且当表有别名时，新的 alias 字段已被添加到表访问迭代器中。在表访问的父迭代器中仍然引用别名。

3EXPLAIN ANALYZE 中使用新的 JSON 格式

新 JSON 格式的另一个巧妙功能是，由于旧的 JSON 格式不适合显示 EXPLAIN ANALYZE 的输出，而新格式基于与树形格式相同的迭代器结构，所以我们现在可以以 JSON 格式获取 EXPLAIN ANALYZE 的输出。这允许对执行时间进行编程分析，因为 EXPLAIN ANALYZE FORMAT=JSON 包含几个字段，其中的信息以前必须从树形格式手动解析。

让我们来看一下前面查询的 EXPLAIN ANALYZE：

mysql> EXPLAIN ANALYZE FORMAT=TREE SELECT name, quantity FROM orders JOIN items i ON item_id = i.id WHERE quantity > 1000\G
-> Nested loop inner join (cost=49828 rows=76470) (actual time=1.95..5182 rows=227103 loops=1)
    -> Filter: ((orders.quantity > 1000) and (orders.item_id is not null)) (cost=23063 rows=76470) (actual time=1.82..1271 rows=227103 loops=1)
        -> Table scan on orders (cost=23063 rows=229432) (actual time=1.76..1135 rows=229376 loops=1)
    -> Single-row index lookup on i using PRIMARY (id=orders.item_id) (cost=0.25 rows=1) (actual time=0.0164..0.0164 rows=1 loops=227103)

以下是相同计划的 FORMAT=JSON 格式：

mysql> SET explain_json_format_version=2;
mysql> EXPLAIN ANALYZE FORMAT=JSON SELECT name, quantity FROM orders JOIN items i ON item_id = i.id WHERE quantity > 1000\G
{
  "query": "/* select#1 */ select `i`.`name` AS `name`,`customer`.`orders`.`quantity` AS `quantity` from `customer`.`orders` join `customer`.`items` `i` where ((`i`.`id` = `customer`.`orders`.`item_id`) and (`customer`.`orders`.`quantity` > 1000))",
  "inputs": [
    {
      "inputs": [
        {
          "operation": "Table scan on orders",
          "table_name": "orders",
          "access_type": "table",
          "actual_rows": 229376.0,
          "schema_name": "customer",
          "actual_loops": 1,
          "used_columns": [
            "item_id",
            "quantity"
          ],
          "estimated_rows": 229432.0,
          "actual_last_row_ms": 1123.953248,
          "actual_first_row_ms": 1.868662,
          "estimated_total_cost": 23063.45
        }
      ],
      "condition": "((orders.quantity > 1000) and (orders.item_id is not null))",
      "operation": "Filter: ((orders.quantity > 1000) and (orders.item_id is not null))",
      "access_type": "filter",
      "actual_rows": 227103.0,
      "actual_loops": 1,
      "estimated_rows": 76469.68433094025,
      "actual_last_row_ms": 1252.748685,
      "actual_first_row_ms": 1.92276,
      "estimated_total_cost": 23063.45
    },
    {
      "alias": "i",
      "covering": false,
      "operation": "Single-row index lookup on i using PRIMARY (id = orders.item_id)",
      "index_name": "PRIMARY",
      "table_name": "items",
      "access_type": "index",
      "actual_rows": 1.0,
      "schema_name": "customer",
      "actual_loops": 227103,
      "used_columns": [
        "id",
        "name"
      ],
      "estimated_rows": 1.0,
      "lookup_condition": "id = orders.item_id",
      "index_access_type": "index_lookup",
      "actual_last_row_ms": 0.016049919261304342,
      "actual_first_row_ms": 0.015992197227689638,
      "estimated_total_cost": 0.25000130770776513
    }
  ],
  "join_type": "inner join",
  "operation": "Nested loop inner join",
  "access_type": "join",
  "actual_rows": 227103.0,
  "actual_loops": 1,
  "estimated_rows": 76469.68433094025,
  "join_algorithm": "nested_loop",
  "actual_last_row_ms": 5071.693038,
  "actual_first_row_ms": 2.071419,
  "estimated_total_cost": 49827.83951582908
}

通过 EXPLAIN ANALYZE，我们会得到一些以 actual_ 开头的额外字段，这些字段包含有关执行的信息。在树形格式中，这些与迭代器中的执行信息相同，例如 (actual time=<actual_first_row_ms>..<actual_last_row_ms> rows=<actual_rows> loops=<actual_loops>)。

4总结

EXPLAIN 和 EXPLAIN ANALYZE 的新 JSON 格式首先在 MySQL 8.3 社区版中引入，现在也在 MySQL 8.4 LTS 和 9.x 创新版本中在包括 OCI、AWS 和 Azure 上的 HeatWave MySQL 在内的所有平台上可用。这种新格式允许基于实际执行计划进行详细的查询分析，可以在本地进行，也可以在服务器上使用 EXPLAIN INTO 进行。在树形格式中找到的所有信息都可以通过 JSON 函数轻松访问，因此你不必手动解析树形输出。这对于  EXPLAIN ANALYZE 特别有用，因为在早期版本中它没有 JSON 格式。

原文链接：https://blogs.oracle.com/mysql/post/new-json-format-for-explain

参考资料