表分区的阴暗面(执行计划)

摘要:
通过CareSon的演示,我仔细观察了实施计划。CareSon的博客文章给了我几个问题:1。为什么不去非聚集索引,2。如果你转到索引3会发生什么。如果索引是非分区索引,则为4。如果表是非分区表,测试结果中分区索引会发生什么变化。根据这个问题,我写了几个sql语句以及相应的执行计划和结果:1。使用索引提示强制转到分区索引SELECTTOP100id、OrderDate、DateModified、PlaceholderFROMdbo。OrdersWITH WHERE DateModified˃='20120625'ORDERBYDateModified,Id表'Orders'。查询速度慢的原因是分区的阴暗面。

CareSon 发表了一片博文http://www.cnblogs.com/CareySon/archive/2012/10/30/2745918.html其实我碰到过类似的事情,但是没有仔细研究为什么。借着CareSon的Demo,仔细的观察了执行计划。

测试数据

  当然第一步根据CareSon的Demo建立一份测试数据。

  第二步为了做比较的需要,建一个非分区的非聚集索引,Key 和 分区对齐的非聚集索引一样。

  第三步建议一个非分区表,数据和分区表一样,我在测试的时候叫Order2

  第四步在Order2建3个索引,1 聚集索引非分区,2.非聚集索引分区,3非聚集索引非分区

疑问

  这样测试数据就准备全了。CareSon的博文给我几个问题:1.为啥不走非聚集索引,2.走了索引会怎么样,3如果索引是非分区索引呢,4如果表是非分区表,中的分区索引会怎么样

测试结果

  根据问题我写了一下几个sql和对应执行计划和结果(性能差距很大所以单看执行计划就能猜出那个性能比较好):

  1.使用索引提示,强行走分区索引

SELECT TOP 100 id,OrderDate,DateModified,Placeholder 
FROM dbo.Orders WITH (INDEX = IDX_Data_DateModified_Id_OrderDate)
WHERE DateModified >='20120625'
ORDER BY DateModified,Id

  

表分区的阴暗面(执行计划)第1张

  

(100 行受影响)
表 'Orders'。扫描计数 4,逻辑读取 286265 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 1514 毫秒,占用时间 = 1132 毫秒。

  2.在分区表中不走索引(在这里表结构要稍微的变动一下,把非聚集索引删除)

SELECT TOP 100 id,OrderDate,DateModified,Placeholder 
FROM dbo.Orders 
WHERE DateModified >='20120625'                          
ORDER BY DateModified,Id

  

表分区的阴暗面(执行计划)第2张

(100 行受影响)
表 'Orders'。扫描计数 6,逻辑读取 25229 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 266 毫秒,占用时间 = 513 毫秒。

  3.使用索引提示,强行走非分区索引

SELECT TOP 100 id,OrderDate,DateModified,Placeholder FROM 
dbo.Orders WITH (INDEX = idx_DateModified)
WHERE DateModified >='20120625' 
ORDER BY DateModified,Id

  

表分区的阴暗面(执行计划)第3张

  

(100 行受影响)
表 'Orders'。扫描计数 1,逻辑读取 318 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 122 毫秒。

  4.在非分区表中,使用索引提示走分区索引

SELECT TOP 100 id,OrderDate,DateModified,Placeholder 
FROM dbo.Orders2 with (INDEX = IDX_Data2p_DateModified_Id_OrderDate)
WHERE DateModified >='20120625'
ORDER BY DateModified,Id

  

表分区的阴暗面(执行计划)第4张

(100 行受影响)
表 'orders2'。扫描计数 4,逻辑读取 286265 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 1701 毫秒,占用时间 = 1152 毫秒。

  5.非分区表正常运行

SELECT TOP 100 id,OrderDate,DateModified,Placeholder 
FROM dbo.Orders2 
WHERE DateModified >='20120625'
ORDER BY DateModified,Id

表分区的阴暗面(执行计划)第5张

