• Primary Key 字段的長度盡量小，能用 small integer 就不要用 integer。例如員工數(shù)據(jù)表，若能用員工編號當(dāng)主鍵，就不要用身分證號碼。
• 一般字段亦同。若該數(shù)據(jù)表要存放的數(shù)據(jù)不會超過 3 萬筆，用 small integer 即可，不必用 integer。
• 文字字段若長度固定，如：身分證號碼，就不要用 varchar 或 nvarchar，應(yīng)該用 char 或 nchar。
• 文字字段若長度不固定，如：地址，則該用 varchar 或 nvarchar。除了可節(jié)省存儲空間外，存取硬盤時(shí)也會較有效率。
• 設(shè)計(jì)字段時(shí)，若其值可有可無，最好也給一個(gè)默認(rèn)值，并設(shè)成「不允許 NULL」(一般字段默認(rèn)為「允許 NULL」)。因?yàn)?SQL Server 在存放和查詢有 NULL 的數(shù)據(jù)表時(shí)，會花費(fèi)額外的運(yùn)算動作 [2]。
• 若一個(gè)數(shù)據(jù)表的字段過多，應(yīng)垂直切割成兩個(gè)以上的數(shù)據(jù)表，并可用同名的 Primary Key 一對多連結(jié)起來，如：Northwind 的 Orders、Order Details 數(shù)據(jù)表。以避免在存取數(shù)據(jù)時(shí)，以「集簇索引 (clustered index)」掃描時(shí)會加載過多的數(shù)據(jù)，或修改數(shù)據(jù)時(shí)造成互相鎖定或鎖定過久。
------------------------------
2、適當(dāng)?shù)亟⑺饕?/span>
• 記得自行幫 Foreign Key 字段建立索引，即使是很少被 JOIN 的數(shù)據(jù)表亦然。
• 替常被查詢或排序的字段建立索引，如：常被當(dāng)作 WHERE 子句條件的字段。
• 用來建立索引的字段，長度不宜過長，不要用超過 20 個(gè) Byte 的字段，如：地址。
• 不要替內(nèi)容重復(fù)性高的字段建立索引，如：性別；反之，若重復(fù)性低的字段則適合建立索引，如：姓名。
• 不要替使用率低的字段建立索引，以免浪費(fèi)硬盤空間。
• 不宜替過多字段建立索引，否則反而會影響到「INSERT、UPDATE、DELETE」的性能，尤其是以「OLTP (聯(lián)機(jī)事務(wù)處理；在線交易)」為主的網(wǎng)站數(shù)據(jù)庫。
• 若數(shù)據(jù)表存放的數(shù)據(jù)很少，就不必刻意建立索引。否則可能數(shù)據(jù)庫沿著存放索引的「樹狀結(jié)構(gòu)」(Balanced Tree) 去搜尋索引中的數(shù)據(jù)，反而比掃描整個(gè)數(shù)據(jù)表還慢。
• 若查詢時(shí)符合條件的數(shù)據(jù)很多，則透過「非集簇索引 (non-clustered index)」搜尋的性能，反而 可能不如整個(gè)數(shù)據(jù)表逐筆掃描。
• 建立「集簇索引」的字段選擇至為重要，會影響到整個(gè)索引結(jié)構(gòu)的性能。要用來建立「集簇索引」的字段，務(wù)必選擇「整數(shù)」類型 (鍵值會較小)、唯一、不可為 NULL。
------------------------------
3、適當(dāng)?shù)厥褂盟饕?/span>
• 有些書籍會提到，使用「LIKE、%」做模糊查詢時(shí)，即使您已替某個(gè)字段建立索引 (如下方代碼的 CustomerID 字段)，但以常量字符開頭才會使用到索引，若以萬用字符 (%) 開頭則不會使用索引，如下所示：
USE Northwind;
GO
SELECT * FROM Orders WHERE CustomerID LIKE 'D%'; --使用索引
SELECT * FROM Orders WHERE CustomerID LIKE '%D'; --不使用索引 |
在 SQL Server 2005 執(zhí)行完成后按 Ctrl + L，可檢閱如下圖的「執(zhí)行計(jì)劃」。
圖 1 可看出「查詢最佳化程序」有使用到索引做搜尋
圖 2 在此的「集簇索引」掃描，并未直接使用索引，性能上幾乎只等于掃描整個(gè)數(shù)據(jù)表
但經(jīng)版工反復(fù)測試，這種語法是否會使用到索引，抑或會逐筆掃描，并非絕對的。仍要看所下的查詢關(guān)鍵詞，以及字段內(nèi) 所存儲的數(shù)據(jù)內(nèi)容而定。但對于存儲數(shù)據(jù)筆數(shù)龐大的數(shù)據(jù)表，最好還是少用 LIKE 做模糊查詢。
• 以下的運(yùn)算符會造成「負(fù)向查詢」，常會讓「查詢最佳化程序」無法有效地使用索引，最好能用其它運(yùn)算符和語法改寫 (經(jīng)版工測試，并非有負(fù)向運(yùn)算符，就絕對無法使用索引)：
NOT 、 != 、 <> 、 !> 、 !< 、 NOT EXISTS 、 NOT IN 、 NOT LIKE
• 避免讓 WHERE 子句中的字段，去做字符串的串接或數(shù)字運(yùn)算，否則可能導(dǎo)致「查詢最佳化程序」無法直接使用索引，而改采「集簇索引掃描」(經(jīng)版工測試并非絕對)。
• 數(shù)據(jù)表中的數(shù)據(jù)，會依照「集簇索引」字段的順序存放，因此當(dāng)您下 BETWEEN、GROUP BY、ORDER BY 時(shí)若有包含「集簇索引」字段，由于數(shù)據(jù)已在數(shù)據(jù)表中排序好，因此可提升查詢速度。
• 若使用「復(fù)合索引」，要注意索引順序上的第一個(gè)字段，才適合當(dāng)作過濾條件。
------------------------------
4、避免在 WHERE 子句中對字段使用函數(shù)
對字段使用函數(shù)，也等于對字段做運(yùn)算或串接的動作，一樣可能會讓「查詢最佳化程序」無法有效地使用索引。但真正對性能影響最重大的，是當(dāng)您的數(shù)據(jù)表內(nèi)若有 10 萬筆數(shù)據(jù)，則在查詢時(shí)就需要呼叫函數(shù) 10 萬次，這點(diǎn)才是真正的性能殺手。程序員應(yīng)注意，在系統(tǒng)開發(fā)初期可能感覺不出差異，但當(dāng)系統(tǒng)上線且數(shù)據(jù)持續(xù)累積后，這些語法細(xì)節(jié)所造成的性能問題就會逐步浮現(xiàn)。
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%' |
注意當(dāng)您在下 UPDATE、DELETE 語句時(shí)，若有采用 WHERE 子句，也應(yīng)符合上述原則。。
------------------------------
5、AND 與 OR 的使用
在 AND 運(yùn)算中，「只要有一個(gè)」條件有用到索引 (如下方的 CustomerID)，即可大幅提升查詢速度，如下圖 3 所示：
| SELECT * FROM Orders WHERE CustomerID='VINET' AND Freight=32.3800 --使用索引，會出現(xiàn)下圖 3 的畫面 |
| SELECT * FROM Orders WHERE Freight=32.3800 --不使用索引，會出現(xiàn)上圖 2 的畫面 |
圖 3
但在 OR 運(yùn)算中，則要「所有的」條件都有可用的索引，才能使用索引來提升查詢速度。因此 OR 運(yùn)算符的使用必須特別小心。
若您將上方 AND 的范例，邏輯運(yùn)算符改成 OR 的話，如下所示：
| SELECT * FROM Orders WHERE CustomerID='VINET' OR Freight=32.3800 |
由于無法有效地使用索引，也會出現(xiàn)圖 2 的畫面。
在使用 OR 運(yùn)算符時(shí)，只要有一個(gè)條件 (字段) 沒有可用的索引，則其它所有的條件 (字段) 都有索引也沒用，只能如圖 2 般，把整個(gè)數(shù)據(jù)表或整個(gè)集簇索引都掃描過，以逐筆比對是否有符合條件的數(shù)據(jù)。
據(jù)網(wǎng)絡(luò)上文件的說法 [1]，上述的 OR 運(yùn)算語句，我們還可用 UNION 聯(lián)集適當(dāng)?shù)馗纳疲缦拢?/span>
SELECT * FROM Orders WHERE CustomerID='VINET'
UNION
SELECT * FROM Orders WHERE Freight=32.3800 |
此時(shí)您再按 Ctrl + L 檢閱「執(zhí)行計(jì)劃」，會發(fā)現(xiàn)上半段的查詢會使用索引，但下半段仍用集簇索引掃描，對性能不無小補(bǔ)。
------------------------------
6、適當(dāng)?shù)厥褂米硬樵?/span>
相較于「子查詢 (Subquery)」，若能用 JOIN 完成的查詢，一般會比較建議使用后者。原因除了 JOIN 的語法較容易理解外，在多數(shù)的情況下，JOIN 的性能也會比子查詢較佳；但這并非絕對，也有的情況可能剛好相反。
我們知道子查詢可分為「獨(dú)立子查詢」和「關(guān)聯(lián)子查詢」兩種，前者指子查詢的內(nèi)容可單獨(dú)執(zhí)行，后者則無法單獨(dú)執(zhí)行，亦即外層查詢的「每一次」查詢動作都需要引用內(nèi)層查詢的數(shù)據(jù)，或內(nèi)層查詢的「每一次」查詢動作都需要參考外層查詢的數(shù)據(jù)。
以下我們看一個(gè)比較極端的例子 [2]。若我們希望所有查詢出來的數(shù)據(jù)，都能另外給一個(gè)自動編號，版工我在之前的文章「ASP.NET 數(shù)據(jù)分頁第一篇 - 探討分頁原理及 SQL Server 2005 的 ROW_NUMBER 函數(shù)」中有介紹過，可用 SQL Server 2005 中新增的 ROW_NUMBER 函數(shù)輕易地達(dá)成，且 ROW_NUMBER 函數(shù)還能再加上「分群 (PARTITION BY)」等功能，而且執(zhí)行性能極佳。
圖 4 將 Orders 數(shù)據(jù)表的 830 筆數(shù)據(jù)都撈出來，并在右側(cè)給一組自動編號
現(xiàn)在我們要如上圖 4 般，將 Northwind 數(shù)據(jù)庫中 Orders 數(shù)據(jù)表的 830 筆數(shù)據(jù)都撈出來，并自動給一組編號，若用 ROW_NUMBER 函數(shù)的寫法如下所示，而且性能極佳，只要 2 ms (毫秒)，亦即千分之二秒。
SET STATISTICS TIME ON
SELECT OrderID, ROW_NUMBER() OVER(ORDER BY OrderID) AS 編號
FROM dbo.Orders |
但如果是傳統(tǒng)的「子查詢」寫法，或 輔以 AS 關(guān)鍵詞的「衍生數(shù)據(jù)表」的語法，寫法必須如下 (拷貝后在 SQL Server 中實(shí)際可執(zhí)行)：
SET STATISTICS TIME ON
SELECT OrderID,
(SELECT COUNT(*) FROM dbo.Orders AS 內(nèi)圈
WHERE 內(nèi)圈.OrderID <= 外圈.OrderID) AS 編號
FROM dbo.Orders AS 外圈
ORDER BY 編號 |
但這種舊寫法，會像先前所提到的，外層 (外圈) 查詢的「每一次」查詢動作都需要引用內(nèi)層 (內(nèi)圈) 查詢的數(shù)據(jù)。以上方示例而言，外層查詢的每一筆數(shù)據(jù)，都要等內(nèi)層查詢「掃描整個(gè)數(shù)據(jù)表」并作比對和計(jì)數(shù)，因此 830 筆數(shù)據(jù)每一筆都要重復(fù)掃描整個(gè)數(shù)據(jù)表 830 次，所耗用的時(shí)間也因此爆增至 170 ms。
若您用相同的寫法，去查詢 AdventureWorks 數(shù)據(jù)庫中，有 31,465 筆數(shù)據(jù)的 Sales.SalesOrderHeader 數(shù)據(jù)表，用 ROW_NUMBER 函數(shù)要 677 ms，還不到 1 秒鐘；但用子查詢的話，居然要高達(dá) 233,835 ms，將近快 4 分鐘的時(shí)間。
-- 用 ROW_NUMBER 的寫法，改查詢 AdventureWorks 數(shù)據(jù)庫 (31,465 筆數(shù)據(jù)，要 677 ms，還不到 1 秒鐘)
SELECT SalesOrderID, ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS rownum
FROM Sales.SalesOrderHeader |
-- 用「子查詢」的寫法，改查詢 AdventureWorks 數(shù)據(jù)庫 (31,465 筆數(shù)據(jù)，要 233,835 ms，將近 4 分鐘)
SELECT SalesOrderID,
(SELECT COUNT(*) FROM Sales.SalesOrderHeader AS 內(nèi)圈
WHERE 內(nèi)圈.SalesOrderID <= 外圈.SalesOrderID) AS 編號
FROM Sales.SalesOrderHeader AS 外圈
ORDER BY 編號 |
雖然這是較極端的范例，但由此可知子查詢的撰寫，在使用上不可不慎，尤其是「關(guān)聯(lián)子查詢」。程序員在系統(tǒng)開發(fā)初期、數(shù)據(jù)量還很少時(shí)感受不到此種 SQL 語法的重大陷阱；但等到系統(tǒng)上線幾個(gè)月或一兩年后，就會有反應(yīng)遲緩的現(xiàn)象， 不可不慎。
注：AS 關(guān)鍵詞及「衍生數(shù)據(jù)表」是 SQL Server 的語法，「衍生數(shù)據(jù)表」只會存在內(nèi)存中，AS 關(guān)鍵詞的作用是賦予一個(gè)別名。過去許多必須用暫存數(shù)據(jù)表或 View (視圖) 的情況，現(xiàn)在都可以用「衍生數(shù)據(jù)表」來取代，如此一來不但可以降低數(shù)據(jù)庫管理工作的負(fù)擔(dān)，亦可提升查詢性能。
------------------------------
7、其他查詢技巧
• DISTINCT、ORDER BY 語法，會讓數(shù)據(jù)庫做額外的計(jì)算。此外「聯(lián)集」的使用，若沒有要剔除重復(fù)數(shù)據(jù)的需求，使用 UNION ALL 會比 UNION 更優(yōu)，因?yàn)楹笳邥尤腩愃?DISTINCT 的算法。
• 在 SQL Server 2005 中，存取數(shù)據(jù)庫對象時(shí)，最好明確指定該對象的「結(jié)構(gòu)描述 (Schema)」，也就是使用兩節(jié)式的名稱，如下方代碼所示。否則若呼叫者的預(yù)設(shè) Schema 不是 dbo，則 SQL Server 在執(zhí)行時(shí)，會先尋找該使用者預(yù)設(shè) Schema 所搭配的對象，找不到的話才會轉(zhuǎn)而使用預(yù)設(shè)的 dbo，會多耗費(fèi)尋找的時(shí)間。因此若要執(zhí)行一個(gè)叫做 dbo.mySP1 的 Stored Procedure，應(yīng)使用以下的兩節(jié)式名稱：
------------------------------
8、盡可能用 Stored Procedure 取代應(yīng)用程序直接存取數(shù)據(jù)表
Stored Procedure 除了經(jīng)過事先編譯、性能較好以外，亦可節(jié)省 SQL 語句傳遞的網(wǎng)絡(luò)頻寬，也方便商業(yè)邏輯的重復(fù)使用。再搭配自訂函數(shù)和 View 的使用，將來若要修改數(shù)據(jù)表結(jié)構(gòu)、重新切割或「反正規(guī)化」時(shí)亦較方便。
------------------------------
9、盡可能在數(shù)據(jù)來源層，就先過濾數(shù)據(jù)
使用 SELECT 語法時(shí)，盡量避免傳回所有的數(shù)據(jù)至前端而不設(shè)定 WHERE 等過濾條件。雖然 ASP.NET 中 SqlDataSource、ObjectDataSource 控件的 FilterExpression 可再做篩選，GridView 控件的 SortExpression 可再做排序，但會多消耗掉數(shù)據(jù)庫的系統(tǒng)資源、web server 的內(nèi)存和網(wǎng)絡(luò)頻寬。最好還是在數(shù)據(jù)庫和數(shù)據(jù)來源層，就先用 SQL 條件式或 Stored Procedure 篩選出所要的資料。有關(guān)這方面，網(wǎng)友們可參考版工我之前寫的「ASP.NET 數(shù)據(jù)分頁」系列的四篇帖子。
------------------------------
結(jié)論：
本文的觀念，不管是寫 SQL statement、Stored Procedure、自訂函數(shù)或 View 皆然。本文只是挑出程序員較容易犯的 SQL 語法性能問題，以期能在短時(shí)間瀏覽過本文后，在寫 ADO.NET 程序時(shí)能修正以往隨興的 SQL 語句撰寫習(xí)慣。文中提到的幾點(diǎn)，只不過是 SQL 語法性能議題的入門。市面上有很多更進(jìn)階的書籍，例如：「The Art of SQL」、「SQL Tuning」，亦有針對 Oracle 或 SQL Server 數(shù)據(jù)庫撰寫的 performance tuning 相關(guān)書籍，有興趣者可自行翻閱。
------------------------------------------------------------
------------------------------------------------------------
參考文件：
[1] SQL 查詢最佳化 (網(wǎng)際烏托邦)：
http://www.ithome.com.tw/plog/index.php?op=ViewArticle&articleId=5421&blogId=620
------------------------------
參考書籍：
[2] SQL Server 2005 Performance Tuning 效能調(diào)校 (臺灣書籍)：
作者：胡百敬、姚巧枚、劉承修
出版社：悅知出版社
http://tlsj.tenlong.com.tw/WebModule/BookSearch/bookSearchViewAction.do?isbn=9789866761225&sid=41966
[3] SQL Server 2005 完全實(shí)戰(zhàn) (臺灣書籍)：
作者：章立民
出版社：碁峰出版社
http://tlsj.tenlong.com.tw/WebModule/BookSearch/bookSearchViewAction.do?isbn=9789861810454&sid=31975
------------------------------
相關(guān)文件：
[4] 臺大醫(yī)院數(shù)據(jù)庫分割疏失，系統(tǒng)幾近停擺 (ITHome)：
http://www.ithome.com.tw/itadm/article.php?c=43597
[5]] 當(dāng) DataGrid 遇見100 萬筆資料：
http://blog.sina.com.tw/4907/article.php?pbgid=4907&entryid=3921
[6] ASP.NET 2.0 GridView 范例集 - 「4-8-4、GridView 的效能」：
http://blog.csdn.net/Code6421/archive/2007/12/22/1958167.aspx
[7] 有關(guān)開啟頁面時(shí)，一次加載數(shù)千筆數(shù)據(jù)的性能問題：
http://www.blueshop.com.tw:80/board/show.asp?subcde=BRD200709141021458MV