tidb limit执行计划疑惑

TiDBer_KkruFifg · 2022 年9 月 22 日 02:07

【 TiDB 使用环境】生产环境 /测试/ Poc
Poc

【 TiDB 版本】
5.1.4

【遇到的问题】
为什么下面这个sql limit 1，但是执行计划里tikv层的 actRows 是7 （Limit_28(Build) ）
这个不应该是 1 吗？帮忙看下，谢谢

desc analyze SELECT status, expiry_time FROM modell_var_tab force index (idx_tag_entity) WHERE tag_id = 8884146564707019816 AND (entity_id >= 470781680 AND entity_type >= ‘user’ ) ORDER BY entity_id, entity_type LIMIT 1;
±-------------------------------------±--------±--------±----------±---------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| id | estRows | actRows | task | access object | execution info

                    | operator info
                                                            | memory  | disk |

±-------------------------------------±--------±--------±----------±---------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| Projection_8 | 0.84 | 1 | root | | time:3.27ms, loops:2, Concurrency:OFF

                    | userdb_tes_mode_aa_db.modell_var_tab.status, userdb_tes_mode_aa_db.modell_var_tab.expiry_time
                                                            | 2.07 KB | N/A  |

                    | offset:0, count:1
                                                            | N/A     | N/A  |

| └─Projection_30 | 0.84 | 1 | root | | time:3.26ms, loops:1, Concurrency:OFF | userdb_tes_mode_aa_db.modell_var_tab.entity_id, userdb_tes_mode_aa_db.modell_var_tab.entity_type, userdb_tes_mode_aa_db.modell_var_tab.tag_id, userdb_tes_mode_aa_db.modell_var_tab.status, userdb_tes_mode_aa_db.modell_var_tab.expiry_time | 2.43 KB | N/A |
| └─IndexLookUp_29 | 0.84 | 1 | root | | time:3.26ms, loops:1, index_task: {total_time: 1.11ms, fetch_handle: 1.1ms, build: 3.82µs, wait: 3.14µs}, table_task: {total_time: 15.4ms, num: 3, concurrency: 5} | | 7.48 KB | N/A |
| ├─Limit_28(Build) | 0.84 | 7 | cop[tikv] | | time:1.1ms, loops:4, cop_task: {num: 7, max: 1.07ms, min: 854µs, avg: 953.3µs, p95: 1.07ms, max_proc_keys: 32, p95_proc_keys: 32, tot_proc: 2ms, tot_wait: 1ms, rpc_num: 7, rpc_time: 6.6ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7}, scan_detail: {total_process_keys: 224, total_keys: 231, rocksdb: {delete_skipped_count: 0, key_skipped_count: 224, block: {cache_hit_count: 91, read_count: 1, read_byte: 31.2 KB}}} | offset:0, count:1 | N/A | N/A |
| │ └─Selection_27 | 0.84 | 224 | cop[tikv] | | tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7} | ge(userdb_tes_mode_aa_db.modell_var_tab.entity_type, “user”) | N/A | N/A |
| │ └─IndexRangeScan_25 | 0.84 | 224 | cop[tikv] | table:modell_var_tab, index:idx_tag_entity(tag_id, entity_id, entity_type) | tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7} | range:[8884146564707019816 470781680,8884146564707019816 +inf], keep order:true | N/A | N/A |
| └─TableRowIDScan_26(Probe) | 0.84 | 7 | cop[tikv] | table:modell_var_tab | time:1.82ms, loops:6, cop_task: {num: 7, max: 601.6µs, min: 518.9µs, avg: 564.6µs, p95: 601.6µs, max_proc_keys: 1, p95_proc_keys: 1, rpc_num: 7, rpc_time: 3.89ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:7, tasks:7}, scan_detail: {total_process_keys: 7, total_keys: 7, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 84, read_count: 0, read_byte: 0 Bytes}}} | keep order:false, stats:pseudo | N/A | N/A |
±-------------------------------------±--------±--------±----------±---------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
【复现路径】做过哪些操作出现的问题
【问题现象及影响】

【附件】

请提供各个组件的 version 信息，如 cdc/tikv，可通过执行 cdc version/tikv-server --version 获取。

TiDBer_KkruFifg · 2022 年9 月 22 日 02:09

desc analyze SELECT `status`,         `expiry_time`     FROM modell_var_tab force index (idx_tag_entity)     WHERE tag_id = 8884146564707019816            AND (entity_id >= 470781680             AND entity_type >= 'user'         )     ORDER BY  entity_id, entity_type LIMIT 1;
+--------------------------------------+---------+---------+-----------+----------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                                   | estRows | actRows | task      | access object                                                              | execution info


                        | operator info
                                                                | memory  | disk |
+--------------------------------------+---------+---------+-----------+----------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_8                         | 0.84    | 1       | root      |                                                                            | time:3.27ms, loops:2, Concurrency:OFF


                        | userdb_tes_mode_aa_db.modell_var_tab.status, userdb_tes_mode_aa_db.modell_var_tab.expiry_time
                                                                | 2.07 KB | N/A  |
| └─Limit_14                           | 0.84    | 1       | root      |                                                                            | time:3.26ms, loops:2


                        | offset:0, count:1
                                                                | N/A     | N/A  |
