【TiDB 4.0 PCTA 学习笔记】- 2.5.5 SQL Tuning Guide（TiDB 的 SQL 性能优化指南）@2班_陈锦楠

Kamner · 2020 年12 月 23 日 17:10

2.5.5 SQL Tuning Guide（TiDB 的 SQL 性能优化指南）

关键组件
- 查询优化器
- 统计信息
- 执行引擎

执行计划示例–explain

id，算子的类型和执行顺序
estRows，估算返回的行数
task，表示算子在哪个组件执行的
- root：在 tidb 中执行
- cop(tikv)：在 tikv 中执行
- cop(tiflash)：在 tiflash 中执行
access object，算子访问的表或索引
operator info，算子的详细输入输出操作

执行计划示例–explain analyze

actRows，实际返回的行数
execution info，记录算子的实际执行时间，变量的值

查询优化器

逻辑优化
- 逻辑的
- 等值的
- 有益的

逻辑优化器的优化规则

逻辑优化器规则–Max/Min Eliminatation

将单个 Max 转换为 TopN，等价的，不会影响性能

Max 中的列有索引
将单个 Max 转换为降序扫描（Limit），只需要读一行

Max/Min Eliminatation 规则要么是等价转换，要么大量提升性能，是绝对有益的规则

逻辑优化器规则–Subquery TransFormation

将聚合转换为普通 join
- 将关联子查询转换为 inner join
- 原来的 SQL 是 t1 读一行，t2 都要做一次全表扫描（如果t2 是大表，性能损益就很严重）
- 改写之后的 SQL，t2 先计算一次，再跟 t1 进行计算

Subquery TransFormation 规则对于关联子查询，性能在以下情况性能较好

外部表的数据量不能太大
内部表的关联字段存在索引

如果改规则在 关联子查询 的时候性能不佳，可以使用 optimization_rule_blacklist 进行关闭

非关联子查询（in 子句）的优化
- 转换为 Inner Join
- 如果没有唯一键，可以把聚合去掉
- 当 t1 小，t2大的时候，原本可以执行以t1为驱动的 index Join，但是t2有个聚合，挡住了index Join的执行，这时候就需要把t2的数据完整的参与一次聚合

Subquery TransFormation 规则对于非关联子查询（in），性能在以下情况性能较好

外部表大，内部表小的时候
内部表的关联列为 NOT NULL

如果改规则在 非关联子查询（in 查询） 的时候性能不佳，可以设置 tidb_opt_insubq_to_join_and_agg 为 0 进行关闭，执行计划如下：

如果子查询涉及的列允许为 NULL 值，tidb 会将 semi join 变成笛卡尔积的形式，这时候性能将大大降低

逻辑优化器规则–Aggregate Push Down

将聚合下推到 Join/Union
- 对于 Join，只有当聚合可以减少 Join 表的数据量的时候性能较好
- 对于 union，只有当聚合可以减少 union 输入数据量的时候有益
- 该规则默认是关闭的，通过设置 tidb_opt_agg_push_down 的值为 1 开启

该 SQL 是 join 之后，再group by进行聚合，最后是 sum
这时候如果 t1 表数据量非法大，那么聚合下推，在 join 之前先对 t1 表进行聚合，减少数据量再进行 join 操作可以大大减少计算量

逻辑优化器规则–Join Reorder

一般书写 SQL 的时候都是根据业务逻辑顺序进行 join 的，而从优化器的角度，不一定是最优的，因此需要对 join 进行重新排序

提取 join 子节点
如果 join 节点数足够小，可以用 DP 算法进行排序

当 join 节点数比较多的时候，使用贪婪算法进行排序

物理优化器

Sort → Join(t1.col1=t2.col1){t1,t2}

物理优化实际就是带着计划搜索的搜索框架
物理优化是会实际影响具体性能的
使用 hints / SPM 进行控制

可能导致选择错误索引的情况

TiDB 还不够智能
估算的行不正确
- analyze 表重新收集统计信息
收集完统计信息，索引依然是错的
- 如果 TiFLash 的列式存储索引比 TiKV 更快
  - 设置 tidb_opt_seek_factor 为较小的值
- 如果不是 TiKV 和 TiFLash 的选择问题
  - 使用 hints / SPM 进行调整

TiDB 执行引擎

从 Iterator 模型演进到 Vectorized/Batch 模型

TiDB 1.0：迭代器模型（Iterator）
- 火山模型
- 每次读一行进行运算
TiDB 2.0 开始：向量化/批量模型
- 基于迭代器模型
- 每次读一组进行运算
- 控制批量组大小：tidb_max_chunk_size

目前，TiDB 部分物理算子实现是 并行化

Hash Join, Index Join, Hash Aggregate 等

TiDB 执行引擎–Hash Join

tidb_hash_join_concurrency 参数
- 默认值是 5
- 控制 join workers 的数量
- 建议设置为跟 CPU 核数 一样的大小

TiDB 执行引擎–Index Join

tidb_index_lookup_join_concurrency 参数
- 默认值：4
- 控制 inner workers 的数量
- 建议设置为跟 CPU 核数 一样的大小
tidb_index_join_batch_size 参数
- 默认值：25000
- 控制内部表中每次 join 的行数
- 一般使用默认值

TiDB 执行引擎–Merge Join

单线程 ，执行效率比较低
数据根据 join key 有序的排序
比 hash join 和index join 使用的 内存少

TiDB 执行引擎–NestedLoopApply

用于做关联子查询
单线程
基于外部表进行估算：row by row
效率较低 （TiDB 5.0 进行优化）

TiDB 执行引擎–Hash Aggregate

Partial Workers 对应 Map/Reduce 中的 Map
Final Workers 对应 Map/Reduce 中的 Reduce
tidb_hashagg_final_concurrency 参数
- 默认值：4
- 控制 final workers 的数据量
- 建议设置为跟 CPU 核数 一样的大小
tidb_hashagg_partial_concurrency 参数
- 默认值：4
- 控制 partial workers 的数据
- 建议设置为跟 CPU 核数 一样的大小

TiDB 执行引擎–Stream Aggregate

单线程
数据需要根据 group by key 排序
比 hash join 和index join 使用的 内存少
建立 hash 表是比较费时的操作，stream 聚合不一定比 hash 聚合慢

TiDB 执行引擎–Index Lookup Reader

执行逻辑与 index join 类似
tidb_index_lookup_concurrency 参数
- 默认值：4
- 控制 TableWorker 的数据
- 建议设置为 CPU 核数 * 2 一样的大小
tidb_index_lookup_size 参数
- 默认值：20000
- 控制一个索引检索任务中表的行数
- 保持默认值即可