5個mysql優化limit查詢語句總結

這篇文章主要介紹了mysql優化limit查詢語句的5個方法,它們分別是子查詢優化法、倒排表優化法、反向查找優化法、limit限制優化法和只查索引法,需要的朋友可以參考下

mysql的分頁比較簡​​單，只需要limit offset,length就可以取得資料了，但是當offset和length比較大的時候，mysql明顯效能下降

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 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以上使用子查詢法可以有效的提升效能。

2.倒排表最佳化法

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

缺點：只適合資料數固定的情況，資料不能刪除，維護頁表困難

倒排表介紹：（而倒排索引具稱是搜尋引擎的演算法基石）

倒排表是指存放在記憶體中的能夠追加倒排記錄的倒排索引。倒排表是迷你的倒排索引。

暫時倒排檔案是指存放在磁碟中，以檔案的形式儲存的不能夠追加倒排記錄的倒排索引。臨時倒排檔案是中等規模的倒排索引。

最終倒排檔案是指由存放在磁碟中，以檔案的形式儲存的暫時倒排檔案歸併得到的倒排索引。最終倒排檔案是較大規模的倒排索引。

倒排索引作為抽象概念，而倒排表、暫時倒排檔案、最終倒排檔案是倒排索引的三種不同的表現形式。

3.反向找出最佳化法

當偏移超過一半記錄數的時候，先用排序，這樣偏移就反轉了

缺點：order by優化比較麻煩，要增加索引，索引影響資料的修改效率，並且要知道總記錄數，偏移大於資料的一半

limit偏移演算法：
正向尋找： (當前頁- 1) * 頁長度
反向查找： 總記錄- 當前頁* 頁長度

做下實驗，看看效能如何

總記錄數： 1,628,775
每頁記錄數： 40
總頁數：1,628,775 / 40 = 40720
中間頁數：40720 / 2 = 20360

第21000SQL頁
:

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40

時間：1.8696 秒

反向找出sql:

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40

時間：1.8336 秒

第30000頁
正向查找SQL:  

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40

時間：2.6493 秒

反向找出sql:

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40

時間：1.0035 秒

注意，反向查找的結果是是降序desc的，並且InputDate是記錄的插入時間，也可以用主鍵聯合索引，但是不方便。

4.limit限制最佳化法

把limit偏移量限制低於某個數。 。超過這個數字等於沒數據，我記得alibaba的dba說過他們是這樣做的

5.只查索引法

MySQL的limit工作原理就是先讀取n筆記錄，然後拋棄前n條，讀m條想要的，所以n越大，效能會越差。
優化前SQL: 

SELECT * FROM member ORDER BY last_active LIMIT 50,5

優化後SQL: 

SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)

差異在於，最佳化前的SQL需要更多I/O浪費，因為先讀索引，再讀數據，然後拋棄無需的行。而優化後的SQL(子查詢那一記)只讀索引(Cover index)就可以了，然後透過member_id讀取所需的欄位。

以上是5個mysql優化limit查詢語句總結的詳細內容。更多資訊請關注PHP中文網其他相關文章！