2012年11月12日星期一

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

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

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

  

  

(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

  

(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

  

  

(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

  

(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

(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的博文让我对分区表和索引对齐有了更加深入的认识。




TAG: