分页查询优化

1、 子查询优化法

先找出第一条数据，然后大于等于这条数据的id就是要获取的数据 

缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性。

实验下：

代码如下

复制代码

mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from Member; +----------+ | count(*) | +----------+ |   169566 |  +----------+ 1 row in set (0.00 sec) mysql> pager grep !~- PAGER set to 'grep !~-' mysql> select * from Member limit 10, 100; 100 rows in set (0.00 sec) mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100; 100 rows in set (0.00 sec) mysql> select * from Member limit 1000, 100; 100 rows in set (0.01 sec) mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100; 100 rows in set (0.00 sec) mysql> select * from Member limit 100000, 100; 100 rows in set (0.10 sec) mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100; 100 rows in set (0.02 sec) mysql> nopager PAGER set to stdout mysql> show profilesG *************************** 1. row *************************** Query_ID: 1 Duration: 0.00003300    Query: select count(*) from Member *************************** 2. row *************************** Query_ID: 2 Duration: 0.00167000    Query: select * from Member limit 10, 100 *************************** 3. row *************************** Query_ID: 3 Duration: 0.00112400    Query: select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100 *************************** 4. row *************************** Query_ID: 4 Duration: 0.00263200    Query: select * from Member limit 1000, 100 *************************** 5. row *************************** Query_ID: 5 Duration: 0.00134000    Query: select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100 *************************** 6. row *************************** Query_ID: 6 Duration: 0.09956700    Query: select * from Member limit 100000, 100 *************************** 7. row *************************** Query_ID: 7 Duration: 0.02447700    Query: select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100

从结果中可以得知，当偏移1000以上使用子查询法可以有效的提高性能。

2、join查询法（实质转化为对id查询）

总数据有500万左右

以下例子：当使用 select * from wl_tagindex where byname='f' order by id limit 300000,10 执行时间是 3.21s

优化后：

select * from (
            select id from wl_tagindex
            where byname='f' order by id limit 300000,10
) a
left join wl_tagindex b on a.id=b.id

执行时间为 0.11s 速度明显提升

这里需要说明的是，我这里用到的字段是 byname ,id 需要把这两个字段做复合索引，否则的话效果提升不明显。

【微信扫码关注我的公众号获取更多信息】