vlambda博客
学习文章列表

在我们日常开发中，分页查询是必不可少的，可以说每干后端程序猿大部分时间都是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','[email protected]','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;

我们看到rows=1949780，这个表示可能扫描了这么多行，这个行数是因为扫描id>400w的记录，后面还有两百万，而这里表示可能扫描了1949780行，但是由于limit 10的存在，所以扫描了10行之后就停止扫描了，我们也可以对比一下使用这种方式和直接使用limit 4000000，10的效率，千差万别。但是这种比较局限，只能适用于自增组件，是哦那个uuid生成的主键这种方式不适用。

2.BETWEEN … AND

select * from user where id BETWEEN 4000000 and 4000010

这种方式也只能适用于自增主键，并且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的优化简单多了，这里就不多做说明了。