Month: July 2018

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

众所周知,我们在SQL Server性能调优的时候第一反应大概就是去check是否有index了,很多文章都会去讲如何加index,甚至SQL Server自己也会有很多index miss的提示,让你能够去build一个index。然而,真正去做性能调优的人会发现我们在工程产品中,一个query并不仅仅是一个简单的where或者一个join on,有时会涉及到一个表中多个column的filter,那么是不是简单的把这些column加到一个index中就万事大吉了呢,我看并不见得。本文就和大家一起来看看,index中column的不同顺序所带来的performance差异。 前期准备 本文有很多的测试来说明我们的讲解,所以需要大家准备一些软件和数据 SQL Server,我装的是SQL Server 2017 SQL Server sample data: https://github.com/Microsoft/sql-server-samples/releases, 本文使用的是AdventureWorks-oltp-install-script.zip进行测试。 数据准备 因为原来的sample table中没有一个很好的测试table,我们用他们的数据来重新create一个,这样也可以重新全面控制我们的index: 我们创建了一个dbo.SalesOrderHeader来进行测试,并对其中的数据进行了一些简单的处理。 测试步骤 我们主要来看一下下面这个query的performance如何: 我们可以看到这个进行的是一个table scan,这样的performance必然不会太好,所以我们的第一反应肯定就是创建一个index,我们可以看到我们的where是在revisionnumber,status,modifieddate这三个列上,那我们是不是就直接在这三列中创建index呢,所以我们可以创建如下所示的index: 在创建index后,我们看一下execution plan,果然不出所料,他如我们所想地用了clustered index seek: 我们从下面IO info可以看到 (可以通过set statistics io on 打开),有6次scan,logical read是363次。 这时候是不是就万事大吉了呢,假如我们把index的顺序更换一下结果相同吗,让我们来实验一下: 这个时候我们再执行上面的query的,我们会发现,虽然还是clustered index seek,但是这次的io read次数和之前的index有了很大不同: Logical reads的数目从之前的363变到了811次,显然performance有了很大的不同。 也就是说index中column不同的顺序,其实会引起performance的差异的,这个差异会随着你涉及的column的数目越多越明显。 追根溯源 实验可以用来说明结论,那我们可能还需要再进一步去看看这究竟是为什么呢?我们来看一下这两个不同的index究竟是怎么存储的。 先来看看我们后面这次的index情况,也就是modifiedDate是第一个,status和revisionnumber紧随其后。 我们下面的语句来看看index的情况 可以得到下面这个结果: 这个结果中的各个column的含义如下: PageFID:页面中的File的ID PagePID: PageId IAMFID: 和这个页面对应的IAM页面的file ID IAMPID: 和这个页面对应的IAM页面的page id ObjectID:这个页面的objectID…

Read More »