SQL Server 索引列的順序——真的沒關係嗎

發糞塗牆發表於2012-06-20
SQLServer索引

翻譯自：http://www.mssqltips.com/sqlservertip/2718/sql-server-index-column-order--does-it-matter/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012619

問題：

當設定表的索引時，在效能上有一個微妙的平衡：太多的索引將影響你的INSERT/UPDATE/DELETE操作。但是索引不足又將影響你的SELECT操作。本文將著眼於索引的列順序和如何影響查詢計劃及效能。

解決方案：

示例SQLServer表和資料集：

-- Tablecreation logic

CREATE TABLE[dbo].[TABLE1]

([col1][int] NOT NULL,[col2] [int]NULL,[col3] [int] NULL,[col4][varchar](50)NULL)

GO

CREATE TABLE[dbo].[TABLE2]

([col1][int] NOT NULL,[col2] [int]NULL,[col3] [int] NULL,[col4][varchar](50)NULL)

GO

ALTER TABLEdbo.TABLE1ADD CONSTRAINT PK_TABLE1 PRIMARY KEY CLUSTERED (col1)

GO

ALTER TABLEdbo.TABLE2ADD CONSTRAINT PK_TABLE2 PRIMARY KEY CLUSTERED (col1)

GO

--Populate tables

DECLARE @val INT

SELECT @val=1

WHILE @val< 1000

BEGIN 

   INSERT INTO dbo.Table1(col1,col2, col3, col4)VALUES(@val,@val,@val,'TEST')

   INSERT INTO dbo.Table2(col1,col2, col3, col4)VALUES(@val,@val,@val,'TEST')

   SELECT @val=@val+1

END

GO

--Create multi-column index on table1

CREATE NONCLUSTEREDINDEX IX_TABLE1_col2col3ONdbo.TABLE1(col2,col3)

  WITH (STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY = OFF,

        ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS = ON)

  ON [PRIMARY]

GO

在執行下面的程式碼前請先開啟執行計劃（Ctrl+M）和開啟統計IO的語句：SET STATISTICS IO ON

單表查詢例子：

在第一個例子裡面，我們將使用在where子句中的一列來查詢。第一個查詢中where子句的索引使用第二列（col3），第二個查詢使用第一列（col2）。注意這裡使用了“DBCC DROPCLEANBUFFERS”，用於確保沒有快取帶來的影響，程式碼如下：

DBCC DROPCLEANBUFFERS

GO

SELECT * FROM dbo.TABLE1 WHEREcol3=88

GO

DBCC DROPCLEANBUFFERS

GO

SELECT * FROM dbo.TABLE1 WHEREcol2=88

GO

執行後檢視執行計劃如下：

可以看到，第一個查詢使用第二列（col3）的索引是在表上執行索引掃描，且沒有用到剛才建立的索引。第二個查詢使用了表查詢，使得在表裡只需要使用更少的資源。第一個查詢讀了6次，而第二個查詢只讀了4次。

執行查詢後，你應該大概猜到，當表越來越大的時候，效能優勢就顯現出來了。

 

兩表關聯查詢例子：

在下一個例子中，查詢使用同樣的where子句，但增加了一個inner join 關聯另外一個表。第一個查詢的where子句使用col3，並使用col2來關聯表。

第二個查詢的where子句使用col2，並使用col3來關聯表。

同樣，先執行DBCC DROPCLEANBUFFERS來確保快取已經清空。程式碼如下：

DBCC DROPCLEANBUFFERS
GO
SELECT * 
  FROM dbo.TABLE1 INNER JOIN 
       dbo.TABLE2 ON dbo.TABLE1.col2 = dbo.TABLE2.col1
 WHERE dbo.TABLE1.col3=255       
GO
DBCC DROPCLEANBUFFERS
GO
SELECT * 
  FROM dbo.TABLE1 INNER JOIN 
       dbo.TABLE2 ON dbo.TABLE1.col3 = dbo.TABLE2.col1
 WHERE dbo.TABLE1.col2=255       
GO

執行計劃如下：

從執行計劃可以看到，當用於關聯表的列也在索引中，但不是第一列時，會執行索引掃描。第二個查詢中索引的第一列來關列，會使用索引查詢。從IO來看，同樣索引查詢的讀次數會更小。

總結：

從這些例子中，可以看到索引列的順序對錶的查詢也有影響。當建立索引時，先確認你總是對儘可能小的集合進行操作，這意味著索引能從where子句中的列開始。另外，對order by子句中的列和SELECT中的列建立覆蓋索引也有助於提高查詢效能。這樣可以不用在查詢時執行書籤查詢。

在前面提到的，增加太多索引將引起insert/update/delete時對這些索引列的修改。所以，找到平衡點才是最重要的。

相關文章