資料庫系列 - 5: SQL 相關小知識(?)

作為這系列(暫時性的)最後一小節，我就將其他小知識一同彙整在這裡吧~

清空資料表

一般來說最簡單的方法是用 DELETE FROM <你的資料表>; 這條指令，雖然方便但是實際上效能很差、速度很慢，也容易觸發問題。相比之下，可以依序使用以下的指令去處理，整體上效能會快速很多:

• 刪除外鍵: ALTER TABLE <你的資料表> DROP FOREIGN KEY <外鍵>;
• 清空表格: TRUNCATE TABLE <你的資料表>;
• 重新添加外鍵: ALTER TABLE <你的資料表> ADD CONSTRAINT <外鍵> FOREIGN KEY (主鍵) REFERENCES <你的資料表(主鍵)>;

如此一來，在清理大數據的資料庫時會很明顯的速度提升喔。

多表時，取得其中一表的最新資料

概念上來說，只需要額外 join 自己的最新那筆資料即可。

舉例來說，如果我有甲乙兩張表，彼此用欄位 order_id 關聯，而甲裡面有個訂單編號 num 的欄位。我現在只想取甲裡面最新每個顧客最新一筆訂單，並且符合其他條件的資料。

SELECT DISTINCT 甲.order_id FROM 甲 left join (SELECT order_id, MAX(num) maxnum FROM 甲 GROUP BY order_id) 丙 on ... left join 乙... WHERE 條件…

另外，記得中間要用 Group by 統整成一筆資料喔！

各部門大量資料的總和與可能的問題

這個是之前面試時碰到的問題，剛好看到網路上有各式各樣的回答，就順手整理一下答案了。

1. 由於需要區分各部門，所以需要下 Group by 導致時間變長，大量資料可能需要1分鐘以上。
2. 程式面來說，可以用非同步方法先讀取資料到陣列中再進行處理
3. 資料庫面來說，可以設置 cache 以及 index (針對要 Group by 的欄位)
4. 商業邏輯面上來說，可以限制總和的時間區間等等

評斷 SQL 語句的效能

一般來說，我們寫出的 SQL 語句都沒有太大的效能差異，很多只是0.1、0.2秒的差距而已，不過如果要一直重複處理、或是真的遇到大量資料時，SQL 寫得好不好可能就真的會有幾秒鐘甚至幾分鐘的差異了，那該怎麼做呢?

除了交給 ChatGPT 幫你修改 SQL 以外，你也可以用 explain 指令去確認效能，用法也很簡單，直接在你原本的 SQL 語句前面加上 explain ，在輸出那邊就會有幾個項目顯示給你看，至於細節這邊就先不多作介紹了。

啊對了，記得我們在資料庫系列 - 2: 索引這集裡提到的，SQL搜尋時記得盡量不要用 not in 喔！這是因為用 not in 就沒有 index 的效果了喔~

參考資料

1. https://stackoverflow.com/questions/44063719/group-by-max-date-and-id
2. https://medium.com/judys-database-sharing/mysql-explain效能分析應用-一-9a8bdbd4f346