效能調校(1)-善用各種索引

SQL Server 2008 supports two basic types of indexes: clustered and nonclustered. Both indexes are implemented as a balanced tree, where the leaf level is the bottom level of the structure. The difference between these index types is that the clustered index is the actual table; that is, the bottom level of a clustered index contains the actual rows, including all columns, of the table. A nonclustered index, on the other hand, contains only the columns included in the index's key, plus a pointer pointing to the actual data row. If a table does not have a clustered index defined on it, it is called a heap, or an unsorted table. You could also say that a table can have one of two forms: It is either a heap (unsorted) or a clustered index (sorted).

Improving Performance with Covered Indexes

覆蓋索引（Covered Index）

The notion of a covered index (覆蓋索引) is that SQL Server doesn't need to use lookups between the nonclustered index and the table to return the query results. Because a clustered index is the actual table, clustered indexes always cover queries. To consider the index covered, it must contain all columns referenced in the query (in any clause, SELECT, JOIN, WHERE, GROUP BY, HAVING, and so on)

覆蓋索引指的是查詢陳述式中的所有用到的欄位（包含出現在 SELECT, JOIN, WHERE, GROUP BY, HAVING 子句中的欄位）都在索引裡（包含 INCLUDE ）。

Select Col1,Col2 From TableA WHERE Col1=1

考量上面這個範例，如果要索引覆蓋這個查詢，則索引至少要包含 Col1 和 Col2。以下幾個方式的索引都有覆蓋這個查詢。

CREATE Index IX_TableA on TableA(Col1, Col2)
CREATE Index IX_TableA on TableA(Col1) INCLUDE (Col2)
CREATE Index IX_TableA on TableA(Col1, Col2, Col3)

CREATE Index IX_TableA on TableA(Col2, Col1)
CREATE Index IX_TableA on TableA(Col2) INCLUDE (Col1)

所以，只要查詢陳述中的欄位都有在索引之中，不管在索引中的位置順序，或者是在 INCLUDE 欄位中，都可以算是覆蓋索引。 雖然，上面這些不同的索引設定，在執行計畫中，是有不同的執行效率，但都算覆蓋索引。

覆蓋索引範例說明

底下範例，比較覆蓋索引與非覆蓋索引的執行效率。

建立測試範例資料

Select Count(*)  From MsgTable -- 98629
--Q1～Q4
Select MsgId, MsgText From MsgTable WHERE MsgId = 1001                  --return 11 rows
Select MsgId, MsgText From MsgTable WHERE MsgId > 1000 AND MsgId < 1100 --return 1056 rows
Select MsgId, MsgText From MsgTable WHERE MsgId Between 1001 AND 1200   --return 1210 rows
Select MsgId, MsgText From MsgTable WHERE MsgId Between 1001 AND 1500   --return 2409 rows

--Noncovered index
CREATE INDEX IX_MsgTable_Noncovered ON MsgTable(MsgId)

--Covered index
CREATE INDEX IX_MsgTable_Covered ON MsgTable(MsgId) INCLUDE(MsgText)

Logical Reads

	Query1	Query2	Query 3	Query 4
No Index	2748	2748	2748	2748
Noncovered index	13	2748	2748	2748
Covered index	4	24	29	69

Execution Plan

	Query1	Query2	Query 3	Query 4
No Index	Table Scan	Table Scan	Table Scan	Table Scan
Noncovered index	Index Seek + RID Lookup	Table Scan	Table Scan	Table Scan
Covered index	Index Seek	Index Seek	Index Seek	Index Seek

RID查閱(RID Lookup)

RID查閱(RID Lookup)：透過RID（資料列識別碼，RowID)，查閱實體資料表中的資料列。

總結

由上表可以看出：

• A covered index always performs better than a noncovered index.
• For queries that return a very limited number of rows, a noncovered index also performs very well.

包含欄位（Included Column）

在探討包含欄位之前，整理一下底下這個表

物件名稱	數目上限
每個主索引鍵的資料行數	16
索引欄位大小的總和	900 Bytes
每個資料表的資料行數(非寬型)	1024
每個 SELECT 陳述式的資料行數	4096
每個 INSERT 陳述式的資料行數	4096
每個 GROUP BY、ORDER BY 的資料行	16
每份資料表的叢集索引數	1
每份資料表的非叢集索引數	249
每個索引鍵的資料行數	16
每個索引鍵的包含資料行數	1023
每個外部索引鍵的資料行數	16

