GreatSQL分页查询优化案例实战

GreatSQL分页查询优化案例实战

导语

随着国产化替代的深入，很多 Oracle 数据库迁移到 GreatSQL 上，GreatSQL 也做了大量的 Oracle 语法兼容，以减少国产化改造工作。
本文说一下 Oracle 的分页查询语句迁到 GreatSQL 上来的表现以及怎样用 GreatSQL 实现高效分页查询。
SQL 案例

SQL语句

1. SELECT *
2.   FROM (SELECT t.*, ROWNUM rn
3.           FROM (SELECT * FROM t_pagequery ORDER BY log_time DESC) t
4.          WHERE ROWNUM <= 10)
5. WHERE RN > 0

复制代码

语句分析

这是 Oracle12c 以前典型的分页查询的写法，借助 ROWNUM 伪列的三层嵌套查询。因为 ROWNUM 是对效果集加的伪列，是先有效果集，返回给客户端时加上去的一个列。

如果在最内层查询上加上 ROWNUM 条件(SELECT * FROM  t_pagequery WHERE  ROWNUM 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)[/code]从上面查询的执行时间来看，GreatSQL原生的写法耗时0.00s，性能更好。
2.非 Oracle 模式下，查询一下执行筹划。

1. 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 原生写法的执行筹划。

1. 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即可，但不能设置成空串。

1. CREATE TABLE t_pagequery(id NUMBER(10) PRIMARY KEY,
2. log_time DATETIME,
3. c1 VARCHAR(10),
4. key idx_logtime(log_time)
5. );
7. SET sql_mode=Oracle;
8. DELIMITER //
9. CREATE OR REPLACE PROCEDURE p1() IS
10. BEGIN
11.   FOR i IN 1..100000 LOOP
12.     INSERT INTO t_pagequery(id,log_time,c1) VALUES(i,SYSDATE-RAND()*1000,'a');
13.   END LOOP;
14. END;
15. //
16. DELIMITER ;
18. 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 😃
关于 GreatSQL

GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。
相关链接：   GreatSQL社区        Gitee        GitHub        Bilibili
GreatSQL社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

技能交换群：

微信：扫码添加GreatSQL社区助手微信好友，发送验证信息加群。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。