GreatSQL分页查询优化案例实战

2025-09-10 98

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处理起来还是有优势的。

总结

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-54-1-1.html

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

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

技术交流群：

微信&QQ群：

QQ群：533341697

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

微信关注我们

原文链接：https://my.oschina.net/GreatSQL/blog/18691258

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

VexDB：源于清华的向量数据库

向量数据库向量数据库是专为高维向量数据的存储、索引与查询而设计的数据库管理系统，它通过特化的算法和数据结构支持高性能、可扩展的相似性搜索，广泛用于机器学习、数据挖掘等场景。它能处理数值、文本、二进制等多种类型的数据，并支持稀疏向量和向量的元数据存储。随着 AI 技术和大语言模型的普及，非结构化数据（如文本、图像、多模态数据）在应用中激增，例如 ChatGPT 等模型处理的信息体量极大，需支持快速、高效的语义检索。向量数据库通过向量嵌入和近似查询机制，能精准匹配复杂信息，满足 AI 应用对大规模非结构化数据处理的需求。 DB-Engines 排行榜（2025年9月）中排名靠前的 Vector DBMS 有：Oracle、PostgreSQL、MongoDB、Redis、Elasticsearch 等。国产向量数据库产品方面，依据墨天轮中国数据库流行度排行榜，向量模型分类，Milvus（Zilliz）、腾讯云 VectorDB、VikingDB、Vearch 等位于前列。根据 Forrester 2024 年报告，向量数据库市场正迅猛扩张，分为原生向量数据库和多模态向量数据库两类，...

2025-09-10

165

JavaScript 中的 Tuples（Tuples）与 Records（Records）提供了不可变的、基于值的数据结构，能简化状态管理、提升性能并增强代码的可预测性。 JavaScript 一直在持续进化以满足现代开发需求，其最新更新往往紧跟函数式编程和不可变数据处理的趋势。Tuples 与 Records 作为语言即将新增的两个特性，旨在简化不可变性的实现，同时提升开发效率与体验。本文将深入探讨这两个新特性，包括它们的设计目的、语法、优势及应用场景。一、什么是 Tuples 与 Records？ 1. Tuples（元组） Tuples 是不可变的有序值列表。和数组类似，Tuples 可以存储多个元素，但不可变性确保了数据一旦创建就无法修改------这保证了数据一致性，非常适合对数据完整性和可预测性要求高的场景。 2. Records（记录） Records 是不可变的键值对结构，类似 JavaScript 中的对象，但它是只读的：一旦创建，其属性和值就无法修改。二、Tuples 与 Records 的核心特性 1. 不可变性（Immutability） Tuples 和...

2025-09-10

167

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。