更新时间:2022-04-14 21:53:31
T-SQL笔记8:索引
本章摘要:
1:什么是索引
2:索引和约束
3:设计索引的准备
4:索引排序顺序
索引是与表或视图关联的磁盘上结构,可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(B 树)中,使 SQL Server 可以快速有效地查找与键值关联的行。
表或视图可以包含以下类型的索引:
有关索引结构的详细信息,请参阅表和索引数据结构体系结构。
聚集索引和非聚集索引都可以是唯一的。这意味着任何两行都不能有相同的索引键值。另外,索引也可以不是唯一的,即多行可以共享同一键值。有关详细信息,请参阅唯一索引设计指南。
每当修改了表数据后,都会自动维护表或视图的索引。
对表列定义了 PRIMARY KEY 约束和 UNIQUE 约束时,会自动创建索引。例如,如果创建了表并将一个特定列标识为主键,则 数据库引擎自动对该列创建 PRIMARY KEY 约束和索引。有关详细信息,请参阅创建索引(数据库引擎)。
设计索引时,应考虑以下数据库准则:
定义索引时,应该考虑索引键列的数据是按升序还是按降序存储。升序是默认设置,保持与 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 函数报告索引列是按升序还是降序存储。