周村网站建设,丹东市市政建设总公司,张家口全景网站建设,下载gs甘肃人社问题描述
mysql中#xff0c;使用limitoffset实现分页难免会遇到深度分页问题#xff0c;即页码数越大#xff0c;性能越差。
select * from student order by id limit 200000,10;如上语句#xff0c;其实我们希望查询第20000页的10条数据#xff0c;实际执行会发现耗时…问题描述
mysql中使用limitoffset实现分页难免会遇到深度分页问题即页码数越大性能越差。
select * from student order by id limit 200000,10;如上语句其实我们希望查询第20000页的10条数据实际执行会发现耗时比获取第1页的10条慢很多。
原因分析
参考https://blog.csdn.net/cschmin/article/details/123148092解释的limitoffset实现原理便可知道mysql引擎层其实是把offsetlimit条记录全部返回给server层了server层再过滤掉前offset条记录把最后10条记录返回给客户端。显而易见由于引擎层的“懒惰”给server层造成了巨大的压力以及数据传输带来的资源消耗。
优化方法
1子查询优化
select *
from student t1, (select id from student order by id limit 200000,10) t2
where t1.idt2.id;网上挺多文章说这种优化是借助了select只选择id一个列符合了覆盖索引规则所以快速跳过了前200000条记录亦或是说避免了回表操作两种说法都是扯蛋。这个子查询优化的本质是大大减少了引擎层返回给server的数据量而已。假如student表的列很少且很小例如只有id、name两个字段你再试试这个优化基本没有效果。所以这个子查询优化的应用场景是表的行很大时可以优化引擎层返给server层的数据量数据条数并没有减少。而且由于子查询的存在引擎层和server层的交互多了一次第一次是子查询返回给server层200010个id第二次返回给server层10条完整记录。
2不使用limitoffset
改用标记法来实现分页这也是企业级业务上比较常用的方法。标记法每次查询都携带着起始条件
select * from student where id200000 limit 10;其中200000就是调用者当前页的起始位置。
优点
直接规避了深度分页问题
缺点
需要调用者自己维护标记例如当前页起始于20结束于29那么调用者要自己算出下一页起始是30不太容易通过页码直接跳转了例如从第3页跳到第20000页
3使用ElasticSearch
考虑将mysql数据同步到ES然后借助ES来实现分页查询。不过ES也会遇到深度分页的问题 只是没有mysql来的那么早而已。 例如我司在做某个文件管理库时由于文件太多了即便做了分表数据量依然很大查询负担太大于是上层通过ES用于一些查询。
案例测试
---------------------------------------------------------------
-- 创建一个测试表
---------------------------------------------------------------
CREATE TABLE student (id int(11) NOT NULL AUTO_INCREMENT,name varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,age int DEFAULT NULL,feild_1 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_2 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_3 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_4 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_5 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_6 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_7 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_8 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_9 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_10 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_11 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_12 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_13 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_14 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_15 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_16 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_17 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_18 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_19 char(255) COLLATE utf8mb4_bin DEFAULT NULL,feild_20 char(255) COLLATE utf8mb4_bin DEFAULT NULL,PRIMARY KEY (id) USING BTREE,KEY idx_name (name)
) ENGINEInnoDB AUTO_INCREMENT208505 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_bin;---------------------------------------------------------------
-- 随机生成20万记录
---------------------------------------------------------------
DELIMITER //
CREATE PROCEDURE insert_student(IN n INT)
BEGINDECLARE i INT DEFAULT 0;WHILE i n DOINSERT INTO student (age, name) VALUES (FLOOR(RAND() * 100), LEFT(UUID(), 8));SET i i 1;END WHILE;
END //
DELIMITER ;CALL insert_student(200000);select count(*) from student;---------------------------------------------------------------
-- 深度分页耗时测试
---------------------------------------------------------------
select * from student limit 200000,10; -- 977msselect id,feild_1,feild_2,feild_3,feild_4,feild_5,feild_6,feild_7,feild_8,feild_9 from student order by id limit 200000,10 -- 475msselect id,feild_1 from student order by id limit 200000,10 -- 138msselect id from student limit 200000,10; -- 65msselect *
from student t1, (select id,feild_1,feild_2,feild_3,feild_4,feild_5,feild_6,feild_7,feild_8,feild_9 from student order by id limit 200000,10) t2
where t1.idt2.id; -- 443msselect *
from student t1, (select id from student order by id limit 200000,10) t2
where t1.idt2.id; -- 60ms
