MYSQL优化之大量数据的limit分页优化

   在mysql中&＃xff0c;如果我们遇到要查询分页列表的数据&＃xff0c;我们正常的sql语句是这样写的&＃xff08;主键索引为user_id&＃xff09;&＃xff1a;

select * from user where 各种条件 limit 0,10

这句sql乍一看非常直接明了&＃xff0c;没啥毛病&＃xff0c;但是当我们的数据量达到了几百万条时&＃xff0c;我们要查几万条数据之后的分页&＃xff0c;那要怎么处理呢&＃xff1f;简单啊&＃xff0c;这样写就好了&＃xff1a;

select * from user where 各种条件 limit 50000,10

然而&＃xff0c;事情并没有想象中的简单&＃xff0c;当我们以这样的方式去查询时&＃xff0c;mysql会如何处理呢&＃xff1f;

首先&＃xff0c;它会根据我们的主键索引去把所有的50010拿出来&＃xff0c;然后再通过主键ID回表去把所有的*里面的各种字段查出来&＃xff0c;最后截取最后的10条数据作为我们最终的返回值。这会出现一个很大的浪费&＃xff0c;我们其实并不需要前面的50000条数据&＃xff0c;更不需要这50000条数据回表查出来的各个字段&＃xff0c;这将严重浪费查询时间。此时该如何优化呢&＃xff1f;请看下面的代码&＃xff1a;

SELECT*
FROMUSER u1RIGHT JOIN ( SELECT user_id FROM USER WHERE各种条件 LIMIT 50000, 10 ) u2 ON u1.user_id &＃61; u2.user_id


这个查询咋一看好像多此一举&＃xff0c;为什么要将同一张表连表查出ID&＃xff0c;然后再进行查询其他字段的操作呢&＃xff1f;如果我们这样操作的话&＃xff0c;当我们子查询去根据主键索引把50010条数据都查出来后&＃xff0c;由于索引里面已经存放了主键的值了&＃xff0c;因此他就不需要回表再进行查询其他各种字段&＃xff0c;此时&＃xff0c;子查询limit结束后&＃xff0c;只剩下了10条数据&＃xff0c;再对这10条数据进行查询所有的字段&＃xff0c;那么我们就相当于省略了一次50000条的所有字段查询&＃xff0c;因此就能够极大的增加我们的查询效率。

根据此处的例子就不得不说一下limit的弊端了&＃xff0c;由于它是最后执行的&＃xff0c;所以当我们limit m,n时&＃xff0c;mysql会进行先查m&＃43;n条数据&＃xff0c;然后再对其截取最后n条数据&＃xff0c;因此当分页数量极大时&＃xff0c;他会大大降低查询的效率&＃xff0c;导致即使使用了索引也无法提升性能&＃xff0c;就是因为他的回表查询其他字段的机制导致了大量时间的花销。

这招大家学会了没&＃xff01;&＃xff01;&＃xff01;有收获点个赞呗