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 * 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 原生分页写法的结果集是一