吵吵   2014-11-01  阅读:1,555

继续聊我们LIS数据库优化的问题,那日我准备重建索引的时候,发现所有的索引都是非聚集索引。

尤其是那张有着8千万条数据的resulto结果表,res_date也被设置成为了非聚集索引。

为了验证我们的非聚集索引会极大的降低效率,我在备用库中做了个实验。

这个是res_date非聚集索引的结果:

执行的SQL语句为:

SELECT * FROM resulto WHERE res_date<'2011-03-04' AND res_date>‘2011-03-02’

SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

(148973 行受影响)
表 ‘resulto’。扫描计数 25,逻辑读取 391439 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 7629 毫秒,占用时间 = 4006 毫秒。

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

那么,当我们将res_date字段改为聚集索引之后,再看结果(sql语句相同):

SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

(128099 行受影响)
表 ‘resulto’。扫描计数 1,逻辑读取 2145 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 343 毫秒,占用时间 = 3318 毫秒。

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

我们发现聚集索引比非聚集索引优势在于:

1、扫描次数从25次降低到1次。
2、CPU时间从7629降低到343。

为什么会有这么大的差异,我们得先了解什么是聚集索引,什么是非聚集索引。

我们可以比较聚集索引和非聚集索引之间的差异,看这两张图就OK了:

聚集索引

非聚集索引

如果这张图你还无法理解的话,我们这么说:

聚集索引就是有循序的索引,相当于书的页码,我们按照顺序找过去就好了。

非聚集索引就相当于二分算法,得一点点的匹配。

举个例子,假设我要找2014-11-1的数据,在聚集索引里面,从最初的时间开始找(如2012-01-01)一直找到2014-11-1就好了。但是如果非聚集索引里面呢,先找2开头的数据,再在这里面找20开头的数据,再找201开头的数据… …直到最后找到2014-11-1的数据。

这么说来,你该明白了,为什么聚集索引扫描全表次数大大减少,因为顺序扫描大部分时候只要扫面一次就够了;为什么CPU时间较非聚集索引少了呢,因为非聚集索引要不停通过二叉树的运算进行匹配。

当然,以上的实验结果还有个问题会看的不太明白,总体占用时间之比是4006:3318,相差并不大,这是为什么呢?

这个其实也很好解释,因为取出的数据是相同的,这个时间是用于存储和返回数据消耗的时间,就没有多大变化了。

由此看来,对于上千万数据的大表来讲,建立基于时间的聚集索引,是非常有必要的。

吵吵微信朋友圈,请付款实名加入:

吵吵 吵吵

9条回应:“聚集索引与非聚集索引”

  1. 鞋机网说道:

    不懂的,看下

  2. 很深奥,俺弄不明白啊。不错。

  3. 再来看看,博主近来一切顺利吗?

  4. QQ云营销说道:

    看到标题搞不懂啥意思,这篇文章是做什么开发的?

  5. 不错,赞一个

  6. 歪妖内涵网说道:

    我来留下脚印

  7. 雨婷说道:

    你加油吧!!!

发表评论

电子邮件地址不会被公开。 必填项已用*标注