为什么我建议你这样实现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;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的分页方案的,其适用性在这篇文章《如何优雅地实现分页查询》讲过了,不再赘述。

原文发布于微信公众号 - Java架构沉思录(code-thinker)

原文发表时间:2018-06-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java3y

数据库两大神器【索引和锁】

所以说,如果我们写select * from user where username = 'Java3y'这样没有进行任何优化的sql语句,默认会这样做:

2030
来自专栏Java进阶架构师

「mysql优化专题」你们要的多表查询优化来啦!请查收(4)

相信这内连接,左连接什么的大家都比较熟悉了,当然还有左外连接什么的,基本用不上我就不贴出来了。这图只是让大家回忆一下,各种连接查询。 然后要告诉大家的是,需要根...

922
来自专栏数据和云

实战演练:洞若观火--治堵之道在清源

堵塞往往是一件可怕的事情,交通堵塞让人心烦意乱,水道堵塞城市就会臭气冲天,言路堵塞则是非难辨。数据库出现会话堵塞,则很可能造成系统业务中断,这对于 DBA 来说...

1105
来自专栏Java爬坑系列

【MySQL疑难杂症】如何将树形结构存储在数据库中(方案二 Path Enumeration)

  今天来介绍把树形结构存入数据库的第二种方法——路径枚举法。   还是借用上一篇的栗子,为了方便大家查阅,我把图又原样搬过来了。 image.png   需...

2818
来自专栏数据和云

执行计划:Oracle的Profile特性与SQL执行计划的稳定性

编辑手记:在Oracle数据库中,版本变化带来的一大挑战就是SQL执行计划的稳定性,为此Oracle经历了从Outline到Profile的特性演进,本文带大家...

3529
来自专栏杨建荣的学习笔记

用Oracle的眼光来学习MySQL 5.7的sys(上)(r11笔记第24天)

sys的初衷 MySQL 5.7的sys自从推出以来,整体的反响似乎没有预期的那么高,而我看到这个sys库的时候,第一感觉是越发和Oracle像了,不是...

3769
来自专栏进击的程序猿

The Clean Architecture in PHP 读书笔记(十)

这是clean architecture的第十篇,也是具体案例的第二篇,本篇会通过使用laravel框架,来开发我们的应用。

1083
来自专栏Java3y

数据库面试题(开发者必看)

数据库常见面试题(开发者篇) ? ? 这里写图片描述 什么是存储过程?有哪些优缺点? 什么是存储过程?有哪些优缺点? 存储过程就像我们编程语言中的函数一样,封装...

6205
来自专栏Golang语言社区

二十种实战调优MySQL性能优化的经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事...

722
来自专栏java达人

mysql性能优化的几条重要建议

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事...

2266

扫码关注云+社区

领取腾讯云代金券