七大SQL程式碼編寫原則：可顯著提高資料庫效能！

理論上，資料庫效能調優應由資料庫管理員（DBA）完成，但是開發人員有義務書寫更規範的SQL程式碼，這也可以有效提高資料庫效能。本文將介紹七大SQL程式碼編寫規範，這是提高資料庫效能最簡單有效的方法。

1、改進索引

我們建立索引一定是有目的，目的是為了更快的查詢。當然，建立有用的索引是獲得更優效能的最佳方法之一，有用的索引可以幫助使用者查詢具有較少I/O操作和資源佔用的資料。索引越多意味著SELECT查詢可能會更快，因為索引的資料量比全表少，需要掃描的資料相對較少；索引已經對欄位排序，可以進行二分法查詢。但是，如果我們對每一列都建立索引，需要的額外儲存空間就會加大，DML（INSERT，UPDATE和DELETE）操作將顯著減慢，因為每個操作都需要維護索引。

因此，如果你的應用程式主要使用SELECT語句，則更多的索引可能會有幫助，但如果主要是DML操作，則需要限制建立的索引數。

你可以在每個表上建立主鍵，使其成為聚簇索引（請注意，如果在Enterprise Manager中設定主鍵，則預設情況下將對其進行聚類）。當然，你可以在外來鍵列上建立索引，外來鍵列上缺少索引會帶來兩個問題——限制併發性和影響效能。

2、刪除不必要的索引

索引維護需要大量的CPU和I/O資源，這是開發人員必須要考慮的問題。

例如，MS SQL Server為其使用者提供dm_db_index_usage_stats DMV以用於統計索引使用情況。你可以使用以下程式碼獲取不同索引的使用情況，對於未曾使用過或使用頻率很低的索引，我們最好刪除掉，這樣會提高MS SQL Server的效能。

SELECT
OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME],
DB_NAME(IUS.database_id) AS [DATABASE NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = IUS.[OBJECT_ID]
AND I.INDEX_ID = IUS.INDEX_ID

（MS SQL Server Performance Monitoring地址：https://www.datasunrise.com/performance_monitoring/ms-sql-server/）

3、避免程式碼中出現迴圈

在許多情況下，開發人員可以簡化程式碼來提高效能，比如：

for (int i = 0; i < 1000; i++)
{
    SqlCommand cmd = new SqlCommand(“INSERT INTO TBL (A,B,C) VALUES…”);
    cmd.ExecuteNonQuery();
}

在此示例中，1000個按順序查詢資料庫的操作需要佔用大量系統資源。但是，通過使用INSERT和UPDATE語句可以很容易地避免這種迴圈，例如：

INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) — SQL SERVER 2008
INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 — SQL SERVER 2005
UPDATE TableName SET A = CASE B
        WHEN 1 THEN ‘NEW VALUE’
        WHEN 2 THEN ‘NEW VALUE 2’
        WHEN 3 THEN ‘NEW VALUE 3’
    END
WHERE B in (1,2,3)

尋找一切可能的方法來減少到伺服器的往返次數，返回多個結果集是執行此操作的一種方法。

4、避免使用相關子查詢

相關子查詢是使用外部查詢中的值的子查詢（巢狀在另一個查詢中的查詢）。相關子查詢的執行依賴於外部查詢，多數情況下是子查詢的WHERE子句中引用外部查詢的表。執行過程如下：

（1）從外層查詢中取出一個元組，將元組相關列的值傳給內層查詢。

（2）執行內層查詢，得到子查詢操作的值。

（3）外查詢根據子查詢返回的結果或結果集得到滿足條件的行。

（4）然後外層查詢取出下一個元組重複做步驟1-3，直到外層的元組全部處理完畢。 

例如：

SELECT c.Name,
       c.City,
       (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName
FROM Customer c

在此示例中，存在問題是一個查詢（SELECT c.Name）返回的每一行都呼叫內部查詢（SELECT CompanyName FROM Company WHERE ID = c.CompanyID），這種方式是比較低效的。

開發人員可以使用JOIN構造更高效的查詢：

SELECT c.Name,
       c.City,
       co.CompanyName
FROM Customer c
        LEFT JOIN Company co
                ON c.CompanyID = co.CompanyID

5、使用SELECT而不是SELECT *

使用Select *（全選）查詢時，你將查詢所選表中的所有資料，包括不必要的資料。因此，請仔細定義SELECT查詢的範圍，避免不必要的操作。

低效示例：

SELECT *FROM public.customers
Efficient:
SELECT FirstName, LastName, Address, ZIPFROM public.customers

6、避免使用SELECT DISTINCT

此查詢的工作原理是對查詢中的所有欄位進行分組以建立不同的結果，雖然這種方法很方便，但它需要大量的系統資源。

反面教材：

SELECT DISTINCT FirstName, LastName, StateFROM public.customers
An efficient and accurate query:
SELECT FirstName, LastName, StateFROM public.customers

注意：僅在短語結尾處使用萬用字元

搜尋純文字資料時，萬用字元可幫助開發人員建立最廣泛的搜尋。但是，這種型別的搜尋也是效率最低的搜尋。

反面教材：

SELECT City FROM Customers
WHERE City LIKE ‘%Char%’

更有效的查詢方式：

SELECT City FROM Customers
WHERE City LIKE ‘Char%’

7、避免建立臨時表

避免使用臨時表，如果一定要用，請使用Create Table #temp顯式建立，也可以使用子查詢作為替代。如果你使用SQL語句生成一個巨大的臨時表，記憶體放不下時會全部拷貝到磁碟，IO瞬間飆升。因此，MySQL在執行SQL查詢時可能會用到臨時表，但是大部分情況下，用到臨時表就意味著效能較低。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31077337/viewspace-2158715/，如需轉載，請註明出處，否則將追究法律責任。