iT邦幫忙

1

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

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天還是採用耗時很高的全表掃描…
針對這個問題,不知道是否有資料庫高手可提供意見進行改善?

看更多先前的討論...收起先前的討論...
我還蠻好奇的為什麼你index這麼多,卻沒吃到半個,兩隻sql都是
dog830228 iT邦研究生 5 級 ‧ 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,可以參考一下。
dog830228 iT邦研究生 5 級 ‧ 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 直接查詢索引後就將結果回傳,而不讓它進行全表格的搜尋。
dog830228 iT邦研究生 5 級 ‧ 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
dog830228 iT邦研究生 5 級 ‧ 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 用的。
嗯你要不要參考一下 <https://stackoverflow.com/a/5826874> 這篇?很妙的是他們說型別不吻合,甚至資料表語言編碼不吻合都會造成 mySQL 不用 index
我突然有個想法...,有沒有可能你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秒(至比以前快了一分鐘以上,可能是因為移除掉重覆無用的索引的累贅資料,優化了該表格)

我楞住了一段時間,且思考不出原因…
心聲:竟然只差一秒就有那麼大的變化…
心聲:竟然只差一秒就有那麼大的變化…
心聲:竟然只差一秒就有那麼大的變化…
心聲:就那麼一秒…
QQ 剛剛在書上看到還能用 <https://dev.mysql.com/doc/internals/en/optimizer-tracing.html> 看到 MySQL 是如何決定要不要用 index 的,但他輸出的結果我實在是有看沒有懂,只能幫你到這了
keberosx iT邦新手 5 級 ‧ 2018-01-17 14:26:20 檢舉
To 牙膏大師:
您也是有幫上不少忙~有協助導引本人去注意一些我本沒來注意到的地方~
過程中多多少少也是有吸收到一些額外的相關知識~
小弟仍然很感謝您關注這個問題~

2 個回答

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

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

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。

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

您好

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

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

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 進行決策的判斷點何在?

我要發表回答

立即登入回答