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

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，查询次数都要避免；

总结

本文，主要是模拟了分页查询中，往后数据查询较慢的现象，以及分析了速度较慢的原因；limit导致随机回表数增多。并提供了解决方法，先找到符合条件的id；然后，根据id做内联查询，减少随机io的次数；并且，总结了一下自己出现的问题以及原因；如果，有一些个人见解不一定正确的话，希望大家多多指正；

到此这篇关于浅谈mysql大数据分页查询解决方案的文章就介绍到这了,更多相关mysql大数据分页查询 内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！