mysql優化limit查詢語句的5個辦法。本站提示廣大學習愛好者:(mysql優化limit查詢語句的5個辦法)文章只能為提供參考,不一定能成為您想要的結果。以下是mysql優化limit查詢語句的5個辦法正文
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
第21000頁
正向查找SQL:
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讀取須要的列。
總結:limit的優化限制都比擬多,所以現實情形用或許不消只能詳細情形詳細剖析了。頁數那末後,根本很少人看的。。。