、堆表

堆表通過IAM連接一起，查詢時全表掃描。

1、1 非聚集索引

結構

葉子節點數據結構：行數據結構+Rid（8字節）

• 中間節點數據結構：?

（非聚集非唯一索引）行數據結構+Page（4）+2+ Rid（8字節）

中間2字節有疑問？

• （非聚集唯一索引）行數據結構+分割符?+ Page（4）

• 堆表非聚集索引結構

1、2 聚集索引表

組織結構

1.2.1 聚集索引

聚集索引表由根節點（Root Node）、中間節點（Branch Nodes）、葉子節點組成。

如果葉子節點不夠多時，根節點（Root Node）、中間節點（Branch Nodes）將不存在。

• 根節點、中間節點行結構

• 系統頭部信息（2字節）+Key+&+PageId

• 葉子節點

• 參見行數據結構

• 插入操作對BTree影響

• 刪除操作對索引樹影響

• 更新操作對索引樹影響

注意事項

1. 聚集索引鍵值不能超過900字節，因為生成keyhashvalue時，如果大于900字節性能會有很大影響。Keyhashvalue用于查詢頁的數據行

2. 聚集索引鍵值盡量保持短，每頁只有8096字節可用。減少中間節點的層數。

3. 聚集索引鍵值采用遞增原則，有利于數據頁連續性，減少BTree調整。

?

1.2.2 非聚集索引

• 非聚集索引在索引表中數據結構

  • 根節點(root nodes)、中間節點(page nodes)結構：2字節系統信息+非聚集索引鍵值+ChildPage(4字節)+Key

  • 葉子節點leaf nodes數據結構：2字節系統信息+非聚集索引鍵值+ Key（keyhasvalue）

• 索引覆蓋

避免聚集索引查找

最大鍵列數為 16，最大索引鍵大小為 900 字節

• 過濾索引

索引tree是否包含部分數據。一部分不需要建立索引，減少索引層數。

2、建立索引規則

• 建立聚集索引規則

  • 唯一性：如果非唯一性，索引節點會增加一列唯一表示。

  • 靜態的:?如果對聚集索引鍵值進行更新時，中間節點頁會發生變化，葉子節點頁也會發生變化。操作次數增加，頁空間造成浪費。

  • 連續性：非連續性會造成頁分拆，頁空間浪費，碎片增多。

  • 鍵值大小：鍵值長度越長，中間節點的層數越多，讀取層數越多，性能下降。

• 索引覆蓋

  對常用查詢指定列的索引可以適當增加列覆蓋。

• 非聚集索引

  • 數據密度原則：數據密度是指列值唯一的記錄占總記錄數的百分比，這個比率越高，則說明此列越適合建立索引。

  • 復合索引鍵列順序：在索引中，索引的順序主要由索引中的每一個鍵列確定，因此，對于復合索引，索引中的列順序是很重要的，應該優先把數據密度大，選擇性列，存儲空間小的列放在索引鍵列的前面。

  • 選擇性原則：選擇性是滿足條件的記錄占總記錄數的百分比，這個比率應該盡可能低，這樣才能保證通過索引掃描后，只需要從基礎表提取很少的數據。

3、相關工具

?

3.1 組織分析命令

DBCC IND

用于分析表組織和索引組織查詢命令。

• 命令行

DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid }, { nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )

• 參數

• Dbname:數據庫名

• Dbid：數據庫Id

• Objname：表名

• Objid：表ID

• nonclustered indid:非聚集索引ID，-2 根節點 -1 中間節點 Branch Nodes 0 葉子節點、1 所有節點

• 下列查詢語句等同于 DBCC IND

Select * from sys.dm_db_database_page_allocations(DB_ID(), object_id('TestData8000'),NULL,NULL,'DETAILED')

sys.dm_db_database_page_allocations(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)

• @DatabaseId：數據庫Id

• @TableId:表名

• @indexId：

• @PartionId：分區Id

堆表

聚集索引表

查詢結果集，字段說明

列	說明
PageFID	索引所在文件Id
PagePid	索引所在頁Id
IAMFID	索引所在IAM文件Id
IAMPID	索引所在IAM的頁Id
objectId	對象ID,表對象ID
IndexId	索引類型 0堆、1聚集索引、2-250非聚集索引
PartitionNumber	索引所在分區編號
PartitionId	索引所在的分區Id
Iam_Chain_Type	該頁存放的數據類型、in-row data 數據頁或索引頁、Row-overflow-data 溢出數據行頁 Blob data 大文件類型頁
PageType	數據類型見頁類型
IndexLevel	索引級別 null 根級，0 葉子級，其他索引級
NextPageFID	雙鏈表前級文件Id
NextPagePID	雙鏈表前級頁Id
PrevPageFID	雙鏈表后級文件Id
PrevPagePID	雙鏈表后級頁Id

DBCC Page

用于查看頁數據信息。??

DBCC PAGE
(
['database name'|database id], -- can be the actual name or id of the database
file number, -- the file number where the page is found
page number, -- the page number within the file?
print option = [0|1|2|3] -- display option; each option provides differing levels of information
)

• database name：數據庫名

• file Number:頁所在文件Id

• Page Number:頁id

• Print 0、1、2、3：不同的級別，3為最高級??

--DBCC IND('DataPageTestDb','TestData8000',-1) 先查看表在數據里頁數據信息

--DBCC PAGE(DataPageTestDb,1,8,3) 以文本信息查看

--DBCC PAGE(DataPageTestDb,1,8,3) with tableresults，以表格信息查看

3.2 查詢計劃

查看索引情況

--dbcc show_statistics ([tablename], [indexname])

--dbcc show_statistics (TestDataUnIndex, PK_TestDataUnIndex)

命令詳細見

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms174384(v=sql.105)

-- 打開IO開銷統計 set STATISTICS io ON

-- 打開執行時間統計 set STATISTICS TIME ON

-- Select * from Table

或

3.3 跟蹤代碼生成的SQL語句

Sql Profiler 用于跟蹤程序生成的語句。

參考文章

https://www.cnblogs.com/yx007/p/7268310.html

下圖用于跟蹤Net sqlclient data provider 產生的語句,net體系應用。

以下語句用于跟蹤，在線運行時，SQL操作用時比較長的語句

以下語句用于查詢數據庫死鎖

?

4、其他

?

4.1 數據庫字段類型及長度

?

類型	字節數	定長	變長	blob類型
uniqueidentifier	16	1
date	3	1
time	5	1
datetime2	8	1
datetimeoffset	10	1
tinyint	1	1
smallint	2	1
int	4	1
smalldatetime	4	1
real	4	1
money	8	1
datetime	8	1
float	8	1
sql_variant	8016		1
bit	1	1
decimal(18.2)	9	1
numeric(18.2)	9	1
varchar(max)				1
nvarchar(max)				1
varbinary(max)				1
XML				1
Image				1
text
ntext
varchar()			1
nvarchar()			1
varbinary()			1
char		1
nchar		1

總結

以上是生活随笔為你收集整理的SQL Server之索引解析（二）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。