When an SQL query is executed, the storage engine first reads the primary key (ICP, partial column of combined index, etc.) of the records that meet the conditions in the index, and then reads the records in the table according to the primary key. Records are then returned to the Server layer, which filters them based on other criteria and returns them to the client. In the preceding process, index scanning and table back operations are performed on the storage engine.

IndexLookUp is tiDB scanning the index and then roWId back to the table. Contains two sub-operators IndexScan and TableRowidscan. The root column of the execution plan task of IndexLookUp indicates that the operator is executed on tiDB Server. Cop [tiKV] indicates that the Coprocessor task is pushed down to tiKV for execution. In the execution plan, the execution sequence of the operator is to execute the child operator first and then return data to the parent operator. For the first execution (build end) above the child operator with parallel depth, Then, another operator (probe end) is executed with the return result. However, it is not strictly required that all operators return results after completion of execution or that all child operators return results to the parent operator after completion.

 

IndexLookUp is described as follows:

First collect the RowID scanned by TiKV at Build end, and then Probe end to read the data on TiKV accurately according to these RowID. The Build end is an IndexFullScan or IndexRangeScan operator, and the Probe end is an TableRowIDScan operator.

IndexLookUp (TableRowidscan, TableRowidscan, TableRowidscan, TableRowidscan, TableRowidscan, TableRowidscan, TableRowidscan) After obtaining ROwiD through index scanning at tikV end, roWID is used to query the table in TikV, and finally the results are returned to TIDB server for filtering again or returned to the client.

IndexLookUp executes as follows:

(1) TiDB Server constructs cop Task of Index scan according to SQL conditions and scans index on TIkV side to obtain roWID that meets the conditions.

(2) RoWID scanned is returned to TIDB Server for summary, and TIDB Server constructs Key and COP tasks according to ROWID to perform table operation

(3) TikV uses TableRowIDScan back to the table to scan data, and then returns the data to TIDB. The time in the execution plan includes network time.

Tidb IndexLookUp requires two more network interactions during the execution of tiDB IndexLookUp, which inevitably causes latency. Therefore, network performance and the processing capability of TIDB Server affect IndexLookUp performance to some extent.

Tidb has parameters that control the performance of indexLookup:

Tidb_index_lookup_size: The number of batches processed when scanning tables using ROWId

Tidb_index_lookup_concurrency: The number of concurrency when scanning a table using Rowid.

Test 1: Tidb_index_lookup_concurrency effect

Concurrency Concurrency changes from 5 to 1. The execution time increases from 6.15 seconds to 26.57 seconds

Test 2: Tidb_index_lookup_size effect

Tidb_index_lookup_concurrency is less effective than the concurrency increase

Originally published by: 2022/4/8 link: tidb.net/blog/e09b58…