Misunderstanding of indexlookup

In understanding IndexLookUp Before executing the process , First introduce mysql The execution of the index scan is used as a comparison ( Borrow the network diagram here ), One SQL During execution, the storage engine side first reads the primary key of the qualified records in the index ( It may involve ICP、 Combined index, partial columns, etc ), Then read the records in the table according to the primary key , Then return the record to server layer , Again by server The layer returns to the client after filtering according to other conditions . In the above process, index scanning and table return operations are completed on the storage engine side .

IndexLookUp Operator is tidb By scanning the index and then by rowid Scanning process of back table , contain 2 Sub operator IndexScan and TableRowidscan, among IndexLookUp Operator's execution plan task As a root Indicates that the operator is in tidb server Side execution ,2 Of sub operators task As a cop[tikv] Express coprocessor task Push down to tikv perform , In the execution plan, the execution order of operators is to execute the child operator first, and then return data to the parent operator , For the sub operators of parallel depth, the above is executed first (build End ), Then execute another operator with the returned result (probe End ), However, it is not strictly required in the execution process that all operators return results only after execution, or the results are returned to the parent operator only after all child operators are completed .

In the official documents IndexLookUp Introduce the following ：

Summarize first Build End TiKV Scanned RowID, Go again Probe On the end, according to these  RowID  Accurately read TiKV The data on the .Build The end is  IndexFullScan  or  IndexRangeScan  Operator of type ,Probe The end is  TableRowIDScan  Operator of type .

For a long time, due to the pair operator (IndexScan and TableRowidscan) stay tikv Performs a hypothetical index scan and mysql The same is done on the side of the storage engine , Cause to be right IndexLookUp Wrong understanding of the execution process ： stay tikv The end is obtained by index scanning rowid In the after tikv Internal use rowid Return to the table for query , Finally return the result to tidb server Filter again or return to the client .

actually IndexLookUp The execution process is as follows ：

(1)  tidb server according to SQL Conditional construction index scan Of cop task, stay tikv Side scan index to obtain qualified rowid.

(2) Scanned out rowid Return to tidb server To summarize , tidb server according to rowid structure Key and cop task Perform a return operation

(3) tikv Use TableRowIDScan Back to table scanning data , Then return the data tidb, The time in the execution plan includes network time .

Compared with mysql Directly query back to the table in the engine ,tidb IndexLookUp There should be more in the process of execution 2 Second network interaction , Inevitably, there will be delays , So network performance 、tidb server The processing power of IndexLookUp Performance has a certain impact .      

Tidb There are relevant parameters to control indexlookup Performance of ：

tidb_index_lookup_size： Use rowid The number of batches processed when scanning the table

tidb_index_lookup_concurrency： Use rowid Number of concurrency while scanning the table .

test 1： tidb_index_lookup_concurrency influence

Concurrency from 5 Reduced to 1 after , The execution time is 6.15 Second growth to 26.57 second

test 2：tidb_index_lookup_size influence

Increase the number of concurrency tidb_index_lookup_concurrency The improvement effect is not very obvious

Original author ：@h5n1 Published in ：2022/4/8
Link to the original text ：https://tidb.net/blog/e09b5872