索引這塊從存儲結(jié)構(gòu)來分，有2大類，聚集索引和非聚集索引，而非聚集索引在堆表或者在聚集索引表都會對其 鍵值有所影響，這塊可以詳細查看本系列第二篇文章：SQL SERVER大話存儲結(jié)構(gòu)_(2)_非聚集索引如何查找到行記錄。

    非聚集索引內(nèi)又分為多類：單列索引、復(fù)合索引、包含索引、過濾索引等。之前文章有具體分析過非聚集索引的存儲情況，但是沒有對復(fù)合索引及包含索引做過多說明，本文來講講這兩個索引。

    如果轉(zhuǎn)載，請注明博文來源： www.cnblogs.com/xinysu/   ，版權(quán)歸 博客園 蘇家小蘿卜 所有。望各位支持！

回到頂部(go to top)

1 語法及說明

--復(fù)合索引CREATE INDEX IndexName ON tbname(columna,columnb [,columnc...] ) 
--包含索引CREATE INDEX IndexName ON tbname(columna [,columnb,columnc...] ) INCLUDE (column1 [,column2,column3...])

     復(fù)合索引，顧名思義，及多個列組成的索引，列的順序非常重要，關(guān)系到查詢性能，這點后面會說明。

     包含索引，建索引SQL 中含有 include 字段，索引鍵值用于WHERE條件過濾，INCLUDE字段用于 SELECT 展示，這點后面也會說明。

     無論是符合索引還是包含索引，都有索引鍵值長度不能超過900字節(jié)的限制，但是要注意一點，包含索引的include字段是不包括在里邊的。

回到頂部(go to top)

2 索引頁存儲情況

    從索引頁的存儲情況來分析，分析過程中，重點在查看復(fù)合索引跟包含包含索引在 子節(jié)點及葉子結(jié)點的鍵值情況。

2.1 創(chuàng)建測試表格

    創(chuàng)建表格 tbindex，建立兩個測試索引，同時造數(shù)據(jù)。

 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 GO11 CREATE INDEX ix_name ON tbindex(numbers) INCLUDE (name)12 GO13  14 DECLARE @ID INT15 SET @ID=116 WHILE @ID<=517 BEGIN18      INSERT INTO tbindex(name,type,numbers)19      SELECT20            name,21          type,22          object_id+@id23      FROM sys.objects24  25         SET @ID=@ID+126 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)

    分析查看，得知：

• 復(fù)合索引 IX_number_name的索引節(jié)點為pageid=395，再挑選一個葉子結(jié)點來分析 pageid=396；

• 包含索引 IX_number 的索引節(jié)點為 pageid=397，再挑選一個葉子節(jié)點來分析 pageid=398。

--復(fù)合索引，395為索引頁節(jié)點，396為索引頁葉子節(jié)點

DBCC PAGE('dbpage',1,395,3)

DBCC PAGE('dbpage',1,396,3)

--包含索引，397為索引頁節(jié)點，398為索引頁葉子節(jié)點

DBCC PAGE('dbpage',1,397,3)

DBCC PAGE('dbpage',1,398,3)

    從這里可以看出，復(fù)合索引跟包含索引的 所有索引列都會存儲在索引葉子節(jié)點跟子節(jié)點，但是包含索引 的INCLUDE列，不在索引頁的子節(jié)點存儲，僅存儲在 索引頁的葉子節(jié)點上。

    從這里不難理解，為什么之前說 include列用于 select 列，而不用于 where 列過濾。因為非聚集索引當索引頁面有多層的時候，是先查詢 索引的子節(jié)點，再查詢索引的葉子節(jié)點，而包含索引的INCLUDE列不在葉子節(jié)點中存儲，無法根據(jù)其來進行過濾。

回到頂部(go to top)

3  對查詢的影響

3.1 復(fù)合索引查詢注意事項

     由于需要數(shù)據(jù)量作為實驗支持，所以不用之前分析索引行結(jié)構(gòu)的表格tbindex，換個高大上 tb_composite 如下。

 大數(shù)據(jù)表格

    至此，測試表格建立完成，開始分析索引頁面信息，統(tǒng)計表格tb_composite信息如下：

 1 --查看表格的數(shù)據(jù)大小跟非聚集索引大小 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 S11 JOIN sys.objects o on s.object_id=o.object_id12 WHERE O.type='U'13 GROUP BY O.name14 )15 SELECT16  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 end23 FROM DATA24 WHERE tb_name = 'tb_composite'25 ORDER BY reserved_Mb DESC26 Go

--詳細分析每一個索引的索引頁面數(shù)量

create table tbind(PageFID int,   PagePID int,IAMFID int,IAMPID int,ObjectID int,IndexID int,PartitionNumber int,PartitionID varchar(50),iam_chain_type varchar(50) ,PageType int,IndexLevel int,NextPageFID int,NextPagePID int,PrevPageFID int,PrevPagePID int )

INSERT INTO TBIND EXEC ('DBCC IND(''yaochufa'',''tb_composite'',-1) ')

SELECT

      i.name,i.index_id,p.page_nums

FROM sys.indexes i join (SELECT IndexID,count(*) page_nums FROM tbind group by IndexID ) p on i.index_id=p.IndexID

WHERE object_id=object_id('tb_composite')

ORDER BY index_id

    可以看到這個表格的非聚集索引總大小 ≈  598Mb ≈  （43022+33279）*8k/1024 ≈  596Mb 。

    ix_userid_name 明顯要比 ix_userid 存儲的頁面多，這是因為 ix_userid_name 比 ix_userid 多存儲了 name 這個索引鍵值，索引頁的增加，意味著使用這個索引就會相應(yīng)增加 IO 。

    比如一下兩個SQL：

