mysql百万数据分页查询优化方案（MySQL单表亿级数据分页怎么优化？）

mysql百万数据分页查询优化方案

前言

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

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

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

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

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

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

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

1. CREATE TABLE `t` ( 
2.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', 
3.   `a` char(32) DEFAULT '' COMMENT '', 
4.   `b` varchar(64) DEFAULT NULL COMMENT '', 
5.   `c` bigint(20) unsigned NOT NULL COMMENT '', 
6.   `d` varchar(64) NOT NULL COMMENT '', 
7.   `e` tinyint(4) DEFAULT NULL COMMENT '', 
8.   `f` int(11) NOT NULL DEFAULT '0' COMMENT '', 
9.   `g` varchar(32) NOT NULL COMMENT '', 
10.   `h` char(32) DEFAULT NULL COMMENT '', 
11.   `i` varchar(64) DEFAULT NULL COMMENT '', 
12.   `j` varchar(64) DEFAULT NULL COMMENT '', 
13.   `k` datetime DEFAULT NULL COMMENT '', 
14.   `l` int(11) DEFAULT NULL COMMENT '', 
15.   `m` timestamp NULL DEFAULT NULL COMMENT '', 
16.   `n` timestamp NULL DEFAULT NULL COMMENT '' 
17.   PRIMARY KEY (`id`), 
18.   UNIQUE KEY `UK_b` (`b`), 
19.   KEY `IDX_c` (`c`,) USING BTREE 
20. ) 

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

1. 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的消耗

1. 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 也很平稳，应该可以再高一些，取决于客户的调用。

优化思路

全表扫描肯定不现实，这时我想到了LSM, Log Structured Merge Trees.这种数据结构，被用在许多产品的文件结构策略：HBase, Cassandra, LevelDB, SQLite,Kafka 等。是一种非常复杂的复合数据结构，它包含了 WAL(Write Ahead Log)、跳表(SkipList)和一个分层的有序表(SSTable，Sorted String Table)。

这里，没有必要实现一个LSM 树，只是参考了其稀疏索引的思想，能够准确定位数据。这样就简单了。步骤如下：

1.根据分析业务，构建一个 字段 a,b的联合索引。因为a,b 是数据的查询条件，且能分离出1/7的数据。

1. ALTER table  ADD INDEX index_a_b('a','b') 

2.因为这个表的数据 都是通过 insert ... on duplicate key update ... 来更新的，【这也是线上死锁分析的那篇文章留下的伏笔】，而且 id 是自增主键，所以，所有的数据都是按照入库时的顺序来的，且后面遇到冲突时修改也是update 的，所以主键id 是不会变的。

在redis 中设计 稀疏索引。

1. 在redis 中设计 稀疏索引。 
2. key = a+b+页面 
3. value = 这页的起始id 
4. 比如 以每页2条数据为例 
5. key1 = ab1 value =0; 
6. key2 = ab1 value =4; 
7. key3 = ab1 value =8; 
8. ..... 
9. 那么第一页： 
10. select * from t where id>0 and a='a' b='b'  limit 2; 
11. 第二页： 
12. select * from t where id>4 and a='a' b='b'   limit 2; 
13. 第三页： 
14. select * from t where id>8 and a='a' b='b'  limit 2; 
15. .... 

那么这样就能很快定位到每页的起始id，少了大量的扫描操作，同时使用了索引，虽然 ab 联合索引 在ab 值都是一样的时候 区分度不高，但是这样也保证了id的顺序，不用order by。因为主键索引的id 本来就是有序的。

稀疏索引的计算时机：

在一批数据入库完成后开始稀疏索引的计算。

计算方法：

第一页 ：id = 0

1. 第一页数据 
2. select * from t where id>0 and a='a' b='b'  limit 2; 

第二页：id计算方法;

1. select max(t.id) from (select * from t where id>0 and a='a' b='b'  limit 2) t;  

第三页：id计算方法;

1. select max(t.id) from (select * from t where id>【第二页id】 and a='a' b='b'  limit 2) t;  

..........

依次类推.....

然后写入redis ,更新也是同样的道理。

为什么不用覆盖索引呢?

有人肯定会说为什么不用覆盖索引呢，这样就不用回表了啊!

答案是不能;

假如我们返回的 字段 是 a,b ,c d,e,f,那么我们建一个 覆盖索引 x。x的B+树如下：

那如果这个时候 我改了id=5 的值a=4 改为a =1

那现在id 就是不是顺序的了!!!!!!

那用覆盖索引+order by id 呢?

数据量不大的话也是可以的，但是这又是何必呢。我们看看order by 的原理。

首先 MySQL 会为每个查询线程分配一块内存，叫做 sort_buffer，这块内存的作用就是用来排序的。这块内存有多大呢?由参数 **sort_buffer_size** 控制，可以通过如下命令来查看。

1. # 查看sort_buffer的大小 
2. show variables like 'sort_buffer_size'; 

这样有两个问题：

每次都是按照筛选条件全量排序

如果数据量太大内存不够会触发文件排序，比较慢。

所以还是老老实实用了刚刚的方案。效果也还不错，也是仅仅加了几行代码而已

这个临时方案也是平稳运行了1年多。(>‿◠)

原文链接：https://www.toutiao.com/i6974629421202424353/