「TiDB查询优化及调优」系列文章将通过一些具体的案例，向大家介绍TiDB查询及优化相关的原理和应用，在文章中我们简要介绍了TiDB查询优化器的优化流程。
查询计划（executionplan）展现了数据库执行SQL语句的具体步骤，例如通过索引还是全表扫描访问表中的数据，连接查询的实现方式和连接的顺序等。查阅及理解TiDB的查询计划是查询调优的基础。本文为系列文章的第二篇，将着重介绍TiDB查询计划以及如何查看。
算子及Task
在上文的TiDB查询优化流程简介中有提到过，TiDB的查询计划是由一系列的执行算子构成，这些算子是为返回查询结果而执行的特定步骤，例如表扫描算子，聚合算子，Join算子等，下面以表扫描算子为例，其它算子的具体解释可以参看下文查看执行计划的小结。
执行表扫描（读盘或者读TiKVBlockCache）操作的算子有如下几类：
TableFullScan：全表扫描。
TableRangeScan：带有范围的表数据扫描。
TableRowIDScan：根据上层传递下来的RowID扫描表数据。时常在索引读操作后检索符合条件的行。
IndexFullScan：另一种“全表扫描”，扫的是索引数据，不是表数据。
目前TiDB的计算任务分为两种不同的task：coptask和roottask。Coptask是指使用TiKV中的Coprocessor执行的计算任务，roottask是指在TiDB中执行的计算任务。
SQL优化的目标之一是将计算尽可能地下推到TiKV中执行。TiKV中的Coprocessor能支持大部分SQL内建函数（包括聚合函数和标量函数）、SQLLIMIT操作、索引扫描和表扫描。但是，所有的Join操作都只能作为roottask在TiDB上执行。
利用EXPLAIN查看分析查询计划
与其它主流商业数据库一样，TiDB中可以通过EXPLAIN语句返回的结果查看某条SQL的执行计划。
EXPLAIN语句
目前TiDB的EXPLAIN主要输出5列，分别是：id，estRows，task，accessobject，operatorinfo。执行计划中每个算子都由这5列属性来描述，EXPLAIN结果中每一行描述一个算子。每个属性的具体含义如下：
EXPLAINANALYZE语句
和EXPLAIN不同，EXPLAINANALYZE会执行对应的SQL语句，记录其运行时信息，和执行计划一并返回出来，可以视为EXPLAIN语句的扩展。EXPLAINANALYZE语句的返回结果中增加了actRows,executioninfo,memory,disk这几列信息：
例如在下例中，优化器估算的estRows和实际执行中统计得到的actRows几乎是相等的，说明优化器估算的行数与实际行数的误差很小。同时IndexLookUp_10算子在实际执行过程中使用了约9KB的内存，该SQL在执行过程中，没有触发过任何算子的落盘操作。
mysqlexplainanalyzeselect*fromtwherea10;+-------------------------------+---------+---------+-----------+-------------------------+------------------------------------------------------------------------+-----------------------------------------------------+---------------+------+
id
estRows
actRows
task
accessobject
executioninfo
operatorinfo
memory
disk
+-------------------------------+---------+---------+-----------+-------------------------+------------------------------------------------------------------------+-----------------------------------------------------+---------------+------+
IndexLookUp_10
9.00
9
root
time:.s,loops:2,rpcnum:1,rpctime:.s,prockeys:0
9.KB
N/A
├─IndexRangeScan_8(Build)
9.00
9
cop[tikv]
table:t,index:idx_a(a)
time:.94s,loops:10,
range:[-inf,10),keeporder:false
N/A
N/A
└─TableRowIDScan_9(Probe)
9.00
9
cop[tikv]
table:t
time:.s,loops:10
keeporder:false
N/A
N/A
+-------------------------------+---------+---------+-----------+-------------------------+------------------------------------------------------------------------+-----------------------------------------------------+---------------+------+3rowsinset(0.00sec)
查看计划中算子的执行顺序
TiDB的执行计划是一个树形结构，树中每个节点即是算子。考虑到每个算子内多线程并发执行的情况，在一条SQL执行的过程中，如果能够有一个手术刀把这棵树切开看看，大家可能会发现所有的算子都正在消耗CPU和内存处理数据，从这个角度来看，算子是没有执行顺序的。
但是如果从一行数据先后被哪些算子处理的角度来看，一条数据在算子上的执行是有顺序的。这个顺序可以通过下面这个规则简单总结出来：
Build总是先于Probe执行，并且Build总是出现Probe前面
这个原则的前半句是说：如果一个算子有多个子节点，子节点ID后面有Build关键字的算子总是先于有Probe关键字的算子执行。后半句是说：TiDB在展现执行计划的时候，Build端总是第一个出现，接着才是Probe端。例如：
TiDB(root
.0.0.1:test)explainselect*fromtuseindex(idx_a)wherea=1;+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+id
estRows
task
accessobject
operatorinfo
+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+
IndexLookUp_7
10.00
root
├─IndexRangeScan_5(Build)
10.00
cop[tikv]
table:t,index:idx_a(a)
range:[1,1],keeporder:false,stats:pseudo
└─TableRowIDScan_6(Probe)
10.00
cop[tikv]
table:t
keeporder:false,stats:pseudo
+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+3rowsinset(0.00sec)
这里IndexLookUp_7算子有两个孩子节点：IndexRangeScan_5(Build)和TableRowIDScan_6(Probe)。可以看到，IndexRangeScan_5(Build)是第一个出现的，并且基于上面这条规则，要得到一条数据，需要先执行它得到一个RowID以后，再由TableRowIDScan_6(Probe)根据前者读上来的RowID去获取完整的一行数据。
这种规则隐含的另一个信息是：在同一层级的节点中，出现在最前面的算子可能是最先被执行的，而出现在最末尾的算子可能是最后被执行的。
例如下面这个例子：
TiDB(root
.0.0.1:test)explainselect*fromtt1useindex(idx_a)jointt2useindex()wheret1.a=t2.a;+----------------------------------+----------+-----------+--------------------------+------------------------------------------------------------------+id
estRows
task
accessobject
operatorinfo
+----------------------------------+----------+-----------+--------------------------+------------------------------------------------------------------+
HashJoin_22
.50
root
innerjoin,inner:TableReader_26,equal:[eq(test.t.a,test.t.a)]
├─TableReader_26(Build)
.00
root
data:Selection_25
│└─Selection_25
.00
cop[tikv]
not(isnull(test.t.a))
│└─TableFullScan_24
.00
cop[tikv]
table:t2
keeporder:false,stats:pseudo
└─IndexLookUp_29(Probe)
.00
root
├─IndexFullScan_27(Build)
.00
cop[tikv]
table:t1,index:idx_a(a)
keeporder:false,stats:pseudo
└─TableRowIDScan_28(Probe)
.00
cop[tikv]
table:t1
keeporder:false,stats:pseudo
+----------------------------------+----------+-----------+--------------------------+------------------------------------------------------------------+7rowsinset(0.00sec)
要完成HashJoin_22，需要先执行TableReader_26(Build)再执行IndexLookUp_29(Probe)。而在执行IndexLookUp_29(Probe)的时候，又需要先执行IndexFullScan_27(Build)再执行TableRowIDScan_28(Probe)。所以从整条执行链路来看，TableRowIDScan_28(Probe)是最后被唤起执行的。
查看表扫描的执行计划
在上文介绍算子和任务时已经提到过表扫描算子，这里再稍微重复介绍一下，分为执行表扫描操作的算子和对扫描数据进行汇聚和计算的算子：
执行表扫描（读盘或者读TiKVBlockCache）操作的算子有如下几类：
TableFullScan：全表扫描。
TableRangeScan：带有范围的表数据扫描。
TableRowIDScan：根据上层传递下来的RowID扫描表数据。时常在索引读操作后检索符合条件的行。
IndexFullScan：另一种“全表扫描”，扫的是索引数据，不是表数据。
IndexRangeScan：带有范围的索引数据扫描操作。
TiDB会汇聚TiKV/TiFlash上扫描的数据或者计算结果，这种“数据汇聚”算子目前有如下几类：
TableReader：将TiKV上底层扫表算子TableFullScan或TableRangeScan得到的数据进行汇总。
IndexReader：将TiKV上底层扫表算子IndexFullScan或IndexRangeScan得到的数据进行汇总。
IndexLookUp：先汇总Build端TiKV扫描上来的RowID，再去Probe端上根据这些RowID精确地读取TiKV上的数据。Build端是IndexFullScan或IndexRangeScan类型的算子，Probe端是TableRowIDScan类型的算子。
IndexMerge：和IndexLookupReader类似，可以看做是它的扩展，可以同时读取多个索引的数据，有多个Build端，一个Probe端。执行过程也很类似，先汇总所有Build端TiKV扫描上来的RowID，再去Probe端上根据这些RowID精确地读取TiKV上的数据。Build端是IndexFullScan或IndexRangeScan类型的算子，Probe端是TableRowIDScan类型的算子。
IndexLookUp示例：
mysqlexplainselect*fromtuseindex(idx_a);+-------------------------------+----------+-----------+-------------------------+--------------------------------+
id
estRows
task
accessobject
operatorinfo
+-------------------------------+----------+-----------+-------------------------+--------------------------------+
IndexLookUp_6
.00
root
├─IndexFullScan_4(Build)
.00
cop[tikv]
table:t,index:idx_a(a)
keeporder:false,stats:pseudo
└─TableRowIDScan_5(Probe)
.00
cop[tikv]
table:t
keeporder:false,stats:pseudo
+-------------------------------+----------+-----------+-------------------------+--------------------------------+3rowsinset(0.00sec)
这里IndexLookUp_6算子有两个孩子节点：IndexFullScan_4(Build)和TableRowIDScan_5(Probe)。可以看到，IndexFullScan_4(Build)执行索引全表扫，扫描索引a的所有数据，因为是全范围扫，这个操作将获得表中所有数据的RowID，之后再由TableRowIDScan_5(Probe)去根据这些RowID去扫描所有的表数据。可以预见的是，这个执行计划不如直接使用TableReader进行全表扫，因为同样都是全表扫，这里的IndexLookUp多扫了一次索引，带来了额外的开销。
TableReader示例：
mysqlexplainselect*fromtwherea1orb;+-------------------------+----------+-----------+---------------+----------------------------------------+
id
estRows
task
accessobject
operatorinfo
+-------------------------+----------+-----------+---------------+----------------------------------------+
TableReader_7
.00
root
data:Selection_6
└─Selection_6
.00
cop[tikv]
or(gt(test.t.a,1),gt(test.t.b,))
└─TableFullScan_5
.00
cop[tikv]
table:t
keeporder:false,stats:pseudo
+-------------------------+----------+-----------+---------------+----------------------------------------+3rowsinset(0.00sec)
在上面例子中TableReader_7算子的孩子节点是Selection_6。以这个孩子节点为根的子树被当做了一个CopTask下发给了相应的TiKV，这个CopTask使用TableFullScan_5算子执行扫表操作。Selection表示SQL语句中的选择条件，可能来自SQL语句中的WHERE/HAVING/ON子句。由TableFullScan_5可以看到，这个执行计划使用了一个全表扫描的操作，集群的负载将因此而上升，可能会影响到集群中正在运行的其他查询。这时候如果能够建立合适的索引，并且使用IndexMerge算子，将能够极大的提升查询的性能，降低集群的负载。
IndexMerge示例：
注意：目前TIDB的IndexMerge为实验特性在5.3及以前版本中默认关闭，同时5.0中的IndexMerge目前支持的场景仅限于析取范式（or连接的表达式），对合取范式（and连接的表达式）将在之后的版本中支持。开启IndexMerge特性，可通过在客户端中设置session或者global变量完成：set
tidb_enable_index_merge=1;mysqlset
tidb_enable_index_merge=1;mysqlexplainselect*fromtuseindex(idx_a,idx_b)wherea1orb1;+------------------------------+---------+-----------+-------------------------+------------------------------------------------+id
estRows
task
accessobject
operatorinfo
+------------------------------+---------+-----------+-------------------------+------------------------------------------------+
IndexMerge_16
.67
root
├─IndexRangeScan_13(Build)
.33
cop[tikv]
table:t,index:idx_a(a)
range:(1,+inf],keeporder:false,stats:pseudo
├─IndexRangeScan_14(Build)
.33
cop[tikv]
table:t,index:idx_b(b)
range:(1,+inf],keeporder:false,stats:pseudo
└─TableRowIDScan_15(Probe)
.67
cop[tikv]
table:t
keeporder:false,stats:pseudo
+------------------------------+---------+-----------+-------------------------+------------------------------------------------+4rowsinset(0.00sec)
IndexMerge使得数据库在扫描表数据时可以使用多个索引。这里IndexMerge_16算子有三个孩子节点，其中IndexRangeScan_13和IndexRangeScan_14根据范围扫描得到符合条件的所有RowID，再由TableRowIDScan_15算子根据这些RowID精确的读取所有满足条件的数据。
查看聚合计算的执行计划
HashAggregate示例：
TiDB上的HashAggregation算子采用多线程并发优化，执行速度快，但会消耗较多内存。下面是一个HashAggregate的例子：
TiDB(root
.0.0.1:test)explainselect/*+HASH_AGG()*/count(*)fromt;+---------------------------+----------+-----------+---------------+---------------------------------+id
estRows
task
accessobject
operatorinfo
+---------------------------+----------+-----------+---------------+---------------------------------+
HashAgg_11
1.00
root
funcs:count(Column#7)-Column#4
└─TableReader_12
1.00
root
data:HashAgg_5
└─HashAgg_5
1.00
cop[tikv]
funcs:count(1)-Column#7
└─TableFullScan_8
.00
cop[tikv]
table:t
keeporder:false,stats:pseudo
+---------------------------+----------+-----------+---------------+---------------------------------+4rowsinset(0.00sec)
一般而言TiDB的HashAggregate会分成两个阶段执行，一个在TiKV/TiFlash的Coprocessor上，计算聚合函数的中间结果。另一个在TiDB层，汇总所有CoprocessorTask的中间结果后，得到最终结果。
StreamAggregate示例：
TiDBStreamAggregation算子通常会比HashAggregate占用更少的内存，有些场景中也会比HashAggregate执行的更快。当数据量太大或者系统内存不足时，可以试试StreamAggregate算子。一个StreamAggregate的例子如下：
TiDB(root
.0.0.1:test)explainselect/*+STREAM_AGG()*/count(*)fromt;+----------------------------+----------+-----------+---------------+---------------------------------+id
estRows
task
accessobject
operatorinfo
+----------------------------+----------+-----------+---------------+---------------------------------+
StreamAgg_16
1.00
root
funcs:count(Column#7)-Column#4
└─TableReader_17
1.00
root
data:StreamAgg_8
└─StreamAgg_8
1.00
cop[tikv]
funcs:count(1)-Column#7
└─TableFullScan_13
.00
cop[tikv]
table:t
keeporder:false,stats:pseudo
+----------------------------+----------+-----------+---------------+---------------------------------+4rowsinset(0.00sec)
和HashAggregate类似，一般而言TiDB的StreamAggregate也会分成两个阶段执行，一个在TiKV/TiFlash的Coprocessor上，计算聚合函数的中间结果。另一个在TiDB层，汇总所有CoprocessorTask的中间结果后，得到最终结果。
查看Join的执行计划
TiDB的Join算法包括如下几类：
HashJoin
MergeJoin
IndexHashJoin
IndexMergeJoin
Apply
下面分别通过一些例子来解释这些Join算法的执行过程
HashJoin示例：
TiDB的HashJoin算子采用了多线程优化，执行速度较快，但会消耗较多内存。一个HashJoin的例子如下：
mysqlexplainselect/*+HASH_JOIN(t1,t2)*/*fromtt1joint2ont1.a=t2.a;+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+
id
estRows
task
accessobject
operatorinfo
+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+
HashJoin_33
.00
root
innerjoin,inner:TableReader_43,equal:[eq(test.t.a,test.t2.a)]
├─TableReader_43(Build)
.00
root
data:Selection_42
│└─Selection_42
.00
cop[tikv]
not(isnull(test.t2.a))
│└─TableFullScan_41
.00
cop[tikv]
table:t2
keeporder:false
└─TableReader_37(Probe)
.00
root
data:Selection_36
└─Selection_36
.00
cop[tikv]
not(isnull(test.t.a))
└─TableFullScan_35
.00
cop[tikv]
table:t1
keeporder:false
+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+7rowsinset(0.00sec)
HashJoin会将Build端的数据缓存在内存中，根据这些数据构造出一个HashTable，然后读取Probe端的数据，用Probe端的数据去探测（Probe）Build端构造出来的HashTable，将符合条件的数据返回给用户。
MergeJoin示例：TiDB的MergeJoin算子相比于HashJoin通常会占用更少的内存，但可能执行时间会更久。当数据量太大，或系统内存不足时，建议尝试使用。下面是一个MergeJoin的例子：
mysqlexplainselect/*+SM_JOIN(t1)*/*fromtt1jointt2ont1.a=t2.a;+------------------------------------+----------+-----------+--------------------------+---------------------------------------------------+
id
estRows
task
accessobject
operatorinfo
+------------------------------------+----------+-----------+--------------------------+---------------------------------------------------+
MergeJoin_6
.00
root
innerjoin,leftkey:test.t.a,rightkey:test.t.a
├─IndexLookUp_13(Build)
.00
root
│├─IndexFullScan_11(Build)
.00
cop[tikv]
table:t2,index:idx_a(a)
keeporder:true
│└─TableRowIDScan_12(Probe)
.00
cop[tikv]
table:t2
keeporder:false
└─IndexLookUp_10(Probe)
.00
root
├─IndexFullScan_8(Build)
.00
cop[tikv]
table:t1,index:idx_a(a)
keeporder:true
└─TableRowIDScan_9(Probe)
.00
cop[tikv]
table:t1
keeporder:false
+------------------------------------+----------+-----------+--------------------------+---------------------------------------------------+7rowsinset(0.00sec)
MergeJoin算子在执行时，会从Build端把一个JoinGroup的数据全部读取到内存中，接着再去读Probe端的数据，用Probe端的每行数据去和Build端的完整的一个JoinGroup依次去看是否匹配（除了满足等值条件以外，还有其他非等值条件，这里的“匹配”主要是指查看是否满足非等值职条件）。JoinGroup指的是所有JoinKey上值相同的数据。
IndexHashJoin示例：
INL_HASH_JOIN(t1_name[,tl_name])提示优化器使用IndexNestedLoopHashJoin算法。该算法与IndexNestedLoopJoin使用条件完全一样，但在某些场景下会更为节省内存资源。
mysqlexplainselect/*+INL_HASH_JOIN(t1)*/*fromtt1jointt2ont1.a=t2.a;+----------------------------------+----------+-----------+--------------------------+--------------------------------------------------------------------------+
id
estRows
task
accessobject
operatorinfo
+----------------------------------+----------+-----------+--------------------------+--------------------------------------------------------------------------+
IndexHashJoin_32
.00
root
innerjoin,inner:IndexLookUp_23,outerkey:test.t.a,innerkey:test.t.a
├─TableReader_35(Build)
.00
root
data:Selection_34
│└─Selection_34
.00
cop[tikv]
not(isnull(test.t.a))
│└─TableFullScan_33
.00
cop[tikv]
table:t2
keeporder:false
└─IndexLookUp_23(Probe)
1.00
root
├─Selection_22(Build)
1.00
cop[tikv]
not(isnull(test.t.a))
│└─IndexRangeScan_20
1.00
cop[tikv]
table:t1,index:idx_a(a)
range:decidedby[eq(test.t.a,test.t.a)],keeporder:false
└─TableRowIDScan_21(Probe)
1.00
cop[tikv]
table:t1
keeporder:false
+----------------------------------+----------+-----------+--------------------------+--------------------------------------------------------------------------+8rowsinset(0.00sec)
IndexMergeJoin示例：INL_MERGE_JOIN(t1_name[,tl_name])提示优化器使用IndexNestedLoopMergeJoin算法。该算法相比于INL_JOIN会更节省内存。该算法使用条件包含INL_JOIN的所有使用条件，但还需要添加一条：joinkeys中的内表列集合是内表使用的index的前缀，或内表使用的index是joinkeys中的内表列集合的前缀。
mysqlexplainselect/*+INL_MERGE_JOIN(t2
sel_2)*/*fromtt1wheret1.ain(selectt2.afromtt2wheret2.bt1.b);+---------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------+id
estRows
task
accessobject
operatorinfo
+---------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------+
IndexMergeJoin_23
6.39
root
semijoin,inner:Projection_21,outerkey:test.t.a,innerkey:test.t.a,othercond:lt(test.t.b,test.t.b)
├─TableReader_28(Build)
7.98
root
data:Selection_27
│└─Selection_27
7.98
cop[tikv]
not(isnull(test.t.a)),not(isnull(test.t.b))
│└─TableFullScan_26
8.00
cop[tikv]
table:t1
keeporder:false,stats:pseudo
└─Projection_21(Probe)
1.25
root
test.t.a,test.t.b
└─IndexLookUp_20
1.25
root
├─Selection_18(Build)
1.25
cop[tikv]
not(isnull(test.t.a))
│└─IndexRangeScan_16
1.25
cop[tikv]
table:t2,index:idx_a(a)
range:decidedby[eq(test.t.a,test.t.a)],keeporder:true,stats:pseudo
└─Selection_19(Probe)
1.25
cop[tikv]
not(isnull(test.t.b))
└─TableRowIDScan_17
1.25
cop[tikv]
table:t2
keeporder:false,stats:pseudo
+---------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------+10rowsinset(0.01sec)```sqlApply示例：```sqlmysqlexplainselect*fromtt1wheret1.ain(selectavg(t2.a)fromt2wheret2.bt1.b);+----------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------+
id
estRows
task
accessobject
operatorinfo
+----------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------+
Projection_10
.00
root
test.t.id,test.t.a,test.t.b
└─Apply_12
.00
root
semijoin,inner:StreamAgg_30,equal:[eq(Column#8,Column#7)]
├─Projection_13(Build)
.00
root
test.t.id,test.t.a,test.t.b,cast(test.t.a,decimal(20,0)BINARY)-Column#8
│└─TableReader_15
.00
root
data:TableFullScan_14
│└─TableFullScan_14
.00
cop[tikv]
table:t1
keeporder:false
└─StreamAgg_30(Probe)
1.00
root
funcs:avg(Column#12,Column#13)-Column#7
└─TableReader_31
1.00
root
data:StreamAgg_19
└─StreamAgg_19
1.00
cop[tikv]
funcs:count(test.t2.a)-Column#12,funcs:sum(test.t2.a)-Column#13
└─Selection_29
.00
cop[tikv]
lt(test.t2.b,test.t.b)
└─TableFullScan_28
.00
cop[tikv]
table:t2
keeporder:false
+----------------------------------+----------+-----------+-----------------------------------------------------------------------------------------------+10rowsinset,1warning(0.00sec)
其它关于EXPLAIN的说明
EXPLAINFORCONNECTION用于获得一个连接中最后执行的查询的执行计划，其输出格式与EXPLAIN完全一致。但TiDB中的实现与MySQL不同，除了输出格式之外，还有以下区别：
MySQL返回的是正在执行的查询计划，而TiDB返回的是最后执行的查询计划。
MySQL的文档中指出，MySQL要求登录用户与被查询的连接相同，或者拥有PROCESS权限，而TiDB则要求登录用户与被查询的连接相同，或者拥有SUPER权限。
本文为「TiDB查询优化及调优」系列文章的第二篇，后续将继续对TiDB慢查询诊断监控及排查、调整及优化查询执行计划以及其他优化器开发或规划中的诊断调优功能等进行介绍。如果您对TiDB的产品有任何建议，欢迎来到