分布式数据库的分页方案

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: 本篇文章介绍了在 DRDS 上做时间序数据分页展示的一种方法,初看简单,实则细节较多,需要一定的关系型数据库基础,也是我们用户在实际使用产品过程中碰到的问题之一,这里分享给大家。

image.png

本篇文章介绍了在 DRDS 上做时间序数据分页展示的一种方法,初看简单,实则细节较多,需要一定的关系型数据库基础,也是我们用户在实际使用产品过程中碰到的问题之一,这里分享给大家。

来实现一个分页吧
日常业务需求中,按时间序对数据进行分页展示是一个非常普遍的需求,单机关系型数据库有比较标准化的解决方案,但是这个需求在分布式数据库中实现,粗看比较简单,实际上却相当复杂,如果要做好,需要有比较扎实的关系型数据库认知。

这个问题分为两个部分:

  1. 功能性要求:

a.数据需按照时间顺序进行返回

b.所有数据均需要被遍历到,不能有遗漏

c.每页返回100条数据

d.同一个时间精度内(例如同一个毫秒内),会有多条数据同时被插入

  1. 性能要求:

a.性能不能随页码的增加而衰减,需要高效并且恒定

b.性能不能随着数据量的增加而衰减,例如10W条数据的时候很高效,100亿条数据的时候也很高效

单机数据库的做法
对于单机数据库而言,以MySQL 为例,功能部分,直接按照自增主键从小到大排列即可,因为自增主键的大小能够区分出数据生成的前后关系,所以功能上不存在问题。

性能部分优化,在 SQL带其他过滤条件的情况下,可以将扁平的带 LIMIT m,n 语句优化成嵌套子查询以便让优化器做索引覆盖,避免在磁盘上遍历数据,SQL 如下所示:

SELECT *
        FROM table a JOIN(
        SELECT pk FROMtable
        WHERE some_column= ?
        ORDER BY pk LIMIT m, n) b ON a.pk= b.pk

**在分布式数据库中实现的难点
**
对于单机数据库来说,因为存在时间序的自增主键,这个需求变得比较简单,但是对于分布式数据库而言,这个问题就变得比较复杂,主要因为主键大小和数据生成时间并没有本质关联,即使是分布式强一致数据库,保证严格时间序代价也很高,这个导致只能通过类似 GMT_CREATE 这种时间字段进行排序分页,但是 GMT_CREATE 可能重复,或者存在大量重复,这个导致分页按时间排序处理变得更加复杂。

另外性能层面,我们如同MySQL 不能使用扁平的带LIMIT m,n语句进行分页处理,但是同时也不能优化成带子查询的分页语句,因为数据分片的原因,需要将 LIMIT m,n 优化成 LIMIT 0,m+n ,分页挪到非常大的时候,需要返回到 DRDS 大量数据,再 skip 掉不必要的数据,即使做了倒序优化,一张拆分表性能最差的一页数据查询需要返回表中一半的数据才能满足需求,这个在拆分表有10亿或者100亿数据的时候,很难满足性能需求。

DRDS的方案
对这个问题仔细分析后,我们提出了一个方案。为了避免返回大量的中间结果数据,我们希望不指定 LIMIT 的 OFFSET,而是用上一页的最大值直接跳转到下一页的起始处,因此业务做一定的妥协,功能上增加如下约束:

  1. 提供下一页、上一页、首页、尾页功能
  2. 可以在当前页相邻的几页进行跳转(例如前后10页)
  3. 不允许做任意页的跳转

对于单机数据库,我们可以使用如下的 SQL 实现上述的需求:

SELECT *
        FROM table
        WHERE pk> 上一页pk最大值 ORDRE BY pk LIMIT n

分布式数据库:

  1. 增加 GMT_CREATE 字段,其默认值为CURRENT_TIMESTAMP
  2. 我们仍然按照 GMT_CREATE 时间字段进行第一个维度排序,但是 GMT_CREATE 有重复,我们希望有一个固定顺序,所以再以主键为第二个维度排序,所以排序部分为 ORDER BY gmt_create,pk 。
  3. 我们记住每一页 GMT_CREATE 和 PK 的最大值作为下一页数据的起始值,但是前面提到过 GMT_CREATE 有数据重复,如果通过简单AND条件拼接,会导致漏数据,所以我们将条件写为:
gmt_create>= ?
        AND(gmt_create> ?or pk> ?)

从查询逻辑上规避掉GMT_CREATE 重复带来的可能漏数据的状况,分页 SQL 变成了这个:

SELECT *
        FROM page_test
        WHERE gmt_create>= ?
        AND(gmt_create> ?
        OR pk> ?)
        ORDER BY gmt_create,
        pk LIMIT n

当然在MySQL 5.7上,我们还可以直接做二元组的比较(5.7之前的版本,多元组的比较 MySQL 无法利用到组合索引):

SELECT *
        FROM page_test
        WHERE(gmt_create, pk)> (?, ?)ORDER BY gmt_create, pk LIMIT n

