在我们日常开发中,分页查询是必不可少的,可以说每干后端程序猿大部分时间都是CURD,所以分页的查询也接触的不少,你们都是怎么实现的呢?前不久的一段时间,我的一个同事突然找我寻求帮助,他说他写的sql查询太慢了,问我能不能帮他优化一下那条查询语句,经过一段时间的优化,我们成功的将原来8秒一条的sql成功优化到了不到一秒,然而想到知识应该学会分享,所以我今天打算写出这个优化过程,可以让更多的程序猿可以看到。
为什么想到了优化分页查询
同事:hi boy ,让我们一起来探讨一下理想如何?握草,我一听就觉得事情不对,肯定没好事,走过去一看,同事对我说,他维护的这张日志表数据已经超过500w了,可能是测试的同事在做压力测试,导致了数据库的用户操作日志记录一下子就突破了几百万,现在,同事写的分页查询速度已经很慢很慢了,当查询的记录越靠后的时候,查询时间越久,果不其然,没过多久,我们就在禅道上看到了测试大哥提交的bug:查看1000页以后的日志返回速度极慢,这是被迫优化啊,好尴尬。
如何优化
问题是找到了,那是因为当数据库存放的记录过大的时候,查询也靠后的记录速度越慢,为什么查询越靠后的记录就越慢呢?
我这里主要介绍mysql的分页优化,sqlserver、Oracle可以参考思想,还是回归之前的那个问题,我们来看看为什么查询会慢?我们需要实现准备好数据表以及记录。
创建数据表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`u_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
`u_password` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
`u_mail` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
`u_phone` varchar(11) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机',
`u_sex` tinyint(1) DEFAULT NULL COMMENT '性别',
`u_headImg` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '头像',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4762599 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
添加数据
我在数据表中添加了100w张三、100w李四、100w王五、200赵六的用户,所以表中一共500w数据,添加数据的方式采用的存储过程。
BEGIN
declare i int; # declare语句是在复合语句中声明变量的指令
set i=1;
while i<=2000000 do
insert into `user`(u_name,u_password,u_mail,u_phone,u_sex,u_headImg) values('赵六','000000