在数据库系统中，查询优化器是数据库管理系统的核心组成部分，负责将用户的 SQL 查询转化为高效的执行计划，因而会直接影响用户体感的性能与稳定性。优化器的设计与实现过程充满挑战，有人比喻称这是数据库技术要持续攀登的珠穆朗玛峰，永远没有最优的止境。在一般的数据库系统中，查询优化涉及复杂的算法和数据结构，需要综合考虑多种因素，如数据分布、索引选择、连接顺序等，这些因素直接影响查询的性能和资源利用率。

优化器在 HTAP（Hybrid Transactional and Analytical Processing）系统难点尤为显著。混合型系统要求优化器在不同查询模式下均能保持高效性，由于 TiDB 的 AP 和 TP 分属不同的存储和计算引擎，在构造计划时候还需要平衡不同计算引擎的计划构建，由于事务查询与分析查询在性质上的显著差异，优化器需在两者之间实现合理的平衡，确保在高并发环境下的性能稳定。

此外，优化器的性能稳定性也至关重要。设计不当的优化器可能导致查询执行时间紊乱，查询计划调变，计划收敛不可控，甚至在负载变化时产生显著的性能瓶颈。因此，从原理上深入理解优化器的设计理念与实现细节，对开发者优化系统性能具有重要意义。

本系列文章将系统地探讨优化器的原理、设计思路及其在 TiDB HTAP 中的具体应用，旨在为读者提供全面的知识体系，以加深对优化器重要性与复杂性的理解。希望通过整体剖面深入的分析，为数据库性能优化提供有价值的见解。

现代数据库数据处理过程跟以前数据库运维人员写 loop 脚本来检索文件并没有差异，只是研发人员将这个过程标准化，用 SQL 语言来描述业务输入，用数据库来处理逻辑分析和执行，人力资源更多的倾向于数据库调优和运维，整体效率也更加高效，数据处理规模也愈发宏大。数据库技术在过去几十年不断的演进发展，逐步演化出单机 / 多机 / 中间件 / map-reduce / 分布式 / nosql / vector 等多种模式，优化器也在其中不断优化迭代，出现了启发式 / 代价模型 / volcano / cascades 等类型，最终成就了现在优化器的百花齐放。

对应传统的 SQL 执行流，TiDB 的整个 SQL 执行可以分为以下三个部分，从语言层面来看：分别为 SQL 语言解释器，IR 优化器（我们可以将 LogicalPlan 所代表的 SQL 呈现称为 IR）以及对应的物理计划执行器。只不过除了一些谓词下推、列裁剪等逻辑上的优化，SQL 的优化器还致力于寻找最优执行路径组合。毕竟 SQL 语言本身的解释器不需要直接翻译成机器码，而是动态输出其它高级语言描述的物理计划，这部分物理计划在下发到其它执行引擎时是以定义好的 protobuf 的形式传输的，这部分序列化呈现还是需要被 TiKV 和 TiFlash 接收，翻译成内部的具体的执行流来执行。由于 TiDB 有 3 套执行引擎，所以物理执行器类型有 3 种，在优化器生成计划的时候，需要考虑到物理 Engine Type 的计划区别。而各个具体物理执行算子的逻辑是既定写到在各个执行引擎里的，这部分更底层的 C++/Rust/Go 语言级的解释和优化在编译各个组件时候就已经优化好了。

这样来说，数据库其实就是一个 SQL 语言的解释器和优化器，输出是物理计划本身。考虑到执行引擎和存储引擎可能是存算分离结构，这部分的自由度可以多种多样，只要在序列化协议层规范好 DAG（有向无环图）的解释执行应该就可以了。因此也致使涌现了一些像 Calcite 这种插件式 SQL 语言的解释器和优化器（没有存储和计算）；同时也涌现了些像 Velox 这种只规范了物理计划呈现接口统一执行引擎框架（没有解释器和优化）；这些都是 SQL 语言前后端组件在走向标准化，统一化中演进出来的形式。而从语言前后端的关系来分，查询优化器属于 SQL 语言编译器的后端范。

SQL 优化器的主要输出是物理计划，负责对给定 SQL 语句的择优执行计划。接下来的章节将主要聚焦在优化过程本身。以下图示是 TiDB 内部 SQL 执行流程图：

MySQL Protocol：协议层
Parser：SQL 语法解析器，产生 AST
由 AST 做映射判断是否有 PlanCache（v8.4 之前仅支持 session 级别的 plan cache，之后支持了 instance 级别的 plan cache），如果可以直接将 cached 的 physical plan refill 参数之后即可使用。
Build Plan：常规 AST 结构到逻辑计划的构建过程
Logical Plan 有两条路可以走，其中 Stats 贯穿其中提供估算：

点查计划的的 Fast Plan，当判断 SQL 为点查(where 条件为主键字段或唯一性索引字段返回 1 行)，则将 SQL 发送给 TiDB Executor 去 TiKV 获取数据，同时还要完成表达式简化,子查询简化处理等。
常规计划的 Logical Optimize 和 Physical Optimize：

Logical Optimize：逻辑优化是对关系代数表达式进行启发式的常量传播，列裁剪，谓词下推，outer join 消除等逻辑；
Physical Optimize：物理优化是参考统计信息对 Logical Optimized Plan 的结果进行基于 cost 的计算和判断，择优 cost 最低的物理执行计划，包括表 join 方式，索引扫描方式，表的扫描方式，算子是否下推到 TiKV 等（物理存储引擎的计划分层也是在物理优化阶段做的）。
物理计划根据存储引擎分发：Root Task（TiDB 端），Cop Task（TiKV/TiFlash 端），MPP Task（TiFlash 端）

上述有提到 SQL 语言是结构化半描述语言，它描述的信息索取的逻辑方式，逻辑操作方式由 SQL 中不同的逻辑操作子句体现，这些子句有一定的逻辑操作顺序，转译到 LogicalPlan Tree 中就是不同的逻辑操作符号 LogicalPlan 的树形层次顺序。接下来看下 SQL 以 Query Block 为构建单元的其中子句构建顺序，及其映射到逻辑和物理计划的算子的对应关系。

在语言层面来看，Logical Plan 其实是 SQL 语言编译器的 IR （Internal Representation）呈现，无论你是什么样的 SQL Dialect 都可以转化到统一的 IR 呈现，具体在优化器后期要翻译成什么样的物理计划的呈现，取决执行器框架里对逻辑算子转译支持的丰富程度。

这个 7 个基本子句构成了一个 SQL 中一个 Query Block 的构建单元，如果任何一个子句中穿插引入子查询，那将递归深入进去到一个新的 Query Block 构建流程中，这个子 Query Block 构建完成之后会在逻辑计划中以一个子树的形式存在，这个子树的根节点是一个 LogicalApply 算子，其左孩子是被关联子查询的逻辑计划，右孩子是关联查询子 Query Block 的逻辑计划，回溯到根 Query Block 时，再以当前新的 Apply 算子为基，基于根 Query Block 上次构建的时停滞的子句单元继续依次构建。

上述列表描述的就是常规 SQL 子句到 TiDB 内部 AST，甚至到 Explain 中显示的逻辑算子映射。由于逻辑优化的存储，Explain 中显示的计划是优化后的结果，其根原始 SQL 逻辑展示形式有一定的区别。有一些特定的算子，其在 SQL 语句的呈现中并不一定要具有自己独立的子句描述，比如 Aggregation Plan，SQL 文本中任何子句中如果任何地方出现了 Agg 函数，我们都需要当前查询 block 上下文在 Projection 之前的进行数据的聚合运算，不然如果在 Projection 之后，Agg 函数所需要的聚合参数可能已经被 Project 掉了，Agg 聚合操作就无法做了，当然不同的数据库实现有些微差别，本质上都是在数据 Filter 之后进行有效数据的聚合计算，才是符合正确的 Agg SQL 语义的。

