sql语句大全之IN和OR会走索引吗？

当前位置:

首页 > 数据库 > sql数据库 >

sql语句大全之IN和OR会走索引吗？

--注：所有步骤均按序号分步单独执行，并观察对应的情况

--0.

USE tempdb

IF OBJECT_ID('t') IS NOT NULL DROP TABLE t

CREATE TABLE t(pkId INT IDENTITY(1,1) PRIMARY KEY, id VARCHAR(10) NOT NULL, room VARCHAR(10) NOT NULL, otherInfo VARCHAR(100))

--构建 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'

-------------- 以上为构建测试表及测试数据 ------------------

-------------- 以下为测试没有索引的情况 ------------------

--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

)

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

作者：吉普赛的歌

来源：CSDN

原文：https://blog.csdn.net/yenange/article/details/79665328

栏目列表