执行计划的阅读

🪐 TiDB 技术问题 TiFlash
1

mysql> explain analyze select store_id,job_code from employees force index(idx_id_hired_store) where (id=200 and lname=‘sylar’) or (id=300 and lname=‘john’) or (id=400 and lname=‘foller’) and hired>‘2024
-02-01’ and hired<=‘2024-05-01’;
±----------------------------------±--------±--------±----------±---------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±----------------------------------±--------±--------±----------±---------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
| Projection_4 | 18.00 | 0 | root | | time:1.11ms, loops:1, Concurrency:OFF | test.employees.store_id, test.employees.job_code | 2.36 KB | N/A |
| └─Projection_9 | 18.00 | 0 | root | | time:1.11ms, loops:1, Concurrency:OFF | test.employees.id, test.employees.lname, test.employees.hired, test.employees.job_code, test.employees.store_id | 3.73 KB | N/A |
| └─IndexLookUp_8 | 18.00 | 0 | root | | time:1.11ms, loops:1 | | 216 Bytes | N/A |
| ├─IndexRangeScan_5(Build) | 22.50 | 0 | cop[tikv] | table:employees, index:idx_id_hired_store(id, hired, store_id) | time:972.5µs, loops:1, cop_task: {num: 1, max: 894.9µs, proc_keys: 0, rpc_num: 1, rpc_time: 866.1µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 3, get_snapshot_time: 143.8µs, rocksdb: {block: {}}} | range:[200,200], [300,300], (400 “2024-02-01”,400 “2024-05-01”], keep order:false, stats:pseudo | N/A | N/A |
| └─Selection_7(Probe) | 18.00 | 0 | cop[tikv] | | | or(and(eq(test.employees.id, 200), eq(test.employees.lname, “sylar”)), or(and(eq(test.employees.id, 300), eq(test.employees.lname, “john”)), and(and(eq(test.employees.id, 400), eq(test.employees.lname, “foller”)), and(gt(test.employees.hired, “2024-02-01”), le(test.employees.hired, “2024-05-01”))))) | N/A | N/A |
| └─TableRowIDScan_6 | 22.50 | 0 | cop[tikv] | table:employees | | keep order:false, stats:pseudo | N/A | N/A |
±----------------------------------±--------±--------±----------±---------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±----------±-----+
6 rows in set (0.00 sec)

一个执行计划如上，咨询下这里的“ range:[200,200], [300,300], (400 “2024-02-01”,400 “2024-05-01”]”，如何理解？

2

构造的谓词条件范围

3

你的or条件就是多个区间，or内是等值区间考试跟结束就是一样的了

1 个赞
4

和你的where条件匹配

5

对应查询里面的 or 过滤

6

where (id=200 and lname=‘sylar’) or (id=300 and lname=‘john’) or (id=400 and lname=‘foller’) and hired>‘2024-02-01’ and hired<=‘2024-05-01’;

就是黑体部分这几个条件，如果你要问为啥没有lname相关的内容出现，是因为

table:employees, index:idx_id_hired_store(id, hired, store_id)

employees表下的idx_id_hired_store索引中不含有lname字段。
所以lname的相关过滤在下面的selection算子中处理了。

7
  1. “range:[200,200]”：这表示 TiDB 在执行查询时使用了一个范围查询，其中值落在闭区间 [200, 200] 内。这可能意味着查询需要检索具有某个特定值的行，例如主键为 200 的行。
  2. “[300,300]”：类似于第一项，这也是一个范围查询，但这次的范围是 [300, 300]，即值为 300 的行。
  3. “(400 “2024-02-01”,400 “2024-05-01”]”：这是一个时间范围查询。在 TiDB 中，时间范围可能以特定的时间点或日期范围的形式出现。在这个例子中，查询选择了从 “2024-02-01”（包括这个日期）到 “2024-05-01”（不包括这个日期）之间的时间范围内的行，这是一个半开半闭区间。这意味着 TiDB 将检索所有时间戳在 “2024-02-01” 和 “2024-05-01” 之间的数据。