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数据

插入测试数据可以查看本篇博文:向mysql里面插入百万条测试数据

三、优化分页的方式

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页,就没有下一页了。如果还是想要有入口可以获取很久之前的数据,那么前台可以提供一个搜索入口,在搜索里面可以搜索所有的数据。



白醋水擦玻璃镜子,玻璃会非常干净   -->生活小窍门



声明:禁止任何非法用途使用,凡因违规使用而引起的任何法律纠纷,本站概不负责。

扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

精彩评论

全部回复12人评论7,777人参与