(100 行受影响)
表 'orders2'。扫描计数 1,逻辑读取 318 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 140 毫秒。

解决问题

  现在结果有了,那么就来解答先前的几个问题1.为啥不走非聚集索引,2.走了索引会怎么样,3如果索引是非分区索引呢,4如果表是非分区表,中的分区索引会怎么样

  1.为啥不走索引呢?

    1和2 对比一下,1中逻辑读大概是2中的10倍,就算SQL Server 再傻也会用2这个执行计划。

  2.走了索引会怎么样?

    看1的执行时间和执行计划,走了索引性能很烂

  3.如果索引是非分区索引呢?

    请对比1和3,2和3性能差距不是一点点,那么就引出另外一个话题,为什么非分区索引性能比分区的好呢?这个下面再讨论

  4.表如果是非分区表,中的分区索引会怎么样?

    对比4和5非常负责任的告诉你,性能很烂。

总结

  现在来说说为什么非分区索引性能在这里比分区的好,分区索引是根据分区方案,分别存放,如前面的例子直接使用某个值来过滤,索引查找会扫描所有分区,查询那些数据,那么它读取的页远远大于直接在非聚集索引中seek,自己运行demo,查看非聚集索引seek左边的排序,是根据orderdate,id排序,视乎在为了给嵌套循环做准备,之后还要根据datemodified,id排序。非分区索引严格按照排序的字段排序,因此看执行计划的时候看实际相应行数为100,但是看走分区索引的实际相应行数则为170081也就意味着走非分区索引加大了IO,和多余的计算。恰恰造成查询偏慢的原因就是因为分区的阴暗面。不恰当的使用分区。CareSon的博文让我对分区表和索引对齐有了更加深入的认识。

免责声明:文章转载自《表分区的阴暗面(执行计划)》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇tempdb相关文章在SQLSERVER中,决定当前会话的SET配置(翻译)[转]下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

SQLserver中用convert函数转换日期格式

SQLserver中用convert函数转换日期格式 2008-01-23 15:47 SQLserver中用convert函数转换日期格式2008-01-15 15:51SQLserver中用convert函数转换日期格式 SQL Server中文版的默认的日期字段datetime格式是yyyy-mm-dd Thh:mm:ss.mmm 例如:...

python,初步--L华

变量,包,模块,类的命名规范 #3:项目名包名模块名变量名函数名都是小写字母,不同的字母之间用下划线隔开 # class_basic 1(推荐) classbasic1 (不推荐) #4: 类名首字母大写驼峰命名StudentInfo HttpRequest 注释: 注释: 【#】 表示单行注释 快捷键 ctr1 + / 【''' '''】...

文件系统格式化和挂载

linux文件系统: 文件系统常用的Linux分为EXT1,EXT2,EXT3,EXT4,xfs,vfat等 windows:NTFS,fat12,fat16等 linux分区: linux文件分区工具有: fdisk---主要为msdos(MBR)分区表支持 最多只可以分出4个主分区或者3个主分区(primary)和1个逻辑分区(logical) 分区表大...

SQLServer数据库表(xxx.bak)迁移到MySql数据库中

参考博客: 下载安装: https://blog.csdn.net/weixin_43468667/article/details/89073817 将(.bak)SQLServer文件导入到Navicat: https://www.pianshen.com/article/40081312661/ 数据迁移(SQLyog工具): htt...

mysql视图

mysql视图机制 什么是视图 视图是一张虚拟的表,为什么是虚拟呢?因为视图与数据库中存在的表不太一样,前面我们创建的4张表都是包含数据的, 如用户信息,订单信息等,而视图则是不包含数据的,下面通过一个例子来演示视图,下面的sql是查询王五的所有 订单情况,需要关联到orders表,orderd etail表 tiems表 user表 mysql> s...

MYSQL中的普通索引,主健,唯一,全文索引区别

MYSQL索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索...

最新文章