在我们日常开发中,分页查询是必不可少的,可以说每干后端程序猿大部分时间都是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','zhaoliu@163.com','18800000000',0,'oss.file.com/images/zhaoliu.png');
set i=i+1;
end while;
END
查询过慢的原因
数据表和记录都已经准备好了,现在我们就需要来排查为什么分页查询页码越靠后查询速度越慢,我们先来看一个普通的分页查询:
select * from user order by id desc limit 100,10;
这是查询第十页的数据,我相信大部分的人在写分页查询的时候都是这么写的,其中100:偏移量,意思就是说从哪里之后就是我需要的数据据,10:表示需要查询多少条记录,这个就是mysql的分页查询语法,你能看出这条sql存在什么问题吗?乍一看好像没啥问题,真的是这样吗?我们来看几个例子。
1:偏移量=0
select * from user order by id desc limit 0,10;
查询时间
2.偏移量=1000
select * from user order by id desc limit 1000,10;
查询时间
3.偏移量=10000
select * from user order by id desc limit 10000,10;
查询结果
就算偏移量达到了10000,我们的查询速度还是很快的,这说明这条sql就没有任何问题了吗?既然这样,我就让你们看一下当偏移量达到200w的时候,会发生什么事情?
4.偏移量= 400w
select * from user order by id desc limit 4000000,10;
查询结果[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YeQGpug3-1584850193772)(
也就是说偏移量越大,查询的时候就越久,这是问什么呢?明明都是从查询10条记录,为什么偏移量越大,查询时间越久呢?
limit分页的原理
为什么会慢?我们不妨先猜测一下,像函数、扫描记录过多等等都会影响查询的速度,很显然这里我们并没有使用到函数,所以这会不会是扫描的记录过多呢?这个就和limit有关了,你们知道limit是如何实现分页的吗?我们使用wxplain关键字来分别打印一下偏移量=0、1000、10000、400w的查询详情。
1:偏移量=0
2.偏移量=1000
3.偏移量=10000
4.偏移量= 400w
我们先来解释一下这些字段分别是什么意思id:标识符select_type:查询的类型。table:结果表partitions:匹配的分区type:表的连接类型possible_keys:也许会使用的索引key:实际使用的索引key_len:索引字段的长度ref:列与索引的比较rows:扫描出的行数filtered:按表条件过滤的行百分比Extra:执行情况的描述和说明
我们对比一下上面的信息,会发现只有一个字段的值有着很大的区别,那就是rows:扫描的行数,当limit分页的偏移量越大的时候扫描的行数就越多,这就是为什么我们查询越靠后的数据越慢。
假如你现在要查询的偏移量为100w,那么limit会扫描1000010行,然后丢弃前100w行数据,留下最后10行,返回给我们,所以说我们只需要控制扫描的行数,查询的速度自然就快了,那如何控制扫描的行数呢?
1.最大id查询法
扫描意思呢?举个例子,我查询第一页的时候是limit 0,10 查询到的最后一条id是10,那么下一页的查询只需要查询id大于10的19条数据即可。
explain select * from user where id > 4000000 limit 10;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eeZMwK7w-1584847316195)(
])我们看到rows=1949780,这个表示可能扫描了这么多行,这个行数是因为扫描id>400w的记录,后面还有两百万,而这里表示可能扫描了1949780行,但是由于limit 10的存在,所以扫描了10行之后就停止扫描了,我们也可以对比一下使用这种方式和直接使用limit 4000000,10的效率,千差万别。但是这种比较局限,只能适用于自增组件,是哦那个uuid生成的主键这种方式不适用。
2.BETWEEN … AND
select * from user where id BETWEEN 4000000 and 4000010
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iD5e6qK3-1584847810565)(
这种方式也只能适用于自增主键,并且id没有断裂,否者不推荐这种方式,我们发现使用BETWEEN AND的时候查询出来11条记录,也就是说BETWEEN AND包含了两边的边间条件。使用的时候需要特别注意一下。
3.limit id
select * from user where id > (select id from user limit 4000000, 1) limit 10;
这种查询方式就是先扫描4000010行,但是只取出id,然后再查询id大于这个值的前10条,这样虽然也是扫描了400多w行记录,由于id是主键,拥有者主键索引,所以查询 查询一个id的limit速度会快很多,我们可以对比一下一开始的limit 4000000,10,效率相差了3倍多。
4.延迟关联(个人推荐)
什么叫延迟关联,他让mysql扫描尽可能少的记录,获取到需要访问的记录后再根据关联列回到远表查询需要的所有列,这样听起来是不是很拗口,我们用sql来实现一下。
select * from user INNER JOIN( select id from user limit 4000000,10 ) as a USING(id)
我们可以看到这种查看的方式和第三种的效率差不多,但是当字段比较多,类型的长度比较长的时候,这种还是比较有优势的。
5.分表查询
mysql推荐一张表的存储不要超过500w数据,查询400w不到1秒对于一般的查询来说已经可以了,如果还要更快的话,我建议使用分表存储,分表又分两种情况,水平分表于垂直分表。
水平分表
假如一张表的原始数据有1000w条数据,我可分三张表存储,一张表300的万,这样查询的时候压力就会小很多,并且效率也很高很多,那问题来了,如何这个水平水表如何实现呢?像可以借助mycat之类的中间件,阿里云也提供了数据库的分表技术,当然,你也可以自己手写分表,但是自己手写分表的时候需要注意id重复以及如何定义搭配当前id在那张表中,算法推荐使用hash值。
垂直分表
假如张彪的记录有100w,按正常来说查询速度应该不会太慢,但是由于这张表的字段超多,而且还有很多text类型的字段,这个时候我们可以将占用空间比较小的字段分在一张表,占用空间比较大的字段分在另一张表,两张表一一关联,这样,查询的时候就会快很多了。
冷热表
这里我还有一种分表思想,可以借鉴一下,那就是冷热表。
什么是冷热表?大家都用银行的app吧,你们查询账单的时候会发现只能查询近几个月的数据,之前的数据需要去柜台获取在查询历史账单中查看,他这里就是冷热表的设计思想。
我们新建两张一模一样的表,一张表存放近三个月的记录(时间随情况而定,不一定时三个月) a表,另一张表存放三个月之前的数据:b表,用户产生的新记录可以存放在a表中,可以在每天凌晨的时候定时扫描a表,只要记录已经在三个月之前了,我们就可以将记录迁移到b表中,对于用户来说,查询近三个月的数据时他们比较敏感的,三个月之前的饿数据他们查询的可能并不多,所以这样的设计完全是合理的。
索引
这一点相信大家都知道,添加索引可以提高查询效率,如何我们的分页查询牵扯到条件的话,我们可以给条件添加索引,数据库会维护一张对应的索引表,查询的时候会先查询索引表,根据索引表返回的记录直接查询记录表,这样也减少了扫描的行数,但是需要注意,只要发生一下几点,索引都有可能不会被触发,一定要注意。1.查询条件使用is not null。2.like语句,比如 keyword like ‘%笔记本’,索引失效,%不能再最前面。3。OR 前后条件中只要有一个没有添加索引,那么将扫描全表,索引失效。4.组合索引:使用组合索引的时候必要要带上第一个索引的字段,否则组合索引不生效。5. > 、< 、 <>。6. 没有单引号的字符串。7.等等
总结
总而言之,查询优化的重点就在于如何能扫描最少的记录,返回查询的结果,看上去容易,但是真正做起来的时候会发现是那么的不容易,对于写后端的程序猿来说,sql是家常菜,也是必不可少的一道菜,因为sql写的好不好直接决定着你程序的抗压能力强不强,这个时候你可能会说我可以使用缓存来降低数据库的访问,这只是治标不治本,只有写出漂亮的sql才能让程序立于不败之地。至于文章开头说的8秒是因为同事的那种表比我的这张表复杂得多,并且还加上了查询总记录的时间,我这里并没有给出count()查询时间,500w的数据,count()差不多都要两秒,所以sount(*),查询总数也是需要优化的,这个优化就比limit的优化简单多了,这里就不多做说明了。