TiDB Query Execution Plan Overview

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 -S command. Then, after the EXPLAIN result 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:

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:

Understand EXPLAIN output

The following describes the output of the EXPLAIN statement above:

id describes the name of an operator, or sub-task that is required to execute the SQL statement. See Operator overview for additional details.
estRows shows 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.
task shows where an operator is performing the work. See Task overview for additional details.
access object shows the table, partition and index that is being accessed. The parts of the index are also shown, as in the case above that the column a from the index was used. This can be useful in cases where you have composite indexes.
operator info shows 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 IndexJoin and Apply operators, the meaning of estRows since v6.4.0 is different from that before v6.4.0.

Before v6.4.0, estRows means 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, estRows means the total number of estimated rows to be processed by the probe side operators. The actual number of rows displayed (indicated by the actRows column) in the result of EXPLAIN ANALYZE means the total row count, so since v6.4.0 the meanings of estRows and actRows for the probe side child nodes of IndexJoin and Apply operators 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 |
+---------------------------------+----------+-----------+-----------------------+------------------------------------------------------------------------------+

Operator overview

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 in TiKV or TiFlash.
IndexReader: Aggregates the data obtained by the underlying operators like IndexFullScan or IndexRangeScan in TiKV.
IndexLookUp: First aggregates the RowID (in TiKV) scanned by the Build side. Then at the Probe side, accurately reads the data from TiKV based on these RowIDs. At the Build side, there are operators like IndexFullScan or IndexRangeScan; at the Probe side, there is the TableRowIDScan operator.
IndexMerge: Similar to IndexLookUp. IndexMerge can be seen as an extension of IndexLookupReader. IndexMerge supports reading multiple indexes at the same time. There are many Builds and one Probe. The execution process of IndexMerge the same as that of IndexLookUp.

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.

Range query

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) < 1992 cannot use an index, but date_column < '1992-01-01 can.
It is recommended to compare data of the same type and character set and collation. Mixing types may require additional cast operations, or prevent indexes from being used.
You can also use AND (intersection) and OR (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 a is an equivalent query, continue to figure out the query range of b; when b is also an equivalent query, continue to figure out the query range of c.
- Otherwise, if a is a non-equivalent query, you can only figure out the range of a.

Task overview

TiDB calculation tasks are categorized into four types: root task, cop task, batchCop task, and MPP task:

Root task: executed in TiDB.
Cop task: executed using TiKV Coprocessor or TiFlash Coprocessor.
BatchCop task: an optimized version of TiFlash cop tasks, allowing multiple Regions to be queried in a single task.
MPP task: executed using TiFlash's MPP mode.

A key goal of SQL optimization is to push calculations down to TiKV or TiFlash whenever possible to improve query efficiency. TiKV Coprocessor supports most of the built-in SQL functions (including aggregate functions and scalar functions), LIMIT operations, index scans, and table scans. TiFlash Coprocessor is similar to TiKV Coprocessor in functionality but does not support index scans.

Operator info overview

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 of cop[tikv]).
keep order:false shows 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 as SELECT * FROM t WHERE a = 1 ORDER BY id), then this condition would be keep order:true.
stats:pseudo shows that the estimates shown in estRows might not be accurate. TiDB periodically updates statistics as part of a background operation. A manual update can also be performed by running ANALYZE 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.

TiDB Query Execution Plan Overview

Understand EXPLAIN output

Operator overview

Range query

Task overview

Operator info overview

Was this page helpful?