[Translation]The SQL high performence series part II

偷懶了兩個月沒有寫文章,目前差不多也適應了新的環境
就以利用空閒時間翻譯的這篇文章作為blog復活的開始吧

原文:The SQL high performence series part II
這篇文章是三篇系列文的第二篇(不過作者於第三篇富監了),裡面對索引以及資料庫是如何決定搜尋計畫有不錯的說明

基本?我們可都是專家!

大部分於這篇文章中提到的內容對許多開發者都是老生常談, 但還是值得複習 – 即使在專家之間對於資料庫的基礎,以及影響系統表現的重大原因,都還是留有誤解以及矛盾的資訊. 我將這篇文章寫的比較像一篇演講,而不是一張」重要清單」,但如果你只想看結論可以直接按End

Data Size

這需要在這裡被強調是因為有越來越多的"data wasters" 錯誤的相信只要他們浪費越多,這個專案就會越像一個"企業解決方案",我跳入這個泥沼,知道有些賺飽口袋的反對者會有"你在說謊"之類的回應,不過如果這樣做能夠多省下一byte tree的資料,我願意付出這個代價.

最小化你的資料,當你不需要GUID的時候,不要用GUID(例如:你不需要做跨全域的複製以及不重複的ID時),不要用bigint當你只需要int或small int, 不要用small int 當一個 tiny int就可以滿足需求,用varchar當你不需要nvarchar.使用最小並適合的資料型態,不要到了要刪減過大的資料時,才使用不明確的packing技巧或是原生資料型別.

當然你應該計畫現實上有可能的成長, 而且我並不是在鼓吹你需要用一個tinyint來儲存你的CustomerID欄位,但是在設計你的應用程式時要有一個合理的標準 – 你真的會有超過兩億的使用者嗎?你的應用程式會有32767種語言嗎? 有可能我們會面對一個需有兩億個月份的日曆系統嗎?

去估計在資料庫中使用這些大類別儲存較小的資料是不是可接受的妥協,這會增加效能,並且允許你能簡單的升級你的資料型別,當那些不太可能發生的需求成真的時候。

很明顯的有需多案子確實需要大資料型別的保障,但太多DBA用GUID或BigInt儲存資料以防萬一,來逃避效率上的責任(建立GUID也要花費資源,並且會增加儲存以及I/O的花費,當GUID被用來記錄網路卡的MAC位置時,GUID是循序性的,但現在Windows上的GUID只是單純的亂數, 作為叢集主鍵將會導致無止境的資料重新排列)

這有什麼重要的?在實際的企業應用程式中,有好幾百萬的資料列在資料庫中,而又需要從像冰河一樣緩慢的儲存系統中讀取與寫入資料 – 在這些有限的資源中, 使用1MB來傳輸20000筆記錄不是比5000筆記錄來的好嗎? 10%的資料庫可以儲存到快取記憶體是不是比5%好呢?當然.

不要被那些小規模,所有資料都在記憶體而Query中的計算大於I/O費用的Benchmark所騙. 認為使用大的DataType隻影響了10%左右的表現 – 當你的資料庫進入真正的企業層級時, 尺寸真的很重要(size really does matter). 使用GUID當叢集式索引的主鍵不僅讓你的資料列更大, 也讓非叢集索引更大(並更慢), 當你SAN中最弱的一環正以100%在跑,你會後悔你浪費的每一個byte

索引

許多SQL Server表現上的問題都是源自於沒有或不正確的索引, 或是沒有用到的後補索引. 這常發生在前端專家不情願的接下資料庫的工作時,甚至是發生在由資料庫專家精心打造的資料庫中。
瞭解索引, 並專注於使用它們 , 是高效能資料庫最重要的一點. 不只是建立正確的索引, 還有如何正確的調整現有的索引.

非叢集索引

在參考書中的索引和資料庫中的索引有同樣的意義(並有很多相同的特徵)，參考索引你可以快速的找到你想要的資訊，與掃過書裡的每一頁來找尋內容不同，可以直接找到你想要的頁面。在SQL Server的世界中這種索引稱為非叢集索引(non-clustered indexes或是Secondary indexes) - 他們是一個依特定目的而排序資料的子集合，包含了一個指向實體資料列的指標。

