order by limit 分页得到的结果有异常

LI-ldc · 2021 年11 月 1 日 07:42

tidb版本：v5.1.1
问题：SQL查询，分页的结果是一致的limit 10,10 limit 5,10等结果是一样的。order by 如果使用desc，则结果是正确的，使用asc则结果异常
SELECT
ds.id,
ds.full_name,
province.area_name AS province,
pro.name AS pro_name,
ds.first_order_time,
dse.last_order_time,
ds.ly_note,
dse.firstorder_pro_id,
dse.file_complete_time,
aa.real_name
FROM
db_cxx_dictionary.ts_drugstore_branch ds
LEFT JOIN db_cxx_dictionary.ts_drugstore_branch_ext dse ON ds.id = dse.drugstore_branch_id
LEFT JOIN db_cxx_admin.ts_admin_account aa ON aa.uid = dse.file_op_id
LEFT JOIN db_cxx_dictionary.ts_areas area ON ds.area_id = area.id
LEFT JOIN db_cxx_dictionary.ts_areas province ON province.area_code = LEFT (area.area_code, 2)
LEFT JOIN db_cxx_dictionary.ts_provider pro ON pro.id = dse.firstorder_pro_id
WHERE
(ds.is_active = 1)
AND (dse.file_status = 0)
AND (dse.has_file = 1)
ORDER BY
dse.last_order_time ASC
LIMIT 10,
10

LI-ldc · 2021 年11 月 1 日 07:45

