dm8四种分页查询的写法以及对比

达梦数据库（DM8）支持多种分页查询方式，包括类似 Oracle(使用 ROWNUM)、MySQL(使用 LIMIT 和 OFFSET) 、 SQL Server(使用 TOP 和子查询) 和FETCH NEXT的分页语法。本文目的为探究在数据量大的情况下这4种分页查询各自的性能差异。

可选择实验工具

1. EXPLAIN执行计划

EXPLAIN执行计划能直观展示SQL的表扫描方式、连接逻辑、索引使用等关键信息，帮助定位“全表扫描”“重复扫描”等性能瓶颈。可以使用EXPLAIN + SQL 或EXPLAIN FOR + SQL获取对应SQL的执行计划

2. AUTOTRACE工具

AUTOTRACE是达梦数据库的SQL执行分析工具，通过SET AUTOTRACE命令指定不同模式，可实现“仅看执行计划”“执行SQL并监控”等不同分析需求
语法如下：

SET AUTOTRACE <OFF(默认值) | NL | INDEX | ON | TRACE | TRACEONLY>

各模式详情如下：

SET AUTOTRACE OFF
停止AUTOTRACE功能，按常规方式执行SQL语句（不额外打印执行计划或监控信息）。
SET AUTOTRACE NL
开启AUTOTRACE功能，不执行SQL语句；若执行计划中包含嵌套循环（NEST LOOP）操作，将仅打印该操作符相关的执行计划详情。
SET AUTOTRACE INDEX（或 ON）
开启AUTOTRACE功能，不执行SQL语句；若执行计划涉及表扫描操作，将打印表扫描方式、关联表名及所用索引信息（INDEX与ON模式功能完全等价）。
SET AUTOTRACE TRACE（常用）
开启AUTOTRACE功能，会实际执行SQL语句，同时打印执行计划及执行过程中的部分监控信息。
注意：需先将dm.ini中3个监控参数（ENABLE_MONITOR、MONITOR_SQL_EXEC、ENABLE_MONITOR_DMSQL）均设置为1（开启状态），该模式才生效。
与EXPLAIN的核心区别：EXPLAIN仅生成估算执行计划，不实际执行SQL，可能存在偏差；而TRACE模式获取的是服务器真实执行的计划（可能重用缓存计划或新生成计划），准确性更高。
SET AUTOTRACE TRACEONLY（常用）
开启AUTOTRACE功能，会实际执行SQL语句，同时打印执行计划及执行过程中的部分监控信息（与TRACE模式的参数依赖一致）。
与TRACE模式的核心区别：执行查询类SQL时，仅输出执行计划和监控信息，不打印查询结果集（避免大结果集占用输出资源）。

实验设计

1. 新建对应表并插入数据

这里设计了一个简单的员工表

CREATE TABLE "TEMP"."EMPLOYEES"
(
 "ID" INT NOT NULL,
 "NAME" VARCHAR(50),
 "DEPARTMENT" VARCHAR(50),
 "SALARY" DECIMAL(10,2),
  NOT CLUSTER PRIMARY KEY("ID")
);

在设计并创建表后可以通过SQLark进行数据生成，这里我们插入100000条数据进行模拟

2. 设计对应SQL

在完成表设计和数据插入后需要根据表结构设计分页查询语句，这里根据4种不同的写法分别编写了对应的分页查询。查询结果一致。

2.1 使用ROWNUM语法分页查询

Oracle风格分页查询

-- 使用ROWNUM
SELECT * FROM (
    SELECT ROWNUM AS rowno, t.* FROM (
        SELECT * FROM employees ORDER BY salary DESC
    ) t WHERE ROWNUM <= 20
) WHERE rowno > 10;

使用说明

在查询中，ROWNUM可与任何数字类型表达式进行比较及运算；
ROWNUM可以在非相关子查询中使用；当参数ENABLE_RQ_TO_INV等于1时，
部分相关子查询支持使用；
在非相关子查询中，ROWNUM 只能实现与 TOP 相同的功能，因此子查询不能含
ORDER BY 和GROUP BY；
ROWNUM所处的子谓词只能为如下形式： ROWNUM op exp，exp的类型只能是
立即数、参数和变量值，op ∈ {<， <=， >， >=， =， <>}。

2.2 使用LIMIT和OFFSET分页查询

-- 使用LIMIT和OFFSET
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET 10;

LIMIT共支持4种方式：

LIMIT N：选择前N条记录；
LIMIT M, N：选择第M条记录之后的N条记录；
LIMIT M OFFSET N：选择第N条记录之后的M条记录；
OFFSET N LIMIT M：选择第N条记录之后的M条记录。

2.3 使用TOP子句分页查询

-- 使用TOP子句
SELECT * FROM (
    SELECT TOP 10 * FROM (
        SELECT TOP 20 * FROM employees
        ORDER BY salary DESC
    ) t1
    ORDER BY salary ASC
) t2
ORDER BY salary DESC;

SELECT TOP 10,10 * FROM EMPLOYEES 
ORDER BY salary DESC;

TOP n 选择结果的前n条记录；
TOP n1, n2 选择第n1条记录之后的n2条记录；
TOP n PERCENT 表示选择结果的前n%条记录；
TOP n PERCENT WITH TIES 表示选择结果的前n%条记录，同时指定结果集可
以返回额外的行。额外的行是指与最后一行以相同的排序键排序的所有行。WITH TIES必须
与ORDER BY子句同时出现，如果没有ORDER BY子句，则忽略WITH TIES。

2.4 使用FETCH NEXT语法分页查询

SQL标准分页语法

-- 使用 FETCH NEXT
SELECT * FROM employees 
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

性能对比

现在根据上述4种分页查询计划分别通过执行计划进行分析：

使用ROWNUM

EXPLAIN SELECT * FROM (
    SELECT ROWNUM AS rowno, t.* FROM (
        SELECT * FROM employees ORDER BY salary DESC
    ) t WHERE ROWNUM <= 20
) WHERE rowno > 10;

使用LIMIT和OFFSET

EXPLAIN SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET 10;

使用TOP子句

EXPLAIN SELECT * FROM (
    SELECT TOP 10 * FROM (
        SELECT TOP 20 * FROM employees
        ORDER BY salary DESC
    ) t1
    ORDER BY salary ASC
) t2
ORDER BY salary DESC;

使用TOP子句中的TOP n1, n2语法(减少子查询)

EXPLAIN SELECT TOP 10,10 * FROM EMPLOYEES ORDER BY salary DESC;

使用FETCH NEXT

EXPLAIN SELECT * FROM employees 
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

在经过查询执行计划后可以发现这4种分页查询中使用FETCH NEXT和使用LIMIT和OFFSET时执行计划相同，TOP子句改写成TOP n1, n2的情况时执行计划也与之前相同，同时TOP子句在没有改写前代价比使用ROWNUM分页查询高。

结论

分页方式	执行计划复杂度	语法简洁性	兼容性
TOP分页	复杂/简单	一般/简洁	SQL Server
LIMIT OFFSET	简单	优秀	MySQL/PG
FETCH NEXT	简单	优秀	ANSI SQL标准
ROWNUM	复杂	一般	Oracle

在选择分页语句时若无特殊要求尽可能选择LIMIT OFFSET和FETCH NEXT语法，或者将TOP分页写法改写成TOP n1, n2形式。在分页查询时也可以通过添加适当索引、改写子查询或者使用with字句进行适当优化。