索引這塊從存儲結構來分，有2大類，聚集索引和非聚集索引，而非聚集索引在堆表或者在聚集索引表都會對其 鍵值有所影響，這塊可以詳細查看本系列第二篇文章：SQL SERVER大話存儲結構_(2)_非聚集索引如何查找到行記錄。 非聚集索引內又分為多類：單列索引、複合索引、包含索引、過濾索引等。之前文章有具體 ...
索引這塊從存儲結構來分，有2大類，聚集索引和非聚集索引，而非聚集索引在堆表或者在聚集索引表都會對其 鍵值有所影響，這塊可以詳細查看本系列第二篇文章：SQL SERVER大話存儲結構_(2)_非聚集索引如何查找到行記錄。 非聚集索引內又分為多類：單列索引、複合索引、包含索引、過濾索引等。之前文章有具體分析過非聚集索引的存儲情況，但是沒有對複合索引及包含索引做過多說明，本文來講講這兩個索引。
如果轉載，請註明博文來源： www.cnblogs.com/xinysu/ ，版權歸 博客園 蘇家小蘿蔔 所有。望各位支持！
1 語法及說明
--複合索引 CREATE INDEX IndexName ON tbname(columna,columnb [,columnc...] ) --包含索引 CREATE INDEX IndexName ON tbname(columna [,columnb,columnc...] ) INCLUDE (column1 [,column2,column3...])複合索引，顧名思義，及多個列組成的索引，列的順序非常重要，關係到查詢性能，這點後面會說明。 包含索引，建索引SQL 中含有 include 欄位，索引鍵值用於WHERE條件過濾，INCLUDE欄位用於 SELECT 展示，這點後面也會說明。 無論是符合索引還是包含索引，都有索引鍵值長度不能超過900位元組的限制，但是要註意一點，包含索引的include欄位是不包括在裡邊的。
2 索引頁存儲情況
從索引頁的存儲情況來分析，分析過程中，重點在查看複合索引跟包含包含索引在 子節點及葉子結點的鍵值情況。2.1 創建測試表格
創建表格 tbindex，建立兩個測試索引，同時造數據。1 CREATE TABLE tbindex( 2 id int identity(1,1) not null primary key , 3 name varchar(50) not null, 4 type varchar(10) not null, 5 numbers int not null 6 ) 7 GO 8 9 CREATE INDEX ix_number_name ON tbindex(numbers,name) 10 GO 11 CREATE INDEX ix_name ON tbindex(numbers) INCLUDE (name) 12 GO 13 14 DECLARE @ID INT 15 SET @ID=1 16 WHILE @ID<=5 17 BEGIN 18 INSERT INTO tbindex(name,type,numbers) 19 SELECT 20 name, 21 type, 22 object_id+@id 23 FROM sys.objects 24 25 SET @ID=@ID+1 26 END
2.2 分析索引行
--查看該表格索引的id情況 SELECT * FROM sys.indexes WHERE object_id=object_id('tbindex') --PK__tbindex__3213E83F89582AC3 1 --ix_number_name 2 --ix_number 3 DBCC traceon(3604) DBCC ind('dbpage','tbindex',-1) DBCC PAGE('dbpage',1,395,3) DBCC PAGE('dbpage',1,396,3) DBCC PAGE('dbpage',1,397,3) DBCC PAGE('dbpage',1,398,3)
.png)
分析查看，得知：
- 複合索引 IX_number_name的索引節點為pageid=395，再挑選一個葉子結點來分析 pageid=396；
- 包含索引 IX_number 的索引節點為 pageid=397，再挑選一個葉子節點來分析 pageid=398。
從這裡可以看出，複合索引跟包含索引的 所有索引列都會存儲在索引葉子節點跟子節點，但是包含索引 的INCLUDE列，不在索引頁的子節點存儲，僅存儲在 索引頁的葉子節點上。 從這裡不難理解，為什麼之前說 include列用於 select 列，而不用於 where 列過濾。因為非聚集索引當索引頁面有多層的時候，是先查詢 索引的子節點，再查詢索引的葉子節點，而包含索引的INCLUDE列不在葉子節點中存儲，無法根據其來進行過濾。
3 對查詢的影響
3.1 複合索引查詢註意事項
由於需要數據量作為實驗支持，所以不用之前分析索引行結構的表格tbindex，換個高大上 tb_composite 如下。
1 create table tb_composite( 2 id int identity(1,1) not null primary key, 3 name varchar(50) not null, 4 userid int not null, 5 timepoint datetime not null 6 ) 7 GO 8 9 create index ix_userid_name on tb_composite(userid,name) 10 GO 11 12 create index ix_userid on tb_composite(userid) 13 GO 14 15 INSERT INTO tb_composite(name,userid,timepoint) 16 SELECT 17 newid(),orderid%10000 ,CreatedDate 18 FROM ORDERS大數據表格 至此，測試表格建立完成，開始分析索引頁面信息，統計表格tb_composite信息如下：
1 --查看表格的數據大小跟非聚集索引大小 2 WITH DATA AS ( 3 SELECT 4 5 O.name tb_name, 6 reservedpages = SUM (reserved_page_count), 7 usedpages = SUM (used_page_count), 8 pages = SUM (CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END ), 9 rowCounts = SUM (CASE WHEN (index_id < 2) THEN row_count ELSE 0 END ) 10 FROM sys.dm_db_partition_stats S 11 JOIN sys.objects o on s.object_id=o.object_id 12 WHERE O.type='U' 13 GROUP BY O.name 14 ) 15 SELECT 16 17 tb_name, 18 rowCounts, 19 reservedpages*8/1024 reserved_Mb, 20 pages*8/1024 data_Mb, 21 index_Mb=(usedpages-pages)*8/1024, 22 unused_Mb=case when usedpages>reservedpages then 0 else (reservedpages-usedpages)*8/1024 end 23 FROM DATA 24 WHERE tb_name = 'tb_composite' 25 ORDER BY reserved_Mb DESC 26 Go
可以看到這個表格的非聚集索引總大小 ≈ 598Mb ≈ （43022+33279）*8k/1024 ≈ 596Mb 。
ix_userid_name 明顯要比 ix_userid 存儲的頁面多，這是因為 ix_userid_name 比 ix_userid 多存儲了 name 這個索引鍵值，索引頁的增加，意味著使用這個索引就會相應增加 IO 。
比如一下兩個SQL：
SET STATISTICS IO ON
--執行前，按下快捷鍵：Ctrl+M, 執行SQL後會顯示實際執行的執行計劃 （註意，Ctrl+L，則為 預估的執行計劃）
SELECT * FROM tb_composite WITH(INDEX=ix_userid_name) WHERE userid =6500
SELECT * FROM tb_composite WITH(INDEX=ix_userid) WHERE userid =6500
查看其IO情況：
.png)
走複合索引會比單列索引要多出3個IO，userid 條件的擴大這個IO差別也會逐步加大。
查看執行計劃如下：
.png)
可以看出，兩者都是先根據索引 進行 index seek 查找到相應的索引行，再根據索引行上的 主鍵，去聚集索引中進行 key lookup查找行記錄。兩者的執行計劃是一模一樣的。這裡加多一個SQL查詢。
SELECT * FROM tb_composite WHERE name='6CDC4A13-36FF-4FA2-94D0-F1CBEA40852C'
name這一列，不存在單列索引，存在於複合索引 ix_userid_name(userid,timepoint,name) 中，那麼 這個查詢能否根據 這個索引進行查找呢？
答案是：NO NO NO ，資料庫會根據其IO情況來做選擇，有兩種可能，一種是根據主鍵做全表scan，另外一種是 對 複合索引 進行 index scan 全掃描，然後再根據鍵值去 聚集索引上查找相應的 行記錄。
且看執行計劃跟IO如下，可以看出，邏輯讀基本上把所有數據頁（聚集索引葉子節點）都掃描出來，一次IO是一個8kb的data page。
.png)
.png)
來吧，總結一下：
- 最左匹配原則：複合索引 鍵值列假設為（a, b, c, d, e），則等同於索引這幾個索引：（a）、（a, b）、（a, b, c）、（a, b, c, d）、（a, b, c, d, e）
- 當where條件 符合 最左匹配原則，那麼，執行計劃則是 INDEX SEEK ，走索引查找；
- 當where條件 不符合 最左匹配原則，則根據性能評估，走primary index scan 或者 非聚集索引掃描再根據鍵值去 primary key lookup ；
- 根據最左匹配原則，可以在日常管理中，避免添加一些冗餘冗餘索引
- 但是也有一個註意事項：隨著複合索引的列增加，索引頁也會增加，使用其索引會增加一定量的IO，所以，再判斷冗餘索引的時候，需要考慮下這種情況，通常很少碰到這種情形。
3.2 複合索引與包含索引的查詢區別
前面測試已經瞭解 複合索引 跟 包含索引 的 存儲結構，這裡進行查詢測試。這裡註意 索引頁數量 = 索引節點頁+索引葉子節點頁。 先創建 包含索引表格，造數據。 CREATE TABLE tb_include( id int identity(1,1) not null primary key, name varchar(50) not null, userid int not null, timepoint datetime not null ) GO CREATE INDEX ix_userid on tb_include(userid) INCLUDE (timepoint,name) GO INSERT INTO tb_include( name , userid , timepoint ) SELECT name,userid,timepoint FROM tb_composite GO 做兩個查詢如下： SELECT USERID,name FROM tb_composite where USERID=71 SELECT USERID,name FROM tb_include where USERID=71 SELECT USERID,name FROM tb_composite where USERID=71 AND NAME='010CC1BD-1736-46A8-9497-7F4DBFD082B2' SELECT USERID,name FROM tb_include where USERID=71 AND NAME='010CC1BD-1736-46A8-9497-7F4DBFD082B2'.png)
.png)
- 如果where 條件包含include列
- include列無法參與 index seek，因為其索引子節點不存在，只存在於索引葉子節點，所以include列一般都是 展示列；
- include列由於無法做 where 過濾的 index seed，同比 複合索引，IO相對會較大
- 如果展示列僅限於索引鍵值及include列
- 包含索引中，根據索引鍵值找到 索引葉子節點後，無鬚根據主鍵值或者RID值 回表 去查詢行記錄，而是直接把 索引葉子節點的 include 列的內容展示即可，減少 回表 的IO；
- 如果where條件僅含鍵值列，select 展示列僅含 鍵值列級include列
- 兩者性能基本一致，包含索引相對少IO，但是區別不大。
- 所有非聚集索引的限制長度是900個位元組，但是 包含索引中的 include列是不計算在索引長度中的，所以如果要是遇到這種索引超過 900 bytes的特殊情況，可以考慮把相關欄位放到include中來處理。
