GreatSQL分页查询优化案例实战
GreatSQL分页查询优化案例实战导语
随着国产化替代的深入，很多 Oracle 数据库迁移到 GreatSQL 上，GreatSQL 也做了大量的 Oracle 语法兼容，以减少国产化改造工作。
本文说一下 Oracle 的分页查询语句迁到 GreatSQL 上来的表现以及如何用 GreatSQL 实现高效分页查询。
SQL 案例
SQL语句
SELECT *
FROM (SELECT t.*, ROWNUM rn
FROM (SELECT * FROM t_pagequery ORDER BY log_time DESC) t
WHERE ROWNUM <= 10)
WHERE RN > 0语句分析
这是 Oracle12c 以前典型的分页查询的写法，借助 ROWNUM 伪列的三层嵌套查询。因为 ROWNUM 是对结果集加的伪列，是先有结果集，返回给客户端时加上去的一个列。
如果在最内层查询上加上 ROWNUM 条件(SELECT * FROMt_pagequery WHEREROWNUM SELECT * -> FROM (SELECT t.*, ROWNUM rn -> FROM (SELECT * FROM t_pagequery order by log_time desc) t -> where ROWNUM WHERE RN > 0;+-------+---------------------+------+------+| id | log_time | c1 | rn |+-------+---------------------+------+------+| 24513 | 2025-07-11 11:08:19 | a | 1 || 78625 | 2025-07-11 11:06:48 | a | 2 || 96674 | 2025-07-11 10:39:17 | a | 3 || 57955 | 2025-07-11 10:30:58 | a | 4 || 41217 | 2025-07-11 10:30:51 | a | 5 || 34115 | 2025-07-11 10:22:08 | a | 6 || 39214 | 2025-07-11 10:19:31 | a | 7 ||2032 | 2025-07-11 10:12:18 | a | 8 ||7805 | 2025-07-11 09:53:12 | a | 9 || 74703 | 2025-07-11 09:44:34 | a | 10 |+-------+---------------------+------+------+10 rows in set (0.14 sec)greatsql> SELECT t.*,ROW_NUMBER() OVER() rn FROM t_pagequery t ORDER BY log_time DESC LIMIT 0,10;+-------+---------------------+------+----+| id | log_time | c1 | rn |+-------+---------------------+------+----+| 24513 | 2025-07-11 11:08:19 | a |1 || 78625 | 2025-07-11 11:06:48 | a |2 || 96674 | 2025-07-11 10:39:17 | a |3 || 57955 | 2025-07-11 10:30:58 | a |4 || 41217 | 2025-07-11 10:30:51 | a |5 || 34115 | 2025-07-11 10:22:08 | a |6 || 39214 | 2025-07-11 10:19:31 | a |7 ||2032 | 2025-07-11 10:12:18 | a |8 ||7805 | 2025-07-11 09:53:12 | a |9 || 74703 | 2025-07-11 09:44:34 | a | 10 |+-------+---------------------+------+----+10 rows in set (0.00 sec)从上面查询的执行时间来看，GreatSQL原生的写法耗时0.00s，性能更好。
2.非 Oracle 模式下，查询一下执行计划。
SELECT t.* FROM t_pagequery t ORDER BY log_time DESC LIMIT 0,10;Oracle 分页查询的写法，在 GreatSQL 上执行，虽然只取前几行数据，却需要会对表进行全表扫描，再进行filesort，如果是个千万级别的大表，代价是很大的。而GreatSQL原生写法，使用了"Backward index scan"，倒序扫描索引10行就可以了，显然这种效率更高效。
3.Oracle 模式下，GreatSQL 原生写法的执行计划。
SELECT t.*,ROW_NUMBER() OVER() rn FROM t_pagequery t ORDER BY log_time DESC LIMIT 0,10;Oracle模式下，GreatSQL原生写法也用不上索引，从Oracle迁移过来的系统，为了更好的兼容Oracle语法，一般是要开启Oracle模式的，那为了使用索引排序，可以在语句级别加hint设置sql_mode来解决，随便指定一个sql_mode即可，但不能设置成空串。
CREATE TABLE t_pagequery(id NUMBER(10) PRIMARY KEY,
log_time DATETIME,
c1 VARCHAR(10),
key idx_logtime(log_time)
);
SET sql_mode=Oracle;
DELIMITER //
CREATE OR REPLACE PROCEDURE p1() IS
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO t_pagequery(id,log_time,c1) VALUES(i,SYSDATE-RAND()*1000,'a');
END LOOP;
END;
//
DELIMITER ;
CALL p1;再提及一点细节，Oracle的普通索引，如果索引列都是NULL值，则不会存储在索引Tree结构中。而GreatSQL没有这个限制，所以我在建测试表时并没有指定索引列log_time带NOT NULL约束，照样用上了索引排序。而Oracle如果这样建表则不会使用索引排序，Oracle要么有非空约束，要么语句中加条件IS NOT NULL，要么与其他NOT NULL列或常数列建联合索引，才能用上索引排序。这一点上来看GreatSQL处理起来还是有优势的。
总结
[*]Oracle的分页查询借助ROWNUM做三层嵌套查询，GreatSQL的原生分页查询是使用LIMIT子句，GreatSQL虽然兼容Oracle这种分页查询语法，却无法使用索引排序，小表没有问题，但是对百万千万级别的大表，使用文件排序会很耗资源，需要改成GreatSQL原生的写法来提升效率。
[*]GreatSQL的sql_mode为Oracle模式时，无法使用索引排序，需要用hin语句级别指定sql_mode来解决，注意不能指定成空串。
[*]GreatSQL的二级索引中会存储索引列都为NULL的数据，在使用索引排序时，不用考虑带着索引列条件IS NOT NULL或者加非空约束。
Enjoy GreatSQL
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]