MySql分页limit 优化

 mysql的分页比较简单，只需要limit offset,length就可以获取数据了，但是当offset比较大的时候，mysql明显性能下降

1.子查询优化法

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 profiles/G

*************************** 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以上使用子查询法可以有效的提高性能。

但是，子查询法限制比较大，只能是在没有where的情况下使用，能用到的机会比较少。可以作为一种思路

2.倒排表优化法

倒排表法就是建立索引，用一张表来维护页数，然后通过高效的连接得到数据