由表中得知每個索引鍵最多只能有16個資料行組成，如果只靠索引的欄位要達到覆蓋索引，16個資料行數可能有所不足。 為了這個目的，SQL2005加入了包含欄位(Included Column)，可以用來避免資料表查閱(table lookup)的時間。

SQL2005 允許每個索引鍵可以有 1023 個包含欄位，而一個資料表最大也只不過 1024 個欄位，所以要達到覆蓋索引就變的可能了。

使用包含欄位注意事項：

• 包含欄位沒有限定LBO型態。
• 包含欄位不適用於 filtering, grouping 或 sorting。

用來執行 filtering, grouping 或 sorting 的欄位，應該要設成是索引鍵的一部份，其他欄位才設成索引的包含欄位。

包含欄位範例說明

建立測試範例資料

CREATE TABLE TestTable(
PKCol UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED
,Col1 INT IDENTITY NOT NULL
,Col2 CHAR(20) NOT NULL
,Col3 CHAR(20) NOT NULL
,Col4 CHAR(20) NOT NULL
,Col5 CHAR(20) NOT NULL
,Col6 CHAR(20) NOT NULL
,Col7 CHAR(20) NOT NULL
,Col8 CHAR(20) NOT NULL
,Col9 CHAR(20) NOT NULL
,Col10 CHAR(20) NOT NULL
,Col11 CHAR(20) NOT NULL
,Col12 CHAR(20) NOT NULL
,Col13 CHAR(20) NOT NULL
,Col14 CHAR(20) NOT NULL
,Col15 CHAR(20) NOT NULL
,Col16 CHAR(20) NOT NULL
);

INSERT TestTable (Col2, Col3, Col4, Col5, Col6, Col7, Col8,
Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16)
SELECT TOP(1000000)
CAST(message_id AS CHAR(20)) AS Col2
,CAST(message_id AS CHAR(20)) AS Col3
,CAST(message_id AS CHAR(20)) AS Col4
,CAST(message_id AS CHAR(20)) AS Col5
,CAST(message_id AS CHAR(20)) AS Col6
,CAST(message_id AS CHAR(20)) AS Col7
,CAST(message_id AS CHAR(20)) AS Col8
,CAST(message_id AS CHAR(20)) AS Col9
,CAST(message_id AS CHAR(20)) AS Col10
,CAST(message_id AS CHAR(20)) AS Col11
,CAST(message_id AS CHAR(20)) AS Col12
,CAST(message_id AS CHAR(20)) AS Col13
,CAST(message_id AS CHAR(20)) AS Col14
,CAST(message_id AS CHAR(20)) AS Col15
,CAST(message_id AS CHAR(20)) AS Col16
FROM sys.messages AS sm
CROSS JOIN (
SELECT TOP(15) 1 AS Col FROM sys.messages
) AS x;

CREATE NONCLUSTERED INDEX IncludedColumns ON TestTable (Col1)
INCLUDE (Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12,
Col13, Col14, Col15, Col16);

CREATE NONCLUSTERED INDEX NoIncludedColumns ON TestTable
(Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11,
Col12, Col13, Col14, Col15, Col16);

PS.若要使用 GUI 設定包含欄位，可在索引屬性中設定。

檢視索引相關資訊

使用 sys.dm_db_index_physical_stats 這個系統內建的動態管理函式可以用來檢視索引的相關資訊

SELECT index_id,index_depth,index_level,page_count,record_count,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('TestTable'),NULL,NULL,'DETAILED') AS a
WHERE index_id>1

索引大小

	IncludedColumns	NoIncludedColumns
總使用空間	40148 pages	41745 pages
非葉級索引使用空間	146 pages	1745 pages
葉級索引使用空間	40002 pages	40000 pages
索引深度	3	5
非葉級索引每列平均的大小	27 bytes	327 bytes
葉級索引每列平均的大小	321 bytes	321 bytes

• 二個索引所使用總空間大小差不多(約4%)，這是因為它們都含有相同的欄位資料
• 但是二個非葉級的索引，IncludedColumns這個索引因為只有一個索引鍵，所以大小就小很多了。
• NoIncludedColumns 這個 Index 的葉級和非葉級索引大小差不多。因為都放了相同欄位的資訊。

覆蓋索引 vs 非覆蓋索引

由前面介紹知道，要設計覆蓋索引，第一種方法是將所有欄位都放在索引鍵，另一種方法是將只會用在 SELECT 的欄位放到 INCLUDE 欄位。 底下來比較看看這二種覆蓋索引和非覆蓋索引的執行效能。

陳述式

