SQL修羅場之欲改善龜速SQL到...心境轉變

23 Mar, 2024

前情提要

為什麼這篇獨立於SQL小抄，因為這個問題我覺得作為一個後端實在太嚴重了...

就是我這幾天工作時放棄了邏輯用我的自然語言造詣寫了一個類似這樣的SQL

select date_format(日期A, "%Y-%m-%d") as 日期A_format, sum(項目A) as 項目A總和, sum(項目B) as 項目B總和, 類型 from 表A
where 流水號 = ? 
and 日期A >= ? 
and 日期B <= ? 
and not (日期A =  
(select MAX(日期A) from 表A where 流水號 = ?  and date_format(日期A, "%Y-%m-%d") = 2024-03-23) 
and 項目A + 項目B < 1000)
group by date_format(日期A, "%Y-%m-%d"), 類型

那張表資料有八百萬筆 這個查詢區間為2天就跑了47秒

裡面的子查詢甚至跑了25秒

但實際上那段程式因為各種複雜的商場問題（？）之後得跑數個月的資料...

......

O M G

翻桌啦 ヽ(｀Д´)ﾉ︵ ┻━┻ ┻━┻

因為當時線上服務還在運行，擔心又把資料庫那邊記憶體衝高，所以還沒加索引（根據前輩的說法，光加索引就要十幾分鐘-.-...），但我在錯愕之際開始科普SQL效能問題時，我發現INDEX不是加了，優化器就會去跑欸！！Orz|||

雖然前輩說我可以把SQL給他們看看，但我希望給他們看以前我再多精進.. 被看到爛SQL很難為情T_T

這些天閱讀資料

• Kaggle課程 如何寫出高效率SQL
• [SQL] 使用in、exists還是join呢?
• exist 和 in 的区别，以及小表驱动大表的原理
• 不要再问我 in，exists 走不走索引了
• 什麼？我寫的一條SQL讓公司網站癱瘓了…SQL慢查詢改善方案
• Database — 八個 Index( 索引 ) 無法生效的 SQL 寫法
• 别再问我MySQL为啥没走索引？就这几种原因，全都告诉你
• [SQL SERVER][Performance] 避免反向查詢
• 小紅書跟Reddit的一些貼文
  • Using NOT EXISTS versus a left join?
  • 在處理大型數據集時，有哪些經驗法則可以防止查詢速度慢
• SQL底層執行的順序相關文章
• 去看資料庫名著!!

於是「SQL初步檢核表」誕生...？

- [ ] SELECT要用的欄位
- [ ] 少用format轉換減少記憶體消耗
  - 但常常有這種必要之呃，可能跟一開始資料設計有比較大的關係...
  - [ ] 如果真的要轉，看能不能改用程式解決
- [ ] 這個比較極端－少用聚合函數 `SUM()` `AVG()`或+-*/運算子等
  - [ ] 如果真的要算，看能不能改用程式解決
- [ ] 少用 `!=` `<>` `not`
  - 看看能不能用`and` `or` `=`組合出一樣的邏輯，雖然判斷式會變多就是...
- [ ] 分清楚 `in` `exist` `join`的場合
- [ ] （有爭議）少用 `BETWEEN`，多用比較運算子縮限區間
- [ ] 少用模糊查詢
- [ ] 檢查多重索引順序

註：有些情境or會慢跟沒有走到索引有關

掘金或YT影片還有蠻多這類的整理就不再贅述...

迷你結論（？）

就算最後整理出這些小tips也不是教條主義，
最棒的學習方法當然是用發現很慢的語法去跑explain研究效能瓶頸，
但工作時常常頭上還有四五張tricket，
所以看資料或某些動物封面的書（？），
建立對關聯式資料庫底層跑法的大概認知，
至少初步減少常常寫出不跑索引SQL的頻率...

但關於最後我決定如何解決瓶頸－我最後做出一個已成顯學的選擇：但也解答了我之前的疑惑

我最後的...

是的，我最後想起「沒有銀彈」，放棄鑽牛角尖，我決定把SQL語法縮短，並把計算的部分完全移動至PHP用Array結構累加完成，日期相關判斷也是多用Carbon的format功能完成，SQL這部分我決定只把"食材"拿出來，帶回我的廚房料理

像是冰箱也可以泡茶、做滷味，但我承認自己現階段實力跟見識都有限[^1]，真的就是做得不好吃，我決定好好燒開水或拿出鍋子來做，做好再冰回冰箱裡（...）

我曾經覺得SQL可以做大部分的事情那為什麼要後端程式呢？隨著寫的程式越多，我逐漸發現廣泛用於商業用途的程式語言通常具備更好的字串或數字處理工具。我今天又更意識到，資料庫主要是個儲存資料的地方，強項在於事務回滾、叢級，但計算這種事情當表太大就太考驗SQL深度功能（像是暫存結果之類的功能）的使用了，問題是這種語法也不是ORM（至少Laravel雄辯是這樣，這類型的語句基本上都得用DB::raw()）方便寫的

但我還是覺得不要停止廣泛閱讀（真的要讀O'Reilly...）跟學習，希望多年工作後回頭看這篇文章，能有更好的見解！

那就去寫程式囉(>▽<)/ !

#SQL