索引
数据库中的索引与书籍中的索引类似。在一本书中,利用索引可以快速查找所需信息,无须阅读整本书。在数据库中,索引使数据库程序无须对整个表进行扫描,就可以在其中找到所需数据。书中的索引是一个词语列表,其中注明了包含各个词的页码。而数据库中的索引是一个表中所包含的值的列表,其中注明了表中包含各个值的行所在的存储位置。可以为表中的单个列建立索引,也可以为一组列建立索引;索引采用 B 树结构。索引包含一个条目,该条目有来自表中每一行的一个或多个列(搜索关键字)。B 树按搜索关键字排序,可以在搜索关键字的任何子词条集合上进行高效搜索。例如,对于一个 A、B、C 列上的索引,可以在 A 以及 A、B 和 A、B、C 上对其进行高效搜索。
大多数书中包含一个关于词汇、名称、地点等等的总索引。数据库则包含分别关于所选类型或数据列的索引:这好比在一本书中分别为人名和地名建立索引。当创建数据库并优化其性能时,应该为数据查询所使用的列创建索引。
在随 Microsoft® SQL Server™ 2000 提供的 pubs 示例数据库中,employee 表在 emp_id 列上有一个索引。下面的示意图显示索引如何存储每个 emp_id 值并指向表中包含各个值的数据行。
当 SQL Server 执行一个语句,在 employee 表中根据指定的 emp_id 值查找数据时,它能够识别 emp_id 列的索引,并使用该索引查找所需数据。如果该索引不存在,它会从表的第一行开始,逐行搜索指定的 emp_id 值。
SQL Server 为某些类型的约束(如 PRIMARY KEY 和 UNIQUE 约束)自动创建索引。可以通过创建不依赖于约束的索引,进一步对表定义进行自定义。
不过,索引为性能所带来的好处却是有代价的。带索引的表在数据库中会占据更多的空间。另外,为了维护索引,对数据进行插入、更新、删除操作的命令所花费的时间会更长。在设计和创建索引时,应确保对性能的提高程度大于在存储空间和处理资源方面的代价。
设计索引
当 Microsoft® SQL Server™ 2000 执行查询时,查询优化器会对可用的数据检索方法的成本进行评估,从中选用最有效的方法。SQL Server 可以扫描表,如果索引存在则使用索引。当 SQL Server 执行表扫描时,它从表的第一行开始逐行查找,将符合查询条件的行提取出来。当 SQL Server 使用索引时,它会查找查询所需的行的存储位置,并只提取出所需的行。
在考虑是否为一个列创建索引时,应考虑被索引的列是否以及如何用于查询中。索引对下列查询很有帮助:
搜索符合特定搜索关键字值的行(精确匹配查询)。精确匹配比较是指查询使用 WHERE 语句指定具有给定值的列条目。例如:
WHERE emp_id = 'VPA30890F'
搜索其搜索关键字值为范围值的行(范围查询)。范围查询是指查询指定其值介于两个值之间的任何条目。例如:
WHERE job_lvl BETWEEN 9 and 12
或
WHERE job_lvl >= 9 and job_lvl <= 12
在表 T1 中搜索根据联接谓词与表 T2 中的某个行匹配的行(索引嵌套循环联接)。
在不进行显式排序操作的情况下产生经排序的查询输出,尤其是经排序的动态游标。
在不进行显式排序操作的情况下,按一种有序的顺序对行进行扫描,以允许基于顺序的操作,如合并联接和流聚合。
以优于表扫描的性能对表中所有的行进行扫描,性能提高是由于减少了要扫描的列集和数据总量(该查询有覆盖索引可供使用)。
搜索插入和更新操作中重复的新搜索关键字值,以实施 PRIMARY KEY 和 UNIQUE 约束。
搜索已定义了 FOREIGN KEY 约束的两个表之间匹配的行。
使用 LIKE 比较进行查询时,如果模式以特定字符串如"abc%"开头,使用索引则会提高效率;如果模式以通配符如"%xyz"开头,则索引不起作用。
在很多查询中,索引可以带来多方面的好处。例如,索引除了可以覆盖查询外,还使得可以进行范围查询。SQL Server 可以在同一个查询中为一个表使用多个索引,并可以合并多个索引(使用联接算法),以便搜索关键字共同覆盖一个查询。另外,SQL Server 会自动确定利用哪些索引进行查询,并且能够在表被改动时确保该表的所有索引都得到维护。
其它索引设计准则
设计索引时还要考虑的其它准则包括:
一个表如果建有大量索引会影响 INSERT、UPDATE 和 DELETE 语句的性能,因为在表中的数据更改时,所有索引都须进行适当的调整。另一方面,对于不需要修改数据的查询(SELECT 语句),大量索引有助于提高性能,因为 SQL Server 有更多的索引可供选择,以便确定以最快速度访问数据的最佳方法。
覆盖的查询可以提高性能。覆盖的查询是指查询中所有指定的列都包含在同一个索引中。例如,如果在一个表的 a、b 和 c 列上创建了组合索引,则从该表中检索 a 和 b 列的查询被视为覆盖的查询。创建覆盖一个查询的索引可以提高性能,因为该查询的所有数据都包含在索引自身当中;检索数据时只需引用表的索引页,不必引用数据页,因而减少了 I/O 总量。尽管给索引添加列以覆盖查询可以提高性能,但在索引中额外维护更多的列会产生更新和存储成本。
对小型表进行索引可能不会产生优化效果,因为 SQL Server 在遍历索引以搜索数据时,花费的时间可能会比简单的表扫描还长。
应使用 SQL 事件探查器和索引优化向导帮助分析查询,确定要创建的索引。为数据库及其工作负荷选择正确的索引是非常复杂的,需要在查询速度和更新成本之间取得平衡。窄索引(搜索关键字中只有很少的列的索引)需要的磁盘空间和维护开销都更少。而另一方面,宽索引可以覆盖更多的查询。确定正确的索引集没有简便的规则。经验丰富的数据库管理员常常能够设计出很好的索引集,但是,即使对于不特别复杂的数据库和工作负荷来说,这项任务也十分复杂、费时和易于出错。可以使用索引优化向导使这项任务自动化。有关更多信息,请参见索引优化向导。
索引的特征
在确定某一索引适合某一查询之后,可以自定义最适合具体情况的索引类型。索引特征包括:
聚集还是非聚集
唯一还是不唯一
单列还是多列
索引中的列顺序为升序还是降序
覆盖还是非覆盖
还可以自定义索引的初始存储特征,通过设置填充因子优化其维护,并使用文件和文件组自定义其位置以优化性能。
使用聚集索引
聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。
聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节省成本。
当索引值唯一时,使用聚集索引查找特定的行也很有效率。例如,使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法,是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。
说明 如果该表上尚未创建聚集索引,且在创建 PRIMARY KEY 约束时未指定非聚集索引,PRIMARY KEY 约束会自动创建聚集索引。
也可以在 lname(姓氏)列和 fname(名字)列上创建聚集索引,因为雇员记录常常是按姓名而不是按雇员 ID 分组和查询的。
--索引实例
A. 使用简单索引
下面的示例为 authors 表的 au_id 列创建索引。
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
ON authors (au_id)
GO
B. 使用唯一聚集索引
下面的示例为 emp_pay 表的 employeeID 列创建索引,并且强制唯一性。因为指定了 CLUSTERED 子句,所以该索引将对磁盘上的数据进行物理排序。
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
VALUES (1, 500, .10)
INSERT emp_pay
VALUES (2, 1000, .05)
INSERT emp_pay
VALUES (3, 800, .07)
INSERT emp_pay
VALUES (5, 1500, .03)
INSERT emp_pay
VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)
GO C. 使用简单组合索引下面的示例为 order_emp 表的 orderID 列和 employeeID 列创建索引。 SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'order_emp')
DROP TABLE order_emp
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'emp_order_ind')
DROP INDEX order_emp.emp_order_ind
GO
USE pubs
GO
CREATE TABLE order_emp
(
orderID int IDENTITY(1000, 1),
employeeID int NOT NULL,
orderdate datetime NOT NULL DEFAULT GETDATE(),
orderamount money NOT NULL
)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (5, '4/12/98', 315.19)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (5, '5/30/98', 1929.04)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (1, '1/03/98', 2039.82)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (1, '1/22/98', 445.29)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (4, '4/05/98', 689.39)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/21/98', 1598.23)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/21/98', 445.77)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/22/98', 2178.98)
GO
SET NOCOUNT OFF
CREATE INDEX emp_order_ind
ON order_emp (orderID, employeeID)
D. 使用 FILLFACTOR 选项
下面的示例使用 FILLFACTOR 子句,将其设置为 100。FILLFACTOR 为 100 将完全填满每一页,只有确定表中的索引值永远不会更改时,该选项才有用。
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'zip_ind')
DROP INDEX authors.zip_ind
GO
USE pubs
GO
CREATE NONCLUSTERED INDEX zip_ind
ON authors (zip)
WITH FILLFACTOR = 100
E. 使用 IGNORE_DUP_KEY
下面的示例为 emp_pay 表创建唯一聚集索引。如果输入了重复的键,将忽略该 INSERT 或 UPDATE 语句。
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
VALUES (1, 500, .10)
INSERT emp_pay
VALUES (2, 1000, .05)
INSERT emp_pay
VALUES (3, 800, .07)
INSERT emp_pay
VALUES (5, 1500, .03)
INSERT emp_pay
VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
GO
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay(employeeID)
WITH IGNORE_DUP_KEY
F. 使用 PAD_INDEX 创建索引
下面的示例为 authors 表中的作者标识号创建索引。没有 PAD_INDEX 子句,SQL Server 将创建填充 10% 的叶级页,但是叶级之上的页几乎被完全填满。使用 PAD_INDEX 时,中间级页也填满 10%。
说明 如果没有指定 PAD_INDEX,唯一聚集索引的索引页上至少会出现两项。
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
ON authors (au_id)
WITH PAD_INDEX, FILLFACTOR = 10
G. 为视图创建索引
下面的示例将创建一个视图,并为该视图创建索引。然后,引入两个使用该索引视图的查询。
USE Northwind
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO
--Create view.
CREATE VIEW V1
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID
GROUP BY OrderDate, ProductID
GO
--Create index on the view.
CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (OrderDate, ProductID)
GO
--This query will use the above indexed view.
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev, OrderDate, ProductID
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND ProductID in (2, 4, 25, 13, 7, 89, 22, 34)
AND OrderDate >= '05/01/1998'
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC
--This query will use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 1998
GROUP BY OrderDate
ORDER BY OrderDate ASC
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2009/03/07/3967321.aspx