Sql Server關於create index include帶有包含列的索引的最全解釋

官方文件
By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:

• They can be data types not allowed as index key columns.
• They are not considered by the Database Engine when calculating the number of index key columns or index key size.

在SqlServer 2016中，當我們要給一個長度超過1700的列建立索引時，資料庫會告訴我們為這個列建索引會超過限制，而include的列是不在這個限制裡面的。

create table t1(hid int,hname varchar(4000),hname1 varchar(4000))
create index ind_hname on t1(hname) --警告Warning! The maximum key length 
for a nonclustered index is 1700 bytes. The index 'ind_hname' has maximum length of 
4000 bytes. For some combination of large values, the insert/update operation will fail.
create index ind_hname on t1(hid) include(hname) --不報錯，正常建立，索引鍵列是hid，包含列是hname

官方文件的說法：透過包含非鍵列，可以建立覆蓋更多查詢的非聚集索引。 這是因為非鍵列具有下列優點：
1、它們可以是不允許作為索引鍵列的資料型別。
2、在計算索引鍵列數或索引鍵大小時，資料庫引擎不考慮它們。
當查詢中的所有列都作為鍵列或非鍵列包含在索引中時，帶有包含性非鍵列的索引可以顯著提高查詢效能。 這樣可以實現效能提升，因為查詢最佳化器可以在索引中找到所有列值；不訪問表或聚集索引資料，從而減少磁碟 I/O 操作。

以上2點的個人理解：
1、它們可以是不允許作為索引鍵列的資料型別。比如varchar(2000)超過1700不能做索引鍵列，但是varchar(2000)可以作為包含列
2、 index key size索引鍵大小是指索引鍵包含的所有欄位的長度總和的限制，比如我有一個表create table table1 (col1 varchar(500), col2 varchar(500),col3 varchar(500), col4 varchar(500));因為indexed key size的限制是1700， 所以create index ind_t1 on table1(col1, col2,col3)沒有問題,因為col1+col2+col3=1500<1700，但是create index ind_t2 on table1(col1, col2,col3,col4)有問題，因為col1+col2+col3+col4=2000>1700,但是我們可以這樣create index ind_t3 on table1(col1, col2,col3) include (col4)，即把col4放入包含列，它就不佔索引鍵ind_t3的大小


結合以上2點的理解，得出結論
1、include包含列只能是針對非聚集索引
2、Index space<>indexed key size+include columns size, Index space是整個index段的尺寸包括include列，Index space就是整個索引佔用的磁碟空間，它包括索引鍵和非索引鍵，而不是說非索引鍵(包含列)就不佔索引段的磁碟空間
3、當查詢中的所有列就包含於索引的鍵值中，那麼就不會發生Lookup回表的操作了，因為找到索引項，就已經找到所需的資料了，沒有必要再到資料行去找了，這種情況，叫做索引覆蓋
4、索引定義中非鍵列(包含列)的順序不會影響使用該索引的查詢的效能。
5、突然多出來一個查詢，而這個查詢欄位只是比之前的查詢多一個欄位的情況下，如果之前的查詢已經有了索引，則可以把之前索引刪除，新建一個索引，再把新增的查詢欄位和之前索引中include欄位合併起來一併放入include
6、當查詢中的所有列都出現在索引(鍵列或包含列)中時，這種包含列的索引才能帶來效果，也就是說它唯一的好處就是直接查詢索引就可以返回值，不用回表，使用的範圍太窄了。一旦要透過索引再回表返回資料，那包含列太得不償失，因為包含列也佔用實際的儲存空間，這樣導致這個索引的index space太大了，成本太高了，特別是超大表，索引如果包含太多包含列的話，佔用儲存實在太可怕了。所以 include包含列是典型的空間換時間的打法，需要平衡場景使用，而不是說它一定很好

