SQL Server中index column顺序不同所产生的性能差别

众所周知,我们在SQL Server性能调优的时候第一反应大概就是去check是否有index了,很多文章都会去讲如何加index,甚至SQL Server自己也会有很多index miss的提示,让你能够去build一个index。然而,真正去做性能调优的人会发现我们在工程产品中,一个query并不仅仅是一个简单的where或者一个join on,有时会涉及到一个表中多个columnfilter,那么是不是简单的把这些column加到一个index中就万事大吉了呢,我看并不见得。本文就和大家一起来看看,indexcolumn的不同顺序所带来的performance差异。

前期准备

本文有很多的测试来说明我们的讲解,所以需要大家准备一些软件和数据

数据准备

因为原来的sample table中没有一个很好的测试table,我们用他们的数据来重新create一个,这样也可以重新全面控制我们的index

我们创建了一个dbo.SalesOrderHeader来进行测试,并对其中的数据进行了一些简单的处理。

测试步骤

我们主要来看一下下面这个queryperformance如何:

我们可以看到这个进行的是一个table scan,这样的performance必然不会太好,所以我们的第一反应肯定就是创建一个index,我们可以看到我们的where是在revisionnumberstatusmodifieddate这三个列上,那我们是不是就直接在这三列中创建index呢,所以我们可以创建如下所示的index

在创建index后,我们看一下execution plan,果然不出所料,他如我们所想地用了clustered index seek

我们从下面IO info可以看到 (可以通过set statistics io on 打开),有6scanlogical read363次。

这时候是不是就万事大吉了呢,假如我们把index的顺序更换一下结果相同吗,让我们来实验一下:

这个时候我们再执行上面的query的,我们会发现,虽然还是clustered index seek,但是这次的io read次数和之前的index有了很大不同:

Logical reads的数目从之前的363变到了811次,显然performance有了很大的不同。

也就是说indexcolumn不同的顺序,其实会引起performance的差异的,这个差异会随着你涉及的column的数目越多越明显


追根溯源

实验可以用来说明结论,那我们可能还需要再进一步去看看这究竟是为什么呢?我们来看一下这两个不同的index究竟是怎么存储的。

先来看看我们后面这次的index情况,也就是modifiedDate是第一个,statusrevisionnumber紧随其后。

我们下面的语句来看看index的情况

可以得到下面这个结果:

这个结果中的各个column的含义如下:

  • PageFID:页面中的FileID
  • PagePID PageId
  • IAMFID 和这个页面对应的IAM页面的file ID
  • IAMPID 和这个页面对应的IAM页面的page id
  • ObjectID:这个页面的objectID
  • IndexID 这个页面的index id
  • partitionID 这个页面的partition id
  • pageType: 页面的类型:
    • 1 — 数据页面
    • 2 — index页面
    • 3,4 文本页面
    • 8 — GAM页面
    • 9 — SGAM页面
    • 10 — IAM 页面
    • 11 — PFS页面
  • IndexLevel 就是索引的级别,这个索引的级别在叶子极点是0,然后越往跟节点数值越大。
  • NextPageFID NextPagePID 就是下一页的页面fileidpageid,页面之间都是通过双向链表连接的。
  • PrevPageFID PrevPagePID: 这个大家想想就知道了,就是前一个页面的FileidpageiD了。

下面我们就来分析一下index页面的情况,这里为了方便,我们把上面dbcc ind的结果保存到一个表中,然后filter出对应的结果,我们的根节点在indexlevel2上。

我们可以到的如下结果:

根据这个结果,我们可以得到indexpage如下图所示:

索引页在level1之间是一个双向的链表。那么我们下面随便来看一个索引页内的内容:

我们可以看到他其实是对应多个数据页的,他其中是按照我们index来进行排序的,第一个是ModifiedDate,然后是status,然后是revisionNumber。所以,当我们进行query的时候,就需要先根据modifiedData来先进行定位,然后再进一步搜索statusrevisionNumber的内容。

不难想象,假如我们把revisionNumberstatus放在index的前面,他们的索引页应该和这个很不相同。

这时候我们可以看到对应的结果如下:

所以这种情况下的索引页的分布是这样的:

很明显,我们可以看到索引页都变少了,level1的索引页只有4页,我们同样来看其中一页的情况:

这个时候我们可以看到RevisionNumber在最前面,statusmodifiedDate在后面,因为revisionNumberdistinct的数目比较少,所以他的切割会更加合理。

至此,本文就详细解释了index中不同column顺序对performance的影响,也希望这篇文章对你在database的性能调优中能够有所帮助。


转载请注明出处:http://www.softlifelogging.com/?p=207&preview=true

更多精彩内容敬请关注微信公众号: 随手记生活

No Comments

Leave a Reply

Your email address will not be published.