TIDB执行计划
1、explain
使用 EXPLAIN 可查看 TiDB 执行某条语句时选用的执行计划。也就是说，TiDB 在考虑上数百或数千种可能的执行计划后，最终认定该执行计划消耗的资源最少、执行的速度最快。EXPLAIN 实际不会执行查询，EXPLAIN ANALYZE 可用于实际执行查询并显示执行计划，如果 TiDB 所选的执行计划非最优，可用 EXPLAIN 或 EXPLAIN ANALYZE 来进行诊断。
2、EXPLAIN返回结果介绍
EXPLAIN SELECT /*+ INL_JOIN(t2) */ * FROM t1 JOIN t2 ON t1.a = t2.a;
+---------------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------+ | IndexJoin_12 | 12487.50 | root | | inner join, inner:IndexLookUp_11, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a) | | ├─TableReader_24(Build) | 9990.00 | root | | data:Selection_23 | | │ └─Selection_23 | 9990.00 | cop[tikv] | | not(isnull(test.t1.a)) | | │ └─TableFullScan_22 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo | | └─IndexLookUp_11(Probe) | 12487.50 | root | | | | ├─Selection_10(Build) | 12487.50 | cop[tikv] | | not(isnull(test.t2.a)) | | │ └─IndexRangeScan_8 | 12500.00 | cop[tikv] | table:t2, index:ia(a) | range: decided by [eq(test.t2.a, test.t1.a)], keep order:false, stats:pseudo | | └─TableRowIDScan_9(Probe) | 12487.50 | cop[tikv] | table:t2 | keep order:false, stats:pseudo | +---------------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------+
EXPLAIN 的返回结果包含以下字段：
id：为算子名，或执行 SQL 语句需要执行的子任务。estRows：为显示 TiDB 预计会处理的行数。该预估数可能基于字典信息（例如访问方法基于主键或唯一键），或基于CMSketch或直方图等统计信息估算而来。task：显示算子在执行语句时的所在位置。access-object：显示被访问的表、分区和索引。显示的索引为部分索引。尤其是在有组合索引的情况下，该字段显示的信息很有参考意义。operator info：显示访问表、分区和索引的其他信息。
2.1、算子（id）
算子是为返回查询结果而执行的特定步骤。真正执行扫表（读盘或者读 TiKV Block Cache）操作的算子有如下几类：
- TableFullScan：全表扫描。
- TableRangeScan：带有范围的表数据扫描。
- TableRowIDScan：TableRowIDScan 是一种数据扫描算子，在 TiDB 等数据库中用于直接通过行 ID 来获取表中的数据行，这种方式比全表扫描或者通过复杂的索引查找更加直接和高效
- RowID：在 TiDB 中，行 ID（Row ID）是用于唯一标识表中每一行数据的标识符。它类似于主键的作用，是数据库内部用于管理和定位行数据的重要依据。每一行都有一个唯一的行 ID，这个 ID 在整个表的范围内是独一无二的，就像每个人都有一个独一无二的身份证号码一样。行 ID 通常是由数据库系统自动生成的，并不是表中的某个字段。行 ID 最重要的作用是能够实现对数据行的快速定位。
- IndexFullScan（可相对提升效率）：另一种“全表扫描”，扫的是索引数据，不是表数据。
- IndexRangeScan：带有范围的索引数据扫描操作。
TiDB 会汇聚 TiKV/TiFlash 上扫描的数据或者计算结果，这种“数据汇聚”算子目前有如下几类：
- TableReader：TableReader 是一种用于读取表数据的操作。TableReader 通常会和其他操作（如TableFullScan、TableRangeScan 、IndexScan、Selection 等）协同工作。它可以接收来自其他操作的数据请求，例如，在经过 IndexScan 获取了一些索引相关的数据后，如果还需要获取表中的其列他数据（可能涉及回表操作），TableReader 就会被调用，从表中读取完整的数据行。
- IndexReader（可相对提升效率）：将 TiKV 上底层扫表算子 IndexFullScan 或 IndexRangeScan 得到的数据进行汇总。
- IndexLookUp（通常与回表有关）：在 TiDB 执行计划中，IndexLookUp 算子用于通过索引查找数据，并获取相关的行记录。它结合了索引扫描和根据索引结果获取完整数据行的操作。IndexLookUp 算子在执行过程中常常会触发回表，导致带来额外的开销，影响性能。当通过索引扫描找到满足条件的索引项后，如果查询需要获取除索引列之外的其他列信息，就会根据索引项中的主键值或其他行标识信息回到表中获取完整的行记录，这个过程就是回表。
- 例如，在一个 “员工表” 中有 “部门名称” 二级索引，执行查询
SELECT * FROM employees WHERE department_name = 'IT'时，IndexLookUp 会先通过 “部门名称” 索引找到符合条件的索引项（包含部门名称和对应的员工主键），然后因为查询要求获取所有列（*），所以需要回表，根据主键获取员工姓名、工资等其他列的信息。
- 例如，在一个 “员工表” 中有 “部门名称” 二级索引，执行查询
- IndexMerge：和
IndexLookupReader类似，可以看做是它的扩展，可以同时读取多个索引的数据，有多个 Build 端，一个 Probe 端。执行过程也很类似，先汇总所有 Build 端 TiKV 扫描上来的 RowID，再去 Probe 端上根据这些 RowID 精确地读取 TiKV 上的数据。Build 端是IndexFullScan或IndexRangeScan类型的算子，Probe 端是TableRowIDScan类型的算子。 Point_Get和Batch_Point_Get：TiDB 直接从主键或唯一键检索数据时会使用Point_Get或Batch_Point_Get算子。这两个算子比IndexLookup更有效率。
其他算子说明：
- Projection：在 TiDB 的执行计划中，Projection 算子（投影算子）主要用于选择查询结果中的列，并且可以对这些列进行一些简单的表达式计算或者重命名操作。它的作用是确定最终返回给用户的列集合。
- Top：TOP 算子用于从查询结果的顶部获取指定数量的行。它主要用于限制结果集的大小，返回最符合条件的前面若干行数据。例如，在一个销售数据表中，想要获取销售额最高的前 10 名员工的记录，就可以使用类似 TOP 算子的功能。
- Limit：LIMIT 算子的主要优点是能够有效地控制返回给用户的数据量，这在分页查询等场景中非常有用。LIMIT 算子不仅可以实现类似 TOP 算子获取前面若干行的功能（当偏移量为 0 时），还能实现从中间某一行开始获取指定行数的功能。
- HashJoin：HashJoin 是一种数据库中用于表连接（JOIN）的操作算法。它主要是基于哈希（Hash）原理来高效地合并两个表中的数据。包括Build阶段和Probe阶段，Build阶段会选择驱动表并构建哈希表，Probe阶段会扫描被驱动表，将两张表进行连接。
- Selection（筛选）：筛选操作，会以上一步得到的数据作为输入，根据给定的条件（如
WHERE子句）进一步筛选数据。
2.2、task
目前 TiDB 的计算任务分为两种不同的 task：cop task（TIKV执行）、root task（TIDB执行）和 tiflash。Cop task 是指使用 TiKV 中的 Coprocessor 执行的计算任务，root task 是指在 TiDB 中执行的计算任务。
SQL 优化的目标之一是将计算尽可能地下推到 TiKV 中执行，避免在 root 中执行计算。TiKV 中的 Coprocessor 能支持大部分 SQL 内建函数（包括聚合函数和标量函数）、SQL LIMIT 操作、索引扫描和表扫描。
2.3、operator info
EXPLAIN 返回结果中 operator info 列可显示诸如条件下推等信息。operator info 结果各字段解释如下：
range: [1,1]表示查询的WHERE字句 (a = 1) 被下推到了 TiKV，对应的 task 为cop[tikv]。keep order:false表示该查询的语义不需要 TiKV 按顺序返回结果。如果查询指定了排序（例如SELECT * FROM t WHERE a = 1 ORDER BY id），该字段的返回结果为keep order:true。stats:pseudo表示estRows显示的预估数可能不准确。
3、算子的执行顺序
算子的结构是树状的，在查询执行过程中，一般都是子节点先执行。在 TiDB 执行计划的树形结构中，数据通常是从叶子节点（子节点）向根节点（父节点）流动的。这意味着子节点的操作会先执行，其产生的结果作为输入传递给父节点进行后续处理。
这种从子节点到父节点的执行顺序也是为了优化查询执行过程。通过先在子节点进行局部的数据筛选、排序等操作，可以减少传递给父节点的数据量，从而提高整个查询的效率。例如，在一个复杂的多表查询中，子节点的选择（Selection）和投影（Projection）操作可以预先过滤掉不需要的数据，使得后续的连接操作（父节点）在较小的数据量上进行，降低了计算资源的消耗和执行时间。
3.1、Build和Probe操作
Build 总是先于 Probe 执行，并且 Build 总是出现在 Probe 前面。即如果一个算子有多个子节点，子节点 ID 后面有 Build 关键字的算子总是先于有 Probe 关键字的算子执行。TiDB 在展现执行计划的时候，Build 端总是第一个出现，接着才是 Probe 端。
- Build操作：在 Hash Join 的执行计划中，Build 操作主要是构建一个哈希表。这个哈希表的构建基于连接操作中的一张表（通常是较小的表，也称为驱动表）。
- 例如，在一个简单的查询
SELECT * FROM table1 JOIN table2 ON table1.custId= table2.custId中，如果选择 table1 作为 Build 表，TiDB 会先扫描 table1 的数据，根据连接键（custId）的值，通过特定的哈希函数计算哈希值。然后，将custId以及对应的table1表中的行数据（或者行数据的引用）存储到哈希表中。这个哈希表的结构有助于后续快速查找匹配的行。
- 例如，在一个简单的查询
- Probe操作：Probe 阶段是在 Build 阶段构建好哈希表之后才开始的。Probe 操作主要是针对连接操作中的另一张表（被驱动表，在上述例子中是 table2）。TiDB 会扫描 table2 的数据，对于每一行中的连接键（custId）的值，使用相同的哈希函数计算哈希值，在 Build 阶段构建的哈希表中进行查找，如果找到匹配的键值，就将两行数据（来自 table1 和 table2）进行连接组合，生成连接后的结果。所以，Probe 操作依赖于 Build 阶段构建的哈希表，必须在 Build 之后才能执行。
4、统计信息的健康度
EXPLAIN ANALYZE 执行时的 actRows 可能跟 estRows 差别很大，即跟实际值差别很大，可以先执行 ANALYZE TABLE 再执行 EXPLAIN ANALYZE，预估数与实际数会更接近。
ANALYZE TABLE xxx;
当在执行计划中看到 stats:pseudo，即表示统计信息健康程度不够，就可执行 anlyze table xxx语句了。
除 ANALYZE TABLE 外，达到 tidb_auto_analyze_ratio 阈值后，TiDB 会自动在后台重新生成统计数据。若要查看 TiDB 有多接近该阈值（即 TiDB 判断统计数据有多健康），可执行 SHOW STATS_HEALTHY 语句。
4、MPP模式（切分为多个 MPP片段进行查询）
TiDB 支持使用 MPP 模式来执行查询，TiDB 的 MPP（Massively Parallel Processing）模式是一种大规模并行处理架构，旨在通过多个节点并行执行任务来提高数据处理和查询的性能与效率。
架构原理：
- 分布式存储与计算：TiDB 采用分布式架构，数据分散存储在多个 TiKV 节点上。在 MPP 模式下，当执行查询或其他数据处理任务时，TiDB 会将任务分解并分发到多个节点上并行执行。每个节点负责处理一部分数据，然后将结果汇总返回。
- 数据分片与并行处理：数据在 TiKV 节点上通过 Region 进行分片，每个 Region 包含一定范围的数据。TiDB 根据查询条件确定需要访问的 Region，并将查询任务发送到对应的 TiKV 节点。这些节点可以同时处理各自负责的 Region 数据，实现并行计算。
注意，MPP 模式通常只会对有 TiFlash 副本的表生效。（在某些特定的场景或配置下，即使没有 TiFlash 副本，TiDB 也可能会尝试使用类似 MPP 的执行方式。例如在执行一些分布式的 JOIN 操作或者 UNION ALL 操作时，TiDB 可能会在 TiKV 节点之间进行数据的交换和并行处理，但这种情况与严格意义上基于 TiFlash 的 MPP 模式有所不同，其并行处理的程度和效果可能会受到一定限制。）
示例：
ALTER TABLE t1 set tiflash replica 1; ANALYZE TABLE t1; SET tidb_allow_mpp = 1;
7、Optimizer Hints控制执行计划
TiDB 支持 Optimizer Hints 语法，它基于 MySQL 5.7 中介绍的类似 comment 的语法，例如 /*+ HINT_NAME(t1, t2) */，当 TiDB 优化器选择的不是最优查询计划时，可以使用 Optimizer Hints 指定使用哪个执行计划。
7.1、语法
Optimizer Hints 不区分大小写，通过 /*+ ... */ 注释的形式跟在 SELECT、UPDATE 或 DELETE 关键字的后面，如果注释不是跟在指定的关键字后，会被当作是普通的 MySQL comment，注释不会生效。INSERT 关键字后不支持 Optimizer Hints。多个不同的 Hint 之间需用逗号隔开。
示例：
SELECT /*+ USE_INDEX(t1, idx1), HASH_AGG(), HASH_JOIN(t1) */ count(*) FROM t t1, t t2 WHERE t1.a = t2.b;
如果 Optimizer Hints 包含语法错误或不完整，查询语句不会报错，而是按照没有 Optimizer Hints 的情况执行。如果 Hint 不适用于当前语句，TiDB 会返回 Warning，用户可以在查询结束后通过 Show Warnings 命令查看具体信息。
7.2、 Hint分类
TiDB 目前支持的 Optimizer Hints 根据生效范围的不同可以划分为两类：
- 第一类是在查询块范围生效的 Hint，例如
/*+ HASH_AGG() */； - 第二类是在整个查询范围生效的 Hint，例如
/*+ MEMORY_QUOTA(1024 MB)*/。
每条语句中每一个查询和子查询都对应着一个不同的查询块，每个查询块有自己对应的名字。以下面这条语句为例：
SELECT * FROM (SELECT * FROM t) t1, (SELECT * FROM t) t2;
该查询语句有 3 个查询块，最外面一层 SELECT 所在的查询块的名字为 sel_1，两个 SELECT 子查询的名字依次为 sel_2 和 sel_3。其中数字序号根据 SELECT 出现的位置从左到右计数。TIDB规定 select 查询块以 sel 开头，DELETE 和 UPDATE 分别以 del、upd 开头，如果分别用 DELETE 和 UPDATE 查询替代上述第一个 SELECT 查询，则对应的查询块名字分别为 del_1 和 upd_1。
7.2.1、块范围生效的 Hint
这类 Hint 可以跟在查询语句中任意 SELECT、UPDATE 或 DELETE 关键字的后面。通过在 Hint 中使用查询块名字可以控制 Hint 的生效范围，以及准确标识查询中的每一个表（有可能表的名字或者别名相同），方便明确 Hint 的参数指向。若不显式地在 Hint 中指定查询块，Hint 默认作用于当前查询块。以如下查询为例：
SELECT /*+ HASH_JOIN(@sel_1 t1@sel_1, t3) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
该 Hint 在 sel_1 这个查询块中生效，参数分别为 sel_1 中的 t1 表（sel_2 中也有一个 t1 表）和 t3 表。
如上例所述，在 Hint 中使用查询块名字的方式有两种：
- 第一种是作为 Hint 的第一个参数，与其他参数用空格隔开。除
QB_NAME外，本节所列的所有 Hint 除自身明确列出的参数外都有一个隐藏的可选参数@QB_NAME，通过使用这个参数可以指定该 Hint 的生效范围； - 第二种在 Hint 中使用查询块名字的方式是在参数中的某一个表名后面加
@QB_NAME，如上面的 t1@sel_1 用法，用以明确指出该参数是哪个查询块中的表。
7.3、QB_NAME（指定查询块的名称）
当查询语句是包含多层嵌套子查询的复杂语句时，识别某个查询块的序号和名字很可能会出错，Hint QB_NAME 可以方便我们使用查询块。QB_NAME 是 Query Block Name 的缩写，用于为某个查询块指定新的名字，同时查询块原本默认的名字依然有效。例如：
SELECT /*+ QB_NAME(QB1) */ * FROM (SELECT * FROM t) t1, (SELECT * FROM t) t2;
这条 Hint 将最外层 SELECT 查询块的命名为 QB1，此时 QB1 和默认名称 sel_1 对于这个查询块来说都是有效的。（注意，如果指定的 QB_NAME 为 sel_2，并且不给原本 sel_2 对应的第二个查询块指定新的 QB_NAME，则第二个查询块的默认名字 sel_2 会失效。）
浙公网安备 33010602011771号