【SQL SERVER】索引-有解無憂

在做開發程序中經常會接觸資料庫索引，不只是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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/5557.html

標籤：SQL Server

上一篇：【SQL SERVER】鎖機制

下一篇：SQL Server 2012自動標識列值跳轉問題