浅谈Mysql大数据分页查询解决方案 - 主机乐

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

3.sql测试与分析

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

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

执行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语句如下：

如果，扫描这么多行，需要这么多时间是可以理解的，那么，为什么需要扫描这么多行呢？我那时候，重新看了一下表的设计，发现原来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" 会失效吗？经过测试：不会失效；