MYSQL分页limit速度太慢的优化方法
在mysql中limit可以实现快速分页，但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了，否则可能卡死你的服务器哦。
当一个表数据有几百万的数据的时候成了问题！
如*fromtablelimit0,10这个没有问题当limit200000,10的时候数据读取就很慢，可以按照一下方法解决
第一页会很快
PERCONAPERFORMANCECONFERENCE2009上，来自雅虎的几位工程师带来了一篇”EfficientPaginationUsingMySQL”的报告
limit10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里。
LIMIT451350,30扫描了45万多行，怪不得慢的都堵死了。
但是
limit30这样的语句仅仅扫描30行。
那么如果我们之前记录了最大ID，就可以在这里做文章
举个例子
日常分页SQL语句
selectid,name,contentfromusersorderbyidasclimit100000,20
扫描100020行
如果记录了上次的最大ID
selectid,name,contentfromuserswhereid>100073orderbyidasclimit20
扫描20行。
总数据有500万左右
以下例子当时候select*fromwl_tagindexwherebyname='f'orderbyidlimit300000,10执行时间是3.21s
优化后：
select*from( selectidfromwl_tagindex wherebyname='f'orderbyidlimit300000,10 )a leftjoinwl_tagindexbona.id=b.id
执行时间为0.11s速度明显提升
这里需要说明的是我这里用到的字段是byname,id需要把这两个字段做复合索引，否则的话效果提升不明显
总结
当一个数据库表过于庞大，LIMIToffset,length中的offset值过大，则SQL查询语句会非常缓慢，你需增加orderby，并且orderby字段需要建立索引。
如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。
如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。
SELECTid,title,contentFROMitemsWHEREidIN(SELECTidFROMitemsORDERBYidlimit900000,10);
如果limit语句的offset较大，你可以通过传递pk键值来减小offset=0，这个主键最好是int类型并且auto_increment
SELECT*FROMusersWHEREuid>456891ORDERBYuidLIMIT0,10;
这条语句，大意如下:
SELECT*FROMusersWHEREuid>= (SELECTuidFROMusersORDERBYuidlimit895682,1)limit0,10;
如果limit的offset值过大，用户也会翻页疲劳，你可以设置一个offset最大的，超过了可以另行处理，一般连续翻页过大，用户体验很差，则应该提供更优的用户体验给用户。
limit分页优化方法
1.子查询优化法
先找出第一条数据，然后大于等于这条数据的id就是要获取的数据
缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性
实验下
mysql>setprofi=1;
QueryOK,0rowsaffected(0.00sec)
mysql>selectcount(*)fromMember;
+———-+
|count(*)|
+———-+
| 169566|
+———-+
1rowinset(0.00sec)
mysql>pagergrep!~-
PAGERsetto‘grep!~-‘
mysql>select*fromMemberlimit10,100;
100rowsinset(0.00sec)
mysql>select*fromMemberwhereMemberID>=(selectMemberIDfromMemberlimit10,1)limit100;
100rowsinset(0.00sec)
mysql>select*fromMemberlimit1000,100;
100rowsinset(0.01sec)
mysql>select*fromMemberwhereMemberID>=(selectMemberIDfromMemberlimit1000,1)limit100;
100rowsinset(0.00sec)
mysql>select*fromMemberlimit100000,100;
100rowsinset(0.10sec)
mysql>select*fromMemberwhereMemberID>=(selectMemberIDfromMemberlimit100000,1)limit100;
100rowsinset(0.02sec)
mysql>nopager
PAGERsettostdout
mysql>showprofilesG
***************************1.row***************************
Query_ID:1
Duration:0.00003300
Query:selectcount(*)fromMember
***************************2.row***************************
Query_ID:2
Duration:0.00167000
Query:select*fromMemberlimit10,100
***************************3.row***************************
Query_ID:3
Duration:0.00112400
Query:select*fromMemberwhereMemberID>=(selectMemberIDfromMemberlimit10,1)limit100
***************************4.row***************************
Query_ID:4
Duration:0.00263200
Query:select*fromMemberlimit1000,100
***************************5.row***************************
Query_ID:5
Duration:0.00134000
Query:select*fromMemberwhereMemberID>=(selectMemberIDfromMemberlimit1000,1)limit100
***************************6.row***************************
Query_ID:6
Duration:0.09956700
Query:select*fromMemberlimit100000,100
***************************7.row***************************
Query_ID:7
Duration:0.02447700
Query:select*fromMemberwhereMemberID>=(selectMemberIDfromMemberlimit100000,1)limit100
从结果中可以得知，当偏移1000以上使用子查询法可以有效的提高性能。
2.倒排表优化法
倒排表法类似建立索引，用一张表来维护页数，然后通过高效的连接得到数据
缺点：只适合数据数固定的情况，数据不能删除，维护页表困难
3.反向查找优化法
当偏移超过一半记录数的时候，先用排序，这样偏移就反转了
缺点：orderby优化比较麻烦，要增加索引，索引影响数据的修改效率，并且要知道总记录数
，偏移大于数据的一半
引用
limit偏移算法：
正向查找：(当前页–1)*页长度
反向查找：总记录–当前页*页长度
做下实验，看看性能如何
总记录数：1,628,775
每页记录数：40
总页数：1,628,775/40=40720
中间页数：40720/2=20360
第21000页
正向查找SQL:
Sql代码
SELECT*FROM`abc`WHERE`BatchID`=123LIMIT839960,40
时间：1.8696秒
反向查找sql:
Sql代码
SELECT*FROM`abc`WHERE`BatchID`=123ORDERBYInputDateDESCLIMIT788775,40
时间：1.8336秒
第30000页
正向查找SQL:
Sql代码
1.SELECT*FROM`abc`WHERE`BatchID`=123LIMIT1199960,40
SELECT*FROM`abc`WHERE`BatchID`=123LIMIT1199960,40
时间：2.6493秒
反向查找sql:
Sql代码
1.SELECT*FROM`abc`WHERE`BatchID`=123ORDERBYInputDateDESCLIMIT428775,40
SELECT*FROM`abc`WHERE`BatchID`=123ORDERBYInputDateDESCLIMIT428775,40
时间：1.0035秒
注意，反向查找的结果是是降序desc的，并且InputDate是记录的插入时间，也可以用主键联合索引，但是不方便。
4.limit限制优化法
把limit偏移量限制低于某个数。。超过这个数等于没数据，我记得alibaba的dba说过他们是这样做的
5.只查索引法