有些 Logical Plan 并没有实际对应的 SQL 子句，比如 Logical Apply，其来源是在构建关联子查询的时候，关联计划子树和被关联计划子树之间需要进行 Apply 模式的运行，即被关联子树每次传入一行给关联子树执行，返回结果后继续下一行的传入。这种来源于 SQL 语义本身的 Apply 执行模式会被直接 build 为 LogicalApply，由于 Apply 执行方式的并不算特别高效，后续在逻辑计划阶段，会有一些解关联逻辑变换将 LogicalApply 转化为 LogicalJoin 执行。

有些 Logical Plan 是一些下推操作和本身的保序性质联合导致的，比如 LogicalTopN，其如果解耦开来就是两个叠加的 Logical Plan（LogicalLimit + LogicalSort），在数据库火山执行流中，更多的算子意味着更多的数据流动和运算，因为两种属性结合的 LogicalTopN 可以更好的在一个算子内完成一致的动作，甚至也可以让排序算法本身显得更为高效，比如归并排序甚至都不用排完就可以直接返回数据并终止执行了。

有些 Logical Plan 本身可能是来自于 SQL 的有些子句修饰符，比如 With Rollup，Rollup 是一个多维度聚合的 Group By 字句的修饰符号，其用法大致为 Agg(x) from t Group By a,b with rollup ，后续的 Agg(x) 的聚合结果需要在数据分组 {a,b} 中输出一次，数据分组 {a} 中输出一次，数据分组 {} 也就是全域为一个 Group 中输出一次。实际应用场景大致为银行报表业务的按照 Group By 年，月，日的多维度聚合结果展示。TiDB 目前采用数据复制操作符 LogicalExpand 来复制底层数据，不同的数据副本按照不同的分组层次来进行分组，然后输入给上次聚合函数进行计算。

Index Join

上述 Join 图示中，我们标注了 Build 和 Probe 字样，该字样如果标注在 Join 下方的两个孩子算子中，则表示的是该 Join 的左右孩子在 Join 执行过程中充当的驱动和被驱动角色，驱动端一般指的是 Hash Join 中用来构建哈希表的一侧，Index Join / Apply/Nest Loop Join 中先行直接读到内存中的相对行数较小的一侧，相应的 Probe 端则是基于哈希表查询的一侧，后续基于已读行走索引，甚至是 Nest Loop 中的被驱动一侧；如果是标注在 IndexlookUp / IndexMerge 下方两侧孩子中，则驱动端表示现行基于索引直接读的一侧，相应的 Probe 端表示的在则是后续基于索引读到的 rowid/handle 来回表的一侧。

如果预计需要连接的行数较少，推荐使用 Index Join 算法。这个算法与 MySQL 主要使用 Join 算法类似。Index Join 其实是 Apply 模式的一个高级形式，其特别在 Probe 端可以走索引并且可以赞批，所以才从 Apply 算子 row by row 的执行模式中脱离出来，自行优化。Index Join 的 Probe 端不需要等待 Build 完全结束，其 Build 端在按到第一批数据之后，就可以直接交与 Probe 侧去驱动索引。

使用 Hint INL_JOIN 进行 Index Join 操作，该操作是流式的，Build 的数据在动态的给 Probe 端是走 Index 查询。
使用 Hint INL_HASH_JOIN 在外表执行返回的部分构上建 Hash Table，该算法区别 Hash Join 的全局哈希，而是基于流式数据的局部哈希，在特定的场景中可以减少内存压力。

Index Join 算法的性能受以下系统变量影响：

tidb_index_join_batch_size（默认值：25000）index lookup join 操作的 batch 大小。
tidb_index_lookup_join_concurrency（默认值：Unset = DefExecutorConcurrency (5) ）- 可以并发执行的 index lookup 任务数。

使用建议：

Index Join Porbe 端的并行是 Batch Size 为单位的并行，Probe Wokers 每次消费一个 Batch Size 的回表任务来加速 Probe 过程。每当一个并行任务的驱动 task 数量 < concurrency 时，说明没有充分利用 Probe 并发，应该适当调小当驱动表的 Batch Size，增加 task 数量，以便为了更高效地触发 Probe 的并行。

对比 explain analyze 结果中的 concurrency 和 task

若 concurrency < task，则并发度最高是 concurrency 的值（probe 并发已经被充分利用）

若 concurrency >= task，则并发度最高是 task 的值（可以适当减少 session batch size，增加 task 数量，提高 probe 并发利用）

Task = 驱动表行数 / tidb_index_join_batch_size

Index Join Prior

当在 Build 端数据行较小时候，Index Join 和 Hash join 都是可以
当 Probe 端数据量很大的情况，仅 Index Join Build 端行较小时候可以使用已读行来驱动 Probe 读，避免 Probe 侧行数的爆炸，不然其回表 Task 数量将很不可控，造成 TiKV 的请求堆积。
当执行环境对内存有限制，Index Join 的流式执行可以更好的缓解这种压力，但其运行速度可能会慢于其它 Join 算法

Hash Join

在 Hash Join 操作中，TiDB 首先读取 Build 端的数据并将其构建在 Hash Table 中，然后再读取 Probe 端的数据，使用 Probe 端的数据来探查 Hash Table 以获得所需行。与 Index Join 算法相比，Hash Join 的 Probe 端是需要严格等待 Build 端拿到所有数据构建完 Hash Table 后才允许 Probe 端执行的。Hash Join 要消耗更多内存，但如果需要左右两端连接的行数都很多，运行速度会比 Index Join 快。TiDB 中的 Hash Join 算子是多线程的，并且可以并发执行。


tidb> explain analyze select * from t1, t2 
where t1.a = t2.a and t1.a=1;

+------------------------------+----------+------------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+---------+

| id                           | estRows  | actRows    | task      | access object | execution info                                                                                                                                                                                                                                    | operator info                  | memory   | disk    |

+------------------------------+----------+------------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+---------+

| HashJoin_8                   | 9663.68  | 1073741824 | root      |               | time:22.7s, loops:1048577, RU:38.960331, build_hash_table:{total:29.3ms, fetch:28ms, build:1.31ms}, probe:{concurrency:5, total:1m51.7s, max:22.8s, probe:1m47.3s, fetch and 
wait:4.41s}                                                          | CARTESIAN inner join           | 1.14 MB  | 0 Bytes |

| ├─TableReader_15(Build)      | 98.30    | 32768      | root      |               | time:28.3ms, loops:33, cop_task: {num: 8, max: 12.7ms, min: 304.4µs, avg: 3.57ms, p95: 12.7ms, tot_proc: 25ms, copr_cache_hit_ratio: 0.00, build_task_duration: 3µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:8, total_time:28.5ms}}   | data:Selection_14              | 399.7 KB | N/A     |

| │ └─Selection_14             | 98.30    | 32768      | cop[tikv] |               | tikv_task:{proc max:12.7ms, min:280.2µs, avg: 3.52ms, p80:7.09ms, p95:12.7ms, iters:0, tasks:8}, time_detail: {total_process_time: 25ms}                                                                                                          | eq(1, test.t2.a)               | N/A      | N/A     |

| │   └─TableFullScan_13       | 98304.00 | 98304      | cop[tikv] | table:t2      | tikv_task:{proc max:12.7ms, min:280.2µs, avg: 3.52ms, p80:7.09ms, p95:12.7ms, iters:0, tasks:8}                                                                                                                                                   | keep order:
false, stats:pseudo | N/A      | N/A     |

