如何优化这个慢查询

大飞飞呀 · 2022 年12 月 4 日 02:56

【 TiDB 使用环境】生产环境
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
SELECT I_ID from 表名 FORCE index (IDX_SYNC_STATUS)
WHERE I_SYNC_STATUS = 0 LIMIT 1000;
【遇到的问题：问题现象及影响】
I_SYNC_STATUS = 0 的数据在表中不多，总共1000多条
上面表名数据量24亿，但是强制使用了索引,为什么依然查询很慢呢

explain analyze sql

CREATE TABLE st_summary_bill_sync_confirm (
I_ID bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘自增ID’,
I_SUMMARY_ID bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
I_SUMMARY_TYPE int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
I_SYNC_STATUS tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘同步状态，0为准备同步，1为正在同步，2为同步成功，3为同步失败，4为未处理的类型’,
I_SYNC_STATUS_DETAIL int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘状态信息详细说明’,
I_RETRY_COUNT int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘重试次数’,
CH_COMMENT varchar(200) NOT NULL DEFAULT ‘’ COMMENT ‘同步涉及注释’,
D_CREATED_AT datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
D_UPDATED_AT datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘’,
PRIMARY KEY (I_ID) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY UNIQ_SUMMARY_ID (I_SUMMARY_ID),
KEY IDX_SYNC_STATUS (I_SYNC_STATUS)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2588993678 COMMENT=‘’
【资源配置】
【附件：截图/日志/监控】

xfworld · 2022 年12 月 4 日 03:35

有多慢？

大飞飞呀 · 2022 年12 月 4 日 03:37

100秒

xfworld · 2022 年12 月 4 日 03:51

第二个图的信息，能详细的给出来么？

IDX_SYNC_STATUS 是什么样的索引？我怀疑是热点索引，所以慢就正常了

大飞飞呀 · 2022 年12 月 4 日 04:09

不是热点索引，详细的数据

Limit_9, 1000.00, 96, root, , time:1.82s, loops:2, offset:0, count:1000, N/A, N/A
└─IndexLookUp_14, 1000.00, 96, root, , time:1.82s, loops:2, index_task: {total_time: 1.44s, fetch_handle: 1.44s, build: 1.68µs, wait: 7.03µs}, table_task: {total_time: 11.5s, num: 1, concurrency: 8}, , 11.1 KB, N/A
├─Limit_13(Build), 1000.00, 96, cop[tikv], , time:1.44s, loops:3, cop_task: {num: 1478, max: 252ms, min: 189.3µs, avg: 954µs, p95: 439.3µs, max_proc_keys: 96, p95_proc_keys: 0, tot_proc: 919ms, tot_wait: 50ms, rpc_num: 1478, rpc_time: 1.4s, copr_cache_hit_ratio: 0.99}, tikv_task:{proc max:354ms, min:39ms, p80:138ms, p95:189ms, iters:1480, tasks:1478}, scan_detail: {total_process_keys: 96, total_keys: 3811337, rocksdb: {delete_skipped_count: 1823, key_skipped_count: 4558158, block: {cache_hit_count: 2483, read_count: 2, read_byte: 16.6 KB}}}, offset:0, count:1000, N/A, N/A
│ └─IndexRangeScan_11, 1000.00, 96, cop[tikv], table:st_summary_bill_sync_confirm, index:IDX_SYNC_STATUS(I_SYNC_STATUS), tikv_task:{proc max:354ms, min:39ms, p80:138ms, p95:189ms, iters:1480, tasks:1478}, 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:[0,0], keep order:false, N/A, N/A
└─TableRowIDScan_12(Probe), 1000.00, 96, cop[tikv], table:st_summary_bill_sync_confirm, time:794.2µs, loops:2, cop_task: {num: 1, max: 726.5µs, proc_keys: 96, rpc_num: 1, rpc_time: 706.3µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:3}, scan_detail: {total_process_keys: 96, total_keys: 289, rocksdb: {delete_skipped_count: 96, key_skipped_count: 864, block: {cache_hit_count: 10, read_count: 0, read_byte: 0 Bytes}}}, keep order:false, stats:pseudo, N/A, N/A

forever · 2022 年12 月 4 日 04:33

表结构是什么也发下，是聚簇表还是非聚簇表呢， I_ID是主键嘛

tidb狂热爱好者 · 2022 年12 月 4 日 04:43

这正常的区分度太低

xfworld · 2022 年12 月 4 日 06:43

可以用 explain analyze SQL 收集下实际的执行情况，

然后根据描述，相关表统计信息不太准确，你先执行下 analyze table ，更新下统计信息

在重新收集下 explain analyze SQL 的执行情况，比较下看看

大飞飞呀 · 2022 年12 月 4 日 09:08

Kongdom · 2022 年12 月 4 日 10:15

看到有stats:pseudo，建议先analyze一下，收集一下统计信息

大飞飞呀 · 2022 年12 月 4 日 10:19

这张表更新比较频繁，如果analyze table之后，过一会可能又失效了，
另外我已经use index了，就一定会走索引吧

Kongdom · 2022 年12 月 4 日 10:30

去掉limit，速度怎么样？

大飞飞呀 · 2022 年12 月 4 日 10:38

staus=0的一共1000条

大飞飞呀 · 2022 年12 月 4 日 10:42

有的时候同样的sql查询很快，详细信息
Projection_4, 5510288.92, 0, root, , time:506.7ms, loops:1, Concurrency:8, stat.st_summary_bill_sync_confirm.i_summary_id, 8.72 KB, N/A
└─IndexLookUp_7, 5510288.92, 0, root, , time:506.6ms, loops:1, table_task: {total_time: 4.02s, num: 0, concurrency: 8}, , 1.14 KB, N/A
├─IndexRangeScan_5(Build), 5510288.92, 0, cop[tikv], table:st_summary_bill_sync_confirm, index:IDX_SYNC_STATUS(I_SYNC_STATUS), time:502.9ms, loops:1, cop_task: {num: 1478, max: 466.1ms, min: 172.3µs, avg: 648.4µs, p95: 400.6µs, tot_proc: 513ms, tot_wait: 56ms, rpc_num: 1478, rpc_time: 947.1ms, copr_cache_hit_ratio: 1.00}, tikv_task:{proc max:465ms, min:44ms, p80:139ms, p95:189ms, iters:1478, tasks:1478}, scan_detail: {total_process_keys: 0, total_keys: 1567009, rocksdb: {delete_skipped_count: 251214, key_skipped_count: 2886335, block: {cache_hit_count: 1640, read_count: 0, read_byte: 0 Bytes}}}, range:[0,0], keep order:false, N/A, N/A
└─TableRowIDScan_6(Probe), 5510288.92, 0, cop[tikv], table:st_summary_bill_sync_confirm, , keep order:false, N/A, N/A

tidb狂热爱好者 · 2022 年12 月 4 日 12:43

你的历史key这么多应该是gc的问题改gc时间为10分钟

大飞飞呀 · 2022 年12 月 4 日 13:06

怎么修改，有具体的命令吗，请教一下

Kongdom · 2022 年12 月 4 日 14:34

时快时慢的话，就有可能是热点问题了

Kongdom · 2022 年12 月 4 日 14:37

GC是全局系统变量，通过set 方法赋值
https://docs.pingcap.com/zh/tidb/v5.0/system-variables#tidb_gc_life_time-从-v50-版本开始引入

大飞飞呀 · 2022 年12 月 5 日 04:38

正常一条select 会去查询历史版本数据吗？这不科学

大飞飞呀 · 2022 年12 月 5 日 04:40

快：scan_detail: {total_process_keys: 78, total_keys: 1234511
慢：scan_detail: {total_process_keys: 1111, total_keys: 136036179
我发现个规律，每次慢的时候total_keys特别大