在Northwind 資料庫中Order Table的ShipPostalCode是一個非叢集索引的範例。這個索引由ShipPostalCode排序，並可能在下列的Query中使用。

SELECT * FROM Orders WHERE ShipPostalCode = '05022'

如果你看一下執行計畫(當你執行Query時按Ctrl + K 或點選"顯示執行計畫"，執行計畫會出現在下方的結果分頁中)，你可以看到索引查找(index seek)的發生，並執行一個Bookmark lookup來找到原始的資料列。如果我們在執行以上的Query前先執行SET STATISTICS IO ON，我們會得到此Query的IO使用量統計報告，會像以下這樣:

Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

比較不會使用到索引的以下Query:

SELECT * FROM Orders WITH(INDEX(0)) WHERE ShipPostalCode = '05022'

在這個案例中執行計畫顯示了完整的資料表搜尋(full table scan)，IO統計報告如下:

Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.

在沒有索引的情況下明顯的使用了更多I/O,如果資料量增加到企業級的數量的話,這情況會變得更糟.讓這個事情更糟的是,每一次的資料表搜尋(table scan)會產生Page/row lock, block掉整個資料表的讀取,讓接下來的交易必須等待資料被unlock之後才能確定需要的資料在不在那些被鎖定的資料之中.可是索引查找(index seek)能知道被鎖定的資料並不是他要搜尋的資料,所以不會被影響.試試看同時在兩個分開的查詢視窗中跑兩句query(如果你不能夠在30秒內很快的切換查詢視窗,可以增加WAITFOR的延遲)

BEGIN TRAN

UPDATE Orders SET OrderDate = '1997-08-27'
WHERE OrderID = 10647

WAITFOR DELAY '00:00:30'

ROLLBACK TRAN

第一句使用索引的Query, 會忽略row lock資料列馬上回傳結果,因為被鎖定的資料列並不是這個query所要尋找的. 但第二句沒有使用索引query(可能因為沒有建立索引,或索引不是最佳搜尋選擇),會被block到另一個連線的鎖定解除. 不使用索引不只是非常的沒有效率,當資料庫增加規模時,也可能讓blocking問題顯著的惡化(或在嘗試增加規模時)

叢集式索引

回到我們的譬喻,許多書更進一步的將他們的內容排序,讓資料本身就是排序好的索引,一本料理書可能是按照料理名稱排序,或是一本電話簿是按照城市,姓,名來排列.所以如果你想在這些排好序的資料中搜尋,是非常的有效率的 - 在電話簿的例子中,你可以很快的找到你想找的城市以及姓名,然後在一小筆的資料中掃瞄你想找的人.在SQL server中這種排序叫做叢集式索引.(資料本身的排序),並且有很明顯的原因在一本書或一個資料表中只能有一個叢集式索引.它主要的優點就是所有符合索引的資料都可以迅速的提供,不用其他的參照.

在Northwind資料庫中使用下列的Query:

SELECT * FROM Orders JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE Orders.ShipPostalCode = '05022'

如果你檢視執行計畫,Order Details的資料會由一個有效率的索引查找(index seek)來取得.
不過叢集式索引並不是萬靈丹. 例如說,想像你是一個努力工作的打字員,正在維護一本電話簿的排版,然後你小心的將每一筆資料放在正確的頁面上.每次一筆新的資料進來,它並不會正好就符合排序資料的尾端,或是有時候改變已經存在的資訊會同時改變它的排序(例如說Smith先生改名成Jones),你需要重新排列一些頁面已取得空間.這些資料碎片的問題在非叢集與叢集索引都會發生, 但叢集式索引由於包含所有資料,所以這個問題特別嚴重.

