[MySQL]一道簡易的 Select 指令，只差了一個數字，查詢時間卻相差了200秒？

mysql

keberosx 2018-01-11 17:02:26 ‧ 12607 瀏覽

分享至

SELECT
   recordTime,
   IFNULL(FORMAT(windSpeed_10mA,2),"NAN") AS WS10,
   IFNULL(FORMAT(windSpeed_30m,2),"NAN") AS WS30,
   IFNULL(FORMAT(windSpeed_50m,2),"NAN") AS WS50,
   IFNULL(FORMAT(windSpeed_95mA,2),"NAN") AS WS95A,
   IFNULL(FORMAT(windSpeed_95mB,2),"NAN") AS WS95B,
   IFNULL(FORMAT(windDirection_10mA,1),"NAN") AS WD10,
   IFNULL(FORMAT(windDirection_30m,1),"NAN") AS WD30,
   IFNULL(FORMAT(windDirection_50m,1),"NAN") AS WD50,
   IFNULL(FORMAT(windDirection_95m,1),"NAN") AS WD95
FROM
   wind_Raw
WHERE
   recordTime BETWEEN '2017-12-01 00:00:01' AND '2017-12-29 00:00:00'
ORDER BY
   recordTime

#wind_Raw資料表簡述：記錄每秒的風速、風向變化。一日計有8萬6千400筆資料。

上面這串 SELECT 查詢指令，耗時了 210 秒才得到查詢結果。
若是將 recordTime 的結束時間改為 '2017-12-28 00:00:00'（少一天），只要耗時15秒就可以得到結果。

所以我利用了 EXPLAIN 來分析這兩串 SQL 的差別，結果如下：

28 天的 EXPLAIN 分析結果：
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
1 	SIMPLE 	wind_Raw 	range 	PRIMARY,recordTime,recordTime_2,recordTime_3,recor... 	PRIMARY 	60 	NULL	2011148 	Using where


29 天的 EXPLAIN 分析結果：
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
1 	SIMPLE 	wind_Raw 	ALL 	PRIMARY,recordTime,recordTime_2,recordTime_3,recordTime_4 	NULL	NULL	NULL	10761191 	Using where; Using filesort

多查詢了一天的資料量，就會造成 MySQL 採用了不一樣的方式來處理。且29天還是採用耗時很高的全表掃描…
針對這個問題，不知道是否有資料庫高手可提供意見進行改善？

看更多先前的討論...收起先前的討論...

牙膏大師！ iT邦新手 4 級 ‧ 2018-01-11 17:18:16 檢舉

我還蠻好奇的為什麼你index這麼多，卻沒吃到半個，兩隻sql都是

石頭 iT邦高手 1 級 ‧ 2018-01-11 21:31:36 檢舉

你如果改成

SELECT
recordTime,
IFNULL(FORMAT(windSpeed_10mA,2),"NAN") AS WS10,
IFNULL(FORMAT(windSpeed_30m,2),"NAN") AS WS30,
IFNULL(FORMAT(windSpeed_50m,2),"NAN") AS WS50,
IFNULL(FORMAT(windSpeed_95mA,2),"NAN") AS WS95A,
IFNULL(FORMAT(windSpeed_95mB,2),"NAN") AS WS95B,
IFNULL(FORMAT(windDirection_10mA,1),"NAN") AS WD10,
IFNULL(FORMAT(windDirection_30m,1),"NAN") AS WD30,
IFNULL(FORMAT(windDirection_50m,1),"NAN") AS WD50,
IFNULL(FORMAT(windDirection_95m,1),"NAN") AS WD95
FROM
wind_Raw
WHERE
recordTime > '2017-12-01 00:00:00' AND recordTime <= '2017-12-29 00:00:00'

時間是否有提升

keberosx iT邦新手 5 級 ‧ 2018-01-12 08:58:51 檢舉

To 牙膏大師：該 wind_Raw 只有 recordTime 欄位是 Primary Key，因為 Primary Key 好像本身就運用索引，所以我個人是沒特別把它新增索引過，多索引的現象可能我同事自已另外加的。

=======================================================
To dog830228：使用 Between 和您提供的雙邏輯運算時間是一樣的~

其實這道SQL，若是不加 Order By 的話，28天跟29天是差不多的回傳時間。約5秒就可得到查詢結果；但若有加 Oder By 的話，29天的就不使用索引，而且還會引發「Using filesort」，反之28天的則不會且還會乖乖地使用索引。

