VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > sql数据库 >
  • sql语句大全之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
)
 
--------------------- 
作者:吉普赛的歌 
来源:CSDN 
原文:https://blog.csdn.net/yenange/article/details/79665328 
版权声明:本文为博主原创文章,转载请附上博文链接!

相关教程