5個(gè)mysql優(yōu)化limit查詢語句總結(jié)

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

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

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

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

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?profilesG  ***************************?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é)果中可以得知,當(dāng)偏移1000以上查詢法可以有效的提高性能。

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

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

缺點(diǎn):只適合數(shù)據(jù)數(shù)固定的情況,數(shù)據(jù)不能查詢,維護(hù)頁表困難

倒排表介紹:(而倒排索引具稱是查詢引擎的算法基石)

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

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

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

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

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

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

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

limit偏移算法:
正向查找: (當(dāng)前頁 – 1) * 頁長度
反向查找: 總記錄 – 當(dāng)前頁 * 頁長度

做下實(shí)驗(yàn),看看性能如何

總記錄數(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

時(shí)間:1.8696 秒

反向查找sql:

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

時(shí)間:1.8336 秒

第30000頁
正向查找SQL: ?

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

時(shí)間:2.6493 秒

反向查找sql:

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

時(shí)間:1.0035 秒

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

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

把limit偏移量限制低于某個(gè)數(shù)。。超過這個(gè)數(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浪費(fèi),因?yàn)橄茸x索引,再讀數(shù)據(jù),然后拋棄無需的行。而優(yōu)化后的SQL(子查詢那條)只讀索引(Cover index)就可以了,然后通過member_id讀取需要的列。

以上就是5個(gè)

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊12 分享