SQL Server 索引和表體系結構(包含列索引)

pursuer.chen發表於2014-06-13

包含列索引

概述

包含列索引也是非聚集索引,索引結構跟聚集索引結構是一樣,有一點不同的地方就是包含列索引的非鍵列只儲存在葉子節點;包含列索引的列分為鍵列和非鍵列,所謂的非鍵列就是INCLUDE中包含的列,至少需要有一個鍵列,且鍵列和非鍵列不允許重複,非鍵列最多允許1023列(也就是表的最多列-1),由於索引鍵列(不包括非鍵)必須遵守現有索引大小的限制(最大鍵列數為 16,總索引鍵大小為 900 位元組)的要求所以引進了包含列索引。

正文

  • 建立包含列索引

 

----建立表
CREATE TABLE [dbo].[Customers](
    [custid] [int] IDENTITY(1,1) NOT NULL,
    [companyname] [nvarchar](40) NOT NULL,
    [contactname] [nvarchar](30) NOT NULL,
    [contacttitle] [nvarchar](400) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
    [custid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

----建立包含列索引
CREATE NONCLUSTERED INDEX [IX1_Customers] ON [dbo].[Customers] 
(
    [companyname] ASC
)
INCLUDE ( [contactname])
WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

這裡的鍵列就是:
companyname
非鍵列就是:contactname

非鍵列具有下列優點:

    • 它們可以是不允許作為索引鍵列的資料型別。

    • 在計算索引鍵列數或索引鍵大小時,資料庫引擎不考慮它們。


 

當查詢中的所有列都作為鍵列或非鍵列包含在索引中時,帶有包含性非鍵列的索引可以顯著提高查詢效能。這樣可以實現效能提升,因為查詢優化器可以在索引中找到所有列值;不訪問表或聚集索引資料,從而減少磁碟 I/O 操作。(當索引包含查詢引用的所有列時,它通常稱為“覆蓋查詢”。)

  • 建立覆蓋查詢

 覆蓋查詢就是建立的索引列包含查詢所引用的所有列時

  1. 查詢列都設為鍵列
當我們的SELECT查詢是這樣的
SELECT 
       [companyname]
      ,[contactname]
      ,[contacttitle]
  FROM [chenmh].[dbo].[Customers]
  where companyname='好孩子'
  
  
---這時我們選擇將索引列都包含在索引建列中  
CREATE NONCLUSTERED INDEX [IX2_Customers] ON [dbo].[Customers] 
(
      
       [companyname] ASC
      ,[contactname] ASC
      ,[contacttitle] ASC
)
WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

將會彈出警告:警告! 最大鍵長度為 900 個位元組。索引 'IX2_Customers' 的最大長度為 940 個位元組。對於某些大值組合,插入/更新操作將失敗。
由於三個欄位都是NVARCHAR欄位型別,每個字元需要 2 個位元組,(40+30+400)*2=940個位元組,大於900位元組,這時我們可以將
[contactname] ,[contacttitle]包含在非鍵列中

2.將大資料型別設為非鍵列

 

CREATE NONCLUSTERED INDEX [IX3_Customers] ON [dbo].[Customers] 
(
      
       [companyname] ASC
      
)
INCLUDE ( [contactname]
        ,[contacttitle])
WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

這時索引鍵大小所佔字元就只有40*2=80個位元組,同時索引也是覆蓋索引,索引的列包含查詢用到的列,當我們查詢資料時直接在索引頁中查詢資料就可以,不需要訪問資料頁,減少磁碟IO,提高效能

 

帶有包含列的索引準則

設計帶有包含列的非聚集索引時,請考慮下列準則:

    • 在 CREATE INDEX 語句的 INCLUDE 子句中定義非鍵列。
    • 只能對錶或索引檢視的非聚集索引定義非鍵列。
    • textntextimage 之外,允許所有資料型別。
    • 精確或不精確的確定性計算列都可以是包含列。有關詳細資訊,請參閱為計算列建立索引。
    • 與鍵列一樣,只要允許將計算列資料型別作為非鍵索引列,從 imagentexttext 資料型別派生的計算列就可以作為非鍵(包含性)列。
    • 不能同時在 INCLUDE 列表和鍵列列表中指定列名。
    • INCLUDE 列表中的列名不能重複。

列大小準則

    • 必須至少定義一個鍵列。最大非鍵列數為 1023 列。也就是最大的表列數減 1。
    • 索引鍵列(不包括非鍵)必須遵守現有索引大小的限制(最大鍵列數為 16,總索引鍵大小為 900 位元組)。
    • 所有非鍵列的總大小隻受 INCLUDE 子句中所指定列的大小限制;例如,varchar(max) 列限制為 2 GB。

列修改準則

修改已定義為包含列的表列時,要受下列限制:

    • 除非先刪除索引,否則無法從表中刪除非鍵列。
    • 除進行下列更改外,不能對非鍵列進行其他更改:

      • 將列的為空性從 NOT NULL 改為 NULL。
      • 增加 varcharnvarcharvarbinary 列的長度。

注意事項

  • 鍵列的大小盡量小,有利用提高效率
  • 將用於搜尋和查詢的列為鍵列,鍵列儘量不要包含沒必要的列。(例如上面建立的覆蓋查詢列,雖然companyname+contactname加起來作為鍵列也不會超過900位元組,但是這樣鍵大小就變大了,降低了查詢效率)
  • 避免新增不必要的列。新增過多的索引列(鍵列或非鍵列)會對效能產生下列影響:
    • 一頁上能容納的索引行將更少。這樣會使 I/O 增加並降低快取效率。
    • 需要更多的磁碟空間來儲存索引。特別是,將 varchar(max)nvarchar(max)varbinary(max)xml 資料型別新增為非鍵索引列會顯著增加磁碟空間要求。這是因為列值被複制到了索引葉級別。因此,它們既駐留在索引中,也駐留在基表中。
    • 索引維護可能會增加對基礎表或索引檢視執行修改、插入、更新或刪除操作所需的時間

 

 

備註:

    作者:pursuer.chen

    部落格:http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創,歡迎大家轉載;但轉載時必須註明文章來源,且在文章開頭明顯處給明連結,否則保留追究責任的權利。

《歡迎交流討論》

相關文章