【概述】 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)