目录
- 1.简介
- 2.分页插件使用
- 3.sql测试与分析
- 3.1 limit现象分析
- 3.2 解决之道
- 4 测试时走过的坑
- 4.1 百万数据内容都一样
- 4.2 写sql时，把”77″写成了77；
- 4.3 一个有趣的现象
- 总结
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大数据分页查询 内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！