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語句各步驟的執行時間，可參考：

本文永久更新連結地址：