當然你可以在每一頁預留一些空間放置這些資料來讓一些小的更動容易些,也就是在SQL Server中fillfactor(填滿因數)的目的(一個較低的fillfactor會在分頁留下更多的空間但會減低真正的資料密度 - 插入資料的速度增加,但讀取資料的表現則減少. 一個較高的fillfactor增加資料密度並提升讀取速度,但增加插入操作時會造成分頁的可能性.注意fillfactor只在索引建立時與重組/重建索引時有用),不規則的插入資料或經常改變叢集式索引欄位可能是會一個嚴重的效能問題.因為這個問題很多開發者使用單一的遞增ID 欄位來作為叢集式索引.歷史上來說有個原因是因為有複數且相同的欄位資料作為索引插入時,全部會被放到相同的索引底下,結果會在這個熱點導致資料的爭奪.SQL Server有邏輯可以解決這些ID欄位並有效的消除這些熱點的問題.

另一個叢集式索引的問題是他們的長度(他們包含了整個資料列的資料). 當你在找某一個特定的資料欄位時,其他資料有可能並不相關,或是你真的計畫在查找後使用所有的資料,但如果你查的是一個範圍內的資料(例如說在Oakville city姓Forbes的人的所有名子)資料庫引擎會使用範圍掃瞄(range scan)讀取整行的資料來找尋符合的資料,再抽出需要的資料. 在我們的電話簿例子中這小小的額外資料並不算什麼,但在真實世界的大型的資料表上可能會導致效能上的衝擊.
考慮如果我們有第二個用city,姓,名來排序的索引,資料庫引擎可以很有效的搜尋這些較小的索引內容.

覆蓋索引(Covering Indexes)

這個帶來一個重點 - 有些索引本身就包含足夠的資訊使你不需要去讀取內容,你的搜尋只要讀取索引就能滿足需求了.考慮一本旅遊書的索引,將有名的景點以國家,城市與名稱排序,然後將你導向更多資訊的頁面,如果你只是想知道義大利的Accademia dell' Arte del Disegno在什麼城市,快速的搜尋索引就可以知道他在Florence. 在這個例子中索引為"覆蓋索引(covering index)",包含了可以覆蓋需求的所有資訊,讓我們不需要去查找資料列來找尋需要的資訊,這常常是最有效的搜尋機制.

變化之前範例中的query:

SELECT OrderID FROM Orders WHERE ShipPostalCode = '05022'

這會很有效的使用ShipPostalCode索引來找尋特定的紀錄,並且這個索引完全可以滿足query本身,所以可以避免消耗資源的bookmark lookup,IO也可以被最小化.

Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

有人應該已經觀察到OrderID實際上並不在索引ShipPostalCode裡,或至少不應該在.但有趣的是所有已排序的資料中的叢集式索引欄位,會自動被加到其他索引的欄位中. 當你需要一個叢集式索引欄位的資料時,有可能非叢集索引的資料會立即滿足你,但也必須衡量它會讓每個其他的索引更大,導致更低的資料密度.

小型的覆蓋式索引是取得資料最有效率的方法,也是一個讓你確認你只取出你實際需要資料的理由.範圍掃瞄(Range Scan)在很多情形下也是很有效率的,並且通常在需要的資料在索引中是連續的的時候,像是你用BETWEEN搜尋兩個日期之間的資料時.雖然因為之前提過的bookmark lookup的花費, 範圍掃瞄(Range Scan)只在索引能完全覆蓋或是使用叢集式索引來搜尋的時候才能看到.

即使在完整的搜尋為必要的時候,索引仍然可能完全覆蓋,考慮加下來加入Customer table的索引:

CREATE INDEX CountryCity ON Customers(Country,City,Address)

這個索引當然包括了Country,City,Address,但如之前提過的它也包含了CustomerID因為它是叢集式索引.單然如果我們搜尋Country,或是Country與City,或是Country,City和Address一個很有效的搜尋或是範圍掃瞄(Range Scan)可能會用來找出符合的資料.

SELECT CustomerID FROM Customers WHERE Country = 'Canada'

如果我們只搜尋address呢?在這個情況下索引不能被用到因為他是先以country再以city與address排序的,所以一個特定的地址有可能出現在索引的任何位址

