【SQL SERVER】索引_ZenDei技術網路在線

在做開發過程中經常會接觸資料庫索引，不只是DBA才需要知道索引知識，瞭解索引可以讓我們寫出更高質量代碼。簡單介紹索引的概述，聚集索引，非聚集索引，唯一索引，複合索引，篩選索引使用及註意事項 ...

在做開發過程中經常會接觸資料庫索引，不只是DBA才需要知道索引知識，瞭解索引可以讓我們寫出更高質量代碼。

索引概述
聚集索引
非聚集索引
唯一索引
篩選索引
非聚集索引包含列

索引概述

索引的存在主要為了提高數據檢索速度，設計高效的索引對於獲得良好的資料庫和應用程式性能極為重要。

索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問資料庫表中的特定信息，就像平常我們用的新華字典的目錄，假如新華字典沒有目錄有找一個字就必須從第一頁一直翻到最後一頁，這是多麼令人絕望的事情。

　　索引是占有而外空間，是一種典型的空間換時間的做法，所以對待索引必須要保持敬畏之心，要建立在經常篩選的條件上，查詢數據要時刻想著利用索引，竟然都花額外空間存儲索引，不能讓它白白浪費掉。

聚集索引

聚集索引基於數據行的鍵值在表內排序和存儲這些數據行。每個表只能有一個聚集索引，因為數據行本身只能按一個順序存儲。　

簡單來說就是聚集索引和數據的存放順序是一致的，聚集索引葉節點就是數據。

創建準則

1. 定義聚集索引鍵時使用的列越少越好

2. 唯一或包含許多不重覆的值（若創建聚集索引時沒使用唯一屬性，SQL Server會自動添加一個4位元組的唯一標識列）

3. 經常需要順序訪問數據

4. 經常用於對錶中檢索到的數據進行排序

5. 列大小不超過900位元組

適合使用聚集索引情況

1. 使用運算符（如 BETWEEN、>、>=、< 和 <=）返回一系列值

2. 返回大型結果集

3. 使用 JOIN 子句；一般情況下，使用該子句的是外鍵列

4. 使用 ORDER BY 或 GROUP BY 排序/分組數據（因為分組數據也是要先排序）

不適合使用聚集索引情況

1. 頻繁更改的列，每次更改都會導致索引頁不斷重新構建。

2. 若幹列或若幹大型列的組合，每次構建排序需要大量計算

TSQL創建聚集索引

--唯一的聚集索引
CREATE UNIQUE CLUSTERED INDEX [IX_TableName_Name] ON [dbo].[TableName] ([FieldName] ASC)

--不唯一的聚集索引
CREATE CLUSTERED INDEX [IX_TableName_Name] ON [dbo].[TableName] ([FieldName] ASC)

非聚集索引

非聚集索引包含索引鍵值和指向表數據存儲位置的行定位器。可以對錶或索引視圖創建多個非聚集索引。通常，設計非聚集索引是為改善經常使用的、沒有建立聚集索引的查詢的性能。

簡單來說不是聚集索引的就是非聚集索引。額，好像沒解釋一樣。。。非聚集索引的葉節點是定位器。

創建準則

1. 不超過1700位元組（其他文檔說是900位元組，我測試SQL Server是1700位元組）

2. 避免添加不必要的列，因為增加索引維護成本

3. 包含許多不重覆的值，這樣才能更好利用索引查詢效率

4. 列的長度儘可能小

適合使用非聚集索引情況

1. 使用 JOIN 或 GROUP BY 子句

2. 不返回大型結果集的查詢

3. 經常包含在查詢的搜索條件中的列

不適合使用非聚集索引情況

1. 列的重覆值較少

2. 不是經常查詢的搜索條件的列

3. 列的長度過長，因為增加索引維護成本

聚集索引與非聚集索引區別（知識有限，未必全，希望補充）

1. 聚集索引是按物理順序排列，非聚集索引邏輯順序排列

2. 聚集索引一個表只能有一個，非聚集索引可以多個

