索引查找

索引查找

问题引入：

表姐蓝发现用相同的列索引次序不同索引，然后同样的SQL语句执行时间却不同

CREATE INDEX INDEX_YN_CREATEDATE ON ORDERS(YN,CREATEDATE)

--118 毫秒

CREATE INDEX INDEX_YN_CREATEDATE ON ORDERS(CREATEDATE,YN)

--187 毫秒

分析过程：

------------------------------------------------------------------------

-- Author: happyflystone

-- Date : 2009-02-13 15:00:07

-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

-- Apr 14 2006 01:12:25

-- Copyright (c) 1988-2005 Microsoft Corporation

-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

--

------------------------------------------------------------------------

-- Test Data: TA

IF OBJECT_ID('TA') IS NOT NULL

DROP TABLE TA

Go

CREATE TABLE TA(PID INT PRIMARY KEY,ID INT,COL DATETIME)

Go

CREATE INDEX INDEX_YN_CREATEDATE2 ON TA(COL,ID)

GO

CREATE INDEX INDEX_YN_CREATEDATE1 ON TA(ID,COL)

GO

SELECT *

FROM TA WHERE COL >= CONVERT(VARCHAR(10),GETDATE()-300,120)

AND COL <= CONVERT(VARCHAR(10),GETDATE(),120) AND ID = 1

|--INDEX SEEK(OBJECT:([CSDNDB].[DBO].[TA].[INDEX_YN_CREATEDATE2]), SEEK:([CSDNDB].[DBO].[TA].[COL] >= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE()-CONVERT_IMPLICIT(DATETIME,[@1],0),120),0) AND [CSDNDB].[DBO].[TA].[COL] <= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120),0)), WHERE:(CONVERT(CHAR(1),[CSDNDB].[DBO].[TA].[ID],0)=[@2]) ORDERED FORWARD)

INDEX SEEK OBJECT:([CSDNDB].[DBO].[TA].[INDEX_YN_CREATEDATE2]), SEEK:([CSDNDB].[DBO].[TA].[COL] >= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE()-CONVERT_IMPLICIT(DATETIME,[@1],0),120),0) AND [CSDNDB].[DBO].[TA].[COL] <= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120),0)), WHERE:(CONVERT(CHAR(1),[CSDNDB].[DBO].[TA].[ID],0)=[@2]) ORDERED FORWARD [CSDNDB].[DBO].[TA].[PID], [CSDNDB].[DBO].[TA].[COL], [CSDNDB].[DBO].[TA].[ID]

SELECT *

FROM TA

WHERE ID = 1

AND COL >= CONVERT(VARCHAR(10),GETDATE()-300,120)

AND COL <= CONVERT(VARCHAR(10),GETDATE(),120)

|--INDEX SEEK(OBJECT:([CSDNDB].[DBO].[TA].[INDEX_YN_CREATEDATE1]), SEEK:([CSDNDB].[DBO].[TA].[ID]=CONVERT_IMPLICIT(BIGINT,[@1],0) AND [CSDNDB].[DBO].[TA].[COL] >= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE()-CONVERT_IMPLICIT(DATETIME,[@2],0),120),0) AND [CSDNDB].[DBO].[TA].[COL] <= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120),0)) ORDERED FORWARD)

INDEX SEEK OBJECT:([CSDNDB].[DBO].[TA].[INDEX_YN_CREATEDATE1]), SEEK:([CSDNDB].[DBO].[TA].[ID]=CONVERT_IMPLICIT(BIGINT,[@1],0) AND [CSDNDB].[DBO].[TA].[COL] >= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE()-CONVERT_IMPLICIT(DATETIME,[@2],0),120),0) AND [CSDNDB].[DBO].[TA].[COL] <= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120),0))

查询计划如下：

(CSDN不让上传图片)

――解释

索引查找

索引查找操作是SQL Server从索引中读取数据采用的迭代器，返回特定谓词上一个或多个范围内的数据行，仅扫描满足该谓词的数据页，查询开销显然要比表中总记录数的开销低，因此，对于大数据量的表进行查询时，使用查找谓词是比较有效率的。

谓词与谓词覆盖

索引要确保包含或覆盖查询中引用的列集合，关于查询优化的例子很多，对列是否能进行索引查找相信大家不陌生吧。

好，对上面的例子进行简要说明，第一个查询使用INDEX_YN_CREATEDATE2(COL,ID)索引，在第一个键列上进行索引查找，然后使用residual谓词来估计ID，

SEEK:([CSDNDB].[DBO].[TA].[COL] >= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE()-CONVERT_IMPLICIT(DATETIME,[@1],0),120),0) AND [CSDNDB].[DBO].[TA].[COL] <= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120),0)), WHERE:(CONVERT(CHAR(1),[CSDNDB].[DBO].[TA].[ID],0)=[@2])

第二个查询在ID，COL索引上进行利用索引INDEX_YN_CREATEDATE1(ID，COL)进行查找。

SEEK:([CSDNDB].[DBO].[TA].[ID]=CONVERT_IMPLICIT(BIGINT,[@1],0) AND [CSDNDB].[DBO].[TA].[COL] >= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE()-CONVERT_IMPLICIT(DATETIME,[@2],0),120),0) AND [CSDNDB].[DBO].[TA].[COL] <= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120),0)) ORDERED FORWARD)

INDEX SEEK OBJECT:([CSDNDB].[DBO].[TA].[INDEX_YN_CREATEDATE1]), SEEK:([CSDNDB].[DBO].[TA].[ID]=CONVERT_IMPLICIT(BIGINT,[@1],0) AND [CSDNDB].[DBO].[TA].[COL] >= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE()-CONVERT_IMPLICIT(DATETIME,[@2],0),120),0) AND [CSDNDB].[DBO].[TA].[COL] <= CONVERT_IMPLICIT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120),0))

DROP TABLE TA

GO