mysql优化limit查询语句的5个方法
mysql的分页比较简单，只需要limitoffset,length就可以获取数据了，但是当offset和length比较大的时候，mysql明显性能下降
1.子查询优化法
先找出第一条数据，然后大于等于这条数据的id就是要获取的数据
缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性，具体方法请看下面的查询实例：
mysql>setprofiling=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>showprofiles\G ***************************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