SET STATISTICS IO ON

--執(zhí)行前，按下快捷鍵：Ctrl+M, 執(zhí)行SQL后會顯示實際執(zhí)行的執(zhí)行計劃 （注意，Ctrl+L，則為 預(yù)估的執(zhí)行計劃）

SELECT * FROM tb_composite WITH(INDEX=ix_userid_name) WHERE userid =6500

SELECT * FROM tb_composite WITH(INDEX=ix_userid) WHERE userid =6500

    查看其IO情況：

    走復(fù)合索引會比單列索引要多出3個IO，userid 條件的擴大這個IO差別也會逐步加大。

    查看執(zhí)行計劃如下：

    可以看出，兩者都是先根據(jù)索引 進行 index seek 查找到相應(yīng)的索引行，再根據(jù)索引行上的 主鍵，去聚集索引中進行 key lookup查找行記錄。兩者的執(zhí)行計劃是一模一樣的。這里加多一個SQL查詢。

SELECT * FROM tb_composite WHERE name='6CDC4A13-36FF-4FA2-94D0-F1CBEA40852C'

    name這一列，不存在單列索引，存在于復(fù)合索引 ix_userid_name(userid,timepoint,name) 中，那么 這個查詢能否根據(jù) 這個索引進行查找呢？

    答案是：NO NO NO ，數(shù)據(jù)庫會根據(jù)其IO情況來做選擇，有兩種可能，一種是根據(jù)主鍵做全表scan，另外一種是 對 復(fù)合索引 進行 index scan 全掃描，然后再根據(jù)鍵值去 聚集索引上查找相應(yīng)的 行記錄。

    且看執(zhí)行計劃跟IO如下，可以看出，邏輯讀基本上把所有數(shù)據(jù)頁（聚集索引葉子節(jié)點）都掃描出來，一次IO是一個8kb的data page。

    來吧，總結(jié)一下：

1. 最左匹配原則：復(fù)合索引 鍵值列假設(shè)為（a, b, c, d, e），則等同于索引這幾個索引：（a）、（a, b）、（a, b, c）、（a, b, c, d）、（a, b, c, d, e）

1. 當where條件 符合 最左匹配原則，那么，執(zhí)行計劃則是 INDEX SEEK ，走索引查找；

2. 當where條件 不符合 最左匹配原則，則根據(jù)性能評估，走primary index scan 或者 非聚集索引掃描再根據(jù)鍵值去 primary key lookup ；

2. 根據(jù)最左匹配原則，可以在日常管理中，避免添加一些冗余冗余索引

3. 但是也有一個注意事項：隨著復(fù)合索引的列增加，索引頁也會增加，使用其索引會增加一定量的IO，所以，再判斷冗余索引的時候，需要考慮下這種情況，通常很少碰到這種情形。

3.2 復(fù)合索引與包含索引的查詢區(qū)別

    前面測試已經(jīng)了解 復(fù)合索引 跟 包含索引 的 存儲結(jié)構(gòu)，這里進行查詢測試。這里注意 索引頁數(shù)量 = 索引節(jié)點頁+索引葉子節(jié)點頁。

    先創(chuàng)建 包含索引表格，造數(shù)據(jù)。

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

    做兩個查詢?nèi)缦拢?/p>

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'

    總結(jié)：

1. 如果where 條件包含include列

1. include列無法參與 index seek，因為其索引子節(jié)點不存在，只存在于索引葉子節(jié)點，所以include列一般都是 展示列；

2. include列由于無法做 where 過濾的 index seed，同比 復(fù)合索引，IO相對會較大

2. 如果展示列僅限于索引鍵值及include列

1. 包含索引中，根據(jù)索引鍵值找到 索引葉子節(jié)點后，無須根據(jù)主鍵值或者RID值 回表 去查詢行記錄，而是直接把 索引葉子節(jié)點的 include 列的內(nèi)容展示即可，減少 回表 的IO；

3. 如果where條件僅含鍵值列，select 展示列僅含 鍵值列級include列

1. 兩者性能基本一致，包含索引相對少IO，但是區(qū)別不大。

4. 所有非聚集索引的限制長度是900個字節(jié)，但是 包含索引中的 include列是不計算在索引長度中的，所以如果要是遇到這種索引超過 900 bytes的特殊情況，可以考慮把相關(guān)字段放到include中來處理。

如果轉(zhuǎn)載，請注明博文來源： www.cnblogs.com/xinysu/ ，版權(quán)歸 博客園 蘇家小蘿卜 所有。望各位支持！

http://www.cnblogs.com/xinysu/p/6928305.html

延伸閱讀

• LINQ TO SQL和Entity Framework 的關(guān)系 你了解多少？ 2017-07-26
• SQL Server事務(wù)的隔離級別和鎖 2017-07-21
• MyBatis-sql映射文件 2017-07-21
• SQL數(shù)據(jù)庫操作(CURD) 2017-07-13
• SQL Server AlwaysOn配置兩個節(jié)點加共享文件夾仲裁見證 2017-07-13
• SQL-結(jié)構(gòu)化查詢語言(1) 2017-07-12
• SQL執(zhí)行過程中的性能負載點 2017-07-10
• SQL Server特殊用法筆記 2017-07-07
• hive集成sentry的sql使用語法 2017-07-04
• SQL Server--獲取磁盤空間使用情況 2017-06-27

SQL Server事務(wù)的隔離級別和鎖