通过 EXPLAIN 可以查看查询的执行计划，TDSQL Boundless 和 MySQL 一样支持三种展示执行计划的格式：TRADITIONAL，TREE 和 JSON。
TRADITIONAL 格式
默认的展示格式，将执行计划输出为一个表格，表格中每一行代表 SELECT 语句中的一张表，行的顺序代表执行查询语句时读取表和做 JOIN 的顺序。
以如下查询为例：
tdsql> explain select * from t1, t2 where t1.a = t2.a;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL || 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (hash join) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
执行计划可以解读为：对 t1 表和 t2 表都执行全表扫描，预期每张表返回3行数据，然后按照 t1 JOIN t2 的顺序执行连接，产生3 * 3 * 33.33 / 100 = 3行连接结果。
TREE 格式
将执行计划展示为一棵树，能更直观地看到查询执行的逻辑，上面的例子对应的 TREE 格式为：
tdsql> explain format=tree select * from t1, t2 where t1.a = t2.a;+---------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+---------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Inner hash join (t2.a = t1.a) (cost=6.28 rows=3)-> Table scan on t2 (cost=0.88 rows=3)-> Hash-> Table scan on t1 (cost=2.84 rows=3)|+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
相比于 TRADITIONAL 格式提供的信息，还能看到对 t1 JOIN t2 选择的算法是 Hash Join，其中 t1 作为 build 端，t2 作为 probe 端，并且还展示了执行计划的 cost 信息，以及 JOIN 使用的连接条件表达式 t2.a = t1.a。
JSON 格式
将执行计划以 JSON 格式输出，上面的例子对应 JSON 输出为：
tdsql> explain format=json select * from t1, t2 where t1.a = t2.a;+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| {"query_block": {"select_id": 1,"cost_info": {"query_cost": "6.27"},"nested_loop": [{"table": {"table_name": "t1","access_type": "ALL","rows_examined_per_scan": 3,"rows_produced_per_join": 3,"filtered": "100.00","cost_info": {"read_cost": "2.54","eval_cost": "0.30","prefix_cost": "2.84","data_read_per_join": "48"},"used_columns": ["a","b"]}},{"table": {"table_name": "t2","access_type": "ALL","rows_examined_per_scan": 3,"rows_produced_per_join": 3,"filtered": "33.33","using_join_buffer": "hash join","cost_info": {"read_cost": "2.54","eval_cost": "0.30","prefix_cost": "6.28","data_read_per_join": "48"},"used_columns": ["a","b"],"attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"}}]}} |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
它可以看作是 TRADITIONAL 格式和 TREE 格式展示内容的混合，还额外提供更丰富的一些信息，包括更细分的 cost 以及每张表具体返回哪些列等。
EXPLAIN FOR CONNECTION
如果想要查看某个正在执行中查询的执行计划，可以使用 EXPLAIN FOR CONNECTION 语句，前提是知道该查询所在的 connection 号，这个 connection 号可以通过在该连接中使用
CONNECTION_ID() 获取，或者在任何连接中通过 SHOW PROCESSLIST 获取，比如：tdsql> SELECT connection_id();+-----------------+| connection_id() |+-----------------+| 1048611 |+-----------------+# 说明：# TDSQL Boundless 为分布式数据库，由多个计算节点组成。默认 SHOW PROCESSLIST 返回的是集群内全局会话列表，结果中的 Id（连接 ID）可能来自不同的计算节点。使用 SHOW FULL PROCESSLIST 可以额外输出 Endpoint 列，标识每条连接所在的计算节点地址。# EXPLAIN FOR CONNECTION <连接 ID> 仅在连接 ID 所属的计算节点上生效。若所选 Id 来自其他节点，命令将因无法在当前节点找到对应连接而报错。建议优先在目标连接上使用 CONNECTION_ID() 获取连接 ID；若通过 SHOW FULL PROCESSLIST 选取，可结合 Endpoint 列确认连接所属节点。tdsql> SHOW FULL PROCESSLIST;+---------+------------+-------+------+-------------------+------+---------+------+-------------------+-------------------+---------+-----------+---------------+----------------+| Id | Tid | Mem | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined | Endpoint |+---------+------------+-------+------+-------------------+------+---------+------+-------------------+-------------------+---------+-----------+---------------+----------------+| 1048611 | 4294970988 | 16384 | test | 127.0.0.1:40318 | test | Query | 0 | init | show processlist | 0 | 0 | 0 | 10.0.0.1:6008 |+---------+------------+-------+------+-------------------+------+---------+------+-------------------+-------------------+---------+-----------+---------------+----------------+tdsql> EXPLAIN FOR CONNECTION 1048611;ERROR 3012 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE. txid: 0. sql-node: node-1-001. error-store-node: nil
例子里因为当前连接并没有在执行可以 EXPLAIN 展示执行计划的 DML 语句，因此 EXPLAIN FOR CONNECTION 报错。
注意可能会有一种情况，通过 EXPLAIN 看到的执行计划，和不带 EXPLAIN 时查询执行时真正用的执行计划并不一样，有以下几种可能的原因：
不同连接的参数设置不同，尤其是优化器参数；
不同连接因为时间差异看到的统计信息不完全相同，因此做出的行数估算不同导致执行计划不同；
优化器代码中个别地方对 EXPLAIN 语句和非 EXPLAIN 语句会走不同路径；
这种情况下，通过 EXPLAIN FOR CONNECTION 查看查询执行真正使用的执行计划就会有帮助；
EXPLAIN ANALYZE
EXPLAIN 用于查看优化器为 SQL 语句选择的执行计划。该语句只展示计划，不会实际执行查询。
EXPLAIN ANALYZE 会按照执行计划实际执行查询，并输出运行时统计信息，包括各算子的实际耗时、返回行数和循环次数。该信息可用于定位查询执行阶段的主要开销。
上文查询对应的 EXPLAIN ANALYZE 输出为：
tdsql> explain analyze select * from t1, t2 where t1.a = t2.a;+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Inner hash join (t2.a = t1.a) (cost=6.28 rows=3) (actual time=1.459..1.481 rows=3 loops=1)-> Table scan on t2 (cost=0.88 rows=3) (actual time=0.528..0.548 rows=3 loops=1)-> Hash-> Table scan on t1 (cost=2.84 rows=3) (actual time=0.841..0.863 rows=3 loops=1)|+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
上述结果表示：
t1 和 t2 均执行了表扫描
t1 和 t2 的实际返回行数均为3行，与优化器估算行数一致
t1 与 t2 执行 Inner hash join，实际返回3行
JOIN 算子产生第一行结果的时间为1.459ms
JOIN 算子完成执行的时间为1.481ms
通过对比各算子的 actual time、rows 和 loops，可以判断查询的主要耗时位置，并据此进行针对性调优。
EXPLAIN ANALYZE VERBOSE
TDSQL Boundless 还支持
EXPLAIN ANALYZE VERBOSE，用于输出更详细的运行时信息，包括 RPC 统计信息和内存使用情况。上文查询对应的
EXPLAIN ANALYZE VERBOSE 输出为：tdsql> explain analyze verbose select * from t1, t2 where t1.a = t2.a;+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Inner hash join (t2.a = t1.a) (cost=6.28 rows=3) (actual time=0.511..0.533 rows=3 loops=1)Chunk pair files: 0, memory usage: 16kB-> Table scan on t2 (cost=0.88 rows=3) (actual time=0.167..0.185 rows=3 loops=1)-> Hash-> Table scan on t1 (cost=2.84 rows=3) (actual time=0.262..0.283 rows=3 loops=1)RPC statistics: leader-> LocalScanRecord=latency(ms): 2,0.266323,0.081208...0.185115, retry_count: 0, retry_interval_all(ms): 0.000000, failure_count: 0 |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
上述结果表示：
查询内存使用量为16KB
查询包含2次 LocalScanRecord RPC
RPC 总耗时为0.266323ms
单次 RPC 最短耗时为0.081208ms，最长耗时为0.185115ms
RPC 执行过程中未发生重试和失败
EXPLAIN ANALYZE VERBOSE 适用于需要进一步分析 RPC 开销、内存使用和算子执行细节的场景。EXPLAIN ANALYZE VERBOSE 与并行预取
当查询涉及分区表的 BKA Join（Batched Key Access）且触发了并行预取优化时，
EXPLAIN ANALYZE VERBOSE 输出中会在 BKA Join 迭代器行额外显示 Async BKA 统计信息，便于观测并行预取的实际触发情况。输出格式：
Async BKA: partition_prefetch=N, mrr_prefetch=N
各字段含义如下：
字段 | 说明 |
partition_prefetch=N | 阶段一（Index 扫描并行预取）实际并发的分区数。 N=0 表示未触发并行预取。 |
mrr_prefetch=N | 阶段二（KV Cache 并行填充）实际并发的 key 分组数。 N=0 表示未触发并行预取。 |
示例：
-- 对分区表执行 BKA Join，触发并行预取tdsql> EXPLAIN ANALYZE VERBOSESELECT /*+ BKA(t2) */ * FROM t1JOIN t2 PARTITION BY HASH(id) ON t1.id = t2.id;+------------------------------------------------------------------+| EXPLAIN |+------------------------------------------------------------------+| -> Nested loop inner join (cost=12.3 rows=100)-> Table scan on t1 (actual time=0.1..0.2 rows=100 loops=1)-> Batched Key Access on t2 (actual time=0.3..1.2 rows=100 loops=100)Async BKA: partition_prefetch=4, mrr_prefetch=4RPC statistics: leader-> ...+------------------------------------------------------------------+
上述输出中
Async BKA: partition_prefetch=4, mrr_prefetch=4 表示该 BKA Join 在两个阶段均触发了4路并行预取。说明：
该统计行仅在触发了并行预取时出现。若查询未使用 BKA Join、内表非分区表、经分区裁剪后仅使用1个分区，或通过
SET tdsql_enable_partition_bka_prefetch = OFF 关闭了并行预取，则输出中不会出现 Async BKA 行。普通
EXPLAIN（不含 ANALYZE VERBOSE）的输出形态不受影响，仍显示 Batched Key Access。相关系统变量：
tdsql_enable_partition_bka_prefetch：控制分区表 BKA MRR 并行预取，默认 ON，支持 HINT。tdsql_async_bka_max_bthreads：限制并行预取同时发起的 bthread 总数，默认 5000。超限时自动退化为串行路径。SHOW PROFILE
在部分场景下，慢查询的耗时不一定集中在执行计划中的算子执行阶段。例如：
EXPLAIN ANALYZE 显示执行耗时较低，但实际查询仍然较慢EXPLAIN ANALYZE 显示查询较慢，但执行计划本身没有明显问题查询可能受到其他慢查询、资源竞争或服务端处理阶段的影响
tdsql> set profiling = 1;Query OK, 0 rows affected, 1 warning (0.00 sec)tdsql> select * from t1 where a > 0;+------+------+| a | b |+------+------+| 1 | 1 || 2 | 2 || 3 | 3 |+------+------+3 rows in set (0.00 sec)tdsql> show profiles;+----------+------------+------------------------------+| Query_ID | Duration | Query |+----------+------------+------------------------------+| 1 | 0.00195300 | select * from t1 where a > 0 |+----------+------------+------------------------------+1 row in set, 1 warning (0.00 sec)tdsql> show profile for query 1;+--------------------------------+----------+| Status | Duration |+--------------------------------+----------+| starting | 0.000204 || Executing hook on transaction | 0.000004 || starting | 0.000031 || checking permissions | 0.000014 || Opening tables | 0.000095 || init | 0.000009 || System lock | 0.000035 || optimizing | 0.000020 || statistics | 0.000076 || Wait gts rsp | 0.000354 || preparing | 0.000082 || executing | 0.000849 || end | 0.000007 || query end | 0.000005 || waiting for handler commit | 0.000028 || closing tables | 0.000061 || freeing items | 0.000079 || cleaning up | 0.000004 |+--------------------------------+----------+18 rows in set, 1 warning (0.01 sec)
SHOW PROFILE 可用于分析查询在服务端处理链路中的阶段耗时，不限于 Executor 执行阶段。
此外，其他分析查询耗时和瓶颈可用的工具还包括 SPAN_TRACE 和 慢查询日志 等，综合使用它们可以帮助找出真正的慢查询，以及它耗时的地方。
OPTIMIZER TRACE
以上的信息都是关于优化器最终选择的执行计划以及其执行情况，它们能帮助定位慢查询到底慢在哪里，在此之上用户可能还需要知道为什么优化器选择了这个慢的执行计划，是因为它真的就没有其他更好的选择了，还是因为它的评估偏差导致选错了执行计划，或者是因为它根本就没有搜索到更优的某个执行计划，这就需要用到 Optimizer Trace 功能，它能回溯优化器对执行计划的具体决策过程。
以一个简单例子解释 Optimizer Trace 的使用：
tdsql> set optimizer_trace='enabled=on';Query OK, 0 rows affected (0.00 sec)tdsql> explain select * from t1 where a > 0;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)tdsql> select * from information_schema.optimizer_trace;+--------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+| QUERY | TRACE | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | INSUFFICIENT_PRIVILEGES |+--------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+| explain select * from t1 where a > 0 | {"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (`t1`.`a` > 0)"}]}},{"join_optimization": {"select#": 1,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`t1`.`a` > 0)","steps": [{"transformation": "equality_propagation","resulting_condition": "(`t1`.`a` > 0)"},{"transformation": "constant_propagation","resulting_condition": "(`t1`.`a` > 0)"},{"transformation": "trivial_condition_removal","resulting_condition": "(`t1`.`a` > 0)"}]}},{"substitute_generated_columns": {}},{"table_dependencies": [{"table": "`t1`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"ref_optimizer_key_uses": []},{"rows_estimation": [{"table": "`t1`","table_scan": {"rows": 3,"cost": 2.5375}}]},{"considered_execution_plans": [{"plan_prefix": [],"table": "`t1`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 3,"filtering_effect": [],"final_filtering_effect": 0.333333,"access_type": "scan","resulting_rows": 1,"cost": 2.8375,"chosen": true}]},"condition_filtering_pct": 100,"rows_for_plan": 1,"cost_for_plan": 2.8375,"chosen": true}]},{"attaching_conditions_to_tables": {"original_condition": "(`t1`.`a` > 0)","attached_conditions_computation": [],"attached_conditions_summary": [{"table": "`t1`","attached": "(`t1`.`a` > 0)"}]}},{"force_batched_key_access": [{"table": "`t1`","batched_key_access": true}]},{"finalizing_table_conditions": [{"table": "`t1`","original_table_condition": "(`t1`.`a` > 0)","final_table_condition ": "(`t1`.`a` > 0)"}]},{"refine_plan": [{"table": "`t1`"}]},{"engine_push_conditions": [{"table": "`t1`","total_rows": 3,"index": "hidden pk","condition_push": {},"single_table_push": {"projection_push": {"enabled": false,"cause": "read field pct is less than tdsql_max_projection_pct"}},"condition_pushed": false,"cause": "scan_rows < tdsql_push_down_threshold_rows"}]}]}},{"parallel_plan": {"select#": 1,"steps": [{"considering": {"chosen": false,"cause": "plan_cost_less_than_threshold"}}]}},{"local_access_optimize": {"check_local": true,"tables": [{"table": "`t1`","type": "scan","strategy": "condidate_for_local","info": "not support direct local"}]}},{"join_explain": {"select#": 1,"steps": []}}]} | 0 | 0 |+--------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+1 row in set (0.01 sec)tdsql> set optimizer_trace='enabled=off';Query OK, 0 rows affected (0.00 sec)
它展示了优化器在每个阶段做了些什么操作和决策，以及背后的原因，比如上面这个例子里，优化器没有选择将过滤条件 a > 0 下推到存储层，以及没有选择对 t1 表做并行扫描，因为它预估 t1 的行数很少没有超过相应优化的触发阈值，它也没有选择在 tdstore 中做列裁剪，因为查询需要的列数量占主键索引中列数量的比例超过了 tdsql_max_projection_pct。
