往期最佳实践系列：

PolarDB-X最佳实践系列（一）：如何设计一张用户表

PolarDB-X最佳实践系列（二）：如何使用DataWorks将数据同步到MaxCompute

分页查询是数据库中常见的操作。本文将介绍，如何在数据库中（无论是单机还是分布式）高效的进行翻页操作。

我们有以下需求：

1、一张表有十亿的数据
2、遍历这张表的所有数据，每次返回1000条
3、遍历要按照数据写入的时间顺序
4、遍历的性能需要是恒定的，不能有衰减，也即翻前面的页很快，翻到后面的页也很快
5、数据不能有遗漏

1. LIMIT M, N的代价是O(M+N)

分页查询最简单的方法，以MySQL为例，，是使用LIMIT M, N：

SELECT * FROM t1 ORDER BY ID LIMIT 10000,1000;

这种方式，在翻前面几页的数据时，代价是比较低的，但越往后翻，代价会成倍的增长。 原因理解起来很简单，常见的数据库，都是无法直接定位到第10000行数据的位置的，所以对于类似语法的查询，数据库的执行方式是从第一行开始扫描，一行一行的扫描并跳过，直到扫描过10000行之后，再返回之后的1000条结果。

对于上面的查询，数据库需要扫描10000+1000=11000条记录，远远超过最后返回的10条记录。越往后翻，需要扫描的数据就越多，代价就越高，性能就越差。

注意，以上描述假设是走了索引的情况下，如果不走索引，代价会更高。

2. 分布式中，LIMIT M, N的代价更大

对于分布式数据库，其实现LIMIT M, N的代价也是O(M+N)，但大多数情况下，其代价比单机数据库更大。 简单说，对于分布式数据库，上文中的LIMIT查询，相当于每一个节点需要执行如下的查询：

SELECT * FROM t1 ORDER BY ID LIMIT 0, 10000 + 1000;

这10000+1000条数据，需要传递到某一个节点上，进行排序之后才能得到最终的1000条数据。

可以看出，总的代价也是O(M+N)，但相对于单机数据库，又需要乘上网络传输的代价。当然，部分情况下，分片之间是按照排序键有序的，只在一个节点上执行即可，此种场景下代价与单机数据库类似。

在数据量较少、并发低、性能要求不高等情况下，直接使用LIMIT M, N就可以了，如果要求更高，就需要使用其它的方法了。

3. 一个简单并高效的分页查询

在单机数据库中，我们很容易可以使用如下的写法：

SELECT * FROM t1 ORDER BY id LIMIT 1000;
SELECT * FROM t1 WHERE id > ? ORDER BY id LIMIT 1000;

记录每一批最后一条ID，并作为下一批的起始值写在WHERE条件中。通常情况下，ID我们会使用一个自增列，其大小代表了数据插入的先后时间。

由于ID是一个有序的索引，数据库可以直接根据ID的值定位到扫描的起始位置，而不需要先扫描之前的数据，最终的代价也只有结果需要的1000条数据。 这种方法非常的高效，能够满足我们的性能要求。

如果直接将此方法套用到分布式数据库或者用于其他业务场景中，可能会面临以下几个问题：

1、大部分分布式数据库，其主键的生成策略为分段的，仅能做到唯一，而做不到单调递增。如果使用ID进行排序，数据不能按照写入的时间进行返回；如果使用时间列进行排序，时间值有可能出现重复。
2、有时想按其他列的顺序进行翻页，这个列和上面的时间列类似，不能保证唯一。
3、要遍历的数据涉及多个节点的数据，有时候对顺序要求不高，希望逐个节点进行遍历。

4. PolarDB-X中如何做

PolarDB-X中，自增主键有两种生成策略：

1、分段生成，不保证有序的，称为Group Sequence。此种生成策略是mode=drds的DB所使用的。（另外，PolarDB-X 1.0中，也是使用此种策略）
2、全局有序的，称为New Sequence。这种生成策略是mode=auto的DB所使用的。
另外，使用SHOW CREATE DATABASE可以查看数据库的mode，使用SHOW SEQUENCES可以查看表的自增策略。关于全局自增有序的Sequence实现原理，可以参考：PolarDB-X 中的 AUTO_INCREMENT 兼容性

5. 使用New Sequence的表

得益于和MySQL AUTO_INCREMENT的高度兼容，在PolarDB-X中，对于使用New Sequence的表，其分页遍历的方式，可以与单机MySQL的一致，也即使用如下的查询：

SELECT * FROM t1 ORDER BY id LIMIT 1000;
SELECT * FROM t1 WHERE id > ? ORDER BY id LIMIT 1000;

但这里要注意的是，在不指定ORDER BY的情况下，数据库实际上并不保证每次返回的顺序：

1、在单机数据库中，通常情况下是按照所使用的索引的顺序进行返回，但这个顺序不能得到保证，使用索引的变化、统计信息的变化等都可能导致顺序的变化。
2、在分布式数据库中，情况更为多变，不同的节点返回数据的先后顺序是随机的，大多数分布式数据库，对于此类查询，返回的结果会有很大的随机性。

因此，对于此类分页查询，无论是使用单机数据库还是分布式数据库，都建议显式的指定ORDER BY，确保从SQL的语义上限制返回的顺序。

