通过 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> show processlist;+---------+------------+-------+-------------------+---------------------+--------------------+---------+------+------------------------+------------------+---------+-----------+---------------+| Id | Tid | Mem | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |+---------+------------+-------+-------------------+---------------------+--------------------+---------+------+------------------------+------------------+---------+-----------+---------------+| 1048611 | 4294970988 | 16384 | test | 127.0.0.1:40318 | test | Query | 0 | init | show processlist | 0 | 0 | 0 |+---------+------------+-------+-------------------+---------------------+--------------------+---------+------+------------------------+------------------+---------+-----------+---------------+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 用户可以解读出执行器会按照什么方式执行这条查询，但 EXPLAIN 只是展示优化器会对查询选择的执行计划，并不会真正执行它，想要识别出这个执行逻辑中耗时高导致整个查询慢的部分，需要用到 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 表的扫描都确实返回了和估算一致的3行数据，t1 JOIN t2 的结果也是3行，产生第一行 JOIN 结果耗时1.459ms，执行完整个 JOIN 耗时 1.481ms。通过分析 EXPLAIN ANALYZE 中各部分的耗时，用户可以找出查询的瓶颈在哪里，进而做针对性的调优。
除此之外，TDSQL 还提供信息更丰富的 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，并且包含两次 LocalScanRecord RPC，总共耗时0.266323ms，其中最快的一次耗时0.081208ms，最慢的一次耗时0.185115ms，RPC 执行过程中都没有遇到错误和重试。这些运行时信息能帮助用户在更细粒度上定位查询的具体开销在什么地方。
有时候慢查询的 EXPLAIN ANALYZE 看起来并不慢，此时查询的时间开销可能并不在执行上；或者 EXPLAIN ANALYZE 确实慢，但它的执行计划可能并没有问题，而是被别的慢查询拖累才变慢的；这些情况下就需要在更大的维度上分析查询的耗时，此时可以用到 SHOW PROFILE 工具，比如：
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)
它能提供查询在数据库服务端经历的各个步骤的耗时，不仅限于执行；
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。
