1 索引介紹

索引是一種幫助查詢語(yǔ)句能夠快速定位到數(shù)據(jù)的一種技術(shù)。索引的存儲(chǔ)方式有行存儲(chǔ)索引、列存儲(chǔ)索引和內(nèi)存優(yōu)化三種存儲(chǔ)方式：

1. 行存儲(chǔ)索引，使用B+樹結(jié)構(gòu)，行存儲(chǔ)指的是數(shù)據(jù)存儲(chǔ)格式為堆、聚集索引和內(nèi)存優(yōu)化表的表，用于OLTP場(chǎng)景。行存儲(chǔ)索引按順序排列的值列表，每個(gè)值都有指向其所在的數(shù)據(jù)頁(yè)面的指針。
   • 聚集索引
   • 非聚集索引
   • 唯一索引
   • 篩選索引
2. 列存儲(chǔ)索引，使用列結(jié)構(gòu)存儲(chǔ)，列存儲(chǔ)指的是在邏輯上整理為包含行和列的表，實(shí)際上以列式數(shù)據(jù)格式存儲(chǔ)的數(shù)據(jù)，用于OLAP場(chǎng)景。使用基于列的數(shù)據(jù)存儲(chǔ)和查詢處理。
   • 聚集列存儲(chǔ)
   • 非聚集列存儲(chǔ)
3. 內(nèi)存優(yōu)化索引，使用Bw樹存儲(chǔ)，Bw樹使用一種“旋轉(zhuǎn)”技術(shù)，更適合處理處理范圍查詢和隨機(jī)插入/刪除操作，適用于各種場(chǎng)景下的數(shù)據(jù)存儲(chǔ)和查詢。
   本文中我們討論的索引就是行存儲(chǔ)索引中的聚集索引和非聚集索引，不涉及其它索引。

Bw樹使用一組新的旋轉(zhuǎn)技術(shù)，支持更加高效的范圍查詢操作。而B+樹則使用葉節(jié)點(diǎn)鏈表來處理范圍查詢。在B+樹中，如果您需要范圍查詢，您需要遍歷整個(gè)鏈表，這會(huì)增加查詢的時(shí)間成本。相比之下，Bw樹通過一些特殊的旋轉(zhuǎn)操作，能夠使得范圍查詢操作更加高效，從而顯著提高查詢性能。
假設(shè)需要查詢數(shù)字在100到200之間的數(shù)據(jù)，那么B+樹需要遍歷相應(yīng)的葉節(jié)點(diǎn)鏈表，而Bw樹則可以使用一些特殊的旋轉(zhuǎn)操作，跳過某些節(jié)點(diǎn)，快速定位到相應(yīng)的數(shù)據(jù)范圍，從而減少了查詢的時(shí)間成本。
總體來說，Bw樹在范圍查詢和隨機(jī)操作等特殊情況下比B+樹更加高效。但是對(duì)于其他類型的查詢操作，它們的性能并沒有很大的區(qū)別，具體的效果需要根據(jù)應(yīng)用場(chǎng)景來進(jìn)行具體分析。

2 行存儲(chǔ)索引的數(shù)據(jù)組織結(jié)構(gòu)

聚集索引和非聚集索引都是使用B+樹結(jié)構(gòu)組織的，最頂層稱為根節(jié)點(diǎn)，中間層稱為中間節(jié)點(diǎn)，最底層稱為葉節(jié)點(diǎn)。在聚集索引中，葉節(jié)點(diǎn)包含了基礎(chǔ)表的數(shù)據(jù)頁(yè)，根節(jié)點(diǎn)和中間節(jié)點(diǎn)包含了索引行的索引頁(yè)，每個(gè)索引行包含一個(gè)鍵值和一個(gè)指針，通過指針來找到某個(gè)葉節(jié)點(diǎn)的數(shù)據(jù)行。而在非聚集索引中，葉節(jié)點(diǎn)只包含了索引行的索引頁(yè)，沒有數(shù)據(jù)頁(yè)，它的索引行中只有指針，通過指針來找到對(duì)應(yīng)的堆表的RID或者聚集索引的數(shù)據(jù)頁(yè)。

聚集索引決定了表中數(shù)據(jù)行的存儲(chǔ)順序（升序/降序），所以每張表只能有1個(gè)聚集索引，可以使用CREATE CLUSTERED INDEX來手動(dòng)創(chuàng)建聚集索引，也可以是在建表時(shí)指定主鍵的方式來自動(dòng)創(chuàng)建。
每張表可以有多個(gè)非聚集索引，可以針對(duì)不同的查詢語(yǔ)句和業(yè)務(wù)場(chǎng)景來創(chuàng)建非聚集索引，只能是使用CREATE NONCLUSTERED INDEX來手動(dòng)創(chuàng)建非聚集索引。