| └─TableReader_12(Probe)      | 98.30    | 32768      | root      |               | time:6.3ms, loops:33, cop_task: {num: 8, max: 8.93ms, min: 329.4µs, avg: 2.89ms, p95: 8.93ms, tot_proc: 19ms, copr_cache_hit_ratio: 0.00, build_task_duration: 22.1µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:8, total_time:23ms}}   | data:Selection_11              | 399.7 KB | N/A     |

|   └─Selection_11             | 98.30    | 32768      | cop[tikv] |               | tikv_task:{proc max:8.83ms, min:304.7µs, avg: 2.83ms, p80:6.28ms, p95:8.83ms, iters:0, tasks:8}, time_detail: {total_process_time: 19ms}                                                                                                          | eq(test.t1.a, 1)               | N/A      | N/A     |

|     └─TableFullScan_10       | 98304.00 | 98304      | cop[tikv] | table:t1      | tikv_task:{proc max:8.83ms, min:304.7µs, avg: 2.83ms, p80:6.28ms, p95:8.83ms, iters:0, tasks:8}                                                                                                                                                   | keep order:
false, stats:pseudo | N/A      | N/A     |

+------------------------------+----------+------------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+---------+

7 rows 
in 
set (22.83 sec)

Hash Join 会比较消耗内存，可以通过 tidb_mem_quota_query 对 SQL 消耗内存进行控制，内存使用超过了 tidb_mem_quota_query 规定的值（默认为 1GB），且 oom-use-tmp-storage 的值为 true （默认为 true），那么 TiDB 会尝试使用临时存储，该文件目录由配置参数 tmp-storage-path 控制，在磁盘上创建 Hash Join 的 Build 端。

Hash Join 算法的性能受以下系统变量影响：

tidb_mem_quota_query（默认值：1GB）如果某条查询的内存消耗超出了配额，TiDB 会尝试将 Hash Join 的 Build 端移到磁盘上以节省内存。
tidb_hash_join_concurrency（默认值：5）可以并发执行的 Hash Join 任务数量。

使用建议：

tidb_hash_join_concurrency 参数控制并发执行的 Hash Join 任务数量，tidb_hash_join_concurrency 的所有任务计算过程当中所申请的内存使用量总和不超过 tidb_mem_quota_query，否则超过部分会移到磁盘从而导致性能降低。


mysql> explain analyze select * from t1,t2 
where t1.a=t2.a;

+--------------------------------+----------+-----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+----------+---------+

| id                             | estRows  | actRows   | task      | access object | execution info                                                                                                                                                                                                                                        | operator info                                    | memory   | disk    |

+--------------------------------+----------+-----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+----------+---------+

| Projection_9                   | 12487.50 | 440401920 | root      |               | time:7.9s, loops:430081, RU:30.108091, Concurrency:5                                                                                                                                                                                                  | test.t1.a, test.t1.b, test.t2.a, test.t2.b       | 379.7 KB | N/A     |

| └─HashJoin_25                  | 12487.50 | 440401920 | root      |               | time:7.71s, loops:430081, build_hash_table:{total:4.84ms, fetch:3.62ms, build:1.22ms}, probe:{concurrency:5, total:38.5s, max:7.95s, probe:37s, fetch and 
wait:1.55s}                                                                                 | inner join, equal:[eq(test.t2.a, test.t1.a)]     | 956.3 KB | 0 Bytes |

|   ├─TableReader_31(Build)      | 9990.00  | 26880     | root      |               | time:3.85ms, loops:28, cop_task: {num: 8, max: 1.54ms, min: 104.6µs, avg: 553.4µs, p95: 1.54ms, tot_proc: 2ms, copr_cache_hit_ratio: 0.00, build_task_duration: 4.83µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:8, total_time:4.38ms}}    | data:Selection_30                                | 303.7 KB | N/A     |

|   │ └─Selection_30             | 9990.00  | 26880     | cop[tikv] |               | tikv_task:{proc max:1.48ms, min:95.3µs, avg: 523.5µs, p80:1.2ms, p95:1.48ms, iters:0, tasks:8}, time_detail: {total_process_time: 2ms}                                                                                                                | not(isnull(test.t2.a))                           | N/A      | N/A     |

|   │   └─TableFullScan_29       | 10000.00 | 26880     | cop[tikv] | table:t2      | tikv_task:{proc max:1.48ms, min:95.3µs, avg: 523.5µs, p80:1.2ms, p95:1.48ms, iters:0, tasks:8}                                                                                                                                                        | keep order:
false, stats:pseudo                   | N/A      | N/A     |

|   └─TableReader_28(Probe)      | 49102.85 | 49152     | root      |               | time:1.95ms, loops:49, cop_task: {num: 9, max: 3.37ms, min: 93.1µs, avg: 1ms, p95: 3.37ms, tot_proc: 6ms, copr_cache_hit_ratio: 0.00, build_task_duration: 1.27µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:9, total_time:8.96ms}}         | data:Selection_27                                | 532.7 KB | N/A     |

|     └─Selection_27             | 49102.85 | 49152     | cop[tikv] |               | tikv_task:{proc max:3.27ms, min:74.8µs, avg: 965.3µs, p80:2.32ms, p95:3.27ms, iters:0, tasks:9}, time_detail: {total_process_time: 6ms}                                                                                                               | not(isnull(test.t1.a))                           | N/A      | N/A     |

|       └─TableFullScan_26       | 49152.00 | 49152     | cop[tikv] | table:t1      | tikv_task:{proc max:3.27ms, min:74.8µs, avg: 965.3µs, p80:2.32ms, p95:3.27ms, iters:0, tasks:9}                                                                                                                                                       | keep order:
false, stats:partial[a:unInitialized] | N/A      | N/A     |

+--------------------------------+----------+-----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+----------+---------+

8 rows 
in 
set (7.95 sec)

当 disk>0 则需要优化 tidb_mem_quota_query 参数。

Hash Join Prior

如果 Join 左右孩子表行数都很大，这时走 Apply 很慢，走 Index Join（如果可以走的话）cop task 数量很多，所以 Hash Join 可能比较好
在 Join 一侧孩子表行比较小的时候，其比较适合用与 hash 表的构建，如果另外一侧没有索引，那么 hash join 是最好的选择，考虑到 hash join 自身的并法，在行数差距不是特别大的情况下，hash join 都还是比较好用的。

Merge Join

Merge Join 是一种特殊的 Join 算法。当两个关联表要 Join 的字段需要按排好的顺序读取时，适用 Merge Join 算法。由于 Build 端和 Probe 端的数据都会有序读取，并进行归并输出，Join 操作是流式向上的，在底层数据很大时候，内存又有限制时候，Merge Join 非常适合对 latency 要求不高或者不敏感的场景。由于归并限制了并发的存在，所以 Merge Join 在执行速度上有所欠缺。

使用建议：

Merge Join Prior

Merge join 可以直接的接受孩子节点传递过来的有序数据源，可以避免 Join 过程中的对齐操作（其对 hash join 来说就是哈希映射，对于 Index Join 来说就是索引行索取，而 Merge Join 本身的对其是按行在归并排序中的，是最简单的一种对齐）
考虑到归并操作的特殊性质，其并不能并发操作，所以其对内存非常友好，通常可以用在内存敏感且对于延迟不敏感的环境中。

Null Aware Join

有些时候 Join 连接词来自于集合谓词，而不是普通等值 EQ 的时候，就会产生 Null Aware 的需求。考虑到下面的例子：


explain select (a, b) not 
in (select a, b from naaj_B) from naaj_A;