3. 聚集索引的葉節點是數據，非聚集索引的葉節點是定位器

4. 聚集索引不能附加信息，非聚集索引可以包含附加信息

TSQL創建非聚集索引

CREATE INDEX IX_TableName_FieldName ON DataTable(FieldName DESC)

唯一索引

唯一索引能夠保證索引鍵中不包含重覆的值，從而使表中的每一行從某種方式上具有唯一性。只有當唯一性是數據本身的特征時，指定唯一索引才有意義。

保證索引鍵中不包含重覆的值（包含NULL值）

創建準則

1. 只有需要保證數據唯一性的情況下才使用

適合使用唯一索引情況

1. 需要保證數據唯一性

不適合使用唯一索引情況

1. 不需要保證數據唯一性

TSQL創建唯一索引

CREATE UNIQUE INDEX IX_TableName_FieldName ON DataTable(FieldName ASC)

篩選索引

篩選索引是一種經過優化的非聚集索引，尤其適用於涵蓋從定義完善的數據子集中選擇數據的查詢。篩選索引使用篩選謂詞對錶中的部分行進行索引。

只把符合條件的數據做索引，相當於在一個表的子集做索引。從而達到下麵幾個好處

1. 提高了查詢性能，因為索引頁的數據比全表索引小

2. 減少索引維護開銷，因為只有符合條件才會對索引頁維護

3. 減少索引存儲開銷，道理跟上面一樣

創建準則

1. 篩選的條件必須是明確的值

2. 通常來說經常查詢出現的條件跟過濾條件符合

3. 經常檢索的都是數據的子集

適合使用篩選索引情況

1. 經常篩選表的子集數據，例如通常我們只查詢有效的訂單，無效的訂單很少查，或者基本不查，這種情況適合建立篩選索引

2. 只查最近數據，例如記錄只查最近一個月，可以通過定期在資料庫空閑的時重新維護篩選索引達到加快查詢效率

不適合使用篩選索引情況

1. 經常查詢條件包含篩選值外，這樣導致走全表掃描（前提沒其他索引覆蓋到）

2. 查詢條件不固定

TSQL創建唯一索引

CREATE INDEX IX_TableName_FieldName ON [dbo].[TableName](FieldName ASC)  where State > 1

非聚集索引包含列

通過將非鍵列添加到非聚集索引的葉級，擴展非聚集索引的功能。通過包含非鍵列，可以創建覆蓋更多查詢的非聚集索引

通過把包含的列同時維護在索引頁，達到當查詢的數據都包含在索引中的數據的時候，因為在索引頁找到所有數據，就不需要訪問表的數據頁，從而減少I/O操作，這種通常稱為“覆蓋查詢”

創建準則

1. 必須至少定義一個鍵列

2. 在 CREATE INDEX 語句的 INCLUDE 子句中定義非鍵列

3. 只能對錶或索引視圖的非聚集索引定義非鍵列

4. 允許除 text、 ntext和 image之外的所有數據類型

5. 精確或不精確的確定性計算列都可以是包含列

6. 不能同時在 INCLUDE 列表和鍵列列表中指定列名

7. INCLUDE 列表中的列名不能重覆

8. 索引鍵列（不包括非鍵）必須遵守現有索引大小的限制

9. 所有非鍵列的總大小隻受 INCLUDE 子句中所指定列的大小限制；例如， varchar(max) 列限製為 2 GB

適合使用非聚集索引包含列

1. 篩選的列是索引鍵 && 查詢的列都是包含的列

不適合使用非聚集索引包含列

2. 篩選的列不是索引鍵 || 查詢的列有不在包含列中的

TSQL創建篩選索引

CREATE  INDEX IX_TableName_FieldName ON DataTable(Field1 ASC) INCLUDE(Field2)

轉發請標明出處： https://www.cnblogs.com/WilsonPan/p/12625364.html

參考文章

SQL Server 索引體繫結構和設計指南 - SQL Server | Microsoft Docs