SQL索引谣言粉碎

11/25/2015来源:ASP.NET技巧人气:2119

很多文章都提到使用IN,OR会破坏索引,造成全表扫描,但实际测试却不是这样。

SELECT c FROM t WHERE c = 1
SELECT c FROM t WHERE c in (1)

或者

SELECT c FROM t WHERE c = 1 OR c = 2
SELECT c FROM t WHERE c in (1,2)

以上SQL文,第一组(=,IN),第二组(=,OR,IN),每一组的两个SQL文都使用相同的执行计划,执行计划中也可以看到使用了相同的索引,磁盘活动信息中的逻辑读次数也相同。

测试中需要的设定:

SET STATISTICS IO ON 使SQLSERVER显示Transact-SQL语句生成的磁盘活动量信息。
SET STATISTICS TIME ON 显示分析,编译,执行语句所需要的毫秒数

每次执行查询前的处理:

CHECKPOINT 强制将当前数据库的全部脏页写入磁盘,然后清除缓冲区
DBCC DROPCLEANBUFFERS  从缓冲池中删除所有CleanBuffers

测试:

SQLServer 2008 R2

SQLServer 2014

1.新建数据库表:

CREATE TABLE [dbo].[TestTable](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Birthday] [varchar](10) NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

2.向Birthday中插入大量数据

3.建立Birthday列的非聚集索引

4.查询测试

CHECKPOINT 
DBCC DROPCLEANBUFFERS
SELECT birthday FROM [dbo].[TestTable] WHERE Birthday IN ('19901111')

表 'TestTable'。扫描计数 1,逻辑读取 8 次,物理读取 2 次,预读 5 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

CHECKPOINT
DBCC DROPCLEANBUFFERS
SELECT Birthday FROM [dbo].[TestTable] WHERE Birthday IN ('19901111')

表 'TestTable'。扫描计数 1,逻辑读取 8 次,物理读取 2 次,预读 5 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

下面的语句执行后,查询计划以及读取次数也都是一样的。

CHECKPOINT 
DBCC DROPCLEANBUFFERS
SELECT Birthday FROM [dbo].[TestTable] WHERE Birthday = '19901111' OR Birthday = '19901212'

表 'TestTable'。扫描计数 2,逻辑读取 16 次,物理读取 2 次,预读 10 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

CHECKPOINT 
DBCC DROPCLEANBUFFERS
SELECT birthday FROM [dbo].[TestTable] WHERE Birthday IN ('19901111','19901212')

表 'TestTable'。扫描计数 2,逻辑读取 16 次,物理读取 2 次,预读 10 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

执行计划截图这里就不上传了,有兴趣的话,大家可以自行验证。