水平分库的分页方案思考

​ 很多业务都有分页获取信息列表的需求,在数据量、读写负载都不大的情况下,不需要分库分表。但是在大型流量和负载量面前,是需要进行分库分表来缓解数据库的存储压力的。上次Jeri老师提到思考分表时如何处理分页查询的问题,就思考了几个解决方案,同时查阅了一些文章,目前还在完善中…

需求概述

​ 比如淘宝上面的商品,有很多排序的维度,比如价格、好评、销量等。但是数据库的表往往是有一个与业务字段无关的主键id作为记录的唯一标识,且默认从数据库中取出数据是按主键id排序的,但业务中的场景要求我们根据其它字段来排序。单表的话只需要在排序字段上建立索引,通过limit就可以完成分页查询需求。

1
SELECT * FROM product ORDER BY price offset 20 limit 10;

​ 大流量、数据量的项目,随着数据量增大,需要将数据库进行水平切分来达到扩容的目的。一般水平分表时,采用的partition key都是主键id。为了保证负载均衡和数据量均衡,可以采用id取模或者做一次hash再取模来分库,这也是必要常用的实现负载均衡的方式,我观察了一下,“联名卡”项目的分表就是直接对主键进行取模,不过有几个表主键不是简单递增的,可能直接取模就不是最好的均衡的手段,我觉得可以做一个随机性高的hash再取模。

​ 举个需求例子,从这个例子开始谈方案。比如商品列表查询,通过主键id取模分了3个表,但查询时有“价格从低到高”的需求,每页十条数据。问题就来了:解决水平分表,且分库key与排序key不同的分页查询需求。

全局数据内存排序(暴力法)

​ 比如要按照价格从低到高取第三页的数据,为保证数据准确性,需要考虑极端情况:价格从高到低的前三页数据都来自于其中一张分表。但正常情况是前三页数据可能在每个表的前三页里都取数据,所以需要每个表都取三页数据出来,在内存中进行类似归并的排序,排序后再进行单表类似的简单查询,假设每页10条数据,

  • 步骤

    1. 每个表都取出前三页的数据

      1
      SELECT * FROM product ORDER BY price offset 0 limit 30;
  1. 各个分表执行这条SQL,业务层将得到90条数据

  2. 业务代码对这90条数据进行排序,排序后再取offset 20 limit 10的数据

  • 优点

    • 数据准确,代码修改简单,易于理解和实现
  • 缺点

    • 每个分库都需要返回大量的数据,占据网络带宽严重
    • 很明显,上面的缺点在指定页码大分表数目多时,是非常致命的

Redis维护索引字段有序的List

​ 这个方案是我不确定能否实践的想法,这里选择用redis而不是关系型数据库是因为性能的考虑,不过我认为redis这块可能会存在可用性问题。

id price table_no
12 80 1
6 100 3
3 102 1
7 104 2
54 120 2
  • 步骤:
    1. 维护一个list,这里面有id和price、table_no的关联,且按照price排序
    2. 数据入库时会在这里同步一条数据,list保证price有序
    3. 查询时从这里取出所需要的id序列再依次去对应表查询需要的结果并组合返回。

业务折衷-不指定页查询:设置锚点

​ 很多时候业务上进行一下折衷可以降低很多开发难度(要看能不能怼过PM了)。我们可以发现,现在我们日常浏览很多的信息流产品,并没有指定页跳转,而是下滑刷新。首先这也是业务上的特点,信息流产品的用户主要功能为浏览,对于跳页的需求不高,所以这里去掉指定页跳转没有问题。这里就有一个不错的方案,可以解决第一个方案的传输量过大的问题,我称他为“设置锚点”。

  • 步骤

    1. 对于每个分表的查询语句为,初始时min变量为0,因为价格都大于0

      1
      SELECT * FROM product ORDER BY price where price > $min limit 10;
    2. 三个表一共取出30条数据,在内存中进行归并排序,再取出结果中的前10条,完成第一页查询。记录结果中的最后一条,作为下一页查询的初始锚点,即变量min。

    3. 下滑刷新,即获取下一页数据时,把min代入,继续执行第一步中的SQL,同样只接受30条数据,就完成了第二页的查询,之后无论多少“下一页”都是固定的数据量:分表数*单页数据量

  • 优点:查询性能有保证,不会根据分表数和页数的增加而大量占用带宽。

  • 缺点:需要业务折衷,商品、信息流适用,管理系统不一定适用。

业务性能兼顾-二次查询

​ 从网上的技术文章学习到了一个终极武器:二次查询法。在此自己梳理一下,假设每页需要5个数据,要查第19页的数据。感觉语言描述不是很清楚,之后做几张图出来。

  • 步骤

    1. 查询语句改写

      1
      2
      3
      4
      5
      # 单表查询时的语句
      SELECT * FROM product ORDER BY price offset 90 limit 5;
      # 改写后发送到分表的语句
      SELECT * FROM product ORDER BY price offset 30 limit 5;
      # 这个30 = 总偏移量offset/分表数
    2. 得到三页按照price递增的数据后,比较三页的第一个数据,找到最小的,即使这三页里面的最小数据,记为min

    3. 第二次查询,所有分表执行,max_n为第二步得到的n页数据中分别的最大数据。

      1
      SELECT * FROM product ORDER BY price WHERE price between $min and $max_n;

      对于min所在表之外的两张表,查询条件事实上是放宽了,会得到更多的数据。

    4. 在每个结果集中虚拟一个min记录,得到min对应记录全局的offset。假设min确实来自表1,则min在表1中的偏移量为30,第二次查询中,对于表2,min和表2第一次查询的最小项的在第二次查询中序号差为k,那么在表2中min的offset就是30-k1;同理,表3中min的offset为30-k2。所以min的全局offset为30+30-k1+30-k2 = 90-k1-k2

    5. 利用全局offset和步骤2得到的结果,为三页数据带上全局offset就可以排序。

  • 优点:实现巧妙;查询量不会激增。

  • 缺点:实现理解有一些难度;数据准确度上有折衷,不能很好的处理极端情况;有一定的需要进行两次数据库查询,性能差一丢丢。