欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

mysql優(yōu)化limit查詢語句的5個方法

 更新時間：2014年07月07日 09:03:34   投稿：junjie  
這篇文章主要介紹了mysql優(yōu)化limit查詢語句的5個方法,它們分別是子查詢優(yōu)化法、倒排表優(yōu)化法、反向查找優(yōu)化法、limit限制優(yōu)化法和只查索引法,需要的朋友可以參考下

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

1.子查詢優(yōu)化法

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

復制代碼 代碼如下:

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


從結(jié)果中可以得知，當偏移1000以上使用子查詢法可以有效的提高性能。

2.倒排表優(yōu)化法

倒排表法類似建立索引，用一張表來維護頁數(shù)，然后通過高效的連接得到數(shù)據(jù)

缺點：只適合數(shù)據(jù)數(shù)固定的情況，數(shù)據(jù)不能刪除，維護頁表困難

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

倒排表是指存放在內(nèi)存中的能夠追加倒排記錄的倒排索引。倒排表是迷你的倒排索引。

臨時倒排文件是指存放在磁盤中，以文件的形式存儲的不能夠追加倒排記錄的倒排索引。臨時倒排文件是中等規(guī)模的倒排索引。

最終倒排文件是指由存放在磁盤中，以文件的形式存儲的臨時倒排文件歸并得到的倒排索引。最終倒排文件是較大規(guī)模的倒排索引。

倒排索引作為抽象概念，而倒排表、臨時倒排文件、最終倒排文件是倒排索引的三種不同的表現(xiàn)形式。

3.反向查找優(yōu)化法

當偏移超過一半記錄數(shù)的時候，先用排序，這樣偏移就反轉(zhuǎn)了

缺點：order by優(yōu)化比較麻煩，要增加索引，索引影響數(shù)據(jù)的修改效率，并且要知道總記錄數(shù) ，偏移大于數(shù)據(jù)的一半

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

做下實驗，看看性能如何

總記錄數(shù)：1,628,775
每頁記錄數(shù)： 40
總頁數(shù)：1,628,775 / 40 = 40720
中間頁數(shù)：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 秒

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

4.limit限制優(yōu)化法

把limit偏移量限制低于某個數(shù)。。超過這個數(shù)等于沒數(shù)據(jù)，我記得alibaba的dba說過他們是這樣做的

5.只查索引法

MySQL的limit工作原理就是先讀取n條記錄，然后拋棄前n條，讀m條想要的，所以n越大，性能會越差。
優(yōu)化前SQL:

復制代碼 代碼如下:
SELECT * FROM member ORDER BY last_active LIMIT 50,5

優(yōu)化后SQL:
復制代碼 代碼如下:
SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)

區(qū)別在于，優(yōu)化前的SQL需要更多I/O浪費，因為先讀索引，再讀數(shù)據(jù)，然后拋棄無需的行。而優(yōu)化后的SQL(子查詢那條)只讀索引(Cover index)就可以了，然后通過member_id讀取需要的列。

總結(jié)：limit的優(yōu)化限制都比較多，所以實際情況用或者不用只能具體情況具體分析了。頁數(shù)那么后，基本很少人看的。。。

相關(guān)文章

  • mysql中in條件使用字符串方式

    mysql中in條件使用字符串方式

    這篇文章主要介紹了mysql中in條件使用字符串方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
    2022-08-08
  • Mysql事務隔離級別之讀提交詳解

    Mysql事務隔離級別之讀提交詳解

    這篇文章主要介紹了Mysql事務隔離級別之讀提交詳解，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
    2019-01-01
  • MySql比較運算符正則式匹配REGEXP的詳細使用詳解

    MySql比較運算符正則式匹配REGEXP的詳細使用詳解

    這篇文章主要介紹了MySql比較運算符正則式匹配REGEXP的詳細使用詳解，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
    2020-10-10
  • mysql存儲過程之引發(fā)存儲過程中的錯誤條件（SIGNAL和RESIGNAL語句）實例分析

    mysql存儲過程之引發(fā)存儲過程中的錯誤條件（SIGNAL和RESIGNAL語句）實例分析

    這篇文章主要介紹了mysql存儲過程之引發(fā)存儲過程中的錯誤條件（SIGNAL和RESIGNAL語句）,結(jié)合實例形式分析了mysql使用SIGNAL和RESIGNAL語句來引發(fā)存儲過程中的錯誤條件相關(guān)操作技巧與注意事項,需要的朋友可以參考下
    2019-12-12
  • 詳解如何通過Mysql的二進制日志恢復數(shù)據(jù)庫數(shù)據(jù)

    詳解如何通過Mysql的二進制日志恢復數(shù)據(jù)庫數(shù)據(jù)

    本篇文章主要介紹了詳解如何通過Mysql的二進制日志恢復數(shù)據(jù)庫數(shù)據(jù)，具有一定的參考價值，有興趣的可以了解一下。
    2017-04-04
  • Ubuntu 18.04安裝mysql 5.7.23

    Ubuntu 18.04安裝mysql 5.7.23

    這篇文章主要為大家詳細介紹了Ubuntu 18.04安裝mysql 5.7.23的相關(guān)資料，具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2019-02-02
  • 詳解MySQL 聯(lián)合查詢優(yōu)化機制

    詳解MySQL 聯(lián)合查詢優(yōu)化機制

    MySQL 使用聯(lián)合的形式的地方會遠遠超過我們過去認知的范疇?；旧?，它會認為每個查詢都有聯(lián)合，而不僅僅是從兩張表中查出匹配的數(shù)據(jù)行，這包括了子查詢，甚至僅僅對單表的 SELECT 操作。因此，理解 MySQL 如何執(zhí)行聯(lián)合十分重要。
    2021-05-05
  • MySQL中UNION語句用法詳解與示例

    MySQL中UNION語句用法詳解與示例

    這篇文章主要給大家介紹了關(guān)于MySQL中UNION語句用法的相關(guān)資料,實際業(yè)務中有時候需要把滿足多種獨立條件的結(jié)果集整合到一起,就可以使用UNOIN聯(lián)合查詢,需要的朋友可以參考下
    2023-08-08
  • Mysql數(shù)據(jù)庫清理binlog日志命令詳解

    Mysql數(shù)據(jù)庫清理binlog日志命令詳解

    這篇文章主要給大家介紹了Mysql數(shù)據(jù)庫清理binlog日志命令的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用Mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧
    2019-09-09
  • 深入了解MySQL鎖機制及應用場景

    深入了解MySQL鎖機制及應用場景

    MySQL鎖是操作MySQL數(shù)據(jù)庫時常用的一種機制。MySQL鎖可以保證多個用戶在同時執(zhí)行讀寫操作時，能夠互相協(xié)同、避免數(shù)據(jù)出現(xiàn)不一致或者讀寫沖突等問題。本篇文章將詳細介紹MySQL鎖的基本知識和具體應用
    2023-03-03

最新評論