【譯】SQL Server索引進(jìn)階第八篇：唯一索引

    索引設(shè)計(jì)是數(shù)據(jù)庫(kù)設(shè)計(jì)中比較重要的一個(gè)環(huán)節(jié)，對(duì)數(shù)據(jù)庫(kù)的性能其中至關(guān)重要的作用，但是索引的設(shè)計(jì)卻又不是那么容易的事情，性能也不是那么輕易就獲取到的，很多的技術(shù)人員因?yàn)椴磺‘?dāng)?shù)膭?chuàng)建索引，最后使得其效果適得其反，可以說(shuō)“成也索引，敗也索引”。

    本系列文章來(lái)自Stairway to SQL Server Indexes，翻譯和整理后發(fā)布在agilesharp和博客園,希望對(duì)廣大的技術(shù)朋友在如何使用索引上有所幫助。

唯一索引和約束

    唯一索引和其它索引本質(zhì)上并沒(méi)有什么不同，唯一不同的是唯一索引不允許索引鍵中存在相同的值。因?yàn)樗饕忻恳粋€(gè)條目都與表中的行對(duì)應(yīng)。唯一索引不允許重復(fù)值被插入索引也就保證了對(duì)應(yīng)的行不允許被插入索引所在的表,這也是為什么唯一索引能夠?qū)崿F(xiàn)主鍵和候選鍵。

    為表聲明主鍵或唯一約束時(shí)，SQL Server會(huì)自動(dòng)創(chuàng)建與之對(duì)應(yīng)的唯一索引。你可以在沒(méi)有唯一約束的情況下創(chuàng)建唯一索引，但反之則不行。定義一個(gè)約束時(shí)，SQL Server會(huì)自動(dòng)創(chuàng)建一個(gè)與之同名的索引，并且你不能在刪除約束之前刪除索引。但可以刪除約束，刪除約束也會(huì)導(dǎo)致與之關(guān)聯(lián)的索引被刪除。

    每個(gè)表中可以包含多個(gè)唯一索引。比如說(shuō)AdventureWorks的Product表，含有四個(gè)唯一索引，分別是ProductID,ProductNumber,rowguid和ProductNameColumn,設(shè)置Product表的人將ProductID作為主鍵，其它三個(gè)作為候選鍵。

     你可以通過(guò)Create INDEX語(yǔ)句創(chuàng)建唯一索引，比如:

CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_Name] ON Production.Product ( 
[Name] );

     也可以通過(guò)直接定義約束創(chuàng)建唯一索引:

ALTER TABLE Production.Product ADD CONSTRAINT PK_Product_ProductID PRIMARY KEY 
CLUSTERED ( ProductID );

    上面第一種方法，你Prodcut表中不能含有相同的ProductName,第二種情況表中不允許存在相同的ProductID。

    因?yàn)槎x一個(gè)主鍵或是定義約束會(huì)導(dǎo)致索引被創(chuàng)建，所以你必須在約束定義時(shí)就給出必要的索引信息，因此上面ALTER TABLE語(yǔ)句中包含了”CLUSTERED”關(guān)鍵字。

    如果唯一索引或約束所約束的列在當(dāng)前的表中已經(jīng)含有了重復(fù)值，那么創(chuàng)建索引會(huì)失敗。

    而當(dāng)唯一索引創(chuàng)建成功后，所有違反這個(gè)約束的DML語(yǔ)句都會(huì)失敗，比如，我們打算加入一條當(dāng)前表中存在的的ProductName，語(yǔ)句如下:

INSERT    Production.Product 

                    ( Name , 

                      ProductNumber , 

                      Color , 

                      SafetyStockLevel , 

                      ReorderPoint , 

                      StandardCost , 

                      ListPrice , 

                      Size , 

                      SizeUnitMeasureCode , 

                      WeightUnitMeasureCode , 

                      [Weight] , 

                      DaysToManufacture , 

                      ProductLine , 

                      Class , 

                      Style , 

                      ProductSubcategoryID , 

                      ProductModelID , 

                      SellStartDate , 

                      SellEndDate , 

                      DiscontinuedDate 

                    ) 
          VALUES    ( 'Full-Finger Gloves, M' , 

                      'A unique product number' , 

                      'Black' , 

                      4 , 

                      3 , 

                      20.00 , 

                      40.00 , 

                      'M' , 

                      NULL , 

                      NULL , 

                      NULL , 

                      0 , 

                      'M' , 

                      NULL , 

                      'U' , 

                      20 , 

                      3 , 

                      GETDATE() , 

                      GETDATE() , 

                      NULL 

                    ) ; 