3 兩種索引的空間占用對(duì)比

由于聚集索引的葉節(jié)點(diǎn)存儲(chǔ)了是數(shù)據(jù)頁(yè)，由中間節(jié)點(diǎn)存放了指針，而非聚集索引的葉節(jié)點(diǎn)存放了指針（行定位器），那通過B+樹的構(gòu)造，可以大概判斷是非聚集索引要消耗的空間更多，因?yàn)榉蔷奂饕娣鸥嗟闹羔樞畔ⅲ?em>葉節(jié)點(diǎn)的數(shù)量肯定會(huì)比中間節(jié)點(diǎn)的數(shù)量多）。

3.1 使用sp_spaceused查看索引大小

1. 查看基礎(chǔ)表order_line，目前行數(shù)1232537行，數(shù)據(jù)大小約80MB，未創(chuàng)建索引。
   使用exec sp_spaceused order_line命令查看。
   
2. 在order_line表的ol_w_id、ol_d_id、ol_o_id和ol_number列上創(chuàng)建聚簇索引 order_line_i1_clustered

   CREATE UNIQUE CLUSTERED INDEX [order_line_i1_clustered] ON [dbo].[order_line]
   (
   	[ol_w_id] ASC,
   	[ol_d_id] ASC,
   	[ol_o_id] ASC,
   	[ol_number] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   GO   
   

3. 查看表的索引大小，約232KB，說明聚簇索引order_line_i1_clustered的大小為232KB-24KB=208KB。
   使用exec sp_spaceused order_line命令查看。
   
4. 在order_line表的ol_w_id、ol_d_id、ol_o_id和ol_number列上創(chuàng)建非聚簇索引order_line_i1_nonclustered

   CREATE UNIQUE CLUSTERED INDEX [order_line_i1_clustered] ON [dbo].[order_line]
   (
   	[ol_w_id] ASC,
   	[ol_d_id] ASC,
   	[ol_o_id] ASC,
   	[ol_number] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   GO
   

5. 查看表的索引大小，約19MB，說明非聚簇索引order_line_i1_clustered的大小為18MB~19MB。
   使用exec sp_spaceused order_line命令查看。
   

3.2 使用DBCC查看索引大小

我們也可以通過另外一種方式來證明，通過查詢索引ID，再使用dbcc ind將索引的所有頁(yè)返回，然后再計(jì)算索引頁(yè)的結(jié)果

1. 首先查看兩個(gè)表的查詢索引ID

    SELECT t.name AS TableName,i.name AS IndexName,i.index_id,i.type_desc
   FROM sys.dm_db_partition_stats AS s
   INNER JOIN sys.indexes AS i 
     ON s.object_id = i.object_id 
     AND s.index_id = i.index_id
   INNER JOIN sys.tables AS t 
     ON t.object_id = i.object_id
   WHERE t.name='order_line'
   

2. 將兩個(gè)索引的DBCC IND結(jié)果輸出到dbcc_ind_result表中，然后計(jì)算索引的大小

   CREATE TABLE dbcc_ind_result (
       PageFID int,
       PagePID int,
       IAMFID int,
       IAMPID int,
       ObjectID int,
       IndexID int,
       PartitionNumber int,
       PartitionID bigint,
       iam_chain_type varchar(30),
       PageType int,
       IndexLevel int,
       NextPageFID int,
       NextPagePID int,
       PrevPageFID int,
       PrevPagePID int
   );
   GO
   INSERT INTO dbcc_ind_result exec('DBCC IND(0,order_line,1)');
   GO
   INSERT INTO dbcc_ind_result exec('DBCC IND(0,order_line,5)');
   GO
   SELECT d.IndexID,i.name,COUNT(*)  AS PageCount,COUNT(*)*8 AS SizeKB
   FROM dbcc_ind_result d 
   INNER JOIN sys.indexes AS i 
   ON d.ObjectID = i.object_id 
   AND d.IndexID = i.index_id
   WHERE d.PageType=2 
   GROUP BY d.IndexID,i.name
   GO
   

   
   實(shí)驗(yàn)證明，在相同的列上，非聚集索引比聚集索引需要更多的空間來存放指針信息（行定位器），消耗更多的空間。

4 兩種索引讀取數(shù)據(jù)的方式

前文提到聚集索引的葉節(jié)點(diǎn)存放的是數(shù)據(jù)頁(yè)，而非聚集索引葉節(jié)點(diǎn)存放的是指針來指向數(shù)據(jù)的位置，數(shù)據(jù)的位置可以是堆(head)的RID，也可以時(shí)聚集索引的葉節(jié)點(diǎn)。下面創(chuàng)建一張測(cè)試表來驗(yàn)證。

4.1 未創(chuàng)建索引時(shí)

1. 創(chuàng)建測(cè)試表，生產(chǎn)10000行測(cè)試數(shù)據(jù)

   DROP TABLE IF EXISTS dbo.Test1;
   CREATE TABLE dbo.Test1 (
       C1 INT,
       C2 INT);
   WITH Nums
   AS (SELECT TOP (10000)
           ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS n
       FROM master.sys.all_columns AS ac1
           CROSS JOIN master.sys.all_columns AS ac2)
   INSERT INTO dbo.Test1 (
       C1,
       C2)
   SELECT n,
          2
   FROM Nums;
   

2. 打開統(tǒng)計(jì)信息和執(zhí)行計(jì)劃功能， 從10000行中查詢1行數(shù)據(jù)，例如查詢C1列為1000的數(shù)據(jù)。
   

   SET STATISTICS TIME;
   SET STATISTICS IO;
   SELECT t.C1,t.C2
   FROM dbo.Test1 AS t
   WHERE C1 = 1000;
   

   執(zhí)行后可以看到統(tǒng)計(jì)信息項(xiàng)，發(fā)生了22個(gè)邏輯讀：
   • 表 'Test1'。掃描計(jì)數(shù) 1，邏輯讀取 22 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
   • 并且執(zhí)行計(jì)劃中使用了全表掃描，需要讀取10000行數(shù)據(jù)。
     

4.2 創(chuàng)建非聚集索引后

在C1列創(chuàng)建1個(gè)非聚集索引后，再觀察統(tǒng)計(jì)信息和執(zhí)行計(jì)劃是否發(fā)生變化

1. 創(chuàng)建非聚集索引

   CREATE NONCLUSTERED INDEX incl ON dbo.Test1(C1);
   

   創(chuàng)建非聚集索引的過程中，消耗了和前一個(gè)查詢相同的資源，統(tǒng)計(jì)信息一樣：
   • 表 'Worktable'。掃描計(jì)數(shù) 0，邏輯讀取 0 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
   • 表 'Test1'。掃描計(jì)數(shù) 1，邏輯讀取 22 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
2. 執(zhí)行相同的查詢語(yǔ)句，觀察統(tǒng)計(jì)信息和執(zhí)行計(jì)劃
   這一次統(tǒng)計(jì)信息發(fā)生了變化，比沒有索引的情況下消耗的邏輯讀更少，只發(fā)生了3個(gè)邏輯讀：
   • 表 'Test1'。掃描計(jì)數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
   • 而執(zhí)行計(jì)劃則由Table SCAN變?yōu)榱薎ndex Seek和RID，先是掃描非聚集索引中特定范圍的行，該行的指針信息為Bmk1000，再將該指針信息到堆中的RID，再返回?cái)?shù)據(jù)，這個(gè)過程在表中只需要讀取1行數(shù)據(jù)。
     

