|
|
有些程序員在撰寫數據庫應用程序時，常專注于 OOP 及各種 framework 的使用，卻忽略了基本的 SQL 語句及其「性能(performance) 優化」問題。版工曾聽過臺灣某半導體大廠的新進程序員，所組出來的一段 PL/SQL跑了好幾分鐘還跑不完；想當然爾，即使他的 AJAX 及 ooxx 框架用得再漂亮，系統性能也會讓使用者無法忍受。以下是版工整理出的一些數據庫規劃、SQL performance tuning簡單心得，讓長年鉆研 .NET、AJAX、一堆高深 ooxx framework，卻無暇研究 SQL statement的程序員，透過最短時間對本帖的閱讀，能避免踩到一些 SQL 的性能地雷。
(注：本帖的 SQL 語句皆經過測試可正常執行無誤。有興趣實驗者，可直接拷貝后，粘貼至 SQL Server 中執行。)
1、數據庫設計與規劃
• Primary Key 字段的長度盡量小，能用 small integer 就不要用 integer。例如員工數據表，若能用員工編號當主鍵，就不要用身分證號碼。
• 一般字段亦同。若該數據表要存放的數據不會超過 3 萬筆，用 small integer 即可，不必用 integer。
• 文字字段若長度固定，如：身分證號碼，就不要用 varchar 或 nvarchar，應該用 char 或 nchar。
• 文字字段若長度不固定，如：地址，則該用 varchar 或 nvarchar。除了可節省存儲空間外，存取硬盤時也會較有效率。
• 設計字段時，若其值可有可無，最好也給一個默認值，并設成「不允許 NULL」(一般字段默認為「允許 NULL」)。因為 SQL Server 在存放和查詢有 NULL 的數據表時，會花費額外的運算動作 [2]。
• 若一個數據表的字段過多，應垂直切割成兩個以上的數據表，并可用同名的 Primary Key 一對多連結起來，如：Northwind的 Orders、Order Details 數據表。以避免在存取數據時，以「集簇索引 (clusteredindex)」掃描時會加載過多的數據，或修改數據時造成互相鎖定或鎖定過久。
------------------------------
2、適當地建立索引
• 記得自行幫 Foreign Key 字段建立索引，即使是很少被 JOIN 的數據表亦然。
• 替常被查詢或排序的字段建立索引，如：常被當作 WHERE 子句條件的字段。
• 用來建立索引的字段，長度不宜過長，不要用超過 20 個 Byte 的字段，如：地址。
• 不要替內容重復性高的字段建立索引，如：性別；反之，若重復性低的字段則適合建立索引，如：姓名。
• 不要替使用率低的字段建立索引，以免浪費硬盤空間。
• 不宜替過多字段建立索引，否則反而會影響到「INSERT、UPDATE、DELETE」的性能，尤其是以「OLTP (聯機事務處理；在線交易)」為主的網站數據庫。
• 若數據表存放的數據很少，就不必刻意建立索引。否則可能數據庫沿著存放索引的「樹狀結構」(Balanced Tree) 去搜尋索引中的數據，反而比掃描整個數據表還慢。
• 若查詢時符合條件的數據很多，則透過「非集簇索引 (non-clustered index)」搜尋的性能，反而 可能不如整個數據表逐筆掃描。
• 建立「集簇索引」的字段選擇至為重要，會影響到整個索引結構的性能。要用來建立「集簇索引」的字段，務必選擇「整數」類型 (鍵值會較小)、唯一、不可為 NULL。
------------------------------
3、適當地使用索引
• 有些書籍會提到，使用「LIKE、%」做模糊查詢時，即使您已替某個字段建立索引 (如下方代碼的 CustomerID 字段)，但以常量字符開頭才會使用到索引，若以萬用字符 (%) 開頭則不會使用索引，如下所示：
| USE Northwind; GO SELECT * FROM Orders WHERE CustomerID LIKE 'D%'; --使用索引 SELECT * FROM Orders WHERE CustomerID LIKE '%D'; --不使用索引 |
在 SQL Server 2005 執行完成后按 Ctrl + L，可檢閱如下圖的「執行計劃」。
圖 1 可看出「查詢最佳化程序」有使用到索引做搜尋
圖 2 在此的「集簇索引」掃描，并未直接使用索引，性能上幾乎只等于掃描整個數據表
但經版工反復測試，這種語法是否會使用到索引，抑或會逐筆掃描，并非絕對的。仍要看所下的查詢關鍵詞，以及字段內 所存儲的數據內容而定。但對于存儲數據筆數龐大的數據表，最好還是少用 LIKE 做模糊查詢。
• 以下的運算符會造成「負向查詢」，常會讓「查詢最佳化程序」無法有效地使用索引，最好能用其它運算符和語法改寫 (經版工測試，并非有負向運算符，就絕對無法使用索引)：
NOT 、 != 、 <> 、 !> 、 !< 、 NOT EXISTS 、 NOT IN 、 NOT LIKE
• 避免讓 WHERE 子句中的字段，去做字符串的串接或數字運算，否則可能導致「查詢最佳化程序」無法直接使用索引，而改采「集簇索引掃描」(經版工測試并非絕對)。
• 數據表中的數據，會依照「集簇索引」字段的順序存放，因此當您下 BETWEEN、GROUP BY、ORDER BY 時若有包含「集簇索引」字段，由于數據已在數據表中排序好，因此可提升查詢速度。
• 若使用「復合索引」，要注意索引順序上的第一個字段，才適合當作過濾條件。
------------------------------
4、避免在 WHERE 子句中對字段使用函數
對字段使用函數，也等于對字段做運算或串接的動作，一樣可能會讓「查詢最佳化程序」無法有效地使用索引。但真正對性能影響最重大的，是當您的數據表內若有 10 萬筆數據，則在查詢時就需要呼叫函數 10萬次，這點才是真正的性能殺手。程序員應注意，在系統開發初期可能感覺不出差異，但當系統上線且數據持續累積后，這些語法細節所造成的性能問題就會逐步浮現。
| SELECT * FROM Orders WHERE DATEPART(yyyy, OrderDate) = 1996 AND DATEPART(mm, OrderDate)=7 可改成 SELECT * FROM Orders WHERE OrderDate BETWEEN '19960701' AND '19960731' |
| SELECT * FROM Orders WHERE SUBSTRING(CustomerID, 1, 1) = 'D' 可改成 SELECT * FROM Orders WHERE CustomerID LIKE 'D%' |
注意當您在下 UPDATE、DELETE 語句時，若有采用 WHERE 子句，也應符合上述原則。。
------------------------------
5、AND 與 OR 的使用
在 AND 運算中，「只要有一個」條件有用到索引 (如下方的 CustomerID)，即可大幅提升查詢速度，如下圖 3 所示：
| SELECT * FROM Orders WHERE CustomerID='VINET' AND Freight=32.3800 --使用索引，會出現下圖 3 的畫面 |
| SELECT * FROM Orders WHERE Freight=32.3800 --不使用索引，會出現上圖 2 的畫面 |
it知識庫：30 分鐘快快樂樂學 SQL Performance Tuning，轉載需保留來源！
鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。
