从零开发短视频电商 数据库大表分页问题

背景

从DB查询数据进行分页返回是很常见的需求，我们经常实现的方式如下：

-- 第一页
SELECT * FROM test ORDER BY id DESC LIMIT 0, 20;
-- 第N页
SELECT * FROM test ORDER BY id DESC LIMIT (N - 1) * 20, 20; 
-- 示例
SELECT * FROM test ORDER BY id DESC LIMIT 10000, 20;
1
2
3
4
5
6

LIMIT 10000 , 20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行。如果是LIMIT 1000000 , 100，需要扫描1000100 行。

我们准备100w数据试试水。

数据模型

CREATE TABLE `video` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) NOT NULL,
  `popular` int(11) DEFAULT NULL,
  `status` tinyint(2) unsigned NOT NULL COMMENT '状态：0 正常，1 删除',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4
1
2
3
4
5
6
7
8

数据初始化

-- 定义存储过程
DELIMITER ;;
CREATE PROCEDURE insertData()
BEGIN
        declare i int;
        /*set autocommit =0 把autocommit设置成0，把默认提交关闭*/ 
    	SET autocommit = 0; 
        set i = 1 ;
        WHILE (i < 1000000) DO
            INSERT INTO video(name,popular,status,create_time)
                VALUES(CONCAT('video_', i),
                       (SELECT FLOOR(RAND() * 10000)),
                       (SELECT FLOOR(RAND() * 2)),
                       now());
            set i = i + 1;
        END WHILE;
        commit;
END;;
DELIMITER;

-- 执行存储过程
CALL insertData();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

测试分页sql

SELECT * from video order by popular desc,id desc limit 1000,20 -- 160ms
SELECT * from video order by popular desc,id desc limit 10000,20 --310ms
SELECT * from video order by popular desc,id desc limit 900000,20 -- 2162ms
1
2
3

SQL变慢原因有两个：

• limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 900000,20，就会扫描900020行，而limit 1000,20，只扫描1002行。

• limit 900000,20 扫描更多的行数，也意味着回表更多的次数。

所以分页越深越慢。

注意截图中的数据，用于后面几种优化后，结果做一致性对比用。

解决方案

延迟关联或子查询

SELECT v.* from video v,
			(SELECT id from video order by popular desc,id desc limit 900000,20) tmp 
	where v.id = tmp.id
1
2
3

阿里开发规范中建议的方式，先在不是所有数据回表的情况下，快速拿到指定偏移量数据的主键ID列表，然后再进行回表查询，此时回表总量仅为20条，效率很高。

对比上图中和之前图中数据，数据一致。

标签记录法

Limit 深分页问题的本质原因就是：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉。这样就导致查询性能的下降。

其实我们可以采用标签记录法，就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。

例如：上一页的limit size 为：899980，20。其最后一条数据为：id = 455447，popular = 997。

则下一页的SQL可以修改为：

SELECT * from video WHERE popular <= 997 and id < 455447 order by popular desc,id desc limit 20 -- 86ms
1

LIMIT会在满足条件下停止查询，因此该方案的扫描总量会急剧减少，效率提升。

重点是：

• WHERE popular <= 997 and id < 455447
• limit 20
  

对比上图中和之前图中数据，数据一致。

同理可以记录上一页899980，20。其第一条数据为：id = 633604，popular = 997。

则下一页的SQL可以修改为：

-- 重点： WHERE popular >= 997 and id > 633604
SELECT * from video WHERE popular >= 997 and id > 633604 order by popular desc,id desc limit 20 -- 86ms
1
2

注意：查询一定要按照id排序，即查询要加上order by id，否则数据就会乱掉了。

总结

• 如果需求就是要求能深度跳页，选用延迟关联或子查询的方式。但是性能比标签记录法慢2-3倍。前端传递的参数一般是current，pageSize参数。
• 可以参考谷歌/百度搜索分页，每次只能跳转到当前页前后10页，也就是最多可以跳10页，要想达到深分页情况需要耐心。
• 标签记录法适合滑动加载下一页、只有上下页按钮等。前端传递的参数一般是maxId/minId（上一次查询的最小Id或者最大Id），业务属性1（例如：上次查询的最小或者最大createTime），业务属性2（例如：上次查询的最小或者最大popular）。