以下7、8兩點結論，額外夾帶的私活，幫忙理解索引掃描的原理
7、100GB的表，10個欄位，每個欄位平均10GB，每個欄位建立一個索引，平均每個索引10GB，掃描某個索引時，不是直接掃描整個索引這10GB，而是掃描索引樹根節點+索引樹幹節點+索引葉子節點，這樣下來找到對應的索引鍵值，可能只需要掃描不到10MB的索引，如果要回表，那再加上回表的成本
8、大表建立索引，加上online=on不會影響業務，但是會產生巨大的事務日誌，所以一般留到業務低峰比如週末操作



結論2的例子：
20210526公司發生的遇到的現象，csdb5的磁碟一下子被使用了200GB
如下語句，ATickMinutesArchive表大小254GB總計10個欄位，原來的索引180GB，如下索引一建立，發現索引達到了400GB，也就是如下新建索引有200GB，這個索引大小剛好和表大小一致，說明include的8個欄位和索引鍵的2個欄位同時計算了到了索引大小中

CREATE NONCLUSTERED INDEX NIX_ATickMinutesArchive_001  
ON [dbo].[ATickMinutesArchive] ([Symbol],[DateUpdate])  
INCLUDE ([Last],[High],[Low],[TotalVol],[TimeUpdate],[Ticks],[OpenPrice],[OpenVolume])  
WITH ( ONLINE= ON )

結論3的例子1：
例如，taable1表上有col1列(主鍵)，col2列，col3列，col4列，col5列...
現在要求作以下查詢 select col2,col3 from taable1 where col2=@col2
一般來說，在col2列上加非聚集索引，就可以加速此查詢。其查詢步驟：檢索col2列上的非聚集索引，獲取對應主鍵col1，再由主鍵檢索聚集索引，從聚集索引的葉級索引頁上獲取相關的記錄。但是，如果在col2列上加非聚集索引時指定include col3列，這樣col3列值便會儲存在非聚集索引的葉級索引頁上。其查詢步驟：檢索col2列上的非聚集索引，直接從非聚集索引的葉級索引頁上獲取相關記錄。

結論3的例子2：
如遇到如下查詢，則最後一個index_5都可以讓下面這些所有查詢語句只訪問索引就返回資料，也就是說當查詢中的所有列都作為鍵列或非鍵列包含在索引中時，直接查詢索引就可以返回結果

select col1,col2 from table1 where col1=XX
select col1,col2,col3 from table1 where col1=XX
select col1,col2,col3,col4 from table1 where col1=XX
select col1,col2,col3,col4,col5 from table1 where col1=XX
select col1,col2,col3,col4,col5,col6  from table1 where col1=XX
CREATE INDEX [index_5] ON [dbo].[table1] ([col1]) INCLUDE ([col2], [col3], 
[col4], [col5], [col6]) WITH (DATA_COMPRESSION = PAGE, ONLINE = ON)

結論4的例子：
索引定義中非鍵列的順序不會影響使用該索引的查詢的效能，比如index_5和index_6效果一樣， include後面的col2和col5順序可以隨意，可以col2在前，也可以col5在前

CREATE INDEX [index_5] ON [dbo].[table1] ([col1]) INCLUDE ([col2], [col3], 
[col4], [col5], [col6]) WITH (DATA_COMPRESSION = PAGE, ONLINE = ON)
CREATE INDEX [index_6] ON [dbo].[table1] ([col1]) INCLUDE ([col5], [col3], 
[col2], [col4], [col6]) WITH (DATA_COMPRESSION = PAGE, ONLINE = ON)

結論5的例子：
如遇到如下查詢，且已經存在index_4索引，則可以把index_4刪除，新建index_5索引，再把新增的查詢col6和之前索引中include欄位合併起來一併放入include

select col1,col2,col3,col4,col5,col6  from table1 where col1=XX
CREATE INDEX [index_4] ON [dbo].[table1] ([col1]) INCLUDE ([col2], 
[col3], [col4], [col5]) WITH (DATA_COMPRESSION = PAGE, ONLINE = ON)
CREATE INDEX [index_5] ON [dbo].[table1] ([col1]) INCLUDE ([col2], 
[col3], [col4], [col5], [col6]) WITH (DATA_COMPRESSION = PAGE, ONLINE = ON)