首页 > 数据库 >【昌哥IT课堂】MySQL8.3 EXPLAIN中的新JSON格式(译)

【昌哥IT课堂】MySQL8.3 EXPLAIN中的新JSON格式(译)

时间：2024-11-01 16:22:41浏览次数：1  
标签：昌哥 EXPLAIN id JSON rows 格式 orders

MySQL提供了两个用于分析查询计划的强大工具：EXPLAIN和EXPLAIN ANALYZE。EXPLAIN显示优化器选择的执行计划，并在执行之前停止，而EXPLAIN ANALYZE实际执行查询并收集有关每个操作的处理时间和返回行数的统计信息。输出格式可以是表格形式（TRADITIONAL）、树形结构或JSON。前两种格式适用于人类阅读，而JSON格式主要面向机器，同时也易于人类阅读。JSON格式应该是自动化查询分析的理想格式，但自从我们转换为基于迭代器的执行计划后，它未能正确表示计划结构。这使得使用JSON格式进行EXPLAIN ANALYZE变得不可能，因为EXPLAIN ANALYZE直接与计划的迭代器结构相关联。

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

如何使用新的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)
TREE格式虽然对人类读者来说紧凑且易于阅读和理解，但需要手动解析才能在代码中进行分析。使用EXPLAIN FORMAT=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"
          ]
        }
      }
    ]
  }
}

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

随着HeatWave MySQL中新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格式与TREE格式直接对应。TREE格式中的顶级迭代器是JSON格式中的顶级对象，而子迭代器可以在JSON对象的“inputs”字段中找到。要将每个迭代器映射到相关的JSON对象，您可以查看JSON中的“operation”字段，这就是在TREE格式中打印的内容。

在旧的JSON格式中存在但现在行为略有变化的字段是“table_name”。在旧格式中，“table_name”实际上是表的别名，而不是底层表的名称。这在新格式中已经改变，其中“table_name”现在是基础表名，并在表有别名时向表访问迭代器添加了新的“alias”字段。别名仍然是在表访问的父迭代器中引用的内容。


使用新的JSON格式与EXPLAIN ANALYZE

新JSON格式的另一个实用特性是，虽然旧的JSON格式不适合显示EXPLAIN ANALYZE的输出，但由于新格式基于与TREE格式相同的迭代器结构，我们现在可以在JSON格式中获得EXPLAIN ANALYZE的输出。这允许对执行时间进行程序化分析，因为EXPLAIN ANALYZE FORMAT=JSON包含多个字段，这些字段包含了以前需要从TREE格式手动解析的信息。
让我们看看我们之前查询的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_"开头的额外字段，这些字段包含了关于执行的信息。在TREE格式中，这些信息与迭代器中的执行信息相同，比如"(actual time=<actual_first_row_ms>..<actual_last_row_ms> rows=<actual_rows> loops=<actual_loops>)"。

总结
EXPLAIN和EXPLAIN ANALYZE的新JSON格式首次在MySQL 8.3社区版中引入，现在也可用于所有平台上的MySQL 8.4 LTS和9.x创新版本，包括OCI、AWS和Azure上的HeatWave MySQL。这种新格式允许基于实际执行计划进行详细的查询分析，无论是在本地还是使用EXPLAIN INTO在服务器上。在TREE格式中找到的所有信息都可以使用JSON函数轻松访问，因此您不必手动解析TREE输出。这在EXPLAIN ANALYZE中特别有用，因为在早期版本中它没有JSON格式。新的JSON格式已经被HeatWave Autopilot用于云中的索引建议，也应该有助于您在自己的应用程序中收集额外的洞察。记得将"explain_json_format_version"设置为2，或者在HeatWave MySQL 9.x中使用新的MySQL超图优化器，以享受EXPLAIN和EXPLAIN ANALYZE新JSON格式的好处。

感谢您使用MySQL！

翻译转载:https://blogs.oracle.com/mysql/post/new-json-format-for-explain

 

 

标签：昌哥,EXPLAIN,id,JSON,rows,格式,orders
From： https://www.cnblogs.com/shukuinfo/p/18520515

相关文章

  • C#读取Json配置文件
    1、安装NuGet包：Newtonsoft.Json2、新建Confgi.json{"K-Fins":-1,"K-Zs":0.2,}3、在Models文件中新建AppConfig.cs和ConfigManager.cspublicclassAppConfig{publicdoubleKFins{get;set;}publicdoublekZs{get;s......
  • C# serialize big collection via NewtonSoft.Json
    System.OutOfMemoryExceptionHResult=0x8007000EMessage=Exceptionoftype'System.OutOfMemoryException'wasthrown.Source=mscorlibStackTrace:atSystem.Text.StringBuilder.ToString()atSystem.IO.StringWriter.ToString()atNewto......
  • Delphi中TJSONObject使用问题
    jsonProcess:=TJSONArray.Create;forvari:=0toprocessForms.ListView_Processes.Items.Count-1dobegintempListItem:=processForms.ListView_Processes.Items[i];varjsonObject:=TJSONObject.Create;tryjsonObject.AddPair('pid......
  • Python数据分析NumPy和pandas（十六、文本格式数据的读取与存储：csv、json、xml和html）
    一、分段读取文本文件在处理非常大的文件时，未找到合适的数据处理方法前，我们一般希望只读取文件的一小部分或遍历文件的较小块来做预处理或参考。这种情况可以采用分段读取文本文件的方式。我们加载一个10000行的ex6.csv文件，其内容如下：一般情况下，对于pandas读取大文件数据时......
  • JavaScript 实现对 JSON 对象数组数据进行分页处理
    JavaScript实现对JSON对象数组数据进行分页处理在前端JavaScript中对JSON对象数组进行分页，可以通过以下方式实现：分页函数示例代码假设有一组JSON对象数据，比如一组用户信息：constdata=[{id:1,name:"Alice"},{id:2,name:"Bob"},{id:3,name:"......
  • latex workshop在vscode中的settings.json设置
    //latex"latex-workshop.latex.autoBuild.run":"never","latex-workshop.showContextMenu":true,"latex-workshop.intellisense.package.enabled":true,"latex-workshop.message.error.show":fals......
  • Fastjson枚举序列化和反序列化的推荐实现
    一、背景项目中定义了很多dto，包含枚举类型，而且这些枚举全都自定义标志码。比如7001对应某种操作。返回前台时，需要转化为对应的7001,前台传入后台时也希望7001转化为枚举。二、研究思路一开始，研究了fastjson的默认实现。发现只有不自定义类似7001这种默认值的时候，可以自动转化......
  • 【SpringMVC】传递json，获取url参数，上传文件
    【传递json数据】【json概念】一种轻量级数据交互格式，有自己的格式和语法，使用文本表示一个对象或数组的信息，其本质上是字符串，负责在不同的语言中数据传递与交换json数据以字符串的形式体现【json字符串与Java对象互转】我们需要在pom.xml中增加对json的依赖【对象转jso......
  • JSON文件转YOLO文件示例
    文章目录前言一、步骤指南二、代码实现1.类别名称到ID的映射2.边界框转换函数3.JSON解码函数4.主程序前言将JSON标注文件转换为YOLO格式通常涉及从JSON文件中提取图像尺寸、对象类别和边界框坐标，并将这些信息格式化为YOLO格式所需的格式。YOLO格式通常要求每行包含......
  • gin json binding 参数验证
    验证器说明示例-忽略字段binding:"-"required必填字段binding:“required”min最小长度binding:“min=10”max最大长度binding:“max=10”|或binding:"rgbstructonly如果有嵌套，可以决定只验证结构体上的binding:“structonly”omite......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99