|   └─Projection_30                    | 0.84    | 1       | root      |                                                                            | time:3.26ms, loops:1, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                 | userdb_tes_mode_aa_db.modell_var_tab.entity_id, userdb_tes_mode_aa_db.modell_var_tab.entity_type, userdb_tes_mode_aa_db.modell_var_tab.tag_id, userdb_tes_mode_aa_db.modell_var_tab.status, userdb_tes_mode_aa_db.modell_var_tab.expiry_time | 2.43 KB | N/A  |
|     └─IndexLookUp_29                 | 0.84    | 1       | root      |                                                                            | time:3.26ms, loops:1, index_task: {total_time: 1.11ms, fetch_handle: 1.1ms, build: 3.82µs, wait: 3.14µs}, table_task: {total_time: 15.4ms, num: 3, concurrency: 5}                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                              | 7.48 KB | N/A  |
|       ├─Limit_28(Build)              | 0.84    | 7       | cop[tikv] |                                                                            | time:1.1ms, loops:4, cop_task: {num: 7, max: 1.07ms, min: 854µs, avg: 953.3µs, p95: 1.07ms, max_proc_keys: 32, p95_proc_keys: 32, tot_proc: 2ms, tot_wait: 1ms, rpc_num: 7, rpc_time: 6.6ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7}, scan_detail: {total_process_keys: 224, total_keys: 231, rocksdb: {delete_skipped_count: 0, key_skipped_count: 224, block: {cache_hit_count: 91, read_count: 1, read_byte: 31.2 KB}}}   | offset:0, count:1                                                                                                                                                                                                                            | N/A     | N/A  |
|       │ └─Selection_27               | 0.84    | 224     | cop[tikv] |                                                                            | tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7}                                                                                                                                                                                                                                                                                                                                                                                                                   | ge(userdb_tes_mode_aa_db.modell_var_tab.entity_type, "user")                                                                                                                                                                                 | N/A     | N/A  |
|       │   └─IndexRangeScan_25        | 0.84    | 224     | cop[tikv] | table:modell_var_tab, index:idx_tag_entity(tag_id, entity_id, entity_type) | tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7}                                                                                                                                                                                                                                                                                                                                                                                                                  | range:[8884146564707019816 470781680,8884146564707019816 +inf], keep order:true                                                                                                                                                              | N/A     | N/A  |
|       └─TableRowIDScan_26(Probe)     | 0.84    | 7       | cop[tikv] | table:modell_var_tab                                                       | time:1.82ms, loops:6, cop_task: {num: 7, max: 601.6µs, min: 518.9µs, avg: 564.6µs, p95: 601.6µs, max_proc_keys: 1, p95_proc_keys: 1, rpc_num: 7, rpc_time: 3.89ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:7, tasks:7}, scan_detail: {total_process_keys: 7, total_keys: 7, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 84, read_count: 0, read_byte: 0 Bytes}}}                                      | keep order:false, stats:pseudo                                                                                                                                                                                                               | N/A     | N/A  |
+--------------------------------------+---------+---------+-----------+----------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+

Meditator · 2022 年9 月 22 日 03:21

最好发下表结构

TiDBer_KkruFifg · 2022 年9 月 22 日 03:33

CREATE TABLE tb_model (
entity_id bigint(20) unsigned NOT NULL,
entity_type char(8) NOT NULL,
tag_id bigint(20) unsigned NOT NULL,
region char(4) NOT NULL,
status tinyint(3) unsigned NOT NULL DEFAULT ‘1’,
expiry_time bigint(20) unsigned NOT NULL DEFAULT ‘0’,
create_time bigint(20) unsigned NOT NULL,
update_time bigint(20) unsigned NOT NULL,
id bigint(20) NOT NULL,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
KEY idx_tag_entity (tag_id,entity_id,entity_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

tidb狂热爱好者 · 2022 年9 月 22 日 04:53

先取7个再限制1个出来数据是取了7个

h5n1 · 2022 年9 月 22 日 05:36

cop_task: {num: 7 7个coptask,每个返回一行

TiDBer_KkruFifg · 2022 年9 月 22 日 06:12

请问，只 limit 1，为什么要取7行数据？

https://docs.pingcap.com/zh/tidb/v5.1/sql-statement-explain-analyze#explain-analyze-输出格式

actRows 表示算子实际输出的数据条数。

Meditator · 2022 年9 月 22 日 08:11

因为在tikv层是并行查询，并发7个任务，因为是limit 1，所以每个task返回符合where and order条件的 1条数据，然后在tidb-server层继续汇总排序。至于为什么并发task是7，1）是参数约束 2）查询表的region个数可能是7个

TiDBer_KkruFifg · 2022 年9 月 22 日 09:01

追问一句，是参数 tidb_executor_concurrency 吗？

Meditator · 2022 年9 月 22 日 09:42

不是tidb_executor_concurrency，tidb_executor_concurrency详解如下：

是 tidb_distsql_scan_concurrency，详解如下：

TiDBer_KkruFifg · 2022 年9 月 22 日 10:07

多谢，应该不是这个参数，应该带一个index之类的

system · 2022 年11 月 21 日 10:07

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。