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