PolarDB-X最佳实践系列(三):如何实现高效的分页查询

原创
2023/12/25 11:34
阅读数 27

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

我们有以下需求:

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、这时候我们可以按照分片对数据进行遍历。

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

  1. 使用HINT指定分片信息,例如查询p1分片:
    /*TDDL:node='MENGSHI1_P00000_GROUP'*/SELECT * FROM t1_iVir_00000 ORDER BY id LIMIT 1000
  2. 使用上文提到的分页查询的方法,对一个分片的数据进行遍历
  3. 外层套一个循环,对所有的分片的数据进行遍历

数据导出场景使用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)上的组合索引。

  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();
            }
        }
    }
}

作者:梦实

原文链接

本文为阿里云原创内容,未经允许不得转载。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部