tidb查询计划疑惑

【概述】 tidb值不一样,查询计划差异

【版本】 5.7.25-TiDB-v5.0.3

mysql> use crm_hub;
Database changed

mysql> explain analyze select id,allocate_condition_id, clue_id,crypto_mobile, chance_type_id, chance_status,item_id,experiment_version,score,batch_id
-> from relation_rule_clue_lgbm
-> where allocate_condition_id=10184163 and update_time > ‘2021-08-10 00:00:00’ order by score desc limit 1;
±---------------------------------±--------±--------±----------±----------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±---------------------------------±--------±--------±----------±----------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| Projection_7 | 1.00 | 1 | root | | time:19.2ms, loops:2, Concurrency:OFF | crm_hub.relation_rule_clue_lgbm.id, crm_hub.relation_rule_clue_lgbm.allocate_condition_id, crm_hub.relation_rule_clue_lgbm.clue_id, crm_hub.relation_rule_clue_lgbm.crypto_mobile, crm_hub.relation_rule_clue_lgbm.chance_type_id, crm_hub.relation_rule_clue_lgbm.chance_status, crm_hub.relation_rule_clue_lgbm.item_id, crm_hub.relation_rule_clue_lgbm.experiment_version, crm_hub.relation_rule_clue_lgbm.score, crm_hub.relation_rule_clue_lgbm.batch_id | 5.00 KB | N/A |
| └─IndexLookUp_33 | 1.00 | 1 | root | | time:19.2ms, loops:2, index_task: {total_time: 18.8ms, fetch_handle: 18.8ms, build: 1.47µs, wait: 2.53µs}, table_task: {total_time: 301.7ms, num: 1, concurrency: 16} | limit embedded(offset:0, count:1) | 13.5 KB | N/A |
| ├─Limit_32(Build) | 1.00 | 1 | cop[tikv] | | time:18.8ms, loops:1, cop_task: {num: 1, max: 18.8ms, proc_keys: 22496, tot_proc: 18ms, rpc_num: 1, rpc_time: 18.8ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:18ms, loops:26}, scan_detail: {total_process_keys: 22496, total_keys: 27359, rocksdb: {delete_skipped_count: 45, key_skipped_count: 27445, block: {cache_hit_count: 34, read_count: 0, read_byte: 0 Bytes}}} | offset:0, count:1 | N/A | N/A |
| │ └─Selection_31 | 1.04 | 113 | cop[tikv] | | tikv_task:{time:18ms, loops:26}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | gt(crm_hub.relation_rule_clue_lgbm.update_time, 2021-08-10 00:00:00.000000) | N/A | N/A |
| │ └─IndexRangeScan_29 | 104.40 | 22496 | cop[tikv] | table:relation_rule_clue_lgbm, index:idx_allocate_condition_id_score_update_time(allocate_condition_id, score, update_time) | tikv_task:{time:14ms, loops:26}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | range:[10184163,10184163], keep order:true, desc | N/A | N/A |
| └─TableRowIDScan_30(Probe) | 1.00 | 1 | cop[tikv] | table:relation_rule_clue_lgbm | time:283.9µs, loops:2, cop_task: {num: 1, max: 245µs, proc_keys: 1, rpc_num: 1, rpc_time: 233.7µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 8, read_count: 0, read_byte: 0 Bytes}}} | keep order:false, stats:pseudo | N/A | N/A |
±---------------------------------±--------±--------±----------±----------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
6 rows in set (0.03 sec)

