MySQL 中的 EXPLAIN INTO 和 EXPLAIN FOR SCHEMA
今天查看官网blog时发现的两个关于explain特性，对于执行计划的统计信息分析是非常有用的，但是对仅查看SQL的执行计划时并没有什么意义。
原文链接：
https://dev.mysql.com/blog-archive/explain-into-and-explain-for-schema-in-mysql-81-and-82/
解释一下
MySQL 8.1 中引入的 EXPLAIN 的 INTO 子句允许将 EXPLAIN 查询的输出存储在用户变量中，而不是将其返回给客户端。目前仅支持 JSON 格式，但这对于大多数用例来说应该足够了。
通过将 EXPLAIN 输出存储在用户变量中，可以像 MySQL 中的任何其他 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 输出占用了很大的空间，而且我只对成本感兴趣。为了从 JSON 对象中提取成本，我们只需使用 MySQL 的 JSON 函数，因为这是一个常规的旧 JSON 对象。
mysql> SELECT JSON_EXTRACT(@explain_output, "$.query_block.cost_info.query_cost") AS query_cost;
+------------+
| query_cost |
+------------+
| "1.60" |
+------------+
解释模式
MySQL 8.2 引入了 EXPLAIN FOR SCHEMA，它允许在当前活动模式之外的其他模式中运行 EXPLAIN 查询。这使得创建一个过程来解释每个模式中最常运行的查询并收集有关它们的统计信息成为可能。
如果当前的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 来说可能不太有用，但是如果我们有多个具有相同表结构的模式，并且想要一次性收集所有这些模式的统计信息，我们可以创建一个过程来为我们做到这一点：
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 对于收集数据以建议更好的索引至关重要。我们希望它对您和我们一样有用。