代碼1.插入的行和表中存在相同的ProductName

上面代碼執(zhí)行后我們可以看到如下報(bào)錯(cuò)信息:

消息 2601，級(jí)別 14，狀態(tài) 1，第 1 行 
不能在具有唯一索引 'AK_Product_Name' 的對(duì)象 
'Production.Product' 中插入重復(fù)鍵的行。 
語(yǔ)句已終止。 

上面的消息告訴我們AK_Product_Name索引不允許我們插入的數(shù)據(jù)含有和當(dāng)前表中一樣的ProductName。

主鍵，唯一約束和沒(méi)有約束

    主鍵約束和唯一約束有如下細(xì)小的差別。

•     主鍵約束不允許出現(xiàn)NULL值。任何索引的索引鍵都不允許包含null值。但唯一約束允許包含NULL值，但唯一約束把兩個(gè)NULL值當(dāng)作重復(fù)值，所以施加了唯一約束的每一列只允許包含一個(gè)NULL值。
•     創(chuàng)建主鍵時(shí)會(huì)自動(dòng)創(chuàng)建聚集索引，除非當(dāng)前表中已經(jīng)含有了聚集索引或是創(chuàng)建主鍵時(shí)指定了NONCLUSTERED關(guān)鍵字。
•     創(chuàng)建唯一約束時(shí)會(huì)自動(dòng)創(chuàng)建非聚集索引，除非你指定了CLUSTERED關(guān)鍵字并且當(dāng)前表中還沒(méi)有聚集索引。
•     每個(gè)表中只能有一個(gè)主鍵，但可以由多個(gè)唯一約束。

    對(duì)于唯一約束和唯一索引的選擇，請(qǐng)參照MSDN上的指導(dǎo)，如下:

    唯一約束和唯一索引并沒(méi)有顯著的區(qū)別。創(chuàng)建獨(dú)立的唯一索引和使用唯一約束對(duì)于數(shù)據(jù)的驗(yàn)證方式并無(wú)區(qū)別。查詢(xún)優(yōu)化器也不會(huì)區(qū)分唯一索引是由約束創(chuàng)建還是手工創(chuàng)建。然而以數(shù)據(jù)完整性為目標(biāo)的話，最好創(chuàng)建約束，這使得對(duì)應(yīng)的索引的目標(biāo)一目了然。

混合唯一索引和過(guò)濾索引

     上面我們提到過(guò)唯一索引只允許一個(gè)NULL值，但這和常見(jiàn)的業(yè)務(wù)需求有沖突。很多時(shí)候我們對(duì)于已經(jīng)存在的值不允許重復(fù)，但是允許存在多個(gè)沒(méi)有值的列。

    比如說(shuō)吧，你是一個(gè)供貨商，你所有的產(chǎn)品都來(lái)自于第三方廠商。你將你這里所有的商品信息都存在一個(gè)叫做ProductDemo的表中。你有自己的ProductID,還追蹤產(chǎn)品的UPC（Universal Product Code）值。但并不是所有的廠商產(chǎn)品都存在UPC,你表中的部分?jǐn)?shù)據(jù)如下所示。

表1.ProductDemo表的部分內(nèi)容

    在上表中第二列，你既要保證UPCode的唯一性，又要保證允許NULL值。實(shí)現(xiàn)這種需求最好的辦法就是混合唯一索引和過(guò)濾索引(過(guò)濾索引實(shí)在SQL Server 2008中引入的)。

    作為演示，我們創(chuàng)建了表1所示的表.

CREATE TABLE ProductDemo 

                ( 

                  ProductID NCHAR(6) NOT NULL 

                                     PRIMARY KEY , 

                  UPCode NCHAR(12) NULL 

                ) ; 

    接下來(lái)我們插入如上所示的數(shù)據(jù).

INSERT  ProductDemo 
        ( 
ProductID, UPCode ) 
VALUES  ( '14AJ-W', '036000291452' ) 

,       ( '23CZ-M', NULL ) 

,       ( '23CZ-L', NULL ) 

,       ( '18MM-J', '044000865867' ) ; 

    當(dāng)我們插入重復(fù)值時(shí)

 INSERT ProductDemo (ProductID , UPCode) VALUES ('14AJ-K', '036000291452'); 

    收到如下錯(cuò)誤

