SQL Server索引使用

一、索引设计注意事项

（1）、检查WHERE条件和连接条件

当一条SQL语句提交时，查询优化器会根据表的信息查询最优的访问机制

1、优化器首先识别WHERE子句与连接条件中包含的列

2、优化器检索这些列上的索引

3、优化器通过从索引上的维护统计来确定子句的选择性以及评估索引的有效性

4、优化器根据前面几个步骤的信息，选择估计开销最低的方式

DBCC FREEPROCCACHESET STATISTICS IO ONSET STATISTICS TIME ONSELECT p.ProductID,p.Name,p.StandardCost,p.Weight,p.Color,p.ProductNumber FROM Production.Product AS pWHERE p.ProductID=871表 'Product'。扫描计数 0，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

这里采用的数据库是微软提供的示例数据库AdventureWorks2008。

WHERE条件里面有ProductID，该字段上面有一个聚簇索引(主键)，根据优化器的选择，最终选择该索引

DBCC FREEPROCCACHESET STATISTICS IO ONSET STATISTICS TIME ONSELECT pm.Name,p.ProductID,p.Name,p.StandardCost,p.Weight,p.Color,p.ProductNumber FROM Production.Product AS pINNER JOIN Production.ProductModel AS pm ON pm.ProductModelID=p.ProductModelIDWHERE p.ProductID=871表 'ProductModel'。扫描计数 0，逻辑读取 2 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'Product'。扫描计数 0，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 

ProductModelID是ProductModel的主键，也是Product的外键 ，ProductID是Product的主键，查看执行计划从上到下，从右到左。

首先根据ProductID=871查找Product表，这里使用聚簇索引没有问题。

第二部就是ProductModel表，这里根据连接条件来，发现也有可用的聚簇索引，所以上图使用了两个聚簇索引。

 

（2）、使用窄索引

在实际中，一般使用整形列来作为索引列，像VARCHAR、NVARCHAR等可能会很大，一般不用来作为索引列。

窄索引可以在8KB的索引页面容纳更多的行，这样有许多好处

A、减少IO数量（读取更少的8KB页面）

B、是数据库缓存更有效，SQL Server可以缓存更少的索引页面，从而减少内存中索引页面的逻辑读

C、减少数据库存储空间

首先创建一个表：

IF EXISTS(SELECT 1 FROM sys.tables WHERE name='test' AND type='U')DROP TABLE dbo.testGOCREATE TABLE dbo.test(obj_id INT,obj_name VARCHAR(200))GOINSERT INTO dbo.test(obj_id,obj_name)SELECT object_id,name FROM sys.objectsGOIF EXISTS(SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID(N'dbo.test') AND name='ix_obj_id')DROP INDEX ix_obj_id ON dbo.testGOCREATE INDEX ix_obj_id ON dbo.test(obj_id)GO

通过动态函数查看该索引的具体信息：

SELECT ix.name,ix.type_desc,ixStats.page_count,ixStats.record_count,ixStats.index_levelFROM sys.indexes AS ixINNER JOIN sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2008'),OBJECT_ID(N'dbo.test'),NULL,NULL,'DETAILED') AS ixStatsON ix.index_id=ixStats.index_idWHERE ix.name='ix_obj_id'name        type_descpage_countrecord_countindex_levelix_obj_idNONCLUSTERED2        583        0ix_obj_idNONCLUSTERED1        2        1

下面修改建表语句：

 

IF EXISTS(SELECT 1 FROM sys.tables WHERE name='test' AND type='U')DROP TABLE dbo.testGOCREATE TABLE dbo.test(obj_id CHAR(500),obj_name VARCHAR(200))GOINSERT INTO dbo.test(obj_id,obj_name)SELECT object_id,name FROM sys.objectsGOIF EXISTS(SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID(N'dbo.test') AND name='ix_obj_id')DROP INDEX ix_obj_id ON dbo.testGOCREATE INDEX ix_obj_id ON dbo.test(obj_id)GO

obj_id字段改成了CHAR(500) 了，肯定比INT型大了，索引的统计信息如下：

SELECT ix.name,ix.type_desc,ixStats.page_count,ixStats.record_count,ixStats.index_levelFROM sys.indexes AS ixINNER JOIN sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2008'),OBJECT_ID(N'dbo.test'),NULL,NULL,'DETAILED') AS ixStatsON ix.index_id=ixStats.index_idWHERE ix.name='ix_obj_id'name        type_descpage_countrecord_countindex_levelix_obj_idNONCLUSTERED39        583        0ix_obj_idNONCLUSTERED3        39        1ix_obj_idNONCLUSTERED1        3        2

