T-SQL笔记8:索引

T-SQL笔记8:索引

本章摘要:

1:什么是索引

2:索引和约束

3:设计索引的准备

4:索引排序顺序

 

1:什么是索引

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

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

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

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

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

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

T-SQL笔记8:索引

 

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 子句指定的顺序返回结果集。

T-SQL笔记8:索引

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

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

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

T-SQL笔记8:索引

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

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

T-SQL笔记8:索引本文基于Creative Commons Attribution 2.5 China Mainland License发布,欢迎转载,演绎或用于商业目的,但是必须保留本文的署名http://www.cnblogs.com/luminji(包含链接)。如您有任何疑问或者授权方面的协商,请给我留言。
上一篇:C#笔记28:I/O操作


下一篇:C#笔记29:程序集、应用程序配置及App.config和YourSoft.exe.config