我有測過變更時間區間來測試（需有Order By）：
01~28 或 02~29，共28天，查詢時間 15 秒
01~29 或 02~30，共29天，查詢時間 210 秒

fillano iT邦超人 1 級 ‧ 2018-01-12 09:12:12 檢舉

我猜跟sort_buffer_size有關，你可以調整看看。

fillano iT邦超人 1 級 ‧ 2018-01-12 09:16:00 檢舉

https://www.kancloud.cn/taobaomysql/monthly/67180

找到這篇有解釋為什麼會出現using filesort，可以參考一下。

石頭 iT邦高手 1 級 ‧ 2018-01-12 09:51:08 檢舉

To keberosx
My SQL我不確定，但 MSSQL 雙邏輯寫法和Between 效能上有差別

keberosx iT邦新手 5 級 ‧ 2018-01-12 09:52:57 檢舉

To fillano：感謝您提供的資訊~只是該文章內容算是提供「 MySQL 引發 Using filesort 」後的資訊文獻，屬於比較後階段的議題。

其中「sort_buffer_size」的參數調整，算是要用來優化「當 MySQL 已引發 Using filesort 時」的一個參數。加大該參數值可減少MySQL 的 filesort 排序時間。

目前我的 SQL 算是要避免掉 MySQL 所引發 Using filesort 的狀況，還有讓 MySQL 正常使用回索引。算是要讓 MySQL 直接查詢索引後就將結果回傳，而不讓它進行全表格的搜尋。

石頭 iT邦高手 1 級 ‧ 2018-01-12 10:07:37 檢舉

To keberosx: 請問一下你有沒有PK索引,
如果有用此PK索引來Order by 是否就不會全表掃瞄了?

keberosx iT邦新手 5 級 ‧ 2018-01-12 10:39:44 檢舉

To dog830228：wind_Raw資料表，本身就是設定 recordTime 為 Primary Key，所以確定是有索引的。且在上方的 EXPLAIN 分析結果當中，也就有顯示 Primary Key 的索引存在（索引名稱=PRIMARY），排版後如下：

28天的 EXPLAIN分析
type = range
possible_keys = PRIMARY,recordTime,recordTime_2,recordTime_3,recor...
key = PRIMARY
Extra = Using Where

29天的EXPLAIN分析
type=ALL
possible_keys =PRIMARY,recordTime,recordTime_2,recordTime_3,recor...
key=NULL
Extra =Using Where; Using filesort

石頭 iT邦高手 1 級 ‧ 2018-01-12 11:19:13 檢舉

不是所有型態都是適合PK
如果有INT 或 BIGINT當PK 效率會比 DateTime好一些

keberosx iT邦新手 5 級 ‧ 2018-01-12 11:50:09 檢舉

TO dog830228：
wind_Raw 資料表，假設有兩個索引：一個是 INT，一個是 DateTime。
MySQL 在查詢時若有發現此兩個索引的存在，自身若是會去使用較快速的 INT 來當搜尋因子，此現象在效能決策上我們可視為理所當然~

不過，在我的案例中，卻只有一個索引。
照理講，在資料處理的決策上，MySQL 不應該放棄掉那唯一的索引，而跑去進行全表格搜尋才對~
而且28天的資料量是作出正常的搜尋決策，29天的資料量卻是作出不正常的搜尋決策。
這就是讓我百思不得其解的地方…，且加大實體記憶體、調整一些 MySQL參數都得不到改善。

keberosx iT邦新手 5 級 ‧ 2018-01-12 16:15:51 檢舉

To Everyone：
最新測試結果出爐，我將多餘的索引刪除，只留下 Primary Key。然後再次執行相同的 SQL，發現 MySQL 對於該時間區間的判斷有移動。

此時，我為了抓出最精微的差異，所以一次一次執行，發現結果如下：
1、BETWEEN '2017-12-01 00:00:01' AND '2017-12-23 21:42:21'
結果：匯出檔案總耗時15秒

2、BETWEEN '2017-12-01 00:00:01' AND '2017-12-23 21:42:22'
結果：匯出檔案總耗時100秒

也就是說：查詢範圍只差了一秒，兩者檔案輸出的時耗卻相差了85秒。

fillano iT邦超人 1 級 ‧ 2018-01-12 16:44:42 檢舉

