程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql優化limit查詢語句的5個辦法

mysql優化limit查詢語句的5個辦法

編輯:MySQL綜合教程

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的優化限制都比擬多,所以現實情形用或許不消只能詳細情形詳細剖析了。頁數那末後,根本很少人看的。。。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved