为什么我建议你这样实现MySQL分页

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 为什么我建议你这样实现MySQL分页

前言

之前分享了关于MySQL分页实现方案的文章《》,有些读者觉得写得太浅显了,今天我们就继续探讨这个话题,当然由于能力有限,这篇文章也未必能够达到某些读者的预期,但我觉得只要有一部分哪怕只有几个读者读了我的文章有所收获,我就很满足了。当然如果有写得不好的地方,也请指正,我是很乐意跟大家探讨的。废话不多说了,今天我们主要从查询性能的角度来继续探讨MySQL分页这个话题。先来回顾下之前提到的MySQL分页的2种常见的方案:

第一种是基于limit的分页方案,如:

SELECT * FROM user ORDER BY id ASC LIMIT 100, 10;

第二种是基于where的分页方案,如:

SELECT * FROM user WHERE id 100 ORDER BY id ASC LIMIT 10;

这里的WHERE id 100中的100是上一次分页结果中最大的id,如果是第一页,那么可以直接去掉where子句,如:

SELECT * FROM user ORDER BY id ASC LIMIT 10;

为了使得查询性能对比效果更加明显,本文使用的测试表的总记录数比较多,达到百万级别。

mysql select count() from user; +———-+ | count() | +———-+ |  1521920 | +———-+ 1 row in set mysql select min(id) from user; +———+ | min(id) | +———+ |       1 | +———+ 1 row in set mysql select max(id) from user; +———+ | max(id) | +———+ | 1521920 | +———+ 1 row in set mysql select * from user limit 10; +—-+———+ | id | name    | +—-+———+ |  1 | user_-4 | |  2 | user_-3 | |  3 | user_-2 | |  4 | user_-1 | |  5 | user_0  | |  6 | user_1  | |  7 | user_2  | |  8 | user_3  | |  9 | user_4  | | 10 | user_5  | +—-+———+ 10 rows in set

此外需要说明的是,不同的MySQL版本的实验结果可能不同,本文所做的实验的MySQL版本如下:

mysql select version(); +————+ | version()  | +————+ | 5.7.18-log | +————+ 1 row in set

2种分页方案的执行计划对比

我们先用explain看下2种分页方案的执行计划是怎样的:

mysql explain select * from user order by id asc limit 400000,10; +—-+————-+——-+————+——-+—————+———+———+——+——–+———-+——-+ | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra | +—-+————-+——-+————+——-+—————+———+———+——+——–+———-+——-+ |  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 8       | NULL | 400010 |      100 | NULL  | +—-+————-+——-+————+——-+—————+———+———+——+——–+———-+——-+ 1 row in set mysql explain select * from user where id 400000 order by id asc limit 10; +—-+————-+——-+————+——-+—————+———+———+——+——–+———-+————-+ | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       | +—-+————-+——-+————+——-+—————+———+———+——+——–+———-+————-+ |  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 732288 |      100 | Using where | +—-+————-+——-+————+——-+—————+———+———+——+——–+———-+————-+ 1 row in set

首先看下key字段,两种方案都是PRIMARY,说明都走了主键索引。再看rows字段,第一种方案的值是400010,第二种方案的值是732288,第一种方案预估需要扫描的行数比第二种方案预估需要扫描的行数少,由于这个数是预估的,不代表实际的扫描行数,所以只能作为参考。从以上结果看来,似乎基于limit的分页方案要优于基于where的分页方案。那究竟是不是这样子呢?我们做个实验就知道了。

2种分页方案的查询性能对比

为了保证试实验的公平性,我们分别查询排在比较靠前的数据、排在比较靠后的数据、以及排在比较中间的数据,以此来对比2种分页方案的查询性能。

同时,我们还要确保MySQL没有开启查询缓存,否则对于同一个SQL的多次查询有可能会命中缓存,这样一来实验就没有意义了。要确认MySQL有没有开启查询缓存,只需要查询下query_cache_type参数就行了:

mysql SHOW GLOBAL VARIABLES WHERE variable_name = ‘query_cache_type’; +——————+——-+ | Variable_name    | Value | +——————+——-+ | query_cache_type | OFF   | +——————+——-+ 1 row in set

可以看到,MySQL查询缓存是关闭的,所以我们可以放心的开始试验了。

