Category: 数据库技术

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 »

SQL Server执行计划详细介绍(三)

前面的SQL Server执行计划详细介绍(一)和SQL Server执行计划详细介绍(二)中我们介绍了SQL Server执行计划中的数据访问操作、书签查询以及聚合,本章节我们来介绍平时遇到得最多的join操作。 总的来说,我们会介绍三种join操作,分别是Nested Loops Join,Merge Join以及Hash Join,它们三者并没有绝对的谁好谁坏,只能说在不同的情况下有不同的join类型更适合。 Nested Loops Join 我们先来看一个Nested Loops Join的例子: 这里我们可以看到Nested Loops Join有两个输入,一个在上面,一个在下面。我们把在上面的称之为Outer Input,把在下面的称之为Inner Input。在真正执行的时候,Outer Input只会执行一次,而Inner Input则会执行Inner Input中的行数的次数,也就是说如果outer Input中有10行,我们就会执行Inner Input十次,就是会遍历Outer Input中的所有record。 在这个例子中我们可以看到上面的salesPerson的index seek的Number Of Executions是1,就是只执行了一次,而它的record数目就是Actual Number Of Batches是17,从而在下面的Employee的clustered index seek的Number of Executions就是上面的record数目,也就是执行了17次。 假如我们加一些过滤让上面的结果record变得少一点,那下面的也就会少执行几次: 我们可以点击看看对应的property,这是上面的record只有3,这样下面的执行次数也就只有3了。具体的数值这里我就不截图了。 从我们上面的解释不难看出,假如我们outter input的数目比较少,而下面的inner input又在join的key上有index的情况下,Nested Loops Join会是一个很好的选择。 Merge Join 下面我们来看一个Merge Join的例子: 和Nested Loops Join不同的是,Merge Join的两个输入都只执行一次,这一点大家可以通过查看对应的属性来确认,另外一个不同就是join的操作需要时一个相等的操作,我们这个例子里是SalesOrderId,并且他们的输入必须是join predicate有序的,我们这里可以看到都是index的scan而且都是salesorderid。 正是因为这个在join join predicatte上有序,所以在merge join的时候,我们只要同时从输入拿出一行进行比较,如果相等,我们就返回,如果不等,我们就把值小的那一行拿去除掉,因为他们是有序排列的,所以肯定不会再有一行和值小的这一行匹配了。当其中一个遍历完成之后,我们就结束了,哪怕另外一个还有很多行,也没有必要再去check了。 从这个角度来看,merge join的最大复杂度就是两个输入的和。 假如我们输入的两个列在join…

Read More »

SQL Server执行计划详细介绍(二)

