欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

當前位置： 維易PHP培訓學院 > MYSQL教程 > 內容正文

MYSQL數據庫mysql優化limit查詢語句的5個方法

作者：VEPHP   時間 2017-09-16

《MYSQL數據庫mysql優化limit查詢語句的5個方法》要點：
本文介紹了MYSQL數據庫mysql優化limit查詢語句的5個方法，希望對您有用。如果有疑問，可以聯系我們。

mysql的分頁比較簡單,只需要limit offset,length就可以獲取數據了,但是當offset和length比較大的時候,mysql明顯性能下降MYSQL應用

1.子查詢優化法MYSQL應用

先找出第一條數據,然后大于等于這條數據的id就是要獲取的數據
缺點：數據必須是連續的,可以說不能有where條件,where條件會篩選數據,導致數據失去連續性,具體方法請看下面的查詢實例：
MYSQL應用

代碼如下:

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應用

mysql> pager grep !~-
PAGER set to 'grep !~-'MYSQL應用

mysql> select * from Member limit 10, 100;
100 rows in set (0.00 sec)MYSQL應用

mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100;
100 rows in set (0.00 sec)MYSQL應用

mysql> select * from Member limit 1000, 100;
100 rows in set (0.01 sec)MYSQL應用

mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100;
100 rows in set (0.00 sec)MYSQL應用

mysql> select * from Member limit 100000, 100;
100 rows in set (0.10 sec)MYSQL應用

mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100;
100 rows in set (0.02 sec)MYSQL應用

mysql> nopager
PAGER set to stdoutMYSQL應用


mysql> show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00003300
?? Query: select count(*) from MemberMYSQL應用

*************************** 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 100MYSQL應用

*************************** 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 100MYSQL應用

*************************** 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
MYSQL應用


從結果中可以得知,當偏移1000以上使用子查詢法可以有效的提高性能.

2.倒排表優化法 MYSQL應用

倒排表法類似建立索引,用一張表來維護頁數,然后通過高效的連接得到數據MYSQL應用

缺點：只適合數據數固定的情況,數據不能刪除,維護頁表困難MYSQL應用

倒排表介紹：(而倒排索引具稱是搜索引擎的算法基石)MYSQL應用

倒排表是指存放在內存中的能夠追加倒排記錄的倒排索引.倒排表是迷你的倒排索引.MYSQL應用

臨時倒排文件是指存放在磁盤中,以文件的形式存儲的不能夠追加倒排記錄的倒排索引.臨時倒排文件是中等規模的倒排索引.MYSQL應用

最終倒排文件是指由存放在磁盤中,以文件的形式存儲的臨時倒排文件歸并得到的倒排索引.最終倒排文件是較大規模的倒排索引.MYSQL應用

倒排索引作為抽象概念,而倒排表、臨時倒排文件、最終倒排文件是倒排索引的三種不同的表現形式.MYSQL應用

3.反向查找優化法 MYSQL應用

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

缺點：order by優化比較麻煩,要增加索引,索引影響數據的修改效率,并且要知道總記錄數 ,偏移大于數據的一半MYSQL應用

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

做下實驗,看看性能如何MYSQL應用

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

第21000頁
正向查找SQL:
MYSQL應用

代碼如下:
SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40?

時間：1.8696 秒

反向查找sql:
MYSQL應用

代碼如下:
SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40

時間：1.8336 秒

第30000頁
正向查找SQL:?
MYSQL應用

代碼如下:
SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40?

時間：2.6493 秒

反向查找sql:
MYSQL應用

代碼如下:
SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40?

時間：1.0035 秒

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

4.limit限制優化法MYSQL應用

把limit偏移量限制低于某個數..超過這個數等于沒數據,我記得alibaba的dba說過他們是這樣做的MYSQL應用

5.只查索引法 MYSQL應用

MySQL的limit工作原理就是先讀取n條記錄,然后拋棄前n條,讀m條想要的,所以n越大,性能會越差.
優化前SQL:
MYSQL應用

代碼如下:
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的優化限制都比較多,所以實際情況用或者不用只能具體情況具體分析了.頁數那么后,基本很少人看的...MYSQL應用

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/1623.html


    標簽：