Projection_25	10.00	10	root		time:24.4ms, loops:2, Concurrency:OFF	db_cxx_dictionary.ts_drugstore_branch.id, db_cxx_dictionary.ts_drugstore_branch.full_name, db_cxx_dictionary.ts_areas.area_name, db_cxx_dictionary.ts_provider.name, db_cxx_dictionary.ts_drugstore_branch.first_order_time, db_cxx_dictionary.ts_drugstore_branch_ext.last_order_time, db_cxx_dictionary.ts_drugstore_branch.ly_note, db_cxx_dictionary.ts_drugstore_branch_ext.firstorder_pro_id, db_cxx_dictionary.ts_drugstore_branch_ext.file_complete_time, db_cxx_admin.ts_admin_account.real_name	2.60 KB	N/A
└─Limit_32	10.00	10	root		time:24.4ms, loops:2	offset:9, count:10	N/A	N/A
└─IndexJoin_209	19.00	19	root		time:24.4ms, loops:1, inner:{total:851.7µs, concurrency:5, task:1, construct:19.4µs, fetch:831.3µs, build:66ns}, probe:9.91µs	left outer join, inner:TableReader_206, outer key:db_cxx_dictionary.ts_drugstore_branch_ext.firstorder_pro_id, inner key:db_cxx_dictionary.ts_provider.id, equal cond:eq(db_cxx_dictionary.ts_drugstore_branch_ext.firstorder_pro_id, db_cxx_dictionary.ts_provider.id)	106.7 KB	N/A
├─TopN_215(Build)	19.00	19	root		time:23.6ms, loops:3	db_cxx_dictionary.ts_drugstore_branch_ext.last_order_time, offset:0, count:19	5.17 KB	N/A
│ └─HashJoin_52	19.00	19	root		time:23.6ms, loops:2, build_hash_table:{total:9.86ms, fetch:9.86ms, build:5.74µs}, probe:{concurrency:5, total:117.6ms, max:23.5ms, probe:2.84ms, fetch:114.7ms}	left outer join, equal:[eq(Column#295, Column#296)]	12.7 KB	0 Bytes
│ ├─Projection_53(Build)	19.00	19	root		time:9.76ms, loops:2, Concurrency:OFF	db_cxx_dictionary.ts_drugstore_branch.id, db_cxx_dictionary.ts_drugstore_branch.full_name, db_cxx_dictionary.ts_drugstore_branch.ly_note, db_cxx_dictionary.ts_drugstore_branch.first_order_time, db_cxx_dictionary.ts_drugstore_branch_ext.last_order_time, db_cxx_dictionary.ts_drugstore_branch_ext.file_complete_time, db_cxx_dictionary.ts_drugstore_branch_ext.firstorder_pro_id, db_cxx_admin.ts_admin_account.real_name, cast(left(cast(db_cxx_dictionary.ts_areas.area_code, var_string(20)), 2), double BINARY)->Column#295	4.77 KB	N/A
│ │ └─Limit_60	19.00	19	root		time:9.71ms, loops:2	offset:0, count:19	N/A	N/A
│ │ └─IndexJoin_185	19.00	19	root		time:9.71ms, loops:1, inner:{total:1.07ms, concurrency:5, task:1, construct:17.3µs, fetch:1.05ms, build:6.14µs}, probe:19.8µs	left outer join, inner:TableReader_182, outer key:db_cxx_dictionary.ts_drugstore_branch.area_id, inner key:db_cxx_dictionary.ts_areas.id, equal cond:eq(db_cxx_dictionary.ts_drugstore_branch.area_id, db_cxx_dictionary.ts_areas.id)	21.8 KB	N/A
│ │ ├─Limit_195(Build)	19.00	19	root		time:8.74ms, loops:3	offset:0, count:19	N/A	N/A
│ │ │ └─IndexJoin_164	19.00	19	root		time:8.74ms, loops:1, inner:{total:855.7µs, concurrency:5, task:1, construct:18.2µs, fetch:835.2µs, build:1.72µs}, probe:10.2µs	left outer join, inner:TableReader_161, outer key:db_cxx_dictionary.ts_drugstore_branch_ext.file_op_id, inner key:db_cxx_admin.ts_admin_account.uid, equal cond:eq(db_cxx_dictionary.ts_drugstore_branch_ext.file_op_id, db_cxx_admin.ts_admin_account.uid)	29.6 KB	N/A
│ │ │ ├─Limit_174(Build)	19.00	19	root		time:7.87ms, loops:3	offset:0, count:19	N/A	N/A
│ │ │ │ └─Projection_142	19.00	19	root		time:7.87ms, loops:1, Concurrency:OFF	db_cxx_dictionary.ts_drugstore_branch.id, db_cxx_dictionary.ts_drugstore_branch.area_id, db_cxx_dictionary.ts_drugstore_branch.full_name, db_cxx_dictionary.ts_drugstore_branch.ly_note, db_cxx_dictionary.ts_drugstore_branch.first_order_time, db_cxx_dictionary.ts_drugstore_branch_ext.last_order_time, db_cxx_dictionary.ts_drugstore_branch_ext.file_op_id, db_cxx_dictionary.ts_drugstore_branch_ext.file_complete_time, db_cxx_dictionary.ts_drugstore_branch_ext.firstorder_pro_id	4.52 KB	N/A
│ │ │ │ └─IndexJoin_150	19.00	19	root		time:7.86ms, loops:1, inner:{total:4.64ms, concurrency:5, task:2, construct:105.9µs, fetch:4.49ms, build:48.1µs}, probe:14.1µs	inner join, inner:TableReader_146, outer key:db_cxx_dictionary.ts_drugstore_branch_ext.drugstore_branch_id, inner key:db_cxx_dictionary.ts_drugstore_branch.id, equal cond:eq(db_cxx_dictionary.ts_drugstore_branch_ext.drugstore_branch_id, db_cxx_dictionary.ts_drugstore_branch.id)	307.7 KB	N/A
│ │ │ │ ├─IndexLookUp_157(Build)	19.00	253	root		time:34.3ms, loops:4, index_task: {total_time: 29.5ms, fetch_handle: 9.29ms, build: 19.6ms, wait: 622.7µs}, table_task: {total_time: 165.9ms, num: 15, concurrency: 5}		5.10 MB	N/A
│ │ │ │ │ ├─IndexFullScan_154(Build)	391.16	394868	cop[tikv]	table:dse, index:last_order_dx(last_order_time)	time:3.05ms, loops:176, cop_task: {num: 1, max: 2.21ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.21ms, copr_cache_hit_ratio: 1.00}, tikv_task:{time:111ms, loops:390}	keep order:true	N/A	N/A
│ │ │ │ │ └─Selection_156(Probe)	19.00	253	cop[tikv]		time:72.2ms, loops:23, cop_task: {num: 8, max: 15.5ms, min: 1.68ms, avg: 6.16ms, p95: 15.5ms, max_proc_keys: 8192, p95_proc_keys: 8192, tot_proc: 36ms, rpc_num: 8, rpc_time: 49.3ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:12ms, min:1ms, p80:11ms, p95:12ms, iters:50, tasks:8}, scan_detail: {total_process_keys: 16352, total_keys: 18133, rocksdb: {delete_skipped_count: 0, key_skipped_count: 15732, block: {cache_hit_count: 17808, read_count: 2, read_byte: 18.1 KB}}}	eq(db_cxx_dictionary.ts_drugstore_branch_ext.file_status, 0), eq(db_cxx_dictionary.ts_drugstore_branch_ext.has_file, 1)	N/A	N/A
│ │ │ │ │ └─TableRowIDScan_155	391.16	16352	cop[tikv]	table:dse	tikv_task:{proc max:12ms, min:1ms, p80:11ms, p95:12ms, iters:50, tasks:8}	keep order:false	N/A	N/A
│ │ │ │ └─TableReader_146(Probe)	0.86	176	root		time:4.29ms, loops:4, cop_task: {num: 2, max: 2.48ms, min: 1.69ms, avg: 2.09ms, p95: 2.48ms, max_proc_keys: 128, p95_proc_keys: 128, tot_proc: 2ms, rpc_num: 2, rpc_time: 4.15ms, copr_cache_hit_ratio: 0.00}	data:Selection_145	N/A	N/A
│ │ │ │ └─Selection_145	0.86	176	cop[tikv]		tikv_task:{proc max:2ms, min:0s, p80:2ms, p95:2ms, iters:5, tasks:2}, scan_detail: {total_process_keys: 192, total_keys: 212, rocksdb: {delete_skipped_count: 0, key_skipped_count: 75, block: {cache_hit_count: 1427, read_count: 1, read_byte: 63.8 KB}}}	eq(db_cxx_dictionary.ts_drugstore_branch.is_active, 1)	N/A	N/A
│ │ │ │ └─TableRangeScan_144	1.00	192	cop[tikv]	table:ds	tikv_task:{proc max:2ms, min:0s, p80:2ms, p95:2ms, iters:5, tasks:2}	range: decided by [db_cxx_dictionary.ts_drugstore_branch_ext.drugstore_branch_id], keep order:false	N/A	N/A
│ │ │ └─TableReader_161(Probe)	1.00	3	root		time:793.4µs, loops:2, cop_task: {num: 1, max: 763.5µs, proc_keys: 3, rpc_num: 1, rpc_time: 756.6µs, copr_cache_hit_ratio: 0.00}	data:TableRangeScan_160	N/A	N/A
│ │ │ └─TableRangeScan_160	1.00	3	cop[tikv]	table:aa	tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 3, total_keys: 11, rocksdb: {delete_skipped_count: 1, key_skipped_count: 16, block: {cache_hit_count: 26, read_count: 0, read_byte: 0 Bytes}}}	range: decided by [db_cxx_dictionary.ts_drugstore_branch_ext.file_op_id], keep order:false	N/A	N/A
│ │ └─TableReader_182(Probe)	1.00	13	root		time:978.3µs, loops:2, cop_task: {num: 1, max: 950.6µs, proc_keys: 13, rpc_num: 1, rpc_time: 944.5µs, copr_cache_hit_ratio: 0.00}	data:TableRangeScan_181	N/A	N/A
│ │ └─TableRangeScan_181	1.00	13	cop[tikv]	table:area	tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 13, total_keys: 14, rocksdb: {delete_skipped_count: 0, key_skipped_count: 4, block: {cache_hit_count: 55, read_count: 0, read_byte: 0 Bytes}}}	range: decided by [db_cxx_dictionary.ts_drugstore_branch.area_id], keep order:false	N/A	N/A
│ └─Projection_196(Probe)	49965.00	49965	root		time:23.2ms, loops:50, Concurrency:5	db_cxx_dictionary.ts_areas.area_name, cast(db_cxx_dictionary.ts_areas.area_code, double UNSIGNED BINARY)->Column#296	284.3 KB	N/A
│ └─TableReader_200	49965.00	49965	root		time:21.9ms, loops:50, cop_task: {num: 1, max: 21.9ms, proc_keys: 0, rpc_num: 1, rpc_time: 21.9ms, copr_cache_hit_ratio: 0.00}	data:TableRangeScan_199	1.29 MB	N/A
│ └─TableRangeScan_199	49965.00	49965	cop[tiflash]	table:province	tiflash_task:{time:5.58ms, loops:1, threads:1}	range:[0,+inf], keep order:false	N/A	N/A
└─TableReader_206(Probe)	1.00	0	root		time:771.2µs, loops:1, cop_task: {num: 1, max: 739.8µs, proc_keys: 0, rpc_num: 1, rpc_time: 731.1µs, copr_cache_hit_ratio: 0.00}	data:TableRangeScan_205	N/A	N/A
└─TableRangeScan_205	1.00	0	cop[tikv]	table:pro	tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 0, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 6, read_count: 0, read_byte: 0 Bytes}}}	range: decided by [db_cxx_dictionary.ts_drugstore_branch_ext.firstorder_pro_id], keep order:false	N/A	N/A