我们在前文SQL Server执行计划详细介绍(一)中详细介绍了执行计划中的数据访问操作和书签查询,本文继续来介绍聚合操作。 聚合(Aggregation) 聚合一般用在一些总结的操作上,比如我们常见的求和或者平均数等操作(SUM, AVG, MAX等),在SQL Server中有两种聚合的操作:流聚合(Stream Aggregate)和哈希聚合(Hash Aggregate)。 排序和哈希 在具体介绍聚合操作之前,简单提一下排序和哈希,流聚合其实是要求数据是有序的,我们可以使用已存在的index或者就显式地进行排序操作。当然排序会使用到memory,假如可用的memory不足,它会使用tempdb,这种情况下必然也就会影响到performance了。同样的,哈希会在哈希聚合中使用,我们需要在memory中建立一个哈希表。无论排序还是哈希操作都是一个block的操作,也就是说我们必须等他们全部完成了才能进行下一步的操作。 流聚合 AVG 首先我们来看一下流聚合的操作例子,我们来看一个ListPrice的平均值: 我们查看Stream Aggregate的property(鼠标右击选择属性),可以看到整个Stream Aggregate的过程,他其实是先求的count和SUM,他们分别保存在Expr1003和Expr1004中。 在随后的Compute Scalar中,会check count数目(本例就是Expr1003)是不是0,如果是0就直接返回null,否则就用sum除以count来得到平均数。具体看下图Compute Scalar中的属性: Group by 下面我们继续来看一个Group by的例子: Group by总是要求输入是一个有序的值,所以我们可以看到在我们的例子这种会先根据ProductLine进行排序,在排序之后,我们就可以发现同样值的ProductLine就在一起了,那么在Stream Aggregate中计算count的时候的就很简单了,直接一条一条判断就可以了,是一个group的就加一,不是就新建一行。具体的Stream Aggregate操作如下图所示: 就像我们之前提到的,Group by其实是可以利用index来进行的,这样就不需要额外的排序操作了: 这个例子中,salesorderid其实是一个index,我们知道它肯定是排好序的,所以我们不需要额外的排序操作。 哈希聚合 哈希聚合一般会用在数据量比较大的表格中,当我们group的column是没有index的,并且输出的结果行也比较少的时候,我们会发现去排序整个表不太值得,我们就有可能会决定不去对整个表排序,而使用哈希聚合,见下面这个例子,我们在revisionNumber这一列上没有index,整个表有三万多行,但聚合后只有两行输出: 哈希聚合就是首先在内存中创建一个哈希表,因为我们之前也会遍历整个表,所以在遍历的时候,我们就会为每一行创建哈希key,这个哈希key可以在hash match的属性表中看到,我们这里很明显哈希key就是revisionnumber,然后每一行就会看对应的哈希值是不是已经在表中存在了,若是不存在就加一行。整个哈希表每一个哈希值一行,这也是为什么我们会说在输出(其实就是哈希值)行比较少的情况下选择会比较好,因为这样我们所建的哈希表就会相对较小。 就像我们上面提到的,哈希聚合只是说在你没有排序的情况下会使用,假如我们创建了一个index,这个时候,我们可能就直接选择一个流聚合而不需要再使用哈希聚合了,如下所示: 在继续之前让我们先把上面创建的index拿drop掉: 我想这个时候,你可能会问假如我们的表格没有排序,但是我们的query中有显式的要求排序,这个时候我们排序还是不排序呢,其实这时候我们有两种选择,一种选择就是和之前的流聚合一样,就是先排序,然后再流聚合。但是也有一个可能就是我们会先进行哈希聚合,然后再对结果进行排序。这个也不难理解,就看这两者谁的cost更小,我们就会选择谁。 还是上面的例子,我们在query中显式要求按照revisionnumber进行排序: 我们可以看到这次,我们选择的是先哈希聚合,然后再对结果进行排序。 最后我们来看一下distinct操作,其实distinct和group by是类似的,我们其实一般可以用group by来重写distinct,假如有index的话,我们显然可以直接使用流聚合操作。假如没有index,我们就需要使用distinct sort或者哈希聚合了。 可以看到这个例子中,就是使用了distinct sort,会排序并去除重复的行。 同样的,对于一个没有index的大表,我们可能会选择哈希聚合操作,如下所示: 至此,我们关于聚合操作的介绍就到这里结束了,下一章节我们会讲一讲join操作,敬请期待。 转载请注明出处:http://www.softlifelogging.com/?p=179&preview=true 更多精彩内容,敬请关注微信公众号:随手记生活

Read More »

SQL Server执行计划详细介绍(一)

