分库分表后-数据分页

前言

因公司与客户签约资源管理方式不一, 需研发统一管理系统, 方便客户, 公司实时了解相关资源消耗情况. 现在已经进入数据压测阶段了;
整个系统在 DB 上压力有资源扣除表和资源操作记录表, 知道上线后数据量一定不小, 在数据分页就没有用插件来查询(用插件来查的话, 插件会查询总计录数, 在数据量庞大的情况下, 这是一个很糟糕的操作…), 而是采用自己组装子查询 SQL 来查询, 前端只展示”首页, 上一页, 下一页, 末页”的功能:

SELECT
  id, c_id, wo_id, s_id, res_type, res_total ...
FROM
  b_res_con_result
WHERE
  id
  IN
    (
      SELECT
        id
      FROM
        b_res_con_result
      WHERE
        c_id = ?
        ....
      ORDER BY
        gmt_create DESC
      LIMIT
        ?, ?
    );

在压测时候,资源操作记录表查询性能随着数据的增加而逐渐衰减, 这就是一个需要必须解决的问题, 跟领导说了下情况. 领导: “嗯~~~~, 这确实是大问题, 一定解决掉, 不能耽误上线啊…”. 我能说点什么, 表着”只要思想不滑坡, 办法总比困难多”的理念和领导说: “我们尽量解决”. 回到自己位置上, 认认真真的把DRDS的管档翻了一遍(用的是阿里云的 DRDS), 只粗略的找到Prepare 协议, 可以稍稍缓解下性能衰减的问题(可能还有其他解决方案, 自己没有找到…), 对于我们的问题来说, 还只是杯水车薪. 然后自己做主放了项目组其他同事半天假, 回家好好休息下…¹(为赶项目在年前提前一个月上, 加班加到…), 自己也出去散散步, 脑子休息下…
休息的时候无聊看下 WX(很尴尬…平时几乎不用…), 看到提示有很多公众号, 更新内容, 才想起以前关注过阿里巴巴数据库技术的公众号, 立马打开, 翻看与之相关的文章, 不负有心人, 看到了一篇解决我们问题的文章…很激动的打开, 慢慢的消化着里面的精华.

参考

分布式数据库的分页方案

单机数据库

在 SQL 带其他过滤条件的情况下，可以将扁平的带”LIMIT m, n”语句优化成嵌套子查询以便让优化器做索引覆盖，避免在磁盘上遍历数据:
- 看完下面的语句后, 一脸尴尬…,自己用的子查询竟然是针对单数据库…竟然还没出问题…不知是感谢阿里呢还是感谢自己分库分表用的键值呢…放下废话暂且不提.

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

DRDS 分库分表

看到里面说到分布式强一致数据库来保证严格时间序代价也很大, 之前自己也看到 DRDS 管档上说主键的生成方式有一种可以保证主键Simple Sequence（SIMPLE）, 但这个方案最大的缺点是单点…, 这就有点…
里面老师说到用GMT_CREATE字段, 结合主键可以解决我们的情况, 而且文主还提供了案例:

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

或者5.7以上才用二元组方式:
SELECT
	*
FROM
	page_test
WHERE
	(gmt_create, pk ) > （?, ?）
ORDER BY
	gmt_create, pk
LIMIT n

看完后, 结合自己的业务有相似之处. 赶紧回公司, 把相关语句改了下, 让测试部协助压测了下, 的确相对之前快了不是一个量级. 让测试部又协助跑业务压入数据, 用来第二天压测用.
第二天压测后, 出来的结果比当初想象的还要好, 非常感谢分享文章的老师.

引用:

当 PM, 有些权利该有还是要有的(当然, 也要感谢领导们的理解与认可). ↩

前言

参考

分布式数据库的分页方案

单机数据库

DRDS 分库分表

Footnotes