最新要闻

广告

手机

iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?

iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?

警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案

家电

观天下!MySQL百万级数据大分页查询优化的实现

来源:博客园

前言:在数据库开发过程中我们经常会使用分页,核心技术是使用用limit start, count分页语句进行数据的读取。


(相关资料图)

一、MySQL分页起点越大查询速度越慢

直接用limit start, count分页语句,表示从第start条记录开始选择count条记录 :

select * from test limit start, count ;

当起始页较小时,查询没有性能问题,我们分别看下从10, 1000, 10000, 100000开始分页的执行时间(每页取20条)。

select * from test limit 10, 20 ;      //0.002秒select * from test limit 1000, 20 ;     //0.011秒select * from test limit 10000, 20 ;    //0.027秒select * from test limit 100000, 20 ;   //0.057秒

我们已经看出随着起始记录的增加,时间也随着增大, 这说明分页语句limit跟起始页码是有很大关系的,那么我们把起始记录改为100w看下:

select * from test limit 1000000, 20 ;  //0.682秒

我们惊讶的发现MySQL在数据量大的情况下分页起点越大查询速度越慢,300万条起的查询速度已经需要1.368秒钟。这是为什么呢?

因为limit 3000000,10的语法实际上是MySQL扫描到前3000020条数据,之后丢弃前面的3000000行,这个步骤其实是浪费掉的。

select * from test limit 3000000, 20 ;   //1.368秒

从中我们也能总结出两件事情:

(1)limit语句的查询时间与起始记录的位置成正比

(2) MySQL的limit语句是很方便,但是对记录很多的表并不适合直接使用。

二、 limit大分页问题的性能优化方法

(1)利用表的覆盖索引来加速分页查询

MySQL的查询完全命中索引的时候,称为覆盖索引,是非常快的。因为查询只需要在索引上进行查找之后可以直接返回,而不用再回表拿数据。

在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何。

select id from test limit 1000000, 20 ; //0.2秒

那么如果我们也要查询所有列,如何优化?

优化的关键是要做到让MySQL每次只扫描20条记录,我们可以使用limit n,这样性能就没有问题,因为MySQL只扫描n行。我们可以先通过子查询先获取起始记录的id,然后根据Id拿数据:

select * from test where id>=(select id from test  limit 1000000,1) limit 20;

(2延迟关联

和上述的子查询做法类似,我们可以使用JOIN,先在索引列上完成分页操作,然后再回表获取所需要的列。

select a.* from test a inner join (select id from test limit 1000000,20) b on a.id=b.id;

注意:如果不使用ORDER BY对主键或者索引字段进行排序,结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3)。

(3)用上次分页的最大id优化

优化思路是使用某种变量记录上一次数据的位置,下次分页时直接从这个变量的位置开始扫描,从而避免MySQL扫描大量的数据再抛弃的操作。

例如:先找到上次分页的最大ID,然后利用id上的索引来查询:

select * from test where id>1000000 limit 100 ;

说明:优化方法中所有的例子都是基于id为主键这个前提,不能使用索引字段,否则会出现返回数据记录数不对的情况。

关键词: