PolarDB-X最佳实践系列（三）：如何实现高效的分页查询
分页查询是数据库中常见的操作。本文将介绍，如何在数据库中（无论是单机还是分布式）高效的进行翻页操作。
我们有以下需求：
1、一张表有十亿的数据
2、遍历这张表的所有数据，每次返回1000条
3、遍历要按照数据写入的时间顺序
4、遍历的性能需要是恒定的，不能有衰减，也即翻前面的页很快，翻到后面的页也很快
5、数据不能有遗漏
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条记录。越往后翻，需要扫描的数据就越多，代价就越高，性能就越差。
注意，以上描述假设是走了索引的情况下，如果不走索引，代价会更高。
分布式中，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就可以了，如果要求更高，就需要使用其它的方法了。
一个简单并高效的分页查询
在单机数据库中，我们很容易可以使用如下的写法：
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、要遍历的数据涉及多个节点的数据，有时候对顺序要求不高，希望逐个节点进行遍历。
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 兼容性
使用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的语义上限制返回的顺序。
使用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中，推荐使用第二种写法。第一种写法可以用于其他不支持元组条件的数据库。
对于按照其他列进行排序分页的需求，同理。
按分片进行遍历
当要查询的数据没有带分区键的时候，以上分页查询是一个跨分区的查询。此类查询在低并发的情况下，不会有太大的性能问题，可以直接使用。 在一些极端场景下，例如：
表的分片数多，例如>=256；
1、稳定性要求极高，不希望有任何不可控因素；
2、对数据的顺序要求不高。
3、这时候我们可以按照分片对数据进行遍历。
- 使用SHOW TOPOLOGY FROM tbl获取表的拓扑信息：
- 使用HINT指定分片信息，例如查询p1分片：
- /*TDDL:node='MENGSHI1_P00000_GROUP'*/SELECT * FROM t1_iVir_00000 ORDER BY id LIMIT 1000
- 使用上文提到的分页查询的方法，对一个分片的数据进行遍历
- 外层套一个循环，对所有的分片的数据进行遍历
数据导出场景使用Batch Tool
有些时候，做分页查询是为了做数据的导出，对于这种场景，推荐直接使用PolarDB-X开源的Batch Tool，其内部对于PolarDB-X的导出操作做了更为丰富的优化。
详见：如何通过BatchTool工具导入导出数据_云原生数据库 PolarDB-阿里云帮助中心
其他注意点
对于排序的列，需要有合适的索引，例如，如果按照(gmt_create, id)进行排序，则应该有(gmt_create, id)上的组合索引。如果有其他的WHERE条件，则应该一并考虑索引信息。例如，对于查询：
SELECT * FROM t1 WHERE c1 = xxx ORDER BY gmt_create, id LIMIT 1000;
通常情况下，需要(c1, gmt_create, id)上的组合索引。
- 对于JAVA应用，需要设置合适的JDBC参数，避免超时等奇怪的错误，包括：
- 设置netTimeoutForStreamingResults=0
- 设置socketTimeout，单位是毫秒
- Statement对象setFetchSize(Integer.MIN_VALUE)，避免爆内存
- 保持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();
}
}
}
}
作者：梦实
原文链接：https://developer.aliyun.com/article/1391959?utm_content=g_1000386657
本文为阿里云原创内容，未经允许不得转载。