不知道这个page是否影响数据准确性,比如根据索引 有order by 这类的
测了一下 执行结果如果order by的字段如果不在索引中或者不符合索引 开启paging还不如默认的 在我这里会回表70W
实际这种变量能做成HINT最好,只影响设置hint的SQL
开启paging order by 字段存在索引 且逆序状态下的执行过程
SELECT
`id`,
`src`,
`collect_time`
FROM
`t_collect_record_1659571200`
WHERE
`uid` = "11100000018"
AND `collect_time` <= 1664000000
AND `collect_time` >= 1659579000
AND IF (0 = 10, TRUE, id > 10)
AND IF (0 = 1, TRUE, src = 1) # 这里条件成立则慢查询
ORDER BY collect_time DESC , id DESC
LIMIT
30;
id task estRows operator info actRows execution info memory disk
Projection_7 root 30 db_location.t_collect_record_1659571200.id, db_location.t_collect_record_1659571200.cs 30 time:9.37ms, loops:2, Concurrency:OFF 6.33 KB N/A
└─Limit_12 root 30 offset:0, count:30 30 time:9.35ms, loops:2 N/A N/A
└─IndexLookUp_27 root 30 paging:true 30 time:9.35ms, loops:1, index_task: {total_time: 6.39ms, fetch_handle: 6.12ms, build: 237μs, wait: 30.1μs}, table_task: {total_time: 17.6ms, num: 4, concurrency: 5} 54.9 KB N/A
├─Selection_25 cop[tikv] 30 gt(db_location.t_collect_record_1659571200.id, 10) 800 time:8.32ms, loops:7, cop_task: {num: 3, max: 2.46ms, min: 1.58ms, avg: 2.01ms, p95: 2.46ms, max_proc_keys: 480, p95_proc_keys: 480, tot_proc: 4ms, rpc_num: 3, rpc_time: 5.94ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:4ms, min:0s, p80:4ms, p95:4ms, iters:9, tasks:3}, scan_detail: {total_process_keys: 800, total_process_keys_size: 65600, total_keys: 803, rocksdb: {delete_skipped_count: 0, key_skipped_count: 803, block: {cache_hit_count: 12, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
│ └─IndexRangeScan_23 cop[tikv] 30 table:t_collect_record_1659571200, index:uid_ctime_id(uid, collect_time, id), range:["11100000018" 1659579000,"11100000018" 1660900000], keep order:true, desc 800 tikv_task:{proc max:4ms, min:0s, p80:4ms, p95:4ms, iters:9, tasks:3} N/A N/A
└─Selection_26 cop[tikv] 30 eq(db_location.t_collect_record_1659571200.src, 1) 164 time:14.1ms, loops:7, cop_task: {num: 5, max: 4.51ms, min: 990.7μs, avg: 2.85ms, p95: 4.51ms, max_proc_keys: 68, p95_proc_keys: 68, rpc_num: 5, rpc_time: 14.1ms, copr_cache_hit_ratio: 0.20}, tikv_task:{proc max:28ms, min:0s, p80:28ms, p95:28ms, iters:7, tasks:5}, scan_detail: {total_process_keys: 136, total_process_keys_size: 19720, total_keys: 138, rocksdb: {delete_skipped_count: 0, key_skipped_count: 4, block: {cache_hit_count: 257, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─TableRowIDScan_24 cop[tikv] 30 table:t_collect_record_1659571200, keep order:false 164 tikv_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:7, tasks:5} N/A N/A
嗯 ,actrows少了,多了个 paging:true 信息
确实 所以目前要么这种分页查询在代码中单独使用db 连接 要么确认代码中所有语句都能因paging受益
我看这个变量描述时不超960时有效,方便试试limit 961或更高时 能生效不
不行试了一下 limit961 limit 大于960 强制回复成默认的样子了 即扫描索引70w行 汇总在查询
该主题在最后一个回复创建后60天后自动关闭。不再允许新的回复。