第 54 期：使用 JSON 格式的执行计划优化 SQL-369IT编程

admin管理员组
文章数量:1031308

第 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 求总记录数，相对简单。要判断哪条更优，只需要看成本信息即可。

代码语言：javascript代码运行次数：0运行复制

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 是否可以替换成主键或者唯一键。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。原始发表：2025-04-08，如有侵权请联系 cloudcommunity@tencent 删除优化jsonsql数据索引

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

作者：杨涛涛，爱可生技术专家。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

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

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

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

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 执行成本？

示例：两条 SQL 的执行成本对比

代码语言：javascript代码运行次数：0运行复制

SQL1： select count(*) from t1 where r1 in (select r1 from t1);
SQL2:  select count(*) from t1 where 1;

这两条 SQL 都是对表 t1 求总记录数，相对简单。要判断哪条更优，只需要看成本信息即可。

代码语言：javascript代码运行次数：0运行复制

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 是否可以替换成主键或者唯一键。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。原始发表：2025-04-08，如有侵权请联系 cloudcommunity@tencent 删除优化jsonsql数据索引

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

版权声明：本文标题：第 54 期：使用 JSON 格式的执行计划优化 SQL 内容由热心网友自发贡献，该文观点仅代表作者本人，转载请联系作者并注明出处：http://it.en369.cn/jiaocheng/1747746600a2212461.html，本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。

369IT编程

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

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

EXPLAIN 有哪些输出格式？

EXPLAIN FORMAT=JSON

如何快速判断 SQL 执行成本？

JSON 格式到底能得到哪些额外数据？

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

EXPLAIN 有哪些输出格式？

EXPLAIN FORMAT=JSON

如何快速判断 SQL 执行成本？

JSON 格式到底能得到哪些额外数据？

更多相关文章

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

发表评论

推荐文章

ollama v0.6.7震撼发布！支持Meta Llama 4+微软Phi 4推理，性能大飞跃！

PostgreSQL无服务 Neon and Aurora 新技术下的新经济模式 （翻译）

zod

用 AI 读懂网友的健康情绪：搭建你自己的舆情分析助手

windows环境下安装Zookeper

热门文章

plugins - Woocommerce: limit user to see only the products he created

ThreadLocal：Java多线程编程的“利器”与“陷阱”

使用 JavaScript 标签函数处理字符串

安利一个超强的linux脚本

windows电脑文件传输至ipadiphone

KB2533623补丁安装指南 - 解决Win7下无法运行.NET 67 WinForm应用

Word分节符深度解析与应用技巧

E-prime 3.0中黑屏或E-run打不开怎么办？已解决~

怎么在手机上用windows系统

微信里拥抱AI最成功的，居然是他们的微信输入法。

最新文章

2025年信创环境下性能测试工具，如何模拟高并发场景？

戴尔笔记本恢复原装系统全攻略

6T算力NPU！基于RK3588国产平台的YOLOv5目标识别案例，真的强！

2025年适用于linux用户的高级web浏览器推荐

基于LLM的异构多机器人操作系统EMOS的深度解析

程序员刚毕业，先去大厂镀金还是先去小厂攒经验？

万象2008清空boss账户密码

【Tools】GitBook简明教程

oracle exadata celldisk 闪存盘受损导致性能下降

SDUT 2138 图结构练习——BFSDFS——判断可达性

javascript - Type &#39;undefined&#39; is not assignable to type &#39;menuItemProps[]&#39; - Stack Overflow

javascript - VS 2015 Angular 2 import modules cannot be resolved - Stack Overflow

javascript - Get the JSON objects that are not present in another array - Stack Overflow

javascript - How to dismiss a phonegap notification programmatically - Stack Overflow

c - Solaris 10 make Error code 1 Fatal Error when trying to build python 2.7.16 - Stack Overflow

PostgreSQL无服务 Neon and Aurora 新技术下的新经济模式（翻译）

javascript - Type 'undefined' is not assignable to type 'menuItemProps[]' - Stack Overflow