导读
在数据库系统中，查询优化器是数据库管理系统的核心组成部分，负责将用户的 SQL 查询转化为高效的执行计划，因而会直接影响用户体感的性能与稳定性。优化器的设计与实现过程充满挑战，有人比喻称这是数据库技术要持续攀登的珠穆朗玛峰，永远没有最优的止境。在一般的数据库系统中，查询优化涉及复杂的算法和数据结构，需要综合考虑多种因素，如数据分布、索引选择、连接顺序等，这些因素直接影响查询的性能和资源利用率。
优化器在 HTAP（Hybrid Transactional and Analytical Processing）系统难点尤为显著。混合型系统要求优化器在不同查询模式下均能保持高效性，由于 TiDB 的 AP 和 TP 分属不同的存储和计算引擎，在构造计划时候还需要平衡不同计算引擎的计划构建，由于事务查询与分析查询在性质上的显著差异，优化器需在两者之间实现合理的平衡，确保在高并发环境下的性能稳定。
此外，优化器的性能稳定性也至关重要。设计不当的优化器可能导致查询执行时间紊乱，查询计划调变，计划收敛不可控，甚至在负载变化时产生显著的性能瓶颈。因此，从原理上深入理解优化器的设计理念与实现细节，对开发者优化系统性能具有重要意义。
本系列文章将系统地探讨优化器的原理、设计思路及其在 TiDB HTAP 中的具体应用，旨在为读者提供全面的知识体系，以加深对优化器重要性与复杂性的理解。希望通过整体剖面深入的分析，为数据库性能优化提供有价值的见解。
SQL 执行流
现代数据库数据处理过程跟以前数据库运维人员写 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 优化器的主要输出是物理计划，负责对给定 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 语言是结构化半描述语言，它描述的信息索取的逻辑方式，逻辑操作方式由 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)Copy
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
最低0.47元/天 解锁文章
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