4.3 創(chuàng)建聚集索引后

在非聚集索引的基礎(chǔ)上，我們?cè)賱?chuàng)建一個(gè)聚集索引，通過語(yǔ)句的執(zhí)行計(jì)劃來了解讀取數(shù)據(jù)的方式。

1. 創(chuàng)建聚集索引

   CREATE CLUSTERED INDEX icl ON dbo.Test1(C1);
   

   創(chuàng)建聚集索引的過程中，產(chǎn)生的統(tǒng)計(jì)信息要比非聚集要多，消耗資源也要更多：
   • 表 'Test1'。掃描計(jì)數(shù) 1，邏輯讀取 22 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
   • 表 'Test1'。掃描計(jì)數(shù) 1，邏輯讀取 24 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
     再來看看執(zhí)行計(jì)劃，由于再4.2中創(chuàng)建了非聚集索引，執(zhí)行計(jì)劃里將創(chuàng)建聚集索引的操作拆成了兩條語(yǔ)句，并且還是INSERT語(yǔ)句：
   • 查詢1：首先還是對(duì)表進(jìn)行了一次全表掃描，并且按照升序的方式進(jìn)行了排序后，再將數(shù)據(jù)插入到聚集索引里面。這里對(duì)應(yīng)的就是邏輯讀取22次這條統(tǒng)計(jì)信息，完成了整個(gè)聚集索引的創(chuàng)建。
   • 查詢2：然后對(duì)整個(gè)聚集索引掃描，并將非聚集索引的指針信息更新為聚集索引的葉節(jié)點(diǎn)。這里對(duì)應(yīng)的就是邏輯讀取24次這條統(tǒng)計(jì)信息，完成了整個(gè)非聚集索引的指針信息更新。
     