mysql> explain analyze select id,allocate_condition_id, clue_id,crypto_mobile, chance_type_id, chance_status,item_id,experiment_version,score,batch_id
-> from relation_rule_clue_lgbm
-> where allocate_condition_id=2032241 and update_time > “2021-08-10 00:00:00” order by score desc limit 1;
±-----------------------------------±--------±--------±----------±----------------------------------------------------------------------------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±-----------------------------------±--------±--------±----------±----------------------------------------------------------------------------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| Projection_7 | 0.03 | 1 | root | | time:211.8ms, loops:2, Concurrency:OFF | crm_hub.relation_rule_clue_lgbm.id, crm_hub.relation_rule_clue_lgbm.allocate_condition_id, crm_hub.relation_rule_clue_lgbm.clue_id, crm_hub.relation_rule_clue_lgbm.crypto_mobile, crm_hub.relation_rule_clue_lgbm.chance_type_id, crm_hub.relation_rule_clue_lgbm.chance_status, crm_hub.relation_rule_clue_lgbm.item_id, crm_hub.relation_rule_clue_lgbm.experiment_version, crm_hub.relation_rule_clue_lgbm.score, crm_hub.relation_rule_clue_lgbm.batch_id | 5.00 KB | N/A |
| └─TopN_10 | 0.03 | 1 | root | | time:211.8ms, loops:2 | crm_hub.relation_rule_clue_lgbm.score:desc, offset:0, count:1 | 5.00 KB | N/A |
| └─IndexLookUp_28 | 0.03 | 39549 | root | | time:211ms, loops:41, index_task: {total_time: 41.3ms, fetch_handle: 41.2ms, build: 6.38µs, wait: 15.3µs}, table_task: {total_time: 1.15s, num: 16, concurrency: 16} | | 6.06 MB | N/A |
| ├─IndexRangeScan_26(Build) | 0.03 | 39549 | cop[tikv] | table:relation_rule_clue_lgbm, index:idx_allocate_condition_id_update_time_score(allocate_condition_id, update_time, score) | time:39.6ms, loops:45, cop_task: {num: 1, max: 39.5ms, proc_keys: 39549, tot_proc: 39ms, rpc_num: 1, rpc_time: 39.5ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:38ms, loops:43}, scan_detail: {total_process_keys: 39549, total_keys: 143641, rocksdb: {delete_skipped_count: 71, key_skipped_count: 143708, block: {cache_hit_count: 101, read_count: 0, read_byte: 0 Bytes}}} | range:(2032241 2021-08-10 00:00:00,2032241 +inf], keep order:false | N/A | N/A |
| └─TableRowIDScan_27(Probe) | 0.03 | 39549 | cop[tikv] | table:relation_rule_clue_lgbm | time:483.6ms, loops:63, cop_task: {num: 16, max: 156.9ms, min: 873.1µs, avg: 29.9ms, p95: 156.9ms, max_proc_keys: 16384, p95_proc_keys: 16384, tot_proc: 429ms, rpc_num: 16, rpc_time: 478.7ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:135ms, min:1ms, p80:25ms, p95:135ms, iters:108, tasks:16}, scan_detail: {total_process_keys: 39549, total_keys: 59631, rocksdb: {delete_skipped_count: 0, key_skipped_count: 28746, block: {cache_hit_count: 213016, read_count: 0, read_byte: 0 Bytes}}} | keep order:false | N/A | N/A |
±-----------------------------------±--------±--------±----------±----------------------------------------------------------------------------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
5 rows in set (0.22 sec)

