mysql优化大批量数据时的分页操作

一、mysql在大数据量下 使用传统的分页方式会带来什么问题？

主要就是会带来性能瓶颈问题、查询巨慢的问题。大数据量下如果继续使用xxx,xxx（比如说：limit 1000000,10）随着offset增大，查询的速度会越来越慢，因为mysql会把前面的数据都取出，然后才能找到对应位置，所以数据量越大 就导致这种查询方式越慢，这样肯定是不行的。

二、准备相关测试表

DROP` `TABLE` `IF EXISTS `testemployee`;``CREATE` `TABLE` ``testemployee` (`` ```id` ``int``(11) ``NOT` `NULL` `AUTO_INCREMENT,`` `````name``` ``varchar``(20) ``DEFAULT` `NULL``,`` ```dep_id` ``int``(11) ``DEFAULT` `NULL``,`` ```age` ``int``(11) ``DEFAULT` `NULL``,`` ```salary` ``decimal``(10,2) ``DEFAULT` `NULL``,`` ```cus_id` ``int``(11) ``DEFAULT` `NULL``,`` ``PRIMARY` `KEY` `(`id`)``) ENGINE=InnoDB AUTO_INCREMENT=1 ``DEFAULT` `CHARSET=utf8;` `-- 假设testemployee表中已经有100w数据

三、优化分页的方式

3.1、使用子查询优化

-- 子查询方式1 ``SELECT` `* ``FROM` `testemployee t ``INNER` `JOIN` `(``SELECT` `id ``FROM` `testemployee t LIMIT 990000,10) tmp ``ON` `t.id = tmp.id``-- 上面该条sql语句优化思路是:子查询里面只查询主键id列,这样会走主键索引,所以查询速度快。然后和外部的sql做一个内连接，将符合条件的数据取出。` `-- 子查询方式2``select` `* ``from` `testemployee ``where` `id >=(``SELECT` `id ``from` `testemployee limit 500000,1) limit 10``-- 上面该条sql语句优化思路是:子查询里面依然只查询主键id列,通过主键索引取出50w之后的那一条数据的主键id值。然后外部sql通过该id值,直接获取符合条件的10条数据。

总结：上面2条优化的sql思路都是 把耗时的操作放到子查询里面，但是子查询里面，是通过主键id索引快速找到对应的数据，然后外部sql根据子查询的结果直接获取符合条件的数据。

3.2、使用 id 限定优化方式

在程序中记录上一页最大的id号（php、java等各类语言），mysql中使用范围查询。

限制 是只能使用于明确知道id的情况，不过一般建立表的时候都会添加基本的id字段，这为分页查询带来很多便利。

示例sql如下：

-- 限定方式1``select` `* ``from` `testemployee ``where` `id >= 990000 limit 10;` `-- 限定方式2``select` `* ``from` `testemployee ``where` `id ``between` `1000000 ``and` `1000100 limit 100;

四、总结

优化分页的主要方式就是查询数据的时候想办法让mysql走索引（使用索引），活学活用 别有的场景你没办法用主键id索引就没办法优化了。使用任何索引都是可以的呀。

五、尾声

以上就是一些mysql大数据量下的优化分页的方式，仅供参考。另外，如果你的数据量真的很大很大的话，那前台的分页肯定要适当做一下调整的。不要让用户能翻到真实的最后一页，业务可以做一下限制，比如说前台分页只允许用户翻到前200页即可，也没有人闲的一直给你翻到最后一页的。 比如百度的搜索结果分页，最大只让你翻到74页，就没有下一页了。如果还是想要有入口可以获取很久之前的数据，那么前台可以提供一个搜索入口，在搜索里面可以搜索所有的数据。