这道题我不会 · 2021 年11 月 1 日 10:45

请问你这里所说的结果异常是指什么？查询报错还是结果集错误？方便的话麻烦提供下具体的查询结果示例。

LI-ldc · 2021 年11 月 2 日 01:33

就比如：ORDER BY dse.last_order_time ASC下，limit 1,10 和 limit 2,10的结果正常的情况应该是不一致的，但是实际上是得到同样的结果

这道题我不会 · 2021 年11 月 2 日 02:03

字段 dse.last_order_time 存不存在相同的值？你直接 limit 12 看下查询结果呢

LI-ldc · 2021 年11 月 2 日 03:07

字段 dse.last_order_time 存在相同的值，limit12得到是不同的结果

这道题我不会 · 2021 年11 月 2 日 03:45

如果方便的话直接贴下查询的结果吧，另外校验下表 dse 的数据和索引，命令 ADMIN CHECK INDEX tbl_name idx_name;

LI-ldc · 2021 年11 月 2 日 04:06

LI-ldc · 2021 年11 月 2 日 04:07

已校验，数据和索引没问题

这道题我不会 · 2021 年11 月 2 日 05:36

字段 last_order_time 值肯定是顺序递增的，但由于该字段上不是唯一索引且存在相同的值，那么在相同的last_order_time 值下，其他字段值在返回时是无法保证顺序的，可以参考说明：https://docs.pingcap.com/zh/tidb/stable/sql-faq#省略-order-by-条件时-tidb-中返回结果的顺序与-mysql-中的不一致

xfworld · 2021 年11 月 2 日 08:25

这个解答不错，又掌握一个知识点

单线程和单节点迭代出来的值 VS 多线程和多节点迭代出来的值（顺序就不一定能有保证）

逍遥_猫 · 2022 年3 月 31 日 03:20

大神高解~

Hacker_xUwtuKxa · 2022 年4 月 14 日 01:40

还是因为分布式与集中式的差异导致。

哈喽沃德 · 2022 年4 月 14 日 11:27

多包一层sql试试

system · 2022 年10 月 31 日 19:16

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。