消息 2601，級(jí)別 14，狀態(tài) 1，第 1 行 
不能在具有唯一索引 'xx' 的對(duì)象 'dbo.ProductDemo' 中插入重復(fù)鍵的行。 

語(yǔ)句已終止。 

   (譯者注，這里原文作者應(yīng)該是疏忽了，略坑爹，因?yàn)樗麤](méi)有創(chuàng)建過(guò)濾唯一索引，所以按照原文不會(huì)報(bào)錯(cuò)，我在這里加上了,代碼：CREATE UNIQUE NONCLUSTERED INDEX xx on ProductDemo(UPCode) where UPCode!=null)

選擇合適的IGNORE_DUP_KEY選項(xiàng)

    當(dāng)你創(chuàng)建唯一索引時(shí)，你可以指定IGNORE_DUP_KEY選項(xiàng)，因此本文最開(kāi)始創(chuàng)建唯一索引的選項(xiàng)可以是:

CREATE UNIQUE NONCLUSTERED INDEX AK_Product_Name ON Production.Product ( [Name] 
) WITH ( IGNORE_DUP_KEY = OFF ); 

     IGNORE_DUP_KEY這個(gè)名字容易讓人誤會(huì)。唯一索引存在時(shí)重復(fù)的值永遠(yuǎn)不會(huì)被忽略。更準(zhǔn)確的說(shuō)，唯一索引中永遠(yuǎn)不允許存在重復(fù)鍵。這個(gè)選項(xiàng)的作用僅僅是在多列插入時(shí)有用。

    比如，你有兩個(gè)表，表A和表B，有著完全相同的結(jié)構(gòu)。你可能提交如下語(yǔ)句給SQL Server。

INSERT INTO TableA SELECT * FROM TableB;

    SQL Server會(huì)嘗試將所有表B中的數(shù)據(jù)插入表A。但如果因?yàn)槲ㄒ凰饕芙^表B中含有和表A相同的數(shù)據(jù)插入A怎么辦？你是希望僅僅重復(fù)數(shù)據(jù)插入不成功，還是整個(gè)INSERT語(yǔ)句不成功？

    這個(gè)取決于你設(shè)定的IGNORE_DUP_KEY參數(shù)，當(dāng)你創(chuàng)建唯一索引時(shí)，通過(guò)設(shè)置設(shè)個(gè)參數(shù)可以設(shè)定當(dāng)插入不成功時(shí)怎么辦，設(shè)置IGNORE_DUP_KEY的兩種參數(shù)解釋如下:

    IGNORE_DUP_KEY=OFF

    整個(gè)INSERT語(yǔ)句都不會(huì)成功并彈出錯(cuò)誤提示，這也是默認(rèn)設(shè)置。

IGNORE_DUP_KEY=OFF

    只有那些具有重復(fù)鍵的行不成功，其它所有的行會(huì)成功。并彈出警告信息。

    IGNORE_DUP_KEY 選項(xiàng)僅僅影響插入語(yǔ)句。而不會(huì)被UPDATE,CREATE INDEX,ALTER INDEX所影響。這個(gè)選項(xiàng)也可以在設(shè)置主鍵和唯一約束時(shí)進(jìn)行設(shè)置。

為什么唯一索引可以提供額外的性能提升

    唯一索引可以提供出乎你意料之外的性能提升。這是因?yàn)槲ㄒ凰饕oSQL Server提供了確保某一列絕對(duì)沒(méi)有重復(fù)值的信息。adventureWork的Product表中的ProductID和ProductName這兩個(gè)唯一索引，提供了很好的例子。

    加入，你們公司數(shù)據(jù)倉(cāng)庫(kù)的某個(gè)哥們希望你給他提供Product表的一些信息，要求如下:

•     產(chǎn)品名稱(chēng)
•     產(chǎn)品銷(xiāo)售的數(shù)量
•     總銷(xiāo)售額

    因此，你寫(xiě)了如下的查詢(xún)語(yǔ)句:

SELECT  [Name] , 
        COUNT(*) AS 
'RowCount' , 
        SUM(LineTotal) AS 
'TotalValue' 
FROM    Production.Product P 

        JOIN Sales.SalesOrderDetail D ON 
