索引查找

索引查找

 

问题引入：

 

表姐蓝发现用相同的列索引次序不同索引，然后同样的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