latest MVCC versions for each of the two rows. • When TiKV processes a scan request with a range of [k1, k2] and a start timestamp of 8: • Without the in-memory engine (left), it needs to process 11 MVCC reducing request latency and CPU consumption. • When TiKV processes a scan request with a range of [k1, k2] and a start timestamp of 7: • Because the required historical versions are missing in the in-memory j2 JSON, a INT, INDEX k1((CAST(j1->'$.path' AS �→ SIGNED ARRAY))), INDEX k2((CAST(j2->'$.path' AS SIGNED ARRAY))), 2069 �→ INDEX ka(a)); EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3

行记录最新的 2 个 MVCC 版本。 • 当 TiKV 处理一个范围为 [k1, k2]，开始时间戳为 8 的扫描请求时： – 左侧未开启内存引擎时需要处理 11 个 MVCC 版本。 – 右侧开启内存引擎时只需处理 4 个 MVCC 版本，因此减少了请求延时和 CPU 消耗。 • 当 TiKV 处理一个范围为 [k1, k2]，开始时间戳为 7 的扫描请求时： – 由于右侧缺少需要读取的 JSON, j2 JSON, a INT, INDEX k1((CAST(j1->'$.path' AS SIGNED ARRAY))), INDEX k2 �→ ((CAST(j2->'$.path' AS SIGNED ARRAY))), INDEX ka(a)); EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE 1 member of (j1->'$.path') �→ AND 2 member of (j2->'$.path') AND (a = 3); > EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE

j2 JSON, a INT, INDEX k1((CAST(j1->'$.path' AS �→ SIGNED ARRAY))), INDEX k2((CAST(j2->'$.path' AS SIGNED ARRAY))), 1596 �→ INDEX ka(a)); EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE 1 �→ member of (j1->'$.path') AND 2 member of (j2->'$.path') AND (a = 3); > EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE root | �→ | �→ type: union | | ￿─ IndexRangeScan_5(Build) | 10.00 | cop[tikv] | table:t3, index:k1(cast( �→ json_extract(`j1`, _utf8'$.path') as signed array)) | range:[1,1], �→ keep order:false, stats:pseudo

j2 JSON, a INT, INDEX k1((CAST(j1->'$.path' AS �→ SIGNED ARRAY))), INDEX k2((CAST(j2->'$.path' AS SIGNED ARRAY))), 1623 �→ INDEX ka(a)); EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE 1 �→ member of (j1->'$.path') AND 2 member of (j2->'$.path') AND (a = 3); > EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE root | �→ | �→ type: union | | ￿─ IndexRangeScan_5(Build) | 10.00 | cop[tikv] | table:t3, index:k1(cast( �→ json_extract(`j1`, _utf8'$.path') as signed array)) | range:[1,1], �→ keep order:false, stats:pseudo

j2 JSON, a INT, INDEX k1((CAST(j1->'$.path' AS �→ SIGNED ARRAY))), INDEX k2((CAST(j2->'$.path' AS SIGNED ARRAY))), 1645 �→ INDEX ka(a)); EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE 1 �→ member of (j1->'$.path') AND 2 member of (j2->'$.path') AND (a = 3); > EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE root | �→ | �→ type: union | | ￿─ IndexRangeScan_5(Build) | 10.00 | cop[tikv] | table:t3, index:k1(cast( �→ json_extract(`j1`, _utf8'$.path') as signed array)) | range:[1,1], �→ keep order:false, stats:pseudo

j2 JSON, a INT, INDEX k1((CAST(j1->'$.path' AS �→ SIGNED ARRAY))), INDEX k2((CAST(j2->'$.path' AS SIGNED ARRAY))), 1653 �→ INDEX ka(a)); EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE 1 �→ member of (j1->'$.path') AND 2 member of (j2->'$.path') AND (a = 3); > EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE root | �→ | �→ type: union | | ￿─ IndexRangeScan_5(Build) | 10.00 | cop[tikv] | table:t3, index:k1(cast( �→ json_extract(`j1`, _utf8'$.path') as signed array)) | range:[1,1], �→ keep order:false, stats:pseudo

j2 JSON, a INT, INDEX k1((CAST(j1->'$.path' AS �→ SIGNED ARRAY))), INDEX k2((CAST(j2->'$.path' AS SIGNED ARRAY))), 1766 �→ INDEX ka(a)); EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE 1 �→ member of (j1->'$.path') AND 2 member of (j2->'$.path') AND (a = 3); > EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE root | �→ | �→ type: union | | ￿─ IndexRangeScan_5(Build) | 10.00 | cop[tikv] | table:t3, index:k1(cast( �→ json_extract(`j1`, _utf8'$.path') as signed array)) | range:[1,1], �→ keep order:false, stats:pseudo

j2 JSON, a INT, INDEX k1((CAST(j1->'$.path' AS �→ SIGNED ARRAY))), INDEX k2((CAST(j2->'$.path' AS SIGNED ARRAY))), 1641 �→ INDEX ka(a)); EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE 1 �→ member of (j1->'$.path') AND 2 member of (j2->'$.path') AND (a = 3); > EXPLAIN SELECT /*+ use_index_merge(t3, k1, k2, ka) */ * FROM t3 WHERE root | �→ | �→ type: union | | ￿─ IndexRangeScan_5(Build) | 10.00 | cop[tikv] | table:t3, index:k1(cast( �→ json_extract(`j1`, _utf8'$.path') as signed array)) | range:[1,1], �→ keep order:false, stats:pseudo

whether its own index merge logic is AND/OR. mysql> CREATE TABLE t(j1 JSON, j2 JSON, a INT, INDEX k1((CAST(j1->'$.path' �→ AS SIGNED ARRAY))), INDEX k2((CAST(j2->'$.path' AS SIGNED ARRAY))), �→ INDEX INDEX ka(a)); Query OK, 0 rows affected (0.02 sec) tidb> EXPLAIN SELECT /*+ use_index_merge(t, k1, k2, ka) */ * FROM t WHERE �→ (1 member of (j1->'$.path')) AND (2 member of (j2->'$.path')) AND (a �→ = order: �→ false, stats:pseudo | | ￿─ IndexRangeScan_6(Build) | 10.00 | cop[tikv] | table:t, index:k1(cast( �→ json_extract(`j1`, _utf8'$.path') as signed array)) | range:[1,1], �→ keep order:false, stats:pseudo

one multi-valued index can be used to access: mysql> create table t(j1 json, j2 json, a int, INDEX k1((CAST(j1->'$.path' �→ AS SIGNED ARRAY))), INDEX k2((CAST(j2->'$.path' AS SIGNED ARRAY))), �→ INDEX INDEX ka(a)); Query OK, 0 rows affected (0.02 sec) mysql> explain select /*+ use_index_merge(t, k1, k2, ka) */ * from t where �→ (1 member of (j1->'$.path')) and (2 member of (j2->'$.path')) and (a �→ = | �→ | �→ type: union �→ �→ | | ￿─ IndexRangeScan_6(Build) | 10.00 | cop[tikv] | table:t, index:k1(cast �→ (json_extract(`j1`, _utf8'$.path') as signed array)) | range:[1,1], �→ keep order:false, stats:pseudo