SQL Server索引——《SQL Server2008查询性能优化》笔记

SQL Server在没有聚簇索引的情况下查找数据，只能对表进行逐行遍历以返回满足条件的行，这一过程被称为扫描。在有索引的表可以进行查找数据而不用扫描整张表。没有聚簇索引的表被称为堆，数据无序存放。当表存在聚簇索引时，数据按序存放，非聚簇索引的行定位器指向聚簇索引键，若是堆表则行定位是指向行ID。

样表：

RowID(不是实际列)                 C1              C2              C3

1                                           A1              A2              A3

2                                           B1              B2              B3

没有聚簇索引情况下的非聚簇索引页面：C1上有一非聚簇索引

C1                       行定位器

A1                       指向RID=1的指针

B1                       指向RID=2的指针

在列C2上创建一个聚簇索引后，非聚簇索引的行定位器发生了变化：

C1                       行定位器

A1                       A2

B1                       B2

一、索引开销

有索引的表需要更多空间来存储索引页面，数据的insert,update,delete等操作需要更长的处理时间来维护不断变化的索引。比如在表中增加一行数据，就要在相应的索引中增加一条记录。如果是聚簇索引，开销更大，因为行必须以正确的顺序添加到数据页面，这可能使其他数据行被重新定位。

下面创建一张数据表测试数据操作中索引开销：

if ( select OBJECT_ID ( ' t1 ' )) is not null
drop table t1;
go
create table dbo.t1(c1 int ,c2 int ,c3 char ( 50 ))
select top 10000 identity ( int , 1 , 1 ) as n
into #nums
from master.dbo.syscolumns sc1,master.dbo.syscolumns sc2;
insert into dbo.t1(c1,c2,c3)
select n,n, ' c3 ' from #nums;
drop table #nums;

接着执行update语句：

update dbo.t1 set c1 = 1 ,c2 = 1 where c2 = 1

 

此时的表是堆表，逻辑读取如下：

表't1'。扫描计数1，逻辑读取84 次

扫描计数：查询数据时对表或索引扫描的次数（index scan,table scan），如果是查找方式（seek）则计数值为0。

（http://connect.microsoft.com/SQLServer/feedback/details/387322/scan-count-or-logical-reads-from-statistics-io-has-0，

    http://technet.microsoft.com/zh-cn/library/ms184361.aspx）

每次数据库引擎从缓冲区高速缓存请求页时都会发生逻辑读取。如果页当前不在缓冲区高速缓存中，物理读取将首先将页从磁盘复制到缓存中。

在C1上添加索引后执行update:

create clustered index i1 on t1(c1)

表't1'。扫描计数1，逻辑读取95 次

表'Worktable'。扫描计数1，逻辑读取5 次

相同的语句，逻辑读取从84次增加到95次，维护索引增加了开销。但是使用索引定位一行数据提高的查询效率通常能够弥补更新索引带来的开销。下面在t1上建立另一个索引，

create index i2 on t1(c2)

再次执行update语句，逻辑读取：

表't1'。扫描计数1，逻辑读取15 次

表'Worktable'。扫描计数1，逻辑读取5 次

Worktable是SQL Server内部使用的一个临时表，用于处理查询的中间结果。工作表创建于tempdb数据库中，查询结束后自动抛弃。

二、索引设计建议

1）、Where子句和连接条件

当一个查询提交sql server时，查询优化器的工作方式：

（1）   优化器识别where子句和连接条件中包含的列。

（2）   优化器检测这些列上的索引。

（3）   优化器评估每个索引的有效性。

（4）   优化器根据前几步的信息，估计读取所查询数据最低开销的方法。

下面看一个例子（AdventureWorks库）,

select p.ProductID,p.Name,p.StandardCost,p. [ Weight ]
from Production.Product p;

表'Product'。扫描计数1，逻辑读取15 次，物理读取3 次，预读22 次

下面加上where子句来看对查询优化器决策上的影响：

select p.ProductID,p.Name,p.StandardCost,p. [ Weight ]
from Production.Product p
where p.ProductID = 738

表'Product'。扫描计数0，逻辑读取2 次，物理读取0 次，预读0 次

2）、使用窄索引

窄索引可以在8K的索引页面容纳比宽索引更多的行，这样的好处：减少I/O次数；使数据库的缓存更有效，减少索引页面的逻辑读取次数；减少数据库的存储空间。

if ( select OBJECT_ID ( ' t1 ' )) is not null
drop table dbo.t1
go
create table dbo.t1(c1 int ,c2 int );
with nums
as
( select 1 as n
union all
select n + 1
from nums
where n < 20 )
insert into t1
(c1,c2)
select n, 2
from nums
create index i1 on t1(c1)

可以通过以下脚本查看：

select i.name,i.type_desc,s.page_count,s.record_count,s.index_level
from sys.indexes as i
join sys.dm_db_index_physical_stats( DB_ID (N ' AdventureWorks ' ),
object_id (N ' dbo.T1 ' ),
null , null , ' DETAiLED ' ) as s
ON i.index_id = s.index_id
WHERE i. object_id = object_id (N ' dbo.T1 ' );

执行结果如下：

name  type_desc    page_count record_count index_level

NULL   HEAP                  1                    20            0

i1  NONCLUSTERED       1                    20            0

下面将列C1的数据类型从int更换为char(500),如下

drop index t1.i1

alter table t1 alter column c1 char(500)

create index i1 on t1(c1)

而后再次执行索引查看脚本：

name  type_desc  page_count record_count index_level

NULL   HEAP                    2                 25              0

i1  NONCLUSTERED         2                 20              0

i1  NONCLUSTERED         1                  2               1

3）、列的唯一性

索引尽可能的建在差异性大的列上，在差异小的列如性别列上建索引对性能没有好处，查询优化器不能有效的使用索引减少返回的行。

select *
from HumanResources.Employee
where Gender = ' F ' and SickLeaveHours = 59 and MaritalStatus = ' M '

表'Employee'。扫描计数1，逻辑读取9 次

而后在Gender上加索引

create index ix_employee_test on HumanResources.Employee(Gender)

执行语句后的开销和无索引时相同。下面创建一个选择性大的索引

create index ix_employee_test on HumanResources.Employee(SickLeaveHours,Gender,MaritalStatus)
with (drop_existing = on )

再次执行查询，开销如下

表'Employee'。扫描计数1，逻辑读取6 次

4）、复合索引列顺序

样表：

C1       C2

1           1

2           1

3           1

1           2

2           2

3           2

如果在列（C1，C2）上创建一复合索引，那么该索引的排序如下：

C1       C2

1           1

1           2

2           1

2           2

3           1

3           2

三、使用建议

1）、首先创建聚簇索引。建议在任何非聚簇索引之前创建聚簇索引。这使得非聚簇索引创建时行定位器直接设置为聚簇索引值。否则，重建索引可能需要很大工作量。

2）、保持窄聚簇索引。非聚簇索引将聚簇索引作为他们的行定位器，为了最佳性能，应使聚簇索引长度尽可能的小。

3）、一步重建聚簇索引。用单独的drop index和create index语句重建索引将导致所有非聚簇索引被建立两次。所以应使用create index语句的drop_existing子句来重建聚簇索引。

4）、频繁更新的列，宽关键字列应该建非聚簇索引。



转载于:https://www.cnblogs.com/qiuwuyu/archive/2011/03/14/1983193.html