資料庫查詢性能優化技巧 : 語句優化、添加索引

March 29, 2023 程式語言

當系統資料量日益龐大常會遇到資料庫查詢時間過久的情況，此篇提供資料庫查詢性能優化的幾個方法。

主要紀錄自己所使用過的幾個技巧包含優化查詢語句、優化索引、減少資料庫中的資料量。

1. 優化查詢語句

以下提供幾個平常在撰寫SQL就需注意的小技巧

select 查詢避免使用 *

效能：SELECT * 會查詢整個表格中的所有欄位，包括不需要的欄位。這會增加資料庫的工作負載，降低查詢效率。而只查詢需要的欄位可以減少查詢的資料量，提高查詢效率。
可讀性：SELECT * 不清楚地表明查詢的欄位，這可能會使程式碼難以理解和維護。而明確指定需要的欄位可以使程式碼更加清晰和易於理解。
衝突：如果表格中有相同名稱的欄位，使用 SELECT * 可能會導致衝突，例如使用 JOIN 時。而使用明確指定欄位名稱的方式可以避免此類問題的發生。

避免在 where 子句中使用!=或<>

使用此語句等於是需要查詢整個資料表，導致效能下降。

避免在 where 子句中使用 or 連接

OR操作符也是需要搜尋整個資料表，以查找符合任一條件的資料導致效能下降。可以藉由union all先分別取得兩個條件的資料再組合。

避免在 where 子句中進行表達式與函數操作

Ex:

SQL
select name from table where age-10 = 20

基本上這種寫法，會先對整個table的age-10再去比對是否等於20，所以應該改為

SQL
select name from table where age = 30

當然這個例子比較極端一點，正常都不會這樣寫，主要重點就是，盡量避免在等號左側使用表達式或函數修改欄位資料，而是在右側修改。

索引（Index）是資料庫管理系統中用於加速數據查詢的一種數據結構。索引透過對欄位或欄位組進行排序，創建一個數據結構來加快查詢和排序的速度。在資料庫中，索引可視為一個指向實際數據的指針或地址。

簡單來說若針對特定欄位添加索引(Ex: 此欄位有A、B、C、D...等資料)，可以在查詢欄位 = B的相關資料時，迅速找到B相關資料的區塊組再比對資料，而不用把A、B、C、D都全部查詢一遍，增加查詢速度。

索引可以創建在單個欄位或多個欄位上，以下提供可以加上索引的情況：

主鍵和外鍵欄位：主鍵和外鍵欄位是資料表中最重要的欄位之一，它們通常會被用作查詢和關聯表格時的連結條件。在這些欄位上建立索引，可以讓查詢和關聯操作更快速。
經常被查詢的欄位：如果某個欄位被頻繁地作為查詢條件使用，那麼建立索引可以大幅提升查詢效率。
!!需注意唯一性與分布的問題
Ex : 性別就不太適合當作索引，因為性別欄位只有兩個值，男和女，不具唯一性，因此無法作為唯一鍵或主鍵欄位，建立索引也無法加速查詢。而性別在欄位中的值分布也相對均勻，並不會造成大量資料集中在某個特定值上，因此不建立索引對查詢效率的影響較小。
組合查詢條件欄位：當多個欄位一起作為查詢條件使用時，可以建立複合索引。複合索引可以讓資料庫系統更快地定位符合多個條件的資料。
用於排序的欄位：如果某個欄位經常被用來進行排序，那麼建立索引可以大幅提升排序操作的效率。
用於分組的欄位：如果某個欄位經常被用來進行分組，那麼建立索引可以大幅提升分組操作的效率。

雖然看似很多情況都可以增加索引，但不是索引越多越好，過多的索引反而會增加查詢的複雜度進而導致效率降低，這裡也就只能藉由經驗去判斷實際該加上索引的情況了。

索引雖然可以加快查詢速度，但實際上它是以空間換取時間。以上方欄位有A、B、C、D...等資料的例子，如果對於此欄位添加索引，等於是額外又會有A、B、C、D...等部分的資料表存放於資料庫以供加速查詢，所以本身的伺服器硬體環境是否能負荷也需要考量。

目前經查詢減少資料量，主要做法就是正規化、壓縮資料庫、刪除資料。

正規化:是指在資料庫設計中的一個過程，通過將資料分割成更小的、相關性更強的部分，來減少資料重複性，提高資料庫的效率和靈活性。
壓縮資料庫: 壓縮檔案也是一門學問，效能增加與否取決於壓縮方式和壓縮率。一般來說，如果壓縮率很高，則需要更長的時間來解壓縮數據，這可能會導致查詢變慢。如果壓縮率較低，則可以減少磁盤I/O操作，提高查詢性能。這部分得有請專業DBA協助~。
刪除資料:視情況刪除多餘或重複資料。

刪除資料這部分我則是覺得應該在初始就需要多注意的地方，一開始schema的設計與該存取怎樣的資料，都需先考慮清楚，而不是等資料量大起來後，才再考慮如何刪除資料。(當然若有系統合併的需求那就沒辦法)

以上就是目前有實際操作過的技巧，其他的就等嘗試過了再補充。😎