IN和OR会走索引吗？

--注：所有步骤均按序号分步单独执行，并观察对应的情况
--0. 
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(pkId INT IDENTITY(1,1) PRIMARY KEY, id VARCHAR(10) NOT NULL, room VARCHAR(10) NOT NULL, otherInfo VARCHAR(100))
GO
--构建 1000 * 1000 + 2 = 1百万+2条 记录
;WITH cte AS (
	SELECT sv.number FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND 1000
)
INSERT INTO t(id,room)
SELECT LEFT(NEWID(),3),LEFT(NEWID(),4)  FROM cte AS a CROSS APPLY cte AS b
UNION ALL
SELECT id ='001',room='A201'
UNION ALL
SELECT id ='002',room='A202'
GO
-------------- 以上为构建测试表及测试数据 ------------------

-------------- 以下为测试没有索引的情况   ------------------
--1.打开执行计划, 删除索引的脚本便于回过头来执行
IF EXISTS(SELECT 1 FROM sys.indexes AS i WHERE i.name='ix_t_id_room') 
	DROP INDEX ix_t_id_room ON t
--1.1 Clustered Index Scan （聚集索引扫描）
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT pkId FROM t WHERE id IN ('001','002') AND room IN ('A201','A202')
/*
 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
表 't'。扫描计数 1，逻辑读取 3229 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 281 毫秒，占用时间 = 299 毫秒。
*/
--1.2 Clustered Index Scan （聚集索引扫描）
SELECT pkId FROM t WHERE (id ='001' OR id='002') AND (room = 'A201' OR room = 'A202')
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
表 't'。扫描计数 1，逻辑读取 3229 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 359 毫秒，占用时间 = 352 毫秒。
*/
--1.3 Clustered Index Scan （聚集索引扫描）
SELECT pkId FROM t WHERE (id ='001' AND room = 'A201') OR (id='002' AND room = 'A202')
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
表 't'。扫描计数 1，逻辑读取 3229 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 359 毫秒，占用时间 = 346 毫秒。
*/
--1.4 Clustered Index Scan （聚集索引扫描） + Concatenation（串联）
SELECT pkId FROM t WHERE id='001' AND room='A201'
UNION ALL
SELECT pkId FROM t WHERE id='002' AND room='A202'
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 2654 毫秒，占用时间 = 2654 毫秒。
表 't'。扫描计数 2，逻辑读取 6458 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 265 毫秒，占用时间 = 257 毫秒。
*/

-------------- 以下为测试有索引的情况   ------------------
--2.创建索引
CREATE INDEX ix_t_id_room ON t(id,room)

--2.1 Index Seek （索引查找）
SELECT pkId FROM t WHERE id IN ('001','002') AND room IN ('A201','A202')
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 2 毫秒，占用时间 = 2 毫秒。
表 't'。扫描计数 4，逻辑读取 12 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
*/
--2.2 Index Seek （索引查找）
SELECT pkId FROM t WHERE (id ='001' OR id='002') AND (room = 'A201' OR room = 'A202')
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
表 't'。扫描计数 4，逻辑读取 12 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
*/
--2.3 Index Seek （索引查找）
SELECT pkId FROM t WHERE (id ='001' AND room = 'A201') OR (id='002' AND room = 'A202')
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
表 't'。扫描计数 2，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
*/
--2.4 Index Seek （索引查找） + Concatenation（串联）
SELECT pkId FROM t WHERE id='001' AND room='A201'
UNION ALL
SELECT pkId FROM t WHERE id='002' AND room='A202'
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
表 't'。扫描计数 2，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
*/

/*******************************************
 ****************  结论  *******************
 1. id IN ('001','002') AND room IN ('A201','A202') 
    或 (id ='001' OR id='002') AND (room = 'A201' OR room = 'A202')
    不符合逻辑，因为产生了4种可能，只是用来测试一下执行计划及效率;
 2. IN 和 OR 不一定就不走索引，很多情况下SQL Server还是会自动优化的;
    个人的优化经验1：子查询中 IN 数量不要超过外表总数量的千分之2;
    个人的优化经验2：较复杂的查询, OR 尽量用 UNION ALL 来代替。
 3. 最重要的一点：数据库优化没有一定之规，不能看了某篇文章就指望着靠那些理论一劳永逸打天下。
    勤动手，多测试，看执行计划和IO、CPU时间消耗是不二法门。
 *******************************************/


--下面是对于 IN 在数据量多大时走索引的测试
/*
总数据量：100万+2, 临界点：万分之23.53
子查询在 TOP<=2353 ，外查询走 Clustered Index Seek 
子查询在 TOP> 2353 ，外查询走 Clustered Index Scan 
*/
SELECT * FROM t WHERE t.pkId IN (
	SELECT TOP 2353 pkId FROM t
)

--清空原表， 插入10万条数据
TRUNCATE TABLE t
;WITH cte AS (
	SELECT sv.number FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND 1000
)
INSERT INTO t(id,room)
SELECT LEFT(NEWID(),3),LEFT(NEWID(),4)  FROM cte AS a CROSS APPLY (SELECT TOP 100 * FROM cte) AS b

/*
总数据量：10万， 临界点：万分之24
子查询在 TOP<=240 ，外查询走 Clustered Index Seek 
子查询在 TOP> 240 ，外查询走 Clustered Index Scan 
*/
SELECT * FROM t WHERE t.pkId IN (
	SELECT TOP 241 pkId FROM t
)