MySQL 8.1 和 8.2 中 EXPLAIN 的新玩法
从 MySQL 8.1 开始 EXPLAIN 引入了 INTO 子句，它允许将 EXPLAIN 查询的输出存储在用户变量中，而不是将其返回给客户端。目前仅支持 JSON 格式，但这对于大多数用例来说应该足够了。
作者：Magnus Brevik
原文地址：https://dev.mysql.com/blog-archive/explain-into-and-explain-for-schema-in-mysql-81-and-82/
EXPLAIN INTO
从 MySQL 8.1 开始 EXPLAIN 引入了 INTO 子句，它允许将 EXPLAIN 查询的输出存储在用户变量中，而不是将其返回给客户端。目前仅支持 JSON 格式，但这对于大多数用例来说应该足够了。
EXPLAIN 的输出存储在用户变量之后，就可以将它当成 JSON 对象操作。这使您能够以编程方式直接从 EXPLAIN中提取、操作和存储数据到数据库中。
举个例子：
mysql> SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id; +-------------+----------+ | name | quantity | +-------------+----------+ | Screwdriver | 23 | | Screwdriver | 1 | | Locket | 17 | | Armoire | 42 | | Armoire | 16 | +-------------+----------+ 如果只对此次查询的开销成本感兴趣，可以先将查询定义为为 @explain_output。
mysql> EXPLAIN FORMAT=JSON INTO @explain_output SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id; Query OK, 0 rows affected (0,00 sec) 现在，从这个 EXPLAIN 中得到的唯一输出是“Query OK”。如果我们想查看完整的 EXPLAIN 输出，我们可以选择 @explain_output，但是完整的 JSON 输出会占用很大的空间，而且我只对开销（cost ）感兴趣。为了从 JSON 对象中提取，只需使用 MySQL 的 JSON 函数对指定的 JSON 对象处理即可。
mysql> SELECT JSON_EXTRACT(@explain_output, "$.query_block.cost_info.query_cost") AS query_cost; +------------+ | query_cost | +------------+ | "1.60" | +------------+ EXPLAIN SCHEMA
从 MySQL 8.2 开始 EXPLAIN 引入了 FOR SCHEMA 子句。它允许在当前 SCHEMA 之外的其他 SCHEMA 中执行 EXPLAIN 查询。这使得创建一个存储过程 PROCEDURE 来解释每个 SCHEMA 中最常运行的查询并收集有关它们的统计信息成为可能。
如果当前的 SCHEMA 与我们要查询的表所在的 SCHEMA 不同，我们可以
mysql> USE statistics; mysql> EXPLAIN FORMAT=TREE FOR SCHEMA customer1 SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id; -> Nested loop inner join (cost=2.3 rows=5) -> Table scan on items (cost=0.55 rows=3) -> Index lookup on orders using fk_item_id (item_id=items.id) (cost=0.472 rows=1.67) 这对于大多数 EXPLAIN 使用的情况可能不太有用，但是如果我们有多个具有相同表结构的 SCHEMA，并且想要一次性收集所有 SCHEMA 的统计信息，我们可以创建一个存储过程 PROCEDURE 来为我们做到这一点：
DELIMITER | CREATE PROCEDURE explain_query_for_schema(IN schema_name VARCHAR(64), IN query VARCHAR(1000)) BEGIN SET @explain_stmt = CONCAT("EXPLAIN FORMAT=JSON INTO @explain_output FOR SCHEMA ", schema_name, " ", query); PREPARE stmt FROM @explain_stmt; EXECUTE stmt; INSERT INTO explain_outputs (schema_name, query, explain_output) VALUES (schema_name, query, @explain_output); END | CREATE PROCEDURE explain_query_for_all_schemas(IN query VARCHAR(1000)) BEGIN DECLARE done BOOLEAN DEFAULT FALSE; DECLARE schema_name VARCHAR(64); DECLARE cur_schema_names CURSOR FOR SELECT schema_name_table.schema_name FROM schema_name_table; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur_schema_names; explain_loop: LOOP FETCH cur_schema_names INTO schema_name; IF done THEN LEAVE explain_loop; END IF; CALL explain_query_for_schema(schema_name, query); END LOOP; CLOSE cur_schema_names; END | DELIMITER ; SET @query = "SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id"; CALL explain_query_for_all_schemas(@query); SELECT schema_name, query, JSON_EXTRACT(explain_output, "$.query_block.cost_info.query_cost") AS query_cost, created_at FROM explain_outputs; +-------------+---------------------------------------------------------------------------+------------+---------------------+ | schema_name | query | query_cost | created_at | +-------------+---------------------------------------------------------------------------+------------+---------------------+ | customer1 | SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id | "2.30" | 2023-11-14 20:56:47 | | customer2 | SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id | "0.70" | 2023-11-14 20:56:47 | | customer3 | SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id | "9.10" | 2023-11-14 20:56:47 | +-------------+---------------------------------------------------------------------------+------------+---------------------+ 就是这样。EXPLAIN 的两个相对简单的扩展乍一看可能并不重要，但使存储过程能够以编程方式处理 EXPLAIN 输出。我们自己将其用于 《使用 MySQL Autopilot Indexing 删除索引猜测值》 这个案例中，其中 EXPLAIN INTO 和 EXPLAIN FOR SCHEMA 对于收集数据以建议更好的索引至关重要。我们希望它对您和我们一样有用。
如果您想了解更多信息，这些都记录在 MySQL EXPLAIN 文档 中。
更多技术文章，请访问：https://opensource.actionsky.com/
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。
SQLE 获取
关注公众号 低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有，本站原创内容转载请注明来源。
- 上一篇
我在阿里做开发的高效打工技巧总结
前言 如果您的工作中完全不需要自己写PRD 、技术方案、测试用例，那么这篇文章除了会浪费您宝贵的15分钟之外，别无益处，可以绕行了。 背景 很多新入职的工友反馈，大家现在除了编码之外，在厂子里还有很多七七八八的杂活才是工作耗时的大头，比如有些项目里面，沟通&对接相关的工作占比甚至大于70%，实际写代码&自测也就一两天，前面那些细碎的内容，也不方便录入工时，非常苦恼。出于对工友的爱和保护，鄙人决定在这里分享一些打工的技巧，希望能提供一些微不足道的小帮助。 如何高效开会 作为会议发起人 首先，作为会议发起人希望大家能自觉做到如下几点： 提前准备文档 提前准备好会议文档，比如技术方案评审，在你评审之前，至少要写好了完整的技术文档，并且提前私下里和参会的上下游开发勾兑过协议，并且在开会前，把文档贴在日程里面。最讨厌都做方案评审了，上下游都还在抓瞎，最后留下来一大堆action“会后再对”，一场会议出现超过3个“会后再对”就一定有严重的信息gap。所有的评审类型日程，都不应该发展成讨论会。为了防止有些眼神不好的工友不知道贴在哪里，我贴心做了大量截图，大家可以贴在日程描述，或者...
- 下一篇
奇思妙想的SQL｜去重立方计算优化新思路
引言 SQL作为目前最通用的数据库查询语言，其功能和特性复杂程度远超大家常用的“SELECT * FROM tbl”这样简单，一段好的SQL和差的SQL，其性能可能有几十上千倍的而写出一个能够兼顾性能和易用性的SQL，超越的不仅仅是了解多少新特性的新写法，而是要深入理解数据的处理过程，然后设计好的数据的处理过程。 因此想推出本系列文章，并取名为《奇思妙想的SQL》，希望能够以实际案例出发，和大家分享一些SQL处理数据的新方案新思路，并在过程中模拟对问题本质的理解，希望大家能够喜欢～。 本文为系列第1篇，分享下于蚂蚁集团数据转运改造升级流程中，针对重立方的优化实践。 一、场景描述 在做数据汇总计算和统计分析时，最头疼的就是去重类指标计算（比如用户数、商家数等），特别是还要带多种维度的下钻分析，由于其不可累加的特性，几乎每一项换一个统计维度组合，都得重新计算。数据量小时考虑可以自动化的用明细数据即时直接统计，但当数据量大时就不得不提前进行计算了。 典型场景如下：省、市、区等维度下的支付宝客户端的日支付用户数（其中省、市、区为用户支付时所在的位置，表格中数据指标对应的）。 存在一种情况，某用...
相关文章
文章评论
共有0条评论来说两句吧...
微信收款码
支付宝收款码