可以很明显的看出页面数量多了很多。

 

(3)、检查列的唯一性

在一个很小范围上面加上索引对性能是没有好处的，查询优化器是不能使用该索引有效减少返回的行数，有时候优化器经过判断不会采用该索引。

DBCC freeproccacheSET STATISTICS IO ONSET STATISTICS TIME ONSELECT * FROM Person.Person WHERE PersonType='IN'表 'Person'。扫描计数 1，逻辑读取 3816 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 78 毫秒，占用时间 = 1787 毫秒。

这里根据PersonType='IN'来查询，由于没有索引可用，只有通过聚簇索引扫描来筛选了。

在PersonType字段上面加上索引

IF EXISTS(SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID(N'Person.Person') AND name='IX_personType')DROP INDEX IX_personType ON Person.PersonGOCREATE INDEX IX_personType ON Person.Person(PersonType)GO

然后强制使用该索引：

DBCC freeproccacheSET STATISTICS IO ONSET STATISTICS TIME ONSELECT * FROM Person.Person WITH(INDEX(IX_personType))WHERE PersonType='IN'表 'Person'。扫描计数 1，逻辑读取 56644 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间: CPU 时间 = 94 毫秒，占用时间 = 1870 毫秒。

 

根据二者比较发现加这个索引不仅没有提高查询效率，反而在逻辑读与时间上还不如按照聚簇索引扫描

 （4）、列的数据类型

通常优先考虑整形

 

（5）、复合索引列的顺序

复合索引，首先是在第一列进行索引键排序，然后在此基础上按照第二列进行排序，因此第一列的位置是很重要的。在Oracle中复合索引需要注意前缀性与可选性，那么在SQL Server中也有类型的注意问题

A、列的唯一性，也就是相同值的个数，想性别之类的字段不仅在单一索引中不要用，在复合索引中也不建议使用

B、列的宽度，前面已经说过，最好使用INT型

C、列的数据类型，当然优先考虑整形

这里建一个索引，但是列的顺序不一样：

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Person].[Address]') AND name = N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode')DROP INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address] WITH ( ONLINE = OFF )GOCREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address] ([AddressLine1] ASC,[City] ASC,[PostalCode] ASC,[StateProvinceID] ASC,[AddressLine2] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]GO----------------------------------------------------------------------------------------IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Person].[Address]') AND name = N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode')DROP INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address] WITH ( ONLINE = OFF )GOCREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address] ([StateProvinceID] ASC,[City] ASC,[PostalCode] ASC,[AddressLine1] ASC,[AddressLine2] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]GO

地址大小：州——>城市——>邮政编码——>街道地址

下面采用第一种索引：

DBCC freeproccacheSET STATISTICS IO ONSET STATISTICS TIME ONSELECT * FROM Person.Address WHERE AddressLine1='1970 Napa Ct.'表 'Address'。扫描计数 1，逻辑读取 9 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

采用第二种索引：

DBCC freeproccacheSET STATISTICS IO ONSET STATISTICS TIME ONSELECT * FROM Person.Address WHERE AddressLine1='1970 Napa Ct.'表 'Address'。扫描计数 1，逻辑读取 218 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 3 毫秒。

可以比较这两种方式建立的索引的好坏
 

（6）、索引类型

SQL Server主要有两种索引类型，聚簇索引与非聚簇索引。

局促索引的叶子页面和表的数据页面相同，一个表只能有一个聚簇索引，主键默认是聚簇索引。

非聚簇索引建立在聚簇索引之上，里面包含一个指向数据行的指针

 

聚簇索引注意事项：

A、首先创建聚簇索引，然后在创建非聚簇索引，因为非聚簇索引的索引行上存有聚簇索引的索引键

B、保持窄索引，通常使用整形

C、一步重建聚簇索引，由于非聚簇索引对聚簇索引的依赖，因此聚簇索引不能先删除再创建，最好使用DROP_EXISTING子句

D、聚簇索引对检索一定范围、读取预先排好序的数据最有效

E、对于频繁更新、宽字段、太多并行的顺序插入的字段不建议使用

 

非聚簇索引注意事项：

在使用非聚簇索引的时候，如果检索的列不属于非聚簇索引的一部分，那么将会定义一个查找——书签查找

A、非聚簇索引适用于从一个大表读取少量数据

B、针对不使用与聚簇索引的列，可以考虑使用非聚簇索引

C、非聚簇索引不使用检索大量的行的查询