The "TiDB Query Optimization and Tuning" series of articles will introduce the principles and applications related to TiDB query and optimization through some specific cases. In the previous article , we briefly introduced the optimization process of the TiDB query optimizer.
The query plan (execution plan) shows the specific steps of the database to execute the SQL statement, such as accessing the data in the table through an index or a full table scan, the implementation of the join query and the order of the join, etc. Reviewing and understanding TiDB's query plan is the basis of query tuning. This article is the second in a series of articles that will focus on the TiDB query plan and how to view it.
Operators and Tasks
As mentioned in the introduction to the TiDB query optimization process above, the query plan of TiDB is composed of a series of execution operators. These operators are specific steps performed to return query results, such as table scan operators, aggregation Operators, Join operators, etc. The table scan operator is used as an example below. For specific explanations of other operators, see the summary of the execution plan below.
The operators that perform table scan (read disk or read TiKV Block Cache) operations are as follows:
- TableFullScan: Full table scan.
- TableRangeScan: Table data scan with ranges.
- TableRowIDScan: Scan table data according to the RowID passed from the upper layer. From time to time, eligible rows are retrieved after an index read operation.
- IndexFullScan: Another "full table scan" that scans index data, not table data.
Currently, the computing tasks of TiDB are divided into two different tasks: cop task and root task. Cop task refers to the computing task executed using Coprocessor in TiKV, and root task refers to the computing task executed in TiDB.
One of the goals of SQL optimization is to push the computation down to TiKV as much as possible. Coprocessor in TiKV can support most of SQL built-in functions (including aggregate functions and scalar functions), SQL LIMIT operations, index scan and table scan. However, all Join operations can only be performed on TiDB as root tasks.
View analytical query plans with EXPLAIN
Like other mainstream commercial databases, in TiDB, you can view the execution plan of a certain SQL through the result returned by the EXPLAIN statement.
EXPLAIN statement
Currently TiDB the main EXPLAIN output 5, respectively, are: id , estRows , task , access object , operator info . Each operator in the execution plan is described by these 5 column attributes, EXPLAIN Each row in the result describes an operator. The specific meaning of each attribute is as follows:
EXPLAIN ANALYZE statement
Different from ---a74d38b340ed60af1d56623f2ee57ab1 EXPLAIN , EXPLAIN ANALYZE will execute the corresponding SQL statement, record its runtime information, and return it together with the execution plan, which can be regarded as the EXPLAIN statement extension. EXPLAIN ANALYZE statement returns the result in an increase actRows , execution info , memory , disk these columns of information:
For example, in the following example, the optimizer estimated estRows and the actual execution statistics actRows are almost equal, indicating that the error between the number of rows estimated by the optimizer and the actual number of rows is very small . At the same time IndexLookUp_10 the operator uses about 9 KB of memory during the actual execution process. During the execution of this SQL, no drop operation of any operator has been triggered.
mysql> explain analyze select * from t where a < 10;
+-------------------------------+---------+---------+-----------+-------------------------+------------------------------------------------------------------------+-----------------------------------------------------+---------------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------------+---------+---------+-----------+-------------------------+------------------------------------------------------------------------+-----------------------------------------------------+---------------+------+
| IndexLookUp_10 | 9.00 | 9 | root | | time:641.245µs, loops:2, rpc num: 1, rpc time:242.648µs, proc keys:0 | | 9.23046875 KB | N/A |
| ├─IndexRangeScan_8(Build) | 9.00 | 9 | cop[tikv] | table:t, index:idx_a(a) | time:142.94µs, loops:10, | range:[-inf,10), keep order:false | N/A | N/A |
| └─TableRowIDScan_9(Probe) | 9.00 | 9 | cop[tikv] | table:t | time:141.128µs, loops:10 | keep order:false | N/A | N/A |
+-------------------------------+---------+---------+-----------+-------------------------+------------------------------------------------------------------------+-----------------------------------------------------+---------------+------+
3 rows in set (0.00 sec)View the execution order of operators in the plan
The execution plan of TiDB is a tree structure, and each node in the tree is an operator. Considering the concurrent execution of multiple threads in each operator, in the process of executing an SQL, if a scalpel can cut the tree and look at it, you may find that all operators are consuming CPU and memory. Processing data, from this point of view, operators have no order of execution.
However, from the perspective of which operators are successively processed by a row of data, the execution of a row of data on the operators is sequential. This order can be summed up simply by the following rule:
Build always comes before Probe , and Build always comes before Probe
The first half sentence of this principle is to say: if an operator has multiple child nodes, the operator with the keyword Build after the child node ID always precedes the operator with the keyword Probe sub-execution. The second half of the sentence says: When TiDB displays the execution plan, the Build end always appears first, followed by the Probe end. E.g:
TiDB(root@127.0.0.1:test) > explain select * from t use index(idx_a) where a = 1;
+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+
| IndexLookUp_7 | 10.00 | root | | |
| ├─IndexRangeScan_5(Build) | 10.00 | cop[tikv] | table:t, index:idx_a(a) | range:[1,1], keep order:false, stats:pseudo |
| └─TableRowIDScan_6(Probe) | 10.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+
3 rows in set (0.00 sec) Here IndexLookUp_7 operator has two child nodes: IndexRangeScan_5(Build) and TableRowIDScan_6(Probe) . It can be seen that IndexRangeScan_5(Build) is the first to appear, and based on the above rule, to get a piece of data, you need to execute it first to get a RowID and then by TableRowIDScan_6(Probe) read from the former RowID to get a complete line of data.
Another information implied by this rule is that in nodes of the same level, the operator that appears at the front may be executed first, and the operator that appears at the end may be executed last.
For example the following example:
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) To complete HashJoin_22 , you need to execute TableReader_26(Build) and then IndexLookUp_29(Probe) . When executing IndexLookUp_29(Probe) , you need to execute IndexFullScan_27(Build) and then execute TableRowIDScan_28(Probe) . So from the perspective of the entire execution link, TableRowIDScan_28(Probe) is the last to be invoked for execution.
View the execution plan of the table scan
The table scan operator has been mentioned in the introduction of operators and tasks above. Here, I will repeat it a little bit. It is divided into operators that perform table scan operations and operators that aggregate and calculate scanned data:
The operators that perform table scan (read disk or read TiKV Block Cache) operations are as follows:
- TableFullScan: Full table scan.
- TableRangeScan: Table data scan with ranges.
- TableRowIDScan: Scan table data according to the RowID passed from the upper layer. From time to time, eligible rows are retrieved after an index read operation.
- IndexFullScan: Another "full table scan" that scans index data, not table data.
- IndexRangeScan: Index data scan operation with ranges.
TiDB will aggregate the data or calculation results scanned on TiKV/TiFlash. This "data aggregation" operator currently has the following categories:
- TableReader: Summarizes the data obtained by the underlying table scanning operators TableFullScan or TableRangeScan on TiKV.
- IndexReader: Summarizes the data obtained by the bottom-level table scanning operators IndexFullScan or IndexRangeScan on TiKV.
- IndexLookUp: First summarize the RowIDs scanned by TiKV on the Build side, and then go to the Probe side to read the data on TiKV accurately based on these
RowID. The Build side is an operator of typeIndexFullScanorIndexRangeScan, and the Probe side is an operator of typeTableRowIDScan. - IndexMerge: Similar to
IndexLookupReader, it can be regarded as its extension, which can read the data of multiple indexes at the same time, has multiple Build ends, and a Probe end. The execution process is also very similar. First, summarize all the RowIDs scanned by TiKV on the Build side, and then go to the Probe side to accurately read the data on TiKV according to these RowIDs. The Build side is an operator of typeIndexFullScanorIndexRangeScan, and the Probe side is an operator of typeTableRowIDScan.
IndexLookUp example:
mysql> explain select * from t use index(idx_a);
+-------------------------------+----------+-----------+-------------------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+----------+-----------+-------------------------+--------------------------------+
| IndexLookUp_6 | 10000.00 | root | | |
| ├─IndexFullScan_4(Build) | 10000.00 | cop[tikv] | table:t, index:idx_a(a) | keep order:false, stats:pseudo |
| └─TableRowIDScan_5(Probe) | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------------+----------+-----------+-------------------------+--------------------------------+
3 rows in set (0.00 sec) Here IndexLookUp_6 operator has two child nodes: IndexFullScan_4(Build) and TableRowIDScan_5(Probe) . As you can see, IndexFullScan_4(Build) perform index full table scan, scan all data of index a , because it is a full range scan, this operation will get all the data in the table RowID , and then TableRowIDScan_5(Probe) RowID . It is foreseeable that this execution plan is not as good as using TableReader to perform a full table scan, because it is also a full table scan. Here IndexLookUp scans the index one more time, which brings additional overhead.
TableReader example:
mysql> explain select * from t where a > 1 or b >100;
+-------------------------+----------+-----------+---------------+----------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+----------------------------------------+
| TableReader_7 | 8000.00 | root | | data:Selection_6 |
| └─Selection_6 | 8000.00 | cop[tikv] | | or(gt(test.t.a, 1), gt(test.t.b, 100)) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+----------------------------------------+
3 rows in set (0.00 sec) In the above example TableReader_7 the child node of the operator is Selection_6. The subtree rooted at this child node is treated as a Cop Task and sent to the corresponding TiKV, this Cop Task uses the TableFullScan_5 operator to perform the table scan operation . Selection represents the selection condition in the SQL statement, which may come from the WHERE / HAVING / ON clause in the SQL statement. As can be seen from TableFullScan_5 , this execution plan uses a full table scan operation, which will increase the load of the cluster and may affect other queries running in the cluster. At this time, if a suitable index can be established and the IndexMerge operator can be used, the query performance will be greatly improved and the cluster load will be reduced.
IndexMerge example:
Note: Currently TIDB's Index Merge is an experimental feature and is disabled by default in 5.3 and earlier versions, while 5.0's Index Merge currently supports scenarios limited to disjunctive normal form (or connected expressions ), conjunctive normal form (expressions connected by and) will be supported in a later release. Enable the Index Merge feature, which can be done by setting session or global variables in the client: set @@tidb_enable_index_merge = 1;
mysql> set @@tidb_enable_index_merge = 1;
mysql> explain select * from t use index(idx_a, idx_b) where a > 1 or b > 1;
+------------------------------+---------+-----------+-------------------------+------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+---------+-----------+-------------------------+------------------------------------------------+
| IndexMerge_16 | 6666.67 | root | | |
| ├─IndexRangeScan_13(Build) | 3333.33 | cop[tikv] | table:t, index:idx_a(a) | range:(1,+inf], keep order:false, stats:pseudo |
| ├─IndexRangeScan_14(Build) | 3333.33 | cop[tikv] | table:t, index:idx_b(b) | range:(1,+inf], keep order:false, stats:pseudo |
| └─TableRowIDScan_15(Probe) | 6666.67 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+------------------------------+---------+-----------+-------------------------+------------------------------------------------+
4 rows in set (0.00 sec) IndexMerge Enables the database to use multiple indexes when scanning table data. Here IndexMerge_16 the operator has three child nodes, of which IndexRangeScan_13 and IndexRangeScan_14 according to the range scan to get all the eligible ones RowID TableRowIDScan_15 The operator reads all the data that meets the conditions accurately according to these RowID .
View the execution plan of the aggregate calculation
Example of Hash Aggregate:
The Hash Aggregation operator on TiDB adopts multi-threaded concurrency optimization, which is fast in execution, but consumes a lot of memory. Here is an example of a Hash Aggregate:
TiDB(root@127.0.0.1:test) > explain select /*+ HASH_AGG() */ count(*) from t;
+---------------------------+----------+-----------+---------------+---------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+----------+-----------+---------------+---------------------------------+
| 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 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+---------------------------+----------+-----------+---------------+---------------------------------+
4 rows in set (0.00 sec) Generally speaking, TiDB's Hash Aggregate will be executed in two stages, one in TiKV/TiFlash's Coprocessor , the intermediate result of the aggregation function is calculated. The other is at the TiDB layer, after summarizing all intermediate results Coprocessor Task , the final result is obtained.
Stream Aggregate example:
TiDB Stream Aggregation operator usually takes up less memory than Hash Aggregate , and in some scenarios, it is faster than Hash Aggregate . When the amount of data is too large or the system memory is insufficient, you can try the Stream Aggregate operator. An example of Stream Aggregate is as follows:
TiDB(root@127.0.0.1:test) > explain select /*+ STREAM_AGG() */ count(*) from t;
+----------------------------+----------+-----------+---------------+---------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------+----------+-----------+---------------+---------------------------------+
| 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 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+----------------------------+----------+-----------+---------------+---------------------------------+
4 rows in set (0.00 sec) Similar to Hash Aggregate , in general, TiDB's Stream Aggregate will also be executed in two stages, one in TiKV/TiFlash's Coprocessor , the middle of the calculation of the aggregate function result. The other is at the TiDB layer, after summarizing all intermediate results Coprocessor Task to get the final result.
View Join's execution plan
The Join algorithm of TiDB includes the following categories:
- Hash Join
- Merge Join
- Index Hash Join
- Index Merge Join
Apply
The following are some examples to explain the execution process of these Join algorithms
Hash Join example:
TiDB's Hash Join operator adopts multi-thread optimization, which is faster to execute, but consumes more memory. An example of Hash Join is as follows:
mysql> explain select /*+ HASH_JOIN(t1, t2) */ * from t t1 join t2 on t1.a = t2.a;
+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+
| HashJoin_33 | 10000.00 | root | | inner join, inner:TableReader_43, equal:[eq(test.t.a, test.t2.a)] |
| ├─TableReader_43(Build) | 10000.00 | root | | data:Selection_42 |
| │ └─Selection_42 | 10000.00 | cop[tikv] | | not(isnull(test.t2.a)) |
| │ └─TableFullScan_41 | 10000.00 | cop[tikv] | table:t2 | keep order:false |
| └─TableReader_37(Probe) | 10000.00 | root | | data:Selection_36 |
| └─Selection_36 | 10000.00 | cop[tikv] | | not(isnull(test.t.a)) |
| └─TableFullScan_35 | 10000.00 | cop[tikv] | table:t1 | keep order:false |
+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+
7 rows in set (0.00 sec) Hash Join will cache the data on the Build side in the memory, and construct a Hash Table based on these data, and then read the data on the Probe , fd1b0ef1da Use the data from the ---ac83292ff865aeaa4a1c9b5bdf7953aa Probe terminal to detect the （Probe）Build constructed from the Hash Table d6263d99db64c05b099dba55a362cf58--- terminal, and return the qualified data to the user.
Merge Join Example:
TiDB's Merge Join operator usually occupies less memory than Hash Join , but may take longer to execute. When the amount of data is too large, or the system memory is insufficient, it is recommended to try it. Here is an example of Merge Join :
mysql> explain select /*+ SM_JOIN(t1) */ * from t t1 join t t2 on t1.a = t2.a;
+------------------------------------+----------+-----------+--------------------------+---------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+----------+-----------+--------------------------+---------------------------------------------------+
| MergeJoin_6 | 10000.00 | root | | inner join, left key:test.t.a, right key:test.t.a |
| ├─IndexLookUp_13(Build) | 10000.00 | root | | |
| │ ├─IndexFullScan_11(Build) | 10000.00 | cop[tikv] | table:t2, index:idx_a(a) | keep order:true |
| │ └─TableRowIDScan_12(Probe) | 10000.00 | cop[tikv] | table:t2 | keep order:false |
| └─IndexLookUp_10(Probe) | 10000.00 | root | | |
| ├─IndexFullScan_8(Build) | 10000.00 | cop[tikv] | table:t1, index:idx_a(a) | keep order:true |
| └─TableRowIDScan_9(Probe) | 10000.00 | cop[tikv] | table:t1 | keep order:false |
+------------------------------------+----------+-----------+--------------------------+---------------------------------------------------+
7 rows in set (0.00 sec) Merge Join Operator when executed, will be from Build to a terminal Join Group all the data is read into memory, and then read again Probe data of the end, use the data of each row of the Build Probe to compare with the complete one of the Join Group to see if it matches (except for meeting the equivalent conditions, There are other non-equivalent conditions, the "matching" here mainly refers to checking whether the non-equivalent job conditions are met). Join Group refers to all data with the same value on Join Key .
Example of Index Hash Join:
INL_HASH_JOIN(t1_name [, tl_name]) Instructs the optimizer to use the Index Nested Loop Hash Join algorithm. This algorithm is exactly the same as Index Nested Loop Join , but in some scenarios, it will save more memory resources.
mysql> explain select /*+ INL_HASH_JOIN(t1) */ * from t t1 join t t2 on t1.a = t2.a;
+----------------------------------+----------+-----------+--------------------------+--------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+----------+-----------+--------------------------+--------------------------------------------------------------------------+
| IndexHashJoin_32 | 10000.00 | root | | inner join, inner:IndexLookUp_23, outer key:test.t.a, inner key:test.t.a |
| ├─TableReader_35(Build) | 10000.00 | root | | data:Selection_34 |
| │ └─Selection_34 | 10000.00 | cop[tikv] | | not(isnull(test.t.a)) |
| │ └─TableFullScan_33 | 10000.00 | cop[tikv] | table:t2 | keep order: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: decided by [eq(test.t.a, test.t.a)], keep order:false |
| └─TableRowIDScan_21(Probe) | 1.00 | cop[tikv] | table:t1 | keep order:false |
+----------------------------------+----------+-----------+--------------------------+--------------------------------------------------------------------------+
8 rows in set (0.00 sec) Index Merge Join example:
INL_MERGE_JOIN(t1_name [, tl_name]) Instruct the optimizer to use the Index Nested Loop Merge Join algorithm. This algorithm is more memory efficient than INL_JOIN . The use conditions of this algorithm include all the use conditions of INL_JOIN , but one more need to be added: join keys The inner table column set is used by the inner table index prefix , or the prefix used by the inner table index is the prefix of the inner table column set in join keys .
mysql> explain select /*+ INL_MERGE_JOIN(t2@sel_2) */ * from t t1 where t1.a in ( select t2.a from t t2 where t2.b < t1.b);
+---------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------+
| IndexMergeJoin_23 | 6.39 | root | | semi join, inner:Projection_21, outer key:test.t.a, inner key:test.t.a, other cond: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 | keep order: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: decided by [eq(test.t.a, test.t.a)], keep order:true, stats:pseudo |
| └─Selection_19(Probe) | 1.25 | cop[tikv] | | not(isnull(test.t.b)) |
| └─TableRowIDScan_17 | 1.25 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
+---------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)Apply example:
mysql> explain select * from t t1 where t1.a in ( select avg(t2.a) from t2 where t2.b < t1.b);
+----------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------+
| Projection_10 | 10000.00 | root | | test.t.id, test.t.a, test.t.b |
| └─Apply_12 | 10000.00 | root | | semi join, inner:StreamAgg_30, equal:[eq(Column#8, Column#7)] |
| ├─Projection_13(Build) | 10000.00 | root | | test.t.id, test.t.a, test.t.b, cast(test.t.a, decimal(20,0) BINARY)->Column#8 |
| │ └─TableReader_15 | 10000.00 | root | | data:TableFullScan_14 |
| │ └─TableFullScan_14 | 10000.00 | cop[tikv] | table:t1 | keep order: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 | 8000.00 | cop[tikv] | | lt(test.t2.b, test.t.b) |
| └─TableFullScan_28 | 10000.00 | cop[tikv] | table:t2 | keep order:false |
+----------------------------------+----------+-----------+-----------------------------------------------------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)Other notes about EXPLAIN
EXPLAIN FOR CONNECTION is used to obtain the execution plan of the last query executed in a connection, and its output format is exactly the same as EXPLAIN . But the implementation in TiDB is different from MySQL. In addition to the output format, there are the following differences:
MySQL returns the query plan that is being executed, while TiDB returns the last executed query plan.
MySQL's documentation points out that MySQL requires that the login user is the same as the connection being queried, or has PROCESS privileges, while TiDB requires that the login user is the same as the connection being queried, or has SUPER permissions.
This article is the second in a series of articles on "TiDB Query Optimization and Tuning". In the future, we will continue to monitor and troubleshoot slow queries in TiDB, adjust and optimize query execution plans, and other diagnostic and tuning functions in optimizer development or planning. introduce. If you have any suggestions for TiDB products, welcome to https://internals.tidb.io to communicate with us.
Click to see more TiDB query optimization and tuning articles
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。