| relation_rule_clue_lgbm | CREATE TABLE relation_rule_clue_lgbm (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
allocate_condition_id int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘分配条件规则id’,
clue_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘线索主键id’,
crypto_mobile varchar(64) NOT NULL DEFAULT ‘’ COMMENT ‘加密手机号(旧)’,
chance_type_id varchar(64) NOT NULL DEFAULT ‘’ COMMENT ‘线索类型id’,
chance_status int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘线索状态’,
item_id varchar(32) NOT NULL DEFAULT ‘’ COMMENT ‘线索唯一字符串’,
experiment_version int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘实验版本’,
score double(8,8) NOT NULL DEFAULT ‘0’ COMMENT ‘模型预测得分’,
batch_id varchar(64) NOT NULL DEFAULT ‘’ COMMENT ‘线索批次号’,
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
is_delete tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘逻辑删除 0 未删除, 1 已删除’,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
KEY idx_clue_id (clue_id),
KEY idx_create_time (create_time),
KEY idx_update_time (update_time),
KEY idx_allocate_condition_id_update_time_score (allocate_condition_id,update_time,score),
KEY idx_clue_id_update_time (clue_id,update_time),
KEY idx_allocate_condition_id_score_update_time (allocate_condition_id,score,update_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=48474289 COMMENT=‘规则与线索模型表’ |
±------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4赞

补充一下 TiDB 的小版本哈、schema、explain analyze SQL 的结果哈

3赞

补充在上面了

3赞

返回两个结果,看一下数据情况;

-- 总数据量
select count(1) from  relation_rule_clue_lgbm;
-- 取值为 10184163 的数据量
select count(1) from  from relation_rule_clue_lgbm
-> where allocate_condition_id=10184163 and update_time > “2021-08-10 00:00:00” order by score desc limit 1;
-- 取值为 2032241 的数据量
select count(1)  from relation_rule_clue_lgbm
where allocate_condition_id=2032241 and update_time > “2021-08-10 00:00:00” order by score desc limit 1;
3赞

mysql> select count(1) from relation_rule_clue_lgbm;
±---------+
| count(1) |
±---------+
| 41318828 |
±---------+
1 row in set (0.77 sec)

mysql> select count(1) from relation_rule_clue_lgbm where allocate_condition_id=10184163 and update_time > “2021-08-10 00:00:00” order by score desc limit 1;
±---------+
| count(1) |
±---------+
| 96833 |
±---------+
1 row in set (0.09 sec)

mysql> select count(1) from relation_rule_clue_lgbm where allocate_condition_id=2032241 and update_time > “2021-08-10 00:00:00” order by score desc limit 1;
±---------+
| count(1) |
±---------+
| 39217 |
±---------+
1 row in set (0.05 sec)

2赞

Hello~ 麻烦执行一下 ,并返回一下结果。

explain analyze explain analyze select id,allocate_condition_id, clue_id,crypto_mobile, chance_type_id, chance_status,item_id,experiment_version,score,batch_id from relation_rule_clue_lgbm use index  idx_allocate_condition_id_score_update_time
where allocate_condition_id=2032241 and update_time > “2021-08-10 00:00:00” order by score desc limit 1;
2赞

mysql> explain analyze select id,allocate_condition_id, clue_id,crypto_mobile, chance_type_id, chance_status,item_id,experiment_version,score,batch_id from relation_rule_clue_lgbm use index (idx_allocate_condition_id_score_update_time) where allocate_condition_id=2032241 and update_time > “2021-08-10 00:00:00” order by score desc limit 1;

±-----------------------------------±--------±--------±----------±----------------------------------------------------------------------------------------------------------------------------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±-----------------------------------±--------±--------±----------±----------------------------------------------------------------------------------------------------------------------------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| Projection_7 | 0.03 | 1 | root | | time:279.7ms, loops:2, Concurrency:OFF | crm_hub.relation_rule_clue_lgbm.id, crm_hub.relation_rule_clue_lgbm.allocate_condition_id, crm_hub.relation_rule_clue_lgbm.clue_id, crm_hub.relation_rule_clue_lgbm.crypto_mobile, crm_hub.relation_rule_clue_lgbm.chance_type_id, crm_hub.relation_rule_clue_lgbm.chance_status, crm_hub.relation_rule_clue_lgbm.item_id, crm_hub.relation_rule_clue_lgbm.experiment_version, crm_hub.relation_rule_clue_lgbm.score, crm_hub.relation_rule_clue_lgbm.batch_id | 5.00 KB | N/A |
| └─TopN_10 | 0.03 | 1 | root | | time:279.7ms, loops:2 | crm_hub.relation_rule_clue_lgbm.score:desc, offset:0, count:1 | 5.00 KB | N/A |
| └─IndexLookUp_23 | 0.03 | 38442 | root | | time:278.9ms, loops:40, index_task: {total_time: 115.5ms, fetch_handle: 115.4ms, build: 7.03µs, wait: 25.3µs}, table_task: {total_time: 2.29s, num: 16, concurrency: 16} | | 6.14 MB | N/A |
| ├─Selection_22(Build) | 0.03 | 38442 | cop[tikv] | | time:113.7ms, loops:45, cop_task: {num: 1, max: 113.5ms, proc_keys: 134403, tot_proc: 112ms, rpc_num: 1, rpc_time: 113.5ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:112ms, loops:136}, scan_detail: {total_process_keys: 134403, total_keys: 240722, rocksdb: {delete_skipped_count: 78, key_skipped_count: 240797, block: {cache_hit_count: 194, read_count: 3, read_byte: 191.8 KB}}} | gt(crm_hub.relation_rule_clue_lgbm.update_time, 2021-08-10 00:00:00.000000) | N/A | N/A |
| │ └─IndexRangeScan_20 | 3.00 | 134403 | cop[tikv] | table:relation_rule_clue_lgbm, index:idx_allocate_condition_id_score_update_time(allocate_condition_id, score, update_time) | tikv_task:{time:78ms, loops:136}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | range:[2032241,2032241], keep order:false | N/A | N/A |
| └─TableRowIDScan_21(Probe) | 0.03 | 38442 | cop[tikv] | table:relation_rule_clue_lgbm | time:442.1ms, loops:64, cop_task: {num: 16, max: 150.3ms, min: 423.8µs, avg: 27.3ms, p95: 150.3ms, max_proc_keys: 16391, p95_proc_keys: 16391, tot_proc: 388ms, rpc_num: 16, rpc_time: 437.4ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:129ms, min:0s, p80:23ms, p95:129ms, iters:103, tasks:16}, scan_detail: {total_process_keys: 38442, total_keys: 58602, rocksdb: {delete_skipped_count: 0, key_skipped_count: 28776, block: {cache_hit_count: 200911, read_count: 0, read_byte: 0 Bytes}}} | keep order:false | N/A | N/A |
±-----------------------------------±--------±--------±----------±----------------------------------------------------------------------------------------------------------------------------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
6 rows in set (0.29 sec)

mysql>
mysql>

2赞

Hi ~ 看了一下,因为 allocate_condition_id=10184163 和 allocate_condition_id=2032241 的数据查询结果差异比较大, 所以在选择符合索引的时候,会有一定的考虑。 过滤条件全被索引过滤最后排序 vs 有一部分过滤条件走索引并且按索引排序然后过过滤条件取 limit 1;有一部分过滤条件这部分数据多的话,优化器会觉得剩下那部分过滤条件可以更早的拿到要被返回的数据。

KEY idx_allocate_condition_id_update_time_score (allocate_condition_id,update_time,score),
KEY idx_allocate_condition_id_score_update_time (allocate_condition_id,score,update_time)

这两个索引在 score 和 update_time 建议还是选择 score 后 update_time 的复合索引排列方式,因为从 SQL 的谓词选择考虑,update_time 会有范围查询,按照 > 或者 < 会导致后面 列 走到索引上面。可以参考一下文档:

2赞

CREATE TABLE relation_rule_clue_lgbm (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
allocate_condition_id int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘分配条件规则id’,
clue_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘线索主键id’,
crypto_mobile varchar(64) NOT NULL DEFAULT ‘’ COMMENT ‘加密手机号(旧)’,
chance_type_id varchar(64) NOT NULL DEFAULT ‘’ COMMENT ‘线索类型id’,
chance_status int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘线索状态’,
item_id varchar(32) NOT NULL DEFAULT ‘’ COMMENT ‘线索唯一字符串’,
experiment_version int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘实验版本’,
score double(8,8) NOT NULL DEFAULT ‘0’ COMMENT ‘模型预测得分’,
batch_id varchar(64) NOT NULL DEFAULT ‘’ COMMENT ‘线索批次号’,
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
is_delete tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘逻辑删除 0 未删除, 1 已删除’,
PRIMARY KEY ( id ) /*T![clustered_index] CLUSTERED */,
KEY idx_clue_id ( clue_id ),
KEY idx_create_time ( create_time ),
KEY idx_update_time ( update_time ),
KEY idx_allocate_condition_id_update_time_score ( allocate_condition_id , update_time , score ),
KEY idx_clue_id_update_time ( clue_id , update_time ),
KEY idx_allocate_condition_id_score_update_time ( allocate_condition_id , score , update_time )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=48474289 COMMENT=‘规则与线索模型表’

这个表里已经有对应的索引也没用上

2赞

查询时采用指定索引的方式试一下,看看执行计划是否一样?

这是另外一个问题吗?还是还是这个问题 ?如果是上面的问题,建议保留一个复合索引,这样可以避免因为优化器的不稳定,导致的执行计划跑偏的问题。