这里的 Not In 是集合谓词（需要感知空集，NULL 性质），虽然可以转化为 Anti Semi Join，但是 (a, b) = (a, b) 在 v7.0 之前是默认不作为 Join 条件的。可以看到计划中有笛卡尔积的关键词

这种计划，在之前 v7.0 之前最好是通过 SQL 改写的方式避免集合谓词的使用，比如可以改成 Exists 的等价查询


explain select * from naaj_A 
where NOT exists (select a, b from naaj_B 
where 

naaj_B.a = naaj_A.a and naaj_B.b = naaj_A.b) ;

在 v7.0 之后，TiDB 引入了 Null Aware join 的性质，可以将集合谓词来作为 Join Key，在 runtime 过程中去感知集合谓词所需要的空集和 Null 值属性，以便加速整个计算过程，避免笛卡尔积的使用。

注意：

现在集合谓词 IN 条件导致的 Join 没有 join key，IN 自身条件会在 other condition 里面，导致 Join 本身是个笛卡尔积的存在，这种 case 暂时还没有用 Null Aware 优化，需要手动改写 Exists 子查询。

Apply Mode Prior

LogicalApply 是一个非常原始的 row by row 驱动的内外表执行方式，同时存在于子查询的构建初期，后续结果解关联优化之后，大部分都会变成 join 来执行。如果 Probe 侧有合适的索引且没有一些异常算子的阻隔，那么可以规划到 IndexJoin 来执行；在没有其他帮助下也能规划一个 HashJoin 来执行。但是有一种时候，row by row 的 Apply 执行方式反而是最好的，看接下来这个例子 issues/37789 ( https://github.com/pingcap/tidb/issues/37789 )。


create table t1(a int, b int);

create table t2(a int, b int, index ia(a));

explain select a > (select sum(b) from t2 
where a = t1.b) from t1;



+------------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------+

| id                                 | estRows  | task      | access object | operator info                                                                             |

+------------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------+

| Projection_10                      | 10000.00 | root      |               | gt(cast(test.t1.a, decimal(20,0) BINARY), Column
#10)->Column#11                           |

| └─HashJoin_11                      | 10000.00 | root      |               | left outer join, equal:[eq(test.t1.b, test.t2.a)]                                         |

|   ├─HashAgg_22(Build)              | 7992.00  | root      |               | group by:test.t2.a, funcs:sum(Column
#13)->Column#10, funcs:firstrow(test.t2.a)->test.t2.a |

|   │ └─TableReader_23               | 7992.00  | root      |               | data:HashAgg_15                                                                           |

|   │   └─HashAgg_15                 | 7992.00  | cop[tikv] |               | group by:test.t2.a, funcs:sum(test.t2.b)->Column
#13                                       |

|   │     └─Selection_21             | 9990.00  | cop[tikv] |               | not(isnull(test.t2.a))                                                                    |

|   │       └─TableFullScan_20       | 10000.00 | cop[tikv] | table:t2      | keep order:
false, stats:pseudo                                                            |

|   └─TableReader_14(Probe)          | 10000.00 | root      |               | data:TableFullScan_13                                                                     |

|     └─TableFullScan_13             | 10000.00 | cop[tikv] | table:t1      | keep order:
false, stats:pseudo                                                            |

+------------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------+

可以看到在常规规划下，选择的是 Hash Join + Table Full Scan(t1, t2)。如果 t1 的表非常小，t1.b = a 就可以过滤很多数据，用 Apply 算子就可以让 t1 表作为驱动表，然后带动 t2 侧，利用 ia 索引来范围查询数据就会快很多。有人说这不就是 IndexJoin，没有错，IndexJoin 就可以看作是一种 Apply 但是在构建 IndexJoin 的时候，v7.0.0 之前的 TiDB 还是有很多局限，比如这里的 IndexJoin 的 Probe 端就变成了一个具有 Agg 的子树，这个在老版本是中无法构建 IndexJoin 的，这个时候就需要用 /+ no_decorrelate() / 来方 HashJoin 保留成原始的 Apply 模式，让 Probe 侧的复杂子树也能走索引了。

在新版本的 v8.2.0 TiDB 中，已经加强了 TiDB 的能力 pull/51354 ( https://github.com/pingcap/tidb/pull/51354 )，一些简单 case 需要配合使用 tidb_enable_inl_join_inner_multi_pattern=1，才能正确走到 IndexJoin 中，这个 case 中在老的版本中就只能通过 /+ no_decorrelate() / 来保留 raw Apply mode 来走内侧索引驱动了。


mysql> explain select a > (select /*+ no_decorrelate() */  sum(b) from t2 
where a = t1.b) from t1;

+------------------------------------------+-----------+-----------+-----------------------+------------------------------------------------------------------------------+

| id                                       | estRows   | task      | access object         | operator info                                                                |

+------------------------------------------+-----------+-----------+-----------------------+------------------------------------------------------------------------------+

| Projection_10                            | 10000.00  | root      |                       | gt(cast(test.t1.a, decimal(10,0) BINARY), Column
#10)->Column#11              |

| └─Apply_12                               | 10000.00  | root      |                       | CARTESIAN left outer join                                                    |

|   ├─TableReader_14(Build)                | 10000.00  | root      |                       | data:TableFullScan_13                                                        |

|   │ └─TableFullScan_13                   | 10000.00  | cop[tikv] | table:t1              | keep order:
false, stats:pseudo                                               |

|   └─MaxOneRow_15(Probe)                  | 10000.00  | root      |                       |                                                                              |

|     └─StreamAgg_20                       | 10000.00  | root      |                       | funcs:sum(Column
#19)->Column#10                                              |

|       └─Projection_45                    | 100000.00 | root      |                       | cast(test.t2.b, decimal(10,0) BINARY)->Column
#19                             |

|         └─IndexLookUp_44                 | 100000.00 | root      |                       |                                                                              |

|           ├─IndexRangeScan_42(Build)     | 100000.00 | cop[tikv] | table:t2, index:ia(a) | range: decided by [eq(test.t2.a, test.t1.b)], keep order:
false, stats:pseudo |

|           └─TableRowIDScan_43(Probe)     | 100000.00 | cop[tikv] | table:t2              | keep order:
false, stats:pseudo                                               |

+------------------------------------------+-----------+-----------+-----------------------+------------------------------------------------------------------------------+

10 rows 
in 
set (0.00 sec)

SQL 算子最佳实践与使用建议

Hash Join 是默认的 Join 算法，在下层算子提供任何有序属性的时候，**都**能够执行。此外 Hash Join 的左右孩子可以利用索引来加速扫描过程，但是 Hash Join 的性质没有变化，依旧需要在 Build 表上构建出 Hash Table，并以此为基础上来 Probe 另外一端，Probe 端的开始时间严格在 Build Hash Table 之后。考虑其左右孩子都可以并发，大多数情况下都有不错的性能。

考虑到 Hash 表构建需要把所有元组都缓存在内存，这种阻塞方式在表很大时候，会有很大的 OOM 风险，因此可以考虑流式 Join 的方式（这里流式指的是，Join 数据流动没有全量等待，左右孩子数据流动也是 row/batch 为单位向上流动的）。流式 Join 在如何找到左右孩子合适的匹配行算法上分为：

通过 Index 查找 Probe 行：Index Join（Probe 可以并行），及部分 Apply Mode（Apply 的 Probe 侧走索引回表）。
Build 侧和 Probe 侧同时保证 Join Key 有序性质：Merge Join。

流式 Join 的方式，主要是根据孩子特定的物理属性（有序或者有索引）来快速查找相应的匹配行。大多数情况，流式 Join 的方式并发都比较有限（Index Join 只有 Probe 并发），或者没有并发（Merge Join），其对内存友好，在有序保证的前提下可以考虑。在大小表场景中，并且大表在 Join Key 有索引的情况下，Index Join 能够很好的避免全表读，利用 row_id/handle scan 的回表能力；考虑到回表的离散性，如果每次大表 Join Key 的回表都比较分散，在一定的情况下需要独立的发送 COP RPC 请求，这种为了很多单一回表导致的大量 COP 会导致 TiKV 侧请求堆积且疲于处理，最终会导致延迟增加。最终权衡选择时应该特别关注：

Join 基表的行数，Hash Join 有无处理慢的情况，可以调并发加速否。
Hash Join 的内存压力，有无 OOM 风险，是否需要降低并发，或者选择流式 Join。
Join 左右表的各自物理属性，有没有合适流式 Join 可以选。
Index Join 的 Probe 侧并发，如果 Index Join Build 侧重复值很多，可以考虑 Index Hash Join。
关于 Index Join 注意实际 Cop 请求数量，以及 TiKV Cop 请求处理数量和延迟。

在上述文章提到，由 SQL 文本自带的语义层次翻译过来的逻辑计划在执行上并不是最优的，通常一些显而易见的常量传播，列裁剪，谓词下推，apply 消除等操作并没有人为写好，这些因为 SQL 在设计之初的逻辑描述是泛化的，仅是符合人为逻辑思考的，而不是为了贴近方便计算而生的。这种计算贴合的优化应该就是优化器本身的责任所在了，就也是逻辑优化应该需要做到的事了。

上述非详尽的罗列了一些当前 TiDB 使用的常规 Logical Rules，对其中的逻辑解释也只是使用了部分比较特征化的例子，更多详细的 rules 和更多复杂的应用场景可以参考：pkg/planner/core ( https://github.com/pingcap/tidb/tree/master/pkg/planner/core )。需要注意当前 TiDB 认为逻辑计划 rule 总是 always good and substituted 的，其不会保留优化前的副本计划。

谓词下推

谓词下推属于逻辑优化阶段，当一个 SQL 需要访问的数据分散在多个 TiKV 节点时，SQL 将多个 TiKV 节点的数据统一拉到 TiDB Server 节点过滤和汇聚的性能是远低于在 TiKV 节点层完成各自节点数据的过滤然后再将结果传到计算层 TiDB Server 聚合，该操作就叫谓词下推。该特殊的好处在于 SQL 数据访问的过程中，TiKV 与 TiDB Server 的数据传输量降低，且也降低了 TiDB Server 侧数据缓存的资源开销。

对用户来说优化器的优化过程是比较黑盒的，唯一的结果观测窗口就是 Explain Info，虽然是一种比较后置的手段，但是其中也结合经验能判断计划的优化的合理性。一旦观测到计划结果不对之后，可以使用 optimizer tracer 功能跟踪整个逻辑和物理优化过程，找到问题的症结。

解读 Explain 的计划结果


tidb> explain analyze select * from t1, t2 
where t1.a = t2.a and t1.a=1;

+------------------------------+----------+------------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+---------+

| id                           | estRows  | actRows    | task      | access object | execution info                                                                                                                                                                                                                                    | operator info                  | memory   | disk    |

+------------------------------+----------+------------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+---------+

| HashJoin_8                   | 9663.68  | 1073741824 | root      |               | time:22.7s, loops:1048577, RU:38.960331, build_hash_table:{total:29.3ms, fetch:28ms, build:1.31ms}, probe:{concurrency:5, total:1m51.7s, max:22.8s, probe:1m47.3s, fetch and 
wait:4.41s}                                                          | CARTESIAN inner join           | 1.14 MB  | 0 Bytes |

| ├─TableReader_15(Build)      | 98.30    | 32768      | root      |               | time:28.3ms, loops:33, cop_task: {num: 8, max: 12.7ms, min: 304.4µs, avg: 3.57ms, p95: 12.7ms, tot_proc: 25ms, copr_cache_hit_ratio: 0.00, build_task_duration: 3µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:8, total_time:28.5ms}}   | data:Selection_14              | 399.7 KB | N/A     |

| │ └─Selection_14             | 98.30    | 32768      | cop[tikv] |               | tikv_task:{proc max:12.7ms, min:280.2µs, avg: 3.52ms, p80:7.09ms, p95:12.7ms, iters:0, tasks:8}, time_detail: {total_process_time: 25ms}                                                                                                          | eq(1, test.t2.a)               | N/A      | N/A     |

| │   └─TableFullScan_13       | 98304.00 | 98304      | cop[tikv] | table:t2      | tikv_task:{proc max:12.7ms, min:280.2µs, avg: 3.52ms, p80:7.09ms, p95:12.7ms, iters:0, tasks:8}                                                                                                                                                   | keep order:
false, stats:pseudo | N/A      | N/A     |

| └─TableReader_12(Probe)      | 98.30    | 32768      | root      |               | time:6.3ms, loops:33, cop_task: {num: 8, max: 8.93ms, min: 329.4µs, avg: 2.89ms, p95: 8.93ms, tot_proc: 19ms, copr_cache_hit_ratio: 0.00, build_task_duration: 22.1µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:8, total_time:23ms}}   | data:Selection_11              | 399.7 KB | N/A     |

|   └─Selection_11             | 98.30    | 32768      | cop[tikv] |               | tikv_task:{proc max:8.83ms, min:304.7µs, avg: 2.83ms, p80:6.28ms, p95:8.83ms, iters:0, tasks:8}, time_detail: {total_process_time: 19ms}                                                                                                          | eq(test.t1.a, 1)               | N/A      | N/A     |

|     └─TableFullScan_10       | 98304.00 | 98304      | cop[tikv] | table:t1      | tikv_task:{proc max:8.83ms, min:304.7µs, avg: 2.83ms, p80:6.28ms, p95:8.83ms, iters:0, tasks:8}                                                                                                                                                   | keep order:
false, stats:pseudo | N/A      | N/A     |

+------------------------------+----------+------------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+----------+---------+

7 rows 
in 
set (22.83 sec)

Explain 的返回结果包含以下字段：

i d 为算子名（对应物理计划中所示），或执行 SQL 语句需要执行的子任务。

TableFullScan：全表扫描。
TableRangeScan：带有范围的表数据扫描。
TableRowIDScan：根据上层传递下来的 RowID 扫描表数据。时常在索引读操作后检索符合条件的行。
IndexFullScan：另一种“全表扫描”，扫的是索引数据，不是表数据。
IndexRangeScan：带有范围的索引数据扫描操作。
TableReader：将 TiKV 上底层扫表算子 TableFullScan 或 TableRangeScan 得到的数据进行汇总。
IndexReader：将 TiKV 上底层扫表算子 IndexFullScan 或 IndexRangeScan 得到的数据进行汇总。
IndexLookUp：先汇总 Build 端 TiKV 扫描上来的 RowID，再去 Pro be 端上根据这些 RowID 精确地读取 TiKV 上的数据。Build 端是 IndexFullScan 或 IndexRangeScan 类型的算子，Probe 端是 TableRowIDScan 类型的算子。
IndexMerge：和 IndexLookupReader 类似，可以看做是它的扩展，可以同时读取多个索引的数据，有多个 Build 端，一个 Probe 端。执行过程也很类似，先汇总所有 Build 端 TiKV 扫描上来的 RowID，再去 Probe 端上根据这些 RowID 精确地读取 TiKV 上的数据。Build 端是 IndexFullScan 或 IndexRangeScan 类型的算子，Probe 端是 TableRowIDScan 类型的算子。
estRows 为显示 TiDB 预计会处理的行数。该预估数可能基于字典信息（例如访问方法基于主键或唯一键），或基于 CMSketch 或直方图等统计信息估算而来。

task 显示算子在执行语句时的所在位置。

目前 TiDB 的计算任务分为三种不同的 task：cop task，root task 和 mpp task。cop task 是指使用 TiKV 中的 Coprocessor 执行的计算任务，root task 是指在 TiDB 中执行的计算任务，而 mpp task 旨在 tiflash 上以 mpp 模式执行的计划。
SQL 优化的目标之一是将计算尽可能下推到 TiKV / TiFlash 中执行。TiKV 和 TiFlash 能支持大部分 SQL 内建函数（包括聚合函数和标量函数）、SQL Limit 操作、索引扫描和表扫描。

access-object 显示被访问的表、分区和索引。在有组合索引的情况下，该字段显示的索引可能为部分索引，此时可以通过所 operator-info 构建 range 使用的前缀参数来判断，很有参考意义。当被访问表示分区表时候，access-object 可以判断出 partition pruning 是否正确的裁剪了其他分区。
execution info 显示 explain analyze 特有的执行细节信息。

time：为算子执行的时间，单位 ms
loops

operator-info 显示访问表、分区和索引的其他信息。

其实就是各种表达式的 CNF 或者 DNF 组合
可以判断有哪些 agg 函数，由 funcs: 标识
可以判断有哪些 group by 列，由 group by: 标识
可以判断 join 类型：比如 inner join，left outer
可以判断是不是笛卡尔积（有没有 join key）CARTESIAN
可以判断有哪些 join key 条件，由 equal:[xxx] 标识
可以判断有哪些后置的其他条件，other cond: 标识
如果使用了索引，可以查看索引 range 的构造信息，使用了多少前缀列等。
keep order 属性，可以判断使用利用底层有序对象的有序性，比索引 / pk 有序。
stats 属性可判断当前这个计划，底层数据库访问对象上的统计信息是不是 pesudo。
常量直接显示字面值
column 如果有自己的原始 name，即用 name 不然就是 column#id
函数一般是标识为：函数名（参数，常量）比如：eq(column#2, 0)，至于 column#2 的来源需要去下层算子溯源之后才知道来自什么样的计算了。
表达式的标识
对 datasource 来说
对 Join 算子来说
对 Agg 算子来说
对 Selection 算子来说

解读 Explain Analyze 的执行结果

和 EXPLAIN 不同， EXPLAIN ANALYZE 会执行对应的 SQL 语句，记录其运行时信息，和执行计划一并返回出来，可以视为 EXPLAIN 语句的扩展。 EXPLAIN ANALYZE 语句的返回结果中增加了 actRows , execution info , memory , disk 这几列信息：

actRows

当前算子实际输出的数据条数

execution info

time 显示从进入算子到离开算子的全部时间，包括所有子算子操作的全部执行时间。 loops 是当前算子被父算子调用 Next 的次数。如果该算子被父算子多次 Next 调用，记作 loops，这个 time 时间就是累积的时间。 rows 是当前算子返回的行的总数。此外对于不同的存储引擎，其 cop_task, tikv_scan 的细节都会一一记录

memory

当前算子占用内存大小

disk

当前算子占用磁盘大小

一个例子：


mysql> explain analyze select * from t 
where a < 10;

+-------------------------------+---------+---------+-----------+-------------------------+------------------------------------------------------------------------+-----------------------------------------------------+---------------+------+

| id                            | estRows | actRows | task      | access object           | execution info                                                         | operator info                                       | memory        | disk |

+-------------------------------+---------+---------+-----------+-------------------------+------------------------------------------------------------------------+-----------------------------------------------------+---------------+------+

| IndexLookUp_10                | 9.00    | 9       | root      |                         | time:641.245µs, loops:2, rpc num: 1, rpc time:242.648µs, proc keys:0   |                                                     | 9.23046875 KB | N/A  |

| ├─IndexRangeScan_8(Build)     | 9.00    | 9       | cop[tikv] | table:t, index:idx_a(a) | time:142.94µs, loops:10,                                               | range:[-inf,10), keep order:
false                   | N/A           | N/A  |

| └─TableRowIDScan_9(Probe)     | 9.00    | 9       | cop[tikv] | table:t                 | time:141.128µs, loops:10                                               | keep order:
false                                    | N/A           | N/A  |

+-------------------------------+---------+---------+-----------+-------------------------+------------------------------------------------------------------------+-----------------------------------------------------+---------------+------+

3 rows 
in 
set (0.00 sec)

从上述例子中可以看出，优化器估算的 estRows 和实际执行中统计得到的 actRows 几乎是相等的，说明优化器估算的行数与实际行数的误差很小。同时 IndexLookUp_10 算子在实际执行过程中使用了约 9 KB 的内存，该 SQL 在执行过程中，没有触发过任何算子的落盘操作。执行总时间差不多 641 微秒，调用 Loops 为 2。

解读算子的执行顺序

算子的结构是树状的，但在查询执行过程中，并不严格要求子节点任务在父节点之前完成。TiDB 支持同一查询内的并行处理，即子节点“流入”父节点。父节点、子节点和同级节点可能并行执行查询的一部分。

上述示例中， TableReader_32 算子为 t1 表所对应， IndexLookUp_10 为 t2 表所对应。IndexJoin 是流式 Join，其 Build 侧行总是流式的读取，并同时作为索引行 Probe t2 表的数据，可以看到在其下方是使用 IndexRangeScan_8 走 t1_id 索引来直接读取数据，其索引列 t1_id 是来自于 t1 表。利用驱动侧传递的 t1_id 在运行时的动态常量性质，我们可以构建 t2 表上索引 t1_id 的范围，随后从 t2 表利用 TableRowIdScan_9 直接检索这些行的 handle / rowid。

Build 总是先于 Probe 并且后续可能同时执行，并且 Build 字样总是出现在 Probe 前面。即如果一个算子有多个子节点，子节点 ID 后面有 Build 关键字的算子总是先于有 Probe 关键字的算子执行。TiDB 在展现执行计划的时候，Build 端总是第一个出现，接着才是 Probe 端。Probe 端的开始时间相较于 Build 中还是 Build 之后，跟算子的性质有关系，比如上述提到的 Hash Join 和 Index Join。

解读 Query Block 层次

执行计划对应 SQL 有时候会比较复杂，特别是当涉及子查询或者设计多表的时候，但是从优化器的构建思想来看，其实是分层的，每层可以看作是一个简单的 Query Block，每层的构建路径都是相同的，执行计划的展示也是每层构建完之后层层堆叠起来的，而后才是经过逻辑优化，join reorder 等一系列变化来改变计划树的形态。

如果你想知道计划哪里不对，那么两个东西首先你要有：

一个是 plan build 完之后的树是什么样的，这个是原始来自于 Query Block 层次的堆叠的 Logical Plan 树
一个是后续要经历的 Logical Plan Rule 的列表，这个已经在上述章节中介绍到了，目前 TiDB 的逻辑优化空间不算特别大，也不算特别复杂。

心中有了这两样，在来对应看 Explain 计划就能举一反三，迅速找到计划异常点：下面来个例子看下。


=drop table 
if exists t1;

create table t1(c1 int, c2 int, c3 int, key(c1), key(c2));

insert into t1 values(4, 4, 4), (5, 5, 5);

insert into t1 select * from t1; * N;



drop table 
if exists t2;

create table t2(c1 int, c2 int, c3 int, key(c1), key(c2));

insert into t2 values(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5);



tidb> explain select * from t1 
where c1 < 2 or c2 < 2 and c3 < ANY(select count(1) from t2);

+----------------------------------+------------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| id                               | estRows    | task      | access object          | operator info                                                                                                                                                                                             |

+----------------------------------+------------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| HashJoin_12                      | 3355443.20 | root      |                        | CARTESIAN inner join, other cond:or(lt(test.t1.c1, 2), and(and(lt(test.t1.c2, 2), or(lt(test.t1.c3, Column
#10), if(ne(Column#11, 0), NULL, 0))), and(ne(Column#12, 0), if(isnull(test.t1.c3), NULL, 1)))) |

| ├─StreamAgg_18(Build)            | 1.00       | root      |                        | funcs:max(Column
#9)->Column#10, funcs:sum(0)->Column#11, funcs:count(1)->Column#12                                                                                                                        |

| │ └─StreamAgg_38                 | 1.00       | root      |                        | funcs:count(Column
#22)->Column#9                                                                                                                                                                          |

| │   └─IndexReader_39             | 1.00       | root      |                        | index:StreamAgg_22                                                                                                                                                                                        |

| │     └─StreamAgg_22             | 1.00       | cop[tikv] |                        | funcs:count(1)->Column
#22                                                                                                                                                                                 |

| │       └─IndexFullScan_36       | 5.00       | cop[tikv] | table:t2, index:c1(c1) | keep order:
false, stats:pseudo                                                                                                                                                                            |

| └─TableReader_16(Probe)          | 3355443.20 | root      |                        | data:Selection_15                                                                                                                                                                                         |

|   └─Selection_15                 | 3355443.20 | cop[tikv] |                        | or(lt(test.t1.c1, 2), and(lt(test.t1.c2, 2), 
if(isnull(test.t1.c3), NULL, 1)))                                                                                                                            |

|     └─TableFullScan_14           | 4194304.00 | cop[tikv] | table:t1               | keep order:
false                                                                                                                                                                                          |

+----------------------------------+------------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

9 rows 
in 
set, 1 warning (0.00 sec)

这里我们有两张表 t1 和 t2，一个不算复杂的 select 查询带了很多 where 条件，上述章节中有提到，sql 的撰写层次跟 logical plan 的 tree 结构层次基本是依依对应的。这里按照算子章节的语法层级来拆解一下，有着这个 SQL 是有子查询的，对应外部主查询为一个 Query Block。也就是有两层 Query Block，由外到内我们依次命名为 Query Block1 和 Query Block2。

Query Block1

Query Block2

在两个 Query Block 都对解释应完了之后，将 Query Block1 中引用 Query Block2 的地方对应替换过去就行，最终就形成了我们的 Explain 计划。各个计划的来源和 SQL Query Block 层次是一一对应的。

SQL 执行计划举例分析

一般 explain 只能看到逻辑计划，在分析性能瓶颈中，还得看 explain analyze 的结果。explain 结果和 explain analyze 的结果的树是一样的，只不过后者加入了执行的细节，在执行的细节中我们更容易找到时间瓶颈，并发瓶颈，内存大头。这里找到 execution info 列中耗时最长的瓶颈地方，然后对应分析 id 列中的逻辑算子是不是符合计划预期。


tidb> explain analyze select  * from t1 
where c1 < 2 or c2 < 2 and c3 < ANY(select count(1) from t2);

+----------------------------------+------------+---------+-----------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+

| id                               | estRows    | actRows | task      | access object          | execution info                                                                                                                                                                                                                                                                                  | operator info                                                                                                                                                                                             | memory    | disk    |

+----------------------------------+------------+---------+-----------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+

| HashJoin_12                      | 1677721.60 | 0       | root      |                        | time:4.74s, loops:1, RU:27860.254239, build_hash_table:{total:44.9ms, fetch:44.9ms, build:3.46µs}                                                                                                                                                                                               | CARTESIAN inner join, other cond:or(lt(test.t1.c1, 2), and(and(lt(test.t1.c2, 2), or(lt(test.t1.c3, Column
#10), if(ne(Column#11, 0), NULL, 0))), and(ne(Column#12, 0), if(isnull(test.t1.c3), NULL, 1)))) | 59.2 KB   | 0 Bytes |

| ├─StreamAgg_18(Build)            | 1.00       | 1       | root      |                        | time:44.8ms, loops:2                                                                                                                                                                                                                                                                            | funcs:max(Column
#9)->Column#10, funcs:sum(0)->Column#11, funcs:count(1)->Column#12                                                                                                                        | 460 Bytes | N/A     |

| │ └─StreamAgg_38                 | 1.00       | 1       | root      |                        | time:44.8ms, loops:2                                                                                                                                                                                                                                                                            | funcs:count(Column
#22)->Column#9                                                                                                                                                                          | 8.25 KB   | N/A     |

| │   └─IndexReader_39             | 1.00       | 1       | root      |                        | time:44.8ms, loops:2, cop_task: {num: 1, max: 110.8µs, proc_keys: 0, copr_cache_hit_ratio: 0.00, build_task_duration: 1.29µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:98.5µs}}                                                                                        | index:StreamAgg_22                                                                                                                                                                                        | 204 Bytes | N/A     |

| │     └─StreamAgg_22             | 1.00       | 1       | cop[tikv] |                        | tikv_task:{time:84.3µs, loops:0}                                                                                                                                                                                                                                                                | funcs:count(1)->Column
#22                                                                                                                                                                                 | N/A       | N/A     |

| │       └─IndexFullScan_36       | 5.00       | 5       | cop[tikv] | table:t2, index:c1(c1) | tikv_task:{time:84.3µs, loops:0}                                                                                                                                                                                                                                                                | keep order:
false, stats:pseudo                                                                                                                                                                            | N/A       | N/A     |

| └─TableReader_16(Probe)          | 1677721.60 | 0       | root      |                        | time:4.74s, loops:1, cop_task: {num: 6, max: 1.1s, min: 33.9ms, avg: 496ms, p95: 1.1s, tot_proc: 2.94s, copr_cache_hit_ratio: 0.00, build_task_duration: 28.1µs, max_distsql_concurrency: 6}, rpc_info:{Cop:{num_rpc:55382, total_time:9.87s},ResolveLock:{num_rpc:55376, total_time:635.4ms}}  | data:Selection_15                                                                                                                                                                                         | 224 Bytes | N/A     |

|   └─Selection_15                 | 1677721.60 | 0       | cop[tikv] |                        | tikv_task:{proc max:1.09s, min:33.9ms, avg: 490.6ms, p80:839.3ms, p95:1.09s, iters:0, tasks:6}, time_detail: {total_process_time: 2.94s}                                                                                                                                                        | or(lt(test.t1.c1, 2), and(lt(test.t1.c2, 2), 
if(isnull(test.t1.c3), NULL, 1)))                                                                                                                            | N/A       | N/A     |

|     └─TableFullScan_14           | 2097152.00 | 4194304 | cop[tikv] | table:t1               | tikv_task:{proc max:1.09s, min:33.9ms, avg: 490.6ms, p80:839.3ms, p95:1.09s, iters:0, tasks:6}                                                                                                                                                                                                  | keep order:
false                                                                                                                                                                                          | N/A       | N/A     |

+----------------------------------+------------+---------+-----------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+

9 rows 
in 
set, 1 warning (4.75 sec)

这里在大数据量我们可以明显看到 TableReader_16 的延迟到了 5 秒左右。但是其实 t1 表上是有相关 {c1,c2,c3} 是索引的，可以看到 Selection_15 这个 t1 的 selection 其实是下推了下来，但是并没有被用作索引的检索条件，而且从执行结果来看并没有相关的有效数据，仅仅是扫描 t1 花了这么多时间，那么能不能将 selection 条件全部用于 index 范围查询呢。
当前这里为了说明问题，我们一直是将 SET session tidb_enable_index_merge = OFF 的，这里直接打开开关之后就可以直接选到 IndexMerge 的计划，在老旧的 TiDB 版本或者统计信息不准确的 T iDB 实例中，观测到上述问题之后，可以先使用 Hint /*+ use_index_merge(t1) */ 指导优化器选择 IndexMerge 的物理实现。可以看到虽然同样是没有扫有效的数据，但是走三路 Merge 的 Index 索引路径快乐很多，只要 0.1s 左右就可以执行完毕。



tidb> explain analyze select /*+ use_index_merge(t1) */ * from t1 
where c1 < 2 or c2 < 2 and c3 < ANY(select count(1) from t2);

+----------------------------------+------------+---------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+

| id                               | estRows    | actRows | task      | access object          | execution info                                                                                                                                                                                                                                                                                                                                                                                | operator info                                                                                                                                                                                             | memory    | disk    |

+----------------------------------+------------+---------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+

| HashJoin_12                      | 3355443.20 | 0       | root      |                        | time:87ms, loops:1, RU:68.567903, build_hash_table:{total:462.6µs, fetch:453.8µs, build:8.88µs}                                                                                                                                                                                                                                                                                               | CARTESIAN inner join, other cond:or(lt(test.t1.c1, 2), and(and(lt(test.t1.c2, 2), or(lt(test.t1.c3, Column
#10), if(ne(Column#11, 0), NULL, 0))), and(ne(Column#12, 0), if(isnull(test.t1.c3), NULL, 1)))) | 60.7 KB   | 0 Bytes |

| ├─StreamAgg_23(Build)            | 1.00       | 1       | root      |                        | time:362µs, loops:2                                                                                                                                                                                                                                                                                                                                                                           | funcs:max(Column
#9)->Column#10, funcs:sum(0)->Column#11, funcs:count(1)->Column#12                                                                                                                        | 460 Bytes | N/A     |

| │ └─StreamAgg_43                 | 1.00       | 1       | root      |                        | time:338.2µs, loops:2                                                                                                                                                                                                                                                                                                                                                                         | funcs:count(Column
#25)->Column#9                                                                                                                                                                          | 8.25 KB   | N/A     |

| │   └─IndexReader_44             | 1.00       | 1       | root      |                        | time:331.1µs, loops:2, cop_task: {num: 1, max: 404.3µs, proc_keys: 0, copr_cache_hit_ratio: 0.00, build_task_duration: 30µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:373.3µs}}                                                                                                                                                                                      | index:StreamAgg_27                                                                                                                                                                                        | 204 Bytes | N/A     |

| │     └─StreamAgg_27             | 1.00       | 1       | cop[tikv] |                        | tikv_task:{time:323.8µs, loops:0}                                                                                                                                                                                                                                                                                                                                                             | funcs:count(1)->Column
#25                                                                                                                                                                                 | N/A       | N/A     |

| │       └─IndexFullScan_41       | 5.00       | 5       | cop[tikv] | table:t2, index:c1(c1) | tikv_task:{time:323.8µs, loops:0}                                                                                                                                                                                                                                                                                                                                                             | keep order:
false, stats:pseudo                                                                                                                                                                            | N/A       | N/A     |

| └─IndexMerge_21(Probe)           | 2684354.56 | 0       | root      |                        | time:86.8ms, loops:1                                                                                                                                                                                                                                                                                                                                                                          | 
type: union                                                                                                                                                                                               | 200 Bytes | N/A     |

|   ├─IndexRangeScan_17(Build)     | 0.00       | 0       | cop[tikv] | table:t1, index:c1(c1) | time:85.5ms, loops:1, cop_task: {num: 1, max: 85.5ms, proc_keys: 0, tot_proc: 85ms, copr_cache_hit_ratio: 0.00, build_task_duration: 219.3µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:85.5ms}}, tikv_task:{time:85.4ms, loops:0}, time_detail: {total_process_time: 85ms}                                                                                           | range:[-inf,2), keep order:
false                                                                                                                                                                          | N/A       | N/A     |

|   ├─IndexRangeScan_18(Build)     | 0.00       | 0       | cop[tikv] | table:t1, index:c2(c2) | time:62.2ms, loops:1, cop_task: {num: 2, max: 62.1ms, min: 53.8ms, avg: 57.9ms, p95: 62.1ms, tot_proc: 115ms, copr_cache_hit_ratio: 0.00, build_task_duration: 43.8µs, max_distsql_concurrency: 2}, rpc_info:{Cop:{num_rpc:2, total_time:115.8ms}}, tikv_task:{proc max:62.1ms, min:53.7ms, avg: 57.9ms, p80:62.1ms, p95:62.1ms, iters:0, tasks:2}, time_detail: {total_process_time: 115ms}  | range:[-inf,2), keep order:
false                                                                                                                                                                          | N/A       | N/A     |

|   └─Selection_20(Probe)          | 2684354.56 | 0       | cop[tikv] |                        |                                                                                                                                                                                                                                                                                                                                                                                               | or(lt(test.t1.c1, 2), and(lt(test.t1.c2, 2), 
if(isnull(test.t1.c3), NULL, 1)))                                                                                                                            | N/A       | N/A     |

|     └─TableRowIDScan_19          | 3355443.20 | 0       | cop[tikv] | table:t1               |                                                                                                                                                                                                                                                                                                                                                                                               | keep order:
false                                                                                                                                                                                          | N/A       | N/A     |

+----------------------------------+------------+---------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+

11 rows 
in 
set, 1 warning (0.09 sec)

使用建议：

观测普通算子时候，主要观察执行时间，物理算子并发，实际行数和估算行数差异，想想最优的逻辑计划应该是什么样的，条件有没有充分利用，下推是否合理，物理选择是不是恰当。
观测 IndexMerge/IndexLookup 算子时候，除了上述指标外，还要观测 Cop Task 的数量，往往 Cop Nums 越多，在短时间回给 TiKV 造成大量的请求迟滞，最终导致 TiKV Load 升高，TiDB 延迟增加。

1: Instance Plan Cache 特性: 使用全局统一的实例级内存缓存更多 Session 间共享 SQL 的执行计划，大幅提升全局 SQL 执行计划命中率，进一步降低 CPU 的消耗，大幅提升数据库整体稳定性和性能。

2: TiDB Predicate Analyze 特性： 通过参数开关控制收集 SQL 中 predicate columns 的行为，在表统计信息收集时只针对 predicate columns 进行收集，达到大幅降低收集统计信息对 CPU 和内存资源的消耗，以及大幅缩短单个表的统计信息时间，确保在有限的统计信息收集窗口完成更多的表的统计信息收集，为 SQL 执行计划生成提供更加准确的环境，最终实现整体数据库的稳定性和 SQL 的性能更进一步的提升。

3: Concurrently init stats 特性: 在 TiDB Server 启动时通过多任务并发的方式，快速将所有统计信息数据快速装载到 TiDB Server 内存中，有效避免和解决了因统计信息加载时延过长，导致生成错误的 SQL 执行计划，从而引起的性能问题，或者直接产生错误 SQL 执行计划导致消耗大量内存，CPU,网络资源，引起的数据库不稳定的问题，进一步提高了数据库的可用性。

通过事前，事中，时候多个阶段对统计信息装载优化，SQL 执行计划的缓存优化，统计信息收集的优化，大幅提升 TiDB 的性能，降低硬件资源的消耗，提升数据库和应用的性能稳定性。

ITPub博客

TiDB 优化器丨执行计划和 SQL 算子解读最佳实践

最新文章