课程名称：201 + 2.5.5 SQL Tuning Guide（TiDB 的 SQL 性能优化指南）
学习时长：40min
课程收获：了解 TiDB 与执行计划相关的性能调优手段
课程内容：
SQL处理流程：
explain 输出：
explain analyze输出更详细：
典型逻辑优化规则举例：
Max/Min 函数消除规则：（绝对有益）
- 在 SQL 中包含了
max/min函数时，查询优化器会尝试使用max/min消除优化规则来将max/min聚合函数转换为 TopN 算子，若能利用索引规避order by，就可以进一步转化为 Limit 算子进行查询，即便没有索引，也没有增加计算量，因此是绝对有益的一条优化规则。- SQL 中的 LIMIT 子句在 TiDB 查询计划树中对应 Limit 算子节点，ORDER BY 子句在查询计划树中对应 Sort 算子节点，相邻的 Limit 和 Sort 算子组合成 TopN 算子节点，表示按某个排序规则提取记录的前 N 项。换言之，Limit 节点等价于一个排序规则为空的 TopN 节点。
关联子查询优化：（可能有害）
- 以
select * from t1 where t1.a < (select sum(t2.a) from t2 where t2.b = t1.b)为例，优化器会将其等价的改写为select t1.* from t1, (select b, sum(a) sum_a from t2 group by b) t2 where t1.b = t2.b and t1.a < t2.sum_a;。- 这样做的好处是，避免了每次子查询执行时，和它的外部查询结果绑定。在上面的例子中，如果
t1.a有一千万个值，那这个子查询就要被重复执行一千万次，因为t2.b=t1.b这个条件会随着t1.a值的不同而发生变化。而改写后，这个子查询就只需要执行一次了。- 这种改写的弊端在于，优化前是可以使用关联列上的索引的。也就是说，虽然这个子查询可能被重复执行多次，但是每次都可以使用索引过滤数据。而优化后会导致关联列的位置发生改变，虽然子查询只被执行了一次，但是单次执行的时间会比优化前的单次执行时间长。
- 总之，在外部的值比较少的情况下，不启用这条优化规则可能对执行性能更有帮助。可以通过优化规则及表达式下推的黑名单来关闭此优化规则。
非关联子查询优化：（可能有害）
- … IN (SELECT … FROM …) 会被改写为 JOIN (SELECT … FROM … GROUP BY …) 的形式。 如
select * from t1 where t1.a in (select t2.a from t2)会被改写为select t1.* from t1 join (select a from t2 group by a) t2 on t1.a = t2.a同时这里的GROUP BY可以在t2.a具有UNIQUE属性时被自动消去。- 这样做的好处是，可以使用以
IN子查询中的 t2 为驱动表的index join。 在IN子查询相对较小，而外部查询相对较大时产生更好的执行性能。- 这种改写的弊端在于，当改写删成的聚合无法被自动消去且
t2表比较大时，反而会影响查询的性能。- set [SESSION | GLOBAL] tidb_opt_insubq_to_join_and_agg = 0 来关闭这个优化规则。
NULL 值的 JOIN：（可能有害）
- 目前的TiDB对于NULL值的JOIN支持不好，会产生笛卡尔积，因此需要特别注意，尽量不要使用可以为NULL的列去做JOIN。
- 参见性能问题8844
聚合下推：（可能有害）
- 默认关闭的规则，需要
set tidb_opt_agg_push_down = 1;开启。- 设置优化器是否执行聚合函数下推到 Join / Union 之前的优化操作。
- 只有当聚合操作可以显著降低 Join / Union 的大小，或聚合操作执行很慢时，时才应考虑开启。
Join Reorder：
- 贪心算法：枚举所有的join情况，取开销最小的作为执行计划
物理优化：
物理优化是基于代价的优化，为上一阶段产生的逻辑执行计划制定物理执行计划。
这一阶段中，优化器会为逻辑执行计划中的每个算子选择具体的物理实现。
逻辑算子的不同物理实现有着不同的时间复杂度、资源消耗和物理属性等。
在这个过程中，优化器会根据数据的统计信息来确定不同物理实现的代价，并选择整体代价最小的物理执行计划。
典型物理优化规则举例：
如图所示，对于sort和join这两个逻辑算子有图中所示的不同物理实现
可以不下推sort算子，使用 hash join 的物理实现
也可以下推sort算子，使join有序，就可以使用 merge join 或 index join 的物理实现
物理优化的过程就是评估这三种不同的物理实现，选择开销最小的那一种
另外如图所示，merge join和index join都将t1.c1有序这一信息推给了IndexScan动作，即不同的物理实现访问了相同的搜索结果，这种情况下，TiDB会把这个重复的搜索结果缓存下来，避免多余的扫描。
代价选择调优的常见问题：
- 由于统计信息过期或者准确度不够造成的选错索引问题，可以重新执行
analyze table或者修改analyze table的参数。- 由于目前区别 TiFlash 和 TiKV 的代价模型还比较粗糙而错选 TiFlash 和 TiKV 造成的，如果TiFlash更快，可以调小
tidb_opt_seek_factor的值，让优化器倾向于选择 TiFlash，反之亦然。- 除上述两种情况外导致错选索引问题时，可以考虑使用HINT进行调优。
执行引擎的发展历史：
- 1.0 版本中，使用 volcano 模型，逐行处理数据。
- 2.0 及之后的版本中，使用 batch 模型，批量处理数据，通过 tidb_max_chunk_size 设置执行过程中一个 chunk 最大的行数，设置过大可能引起缓存局部性的问题，默认1024，最小32。
重点算子的执行逻辑：
计算引擎概览：
哈希JOIN：
1.TiDB 的 Hash Join 算子采用了多线程优化，执行速度较快，但会消耗较多内存。
2.Hash Join 分为两部分，Build 和 Probe。
3.Build 端将数据缓存在内存中，根据这些数据构，单线程的造出一个 Hash Table。
4.Probe 端读取数据，并用于探测 Build 端构造出来的 Hash Table，将符合条件的数据返回给用户。
相关配置：
- tidb_hash_join_concurrency：作用域：SESSION | GLOBAL。控制 hash join 算法的并发度。默认5，推荐等于CPU核数。
Index Join：
对于外表经过 WHERE 条件过滤后结果集较小（小于 1 万行）的场景，可以尝试使用这个算法。
具体流程：
1.如图，首先拿到驱动表 Outer Table 的数据
2.将这些数据分成多个task
3.每个task都分发给对应的 InnerWorker
4.InnerWorker 根据 task 中的数据和索引构造 Inner Table 上的查询请求，然后再做一次Hash join。
5.将最终结果返回给上层算子
相关配置：
- tidb_index_lookup_concurrency：作用域：SESSION | GLOBAL。这个变量用来设置 index lookup 操作的并发度，AP类应用适合较大的值，TP 类应用适合较小的值。默认值：4，推荐等于CPU核数。
- tidb_index_join_batch_size：作用域：SESSION | GLOBAL。这个变量用来设置 index lookup join 操作的 batch 大小，AP 类应用适合较大的值，TP 类应用适合较小的值。默认值：25000，推荐使用默认值。
Merge Join：
Merge Join 算子在执行时，会从 Build 端把一个 Join Group 的数据全部读取到内存中，接着再去读 Probe 端的数据，用 Probe 端的每行数据去和 Build 端的一个完整 Join Group 比较，依次查看是否匹配（Join Group 指的是所有 Join Key 上值相同的数据，这里的 “匹配” 主要是指查看是否满足非等值条件）。
特点：
1. 单线程。
2. 需要在 join key 上有序。
3. 相比于 Hash Join 通常会占用更少的内存，但可能执行时间会更久。当数据量太大，或系统内存不足时，建议尝试使用。
NestedLoopApply：
用于执行关联子查询
特点：
1. 单线程。
2. 逐行处理数据。
3. join key上有索引则可以使用到。
4. 效率较差
哈希聚合：
一般而言 TiDB 的 Hash Aggregate 会分成两个阶段执行，一个在 TiKV/TiFlash 的 Coprocessor 上，在扫表算子读取数据时计算聚合函数的中间结果。另一个在 TiDB 层，汇总所有 Coprocessor Task 的中间结果后，得到最终结果。
特点：
1. 多线程并发优化，执行速度快，但会消耗较多内存。
相关配置：
- tidb_hashagg_final_concurrency：作用域：SESSION | GLOBAL。这个变量用来设置并行 hash aggregation 算法 final 阶段的执行并发度。对于聚合函数参数不为 distinct 的情况，HashAgg 分为 partial 和 final 阶段分别并行执行。默认值：4，推荐等于CPU核数。
- tidb_hashagg_partial_concurrency：作用域：SESSION | GLOBAL。这个变量用来设置并行 hash aggregation 算法 partial 阶段的执行并发度。对于聚合函数参数不为 distinct 的情况，HashAgg 分为 partial 和 final 阶段分别并行执行。默认值：4，推荐等于CPU核数。
流式聚合：
和 Hash Aggregate 类似，一般而言 TiDB 的 Stream Aggregate 也会分成两个阶段执行，一个在 TiKV/TiFlash 的 Coprocessor 上，在扫表算子读取数据时计算聚合函数的中间结果。另一个在 TiDB 层，汇总所有 Coprocessor Task 的中间结果后，得到最终结果。
特点：
1. 单线程
2. 需要在 group by key 上有序
3. TiDB Stream Aggregation 算子通常会比 Hash Aggregate 占用更少的内存，有些场景中也会比 Hash Aggregate 执行得更快。当数据量太大或者系统内存不足时，可以试试 Stream Aggregate 算子。
回表读：
可以同时读取多个索引的数据，有多个 Build 端，一个 Probe 端。先汇总所有 Build 端 TiKV 扫描上来的 RowID，再去 Probe 端上根据这些 RowID 精确地读取 TiKV 上的数据。Build 端是 IndexFullScan 或 IndexRangeScan 类型的算子，Probe 端是 TableRowIDScan 类型的算子。
具体流程与IndexJoin很像：
1.如图，首先根据索引读取数据
2.将这些数据分成多个task
3.每个task都分发给对应的 TableWorker
4.TableWorker 根据 task 中的数据和 RowID 构造 Table 上的范围查询，然后再做一次Hash join。
5.将最终结果返回给上层算子
相关配置：
- tidb_index_lookup_concurrency：作用域：SESSION | GLOBAL。这个变量用来设置 index lookup 操作的并发度，AP 类应用适合较大的值，TP 类应用适合较小的值。默认值：4，推荐等于CPU核数 * 2。
- tidb_index_lookup_size：作用域：SESSION | GLOBAL。这个变量用来设置 index lookup 操作的 batch 大小，AP 类应用适合较大的值，TP 类应用适合较小的值。默认值：20000，推荐使用默认值。
控制执行计划：
HINT：
- 兼容MySQL中的 use/force/ignore index 语法，写在 table_name 后，例如：select * from t1 use index(idx);
- 支持通用的注释HINT语法，/*+ HINE_NAME(para1, para2, …) /，写在 select field 前，例如：select /+ use_index(t1, idx) */ * from t1;
SQL Plan Management：
- 可以避免通过修改业务 SQL的方式，改变执行计划。
- 可以通过
create global binding将新的执行计划绑定一组 SQL，避免重复的修改SQL工作。- 可以通过
show global bindings查看当前所有 binding 。
学习过程中遇到的问题或延伸思考：
问题 1：算子的执行顺序是什么？
- Build 总是先于 Probe 执行，并且 Build 总是出现在 Probe 前面。
- 在同一层级的节点中，出现在最前面的算子可能是最先被执行的，而出现在最末尾的算子可能是最后被执行的。例如：
TiDB(root@127.0.0.1:test) > explain select * from t t1 use index(idx_a) join t t2 use index() where t1.a = t2.a;
+----------------------------------+----------+-----------+--------------------------+------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+----------+-----------+--------------------------+------------------------------------------------------------------+
| HashJoin_22 | 12487.50 | root | | inner join, inner:TableReader_26, equal:[eq(test.t.a, test.t.a)] |
| ├─TableReader_26(Build) | 9990.00 | root | | data:Selection_25 |
| │ └─Selection_25 | 9990.00 | cop[tikv] | | not(isnull(test.t.a)) |
| │ └─TableFullScan_24 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─IndexLookUp_29(Probe) | 9990.00 | root | | |
| ├─IndexFullScan_27(Build) | 9990.00 | cop[tikv] | table:t1, index:idx_a(a) | keep order:false, stats:pseudo |
| └─TableRowIDScan_28(Probe) | 9990.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+----------------------------------+----------+-----------+--------------------------+------------------------------------------------------------------+
7 rows in set (0.00 sec)
要完成 HashJoin_22，需要先执行 TableReader_26(Build) 再执行 IndexLookUp_29(Probe)。而在执行 IndexLookUp_29(Probe) 的时候，又需要先执行 IndexFullScan_27(Build) 再执行 TableRowIDScan_28(Probe)。所以从整条执行链路来看，TableRowIDScan_28(Probe) 是最后被唤起执行的。
问题 2：EXPLAIN ANALYZE 是否会实际执行语句？
EXPLAIN ANALYZE语句的工作方式类似于EXPLAIN，主要区别在于前者实际上会执行语句。这样可以将查询计划中的估计值与执行时所遇到的实际值进行比较。如果估计值与实际值显著不同，那么应考虑在受影响的表上运行ANALYZE TABLE。- 在使用
EXPLAIN ANALYZE执行 DML 语句时，数据的修改操作会被正常执行。但目前 DML 语句还无法展示执行计划。
延伸思考 1：
延伸思考 2：