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.倒排表優化法

倒排表法就是建立索引，用一張表來維護頁數，然後通過高效的連接配接得到資料