当前位置：紫竹林-程序员中文网 > 科技 > 正文

GreatSQL分页查询优化案例实战

2025-09-10 分类：科技 阅读(11) 评论(0)


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 * FROM t_pagequery WHERE ROWNUM<=10 ORDER BY log_time DESC)，这样的效果是先取10行再排序，不是先排序再取10行，要对排序后的结果集取前10行，需要将ROWNUM放在第二层。因为 ROWNUM 是结果集的序号，不能用大于的条件，所以只能在第二层对其起别名，再套一层查询对别名施加大于的条件实现分页查询。

说实话，Oracle 这种写法，还挺复杂的，GreatSQL 原生的分页查询写法就相对简单了，只需一层查询即可实现。

SELECT t.* FROM t_pagequery t ORDER BY log_time DESC LIMIT 0,10;

GreatSQL 的 LIMIT 子句不是 WHERE 条件，对查询结果的一个分页截取。如果非要跟 Oracle 对标，把序号加上，需要使用 ROW_NUMBER 窗口函数。

SELECT t.*,ROW_NUMBER() OVER() rn FROM t_pagequery t ORDER BY log_time DESC LIMIT 0,10;

需要注意的一点，GreatSQL 的 sql_mode 如果设置为 Oracle 模式，会导致上面的查询无法使用索引排序。

据研发人员解释 Oracle 的索引与 GreatSQL 有所不同，Oracle 的普通索引，如果索引列的数据都是 NULL 值，不会放在索引树上，而 GreatSQL 上没有这个限制，即使都为 NULL，也可以存放在索引树上，因为 GreatSQL 的索引结构中除了存储索引列信息，还存储了主键信息。另外Oracle的默认排序是把NULL值放在后面，而 GreatSQL 的默认排序是把 NULL 值放在前面。基于索引结构与排序方式的不同，GreatSQL 在兼容 Oracle 的模式时，没有用索引排序。

GreatSQL 对 Oracle 的 ROWNUM 函数语法做了兼容，在 Oracle 上执行的分页查询语句，在 GreatSQL 上执行是完全没有问题的，但是 ROWNUM 函数的使用也会导致无法使用索引排序，小表使用文件排序也没有性能问题，遇到百万千万级大表时性能问题会显现，此时要将 Oracle 分页查询的写法，改为 GreatSQL 原生的分页查询，来提升性能。

实验验证

下面通过实验验证上面的结论。

创建测试表,插入10万行测试数据。

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;

1.验证 Oracle 分页写法与 GreatSQL 原生分页写法的结果集是一致的。

greatsql> SELECT *
    ->   FROM (SELECT t.*, ROWNUM rn
    ->           FROM (SELECT * FROM t_pagequery order by log_time desc) t
    ->          where ROWNUM <= 10)
    ->  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 模式下，查询一下执行计划。

greatsql> SELECT @@sql_mode;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

greatsql> explain
    -> SELECT *
    ->   FROM (SELECT t.*, ROWNUM rn
    ->           FROM (SELECT * FROM t_pagequery order by log_time desc) t
    ->          where ROWNUM <= 10)
    ->  WHERE RN > 0;
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | PRIMARY     | <derived2>  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100406 |    33.33 | Using where    |
|  2 | DERIVED     | <derived3>  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100406 |   100.00 | Using where    |
|  3 | DERIVED     | t_pagequery | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100406 |   100.00 | Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
3 rows in set, 1 warning (0.00 sec)

greatsql> explain SELECT t.*,ROW_NUMBER() OVER() rn FROM t_pagequery t ORDER BY log_time DESC LIMIT 0,10;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra               |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx_logtime | 6       | NULL |   10 |   100.00 | Backward index scan |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
1 row in set, 2 warnings (0.01 sec)

Oracle 分页查询的写法，在 GreatSQL 上执行，虽然只取前几行数据，却需要会对表进行全表扫描，再进行filesort，如果是个千万级别的大表，代价是很大的。而GreatSQL原生写法，使用了”Backward index scan”，倒序扫描索引10行就可以了，显然这种效率更高效。

3.Oracle 模式下，GreatSQL 原生写法的执行计划。

greatsql> SET sql_mode=Oracle;
Query OK, 0 rows affected (0.00 sec)

greatsql> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,Oracle,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

greatsql> explain SELECT t.* FROM t_pagequery t ORDER BY log_time DESC LIMIT 0,10;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100406 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

Oracle模式下，GreatSQL原生写法也用不上索引，从Oracle迁移过来的系统，为了更好的兼容Oracle语法，一般是要开启Oracle模式的，那为了使用索引排序，可以在语句级别加hint设置sql_mode来解决，随便指定一个sql_mode即可，但不能设置成空串。

greatsql> explain SELECT /*+ set_var(sql_mode="ONLY_FULL_GROUP_BY") */  t.*,row_number() over() FROM t_pagequery t ORDER BY log_time DESC LIMIT 0,10;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra               |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx_logtime | 6       | NULL |   10 |   100.00 | Backward index scan |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
1 row in set, 2 warnings (0.00 sec)

greatsql> explain SELECT /*+ set_var(sql_mode="") */  t.*,row_number() over() FROM t_pagequery t ORDER BY log_time DESC LIMIT 0,10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 94474 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 3 warnings (0.00 sec)

再提及一点细节，Oracle的普通索引，如果索引列都是NULL值，则不会存储在索引Tree结构中。而GreatSQL没有这个限制，所以我在建测试表时并没有指定索引列log_time带NOT NULL约束，照样用上了索引排序。而Oracle如果这样建表则不会使用索引排序，Oracle要么有非空约束，要么语句中加条件IS NOT NULL，要么与其他NOT NULL列或常数列建联合索引，才能用上索引排序。这一点上来看GreatSQL处理起来还是有优势的。

总结

  1. Oracle的分页查询借助ROWNUM做三层嵌套查询，GreatSQL的原生分页查询是使用LIMIT子句，GreatSQL虽然兼容Oracle这种分页查询语法，却无法使用索引排序，小表没有问题，但是对百万千万级别的大表，使用文件排序会很耗资源，需要改成GreatSQL原生的写法来提升效率。
  2. GreatSQL的sql_mode为Oracle模式时，无法使用索引排序，需要用hin语句级别指定sql_mode来解决，注意不能指定成空串。
  3. GreatSQL的二级索引中会存储索引列都为NULL的数据，在使用索引排序时，不用考虑带着索引列条件IS NOT NULL或者加非空约束。

Enjoy GreatSQL :)

关于 GreatSQL

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

相关链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

image

社区有奖建议反馈： https://greatsql.cn/thread-54-1-1.html

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

（对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~）

技术交流群：

微信&QQ群：

QQ群：533341697

微信群：添加GreatSQL社区助手（微信号：wanlidbc ）好友，待社区助手拉您进群。

                                                                                </div>



Source link

未经允许不得转载：紫竹林-程序员中文网 » GreatSQL分页查询优化案例实战

相关推荐

评论 抢沙发

取消

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

近期文章

关于我们 免责申明 意见反馈 隐私政策
程序员中文网：公益在线网站，帮助学习者快速成长！
关注微信 技术交流
推荐文章
每天精选资源文章推送
推荐文章
随时随地碎片化学习
推荐文章
发现有趣的

Copyright©2010-2022 鄂ICP备16010909号