首先建立一個非覆蓋索引，和另一個要關連的資料表。

-- Create the NotCovered index.
CREATE NONCLUSTERED INDEX NotCovered ON TestTable (Col1);

-- Create and populate the OtherTable table.
CREATE TABLE OtherTable (
	PKCol INT IDENTITY NOT NULL PRIMARY KEY
	,Col1 INT NOT NULL
);

-- Insert test data
INSERT OtherTable (Col1) SELECT Col1 FROM TestTable;

下面陳述式中 [WITH(INDEX)] 是用來強迫 SQL Server 只使用指定的索引。

SELECT O.PKCol, T.Col2
FROM OtherTable AS O
INNER JOIN TestTable AS T WITH(INDEX(IncludedColumns)) ON O.Col1 = T.Col1
WHERE O.PKCol BETWEEN 1 AND 1000;

SELECT O.PKCol, T.Col2
FROM OtherTable AS O
INNER JOIN TestTable AS T WITH(INDEX(NoIncludedColumns)) ON O.Col1 = T.Col1
WHERE O.PKCol BETWEEN 1 AND 1000;

SELECT O.PKCol, T.Col2
FROM OtherTable AS O
INNER JOIN TestTable AS T WITH(INDEX(NotCovered)) ON O.Col1 = T.Col1
WHERE O.PKCol BETWEEN 1 AND 1000;

執行計畫

覆蓋索引(使用包含欄位)

覆蓋索引(不使用包含欄位)

非覆蓋索引

Page Reads

三個陳述式的 Page Reads：

• Query 1：3292
• Query 2：5434
• Query 3：6288

數據說明：

• Query3 效率最差是因為必須額外再使用索引值去lookup資料。
• Query2 效率比 Query1 效率差約 40% ，這主要是因為該索引的深度比較大造成的。這個 40% 的差距，也大約就是它們的深度比 3:5。

Using Clustered Indexes

關於叢集索引的介紹，請看這一篇。

Using Computed Columns

計算資料行 (Computed Columns)是一種特殊的欄位，該欄位值是使用同一資料表中其他欄位的值運算而成的。詳情可參考MSDN:計算資料行（Computed Columns）。

利用計算欄位，也可以用來將 scan 改善成 seek 。

Computed Columns 範例說明

使用 Computed Columns 調校 WHERE 子句中使用函式的問題

如果我們想統計整個資料表中六月份的訂單數量，我們通常會使用底下這個範例。 然而這並不是一個合法的 SARG ，因為 WHERE 子句中使用了函式，所以會採用 scan 運算以查詢資料。

Select COUNT(1) From Orders　WHERE MONTH(OrderDate)=6

要避免這個問題，就可以採用計算資料行（Computed Columns）來調整效能。

--建立計算欄位
ALTER TABLE Orders
ADD OrderMonth AS MONTH(OrderDate);

--使用計算欄位建立索引
CREATE NONCLUSTERED INDEX OrderMonthIndex
ON Orders (OrderMonth);

這時候你就可以利用計算資料行來進行查詢，最佳化工具就會採用 seek 運算。

Select COUNT(1) From Orders WHERE OrderMonth=6

其實，此時即使你沒有使用計算資料行，最佳化工具也會使用計算資料行的索引。

As you can see, SQL Server used the index of the computed column without having a reference to it in the query. This is a great feature because it makes it possible to add computed columns and index them without having to change the queries in applications or stored procedures to use the new index.

Select COUNT(1) From Orders WHERE MONTH(OrderDate)=6

使用 Computed Columns 調校 WHERE 子句中使用 collations 轉換的問題

建立測試資料

DROP TABLE ProductNames
CREATE TABLE ProductNames (
	Name NVARCHAR(50) COLLATE Latin1_General_CI_AI
);

INSERT ProductNames (Name) VALUES (N'Öl');
INSERT ProductNames (Name) VALUES (N'Olja');
INSERT ProductNames (Name) VALUES (N'Beer');
INSERT ProductNames (Name) VALUES (N'Oil');

CREATE CLUSTERED INDEX NameIndex ON ProductNames (Name);

Query 1

使用不分腔調字的查詢

SELECT Name FROM ProductNames WHERE Name LIKE N'Ö%';

Query 2

使用 COLLATE 讓查詢區分腔調字

SELECT Name FROM ProductNames WHERE Name LIKE N'Ö%' COLLATE Latin1_General_CS_AS

這個結果與書上例子不太一樣，書上的例子是使用 clustered index scan

建立 Computed Columns

