sql语句条件查询 效率低

不知道这个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天后自动关闭。不再允许新的回复。