備註:本文譯自MSDN和自己的一些筆記心得,因本人身在台灣!使用的是繁體字!嫌看著蛋疼的請繞道!勿噴!
何為索引:就是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。 當然,即使没有索引,SQL Server仍然可以实现应有的功能。但索引可以在大多数情况下大大提升查询性能
先說說怎麼建立索引吧!
Transact-SQL 語法慣例
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WHERE <filter_predicate> ]
[ WITH ( <relational_index_option> [ ,...n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
[ ; ]
<object> ::=
{
[ database_name. [ schema_name ] . | schema_name. ]
table_or_view_name
}
<relational_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE}
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
<filter_predicate> ::=
<conjunct> [ AND <conjunct> ]
<conjunct> ::=
<disjunct> | <comparison>
<disjunct> ::=
column_name IN (constant ,…)
<comparison> ::=
column_name <comparison_op> constant
<comparison_op> ::=
{ IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }
<range> ::=
<partition_number_expression> TO <partition_number_expression>
Backward Compatible Relational Index
Important The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
[ WITH <backward_compatible_index_option> [ ,...n ] ]
[ ON { filegroup_name | "default" } ]
<object> ::=
{
[ database_name. [ owner_name ] . | owner_name. ]
table_or_view_name
}
<backward_compatible_index_option> ::=
{
PAD_INDEX
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB
| IGNORE_DUP_KEY
| STATISTICS_NORECOMPUTE
| DROP_EXISTING
}
看著蛋疼加複製對吧!但你只要記住以下幾個就行:
-
UNIQUE
-
在資料表或檢視上建立唯一索引。在唯一索引中,任兩個資料列都不能有相同的索引鍵值。檢視中的叢集索引必須是唯一的。
不論 IGNORE_DUP_KEY 是否設為 ON,Database Engine 都不允許在已包含重複值的資料行上建立唯一索引。如果嘗試執行這項作業,Database Engine 會顯示錯誤訊息。必須先移除重複值,才能在資料行上建立唯一索引。唯一索引中使用的資料行應設為 NOT NULL,因為,當唯一索引建立時,多個 Null 值會被視為重複值。
-
CLUSTERED
-
建立一個索引,在該索引中,鍵值的邏輯順序決定資料表中之對應資料列的實體順序。叢集索引的層級 (底部或分葉) 包含資料表的實際資料列。資料表或檢視一次只能使用一個叢集索引。如需詳細資訊,請參閱<叢集索引結構>。
含有唯一叢集索引的檢視稱為索引檢視。在檢視上建立唯一叢集索引,可將檢視實際具體化。必須先在檢視上建立唯一叢集索引,才能在相同檢視上定義任何其他索引。如需詳細資訊,請參閱<設計索引檢視>。
先建立叢集索引,再建立任何非叢集索引。當叢集索引建立時,會重建資料表上現有的非叢集索引。
如果未指定 CLUSTERED,就會建立非叢集索引。
-
NONCLUSTERED
-
建立索引來指定資料表的邏輯排序。如果是非叢集索引,資料列的實體順序和它們的索引順序無關。如需詳細資訊,請參閱<非叢集索引結構>。
不論建立多少索引,每一份資料表最多只能有 999 個非叢集索引:以隱含的方式使用 PRIMARY KEY 和 UNIQUE 條件約束或以明確的方式使用 CREATE INDEX。
如果是索引檢視,只能在已定義唯一叢集索引的檢視上建立非叢集索引。
預設值是 NONCLUSTERED。
以下是索引的範例:
使用的是AdventureWorks示例數據庫
建立簡單的非叢集索引
下列範例會在 Purchasing.ProductVendor
資料表的 VendorID
資料行上建立非叢集索引。
USE AdventureWorks; GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_ProductVendor_VendorID') DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor; GO CREATE INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor (VendorID); GO |
B. 建立簡單的非叢集複合索引
下列範例會在 Sales.SalesPerson
資料表的 SalesQuota
和 SalesYTD
資料行上建立非叢集複合索引。
複製程式碼 | |
---|---|
USE AdventureWorks GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD') DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ; GO CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD); GO |
C. 建立唯一的非叢集索引
下列範例會在 Production.UnitMeasure
資料表的 Name
資料行上建立唯一非叢集索引。索引會強制將資料上的唯一性插入Name
資料行中。
USE AdventureWorks; GO IF EXISTS (SELECT name from sys.indexes WHERE name = N'AK_UnitMeasure_Name') DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure; GO CREATE UNIQUE INDEX AK_UnitMeasure_Name ON Production.UnitMeasure(Name); GO |
下列查詢會嘗試插入一個含有其值與現有資料列相同的資料列,來測試唯一性條件約束。
--Verify the existing value. SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces'; GO INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate) VALUES ('OC', 'Ounces', GetDate()); |
產生的錯誤訊息如下:
Server: Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated. |
利用 DROP_EXISTING 來卸除和重新建立索引
下列範例會利用 DROP_EXISTING
選項,在 Production.WorkOrder
資料表的 ProductID
資料行上卸除及重新建立現有的索引。也會設定 FILLFACTOR
和 PAD_INDEX
選項。
USE AdventureWorks; GO CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID ON Production.WorkOrder(ProductID) WITH (FILLFACTOR = 80, PAD_INDEX = ON, DROP_EXISTING = ON); GO |
F. 在檢視上建立索引
下列範例在該檢視上建立檢視和索引。內含使用索引檢視的兩項查詢。
USE AdventureWorks;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev,
OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
AND ProductID BETWEEN 700 and 800
AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO
|
G. 利用內含 (非索引鍵) 資料行建立索引
下列範例會利用一個索引鍵資料行 (PostalCode
) 和四個非索引鍵資料行 (AddressLine1
、AddressLine2
、City
、StateProvinceID
) 來建立非叢集索引。其後有一個由索引處理的查詢。若要顯示查詢最佳化工具所選取的索引,請先在 SQL Server Management Studio 的 [查詢] 功能表上選取 [顯示實際執行計畫],再執行查詢。
USE AdventureWorks; GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_Address_PostalCode') DROP INDEX IX_Address_PostalCode ON Person.Address; GO CREATE NONCLUSTERED INDEX IX_Address_PostalCode ON Person.Address (PostalCode) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID); GO SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode FROM Person.Address WHERE PostalCode BETWEEN N'98000' and N'99999'; |