阅读 257

mysql单表亿级数据分页怎么优化?

把简单的事情放大了,它就不简单了

前言

有人说单表超千万数据就应该分库分表了,这么玩不合理啊。但是对于创新业务来讲,业务系统的设计不可能一上来就预估这么大的容量,成本和工期都不足矣完成系统的开发工作。我觉得对于创新型业务系统的设计,首先满足需求,其次考虑到万一业务井喷发展所要考虑到的临时解决方案,为系统升级预留时间。

谁都希望业务井喷,那么它来了!

01

具体时间点就不说了,开始做了一个新业务,见了一个表,该表累计数据条不超过100万,提供查询功能。后来业务量持续上涨,mysql 磁盘开始报警,查询超时报警。而且,客户需要实时查询该业务表的数据并下载。头大,临时改存储方案已经来不及了,不能耽误KPI。

02

先解决眼下问题,先扩充磁盘。停止双机房同步,减少不必要的报警。

图片

但是1000G 估计也扛不了多久,和业务同学讨论后,业务接受的范围T-7范围内的数据实时查询下载。按这个增长量,7天也是过亿的记录条数。但是7天的数据磁盘肯定是够用的,那就要先把历史数据离线存储。

这个也简单,几行代码的事儿。当然这样依靠完善的基建。

图片

03

容量的问题解决了,那么改对数据分页查询的进行优化。为了说明问题,去掉敏感的业务数据,数据表结构如下:

CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `a` char(32) DEFAULT '' COMMENT '',
  `b` varchar(64) DEFAULT NULL COMMENT '',
  `c` bigint(20) unsigned NOT NULL COMMENT '',
  `d` varchar(64) NOT NULL COMMENT '',
  `e` tinyint(4) DEFAULT NULL COMMENT '',
  `f` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `g` varchar(32) NOT NULL COMMENT '',
  `h` char(32) DEFAULT NULL COMMENT '',
  `i` varchar(64) DEFAULT NULL COMMENT '',
  `j` varchar(64) DEFAULT NULL COMMENT '',
  `k` datetime DEFAULT NULL COMMENT '',
  `l` int(11) DEFAULT NULL COMMENT '',
  `m` timestamp NULL DEFAULT NULL COMMENT '',
  `n` timestamp NULL DEFAULT NULL COMMENT ''
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_b` (`b`),
  KEY `IDX_c` (`c`,) USING BTREE
)
复制代码

当数据量少时,我们用下面的分页是没有问题的:

SELECT id,a,b… FROM t LIMIT n,m
复制代码

例如:

pagesize :每页显示条数。

pageno:页码

那么 m=pagesize;    n=(pageno-1)*pagesize.

MySQL的limit工作原理就是先读取前面n条记录,然后抛弃前n条,读后面m条想要的,所以n越大,偏移量越大,性能就越差。

修改sql,减少io的消耗

SELECT id,a,b… FROM t where id in(SELECT id FROM t LIMIT n,m)
复制代码

其实这样也避免不了扫描前n 条,但是时间已经节约了很多。

图片

上面是每页请求的RT,可见随着页数的增加,RT 逐渐上升。

图片

Qps 逐渐下降。

那么如果数据太多的话,最后一页超时的概率会非常大。

优化后

先卖个关子,先看看优化后的表现,这个接口的性能明显提升。如图所示:

图片

RT 平均在10ms 左右,因为返回做了数据处理,RT最终在15ms左右

图片

qps 也很平稳,应该可以再高一些,取决于客户的调用。

未完待续...........

关注公众号【小汪哥写代码】回复【优化】获取本文最新更新

文章分类
后端