| title | summary | aliases | ||||||
|---|---|---|---|---|---|---|---|---|
TiDB Query Execution Plan Overview |
Learn about the execution plan information returned by the `EXPLAIN` statement in TiDB. |
|
Note:
When you use the MySQL client to connect to TiDB, to read the output result in a clearer way without line wrapping, you can use the
pager less -Scommand. Then, after theEXPLAINresult is output, you can press the right arrow → button on your keyboard to horizontally scroll through the output.
SQL is a declarative language. It describes what the results of a query should look like, not the methodology to actually retrieve those results. TiDB considers all the possible ways in which a query could be executed, including using what order to join tables and whether any potential indexes can be used. The process of considering query execution plans is known as SQL optimization.
The EXPLAIN statement shows the selected execution plan for a given statement. That is, after considering hundreds or thousands of ways in which the query could be executed, TiDB believes that this plan will consume the least resources and execute in the shortest amount of time:
{{< copyable "sql" >}}
CREATE TABLE t (id INT NOT NULL PRIMARY KEY auto_increment, a INT NOT NULL, pad1 VARCHAR(255), INDEX(a));
INSERT INTO t VALUES (1, 1, 'aaa'),(2,2, 'bbb');
EXPLAIN SELECT * FROM t WHERE a = 1;Query OK, 0 rows affected (0.96 sec)
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
+-------------------------------+---------+-----------+---------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+---------------------+---------------------------------------------+
| IndexLookUp_10 | 10.00 | root | | |
| ├─IndexRangeScan_8(Build) | 10.00 | cop[tikv] | table:t, index:a(a) | range:[1,1], keep order:false, stats:pseudo |
| └─TableRowIDScan_9(Probe) | 10.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+---------------------+---------------------------------------------+
3 rows in set (0.00 sec)EXPLAIN does not execute the actual query. EXPLAIN ANALYZE can be used to execute the query and show EXPLAIN information. This can be useful in diagnosing cases where the execution plan selected is suboptimal. For additional examples of using EXPLAIN, see the following documents:
The following describes the output of the EXPLAIN statement above:
iddescribes the name of an operator, or sub-task that is required to execute the SQL statement. See Operator overview for additional details.estRowsshows an estimate of the number of rows TiDB expects to process. This number might be based on dictionary information, such as when the access method is based on a primary or unique key, or it could be based on statistics such as a CMSketch or histogram.taskshows where an operator is performing the work. See Task overview for additional details.access objectshows the table, partition and index that is being accessed. The parts of the index are also shown, as in the case above that the columnafrom the index was used. This can be useful in cases where you have composite indexes.operator infoshows additional details about the access. See Operator info overview for additional details.
Note:
In the returned execution plan, for all probe-side child nodes of
IndexJoinandApplyoperators, the meaning ofestRowssince v6.4.0 is different from that before v6.4.0.Before v6.4.0,
estRowsmeans the number of estimated rows to be processed by the probe side operators for each row from the build side operators. Since v6.4.0,estRowsmeans the total number of estimated rows to be processed by the probe side operators. The actual number of rows displayed (indicated by theactRowscolumn) in the result ofEXPLAIN ANALYZEmeans the total row count, so since v6.4.0 the meanings ofestRowsandactRowsfor the probe side child nodes ofIndexJoinandApplyoperators are consistent.For example:
CREATE TABLE t1(a INT, b INT); CREATE TABLE t2(a INT, b INT, INDEX ia(a)); EXPLAIN SELECT /*+ INL_JOIN(t2) */ * FROM t1 JOIN t2 ON t1.a = t2.a; EXPLAIN SELECT (SELECT a FROM t2 WHERE t2.a = t1.b LIMIT 1) FROM t1;-- Before v6.4.0: +---------------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------+ | 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) | 1.25 | root | | | | ├─Selection_10(Build) | 1.25 | cop[tikv] | | not(isnull(test.t2.a)) | | │ └─IndexRangeScan_8 | 1.25 | 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) | 1.25 | cop[tikv] | table:t2 | keep order:false, stats:pseudo | +---------------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------+ +---------------------------------+----------+-----------+-----------------------+------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+----------+-----------+-----------------------+------------------------------------------------------------------------------+ | Projection_12 | 10000.00 | root | | test.t2.a | | └─Apply_14 | 10000.00 | root | | CARTESIAN left outer join | | ├─TableReader_16(Build) | 10000.00 | root | | data:TableFullScan_15 | | │ └─TableFullScan_15 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo | | └─Limit_17(Probe) | 1.00 | root | | offset:0, count:1 | | └─IndexReader_21 | 1.00 | root | | index:Limit_20 | | └─Limit_20 | 1.00 | cop[tikv] | | offset:0, count:1 | | └─IndexRangeScan_19 | 1.00 | cop[tikv] | table:t2, index:ia(a) | range: decided by [eq(test.t2.a, test.t1.b)], keep order:false, stats:pseudo | +---------------------------------+----------+-----------+-----------------------+------------------------------------------------------------------------------+ -- Since v6.4.0: -- You can find that the `estRows` column values for `IndexLookUp_11`, `Selection_10`, `IndexRangeScan_8`, and `TableRowIDScan_9` since v6.4.0 are different from that before v6.4.0. +---------------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------+ | 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 | +---------------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------+ -- You can find that the `estRows` column values for `Limit_17`, `IndexReader_21`, `Limit_20`, and `IndexRangeScan_19` since v6.4.0 are different from that before v6.4.0. +---------------------------------+----------+-----------+-----------------------+------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+----------+-----------+-----------------------+------------------------------------------------------------------------------+ | Projection_12 | 10000.00 | root | | test.t2.a | | └─Apply_14 | 10000.00 | root | | CARTESIAN left outer join | | ├─TableReader_16(Build) | 10000.00 | root | | data:TableFullScan_15 | | │ └─TableFullScan_15 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo | | └─Limit_17(Probe) | 10000.00 | root | | offset:0, count:1 | | └─IndexReader_21 | 10000.00 | root | | index:Limit_20 | | └─Limit_20 | 10000.00 | cop[tikv] | | offset:0, count:1 | | └─IndexRangeScan_19 | 10000.00 | cop[tikv] | table:t2, index:ia(a) | range: decided by [eq(test.t2.a, test.t1.b)], keep order:false, stats:pseudo | +---------------------------------+----------+-----------+-----------------------+------------------------------------------------------------------------------+
An operator is a particular step that is executed as part of returning query results. The operators that perform table scans (of the disk or the TiKV Block Cache) are listed as follows:
- TableFullScan: Full table scan
- TableRangeScan: Table scans with the specified range
- TableRowIDScan: Scans the table data based on the RowID. Usually follows an index read operation to retrieve the matching data rows.
- IndexFullScan: Similar to a "full table scan", except that an index is scanned, rather than the table data.
- IndexRangeScan: Index scans with the specified range.
TiDB aggregates the data or calculation results scanned from TiKV/TiFlash. The data aggregation operators can be divided into the following categories:
- TableReader: Aggregates the data obtained by the underlying operators like
TableFullScanorTableRangeScanin TiKV. - IndexReader: Aggregates the data obtained by the underlying operators like
IndexFullScanorIndexRangeScanin TiKV. - IndexLookUp: First aggregates the RowID (in TiKV) scanned by the
Buildside. Then at theProbeside, accurately reads the data from TiKV based on these RowIDs. At theBuildside, there are operators likeIndexFullScanorIndexRangeScan; at theProbeside, there is theTableRowIDScanoperator. - IndexMerge: Similar to
IndexLookUp.IndexMergecan be seen as an extension ofIndexLookupReader.IndexMergesupports reading multiple indexes at the same time. There are manyBuilds and oneProbe. The execution process ofIndexMergethe same as that ofIndexLookUp.
While the structure appears as a tree, executing the query does not strictly require the child nodes to be completed before the parent nodes. TiDB supports intra-query parallelism, so a more accurate way to describe the execution is that the child nodes flow into their parent nodes. Parent, child and sibling operators might potentially be executing parts of the query in parallel.
In the previous example, the ├─IndexRangeScan_8(Build) operator finds the internal RowID for rows that match the a(a) index. The └─TableRowIDScan_9(Probe) operator then retrieves these rows from the table.
In the WHERE/HAVING/ON conditions, the TiDB optimizer analyzes the result returned by the primary key query or the index key query. For example, these conditions might include comparison operators of the numeric and date type, such as >, <, =, >=, <=, and the character type such as LIKE.
Note:
- In order to use an index, the condition must be sargable. For example, the condition
YEAR(date_column) < 1992cannot use an index, butdate_column < '1992-01-01can.- It is recommended to compare data of the same type and character set and collation. Mixing types may require additional
castoperations, or prevent indexes from being used.- You can also use
AND(intersection) andOR(union) to combine the range query conditions of one column. For a multi-dimensional composite index, you can use conditions in multiple columns. For example, regarding the composite index(a, b, c):
- When
ais an equivalent query, continue to figure out the query range ofb; whenbis also an equivalent query, continue to figure out the query range ofc.- Otherwise, if
ais a non-equivalent query, you can only figure out the range ofa.
Currently, calculation tasks of TiDB can be divided into two categories: cop tasks and root tasks. A cop[tikv] task indicates that the operator is performed inside the TiKV coprocessor. A root task indicates that it will be completed inside of TiDB.
One of the goals of SQL optimization is to push the calculation down to TiKV as much as possible. The Coprocessor in TiKV supports most of the built-in SQL functions (including the aggregate functions and the scalar functions), SQL LIMIT operations, index scans, and table scans.
The operator info can show useful information such as which conditions were able to be pushed down:
range: [1,1]shows that the predicate from the where clause of the query (a = 1) was pushed right down to TiKV (the task is ofcop[tikv]).keep order:falseshows that the semantics of this query did not require TiKV to return the results in order. If the query were to be modified to require an order (such asSELECT * FROM t WHERE a = 1 ORDER BY id), then this condition would bekeep order:true.stats:pseudoshows that the estimates shown inestRowsmight not be accurate. TiDB periodically updates statistics as part of a background operation. A manual update can also be performed by runningANALYZE TABLE t.
Different operators output different information after the EXPLAIN statement is executed. You can use optimizer hints to control the behavior of the optimizer, and thereby controlling the selection of the physical operators. For example, /*+ HASH_JOIN(t1, t2) */ means that the optimizer uses the Hash Join algorithm. For more details, see Optimizer Hints.