一、检查where子句和连接条件列
下面是不带where子句的select语句组成
select
p.Name,
p.StandardCost,
p.Weight,
p.ProductID
from Production.Product p
打开set statistics io on报告,该select语句逻辑读取操作数量如下
表 'Product'。扫描计数 1,逻辑读取 15 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
为了理解where子句在查询优化器决策上的影响。添加一个检索但一行的where子句
set statistics io on
select
p.Name,
p.StandardCost,
p.Weight,
p.ProductID
from Production.Product p
where p.ProductID=738
set statistics io off
表 'Product'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
有了where子句,查询优化器检查where子句列ProductId,确定了ProductId上索引PK_Product_ProductId的可用性,产生的逻辑读取次数减少。
这也适用于两个表之间的连接条件中使用的列。
二、使用窄索引
先创建一个具有20行的测试表和一个索引
if(select OBJECT_ID('t1')) is not null
drop table t1;
go
create table 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)
因为索引列是窄的(int数据类型为4字节),一个8KB的索引行可以容纳索引的索引行
select i.name,
s.page_count,
i.type_desc,
s.record_count,
s.index_level
from sys.indexes i
join sys.dm_db_index_physical_stats(db_id(N'AdventureWorks2008'),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')
执行上面代码就可以看出.
为了看出宽索引的劣势,可以将索引列的c1数据类型从int改为char(500)。
drop index t1.i1
alter table t1 alter column c1 char(500)
create index i1 on t1(c1)
再次执行索引相关的动态视图
因为char(500)数据类型列宽度为500字节。因为索引列的宽度很大,所以包含所有20个索引行需要2个索引页面。大的索引键值增加了索引页面的数量,从而增加了索引所需的内存和磁盘活动数量。
三、检查列的唯一性
比如说通过在表HumanResource.Employee,查找
select *
from HumanResources.Employee
where Gender='F' and SickLeaveHours=59 and MaritalStatus='M'
可以看出,返回的数据来自于扫描聚集索引(数据存储的地方)查找Gender=‘F’的相应值。
如果在该表上面的Gender列创建索引
create index ix_Employee on HumanResources.Employee(Gender)
再次执行上述查询
select *
from HumanResources.Employee
where Gender='F' and SickLeaveHours=59 and MaritalStatus='M'
可以看出
执行计划任然相同。使用以下复合索引
create index ix_Employee on HumanResources.Employee(MaritalStatus,SickLeaveHours,Gender)
with (drop_existing =on)
再次执行查询
select *
from HumanResources.Employee
where Gender='F' and SickLeaveHours=59 and MaritalStatus='M'
可以看出比用扫描聚集索引做的好。一个清晰的索引查找操作话费大约一半时间来收集数据,其余时间用作键值查找。
四、检查列数据类型
索引的数据列是重要的,例如,在一个整数键值上的索引查询是非常快的,这是应为int数据尺寸很小。
五、考虑列顺序
比如在Person.Address表上添加索引.
create index ix_test on Person.Address(City,PostalCode)
运行这个表上将使用新的索引的一个简单的select语句
select * from Person.Address as a
where a.City='Warrington'
查询I/O和执行时间
表 'Address'。扫描计数 1,逻辑读取 188 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
所以这个查询利用了索引的前沿来执行行查找操作,以检索数据。如果不适用前沿。
执行查询语句便是:
set statistics io on
select * from Person.Address as a
where a.PostalCode='WA3 7BH'
set statistics io off
查询I\O和执行时间
表 'Address'。扫描计数 1,逻辑读取 173 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
最后将查询改成下面的
set statistics io on
select a.AddressID,
a.City,
a.PostalCode
from Person.Address as a
where a.PostalCode='WA3 7BH' and a.City='Warrington'
set statistics io off
表 'Address'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
I/O和执行计划的根本变化表现了复合索引的真正用处,--------覆盖索引
六、考虑索引类型(默认一张表的主键上面建索引都是聚族索引)
SQL SERVER有聚族索引和非聚族索引。都为B-树结构。主要区别是聚族索引叶子页面是表的数据页面。
表行物理上按照聚族索引列排序,因为表数据只能有一种物理顺序,所以一个表只有一个聚族索引
堆表,没有聚族索引的表称为堆表。堆表的数据列没有任何特别的顺序。与访问大的非堆表相比,堆表无组织结构增加了访问堆表的开销。
如表dbo.DatabaseLog没有聚族索引,只有一个非聚族索引。执行下面查询
select d.DatabaseLogID,d.PostTime
from dbo.DatabaseLog d
where d.DatabaseLogID=115
可以看到有RID查找开销,因为非聚集索引通过RID查找操作来检索数据。
如果是聚族索引查找,在HumanResources.Department表中有个个聚族索引。DepartmentID
select d.DepartmentID,
d.ModifiedDate
from HumanResources.Department as d
where d.DepartmentID=10
大多数的开销都是聚集索引查找。
聚族索引如果列值为1、2、4、5.如果想在其中插入聚族索引3的列值在4、5之间,如果该位置没有足够的可用空间,数据页面将发生一次页面分割。
然而非聚族索引便不会