SQLSERVER聚集索引與非聚集索引的再次研究（下）
上篇主要說了聚集索引和簡單介紹了一下非聚集索引，相信大家一定對聚集索引和非聚集索引開始有一點了解了。
這篇文章只是作為參考，里面的觀點不一定正確
上篇的地址：SQLSERVER聚集索引與非聚集索引的再次研究（上）
下篇主要說非聚集索引
先上非聚集索引的結構圖
先創建Department8表
1 --非聚集索引 2 USE [pratice] 3 GO 4 5 CREATE TABLE Department8( 6 DepartmentID int IDENTITY(1,1) NOT NULL , 7 Name NVARCHAR(200) NOT NULL, 8 GroupName NVARCHAR(200) NOT NULL, 9 Company NVARCHAR(300), 10 ModifiedDate datetime NOT NULL DEFAULT (getdate()) 11 ) 12 13 14 CREATE NONCLUSTERED INDEX NCL_Name_GroupName ON [dbo].[Department8](Name,[GroupName]) 15 16 DECLARE @i INT 17 SET @i=1 18 WHILE @i < 100 19 BEGIN 20 INSERT INTO Department8 ( name, [Company], groupname ) 21 VALUES ( '銷售部'+CAST(@i AS VARCHAR(200)), '中國你好有限公司XX分公司', '銷售組'+CAST(@i AS VARCHAR(200)) ) 22 SET @i = @i + 1 23 END 24 25 SELECT * FROM [dbo].[Department8] 26 27 --TRUNCATE TABLE [dbo].[DBCCResult] 28 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department8,-1) ') 29 30 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
先說明一下：
PageType 分頁類型： 1：數據頁面；2：索引頁面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM頁面
IndexID 索引ID： 0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引 ，大于250就是text或image字段
每個數據頁的IndexID都是0，說明數據頁不屬于非聚集索引的一部分，如果你有看到本系列的上篇，你會看到聚集索引表里數據頁的IndexID都是1
說明數據頁屬于聚集索引的一部分，這里非聚集索引表的數據頁的IndexID不是2而是0
-------------------------------------------------華麗的分割線--------------------------------------------------
下面看一下非聚集索引的索引頁
1 DBCC TRACEON(3604,-1) 2 GO 3 DBCC PAGE([pratice],1,14499,3) 4 GO
聚集索引跟非聚集索引不同，聚集索引頁里的一行表示一個數據頁，而且標記了這個數據頁索引字段的范圍值
而非聚集索引跟數據表的記錄一一對應，非聚集索引頁里的一行記錄表示數據表的一行記錄，而且記錄了指向實際記錄的指針
其實非聚集索引的所有索引頁合并在一起就是數據表的一個縮小版（表中只有非聚集索引），索引頁中只包含創建非聚集索引時的字段，
所以當數據量少的時候，會使用全表掃描而不用索引掃描，因為堆中的數據頁包含了表的全部字段 而索引頁只包含了索引的字段，當select的時候
無論你是select * 還是select 某個字段 ，在效率上會差不多但是可以select出來的數據就會多很多
------------------------------------------------華麗的分割線--------------------------------------------------------------
那么非聚集索引是怎麼查找記錄的？
這里分兩種情況：（1）非聚集索引查找（2）非聚集索引掃描
這一次我就非聚集索引查找和非聚集索引掃描一起講了，不像《SQLSERVER聚集索引與非聚集索引的再次研究（上）》里那樣
查找和掃描分開來講
這里創建Department9表,由于Department8表只有99行記錄,數據量少的話SQLSERVER會直接走全表掃描,看不出效果
1 --非聚集索引 2 USE [pratice] 3 GO 4 5 CREATE TABLE Department9( 6 DepartmentID int IDENTITY(1,1) NOT NULL , 7 Name NVARCHAR(200) NOT NULL, 8 GroupName NVARCHAR(200) NOT NULL, 9 Company NVARCHAR(300), 10 ModifiedDate datetime NOT NULL DEFAULT (getdate()) 11 ) 12 13 14 CREATE NONCLUSTERED INDEX NCL_Name_GroupName ON [dbo].[Department9](Name,[GroupName]) 15 16 DECLARE @i INT 17 SET @i=1 18 WHILE @i < 1000000 19 BEGIN 20 INSERT INTO Department9 ( name, [Company], groupname ) 21 VALUES ( '銷售部'+CAST(@i AS VARCHAR(200)), '中國你好有限公司XX分公司', '銷售組'+CAST(@i AS VARCHAR(200)) ) 22 SET @i = @i + 1 23 END 24 25 SELECT * FROM [dbo].[Department9] 26 27 --TRUNCATE TABLE [dbo].[DBCCResult] 28 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department9,-1) ') 29 30 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
可以看到Department9表有446142行記錄,因為insert插入的時間太久了，SQLSERVER沒有執行完我就停止執行了，我的本本不給力啊！！
1 SELECT [GroupName] FROM [dbo].[Department9] WHERE name= '銷售部1' --索引查找 2 SELECT [GroupName] FROM [dbo].[Department9] WHERE [GroupName]='銷售組10' --索引掃描 3 SELECT [GroupName] FROM [dbo].[Department9] WHERE [DepartmentID]=66 --全表掃描 4 SELECT [DepartmentID],[ModifiedDate] FROM [dbo].[Department9] WHERE name= '銷售部8' --RID查找 索引查找 5 SELECT * FROM [dbo].[Department9] WHERE [GroupName]='銷售組10' --RID查找 索引掃描
大家可以用《SQLSERVER聚集索引與非聚集索引的再次研究（上）》中用到的腳本來看SQLSERVER查找記錄的過程中申請了什么鎖來推測
查找的過程
1 USE [pratice] 2 GO 3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 4 GO 5 6 BEGIN TRAN 7 SELECT [GroupName] FROM [dbo].[Department9] WHERE name= '銷售部1' ----替換相應的SQL語句 8 9 --COMMIT TRAN--當看到結果之后要commit tran，不然鎖不會釋放 10 11 USE [pratice] --要查詢申請鎖的數據庫 12 GO 13 SELECT 14 [request_session_id], 15 c.[program_name], 16 DB_NAME(c.[dbid]) AS dbname, 17 [resource_type], 18 [request_status], 19 [request_mode], 20 [resource_description],OBJECT_NAME(p.[object_id]) AS objectname, 21 p.[index_id] 22 FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p 23 ON a.[resource_associated_entity_id]=p.[hobt_id] 24 LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid] 25 WHERE c.[dbid]=DB_ID('pratice') AND a.[request_session_id]=@@SPID ----要查詢申請鎖的數據庫 26 ORDER BY [request_session_id],[resource_type]
因為聚集索引頁的每一行記錄只記錄了聚集索引字段在數據頁的范圍
但是這里有一個問題：為什麼非聚集索引掃描沒有到堆中的數據頁里去掃描呢?而在索引頁里掃描？
既然在索引頁里掃描和在數據頁里掃描大家都是漫無目的地去掃描，那么到堆中的數據頁里去掃描不是更好？？因為堆中的數據頁包含了
記錄的所有字段，而索引頁只包含了創建非聚集索引時所包含的字段
因為非聚集索引掃描的前提是：where 后面要查找的字段不是建立索引時的第一個字段（不是索引查找），但是要查找的字段是包含創建非聚集索引時
的字段列中，這個字段已經保存在非聚集索引的索引頁里，例子里就是GroupName列
CREATE NONCLUSTERED INDEX NCL_Name_GroupName ON [dbo].[Department9](Name,[GroupName])
又因為在一個表中索引頁一般會等于或者小于數據頁，所以在非聚集索引頁里掃描會比堆里的數據頁里
掃描的時間快，掃描的次數少（當數據量很多的時候）
-----------------------------------------------------------------------------------------------------------------------------------
SELECT [GroupName] FROM [dbo].[Department9] WHERE name= '銷售部1' --索引查找
為什麼上面這條語句是索引查找，而沒有RID查找？因為索引建立在GroupName和NAME上
1 CREATE NONCLUSTERED INDEX NCL_Name_GroupName ON [dbo].[Department9](Name,[GroupName])
再看一下剛才給出的非聚集索引頁結構
SELECT [GroupName] FROM [dbo].[Department9] WHERE [GroupName]='銷售組10' --索引掃描
也是根據《SQLSERVER聚集索引與非聚集索引的再次研究（上）》里提到的
因為GroupName不是非聚集索引的第一個字段，所以只能用索引掃描
因為不知道key，所以SQLSERVER只能掃描所有索引頁直到找到[GroupName]='銷售組10'，但是因為[GroupName]就存儲在
索引頁，所以沒有RID查找
SELECT [GroupName] FROM [dbo].[Department9] WHERE [DepartmentID]=66 --全表掃描
因為DepartmentID不在非聚集索引里，所以SQLSERVER只能全表掃描
SELECT [DepartmentID],[ModifiedDate] FROM [dbo].[Department9] WHERE name= '銷售部8' --RID查找 索引查找
因為非聚集索引不包括[DepartmentID],[ModifiedDate]這兩個字段，所以SQLSERVER先索引查找，在索引頁里找出name= '銷售部8'的那條記錄
然后根據name= '銷售部8'的那條記錄存儲的HEAP RID(key) 值，在數據頁里找到name= '銷售部8' 這條記錄，然后把其他字段讀出來
實際上HEAP RID(key) 存儲的就是指向數據頁的指針，直接指向數據頁里name= '銷售部8' 這條記錄
SELECT * FROM [dbo].[Department9] WHERE [GroupName]='銷售組10' --RID查找 索引掃描
其實這條語句的前半部分查找過程跟SELECT [GroupName] FROM [dbo].[Department9] WHERE [GroupName]='銷售組10' --索引掃描
這條語句是一樣的，因為其他字段不在非聚集索引的索引頁里，所以需要利用HEAP RID(key) 值找到記錄所在的數據頁然后把其他字段的值讀出來
補充實驗
為了驗證下面這句話
因為在一個表中索引頁一般會等于或者小于數據頁，所以在非聚集索引頁里掃描會比堆里的數據頁里
掃描的時間快，掃描的次數少（當數據量很多的時候）
創建CompareNonclusteredScan表 ,CompareNonclusteredScan表跟Department9表是一樣的,只是沒有添加任何索引
1 --非聚集索引掃描和全表掃描比較 2 USE [pratice] 3 GO 4 5 CREATE TABLE CompareNonclusteredScan( 6 DepartmentID int IDENTITY(1,1) NOT NULL , 7 Name NVARCHAR(200) NOT NULL, 8 GroupName NVARCHAR(200) NOT NULL, 9 Company NVARCHAR(300), 10 ModifiedDate datetime NOT NULL DEFAULT (getdate()) 11 ) 12 13 14 DECLARE @i INT 15 SET @i=1 16 WHILE @i < 1000000 17 BEGIN 18 INSERT INTO CompareNonclusteredScan ( name, [Company], groupname ) 19 VALUES ( '銷售部'+CAST(@i AS VARCHAR(200)), '中國你好有限公司XX分公司', '銷售組'+CAST(@i AS VARCHAR(200)) ) 20 SET @i = @i + 1 21 END 22 23 SELECT * FROM [dbo].CompareNonclusteredScan
1 --堆表掃描統計 2 USE [pratice] 3 GO 4 DBCC DROPCLEANBUFFERS 5 GO 6 SET STATISTICS IO ON 7 GO 8 SET STATISTICS TIME ON 9 GO 10 11 SELECT [GroupName] FROM CompareNonclusteredScan WHERE [GroupName]='銷售組10'
1 --非聚集索引表掃描統計 2 USE [pratice] 3 GO 4 DBCC DROPCLEANBUFFERS 5 GO 6 SET STATISTICS IO ON 7 GO 8 SET STATISTICS TIME ON 9 GO 10 11 SELECT [GroupName] FROM [dbo].[Department9] WHERE [GroupName]='銷售組10'
我們就比較掃描的時候，堆表和非聚集索引表 所用IO 和所用時間的情況
Department9表的統計情況
1 SQL Server 分析和編譯時間: 2 CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。 3 4 (1 行受影響) 5 表 'Department9'。掃描計數 1，邏輯讀取 4313 次，物理讀取 3 次，預讀 4304 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 6 7 SQL Server 執行時間: 8 CPU 時間 = 219 毫秒，占用時間 = 1593 毫秒。
CompareNonclusteredScan表的統計情況
1 SQL Server 分析和編譯時間: 2 CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。 3 4 (1 行受影響) 5 表 'CompareNonclusteredScan'。掃描計數 5，邏輯讀取 8766 次，物理讀取 0 次，預讀 8756 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 6 7 SQL Server 執行時間: 8 CPU 時間 = 296 毫秒，占用時間 = 1059 毫秒。
當我不執行DBCC DROPCLEANBUFFERS 不清空緩存
1 SQL Server 分析和編譯時間: 2 CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。 3 4 (1 行受影響) 5 表 'Department9'。掃描計數 1，邏輯讀取 4313 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 6 7 SQL Server 執行時間: 8 CPU 時間 = 78 毫秒，占用時間 = 73 毫秒。
1 SQL Server 分析和編譯時間: 2 CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。 3 4 (1 行受影響) 5 表 'CompareNonclusteredScan'。掃描計數 5，邏輯讀取 8766 次，物理讀取 0 次，預讀 8756 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 6 7 SQL Server 執行時間: 8 CPU 時間 = 250 毫秒，占用時間 = 995 毫秒。
為什麼邏輯讀的次數、掃描的次數 、占用時間會相差這麼多？？
其實原因很簡單
一般在一張表里面索引頁面都會比數據頁面少，比如一個表有100行記錄，非聚集索引頁面用一個頁面就裝下100行記錄
數據頁面一個頁面只能裝下50個，需要用兩個數據頁才能裝得下所有數據
如下圖，一個只有非聚集索引的表，數據頁面有11個，非聚集索引頁面有9個
如果要找一行記錄，如果掃描數據頁可能要掃描到第二頁才能找到那條記錄，如果掃描非聚集索引頁，只需要掃描一個非聚集索引頁就可以了
先讀取非聚集索引頁面（邏輯讀取）-》再掃描非聚集索引頁面（掃描計數）
所以邏輯讀取、掃描計數、占用時間跟堆表相差這麼大的原因就是這個
還有下面這個SQL語句也是索引掃描，不加where 篩選條件，因為GroupName包含在非聚集索引中，所以掃描非聚集索引頁面比掃描數據頁面的效率高
1 SELECT [GroupName] FROM [dbo].[Department9] --索引掃描
現在大家可以看出來掃描索引頁和掃描數據頁的優勢了吧??
那么非聚集索引是不是一定會掃描非聚集索引頁呢（當數據量很少的時候）？？？
在有聚集索引的表里，只插入少量記錄，表中是不會產生聚集索引頁的，因為聚集索引掃描是掃描數據頁不會掃描聚集索引頁
那么非聚集索引會不會跟聚集索引一樣呢？？
先drop掉Department9表，然后重新建立Department9表，建表腳本跟剛才一樣，只插入5條記錄
結果還是會生成非聚集索引頁，就是說無論什么情況，非聚集索引只會掃描非聚集索引頁
-------------------------------------------華麗的分割線------------------------------------------------------
覆蓋索引 INCLUDE（）
覆蓋索引只能建立在非聚集索引上，那么覆蓋索引是怎樣的呢？
建立Department10表
1 --覆蓋索引 2 USE [pratice] 3 GO 4 DROP TABLE [dbo].[Department10] 5 CREATE TABLE Department10( 6 DepartmentID int IDENTITY(1,1) NOT NULL , 7 Name NVARCHAR(200) NOT NULL, 8 GroupName NVARCHAR(200) NOT NULL, 9 Company NVARCHAR(300), 10 ModifiedDate datetime NOT NULL DEFAULT (getdate()) 11 ) 12 13 14 CREATE INDEX NCL_Name_GroupName ON [dbo].[Department10](Name,[GroupName]) INCLUDE(ModifiedDate) 15 16 17 DECLARE @i INT 18 SET @i=1 19 WHILE @i < 10000 20 BEGIN 21 INSERT INTO Department10( name, [Company], groupname ) 22 VALUES ( '銷售部'+CAST(@i AS VARCHAR(200)), '中國你好有限公司XX分公司', '銷售組'+CAST(@i AS VARCHAR(200)) ) 23 SET @i = @i + 1 24 WAITFOR DELAY '00:00:00:100' 25 END 26 27 SELECT * FROM [dbo].[Department10] 28 29 --TRUNCATE TABLE [dbo].[DBCCResult] 30 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department10,-1) ') 31 32 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
查看一下索引頁14549
你會發現覆蓋索引跟非聚集索引的索引頁結構是一樣的，只不過多了ModifiedDate列，但是ModifiedDate列沒有在旁邊加上（key）
大家注意看
那么既然覆蓋索引只是在索引頁加上一個字段，那么倒不如創建非聚集索引的時候,把ModifiedDate列也納入到非聚集索引中
1 CREATE INDEX NCL_Name_GroupName ON [dbo].[Department10](Name,[GroupName],[ModifiedDate])
究竟覆蓋索引有什么存在的價值呢？？？
我們看一下MSDN的解釋
http://msdn.microsoft.com/zh-cn/library/ms190806(SQL.90).aspx
大家注意看里面其中一句話
看了MSND的介紹，本人覺得覆蓋索引最大的優勢是突破了索引列大小的限制，將盡可能多的列（字段）放到索引頁，
這樣查詢數據的時候就可以盡量使用索引掃描而不用RID查找或全表掃描，覆蓋索引其他特別的用途或者特點或者優勢就找不到了
還有MSDN里面提到“只能對表或索引視圖的非聚集索引定義非鍵列”，為什麼聚集索引不能使用覆蓋索引？？
如果聚集索引掃描的是數據頁，那么就算你把覆蓋索引加到聚集索引的索引頁也沒有用，因為SQLSERVER使用聚集索引掃描的時候掃描的
是數據頁而不像非聚集索引那樣掃描的是索引頁
--------------------------------------------------華麗的分割線-------------------------------------------------------
聚集索引和非聚集索引并存
1 --非聚集索引和聚集索引 2 USE [pratice] 3 GO 4 5 CREATE TABLE Department11( 6 DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY, 7 Name NVARCHAR(200) NOT NULL, 8 GroupName NVARCHAR(200) NOT NULL, 9 Company NVARCHAR(300), 10 ModifiedDate datetime NOT NULL DEFAULT (getdate()) 11 ) 12 13 CREATE NONCLUSTERED INDEX NCL_Name_GroupName ON [dbo].[Department11](Name,[GroupName]) 14 15 DECLARE @i INT 16 SET @i=1 17 WHILE @i < 1000 18 BEGIN 19 INSERT INTO Department11( name, [Company], groupname ) 20 VALUES ( '銷售部'+CAST(@i AS VARCHAR(200)), '中國你好有限公司XX分公司', '銷售組'+CAST(@i AS VARCHAR(200)) ) 21 SET @i = @i + 1 22 END 23 24 SELECT * FROM [dbo].[Department11] 25 26 --TRUNCATE TABLE [dbo].[DBCCResult] 27 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department11,-1) ') 28 29 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
先來看一下非聚集索引頁，可以看到非聚集索引中多了一個字段就是建立聚集索引時的第一個字段DepartmentID
再來看聚集索引頁
MSDN中的解釋：http://msdn.microsoft.com/zh-cn/library/ms177484(v=SQL.105).aspx
如果表有聚集索引或索引視圖上有聚集索引，則行定位器是行的聚集索引鍵。
如果聚集索引不是唯一的索引，SQL Server 將添加在內部生成的值（稱為唯一值）以使所有重復鍵唯一。
此四字節的值對于用戶不可見。僅當需要使聚集鍵唯一以用于非聚集索引中時，才添加該值。
SQL Server 通過使用存儲在非聚集索引的葉行內的聚集索引鍵搜索聚集索引來檢索數據行
根據MSDN的解釋，實際上相當于在非聚集索引上建立多一個字段，而這個字段就是聯系聚集索引和非聚集索引的橋梁
1 CREATE NONCLUSTERED INDEX NCL_Name_GroupName ON [dbo].[Department11](Name,[GroupName],[DepartmentID])
那么當要查詢的字段不在非聚集索引的索引頁的時候，那么就要到聚集索引的葉子節點（數據頁）去找記錄，那么這個查找記錄的過程是怎樣的呢？
這個查找記錄的過程實際上就是“書簽查找”，在本文章的下面會講到
-------------------------------------------------華麗的分割線-------------------------------------------------------------
網上有人說,只有堆表才有IAM頁，并且IAM頁面維護著數據頁的前后順序，那么索引頁是不是就沒有IAM頁維護索引頁面的前后順序呢？
有索引的表的數據頁有沒有IAM頁來維護數據頁的前后順序呢？
MSDN中的解釋：
http://msdn.microsoft.com/zh-cn/library/ms189051%28SQL.90%29.aspx
表、索引或索引視圖分區的頁分配由一個 IAM 頁鏈管理。sys.system_internals_allocation_units 中的 first_iam_page 列指向 IAM 頁鏈（用于管理分配給 IN_ROW_DATA 分配單元中的表、索引或索引視圖的空間）中的第一個 IAM 頁。
sys.partitions 為表或索引中每個分區返回一行。
堆在 sys.partitions 中有一行，其 index_id = 0。
sys.system_internals_allocation_units 中的 first_iam_page 列指向指定分區中堆數據頁集合的 IAM 鏈。服務器使用 IAM 頁查找數據頁集合中的頁，因為這些頁沒有鏈接。
表或視圖的聚集索引在 sys.partitions 中有一行，其 index_id = 1。
sys.system_internals_allocation_units 中的 root_page 列指向指定分區內聚集索引 B 樹的頂端。服務器使用索引 B 樹查找分區中的數據頁。
為表或視圖創建的每個非聚集索引在 sys.partitions 中有一行，其 index_id > 1。
sys.system_internals_allocation_units 中的 root_page 列指向指定分區內非聚集索引 B 樹的頂端。
至少有一個 LOB 列的每個表在 sys.partitions 中也有一行，其 index_id > 250。
first_iam_page 列指向管理 LOB_DATA 分配單元中的頁的 IAM 頁鏈。
再說明一下在DBCC IND的結果中PageType 字段和IndexID字段的含義：
PageType 分頁類型： 1：數據頁面；2：索引頁面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM頁面
IndexID 索引ID： 0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引 ，大于250就是text或image字段
-------------------------------------------------------華麗的分割線----------------------------------------------------
書簽查找 BookMark Lookup
建立Department12表
1 --非聚集索引和聚集索引 書簽查找 2 USE [pratice] 3 GO 4 5 CREATE TABLE Department12( 6 DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY, 7 Name NVARCHAR(200) NOT NULL, 8 GroupName NVARCHAR(200) NOT NULL, 9 Company NVARCHAR(300), 10 ModifiedDate datetime NOT NULL DEFAULT (getdate()) 11 ) 12 13 CREATE NONCLUSTERED INDEX NCL_Name_GroupName ON [dbo].[Department12](Name) 14 15 DECLARE @i INT 16 SET @i=1 17 WHILE @i < 10000 18 BEGIN 19 INSERT INTO Department12( name, [Company], groupname ) 20 VALUES ( '銷售部'+CAST(@i AS VARCHAR(200)), '中國你好有限公司XX分公司', '銷售組'+CAST(@i AS VARCHAR(200)) ) 21 SET @i = @i + 1 22 END 23 24 SELECT * FROM [dbo].[Department12] 25 26 --TRUNCATE TABLE [dbo].[DBCCResult] 27 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department12,-1) ') 28 29 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
書簽查找的主體是“非聚集索引”
所以書簽查找只會出現在下面兩種表中：
（1）只有非聚集索引的表
（2）聚集索引和非聚集索引并存的表
而只有聚集索引的表是不會出現書簽查找的
為什麼非聚集索引才會出現書簽查找？？？
大家可以再看一下非聚集索引的結構圖和MSDN的定義
而書簽查找的定義：
MSDN定義：
定義：當查詢優化器使用非聚集索引進行查找時，如果所選擇的列或查詢條件中的列只部分包含在使用的非聚集索引和聚集索引中時，就需要一個查找（lookup）來檢索其他字段來滿足請求。對一個有聚簇索引的表來說是一個鍵查找（key lookup），對一個堆表來說是一個RID查找（RID lookup），這種查找即是——書簽查找(bookmark lookup)。簡單的說就是當你使用的sql查詢條件和select返回的列沒有完全包含在索引列中時就會發生書簽查找
因為無論是（1）只有非聚集索引的表 還是（2）聚集索引和非聚集索引并存的表 數據頁都不是非聚集索引的一部分
所以如果所查找的數據不在非聚集索引的索引頁就需要到數據頁去取數據，這種情況就叫“書簽查找”
其實數據表里的數據就像書本里的內容，而非聚集索引就像書簽，因為書本里的內容不可能全部在書簽里，但是要找到書本里的內容需要書簽去定位
其實簡單來講，就四種情況：
我們再回到Department9表
第一種
1 SELECT [DepartmentID],[ModifiedDate] FROM [dbo].[Department9] WHERE name= '銷售部8' --RID查找 索引查找
前面說到的:
因為非聚集索引不包括[DepartmentID],[ModifiedDate]這兩個字段，所以SQLSERVER先索引查找，在索引頁里找出name= '銷售部8'的那條記錄
然后根據name= '銷售部8'的那條記錄存儲的HEAP RID(key) 值，在數據頁里找到name= '銷售部8' 這條記錄，然后把其他字段讀出來
實際上HEAP RID(key) 存儲的就是指向數據頁的指針，直接指向數據頁里name= '銷售部8' 這條記錄
第二種
1 SELECT * FROM [dbo].[Department9] WHERE [GroupName]='銷售組10' --RID查找 索引掃描
前面說到的：
其實這條語句的前半部分查找過程跟SELECT [GroupName] FROM [dbo].[Department9] WHERE [GroupName]='銷售組10' --索引掃描
這條語句是一樣的，因為其他字段不在非聚集索引的索引頁里，所以需要利用HEAP RID(key) 值找到記錄所在的數據頁然后把其他字段的值讀出來
第三種
我們先建立Department13表 ，Department13表和Department11表的表結構是一樣的
不過在Department13表里添加了1000000條記錄，因為記錄不夠多（數據量很少）會走聚集索引掃描
1 --非聚集索引和聚集索引 2 USE [pratice] 3 GO 4 5 CREATE TABLE Department13( 6 DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY, 7 Name NVARCHAR(200) NOT NULL, 8 GroupName NVARCHAR(200) NOT NULL, 9 Company NVARCHAR(300), 10 ModifiedDate datetime NOT NULL DEFAULT (getdate()) 11 ) 12 13 CREATE NONCLUSTERED INDEX NCL_Name_GroupName ON [dbo].[Department13](Name,[GroupName]) 14 15 DECLARE @i INT 16 SET @i=1 17 WHILE @i < 1000000 18 BEGIN 19 INSERT INTO Department13( name, [Company], groupname ) 20 VALUES ( '銷售部'+CAST(@i AS VARCHAR(200)), '中國你好有限公司XX分公司', '銷售組'+CAST(@i AS VARCHAR(200)) ) 21 SET @i = @i + 1 22 END 23 24 SELECT * FROM [dbo].[Department13]
1 SELECT [ModifiedDate] FROM [Department13] WHERE GroupName='銷售組168' --索引掃描 鍵查找 并行
那么第三種情況的查找過程是怎樣的？？
先用之前用到的測試語句測試一下用了什么鎖
1 USE [pratice] 2 GO 3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 4 GO 5 6 BEGIN TRAN 7 SELECT [ModifiedDate] FROM [Department13] WHERE GroupName='銷售組168' --索引掃描 鍵查找 并行 8 9 --COMMIT TRAN 10 11 USE [pratice] --要查詢申請鎖的數據庫 12 GO 13 SELECT 14 [request_session_id], 15 c.[program_name], 16 DB_NAME(c.[dbid]) AS dbname, 17 [resource_type], 18 [request_status], 19 [request_mode], 20 [resource_description],OBJECT_NAME(p.[object_id]) AS objectname, 21 p.[index_id] 22 FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p 23 ON a.[resource_associated_entity_id]=p.[hobt_id] 24 LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid] 25 WHERE c.[dbid]=DB_ID('pratice') AND a.[request_session_id]=@@SPID ----要查詢申請鎖的數據庫 26 ORDER BY [request_session_id],[resource_type]
大家可以看到執行計劃里面索引掃描的開銷是96% ，鍵查找的開銷是0%
因為SQLSERVER要掃描每一個非聚集索引頁，當找到GroupName='銷售組168'的那條記錄的時候，自然就知道[DepartmentID]的值
這里GroupName='銷售組168'的那條記錄DepartmentID的值是168
然后根據[DepartmentID]的值去聚集索引頁里找記錄，但是聚集索引頁里的每行記錄只記錄了聚集索引鍵（DepartmentID）的范圍值
那怎么找呢？
先看一下Department13表的聚集索引頁
1 --先清空[DBCCResult]表里的記錄 2 --TRUNCATE TABLE [dbo].[DBCCResult] 3 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,Department13,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
可以看到每隔120條記錄為一行，即是每個數據頁有120條記錄
剛才說到：然后根據[DepartmentID]的值去聚集索引頁里找記錄，但是聚集索引頁里的每行記錄只記錄了聚集索引鍵（DepartmentID）的范圍值
那怎么找呢？
答案是匹配查找，SQLSERVER需要在聚集索引頁里找到168究竟在哪個范圍之內，逐個匹配，當匹配到108~209這個范圍之后
就到14554的數據頁里找數據，把[ModifiedDate]的值讀出來
因為要用到匹配查找，所以這里會有嵌套循環inner join，又因為數據有點多，所以需要用到并行
因為這里只需要在每個非聚集索引頁里逐行記錄逐行記錄掃描，而不需要在聚集索引頁里逐行記錄去查找（就是說聚集索引什么動作都不用做，
等非聚集索引把GroupName='銷售組168'那條記錄讀出來把DepartmentID也讀出來然后與聚集索引頁里的記錄逐個匹配就可以了）
所以開銷是0%
第四種
1 SELECT [ModifiedDate] FROM [Department13] WHERE [Name]='銷售部168' --索引查找 鍵查找
前面說到的:
第四種的后半部分查找過程其實跟第三種SELECT [ModifiedDate] FROM [Department13] WHERE GroupName='銷售組168' --索引掃描 鍵查找 并行
是一樣的，而前半部分的索引查找過程就不說了，前面已經說過了
解決方法：
至于書簽查找的解決辦法，網上很多說使用覆蓋索引，實際上這個解決方法只是對了一半
因為前面在講覆蓋索引的時候說到“當你當前索引的列數超過16列或最大索引鍵大小超過900字節”才考慮使用覆蓋索引，如果你當前表中的
非聚集索引還沒有達到這個限制可以把要包含的列納入到索引中來
例子里就是[ModifiedDate]字段
1 DROP INDEX [dbo].[Department13].[NCL_Name_GroupName] 2 CREATE NONCLUSTERED INDEX NCL_Name_GroupName ON [dbo].[Department13](Name,[GroupName],[ModifiedDate]) 3 4 SELECT [ModifiedDate] FROM [Department13] WHERE GroupName='銷售組168' --索引掃描
把[ModifiedDate]字段 納入到非聚集索引之后就變成了索引掃描
可以看到已經把[ModifiedDate]字段放到非聚集索引頁里去了
對于Department9表
1 DROP INDEX [dbo].[Department9].[NCL_Name_GroupName] 2 CREATE NONCLUSTERED INDEX NCL_Name_GroupName ON [dbo].[Department9](Name,[GroupName],[ModifiedDate],[DepartmentID],[Company])
已經把需要查詢到的字段放到非聚集索引頁里去了
--------------------------------------------------------華麗的分割線----------------------------------------------------
MSDN參考資料：
大家看完這兩篇文章之后可以到
園子里懶惰的肥兔大俠寫的文章里看一下他畫的圖跟本人畫的圖的差別，判斷一下懶惰的肥兔大俠畫的圖的正確性
至于懶惰的肥兔大俠畫的圖是對的還是錯的本人不作評論，因為本人畫的圖也有可能是錯的o(∩_∩)o
---------------------------------------------------華麗的分割線--------------------------------------------------
這兩篇文章寫完了，松一口氣了，斷斷續續用了4天時間差不多用了20個小時來寫，不停找資料，畫草圖，希望各位看官可以給個推薦o(∩_∩)o
如有不對的地方，也歡迎強烈拍磚o(∩_∩)o
----------------------------------------------------------------------------------
2013-8-18 補充：
關于覆蓋索引的列只存在于葉子節點索引頁，根節點的索引頁里是不存在覆蓋索引的列的
覆蓋索引根節點和葉子節點的區別，根節點索引頁面會有ChildFileId和ChildPageId來連接葉子節點
以下來自聯機叢書
“通過將包含列（稱為非鍵列）添加到索引的葉級， 可以擴展非聚集索引的功能。
鍵列存儲在非聚集索引的所有級別，而非鍵列僅存儲在葉級別。”
更詳細的大家可以看一下這篇文章：T-SQL查詢高級--理解SQL SERVER中非聚集索引的覆蓋，連接，交叉和過濾
在文章的評論里有講到“非鍵列僅存儲在葉級別”
------------------------------------------------------------------------------------------
2013-9-15 補充：
如何查看非聚集索引頁面的內容，使用DBCC PAGE的時候使用1這個格式就可以了
表中有兩條數據記錄，因此非聚集索引也有兩條記錄，而且兩套記錄的Record Type = INDEX_RECORD
1 DBCC TRACEON(3604,-1) 2 GO 3 DBCC PAGE([pratice],1,13571,1) 4 GO
1 PAGE: (1:13571) 2 3 4 BUFFER: 5 6 7 BUF @0x03E05DA4 8 9 bpage = 0x1748A000 bhash = 0x00000000 bpageno = (1:13571) 10 bdbid = 5 breferences = 0 bUse1 = 3652 11 bstat = 0x2c0000b blog = 0x2159bbcb bnext = 0x00000000 12 13 PAGE HEADER: 14 15 16 Page @0x1748A000 17 18 m_pageId = (1:13571) m_headerVersion = 1 m_type = 2 19 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4 20 m_objId (AllocUnitId.idObj) = 537 m_indexId (AllocUnitId.idInd) = 256 21 Metadata: AllocUnitId = 72057594073120768 22 Metadata: PartitionId = 72057594060931072 Metadata: IndexId = 3 23 Metadata: ObjectId = 1399676034 m_prevPage = (0:0) m_nextPage = (0:0) 24 pminlen = 13 m_slotCnt = 2 m_freeCnt = 8060 25 m_freeData = 128 m_reservedCnt = 0 m_lsn = (3045:23318:110) 26 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 27 m_tornBits = 0 28 29 Allocation Status 30 31 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED 32 PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED 33 ML (1:7) = NOT MIN_LOGGED 34 35 DATA: 36 37 38 Slot 0, Offset 0x60, Length 16, DumpStyle BYTE 39 40 Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP 41 Memory Dump @0x09F3C060 42 43 00000000: 16010000 009f3800 00010000 000200fc †......8......... 44 45 Slot 1, Offset 0x70, Length 16, DumpStyle BYTE 46 47 Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP 48 Memory Dump @0x09F3C070 49 50 00000000: 16020000 009f3800 00010001 000200fc †......8......... 51 52 OFFSET TABLE: 53 54 Row - Offset 55 1 (0x1) - 112 (0x70) 56 0 (0x0) - 96 (0x60) 57 58 59 DBCC 執行完畢。如果 DBCC 輸出了錯誤信息，請與系統管理員聯系。
----------------------------------------------------------------------------------------------
2013-10-18 補充
在原先只有非聚集索引的表上加聚集索引之后，除了所有索引頁和數據頁重新調整外，數據頁會比沒有加聚集索引前少了
為了證明這個，使用下面腳本
1 USE [pratice] 2 GO 3 4 --建表 5 CREATE TABLE ct1(c1 INT,c2 INT, c3 VARCHAR (2000)); 6 GO 7 8 9 --建立非聚集索引 10 CREATE INDEX nt1c1 ON ct1(c2); 11 GO 12 13 14 --插入測試數據 15 DECLARE @a INT; 16 SELECT @a = 1; 17 WHILE (@a <= 1000) 18 BEGIN 19 INSERT INTO ct1 VALUES (@a,@a, replicate('a', 2000)) 20 SELECT @a = @a + 1 21 END 22 GO 23 24 25 26 --查詢數據 27 SELECT * FROM ct1
看一下數據頁
1 CREATE TABLE DBCCResult ( 2 PageFID NVARCHAR(200), 3 PagePID NVARCHAR(200), 4 IAMFID NVARCHAR(200), 5 IAMPID NVARCHAR(200), 6 ObjectID NVARCHAR(200), 7 IndexID NVARCHAR(200), 8 PartitionNumber NVARCHAR(200), 9 PartitionID NVARCHAR(200), 10 iam_chain_type NVARCHAR(200), 11 PageType NVARCHAR(200), 12 IndexLevel NVARCHAR(200), 13 NextPageFID NVARCHAR(200), 14 NextPagePID NVARCHAR(200), 15 PrevPageFID NVARCHAR(200), 16 PrevPagePID NVARCHAR(200) 17 ) 18 19 --TRUNCATE TABLE [dbo].[DBCCResult] 20 -- 21 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,ct1,-1) ') 22 23 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
查詢到有345行記錄,去除兩個IAM頁面, 非聚集索引頁面和數據頁有343個
這時候的非聚集索引頁面
1 DBCC TRACEON(3604,-1) 2 GO 3 DBCC PAGE([pratice],1,110,3) 4 GO
建立聚集索引
1 --建立聚集索引 2 CREATE CLUSTERED INDEX t1c1 ON ct1(c1); 3 GO
再看一下頁面情況
1 --TRUNCATE TABLE [dbo].[DBCCResult] 2 -- 3 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,ct1,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 6 7 SELECT COUNT(*) FROM [dbo].[DBCCResult]
只有256行記錄,取出兩個IAM頁面, 也就是有254個數據頁和索引頁面
看一下非聚集索引頁面
1 DBCC TRACEON(3604,-1) 2 GO 3 DBCC PAGE([pratice],1,14495,3) 4 GO
會看到多了UNIQUIFIER(KEY)列，這個很正常，因為SQLSERVER需要將聚集索引和非聚集索引做關聯
關聯的字段是c1列，那么肯定會將c1(KEY)列和UNIQUIFIER(KEY)列搬過來非聚集索引頁面
這個沒有什么好研究的
關鍵大家看一下加聚集索引前和加聚集索引后的頁面情況
非聚集索引頁面少了，所有數據頁面的頁面編號都不一樣了，這里就證明了“建立聚集索引后所有索引頁和數據頁重新調整”
我們drop掉聚集索引
1 DROP INDEX t1c1 ON ct1
看一下頁面情況
1 --TRUNCATE TABLE [dbo].[DBCCResult] 2 -- 3 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,ct1,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 6 7 SELECT COUNT(*) FROM [dbo].[DBCCResult]
頁面還是256
可以看到數據頁面和非聚集索引頁面的頁面編號有不同了，證明SQLSERVER又重新分配索引頁面和數據頁面
其實這里可以證明：建立聚集索引和刪除聚集索引是一個昂貴的操作，建立和刪除聚集索引都需要重新分配頁面
我們刪除非聚集索引
1 DROP INDEX nt1c1 ON ct1
1 --TRUNCATE TABLE [dbo].[DBCCResult] 2 -- 3 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,ct1,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
可以看到，數據頁面的編號都沒有變化，非聚集索引頁面全部刪除了
所以，刪除非聚集索引對于SQLSERVER影響不大，為什麼聚集索引和非聚集索引的建立和刪除會有這麼大的區別
大家只要認真看 SQLSERVER聚集索引與非聚集索引的再次研究（上/下）就可以了，文章開頭的結構圖已經給出了答案o(∩_∩)o
簡單對比
雖然使用主鍵值當作指針會讓輔助索引占用更多空間，但好處是，聚集索引在移動行時無需更新輔助索引中的主鍵值，而非聚集索引需要調整其葉子節點中的堆地址
聚集索引下，數據記錄是保存在B+樹的葉子節點（大小相當于磁盤上的頁）上，當插入新的數據時，如果主鍵的值是有序的，它會把每一條記錄都存儲在上一條記錄的后面，但是如果主鍵使用的是無序的數值，例如UUID，這樣在插入數據時聚集索引無法簡單地把新的數據插入到最后，而是需要為這條數據尋找合適的位置，這就額外增加了工作
文章列表
留言列表
