iT邦幫忙

0

請較 MySQL 正排序時條件數字越大、速度越慢的原因

各位大大好

我在寫 SQL 時遇到條件數字越大、速度越慢的問題,比如:
WHERE price >= 500 (21.515s)
WHERE price BETWEEN 500 AND 1000 (18s)

但反之如果從小數字開始則速度飛快:
WHERE price >= 0 (0.078s)
WHERE price BETWEEN 0 AND 1000 (0.094s)
實際測試後發現數字跟速度成正比遞增
已確認 price 欄位有索引,而且 EXPLAIN 確認有使用到索引

上面條件都是基於價格由小到大排序 ORDER BY price ASC
但當我改成倒排 ORDER BY price DESC,速度又會變快:
WHERE price >= 500 (1.625s)
WHERE price BETWEEN 500 AND 1000 (1.094s)

請問有大大知道原因嗎?

如果我希望維持正排序 ORDER BY price ASC
並提升 WHERE price BETWEEN 500 AND 1000 語句的效率
我可以怎麼改善比較好?
非常感謝!

附上簡化過後的 SQL:

SELECT DISTINCT t1.product_id, t1.price
FROM my_table t1
LEFT JOIN my_table t2
	ON t1.product_id = t2.product_id AND t1.price > t2.price
WHERE t2.product_id IS NULL
	AND t1.price BETWEEN 500 AND 1000
ORDER BY t1.price ASC
LIMIT 0, 20

(上面的 SQL 是取每個 product_id 分組的最小值後依照價格排序,寫法參考了 MySQL 文檔 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column)

看更多先前的討論...收起先前的討論...
開一個VIEW,然後用 PRICE排序,然後去搜尋這個VIEW,然後不管你有沒有加ORDER BY,因為表本身已經排序好了,自然速度就會快
Blueberry iT邦新手 5 級 ‧ 2022-06-30 15:10:22 檢舉
感謝大大回應,轉成 view 之後發現 SELECT * FROM view 會變成 Derived Table,反而速度變慢 Orz
VIEW 會比較快第一個他基本的排序已經定義,第二個它的資料量比原本的資料表少
然後他也可以是跨表查詢的結果,然後不管是表或是VIEW都不該也不能用 SELECT * 吧,這是基本常識,你要那些欄位,直接拉,回傳的資料量越少速度越快,這是基本的認知,SELECT * 呵呵呵,你前面寫得怎麼沒用SELECT * 來看看秒數呢
Blueberry iT邦新手 5 級 ‧ 2022-07-01 14:31:05 檢舉
抱歉抱歉,我沒說清楚,我的 view 只有兩個欄位,所以沒有特別指定欄位
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
2
海綿寶寶
iT邦大神 1 級 ‧ 2022-06-30 14:26:47
最佳解答

WHERE price >= 500 (21.515s)來看
你處理的「資料量」「SQL 複雜度」執行要 21 秒
這已經超過這裡許多人的經驗值

1.我不知道原因
2.如果是我
我會用ORDER BY price DESC以加快速度
然後在讀 ResultSet 的時侯
再想辦法把資料「轉成」ORDER BY price ASC
算是偷雞的做法

Blueberry iT邦新手 5 級 ‧ 2022-06-30 15:11:02 檢舉

感謝大大提供新思路!

0
MatthewWangUS
iT邦新手 3 級 ‧ 2022-06-30 16:52:50

個人感覺怪怪的
假設 t2.product_id IS NULL 會有可能有 t2.price 是有值的?
沒有 product_id 卻有 price ?

Blueberry iT邦新手 5 級 ‧ 2022-07-01 14:43:23 檢舉

這個寫法參考這篇文檔最下面的解法
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column

The LEFT JOIN works on the basis that when s1.price is at its maximum value, there is no s2.price with a greater value and thus the corresponding s2.article value is NULL.

當初看到覺得很神奇,實際使用真的速度很快XD

0
fuzzylee1688
iT邦研究生 3 級 ‧ 2022-07-01 11:45:36

這段join挺發散的, 可能是影響到效能問題.
LEFT JOIN my_table t2 ON t1.product_id = t2.product_id AND t1.price > t2.price

Blueberry iT邦新手 5 級 ‧ 2022-07-01 14:38:21 檢舉

當初會這樣寫是因為看到這篇問答
MySQL - Fetching lowest value
之前有用 GROUP BY 寫過,但速度比這個方法慢很多
感謝回應

obarisk iT邦研究生 2 級 ‧ 2022-07-01 23:44:15 檢舉

同意,看Query Plan時,要特別看一下這一段

我要發表回答

立即登入回答