KingbaseES 数据库使用Limit子句查询结果返回顺序不一致
一、KingbaseES数据库limit查询子句：
在KingbaseES数据库使用LIMIT子句限制查询结果的行数，从而实现分段显示数据的功能。
使用LIMIT子句在KingbaseES数据库中进行查询结果行数限制：
语法格式为：
SELECT * FROM table_name LIMIT row_count OFFSET offset;
--row_count表示每页显示的行数，offset表示从第几行开始显示。
--例如，如果要查询第二页的数据，每页显示10行，可以写为
SELECT * FROM table_name LIMIT 10 OFFSET 10;
二、使用limit子句限制查询结果的行数：
1：准备测试环境：
CREATE TABLE public.t1 (
order_no integer not null,
workid character varying(50 char) not null,
id character varying(50 char),
specificworkarrangement character varying(2000 char),
last_feedback_time character varying(50 char) not null
);
INSERT INTO public.t1 VALUES (2, 'bba0e34a98c842a5b5cd0f5b05b5f589', '1646713438360268802', '李强测试安排-02', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (4, 'f530d705b0ff4f278d9c8ca3085f55ca', '1646714576224612354', '高晶测试安排-04', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (1, 'fd9317e8597d4757ac9d7cee57283ed5', '1646713389349826561', '李强测试安排-01', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (1, 'fd9317e8597d4757ac9d7cee57283ed5', '1646713785514422273', '田辰测试安排-01', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (4, 'f530d705b0ff4f278d9c8ca3085f55ca', '1646713217895067650', '董辉测试安排-04', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (4, 'f530d705b0ff4f278d9c8ca3085f55ca', '1646713932243759106', '田辰测试安排-04', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (2, 'bba0e34a98c842a5b5cd0f5b05b5f589', '1646714421022781441', '高晶测试安排-02', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (2, 'bba0e34a98c842a5b5cd0f5b05b5f589', '1646713121581264897', '董辉测试安排-02', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (3, '54819e7ba7d7424d9df5ed851c9e69de', '1646714834161725442', '赵琦测试安排-03', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (1, 'fd9317e8597d4757ac9d7cee57283ed5', '1646716902939258881', '杨云测试安排-01', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (4, 'f530d705b0ff4f278d9c8ca3085f55ca', '1646713581792882690', '李强测试安排-04', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (2, 'bba0e34a98c842a5b5cd0f5b05b5f589', '1646713829130989570', '田辰测试安排-02', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (3, '54819e7ba7d7424d9df5ed851c9e69de', '1646713878858657794', '田辰测试安排-03', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (2, 'bba0e34a98c842a5b5cd0f5b05b5f589', '1646716953950384129', '杨云测试安排-02', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (3, '54819e7ba7d7424d9df5ed851c9e69de', '1646717012012134401', '杨云测试安排-03', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (4, 'f530d705b0ff4f278d9c8ca3085f55ca', '1646717059625873409', '杨云测试安排-04', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (1, 'fd9317e8597d4757ac9d7cee57283ed5', '1646714734073049089', '赵琦测试安排-01', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (3, '54819e7ba7d7424d9df5ed851c9e69de', '1646713535290634242', '李强测试安排-03', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (1, 'fd9317e8597d4757ac9d7cee57283ed5', '1646712945626017793', '董辉测试安排-01', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (1, 'fd9317e8597d4757ac9d7cee57283ed5', '1646714368967274497', '高晶测试安排-01', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (3, '54819e7ba7d7424d9df5ed851c9e69de', '1646713175176081409', '董辉测试安排-03', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (2, 'bba0e34a98c842a5b5cd0f5b05b5f589', '1646714788343148545', '赵琦测试安排-02', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (3, '54819e7ba7d7424d9df5ed851c9e69de', '1646714510139158529', '高晶测试安排-03', '2023-04-17 16:42:06');
INSERT INTO public.t1 VALUES (4, 'f530d705b0ff4f278d9c8ca3085f55ca', '1646714882677239809', '赵琦测试安排-04', '2023-04-17 16:42:06');
2：使用order by和limit子句限制查询结果的行数：
排序列无索引：
--查询10条数据
test=# select ctid,* from t1 order by order_no limit 10;
CTID | ORDER_NO | WORKID | ID | SPECIFICWORKARRANGEMENT | LAST_FEEDBACK_TIME
--------+----------+----------------------------------+---------------------+-------------------------+---------------------
(0,19) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646712945626017793 | 董辉测试安排-01 | 2023-04-17 16:42:06
(0,4) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713785514422273 | 田辰测试安排-01 | 2023-04-17 16:42:06
(0,20) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714368967274497 | 高晶测试安排-01 | 2023-04-17 16:42:06
(0,10) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646716902939258881 | 杨云测试安排-01 | 2023-04-17 16:42:06
(0,3) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713389349826561 | 李强测试安排-01 | 2023-04-17 16:42:06
(0,17) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714734073049089 | 赵琦测试安排-01 | 2023-04-17 16:42:06
(0,7) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714421022781441 | 高晶测试安排-02 | 2023-04-17 16:42:06
(0,1) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713438360268802 | 李强测试安排-02 | 2023-04-17 16:42:06
(0,8) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713121581264897 | 董辉测试安排-02 | 2023-04-17 16:42:06
(0,12) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713829130989570 | 田辰测试安排-02 | 2023-04-17 16:42:06
(10 rows)
--执行计划
test=# explain analyze select ctid,* from t1 order by order_no limit 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=12.84..12.87 rows=10 width=880) (actual time=0.037..0.040 rows=10 loops=1)
-> Sort (cost=12.84..13.07 rows=90 width=880) (actual time=0.036..0.037 rows=10 loops=1)
Sort Key: order_no
Sort Method: top-N heapsort Memory: 27kB
-> Seq Scan on t1 (cost=0.00..10.90 rows=90 width=880) (actual time=0.012..0.020 rows=24 loops=1)
Planning Time: 0.077 ms
Execution Time: 0.060 ms
(7 rows)
--查询13条数据
test=# select ctid,* from t1 order by order_no limit 13;
CTID | ORDER_NO | WORKID | ID | SPECIFICWORKARRANGEMENT | LAST_FEEDBACK_TIME
--------+----------+----------------------------------+---------------------+-------------------------+---------------------
(0,3) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713389349826561 | 李强测试安排-01 | 2023-04-17 16:42:06
(0,4) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713785514422273 | 田辰测试安排-01 | 2023-04-17 16:42:06
(0,20) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714368967274497 | 高晶测试安排-01 | 2023-04-17 16:42:06
(0,10) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646716902939258881 | 杨云测试安排-01 | 2023-04-17 16:42:06
(0,17) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714734073049089 | 赵琦测试安排-01 | 2023-04-17 16:42:06
(0,19) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646712945626017793 | 董辉测试安排-01 | 2023-04-17 16:42:06
(0,12) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713829130989570 | 田辰测试安排-02 | 2023-04-17 16:42:06
(0,14) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646716953950384129 | 杨云测试安排-02 | 2023-04-17 16:42:06
(0,7) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714421022781441 | 高晶测试安排-02 | 2023-04-17 16:42:06
(0,8) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713121581264897 | 董辉测试安排-02 | 2023-04-17 16:42:06
(0,22) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714788343148545 | 赵琦测试安排-02 | 2023-04-17 16:42:06
(0,1) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713438360268802 | 李强测试安排-02 | 2023-04-17 16:42:06
(0,13) | 3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646713878858657794 | 田辰测试安排-03 | 2023-04-17 16:42:06
(13 rows)
--执行计划
test=# explain analyze select ctid,* from t1 order by order_no limit 13;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=13.02..13.05 rows=13 width=880) (actual time=0.035..0.039 rows=13 loops=1)
-> Sort (cost=13.02..13.24 rows=90 width=880) (actual time=0.034..0.036 rows=13 loops=1)
Sort Key: order_no
Sort Method: quicksort Memory: 28kB
-> Seq Scan on t1 (cost=0.00..10.90 rows=90 width=880) (actual time=0.012..0.020 rows=24 loops=1)
Planning Time: 0.083 ms
Execution Time: 0.060 ms
(7 rows)
--查询15条数据
test=# select ctid,* from t1 order by order_no limit 15;
CTID | ORDER_NO | WORKID | ID | SPECIFICWORKARRANGEMENT | LAST_FEEDBACK_TIME
--------+----------+----------------------------------+---------------------+-------------------------+---------------------
(0,3) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713389349826561 | 李强测试安排-01 | 2023-04-17 16:42:06
(0,4) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713785514422273 | 田辰测试安排-01 | 2023-04-17 16:42:06
(0,20) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714368967274497 | 高晶测试安排-01 | 2023-04-17 16:42:06
(0,10) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646716902939258881 | 杨云测试安排-01 | 2023-04-17 16:42:06
(0,17) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714734073049089 | 赵琦测试安排-01 | 2023-04-17 16:42:06
(0,19) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646712945626017793 | 董辉测试安排-01 | 2023-04-17 16:42:06
(0,12) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713829130989570 | 田辰测试安排-02 | 2023-04-17 16:42:06
(0,14) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646716953950384129 | 杨云测试安排-02 | 2023-04-17 16:42:06
(0,7) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714421022781441 | 高晶测试安排-02 | 2023-04-17 16:42:06
(0,8) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713121581264897 | 董辉测试安排-02 | 2023-04-17 16:42:06
(0,22) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714788343148545 | 赵琦测试安排-02 | 2023-04-17 16:42:06
(0,1) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713438360268802 | 李强测试安排-02 | 2023-04-17 16:42:06
(0,13) | 3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646713878858657794 | 田辰测试安排-03 | 2023-04-17 16:42:06
(0,9) | 3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646714834161725442 | 赵琦测试安排-03 | 2023-04-17 16:42:06
(0,15) | 3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646717012012134401 | 杨云测试安排-03 | 2023-04-17 16:42:06
(15 rows)
--执行计划
test=# explain analyze select ctid,* from t1 order by order_no limit 15;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=13.11..13.15 rows=15 width=880) (actual time=0.046..0.050 rows=15 loops=1)
-> Sort (cost=13.11..13.33 rows=90 width=880) (actual time=0.046..0.047 rows=15 loops=1)
Sort Key: order_no
Sort Method: quicksort Memory: 28kB
-> Seq Scan on t1 (cost=0.00..10.90 rows=90 width=880) (actual time=0.011..0.020 rows=24 loops=1)
Planning Time: 0.077 ms
Execution Time: 0.073 ms
(7 rows)
通过以上结果可以看出：
在排序列无索引的场景下limit 10跟limit 13、limit 15结果返回的顺序是不同的。这是由于使用limit进行查询分页的时候，limit <= 10的时候数据库使用Top-N heapsort算法，limit > 10 的时候数据库使用memory sort或者disk sort进行排序，这种差异会导致在排序列无索引的场景下使用Top-N heapsort算法的跟使用memory sort或者disk sort返回顺序有差异（结果都是正确的）。
三、如何避免limit限制结果返回行数顺序不一致：
1：排序列无索引场景增加一个排序列，并且该列是唯一的
--查询10条数据
test=# select ctid,* from t1 order by order_no,id limit 10;
CTID | ORDER_NO | WORKID | ID | SPECIFICWORKARRANGEMENT | LAST_FEEDBACK_TIME
--------+----------+----------------------------------+---------------------+-------------------------+---------------------
(0,19) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646712945626017793 | 董辉测试安排-01 | 2023-04-17 16:42:06
(0,3) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713389349826561 | 李强测试安排-01 | 2023-04-17 16:42:06
(0,4) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713785514422273 | 田辰测试安排-01 | 2023-04-17 16:42:06
(0,20) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714368967274497 | 高晶测试安排-01 | 2023-04-17 16:42:06
(0,17) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714734073049089 | 赵琦测试安排-01 | 2023-04-17 16:42:06
(0,10) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646716902939258881 | 杨云测试安排-01 | 2023-04-17 16:42:06
(0,8) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713121581264897 | 董辉测试安排-02 | 2023-04-17 16:42:06
(0,1) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713438360268802 | 李强测试安排-02 | 2023-04-17 16:42:06
(0,12) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713829130989570 | 田辰测试安排-02 | 2023-04-17 16:42:06
(0,7) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714421022781441 | 高晶测试安排-02 | 2023-04-17 16:42:06
(10 rows)
--执行计划
test=# explain analyze select ctid,* from t1 order by order_no,id limit 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=12.84..12.87 rows=10 width=880) (actual time=0.059..0.062 rows=10 loops=1)
-> Sort (cost=12.84..13.07 rows=90 width=880) (actual time=0.058..0.059 rows=10 loops=1)
Sort Key: order_no, id
Sort Method: top-N heapsort Memory: 27kB
-> Seq Scan on t1 (cost=0.00..10.90 rows=90 width=880) (actual time=0.017..0.024 rows=24 loops=1)
Planning Time: 0.141 ms
Execution Time: 0.085 ms
(7 rows)
--查询13条数据
test=# select ctid,* from t1 order by order_no,id limit 13;
CTID | ORDER_NO | WORKID | ID | SPECIFICWORKARRANGEMENT | LAST_FEEDBACK_TIME
--------+----------+----------------------------------+---------------------+-------------------------+---------------------
(0,19) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646712945626017793 | 董辉测试安排-01 | 2023-04-17 16:42:06
(0,3) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713389349826561 | 李强测试安排-01 | 2023-04-17 16:42:06
(0,4) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713785514422273 | 田辰测试安排-01 | 2023-04-17 16:42:06
(0,20) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714368967274497 | 高晶测试安排-01 | 2023-04-17 16:42:06
(0,17) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714734073049089 | 赵琦测试安排-01 | 2023-04-17 16:42:06
(0,10) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646716902939258881 | 杨云测试安排-01 | 2023-04-17 16:42:06
(0,8) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713121581264897 | 董辉测试安排-02 | 2023-04-17 16:42:06
(0,1) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713438360268802 | 李强测试安排-02 | 2023-04-17 16:42:06
(0,12) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713829130989570 | 田辰测试安排-02 | 2023-04-17 16:42:06
(0,7) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714421022781441 | 高晶测试安排-02 | 2023-04-17 16:42:06
(0,22) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714788343148545 | 赵琦测试安排-02 | 2023-04-17 16:42:06
(0,14) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646716953950384129 | 杨云测试安排-02 | 2023-04-17 16:42:06
(0,21) | 3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646713175176081409 | 董辉测试安排-03 | 2023-04-17 16:42:06
(13 rows)
--执行计划
test=# explain analyze select ctid,* from t1 order by order_no,id limit 13;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=13.02..13.05 rows=13 width=880) (actual time=0.058..0.062 rows=13 loops=1)
-> Sort (cost=13.02..13.24 rows=90 width=880) (actual time=0.058..0.059 rows=13 loops=1)
Sort Key: order_no, id
Sort Method: quicksort Memory: 28kB
-> Seq Scan on t1 (cost=0.00..10.90 rows=90 width=880) (actual time=0.014..0.021 rows=24 loops=1)
Planning Time: 0.148 ms
Execution Time: 0.085 ms
(7 rows)
--查询15条数据
test=# select ctid,* from t1 order by order_no,id limit 15;
CTID | ORDER_NO | WORKID | ID | SPECIFICWORKARRANGEMENT | LAST_FEEDBACK_TIME
--------+----------+----------------------------------+---------------------+-------------------------+---------------------
(0,19) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646712945626017793 | 董辉测试安排-01 | 2023-04-17 16:42:06
(0,3) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713389349826561 | 李强测试安排-01 | 2023-04-17 16:42:06
(0,4) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713785514422273 | 田辰测试安排-01 | 2023-04-17 16:42:06
(0,20) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714368967274497 | 高晶测试安排-01 | 2023-04-17 16:42:06
(0,17) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714734073049089 | 赵琦测试安排-01 | 2023-04-17 16:42:06
(0,10) | 1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646716902939258881 | 杨云测试安排-01 | 2023-04-17 16:42:06
(0,8) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713121581264897 | 董辉测试安排-02 | 2023-04-17 16:42:06
(0,1) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713438360268802 | 李强测试安排-02 | 2023-04-17 16:42:06
(0,12) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713829130989570 | 田辰测试安排-02 | 2023-04-17 16:42:06
(0,7) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714421022781441 | 高晶测试安排-02 | 2023-04-17 16:42:06
(0,22) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714788343148545 | 赵琦测试安排-02 | 2023-04-17 16:42:06
(0,14) | 2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646716953950384129 | 杨云测试安排-02 | 2023-04-17 16:42:06
(0,21) | 3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646713175176081409 | 董辉测试安排-03 | 2023-04-17 16:42:06
(0,18) | 3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646713535290634242 | 李强测试安排-03 | 2023-04-17 16:42:06
(0,13) | 3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646713878858657794 | 田辰测试安排-03 | 2023-04-17 16:42:06
(15 rows)
--执行计划
test=# explain analyze select ctid,* from t1 order by order_no,id limit 15;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=13.11..13.15 rows=15 width=880) (actual time=0.073..0.078 rows=15 loops=1)
-> Sort (cost=13.11..13.33 rows=90 width=880) (actual time=0.072..0.074 rows=15 loops=1)
Sort Key: order_no, id
Sort Method: quicksort Memory: 28kB
-> Seq Scan on t1 (cost=0.00..10.90 rows=90 width=880) (actual time=0.016..0.026 rows=24 loops=1)
Planning Time: 0.132 ms
Execution Time: 0.105 ms
(7 rows)
- 当ORDER BY子句应用于非唯一的列名时，数据可能会以非决定性（随机）的顺序返回结果。这在使用LIMIT和OFFSET时尤其是一个问题，因为它们可能会返回完全不同的结果。为了避免这种情况，建议在增加一个排序列，并且该列是唯一的，或者至少是在排序列的相同值中是唯一的。
- 如果查询没有使用order by排序（表没有主键、唯一索引），扫描器（scanner）在扫描时，会检查是否有 limit 子句，如果有就会记录已经返回的行数，并在达到 limit 的值时停止扫描，节省IO开销和内存消耗。
- limit 子句是通过扫描器（scanner）实现的，扫描器在扫描表或索引时，会检查是否有 limit 子句，如果有就会记录已经返回的行数，并在达到 limit 的值时停止扫描。
- limit 子句和 order by 子句是分开处理的，order by 子句会导致扫描器对所有符合条件的行进行排序，然后再应用 limit 子句。 order by 子句会增加排序的开销，而不会减少扫描器的开销。
2：对排序列添加索引限制查询结果返回行数：
--创建索引
create index t1_id_idx on t1(id);
create index t1_order_no_specificworkarrangement_idx on t1(order_no,specificworkarrangement);
test=# select * from t1 order by order_no limit 10;
ORDER_NO | WORKID | ID | SPECIFICWORKARRANGEMENT | LAST_FEEDBACK_TIME
----------+----------------------------------+---------------------+-------------------------+---------------------
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713389349826561 | 李强测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646716902939258881 | 杨云测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713785514422273 | 田辰测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646712945626017793 | 董辉测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714734073049089 | 赵琦测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714368967274497 | 高晶测试安排-01 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713438360268802 | 李强测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646716953950384129 | 杨云测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713829130989570 | 田辰测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713121581264897 | 董辉测试安排-02 | 2023-04-17 16:42:06
(10 rows)
test=# explain analyze select * from t1 order by order_no limit 10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.14..5.63 rows=10 width=874) (actual time=0.018..0.023 rows=10 loops=1)
-> Index Scan using t1_order_no_specificworkarrangement_idx on t1 (cost=0.14..49.49 rows=90 width=874) (actual time=0.017..0.020 rows=10 loops=1)
Planning Time: 0.121 ms
Execution Time: 0.042 ms
(4 rows)
test=# explain analyze select * from t1 order by id limit 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.14..5.63 rows=10 width=874) (actual time=0.011..0.020 rows=10 loops=1)
-> Index Scan using t1_id_idx on t1 (cost=0.14..49.49 rows=90 width=874) (actual time=0.010..0.018 rows=10 loops=1)
Planning Time: 0.091 ms
Execution Time: 0.038 ms
(4 rows)
test=#
test=#
test=# select * from t1 order by order_no limit 15;
ORDER_NO | WORKID | ID | SPECIFICWORKARRANGEMENT | LAST_FEEDBACK_TIME
----------+----------------------------------+---------------------+-------------------------+---------------------
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713389349826561 | 李强测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646716902939258881 | 杨云测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713785514422273 | 田辰测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646712945626017793 | 董辉测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714734073049089 | 赵琦测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714368967274497 | 高晶测试安排-01 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713438360268802 | 李强测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646716953950384129 | 杨云测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713829130989570 | 田辰测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713121581264897 | 董辉测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714788343148545 | 赵琦测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714421022781441 | 高晶测试安排-02 | 2023-04-17 16:42:06
3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646713535290634242 | 李强测试安排-03 | 2023-04-17 16:42:06
3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646717012012134401 | 杨云测试安排-03 | 2023-04-17 16:42:06
3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646713878858657794 | 田辰测试安排-03 | 2023-04-17 16:42:06
(15 rows)
test=# explain analyze select * from t1 order by order_no limit 15;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.14..8.37 rows=15 width=874) (actual time=0.019..0.026 rows=15 loops=1)
-> Index Scan using t1_order_no_specificworkarrangement_idx on t1 (cost=0.14..49.49 rows=90 width=874) (actual time=0.018..0.022 rows=15 loops=1)
Planning Time: 0.095 ms
Execution Time: 0.045 ms
(4 rows)
test=#
test=# select * from t1 order by order_no limit 20;
ORDER_NO | WORKID | ID | SPECIFICWORKARRANGEMENT | LAST_FEEDBACK_TIME
----------+----------------------------------+---------------------+-------------------------+---------------------
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713389349826561 | 李强测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646716902939258881 | 杨云测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646713785514422273 | 田辰测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646712945626017793 | 董辉测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714734073049089 | 赵琦测试安排-01 | 2023-04-17 16:42:06
1 | fd9317e8597d4757ac9d7cee57283ed5 | 1646714368967274497 | 高晶测试安排-01 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713438360268802 | 李强测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646716953950384129 | 杨云测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713829130989570 | 田辰测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646713121581264897 | 董辉测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714788343148545 | 赵琦测试安排-02 | 2023-04-17 16:42:06
2 | bba0e34a98c842a5b5cd0f5b05b5f589 | 1646714421022781441 | 高晶测试安排-02 | 2023-04-17 16:42:06
3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646713535290634242 | 李强测试安排-03 | 2023-04-17 16:42:06
3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646717012012134401 | 杨云测试安排-03 | 2023-04-17 16:42:06
3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646713878858657794 | 田辰测试安排-03 | 2023-04-17 16:42:06
3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646713175176081409 | 董辉测试安排-03 | 2023-04-17 16:42:06
3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646714834161725442 | 赵琦测试安排-03 | 2023-04-17 16:42:06
3 | 54819e7ba7d7424d9df5ed851c9e69de | 1646714510139158529 | 高晶测试安排-03 | 2023-04-17 16:42:06
4 | f530d705b0ff4f278d9c8ca3085f55ca | 1646713581792882690 | 李强测试安排-04 | 2023-04-17 16:42:06
4 | f530d705b0ff4f278d9c8ca3085f55ca | 1646717059625873409 | 杨云测试安排-04 | 2023-04-17 16:42:06
(20 rows)
test=# explain analyze select * from t1 order by order_no limit 20;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.14..11.11 rows=20 width=874) (actual time=0.018..0.026 rows=20 loops=1)
-> Index Scan using t1_order_no_specificworkarrangement_idx on t1 (cost=0.14..49.49 rows=90 width=874) (actual time=0.017..0.023 rows=20 loops=1)
Planning Time: 0.148 ms
Execution Time: 0.046 ms
(4 rows)
- 排序列无索引场景增加一个排序列，并且该列是唯一的或者至少在排序列的相同值中是唯一的查询分页符合预期结果。
- 在排序列有索引场景的查询分页数据库使用索引扫描来获取所需的行数，然后停止扫描，并且由于索引本身是有序的，保证了返回结果顺序的一致性。符合预期并且也可以节省IO开销和内存消耗。
四、总结
-
当ORDER BY子句应用于非唯一的列名时，数据块可能会以非决定性的顺序返回结果。这在使用LIMIT和OFFSET时尤其是一个问题，因为它们可能会返回完全不同的结果。为了避免这种情况，建议在一个列上使用一个并列，该列是唯一的，或者至少是在有序列的相同值中是唯一的。
-
limit 子句是通过扫描器（scanner）实现的，扫描器在扫描表或索引时，会检查是否有 limit 子句，如果有就会记录已经返回的行数，并在达到 limit 的值时停止扫描。
-
limit 子句和 order by 子句是分开处理的，order by 子句会导致扫描器对所有符合条件的行进行排序，然后再应用 limit 子句。这意味着 order by 子句会增加排序的开销，而不会减少扫描器的开销。
-
limit 子句和 offset 子句也是分开处理的，offset 子句会导致扫描器跳过指定数量的行，而不返回给客户端。这意味着 offset 子句会增加扫描器的开销，而不会减少数据传输的开销。
-
如果查询带有OFFSET子句，KingbaseES数据库需要先跳过指定的行数，然后再返回所需的行数，这样会增加扫描开销和内存消耗。
-
limit 子句和 offset 子句是分开处理的，offset 子句会导致扫描器跳过指定数量的行，而不返回给客户端。offset 子句会增加扫描器的开销，而不会减少数据传输的开销。
-
尽量避免使用复杂的排序条件，使用简单的索引列或主键进行排序。
KINGBASE研究院