由上面結果得知，因為轉換定序的原故，使用最佳化工具會使用 scan 取代 seek 。 這時候也可以利用 Computed Columns 來改善這個問題。

  -- 加入一個區分腔調字的「計算欄位」  ALTER TABLE ProductNames  ADD Name_Latin1_General_CS_AS  AS Name COLLATE Latin1_General_CS_AS;    -- 在「計算欄位」上建立索引  CREATE NONCLUSTERED INDEX NameIndex2 ON ProductNames(Name_Latin1_General_CS_AS);    -- 執行同樣查詢SELECT Name FROM ProductNames WHERE Name LIKE N'Ö%' COLLATE Latin1_General_CS_AS

Using Indexed Views

一般使用VIEW，只是簡化查詢語法，對執行效能是沒有任何幫助的。 然而，由 SQL 2000 開始，VIEW 加入了索引支援，這可以大大的提升 VIEW 的查詢效能。

建立測試 VIEW

這個VIEW建立了客戶與購買商品的關連

CREATE VIEW dbo.vw_OrderDetail
WITH SCHEMABINDING
AS
	SELECT O.CustomerID, O.OrderID, P.ProductName, OD.Quantity, OD.UnitPrice, O.OrderDate, P.SupplierID
	FROM dbo.Products P
	INNER JOIN dbo.OrderDetails OD ON P.ProductID = OD.ProductID
	INNER JOIN dbo.Orders O ON O.OrderID = OD.OrderID

底下是查詢VIEW的執行計畫：

建立 VIEW 的索引

現在我們在 VIEW 上建立索引，但是要注意，只有使用 WITH SCHEMABINDING 建立的 VIEW ，才可以建立索引。 而且第一個建立的索引，必須是唯一叢集索引，之後才可以建立非叢集索引。

CREATE UNIQUE CLUSTERED INDEX [PK_vw_OrderDetail] ON [dbo].[vw_OrderDetail] 
(
	CustomerID, OrderID ,ProductName
)

底下是新的查詢陳述式與產生的執行計畫：

SELECT SupplierID,OrderID,ProductName FROM vw_OrderDetail WITH(NOEXPAND)
WHERE CustomerID='SAVEA'

這裡要注意一點，若不是使用 SQL Server 2008 企業版或開發版，必須加上 WITH(NOEXPAND)

Using Columnstore Index

關於 Columnstore Index 的介紹，請看這一篇。

Read Performance vs. Write Performance

待補

Analyzing Index Usage

要分析索引的使用狀況，可以使用 sys.dm_db_index_usage_stats 這個 DMV 。或者由 SSMS 的報表功能查看。

使用 sys.dm_db_index_usage_stats

select * from sys.dm_db_index_usage_stats

• user_seeks：使用 index seek 次數
• user_scans：使用 index scan 次數
• user_lookups：使用 index lookup 次數
• user_updates：索引更新次數
• last_user_seek：
• last_user_scan：
• last_user_lookup：

在查詢結果中，你應該關注的是：

• 若 user_seeks 或 user_scans 值都很低，表示這個索引很少在使用。
• 若 user_seeks 很低， user_scans 很高，表示這個索引不是一個好的索引，應該可以進一步重新規劃。
• 若 user_updates 很高，可以檢查看看，是否有不必要加入索引的欄位。

使用 SSMS 的索引統計報表

Partitioning

資料分割資料表是 SQL Server 2005 針對 Enterprise 和 Developer 版所提供的一種用來將大型資料表利用分割的功能，以水平方式拆分成數個分割（Partition），藉此提升資料載入的效能並保有資料集合的完整性。 每個分割都是獨立的，假設您是依照年度來進行資料表分割，您就可以規劃針對今年的分割進行 INSERT、UPDATE、DELETE 等資料維護作業，今年以前的分割用來做查詢用途，配合適當的硬體組態來盡可能的提升查詢效能。

詳細內容請看這篇

Tuning Indexes Automatically

SQL Server 也有提供一些自動化調校的功能。

例如：在使用圖型執行計畫時，有時候你會見到索引遺漏（Missing Index.）的提示訊息，你可以直接在提示訊息上使用右鍵功能取得建議的索引建立腳本。

或者使用「Database Engine Tuning Advisor」這個工具來分析系統所缺少的索引。這個工具的使用，可以參考這篇部落格。

• CREATE INDEX (Transact-SQL)
• Indexes on Computed Columns
• Create Indexed Views
• [WILL]如何找出 SQL Server 2008 資料庫裡是否建立了多餘的索引