β

Mysql百万数据分页优化

大熊的记录 137 阅读

大数据表中的分页

一般分页的SQL都是

select * from coupon_code order by code_id limit 100,20;

这种SQL在表中数据量小的时候没有任何问题,但是表中数据在百万级的时就会出现性能上的问题。查看靠后页数的时候就会非常慢。

select * from coupon_code order by code_id limit 1000000,20;

这条SQL运行了1.41秒。

使用explain分析sql语句Mysql用了全表扫描
14805625990846.png

可是code_id我是加了索引的啊,为什么不走索引呢?

于是又试验了下面这条SQL,结果让我震惊了456毫秒,快了将近3倍,这是为什么都是limit 1000000,20为什么结果差这么多,差异就在select 条件语句中,一个是使用了 * 返回所有字段,一个只返回code_id字段,难道select还和索引有关系?

select code_id from coupon_code limit 1000000,20;

14805626988327.jpg

在网上查了一些资料,应该是Innodb的覆盖索引导致的差异。

覆盖索引:如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index),也就是平时所说的不需要回表操作

如果select的字段是索引字段,Mysql就不用在去表中取数据了。所以速度很快。

简单SQL分页优化方案

可以使用子查询来优化下这个分页。

select * from coupon_code where code_id >= (select code_id from coupon_code limit 1100000,1) limit 20;

运行了450毫秒,性能提升了3倍。

这种优化方法的优点就是简单,缺点就是复杂的语句就无能为力了,比如条件语句where status=1。这样就无能为力了。

复杂SQL分页优化

复杂一些的语句使用2次查询来优化。

根据 expire>=1404057600 的条件来做分页,还是按照code_id来排序

select code_id from coupon_code where expire >=1404057600  order by code_id desc limit 800000,20;

查询出来这20条信息的code_id。

select * from user where code_id in(1,2,3,4);

in 语句中的coide_id就是第一条SQL查询出来的,在利用索引来查处这20条数据。

相关资料:

  1. mysql高效索引之覆盖索引
  2. MySQL索引背后的数据结构及算法原理
  3. MySQL索引原理及慢查询优化
  4. 德问网站中的问题回答
  5. mysql大数据量分页场景的查询优化
  6. 大数据分页方案
作者:大熊的记录
我的资料、笔记等等记录在此
原文地址:Mysql百万数据分页优化, 感谢原作者分享。

发表评论