前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >为什么我建议你这样实现MySQL分页

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

作者头像
Bug开发工程师
发布2018-07-23 18:44:02
7530
发布2018-07-23 18:44:02
举报
文章被收录于专栏:码农沉思录

前言

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

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

代码语言:javascript
复制
SELECT * FROM `user` ORDER BY id ASC LIMIT 100, 10;

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

代码语言:javascript
复制
SELECT * FROM `user` WHERE id > 100 ORDER BY id ASC LIMIT 10;

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

代码语言:javascript
复制
SELECT * FROM `user` ORDER BY id ASC LIMIT 10;

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

代码语言:javascript
复制
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版本如下:

代码语言:javascript
复制
mysql> select version(); 
+------------+
| version()  |
+------------+
| 5.7.18-log |
+------------+
1 row in set

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

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

代码语言:javascript
复制
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参数就行了:

代码语言:javascript
复制
mysql> SHOW GLOBAL VARIABLES WHERE variable_name = 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set

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

先来看下测试代码:

代码语言:javascript
复制
   @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;i<20;i++){
            preparedStatement.executeQuery();
        }
        return System.currentTimeMillis()-start;
    }

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

实验结果有点出乎意料,当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的分页方案的,其适用性在这篇文章《如何优雅地实现分页查询》讲过了,不再赘述。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2018-06-26,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 码农沉思录 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档