【MySQL】最简单的深度分页问题优化方案

站长

2023年06月08日 19:12 · 阅读数 20

1、创建表并初始化数据

执行如下语句建表：

CREATE TABLE `test_big_data` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `add_time` bigint NOT NULL,
  `update_time` bigint DEFAULT NULL,
  `del` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建存储过程初始化数据

CREATE PROCEDURE test_insert_bigdata()
begin
	declare i int(11) default 1;
	while i < 10000000
DO 
INSERT INTO test_big_data(name, add_time, update_time, del) VALUES (CONCAT("itlgitlg",SUBSTRING(MD5(RAND()),1,30)),
(SELECT 1419955200- (FLOOR(1 + (RAND() * 12)) * 2678400) - (FLOOR(1 + (RAND() * 31)) * 86400) - FLOOR(1 + (RAND() * 86400))),
(SELECT 1419955200- (FLOOR(1 + (RAND() * 12)) * 2678400) - (FLOOR(1 + (RAND() * 31)) * 86400) - FLOOR(1 + (RAND() * 86400))), 0);
SET i = i + 1;
END WHILE ;
commit; 
end

执行存储过程，可多窗口执行

call test_insert_bigdata()

创建联合索引,导入数据后才创建，保证初始化数据速度

CREATE INDEX idx_name_update_time USING BTREE ON test.test_big_data (name,update_time);

2、查看当前数据情况

执行语句

// 查看数据库最大id记录
select max(id) from test_big_data

【MySQL】最简单的深度分页问题优化方案

3、深度分页场景

执行sql 语句：

select *  from test_big_data where name like 'itlgitlg%' limit 800000,10

该查询分页需要扫描800000 +10行数据，然后丢掉前面800000行记录，同时进行800000 +10次回表，总计耗时7.5秒； SQL变慢原因总结：

limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据;
limit 800000 +10 扫描更多的行数，也意味着回表更多（800000 +10）的次数。

【MySQL】最简单的深度分页问题优化方案

4、优化方案

4.1 采用覆盖索引优化

目前该表name、update_time创建了联合索引，如果业务上，所需的字段都在索引上，可以使用覆盖索引来优化SQL，减少数据库的回表操作；如下sql：

select name,update_time from test_big_data where name like 'itlgitlg%' limit 800000,10

sql耗时470ms左右，如下图所示：

【MySQL】最简单的深度分页问题优化方案

4.2 延迟关联法

优化思路：跟子查询的优化思路其实是一样的，把条件转移到主键索引树，然后减少回表。不同点是，延迟关联使用了inner join代替子查询。先通过二级索引查询主键，再通过主键关联，减少回表提升性能，,优化后SQL为：

select a.* from test_big_data a
inner join
(select id from test_big_data 
where name like 'itlgitlg%' limit 800000,10) t
on a.id = t.id

sql耗时为466ms，如下图所示：

【MySQL】最简单的深度分页问题优化方案 拓展:MySQL B+树索引： InnoDB存储引擎中，索引分主键索引（聚簇索引）和二级索引 主键索引：叶子节点存放的是整行数据 二级索引：叶子节点存放的是主键的值；回表：在InnoDB存储引擎下，二级索引查询到的索引列，如果需要查找所有列的数据，则需要到主键索引里面去取出数据。这个过程就称为回表。因为行的数据都是存在主键B+tree的叶子节点里面，二级索引的B+树叶子节点都是存放的(索引列,主键)。

select *  from test_big_data where name like 'itlgitlg%' limit 800000,10

4.3 标签记录法

limit 深度分页问题本质原因：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉。这样就导致查询性能的下降。标签记录法：就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。

就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到了。

则SQL可以修改为：

select * from test_big_data where name like 'itlgitlg%' and id > 800000 order by id asc limit 0,10

执行耗时10ms左右，如下图:

【MySQL】最简单的深度分页问题优化方案

优点：这种方式查询性能得到很大提升，从根本上上处理了深度分页造成的问题。缺点：

该方法要求按照id连续查询，或者其他连续自增字段查询（如更新时间），每次查询后将最大值返给前端；
下一次查询时，前端将最大值带到后端查询，实际中可能很多场景不支持。

4.4 团队沟通法

和产品经理进行积极有效的沟通，尽量避免这种用户查看深度分页数据的情况，前端控制当选择页面数如20万时，界面上返回一个提示；或者引导用户输入一些有效的条件进行筛选后查询。更详细的视频讲解链接www.bilibili.com/video/BV1KP…，欢迎在B站给个关注和三连的支持，持续分享技术经验。