請參考：
https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html#order-by-filesort-in-memory
所以我懷疑可能跟sort_buffer_size設定有關係，因為結果集超過可以使用的sort_buffer，所以無法使用in-memory的方式來執行

keberosx iT邦新手 5 級 ‧ 2018-01-15 08:35:33 檢舉

To fillano：sort_buffer_size 參數我也是有調整過~不過這項參數是屬於 MySQL 引發 filesort 時，執行排序時的優化用參數。而不是避免 MySQL 引發 filesort 用的。

牙膏大師！ iT邦新手 4 級 ‧ 2018-01-16 11:24:39 檢舉

嗯你要不要參考一下 <https://stackoverflow.com/a/5826874> 這篇？很妙的是他們說型別不吻合，甚至資料表語言編碼不吻合都會造成 mySQL 不用 index

牙膏大師！ iT邦新手 4 級 ‧ 2018-01-16 11:32:51 檢舉

我突然有個想法...，有沒有可能你28日的資料量超級多，也許有個八百萬筆（兩筆 EXPLAIN 的 rows 相減）？而且你的資料量也正好只有 12 月 1 日到 28 日，所以第二筆 SQL 即使有 range ，但搜尋範圍已經無異於全表掃描，所以 MySQL 雙手一攤就認命掃了整張表

keberosx iT邦新手 5 級 ‧ 2018-01-16 14:52:16 檢舉

To 牙膏大師：
===回應上上則===
我有看了一下該文內容，原 PO 下搜尋絛件，該型別可能是數字或文字，所以或許會造成 MySQL誤判。但我的案例所搜尋的型別卻都是一樣的…。
而且，文章裡面也有提示可以迫使 MySQL 使用「USE INDEX ( $indexName )」來優化查詢。
我保持著雀躍的心，嘗試加入了「USE INDEX (PRIMARY)」這一段SQL, 結果…
還是一樣：全表搜尋 + 不使用index + 並執行 filesort …
著實讓我好傷心…

===回應上則===
我Count 12/28 當日的資料筆數，結果一樣是86,400筆~，所以28日的資料量是跟其它日子一樣的~~
目前我們的秒資料目前為 2017/09/01 ~ 2018/01/16，所以也並非是剛剛符合該時間區段的數據~~

您亦可以順便點擊一下上方的「看更多先前的討論」的功能一下，並請參閱我前陣子補充的內容~
裡面其實也有提到我後續有移除掉無用的索引，只保留「PRIMARY」這個因 Primary Key 建立 MySQL 自動產生出的索引~

後來測試了一下，原本 28天與29天的差異，變成23與24天的差異了…

我不信邪，為了追根究抵找出 MySQL 作此決策差異的最微細的依據變化。
結果測試出：BETWEEN '2017-12-01 00:00:01' AND '2017-12-23 21:42:21' 的搜尋條件是屬於高速處理的。若是時間區間再加了1秒，就會變成龜速處理。

高速處理：使用範圍搜尋 + 使用 Index + 直接採 Index 排序。總耗時約14秒
龜速處理：使用全表搜尋 + 不使用 Index + 使用 filesort 排序。總耗時約140秒（至比以前快了一分鐘以上，可能是因為移除掉重覆無用的索引的累贅資料，優化了該表格）

我楞住了一段時間，且思考不出原因…
心聲：竟然只差一秒就有那麼大的變化…
心聲：竟然只差一秒就有那麼大的變化…
心聲：竟然只差一秒就有那麼大的變化…
心聲：就那麼一秒…

修改

牙膏大師！ iT邦新手 4 級 ‧ 2018-01-16 17:55:08 檢舉

QQ 剛剛在書上看到還能用 <https://dev.mysql.com/doc/internals/en/optimizer-tracing.html> 看到 MySQL 是如何決定要不要用 index 的，但他輸出的結果我實在是有看沒有懂，只能幫你到這了

keberosx iT邦新手 5 級 ‧ 2018-01-17 14:26:20 檢舉

To 牙膏大師：
您也是有幫上不少忙~有協助導引本人去注意一些我本沒來注意到的地方~
過程中多多少少也是有吸收到一些額外的相關知識~
小弟仍然很感謝您關注這個問題~

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

haoming

iT邦好手 1 級 ‧ 2018-01-12 08:42:57

會不會是你有啟動 query cache, 當你先query 29天時候已經先cache 了部分結果。
所以當你跑第二次，只query 28天時候, cache 就倒出來用了。
多執行幾次結果都是一樣嗎?