D.ProductID = P.ProductID 
GROUP BY P.Name

    (譯者注，這里原作者給的代碼有問(wèn)題，ProductID替換為P.Name)

   數(shù)據(jù)倉(cāng)庫(kù)的哥們對(duì)你的查詢(xún)語(yǔ)句很滿意，每一行都包含了產(chǎn)品名稱(chēng)，銷(xiāo)售數(shù)量和總的銷(xiāo)售額，查詢(xún)出來(lái)的部分結(jié)果如下:

    但是，你對(duì)于這個(gè)查詢(xún)的成本有所擔(dān)心。SalesOrderDetail是上面查詢(xún)中兩個(gè)表中比較大的表，并且還按照ProductName進(jìn)行分組，這個(gè)ProductName是來(lái)自Product表而不是SalesOrderDetail表。

    通過(guò)SQL Server Management Studio,你注意到SalesOrderDetail表有主鍵，并且主鍵也是聚集索引鍵，也就是SalesOrderID和SalesOrderDetailID,這個(gè)主鍵并不會(huì)給按照ProductName分組帶來(lái)性能提升。

    如果你運(yùn)行了第五篇包含列的代碼，你創(chuàng)建了如下非聚集索引。

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate 
ON 
Sales.SalesOrderDetail 
( 
ProductID, 
ModifiedDate 
) 
INCLUDE 

( 
OrderQty, 
UnitPrice, 
LineTotal 
) ; 

    你覺(jué)得這個(gè)索引可以對(duì)你的查詢(xún)有幫助因?yàn)檫@個(gè)索引包含了除了ProductName列的所有查詢(xún)所需的信息。并且這個(gè)索引是按照ProductID進(jìn)行排序的，但你仍然擔(dān)心分組的ProductID來(lái)自其中一個(gè)表而Select的信息來(lái)自另一個(gè)表。

    你通過(guò)SQL Server Management Studio,通過(guò)查看執(zhí)行計(jì)劃，看到前面數(shù)據(jù)倉(cāng)庫(kù)那哥們要的查詢(xún)的執(zhí)行計(jì)劃如圖1所示。

圖1.按Product.Name進(jìn)行分組時(shí)的執(zhí)行計(jì)劃

    首先你可以驚訝于Product表的Product name索引，Product.AK_Product_Name沒(méi)有被使用.然后你意識(shí)到在Product.Name列上和Product.ProductID上有唯一索引，這使得SQL Server知道這兩列是唯一的。因此，Group By Name等效于Group By ProductID。這使得一個(gè)產(chǎn)品一個(gè)組。

    因此，查詢(xún)優(yōu)化器意識(shí)到你的查詢(xún)等同于如下查詢(xún)，這兩個(gè)ProductID索引因此支持對(duì)所求查詢(xún)的Join和group操作。

SELECT  [Name] , 
        COUNT(*) AS 
'RowCount' , 
        SUM(LineTotal) AS 
'TotalValue' 
FROM    Production.Product P 

        JOIN Sales.SalesOrderDetail D ON 
D.ProductID = P.ProductID 
GROUP BY ProductID

    SQL Server會(huì)同時(shí)掃描SalesOrderDetail上的覆蓋索引和聚集索引，這兩個(gè)索引都是以ProductID進(jìn)行排序的。因此使用合并連接，而免去了排序或散列操作，總之SQL Server生成了最有效的查詢(xún)計(jì)劃。

    如果你Drop了Product.AK_Product_Name索引，比如:

IF EXISTS ( SELECT * 
FROM sys.indexes 
WHERE OBJECT_ID = 
OBJECT_ID(N'Production.Product') 
AND name = N'AK_Product_Name') 
DROP 
INDEX AK_Product_Name 
ON Production.Product; 

    那么生成的新的執(zhí)行計(jì)劃就沒(méi)有那么有效了，需要額外的排序和合并連接操作。

圖2.當(dāng)Drop掉索引后，按照Product Name進(jìn)行分組的查詢(xún)的執(zhí)行計(jì)劃

    你可以看到，雖然唯一索引的主要功能是保證數(shù)據(jù)的完整性，還可以幫助查詢(xún)優(yōu)化器生成更好的查詢(xún)計(jì)劃，即使這個(gè)索引本身不被用來(lái)訪問(wèn)數(shù)據(jù)。

總結(jié)

    唯一索引為主鍵和候選鍵提供了約束。唯一索引可以在沒(méi)有唯一約束時(shí)存在，反之則不行。

     唯一索引同時(shí)也可以是過(guò)濾索引，這使得唯一索引可以允許一列中有多個(gè)NULL值。

    IGNORE_DUP_KEY 關(guān)鍵字可以影響批量插入語(yǔ)句。

    唯一索引還可以提供更好的性能，既然唯一索引本身并沒(méi)有用于數(shù)據(jù)訪問(wèn)。