浅谈Mysql大数据分页查询解决方案

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

1.简介

之前，面阿里的时候，有个面试官问我有没有使用过分页查询，我说有，他说分页查询是有问题的，怎么解决；后来这个问题我没有回答出来；本着学习的态度，今天来解决一下这个问题；

2.分页插件使用

1.pom文件

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>4.1.6</version>
        </dependency>

2.创建分页配置器

@Configuration
public class PageHelperConfig {
    @Bean
    public PageHelper pageHelper() {
        PageHelper pageHelper = new PageHelper();
        Properties p = new Properties();
        p.setProperty("offsetAsPageNum", "true");
        p.setProperty("rowBoundsWithCount", "true");
        p.setProperty("reasonable", "true");
        pageHelper.setProperties(p);
        return pageHelper;
    }
}

测试代码：

    @Test
    void test() {
        PageHelper.startPage(400000,10,"id desc");
        List<UploadData> users = userMapper.queryAll();
        System.out.println(users.size());
        System.out.println(users);
        for (UploadData uploadData: users) {
            System.out.println(uploadData);
        }
    }

4.重写sql分析

debug 后可以查看它是通过重写sql来实现分页功能；重写后的sql语句为"SELECT * FROM amj_devinfo order by id desc limit ?, ?";
limit a, b；// 跳过前a条数据，取b条数据；
所以，其实现在问题就是回到了，执行这条sql语句所需要花费多少的问题了；

3.sql测试与分析

select * from amj_devinfo order by id limit 2000, 20;     // 0.027s
select * from amj_devinfo order by id limit 20000, 20;    // 0.035s
select * from amj_devinfo order by id limit 200000, 20;   // 0.136s
select * from amj_devinfo order by id limit 2000000, 20;  // 1.484s

select * from amj_devinfo order by devaddress limit 2000000, 20; // 7.356 全表扫描 + filesort；

结论：如果说，是小的数据量的话，使用该分页完全没问题；当数据量到达两百万的时候，执行时间就得为6.729s了，对于用户来说，这是不可接受的；

3.1 limit现象分析

使用explain对sql先来分析一波；感兴趣的同学可以看看我的另一篇文章 MySQL结合explain分析结果如下：

针对，select * from amj_devinfo order by id limit 2000, 20来说：

可以看到，使用的是基于索引树 + 回表的方法来获取数据的，顺序IO查询列数为：2000020；首先，根据阿里Java开发手册，type为index 就已经不可接受了；最低标准为range；而且，它是order by id 能够使用上主键索引，要是order by '其他列（无索引）如devaddress' 这个时候，就是全表扫描 + filesort，效率更慢；
备注：

select * from amj_devinfo order by id limit 2000000, 20;

这条语句是方案一：先通过id找到2000000，然后，剩下的20条再全表扫描；还是，方案二：通过id回表直接找到2000020条，然后，放弃前2000000条；理论上剩下20条进行全表扫描肯定是快很多的；但是，有点尴尬。Mysql选择的其实是方案二；

3.2 解决之道

很显然，现在已经是发现了问题所在，我们需要对其进行解决；我们对下面的sql语句来进行升级；

测试背景：
1.mysql 数据表中有5695594 (五百万)条数据，在devcho中数据相对离散。
2.表的设计如下：

有需要测试的同学，可以按照我表设计来模拟测试；

select * from amj_devinfo where devcho = "77" limit 20000, 10;

3.2.1 对devcho建立索引

很显然，通过sql来查询的话，对devcho建立索引的话，可以把全表扫描升级为基于索引列的扫描；能提升一个量级；
索引建立结果如下：

执行sql语句：

执行时间8.415s 这个时间是不可以接收的；

3.2.2 sql执行时间长分析

经过多次测试，发现时间都是很久，那么，就不会是Mysql 刷脏页，而且，数据库空闲，没有别的sql与其竞争磁盘IO 而且，通过MVCC查找数据也不存在锁相关问题；所以，问题肯定是出现在sql语句上；
那么，为什么会出现这个问题呢？ -- 答案是回表这条sql语句是怎么执行的呢？

先基于devcho的索引列，找到devcho='77'的这一行；
在通过devcho中存的主键id,然后，回表找所有的数据；找20010条数据；

这时候，问题就出现了，这个回表的过程是随机IO；这个随机IO效率是很低的；所以，undo log要把随机IO变成顺序IO。这里，就是最大的瓶颈所在；
扫描条数验证： Handler_read_next: 该选项表明在进行索引扫描时，按照索引从文件数据里取数据的次数；