先来看下测试代码:

   @Test    public void testMysqlPage() throws Exception{        Connection connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8”, “root”, “root”);        PreparedStatement limitStmt = connection.prepareStatement(“SELECT * FROM user ORDER BY id ASC LIMIT ?,?”);        PreparedStatement whereStmt = connection.prepareStatement(“SELECT * FROM user  WHERE id ? ORDER BY id ASC LIMIT ?”);        long limit=10;        int queryTimes=40;        long offset=0;        long time;        time = calculateQueryTime(offset, limit, queryTimes, limitStmt);        System.out.println(String.format(“使用limit的分页平均查询时间为-s}ms,offset-s},limit-s}”,new Object[]{time,offset,limit}));        time=calculateQueryTime(offset,limit,queryTimes,whereStmt);        System.out.println(String.format(“使用where的分页平均查询时间为-s}ms,offset-s},limit-s}”,new Object[]{time,offset,limit}));        offset=400000;        time = calculateQueryTime(offset, limit, queryTimes, limitStmt);        System.out.println(String.format(“使用limit的分页平均查询时间为-s}ms,offset-s},limit-s}”,new Object[]{time,offset,limit}));        time=calculateQueryTime(offset,limit,queryTimes,whereStmt);        System.out.println(String.format(“使用where的分页平均查询时间为-s}ms,offset-s},limit-s}”,new Object[]{time,offset,limit}));        offset=1000000;        time = calculateQueryTime(offset, limit, queryTimes, limitStmt);        System.out.println(String.format(“使用limit的分页平均查询时间为-s}ms,offset-s},limit-s}”,new Object[]{time,offset,limit}));        time=calculateQueryTime(offset,limit,queryTimes,whereStmt);        System.out.println(String.format(“使用where的分页平均查询时间为-s}ms,offset-s},limit-s}”,new Object[]{time,offset,limit}));    }    private static long calculateQueryTime(long offset,long limit,int queryTimes,PreparedStatement preparedStatement) throws Exception{        preparedStatement.setLong(1,offset);        preparedStatement.setLong(2,limit);        long start=System.currentTimeMillis();        for(int i=0;i20;i++){            preparedStatement.executeQuery();        }        return System.currentTimeMillis()-start;    }

代码比较简单,就是使用2种分页方案分别查询offset为0,offset为400000,offset为1000000的分页数据,每个查询都重复进行40次,最后取平均数作为该次查询的平均查询时间。由于只是试验目的,所以代码中省略了关闭资源以及异常捕获等逻辑。实验结果如下:

为什么我建议你这样实现MySQL分页

实验结果有点出乎意料,当offset为0时,2种分页方案的平均查询时间相差无几。当offset为400000的时候,基于limit的分页方案的查询时间是基于where的分页方案的查询时间的100倍左右。当offset为1000000的时候,基于limit的分页方案的查询时间是基于where的分页方案的查询时间的200倍左右。这结果与上面的执行计划对比结果大相径庭,因此也说明了执行计划并不能完完全全地反映SQL语句的执行过程。

总结

看了以上试验结果,我们来猜测一下(当然只是猜测)MySQL对于以上两种方案的执行过程是怎样的。首先来看基于limit的分页方案的实验结果。我们可以看到,随着offset的增大,平均查询时间呈线性增长了,所以可以猜测,MySQL对于这种分页是这样处理的:先查询出前(offset+pageSize)行记录,再排序,然后取出后pageSize条记录,因此需要扫描的行数会随着offset的增大而增加。

再来看基于where的分页方案的实验结果。我们可以看到,随着offset的增大,平均查询时间并没有呈现明显的线性增长,3个不同量级的offset的平均查询时间都是相同量级的,所以我们可以猜测(也仅仅是猜测),MySQL对于这种分页查询是先走索引查出offset所在的记录行,再利用B+tree索引的特点,通过遍历链表查询出offset+1到offset+pageSize的记录行,因此这种查询方案的查询速度取决于MySQL定位到第offset行的时间,理论上跟offset的关系不是呈线性关系的,因此随着offset的增大平均查询时间并没有明显的增长。

因此,如果表记录数比较多,不建议使用基于limit的分页方案,而要使用基于where的分页方案。不过,也不是任何时候都可以使用基于where的分页方案的,其适用性在这篇文章《》讲过了,不再赘述。

既然都看到这里了,点个赞再走呗。

为什么我建议你这样实现MySQL分页
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 为什么我建议你这样实现MySQL分页


 上一篇
再谈如何优雅地使用Redis之位图操作 再谈如何优雅地使用Redis之位图操作
前言 在之前的文章《》里为大家介绍了Redis位图操作常见的应用场景,今天继续聊聊Redis位图的其他应用。 首先我们还是从之前的例子入手。在之前的文章中,我们用Redis位图存储了每个用户注册后每天的登录情况,具体的存储方案是以用户id为
2021-04-06
下一篇 
什么是REST架构? 什么是REST架构?
作者:行者 链接:www.cnblogs.com/EasyLive2006 知音专栏 公众号注:架构设计相关文章点击阅读原文直达 REST架构风格是全新的针对Web应用的开发风格,是当今世界最成功的互联网超媒体分布式系统架构,它使
2021-04-06