SELECT CustomerID FROM Customers WHERE Address = '43 rue St. Laurent'

你可能會很驚訝看到它仍然使用了索引, 儘管這次用的是很沒效率的掃瞄(scan)而不是查找(seek).索引因為覆蓋了query所有的敘句和傳回欄位而被使用,且因為索引只是資料的子集合,和資料表掃瞄(scan data table)比起來它使用較少的I/O來掃瞄整個索引

統計資料與bookmark

在之前的例子上我們跑了一個需要bookmark lookup的query(因為他沒有覆蓋索引).query如下:

SELECT * FROM Orders WHERE ShipPostalCode = '05022'

如果你觀看這個query的執行計畫可以看到它在索引中找尋"lookups",並且在叢集式索引上進行bookmark lookup,找尋真正資料列的資料.在這個案例中只有單一的傳回資料列,但即使如此,bookmark lookup的費用還是佔了50%的query運算費用.

bookmark lookup 的花費,是當一個item在索引中被找到,但索引並沒有包含所有需要的資料時所產生的,這也是為什麼很多人驚訝的發現SQL Server忽略了他們認為是完美的索引而使用資料庫掃瞄 (為什麼他們不用我的索引啊啊啊啊!!!). 考慮以下的query

SELECT * FROM Orders WHERE ShipPostalCode = '24100'

從執行計畫你可以看到實際上進行了一個叢集式索引掃瞄 (是用叢集式索引進行的一個資料表掃瞄)來取代使用我們的索引,而且subtree的花費是0.0530
這也許看起來有些複雜,因為我們應該已經有了一個完美的索引,但讓我們重新執行一次相同的query,這次使用一個query hint來強迫他使用我們的索引

SELECT * FROM Orders WITH(INDEX(ShipPostalCode)) WHERE ShipPostalCode = '24100'

你可以從執行計畫上看到它使用了我們的索引,但這次bookmark lookups佔了所有執行時間的80%.我們的subtree花費變成了0.0564 - 比使用資料庫掃瞄還多!

在這個案例中只使用了總共830筆中的10筆記錄(1.2%),而它仍然選擇執行資料庫掃瞄而不是我們的索引.很多開發者都被這個情況迷惑,不知道為什麼SQL Server不使用他們美麗的索引,但資料庫引擎是因為非常實際的理由這樣做的 - 因為這樣比進行非直接從bookmarks的找尋每一小塊資料來的有效率.

當然我們可以儘可能的讓覆蓋索引包含所有必要的資訊來避免bookmark lookups,假設它包含了我們需要從資料表中取得的所有資訊:

SELECT OrderID FROM Orders WHERE ShipPostalCode = '24100'

現在它會使用我們的索引,而且超級有效率. 它的subtree花費只有0.0064.在大型的資料庫這些迴避bookmark lookup與資料表掃瞄的差異可能會非常大.

所以資料庫引擎是如何猜到有多少資料列會符合標準,來決定使用哪個方法最有效率?(不論是索引,bookmark lookup或資料表掃瞄) 這就是分散式統計資料(distribution statistics)派上用場的時候了. 統計資料(Statistics)是一個資料集合,資料庫引擎用來推測哪個計畫在這個查詢上最有效率.你可以用DBCC SHOW_STATISTICS 的指令來看到某些索引的統計資料,在以下的例子中為ShipPostalCode:

DBCC SHOW_STATISTICS('Orders','ShipPostalCode')

因為資料表中的ShipPostalCode是有範圍並分散的號碼,所以在這個例子中統計資料是完全正確的,在一個更實際的資料庫中,有上百萬行的資料列, 統計資料開始更接近估計(錯誤邊際值會逐漸增加),這些資料庫引擎的估計在某些極端的情形下可能會導致完全錯誤的假設,像是預測會產生幾千行資料但實際只用了幾行.