众所周知,SQL Server是我们常见的关系型数据库之一。简单地写出一个存储过程实现businees功能并不是一件难事,难的是如何写出一个高效的存储过程,快速地得到我们想要的结果。也就是我们通常所说的高性能存储过程,或者说性能优化。在进行具体说明之前我们首先来看一下一个查询的执行流程是什么样的。 查询过程 SQL Server数据库引擎由两个主要部分组成,一个是存储引擎,一个是查询过程。前者主要负责磁盘和内存中进行的数据读取以及对数据一致性的保证。而后者则顾名思义,主要就是接受查询,设计出一个优化的计划,然后执行这个计划,并最终返回结果。 整个查询过程所做的事情可以用下图来表示: 当我们接收到一个查询语句的时候,第一件事就是解析这个查询语句,主要是看看他要查的是哪一个表啊,做得是一个什么join啊之类的。这一步也至少保证了我们的接收的查询是能够被解析的,否则我们也不能知道究竟要做些什么,这里的检查更多的是语法方面的检查。 在解析完成之后,就是进行绑定,这个步骤更多的是一个名字的对应处理,就是把上一步解析出来的内容和我们系统中真实存在一些object进行对应,顺便也检查这些object是否真实存在,从而进一步对查询的有效性进行验证,当然这里的检查就更多地是逻辑方面的检查了。 在绑定之后,我们就需要找到一个最优的执行计划,这里会有列出一些可能的执行计划,然后从中找到最优的执行计划。然而现实并不总是如我们所料,比如说一个查询语句,可能有几万,几十万设置几百万种执行计划,我们不可能遍历检查所有的执行计划,然后找出最优的执行计划。因为这个遍历也是要花时间的,所以这里会有一个trade off。因为我们只能说这里会找到一个局部最优解(想到了AI,哈哈)来设置执行计划。 有了查询优化后的结果,在后面两步中我们就不需要动太多的脑筋,只要执行这个查询,并返回结果就可以了。 了解了这样的整个过程之后,我们知道其实所有的关键就是查询优化这一块,为了让这一步能够选择出最优的执行计划,我们首先要来看一下它究竟会产生哪些执行计划,然后再来分析这些执行计划在我们的场景中是好还是不好,或者在我们的场景中哪些步骤占据了大的时间块,从而才可以给我们优化提供具体的建议。因此本文主要就是介绍SQL Server中的执行计划。 前期准备 本文有很多的测试来说明我们的讲解,所以需要大家准备一些软件和数据 SQL Server,我装的是SQL Server 2017 SQL Server sample data: https://github.com/Microsoft/sql-server-samples/releases, 本文使用的是AdventureWorks-oltp-install-script.zip进行测试。 数据访问的操作 首先我们来看一下数据访问的操作,所谓数据访问就是直接访问数据,可以是访问一个表也可以是访问一个索引。通常有两种方法:一种是扫描(scan)一种是查找(seek)。扫描就是读取整个结构,可以访问一个heap或者一个clustered索引或者一个non-clustered索引。而查找则不会读取整个结构,他则是更高效地通过索引访问一行,所以从这个角度来看,查找就只能应用在索引上面了。简单总结如下表所示: 下面我们来看一下几个扫描的例子,在开始具体的例子之前,先说明一下如何得到执行计划,其实得到执行计划有很多种方法,一种最简单的方法就是SSMS中打开‘include actual exectution plan’选项,如下图所示: 全表扫描 这样我们就可以看到下面的执行计划: 我们可以清楚的看到这里就是一个全表的扫描。 Clustered index 扫描 这样我们可以看到下面的执行计划: 这里有一点需要强调的是,尽管我们的clustered index在保存的时候是有序的,但并不保证我们通过他进行scan出来的结果也是有序排列的,因此如果你想要按照clustered index进行排序的结果,那么请显式加上order by的语句,如下图所示: 此时把鼠标放到clustered index scan的图标上可以看到如下结果: 这里我们可以看到在弹出的property窗口中的ordered属性被置为了true,这就表明我们的结果是有序的,而你要是返回去看看之前的这个属性,毫无疑问,它是false的。 Non-clustered index 扫描我们可以看到下面的结果: 这个例子中,我们查询了addressId, city以及stateProvinceId三列,而我们查询的是IX_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode,这个index是基于AddressLine1,AddressLine2,City,StateProvinceID和PostalCode这几列产生的,很显然city和stateProvinceId是处于这个index中的,但是addressID却不在其中,那我们究竟怎么得到这一列的呢?其实原因很简单,addressId是一个clustered index,所有的non clustered index都默认包含 clustered index这一列,所以我们在设置non clustered index的时候没有必要显式包含clustered index的列。 上面所说的三种扫描都会扫描所有的结构,下面我们来看看查询的一些例子:…

Read More »