结果验证和总结
如下图page_test3表,数据量为36亿多条,表结构如图所示,总共拆了96张表,4个RDS存储数据。
image.png

image.png

其中数据起始值如下:
image.png

如果我们挑选数据集中间的值,从下图看性能也很好,所以只要按照这套方案做分布式数据库分页或者全量扫描数据,性能将不会劣化,可以严格按照时间序排列,并且不会扫漏已经存在于数据库中的数据。

image.png

代码

package com.taobao.tddl.sample;

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

import com.alibaba.druid.pool.DruidDataSource;

public class PageSample {

    public static void main(String[] args) throws Exception {

        DruidDataSource ds = new DruidDataSource();
        ds.setUrl(
                "jdbc:mysql://drdsxxxx.drds.aliyuncs.com:3306/dbname?characterEncoding=utf8&rewriteBatchedStatements=true&clobberStreamingResults=true&allowMultiQueries=true");
        ds.setUsername("user");
        ds.setPassword("password");
        ds.init();
        int index = 0;
        boolean first = true;
        Object maxGmtCreate = null;
        long maxId = -1;
        while (true) {
            Connection conn = null;
            try {
                conn = ds.getConnection();
                PreparedStatement ps = null;
                if (first) {
                    ps = conn.prepareStatement("SELECT * FROM page_test  order by gmt_create,id limit 99");

                    first = false;
                } else {
                    ps = conn.prepareStatement(
                            "SELECT * FROM page_test 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开源数据库
本实验环境已内置PostgreSQL数据库以及PolarDB开源数据库:PolarDB PostgreSQL版和PolarDB分布式版,支持一键拉起使用,方便各位开发者学习使用。
相关文章
|
3月前
|
SQL 关系型数据库 MySQL
乐观锁在分布式数据库中如何与事务隔离级别结合使用
乐观锁在分布式数据库中如何与事务隔离级别结合使用
|
16天前
|
SQL 关系型数据库 MySQL
乐观锁在分布式数据库中如何与事务隔离级别结合使用
乐观锁在分布式数据库中如何与事务隔离级别结合使用
|
2月前
|
消息中间件 canal 缓存
项目实战:一步步实现高效缓存与数据库的数据一致性方案
Hello,大家好!我是热爱分享技术的小米。今天探讨在个人项目中如何保证数据一致性,尤其是在缓存与数据库同步时面临的挑战。文中介绍了常见的CacheAside模式,以及结合消息队列和请求串行化的方法,确保数据一致性。通过不同方案的分析,希望能给大家带来启发。如果你对这些技术感兴趣,欢迎关注我的微信公众号“软件求生”,获取更多技术干货!
121 6
项目实战:一步步实现高效缓存与数据库的数据一致性方案
|
2月前
|
canal 缓存 NoSQL
Redis缓存与数据库如何保证一致性?同步删除+延时双删+异步监听+多重保障方案
根据对一致性的要求程度,提出多种解决方案:同步删除、同步删除+可靠消息、延时双删、异步监听+可靠消息、多重保障方案
Redis缓存与数据库如何保证一致性?同步删除+延时双删+异步监听+多重保障方案
|
3月前
|
存储 SQL 分布式数据库
OceanBase 入门:分布式数据库的基础概念
【8月更文第31天】在当今的大数据时代,随着业务规模的不断扩大,传统的单机数据库已经难以满足高并发、大数据量的应用需求。分布式数据库应运而生,成为解决这一问题的有效方案之一。本文将介绍一款由阿里巴巴集团自主研发的分布式数据库——OceanBase,并通过一些基础概念和实际代码示例来帮助读者理解其工作原理。
274 0
|
1天前
|
缓存 关系型数据库 MySQL
高并发架构系列:数据库主从同步的 3 种方案
本文详解高并发场景下数据库主从同步的三种解决方案:数据主从同步、数据库半同步复制、数据库中间件同步和缓存记录写key同步,旨在帮助解决数据一致性问题。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
高并发架构系列:数据库主从同步的 3 种方案
|
5天前
|
NoSQL 算法 关系型数据库
分布式 ID 详解 ( 5大分布式 ID 生成方案 )
本文详解分布式全局唯一ID及其5种实现方案,关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
分布式 ID 详解 ( 5大分布式 ID 生成方案 )
|
13天前
|
存储 缓存 NoSQL
分布式架构下 Session 共享的方案
【10月更文挑战第15天】在实际应用中,需要根据具体的业务需求、系统架构和性能要求等因素,选择合适的 Session 共享方案。同时,还需要不断地进行优化和调整,以确保系统的稳定性和可靠性。
|
2月前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
311 15
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
26天前
|
SQL 关系型数据库 分布式数据库
Citus 简介,将 Postgres 转换为分布式数据库
【10月更文挑战第4天】Citus 简介,将 Postgres 转换为分布式数据库
72 4