統計資料也可能在複數索引時出錯.在這個案例中可選擇的第一個欄位被使用到,所以之前我們建立的索引(Country,City,Address)因為Country的低辨識性(有很多相同的Country欄位),這個索引經常會被忽略,即使City與Address的結合是非常unique的. 所以普遍來說都建議使用可辨識性最高的欄位作為索引的第一個欄位,在這個案例的索引來說是Address,City,Country. 這是可以討論的因為它讓索引更具有單一的用途-他不再具有增進效率的用途,提供給比較模糊的搜尋像只有Country,或是Country/City. 但這需要依照案例來判斷,而且在一個完整正規化的資料表中,這並不是個問題.

並且需要注意的是,你的統計資料儘可能的接近正確值是很重要的. SQL Server包含了自動更新統計資料,依照預設值,在覺得資料已經過期時會自動嘗試資料取樣與更新統計資料. 但儘管如此,最好的方法還是定期的安排完整的統計資料更新(最少每週),最好使用WITH FULLSCAN 的選項讓結果儘可能的正確. 標準的database維護計畫包含了更新統計資料的步驟,允許你選擇取樣的資料範圍.

但即使依照以上的步驟,你的統計資料仍然有過期的可能. 在這種情況下可能是索引提示需要被加入query,來要求query重新評量. 不過很明顯的這應該是最後才考慮的手段.

實際使用索引

所以你已經建立了美麗的索引,然後你已經確定了你的query只從每個資料表中提取了必需的資料,在可能的地方使用覆蓋索引來避免bookmark lookup的花費. 你使用執行計畫來評量效能,並發現......資料庫引擎完全忽略了你的索引. 以下幾點是可能的原因.

考慮以下的query

SELECT OrderID FROM Orders WHERE LEFT(ShipPostalCode,4) = '0502'

很簡單的query, 而且看起來這像是一個很有效率的覆蓋索引查找(covered index seek). 但執行後會發現這其實是一個沒有效率的掃瞄.考慮下列query:

SELECT OrderID FROM Orders WHERE ShipPostalCode LIKE '0502%'

在這個案例中query以高效率的索引查找(index seek)執行. 我已經有過這種微小的差距讓企業報表產生的時間從幾小時降到幾秒鐘的案例.

原因是因為前一個敘句的索引欄位被隱藏在函式之中. 資料庫引擎不能夠預測函式的結果是什麼,所以他會強迫去計算每一列的來看產生的結果. LIKE是第一級的比較函式,資料庫知道他的行為,所以可以對他做最佳化. 有無數的案例是由於人們沒有必要的將索引欄位放到了函式中,造成了大量與無法預期的效率低落.

最常見的例子是使用DATAADD/DATEDIFF來取得一定時間內的資料列 - 而不用預先計算好的數值(例如: GateDate() - 3 years) 並與資料列進行直接的比對, 開發者強迫整個資料庫掃瞄在每一個資料列上進行無謂的資料比對. 考慮一個從新聞資料表報告12小時內所有新出現新聞的query.

DECLARE @CurrentTime 

SET @CurrentTime = GetDate() 

SELECT * FROM NewsStories WHERE DATEDIFF(hh,NewsDate,@CurrentTime)<12 

我保證這會非常的沒有效率, 但這也是非常普遍的例子. 使用下列變數,資料庫引擎可以更有效的最佳化:

DECLARE @StartTime   
SET @StartTime = DATEADD(hh,-12,GetDate())   
SELECT * FROM NewsStories WHERE NewsDate > @StartTime

資料庫速查表:

索引欄位

在文章開頭我建議你們最小化使用的資料空間(增加實際資料密度). 這個目標並不是要將資料庫移植到一張磁片上,而是最小化查詢計需的磁碟I/O,因為磁碟I/O是企業系統中最脆弱的一環. 當然增加而外的索引,是一個設計上的選擇.它雖然會增加資料庫的大小但卻能減低實際上需要存取的I/O,所以通常都是很值得的交換.

另一個有用的技巧是,你可以以磁碟空間交換資料庫的效能,有很多的變化,但我只提一個最常使用的情形 - 依照年份提供每個月份的報表. 在這個情形下Orders 資料表可能被壓縮成用以下的Query來查詢:

SELECT YEAR(OrderDate) AS [Year], MONTH(OrderDate) AS [Month], COUNT(*) AS [Monthly Orders] 
FROM Orders 
WHERE YEAR(OrderDate)=1997 
GROUP BY YEAR(OrderDate), MONTH(OrderDate)

取代使用串接式的命令來取得年分與月份的資料,考慮把它們加為資料欄位:

ALTER TABLE dbo.Orders ADD 
OrderDateYear  AS CONVERT(smallint,YEAR(OrderDate)), 
OrderDateMonth  AS CONVERT(tinyint,MONTH(OrderDate)) 

現在我們可以把query改成

SELECT OrderDateYear AS [Year], OrderDateMonth AS [Month], COUNT(*) AS [Monthly Orders] 
FROM Orders 
WHERE OrderDateYear=1997 
GROUP BY OrderDateYear, OrderDateMonth

本身我們並沒有用任何方法來增加查詢的效率(事實上查詢效率在變更之後還降低了),雖然我們讓資料庫更複雜化,但我們也建立了一些索引欄位作為建立索引的基礎.

CREATE NONCLUSTERED INDEX IX_OrderDateDecomposed ON dbo.Orders
(
OrderDateYear,
OrderDateMonth
) ON [PRIMARY]
GO

現在參考這些索引欄位的查詢戲劇性的便的非常有效率,更好的是,這些增加的索引欄位並沒有減低實際的資料密度,因為他們只出現在索引中. 注意:確認不需要這些索引欄位的查詢不會外在的或隱含的使用浪費的* column selector, 因為它會不必要為每一個資料列評估這些欄位.

索引視圖

[這個功能只在Enterprise和Developer edition of SQL Server才有]

一個索引視圖,有時候代表了一個具體的視圖,是一個排列後的索引欄位,把儲存計算結果帶到了下一個階段.在一個之前的例子中我們將索引欄位組合在一起來增加資料組合的邏輯效能.我們可以把它做個改變來建立一個視圖(View)

CREATE VIEW dbo.OrdersByMonth
WITH SCHEMABINDING
AS
SELECT OrderDateYear AS [Year], OrderDateMonth AS [Month], COUNT_BIG(*) AS [Monthly Orders]
FROM dbo.Orders
GROUP BY OrderDateYear, OrderDateMonth

這個視圖(View)只是一個查詢的範本,並且只對重用程式碼有用(雖然也是個很有價值的目標).我們可以更進一步的實體化這個視圖(View),儲存結果並更改它,使更改內容能反映到實際的資料.以下的指令可建立一個索引視圖:

CREATE UNIQUE CLUSTERED INDEX IX_OrdersByMonth ON OrdersByMonth(Year,Month)

現在以下的查詢可以滿足索引視圖了,使用預先計算的值來取代每次查詢的重新計算.

SELECT [Year],[Month],[Monthly Orders] FROM OrdersByMonth WHERE Year=1997

最後我們每個月的訂單查詢使用的資源比原本的下降了90%,這還只是在一個很小的sample資料庫的結果.在真實的資料庫中結果可能差距會非常大.

索引視圖的確有缺點,像是會在資料改變的時候執行自動維護,但這可以是一個非常有用的工具,當你的工具箱中有這項工具並且你使用的平台支援它的時候.

結論 :

• 儘可能保持資料列很小, 增加資料密度
• 所有的Table都需要叢集式索引,除了一些例外. 通常單一欄位的主鍵就是索引
• 小的叢集式索引讓非叢集式索引也很小,增加資料密度
• 叢集式索引可幫助建立其他覆蓋索引
• 使用覆蓋索引非常的有效率
• 避免把標準欄位隱藏在函式中 – 他們不能用到索引
• 當適合的時候使用有索引的欄位
• 如果你已經花錢買了企業板的資料庫,認真考慮使用索引視圖(indexed view)
• 索引索引索引!只有很有限的案例中,新增與插入時維持索引的費用會超過它的好處
• 瞭解執行計畫,定期的去評估它