索引对查询效率的影响

0.参考文献

http://msdn.microsoft.com/zh-cn/library/ms172984(SQL.90).aspx

1.实验数据

我们将利用AdvantureWords2008R2中的Sales.SalesOrderDetail表，其中有12万条数据，非常适合用于测试。不过我们不直接在这张表上做测试，因为这张表上已经有索引了。我们需要新建一张表，将该表中的数据导入我们新建的test和test2表。test和test2的创建方法有两种，我们选择第二种。

--实验1：使用INSERT INTO tablename(col1,col2...) SELECT 往已存在表复制数据
 --1.创建表
 create table OrderDetail2
 (
    SalesOrderDetailID int primary key not null,
    SalesOrderID int,
    CarrierTrackingNumber nvarchar(25)
 );
 --2.插入数据
 Insert into OrderDetail2(SalesOrderDetailID,SalesOrderID,CarrierTrackingNumber) 
 select SalesOrderDetailID,SalesOrderID,CarrierTrackingNumber from AdventureWorks2008R2.Sales.SalesOrderDetail
 --实验1结束----------------------------------------------

--实验2:使用SELECT INTO创建表并复制数据
select * into test from AdventureWorks2008R2.Sales.SalesOrderDetail
select * into test2 from AdventureWorks2008R2.Sales.SalesOrderDetail
--实验2结束----------------------------------------------

2.聚集索引与非聚集索引对查询效率的影响

下面我们将通过实验来说明聚集索引和非聚集索引在查询效率上的影响。根据logic read以及execution plan我们能够更加清晰知道索引的结构，以及sql server是如何查找数据的。

--实验3:聚集索引跟非聚集索引对查询效率的影响。-----------------------
--1.统计io跟time
set statistics io on
set statistics time on
set statistics profile on

--2.在test表没有索引，因此是堆，我们首先对heap结构进行查询实验
--2.1通过dbcc ind发现有1个IAM page，1495个 data page
dbcc ind(TESTDB3,test,-1);

--2.2全表查询，table scan,逻辑读的次数是1495，Table 'test'. Scan count 1, logical reads 1495
select * from test 

--2.3条件查询,table scan,逻辑读次数是1495，Table 'test'. Scan count 1, logical reads 1495
select * from test where SalesOrderDetailID=55831

--3.接下来会在test2表上创建聚集索引和非聚集索引，然后查看创建索引以后的查询效率。
--3.1在test2表的SalesOrderDetailID列上创建聚集索引
create clustered index idx_test2_SalesOrderDetailID on test2(SalesOrderDetailID);

--3.2全表扫描,clustered index scan，所以也可以说，clustered index scan就是table scan。
--Table 'test2'. Scan count 1, logical reads 1513，逻辑多比原来多了，这是因为读了索引页
select * from test2  

--条件查询,Clustered index seek,Table 'test2'. Scan count 1, logical reads 4,发现逻辑读大大减少
select * from test2 where SalesOrderDetailID=55831 

--3.3查看test2表页信息，发现有1个IAM page，5个index page，1507个data page,indexlevel有012三层。
TRUNCATE TABLE sp_table_pages;
INSERT INTO sp_table_pages EXEC ('dbcc ind(TESTDB3,test2,-1)' );--(1513 row(s) affected)，刚好是逻辑读的数量
select * from sp_table_pages order by IndexLevel desc;
select count(*) from sp_table_pages where PageType=1;--1507条记录

--4.条件列是SalesOrderID，clustered index scan,Table 'test2'. Scan count 1, logical reads 1513
select * from test2 where SalesOrderID=55302

--4.1在SalsOrderID上面创建非聚集索引
create nonclustered index idx_test2_SalesOrderID on test2(SalesOrderID);

--4.2再次查询，execution plan是Key Lookup(Clustered)91%，Index Seek(NonClustered)9%。
--这说明是非聚集索引叶子节点找到了聚集索引的键值，然后通过聚集索引键值去查找记录，也就是Key Lookup。
--Table 'test2'. Scan count 1, logical reads 44，逻辑读大大减少。
select * from test2 where SalesOrderID=55302
--实验3结束---------------------------------------------------------------------------

3.复合索引

数据库中经常会存在复合索引，那么复合索引在什么情况下会起到查询优化作用，又在什么情况下起不到作用呢。如果查询条件是复合索引的非leading column，那么索引不起作用，不会使用这个复合索引。

--实验4：复合索引，index seek，index scan
--1.查询的内容是索引的键，index seek,直接从非聚集索引的叶子节点返回，不需要再去查找聚集索引。
--Table 'test2'. Scan count 1, logical reads 2，逻辑读只需要2次。
select SalesOrderID from test2 where SalesOrderID=55302

--2.查询的内容大于索引的键，那么查询计划是index seek+key lookup。Table 'test2'. Scan count 1, logical reads 44
select SalesOrderID,OrderQty from test2 where SalesOrderID=55302

--3.删除在SalesOrderID列上的非聚集索引
DROP INDEX idx_test2_SalesOrderID ON test2;

--4.创建复合索引
CREATE NONCLUSTERED INDEX IX_SalesOrderID_OrderQty ON test2(SalesOrderID,OrderQty);

--4.1.where条件是复合索引的leading column,9%index seek+91%key lookup,logical reads 44
select * from test2 where SalesOrderID=55302

--4.2.where条件是复合索引的leading column跟second column,34%index seek+66%key lookup,logical reads 8
select * from test2 where SalesOrderID=55302 and OrderQty=2

--4.3.where条件是复合索引的second column跟leading column,同上
select * from test2 where OrderQty=2 and SalesOrderID=55302