回表是sql瓶颈验证:

查找主键id,不需要回表，发现0.01s就可以搞定；证明了sql导致的回表就是瓶颈所在；

3.2.3 解决之道

我们刚刚发现，因为limit比较笨。select * from amj_devinfo where devcho = "77" limit 20000, 10;需要回表20010次；但是，我们只需要它回表10次啊。所以，我们可以先把符合条件的id找出来；再根据id使用inner join 去进行回表；
sql语句如下：

select * from amj_devinfo a INNER JOIN (select id from amj_devinfo where devcho = "77" limit 20000, 10) b on a.id = b.id;

查询时间：0.025s

这个时候，就可以达到我们的要求了；这个联结是会产生笛卡尔积的。检索出来行的数目是第一个表中的行数乘以第二个表中的行数，以前，感觉挺慢的，这也证明，如果没有文件排序或者临时表的话，效率其实还可以；

4 测试时走过的坑

在测试的时候，其实我犯了两个错，卡了自己好几个小时，证明测试都不对；特此记录一下，给想复现现象的同学提个醒；

插入百万条数据数据内容相同；
在执行sql时，格式没有对应上，导致索引失效select * from amj_devinfo where devcho = 77 limit 20000, 10; 77是字符，我输入为整型；

4.1 百万数据内容都一样

select * from amj_devinfo where devcho = "2212" limit 20000, 10; // 0.042s
select id from amj_devinfo where devcho = "2212" limit 20000, 10; // 0.026s

还是上面的语句，只是数据内容是一样的；为什么两者时间是一个级别？
为什么会产生这种现象呢？

因为数据都一样的devcho 索引其实是没有用的；成为链表了；
第一条语句，找出20010条语句就找到内容了，因为，都存在一起都在一个或者几个页表中，随机IO升级为顺序IO，是有回表，但是，顺序IO的回表也是很快的。所以，效率很高；即，第一条语句和第二条语句花的时间是差不多的；

4.2 写sql时，把"77"写成了77；

现象再现：

select id from amj_devinfo where devcho = 77 limit 20000, 10; // 查询时间2.064s
select * from amj_devinfo where devcho = 77 limit 20000, 10; // 查询时间3.716s

这里第一条语句因为字段比第二条语句中少；所以，放入sort_buffer中的数据是不同的；
问题回顾：我之前就在想，为什么我基于索引列查询id会这么慢？我当时没想到索引失效问题；后来，我是怎么发现这个问题的呢？因为，基于索引列查询的时候，Mysql要扫描的字段也就是20010条数据即可；而我查看Handler_read_next（此选项表明在进行索引扫描时，按照索引从数据文件里取数据的次数）时，

Handler_read_next 4274160

explain分析结果：

如果，扫描这么多行，需要这么多时间是可以理解的，那么，为什么需要扫描这么多行呢？我那时候，重新看了一下表的设计，发现原来devcho字段的类型是varchar；这个时候，就想到了索引失效这个问题；

4.2.1 为什么会索引失效？

既然，发现了类型不同导致索引失效，那么就分析一下，为什么会导致索引失效？这条sql又将如何执行？因为，他是基于索引列找的。但是，由于77 != '77'所以，这就导致了索引实现；但是，最终它还是找到了数据，这个时候，结合了扫描行数，我个人感觉应该是采用了全表扫描，然后，通过，强制类型转换,cpu进行判断，查询所得；
当改成 select id from amj_devinfo where devcho = "77" limit 20000, 10;就没有这个问题了；扫描的行数为20009行；所以，在写sql语句的过程中还是要注意啊；
字段为varchar 传入 int 会索引失效，那么，字段为bigint 传入 "String" 会失效吗？经过测试：不会失效；

所以，在Mybatis中，可以放心使用#{}占位符了；

4.3 一个有趣的现象

大扫描行数 VS 随机IO

select * from amj_devinfo  where devcho  = 77 limit 20000, 10; 查询时间 3.311s
select * from amj_devinfo  where devcho  = "77" limit 20000, 10; 查询时间 3.188s

第一个sql扫描的行数是500多万行；但是，由于每个行都需要读入内存中，使用的是顺序IO 第二个sql扫描的行数是20010行，但是，需要访问随机IO 20010次；其实，基本上也就把所有的页表都找了一次；
小总结：随机IO，查询次数都要避免；