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

MySQL的分頁優化,MySQL分頁優化

編輯:MySQL綜合教程

MySQL的分頁優化,MySQL分頁優化


今天下午,幫同事重寫了一個MySQL SQL語句,該SQL語句涉及兩張表,其中一張表是字典表(需返回一個字段),另一張表是業務表(本身就有150個字段,需全部返回),當然,字段的個數是否合理在這裡不予評價。平時,返回的數據大概5w左右,系統尚能收到數據。但12月31日那天,數據量大概20w,導致SQL執行時間過長,未能在規定的時間內反饋結果,於是系統直接報錯。

一般的思路是用MySQL的分頁功能,即直接在原SQL語句後面增加LIMIT子句。但請注意,雖然你看到的反饋結果只是LIMIT後面指定的數量,於是想當然的以為MySQL只是檢索了指定數量的數據,然後給予返回。其實,MySQL內部實現的原理是,檢索所有符合where條件的記錄,然後返回指定數量的記錄。從這個角度來看,直接在原SQL語句後面添加LIMIT子句只能說是一種可以實現功能的方案,但未必最優。

具體在本例中,首先我們來看一下150個字段的表的統計信息:

一行大概就占2k,而Innodb默認頁的大小為16k,這意味著,一個頁中最多可存儲8行的數據。隨機讀的可能性大大增加。而這無疑會對數據庫系統的IO造成極大的壓力。

 

優化前

如果采用上述方案,即直接在原SQL語句後面增加LIMIT子句,下面,我們來看看它的執行情況。

首先,直接添加LIMIT子句後的SQL語句如下(已省略a1表的150個字段和a2中的一個字段):

FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) WHERE STATUS<>'00' AND settle_date=20151230 limit 50000,10000;

其執行時間如下:

大概執行了32s,絕大部分都花費到Sending data上了。Sending data指的是服務器檢索數據,讀取數據,並將數據返回給客戶端的時間。

 

關於上述執行結果,有以下幾點需要說明:

1. 這是SQL語句多次執行後的結果,這樣就可以排除結果緩存的影響,事實上,每次查詢的時長都是32s左右。

2. 為什麼選用的是limit 50000,10000,而不是0,10000,這個主要是考慮到對於LIMIT子句來說,越到後面,分頁的成本越高。基於此,選擇了中間值來作為分頁的結果。

該語句的執行計劃如下:

 

優化後:

優化的思路:

只對該表的主鍵進行分頁,然後用返回的主鍵作為子查詢的結果,來檢索該表其它字段的值。

改寫後的SQL語句如下:

FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) where seq_id in (select seq_id from (select seq_id FROM upay_csys_scquery_txn_log_his a1  WHERE STATUS<>'00' AND settle_date=20151230 order by 1 limit 50000,10000) as t);

其執行時間如下:

大概3s多,比第一種方案快了差不多10倍,效果顯著。

下面來看看其執行計劃(explain extended)

 

總結:

1. 改寫後的語句原本如下:

FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) where seq_id in (select seq_id FROM upay_csys_scquery_txn_log_his a1  WHERE STATUS<>'00' AND settle_date=20151230 order by 1 limit 50000,10000);

但MySQL報以下錯誤:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

需再增加一個嵌套子查詢,

比如這樣的語句是不能正確執行的。 
select * from table where id in (select id from table limit 12); 

但是,只要你再加一層就行。如: 
select * from table where id in (select t.id from (select * from table limit 12)as t) 

這樣就可以繞開limit子查詢的問題。 
問題解決。

2. 如果想查看MySQL查詢優化器等價改寫後的SQL語句,可首先通過explain extended得到具體的執行計劃,然後通過show warnings查看。

具體在本例中,等價改寫後的SQL語句如下:

與設想中的執行順序一致~

3. 如何查看MySQL語句各步驟的執行時間,可參考:http://www.cnblogs.com/ivictor/p/5085965.html

 

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