2. 再次執(zhí)行相同的查詢語(yǔ)句，消耗的邏輯讀比非聚集索引要少，只需要2次邏輯讀
   • 表 'Test1'。掃描計(jì)數(shù) 1，邏輯讀取 2 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
   • 執(zhí)行計(jì)劃也不再需要使用非聚集索引和堆的RID返回?cái)?shù)據(jù)
     
3. 繼續(xù)驗(yàn)證非聚集索引是否會(huì)通過聚集索引來返回?cái)?shù)據(jù)，需要使用提示語(yǔ)法來固定語(yǔ)句使用非聚集索引。

   SELECT t.C1,t.C2
   FROM dbo.Test1 AS t WITH(INDEX = incl)
   WHERE C1 = 1000;
   

   發(fā)現(xiàn)這種讀取數(shù)據(jù)的方式要消耗更多的邏輯讀，比RID多了1次邏輯讀，比聚集索引多了2次邏輯讀：
   • 表 'Test1'。掃描計(jì)數(shù) 1，邏輯讀取 4 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
   • 執(zhí)行計(jì)劃中先到非聚集索引查找C1=1000所在的行，然后再將輸出的指針信息Uniq1001到聚集索引中執(zhí)行鍵值查找，返回?cái)?shù)據(jù)。
     

5 行存儲(chǔ)索引的基礎(chǔ)總結(jié)

行存儲(chǔ)索引的聚集索引和非聚集索引在生產(chǎn)環(huán)境上普遍都會(huì)使用到，在本文的基礎(chǔ)上，我們進(jìn)行簡(jiǎn)單總結(jié)。

1. 在數(shù)據(jù)組織結(jié)構(gòu)上
   聚集索引的葉節(jié)點(diǎn)存儲(chǔ)的是數(shù)據(jù)頁(yè)，決定了表數(shù)據(jù)的排序方式；非聚集索引的葉節(jié)點(diǎn)存儲(chǔ)的是指針（行定位器），有可能是堆的RID，也有可能是聚集索引的指針。
2. 在空間占用上
   聚集索引只需要很小的空間來存儲(chǔ)數(shù)據(jù)頁(yè)的信息和順序；非聚集索引需要存儲(chǔ)數(shù)據(jù)的指針，占用空間大。
3. 在讀取數(shù)據(jù)的方式上
   聚集索引直接通過葉節(jié)點(diǎn)讀取數(shù)據(jù)頁(yè)；非聚集索引需要通過指針找到RID或者聚集索引的指針，再通過聚集索引查找鍵值。
4. 在邏輯讀的次數(shù)上
   直接讀聚集索引，邏輯讀最小，測(cè)試邏輯讀次數(shù)為2
   通過非聚集索引+RID，邏輯讀居中，測(cè)試邏輯讀次數(shù)為3
   通過聚集索引+非聚集索引，邏輯讀最大，測(cè)試邏輯讀次數(shù)為4
5. 在創(chuàng)建方式上
   聚集索引：創(chuàng)建主鍵時(shí)自動(dòng)使用主鍵列為聚集索引，沒有主鍵時(shí)可以通過CRAETE CLUSTERED INDEX 創(chuàng)建，可以指定多個(gè)列；每張表只能有1個(gè)聚集索引。
   非聚集索引：手動(dòng)創(chuàng)建，通過CRAETE NONCLUSTERED INDEX 創(chuàng)建；每張表可以有多個(gè)非聚集索引。

本次僅對(duì)索引的基本知識(shí)進(jìn)行介紹，后續(xù)再根據(jù)不同的使用場(chǎng)景來驗(yàn)證和說明。文章來源地址http://www.zghlxwxcb.cn/news/detail-439440.html

到了這里，關(guān)于通過空間占用和執(zhí)行計(jì)劃了解SQL Server的行存儲(chǔ)索引的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！