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.

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 like TableFullScan or TableRangeScan in TiKV.
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 can not 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

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. However, all Join operations can only be performed as root tasks in TiDB.

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.