--4.4.where条件是复合索引的second column，clustered index scan, logical reads 1513
select * from test2 where OrderQty=2 

--5.在OrderQty列上创建单独的非聚集索引
CREATE NONCLUSTERED INDEX IX_OrderQty ON test2(OrderQty);

--5.1.logical reads 1513,clustered index scan,这是因为返回结果有14200 rows，当返回结果太大时查询优化器会选择不适用这个非聚集索引。
select * from test2 where OrderQty=2 

 PS：2012-9-3

今天看到了博客园中的数据库查询性能优化之利器—索引（二），看着觉得有点不对劲，所以对文中的疑点进行测试。

疑问一：一次查询只能使用一个索引

参考：http://www.itpub.net/thread-1623492-1-1.html

首先我们准备实验数据，在这里我新建一张OrderDetail2，并将adventureworks2008r2的 AdventureWorks2008R2.Sales.SalesOrderDetail表中的其中四列导入OrderDetail2表中，TSQL如下所示：

use TESTDB;

 --1.创建表
 create table OrderDetail2
 (
    SalesOrderDetailID int,
    SalesOrderID int,
    CarrierTrackingNumber nvarchar(25),
    UnitPrice money NOT NULL,
    CONSTRAINT PK_SalesOrderDetailID PRIMARY KEY(SalesOrderDetailID)
 );

 --2.插入数据
 Insert into OrderDetail2(SalesOrderDetailID,SalesOrderID,CarrierTrackingNumber,UnitPrice) 
 select SalesOrderDetailID,SalesOrderID,CarrierTrackingNumber,UnitPrice
 from AdventureWorks2008R2.Sales.SalesOrderDetail

--3.创建单列非聚集索引
CREATE NONCLUSTERED INDEX idx_nc_SalesOrderID ON dbo.OrderDetail2(SalesOrderID);
CREATE NONCLUSTERED INDEX idx_nc_CarrierTrackingNumber ON dbo.OrderDetail2(CarrierTrackingNumber);
CREATE NONCLUSTERED INDEX idx_nc_UnitPrice ON dbo.OrderDetail2(UnitPrice);


--5.开启统计信息
set statistics io on
set statistics time on
set statistics profile on

DBCC DROPCLEANBUFFERS--清空执行计划缓存
DBCC FREEPROCCACHE--清空数据缓存

然后我们按照UnitPrice来查询，查询语句如下：

select * from OrderDetail2 where UnitPrice =5.70

其查询计划如下：

从上述查询计划我们可以看出，一个查询使用了两个索引。在idx_nc_UnitPrice上面是哦那个了Index Seek，而在PK_SalesOrderDetailId上面使用了Clustered Index Seek。

疑问二：mutilindex(name,age,tel)。对于mutilindex，若判别条件为(name),(name,age),(name,age,tel)都可以使用该索引，而(name,tel),(age,tel),(tel)都不能够使用该做引。

接下来我们创建一个复合索引包含SalesOrderID,CarrierTrackingNumber,UnitPrice这三个列，然后测试复合索引在什么情况下会被使用。创建复合索引的TSQL如下所示：

--4.创建复合索引
CREATE NONCLUSTERED INDEX idx_nc_com ON dbo.OrderDetail2(SalesOrderID,CarrierTrackingNumber,UnitPrice);

（1）然后我们将查询条件设定为复合索引的引导列，我们会发现：where条件是引导列，不论查询的是所有列或者是单列SalesOrderID，都使用了复合索引，而没有使用单列索引。TSQL查询如下所示：

--where条件是引导列，不论查询的是所有列或者是单列SalesOrderID，都使用了复合索引，而没有使用单列索引。
select * from OrderDetail2 where SalesOrderID = 43659
select SalesOrderID from OrderDetail2 where SalesOrderID = 43659

查询计划如下图所示：

（2）如果查询条件是非引导列，那么将使用单列索引，而不使用复合索引，TSQL查询如下所示，执行计划在疑问一中已经给出。

--CarrierTrackingNumber和UnitPrice是非引导列，所以使用自身的单列索引。与查询结果的条目数无关
select * from OrderDetail2 where CarrierTrackingNumber = '4911-403C-98'
select * from OrderDetail2 where UnitPrice=1.3282

（3）where查询条件包含了引导列，那么不论引导列在where条件的何处(多条件情况)，都会使用复合索引。

--不论引导列在where条件的何处位置，只要包含了引导列，那么就会使用复合索引。
--引导列紧跟 where
select * from OrderDetail2 where SalesOrderID = 51702 and CarrierTrackingNumber='48F0-4F3E-AE'
select * from OrderDetail2 where SalesOrderID = 51702 and UnitPrice=1.374
select * from OrderDetail2 where SalesOrderID = 51702 and CarrierTrackingNumber='48F0-4F3E-AE' and UnitPrice=1.374
--将引导列的位置放在后面
select * from OrderDetail2 where UnitPrice=1.374 and SalesOrderID = 51702

查询计划如（1）所示。

（4）不包含引导列。假如where条件不包含引导列，那么将不会使用复合索引。比如执行如下TSQL查询，就没有使用复合索引，而是使用了两个单列各自的非聚集索引。这又是一个“一个查询可以使用多个索引”的例子。

select * from OrderDetail2 where CarrierTrackingNumber='48F0-4F3E-AE' and UnitPrice=1.374

上述查询的查询计划如下图所示：

总结：对于符合复合mutilindex(name,age,tel)。若判别条件为(name),(name,age),(name,tel),(name,age,tel)都可以使用该复合索引，而(age,tel),(tel)都不能够使用该做引。