回應 1
分享
檢舉

keberosx iT邦新手 5 級 ‧ 2018-01-12 09:04:35 檢舉

我有測試過加大 MySQL 的 Cache，但多次執行結果都一樣~

我有測過變更時間區間來測試（需有Order By）：
01~28 或 02~29，共28天，查詢時間 15 秒。
01~29 或 02~30，共29天，查詢時間 210 秒。

下面就是 EXPLAIN 結果，最令人匪夷所思的狀況：
只要是28天的查詢，MySQL 就會使用範圍搜尋、利用索引、不使用 Filesort。
只要是超過29天的查詢，MySQL 就使用全表搜尋、不利用索引，使用 Filesort。

登入發表回應

pupuliao

iT邦新手 5 級 ‧ 2018-01-12 10:47:47

您好

根據我之前自己的測試(資料量大約60萬筆以上)
強烈建議不要用datetime 作 index 或是搜尋條件
datetime的索引效果很差

建議根據蒐尋的方式來拆開欄位
我自己是拆開程 date 跟 time ，這樣我要搜尋某一天的資料時速度會快很多

回應 3
分享
檢舉

keberosx iT邦新手 5 級 ‧ 2018-01-12 11:34:49 檢舉

我的表格資料量一天是新增8萬6400筆（一天的秒數），預計要一次 SQL 要查詢 31 天的量。

其實我實測起來，datetime 的 Primary Key 索引效果並不差~
任何一天的資料，甚至28天的區間，都可以快速查詢到~（phpMyAdmin的查詢顯示只要0.6秒就完成了，只是畫面顯示要稍微等待一下）

雖然拆開為 date 和 time，再組成 Primary Key 形成雙欄位的索引也是一個好方法，但實際上我也是害怕超過 29 天的量後，就又回到原本的問題。且單索引就已造成 MySQL 作出詭異的檔案處理決策，我想換成雙索引則有更的高機率發生。

其實在我的案例中，索引的設計上並非大問題~
反而是 MySQL 面對某一程度的大數據時，所要作的資料處理決策差異，才是引發這個問題的主因。

也就是說不知道為什麼：「一樣的SQL，只是多了8萬的資量料，卻會造成 MySQL 放棄以鎖定資料範圍的方式進行索引查詢。反而去採用全表格的非索引搜尋？」

註：此 SQL 若不加 Order By 子句，則28、29天都是採用鎖定資料範圍的索引搜尋。回傳結果極為快速的。

pupuliao iT邦新手 5 級 ‧ 2018-01-12 13:53:52 檢舉

當然這是要看你的使用需求，我是航班搜尋，資料量每個table 上千萬筆，每次都是拉出特定航線一天的資料，所以把date time 拆開會有明顯效果

另外看你的需求是每次要處理一個月份的資料，我自己的做法是，Log 表跟結果顯示表分開，log的資料每天一次撈出來處理後塞進結果顯示報表，資料再每天固定處理一次，顯示的人何時看都可以

當然這要看你如何顯示資料

假設每天處理一次把資料量從八萬筆變成一筆，這樣資料量會大幅縮減成一個月28~31筆

keberosx iT邦新手 5 級 ‧ 2018-01-12 14:57:20 檢舉

以我們的狀況也有是作到資料精簡化的需求~
只不過，我們是把秒原始資料另計成 10 分鐘統計資料~
利用這些 10 分鐘統計資料來顯示日、月、年報的話，原則上並不會有系統壓力出現~
同時，秒資料並不開放使用者搜尋與使用的~

但因為我們目前在需求上有固定將一月份的秒資料匯出成 txt 檔的情況。
在進行這種一次性的大量資料匯出時，無意間發現資料庫系統有這樣子的特性，所以正在想辦法優化這部份的問題~

就我目前最新測試結果，無論 28 天或 29 天，如果只有 SELECT recrodTime 這個 Primary Key 欄位，就算都有加 ORDER BY 子句，兩者 MySQL 一樣會採用索引的方式進行查詢。
但此 SELECT 只要另加入一個非 Primary Key 的欄位上去，就如同原 PO 的內容，MySQL 對於 29 天的是採用非索引式的全表格搜尋，並再作 filesort 的動作。28 的則不會。

所以我到現在還是搞不清楚 MySQL 進行決策的判斷點何在？

登入發表回應