TSQL笔记8：索引

T-SQL笔记8：索引

本章摘要：

1：什么是索引

2：索引和约束

3：设计索引的准备

4：索引排序顺序

 

1：什么是索引

     索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构（B 树）中，使 SQL Server 可以快速有效地查找与键值关联的行。

     表或视图可以包含以下类型的索引：

• 聚集
  • 聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。
  • 只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。
• 非聚集
  • 非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。
  • 从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位器是聚集索引键。
  • 您可以向非聚集索引的叶级添加非键列以跳过现有的索引键限制（900 字节和 16 键列），并执行完整范围内的索引查询。有关详细信息，请参阅具有包含列的索引。

     有关索引结构的详细信息，请参阅表和索引数据结构体系结构。

     聚集索引和非聚集索引都可以是唯一的。这意味着任何两行都不能有相同的索引键值。另外，索引也可以不是唯一的，即多行可以共享同一键值。有关详细信息，请参阅唯一索引设计指南。

     每当修改了表数据后，都会自动维护表或视图的索引。

2：索引和约束

     对表列定义了 PRIMARY KEY 约束和 UNIQUE 约束时，会自动创建索引。例如，如果创建了表并将一个特定列标识为主键，则 数据库引擎自动对该列创建 PRIMARY KEY 约束和索引。有关详细信息，请参阅创建索引（数据库引擎）。

3：设计索引的准备

     设计索引时，应考虑以下数据库准则：

• 一个表如果建有大量索引会影响 INSERT、UPDATE、DELETE 和 MERGE 语句的性能，因为当表中的数据更改时，所有索引都须进行适当的调整。
  • 避免对经常更新的表进行过多的索引，并且索引应保持较窄，就是说，列要尽可能少。
  • 使用多个索引可以提高更新少而数据量大的查询的性能。大量索引可以提高不修改数据的查询（例如 SELECT 语句）的性能，因为查询优化器有更多的索引可供选择，从而可以确定最快的访问方法。
• 对小表进行索引可能不会产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长。因此，小表的索引可能从来不用，但仍必须在表中的数据更改时进行维护。
• 视图包含聚合、表联接或聚合和联接的组合时，视图的索引可以显著地提升性能。若要使查询优化器使用视图，并不一定非要在查询中显式引用该视图。有关详细信息，请参阅设计索引视图。
• 使用数据库引擎优化顾问来分析数据库并生成索引建议。有关详细信息，请参阅了解数据库引擎优化顾问。

     更多索引设计指南，请查看http://msdn.microsoft.com/zh-cn/library/ms191195.aspx

4：索引排序顺序

     定义索引时，应该考虑索引键列的数据是按升序还是按降序存储。升序是默认设置，保持与 SQL Server 早期版本的兼容性。CREATE INDEX、CREATE TABLE 和 ALTER TABLE 语句的语法在索引和约束中的各列上支持关键字 ASC（升序）和 DESC（降序）：

     当引用表的查询包含用以指定索引中键列的不同方向的 ORDER BY 子句时，指定键值存储在该索引中的顺序很有用。在这些情况下，索引就无需在查询计划中使用 SORT 运算符。因此，使得查询更有效。例如，Adventure Works Cycles 采购部门的买方不得不评估他们从供应商处购买的产品的质量。买方倾向于查验那些由具有高拒绝率的供应商发送的产品。检索数据以满足此条件需要将 Purchasing.PurchaseOrderDetail 表中的 RejectedQty 列按降序（由大到小）排序，并且将 ProductID 列按升序（由小到大）排序，如下列查询所示。

USE AdventureWorks2008R2;
GO
SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,
    ProductID, DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;

     此查询的下列执行计划显示了查询优化器使用 SORT 运算符按 ORDER BY 子句指定的顺序返回结果集。

     如果使用与查询的 ORDER BY 子句中的键列匹配的键列创建索引，则无需在查询计划中使用 SORT 运算符，从而使查询计划更有效。

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);

     再次执行查询后，下列执行计划显示未使用 SORT 运算符，而使用了新创建的非聚集索引。

     数据库引擎 可以在两个方向上同样有效地移动。对于一个在 ORDER BY 子句中列的排序方向倒排的查询，仍然可以使用定义为 (RejectedQty DESC, ProductID ASC) 的索引。例如，包含 ORDER BY 子句 ORDER BY RejectedQty ASC, ProductID DESC 的查询可以使用该索引。

     只可以为键列指定排序顺序。sys.index_columns 目录视图和 INDEXKEY_PROPERTY 函数报告索引列是按升序还是降序存储。