6. 使用Group Sequence的表

对于此种类型的表，id的顺序并不能代表记录写入的时间先后。大多情况下，我们的表里都会有一个时间列来标记行的写入时间，例如：

CREATE TABLE t1(
  id bigint PRIMARY KEY AUTO_INCREMENT BY GROUP,
  gmt_create timestamp DEFAULT current_timestamp,
  INDEX idx_gmt_create_id(gmt_create, id)
) PARTITION BY HASH(id);

如果简单套用上述的方法，记录每一批gmt_create的最大值，并作为下一批的起始值，如下：

## 错的方法！！不要用！！
SELECT * FROM t1 ORDER BY gmt_create LIMIT 1000;
SELECT * FROM t1 WHERE gmt_create > ? ORDER BY gmt_create LIMIT 1000;
## 错的方法！！不要用！！

由于gmt_create可能出现重复，因此第二批使用“gmt_create > ?”会漏数据，使用“gmt_create >= ?”会有重复数据。

正确的方法是：

SELECT * FROM t1 ORDER BY gmt_create, id LIMIT 1000;
SELECT * FROM t1 WHERE (gmt_create = ? AND  > ?) OR gmt_create > ? ORDER BY gmt_create, id LIMIT 1000;

或者：

SELECT * FROM t1 ORDER BY gmt_create, id LIMIT 1000;
SELECT * FROM t1 WHERE (gmt_create, id) > (?, ?) ORDER BY gmt_create, id LIMIT 1000;

以上两种写法是等价的。在PolarDB-X中，推荐使用第二种写法。第一种写法可以用于其他不支持元组条件的数据库。

对于按照其他列进行排序分页的需求，同理。

7. 按分片进行遍历

当要查询的数据没有带分区键的时候，以上分页查询是一个跨分区的查询。此类查询在低并发的情况下，不会有太大的性能问题，可以直接使用。 在一些极端场景下，例如：

表的分片数多，例如>=256；
1、稳定性要求极高，不希望有任何不可控因素；
2、对数据的顺序要求不高。
3、这时候我们可以按照分片对数据进行遍历。

1. 使用SHOW TOPOLOGY FROM tbl获取表的拓扑信息：

1. 使用HINT指定分片信息，例如查询p1分片：

/*TDDL:node='MENGSHI1_P00000_GROUP'*/SELECT * FROM t1_iVir_00000 ORDER BY id LIMIT 1000

1. 使用上文提到的分页查询的方法，对一个分片的数据进行遍历
2. 外层套一个循环，对所有的分片的数据进行遍历

数据导出场景使用Batch Tool

有些时候，做分页查询是为了做数据的导出，对于这种场景，推荐直接使用PolarDB-X开源的Batch Tool，其内部对于PolarDB-X的导出操作做了更为丰富的优化。

详见：如何通过BatchTool工具导入导出数据_云原生数据库 PolarDB-阿里云帮助中心

8. 其他注意点

对于排序的列，需要有合适的索引，例如，如果按照(gmt_create, id)进行排序，则应该有(gmt_create, id)上的组合索引。如果有其他的WHERE条件，则应该一并考虑索引信息。例如，对于查询：

SELECT * FROM t1 WHERE c1 = xxx ORDER BY gmt_create, id LIMIT 1000;

通常情况下，需要(c1, gmt_create, id)上的组合索引。

1. 对于JAVA应用，需要设置合适的JDBC参数，避免超时等奇怪的错误，包括：
2. 设置netTimeoutForStreamingResults=0
3. 设置socketTimeout，单位是毫秒
4. Statement对象setFetchSize(Integer.MIN_VALUE)，避免爆内存
5. 保持autocommit=true，避免因为分页查询出现长事务。

附：一个简单的分页查询的Java Demo

package com.taobao.tddl.sample;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class PageSample {
    public static void main(String[] args) throws Exception {
        int index = 0;
        boolean first = true;
        Object maxGmtCreate = null;
        long maxId = -1;
        while (true) {
            Connection conn = null;
            try {
                conn = ds.DriverManager.getConnection("jdbc:mysql://xxxxxxx:3306/dbname","user","password")
                PreparedStatement ps = null;
                if (first) {
                    ps = conn.prepareStatement("SELECT * FROM t1  order by gmt_create,id limit 99");
                    first = false;
                } else {
                    ps = conn.prepareStatement(
                        "SELECT * FROM t1 where gmt_create >= ? and (gmt_create > ? or id > ?) order by gmt_create,id limit 99");
                    ps.setObject(1, maxGmtCreate);
                    ps.setObject(2, maxGmtCreate);
                    ps.setLong(3, maxId);
                }

                ResultSet rs = ps.executeQuery();
                maxGmtCreate = null;
                maxId = -1;
                while (rs.next()) {
                    System.out.println((++index) + " " + rs.getInt("id") + " " + rs.getString("gmt_Create"));
                    maxGmtCreate = rs.getObject("gmt_create");
                    maxId = rs.getLong("id");
                }

                if (maxId == -1) {
                    break;
                }
            } finally {
                conn.close();
            }
        }
    }
}

数据库PolarDB-X新人入门一站式页面，快速体验集中分布式一体化新特性！
云原生数据库PolarDB分布式版新人入门
作者：梦实