IndexMerge for multi-valued index cannot hit cached table
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
The multi-valued index uses IndexMerge operator to do index scan and then fetch data. However, if the table is set to a cached table, the IndexMerge will still send request to TiKV instead of using table cache in TiDB. This should be a compatibility issue for cached table and multi-valued index.
```
mysql> CREATE TABLE t1 (a INT, j JSON, INDEX idx((CAST(j AS CHAR(3) ARRAY))));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t1 values(1,'["US"]');
Query OK, 1 row affected (0.00 sec)
mysql> alter table t1 cache;
Query OK, 0 rows affected (0.20 sec)
mysql> explain analyze select * from t1 use index(idx) where 'US' member of (j);
+-----------------------------------+---------+---------+-----------+-------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------------------+---------+---------+-----------+-------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------+
| Projection_5 | 0.80 | 1 | root | | time:3.08ms, loops:2, RU:1.024202, Concurrency:OFF | test.t1.a, test.t1.j | 1.36 KB | N/A |
| └─UnionScan_6 | 1.00 | 1 | root | | time:3.07ms, loops:2 | json_memberof(cast("US", json BINARY), test.t1.j) | N/A | N/A |
| └─IndexMerge_9 | 1.00 | 1 | root | | time:3.04ms, loops:3, index_task:{fetch_handle:1.351554ms, merge:6.724µs}, table_task:{num:1, concurrency:5, fetch_row:1.343729ms, wait_time:1.632878ms} | type: union | 16.2 KB | N/A |
| ├─IndexRangeScan_7(Build) | 1.00 | 1 | cop[tikv] | table:t1, index:idx(cast(`j` as char(3) array)) | time:1.32ms, loops:3, cop_task: {num: 1, max: 1.17ms, proc_keys: 1, tot_proc: 116.3µs, tot_wait: 244.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 47.5µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:1.13ms}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 46, total_keys: 2, get_snapshot_time: 54.5µs, rocksdb: {key_skipped_count: 1, block: {}}}, time_detail: {total_process_time: 116.3µs, total_wait_time: 244.6µs, tikv_wall_time: 612.2µs} | range:["US","US"], keep order:false, stats:pseudo | N/A | N/A |
| └─TableRowIDScan_8(Probe) | 1.00 | 1 | cop[tikv] | table:t1 | time:1.11ms, loops:2, cop_task: {num: 1, max: 947.9µs, proc_keys: 1, tot_proc: 101.6µs, tot_wait: 106.8µs, copr_cache_hit_ratio: 0.00, build_task_duration: 31.7µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:913.4µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 57, total_keys: 1, get_snapshot_time: 49µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 101.6µs, total_wait_time: 106.8µs, tikv_wall_time: 462.8µs} | keep order:false, stats:pseudo | N/A | N/A |
+-----------------------------------+---------+---------+-----------+-------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------+
5 rows in set (0.00 sec)
mysql> trace select * from t1 use index(idx) where 'US' member of (j);
+-------------------------------------------------------------------------+-----------------+------------+
| operation | startTS | duration |
+-------------------------------------------------------------------------+-----------------+------------+
| trace | 17:36:29.337159 | 5.253464ms |
| ├─session.ExecuteStmt | 17:36:29.337169 | 1.347708ms |
| │ ├─executor.Compile | 17:36:29.337221 | 725.904µs |
| │ └─session.runStmt | 17:36:29.337981 | 490.862µs |
| │ └─UnionScanExec.Open | 17:36:29.338370 | 74.545µs |
| │ ├─buildMemIndexMergeReader | 17:36:29.338394 | 2.766µs |
| │ └─memIndexMergeReader.getMemRows | 17:36:29.338413 | 14.554µs |
| ├─*executor.ProjectionExec.Next | 17:36:29.338534 | 3.722918ms |
| │ └─*executor.UnionScanExec.Next | 17:36:29.338538 | 3.695533ms |
| │ ├─*executor.IndexMergeReaderExecutor.Next | 17:36:29.338546 | 3.607029ms |
| │ │ ├─distsql.Select | 17:36:29.338663 | 113.744µs |
| │ │ │ └─regionRequest.SendReqCtx | 17:36:29.338906 | 1.312148ms |
| │ │ │ └─rpcClient.SendRequest, region ID: 14, type: Cop | 17:36:29.338982 | 1.176707ms |
| │ │ │ └─tikv.RPC | 17:36:29.338986 | 546.86µs |
| │ │ │ ├─tikv.Wait | 17:36:29.338986 | 130.59µs |
| │ │ │ │ └─tikv.GetSnapshot | 17:36:29.338986 | 53.393µs |
| │ │ │ └─tikv.Process | 17:36:29.339116 | 129.195µs |
| │ │ ├─distsql.Select | 17:36:29.340616 | 60.678µs |
| │ │ │ └─regionRequest.SendReqCtx | 17:36:29.340803 | 1.135737ms |
| │ │ │ └─rpcClient.SendRequest, region ID: 14, type: Cop | 17:36:29.340846 | 1.060031ms |
| │ │ │ └─tikv.RPC | 17:36:29.340848 | 516.713µs |
| │ │ │ ├─tikv.Wait | 17:36:29.340848 | 147.027µs |
| │ │ │ │ └─tikv.GetSnapshot | 17:36:29.340848 | 57.153µs |
| │ │ │ └─tikv.Process | 17:36:29.340995 | 123.803µs |
| │ │ ├─*executor.TableReaderExecutor.Next | 17:36:29.340762 | 1.290193ms |
| │ │ └─*executor.TableReaderExecutor.Next | 17:36:29.342086 | 12.866µs |
| │ └─*executor.IndexMergeReaderExecutor.Next | 17:36:29.342219 | 2.044µs |
| └─*executor.ProjectionExec.Next | 17:36:29.342277 | 40.914µs |
| └─*executor.UnionScanExec.Next | 17:36:29.342282 | 23.349µs |
| └─*executor.IndexMergeReaderExecutor.Next | 17:36:29.342293 | 1.246µs |
+-------------------------------------------------------------------------+-----------------+------------+
30 rows in set (0.01 sec)
```
As a comparison, the IndexLookUp operator can hit table cache.
```
mysql> CREATE TABLE t2 (a INT, j CHAR(3), INDEX idx(j));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into t2 values(1,'US');
Query OK, 1 row affected (0.00 sec)
mysql> alter table t2 cache;
Query OK, 0 rows affected (0.19 sec)
// execute several times
mysql> explain analyze select * from t2 use index(idx) where j='US';
+-----------------------------------+---------+---------+-----------+------------------------+-----------------------------------------------------+---------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------------------+---------+---------+-----------+------------------------+-----------------------------------------------------+---------------------------------------------------+---------+------+
| Projection_5 | 0.00 | 1 | root | | time:16.3µs, loops:2, RU:0.000000, Concurrency:OFF | test.t2.a, test.t2.j | 11.0 KB | N/A |
| └─UnionScan_6 | 1.00 | 1 | root | | time:9.89µs, loops:2 | eq(test.t2.j, "US") | N/A | N/A |
| └─IndexLookUp_9 | 1.00 | 0 | root | | time:0s, loops:0 | | N/A | N/A |
| ├─IndexRangeScan_7(Build) | 1.00 | 0 | cop[tikv] | table:t2, index:idx(j) | | range:["US","US"], keep order:false, stats:pseudo | N/A | N/A |
| └─TableRowIDScan_8(Probe) | 1.00 | 0 | cop[tikv] | table:t2 | | keep order:false, stats:pseudo | N/A | N/A |
+-----------------------------------+---------+---------+-----------+------------------------+-----------------------------------------------------+---------------------------------------------------+---------+------+
5 rows in set (0.00 sec)
mysql> trace select * from t2 use index(idx) where j='US';
+-------------------------------------------------+-----------------+------------+
| operation | startTS | duration |
+-------------------------------------------------+-----------------+------------+
| trace | 17:40:57.693814 | 1.360472ms |
| ├─session.ExecuteStmt | 17:40:57.693827 | 1.194916ms |
| │ ├─executor.Compile | 17:40:57.693859 | 622.235µs |
| │ └─session.runStmt | 17:40:57.694518 | 442.789µs |
| │ └─UnionScanExec.Open | 17:40:57.694816 | 122.886µs |
| │ ├─buildMemIndexLookUpReader | 17:40:57.694830 | 2.954µs |
| │ └─memIndexLookUpReader.getMemRows | 17:40:57.694848 | 74.831µs |
| │ └─memTableReader.getMemRows | 17:40:57.694887 | 26.294µs |
| ├─*executor.ProjectionExec.Next | 17:40:57.695045 | 29.356µs |
| │ └─*executor.UnionScanExec.Next | 17:40:57.695050 | 11.651µs |
| └─*executor.ProjectionExec.Next | 17:40:57.695090 | 15.176µs |
| └─*executor.UnionScanExec.Next | 17:40:57.695093 | 3.495µs |
+-------------------------------------------------+-----------------+------------+
12 rows in set (0.00 sec)
```
Contributor guide
Assessment
This issue has not been assessed yet.