澳门金沙vip 11

【澳门金沙vip】浅析SQL查询语句未显式钦点排序格局,不恐怕保障同风姿罗曼蒂克的查询每一回排序结果都同样的来由

  

《Microsoft Sql server 2008 Internals》读书笔记订阅地址:

 

本文出处: 

   
许多有经验的数据库开发或者DBA都曾经头痛于并行查询计划,尤其在较老版本的数据库中(如sqlserver2000、oracle
7、mysql等)。但是随着硬件的提升,尤其是多核处理器的提升,并行处理成为了一个提高大数据处理的高效方案尤其针对OLAP的数据处理起到了很好的作用。

 

《Microsoft Sql server 2008 Internals》索引目录:

   
充分高效地利用并行查询需要对调度、查询优化和引擎工作等有一个比较好的了解,但是针对一般场景的应用我们只需要如何常规使用即可,这里也就不深入描述了,感兴趣可以一起讨论。

 

《Microsoft Sql server 2008
Internal》读书笔记–目录索引

    那么这里我就简单介绍下SQLServer中并行的应用?

  标题有点拗口,来源于一个开发人员遇到的实际问题
  先抛出问题:一个查询没有明确指定排序方式,那么,第二次执行这个同样的查询的时候,查询结果会不会与第一次的查询结果排序方式完全一样?
  答案是不确定的,两个完全一样的查询,结果也完全一样,两次(多次)查询结果的排序方式有可能一致,有可能不一致。
  如果不一致,又是什么原因导致同样的查询默认排序方式不一致?
  以下简单分析几种情况,说明为什么查询同样的查询会出现默认排序结果不一样的情况。当然对于该问题,包含但不限于以下几种情况。

上文主要介绍有效处理数据库的几个方法:事实生成、使用查询处理器。本文关注批处理、读取数据页到进程和并行机制。

什么是并行?

我们从小就听说过“人多力量大”、“人多好办事”等,其思想核心就是把一个任务分给许多人,这样每个人只需要做很少的事情就能完成整个任务。更重要的是,如果额外的人专门负责分配工作,那么任务的完成时间就可以大幅减少了。

 

■批处理

数糖豆

   
设想你正面对一个装满各式各样糖豆的罐子,并且要求书有多少个。假设你能平均每秒数出五个,需要大于十分钟才能数完这个盒子里的3027个糖豆。

   
如果你有四个朋友帮助你去做这个任务。你就有了多种策略来安排这个数糖豆任务,那让我们模仿SQLServer
将会采取的策略来完成这个任务。你和4个朋友围坐在一个桌子四周,糖果盒在中心,用勺子从盒子中拿出糖豆分给大家去计数。每个朋友还有一个笔和纸去记录数完的糖豆的而数量。

   
一旦一个人输完了并且盒子空了,他们就把自己的纸给你。当你收集完每个人的计数,然后把所有的数字加在一起就是糖豆的数量。这个任务也就完成了。大概1-2分钟,完成的效率提高了四倍多。当然四个人累加也是十分钟左右甚至还要多(因为多出来了分配和累加的过程)。这个任务很好的展示了并行的优点,也没有其他额外的工作需要处理。

场景1:并行查询导致默认结果集的排序是随机的

在per-table逻辑检查期间,DBCC
CHECKED
通常并不同时处理数据库所有的表,也通常不会在某个时间处理一个单表。它将表分组进批处理(Beatches),并在批处理中的所有表上运行事实生成和聚集算法。所有批处理被处理完后,数据库中的所有表都被一致性检查完毕。 

使用SQLServer 完成“数糖豆”

    当然SQLServer
不会去数罐子里的糖豆,那我就让它去计算表里的行数。如果表很小那么执行计划如图1:

澳门金沙vip 1

图1  串行执行计划:

这个查询计划使用了单一进程,就好像自己一个人数糖豆一样。计划本身很简单:流聚合操作符负责统计接收来自索引扫描操作符的行数,然后统计出总行数。相似的情况下,如果盒子里面糖豆非常少,虽然分配糖豆的时间会减少很多,但是统计步骤就显得效率不是那么高了,因为相对于大数量的糖豆这部分的所占时间就高很多了。所以当表足够大,SQLServer
优化器可以选择增加更多的线程,执行计划如图2:

澳门金沙vip 2

图2 并行计数计划

 

右侧三个操作符中的黄色箭头图标表示引入了多线程。每个线程被分配了一部分工作,然后完成分分部工作被聚集在一起成为最终结果。如同前面人工数糖豆的例子一样,并行计划有很大可能提高完成速度,因为多线程在计数上更优。

按照惯例,先造一个表供测试

DBCC
CHECKED
为什么要将数据库分割为一系列的批处理呢?主要是tempdb数据库(为事实存储而)需求的空间数量的限制。每个生成的事实占用一定数量的空间,取决于事实的类型的内容。一个架构越复杂,必须生成(以允许将要被一致性检查的表架构的所有方面)的事实越多。

并行如何工作?

 

设想一下,如果SQLServer没有内置对于并行的支持。或许我们只能手动去平均划分并行查询来实现性能优化,然后分别运行分配的流,独立地访问服务器。

澳门金沙vip 3

图3 手动分配并行

每次查询都必须手写分隔表行数的独立查询,确保全表数据都被查询到。幸运的是SQLServer
能在一个处理单元内完成每一个分隔的独立线程,然后接收三个部分结果集只需要三分之一的时间左右。自然地我们还需要额外的时间来合并三个结果集。

create table TestDefaultOrder1
(
    id int identity(1,1) primary key,
    col2 varchar(50),
    col3 varchar(50),
    col4 varchar(50),
    col5 varchar(50),
    col6 varchar(50),
    col7 varchar(50),
    col8 varchar(50),
    CreateDate Datetime
)
go

declare @i int =0 
begin tran
    while @i<500000
    begin
        insert into TestDefaultOrder1 values (NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),GETDATE()-RAND()*500)
        set @i=@i+1
    end
commit

正如你所想像,对一个非常大的数据库来说,如果数据库所有的表在一个批处理中被进行一致性检查,用来存储所有事实的空间数量很快达到tempdb的可用存储。

并行执行多个串行计划

回想一下图2中显示的并行查询计划,然后假设SQLServer
分配了三个额外的线程在运行时去查询。概括的讲,重新生成并行计划来展示SQLServer
运行三个独立串行的计划流(这个表示是我自己起的不是很精确。)

澳门金沙vip 4

图4: 多串行计划

 

每个线程被分配三个branch 中的一个,最后汇聚到Gather Streams(流聚合)
操作符。注意这个图中只有流聚合操作符带有黄色并行箭头;所以这个操作符是这个计划中仅有的与多线程交互的操作符。这种通用策略有两个原因始适合SQLServer的。首先,所有必要地执行串行计划SQL代码已经存在并且已经被优化多年和在线发布。其次,方法的方位很合适:如果更多线程被调用,SQLServer
能轻易添加额外计划分之来分配更多线程。

额外的线程数量分配给每一个并行计划,这被称为并行度(缩写为DOP)。SQLServer
在查询开始之前就选择了DOP,然后不需要计划重新编译就能改变并行度。最大DOP对于每一个并行区域都是由SQLServer的逻辑处理单元的可利用数量决定的(物理核)

 

批处理中的表集当DBCC
CHECKED
(在Per-table逻辑检查开始时期)扫描关于表的元数据时被确定。批处理总是至少有一个表(再加上它的非聚集索引),每个批处理的大小被限定于下列规则之一:

并行扫描和并行页支持

   
图4中的问题是每个索引扫描操作符都会去数整个输入集的每一行。不及时纠正,计划就会产生错误的结果集并且和可能花费更多时间。手工并行的例子通过使用where子句来避免这个问题。

    SQLServer
没有用相同的方法,因为分配工作假定平均地使每个查询接收相等的可利用资源,并且每个数据行需要相同的处理。在一个简单例子中,例如统计一个表中的行数,这种假定可能会效果很好(同一个服务器没有其他活动的时候),并且三个查询可能返回的查询也是完全等时的。

   
与分配固定数量行数给每个线程不同,SQLServer使用存储引擎的功能叫做“Parallel
Page Supplier
”来按需分配行数给线程。在查询计划中是看不到“Parallel
Page Supplier

”的,因为它不是查询处理器的一部分,但是我们能拓展图4来形象的展示他的连接方式:

澳门金沙vip 5

图5:  Parallel Page Supplier

    这里的关键点就是demand-based
(基于需求)架构;通过响应现成的请求提供一个行数的批处理给需要更多工作的线程去做。对比数糖豆的案例,Parallel
Page Supplier
就像是专门用勺子从罐子里面拿出糖豆的过程。只有一个勺子防止两个人都去数相同的豆子。并且其他线程将会数更多豆子来补偿。

   注意Parallel Page Supplier
的使用并不阻止现有的优化像预读扫描(在硬盘上提前读取数据)。事实上,这种预读在这种情况下效率要比单线程还要好,这个单线程是底层的物理扫描而不是之前我们看到的三个独立的手动并行的例子。

    Parallel Page Supplier
也不会限制索引扫描;SQLServer利用它当多线程协同读取一个数据架构。数据架构可能是堆、聚集索引表、或者一个索引,并且操作可以是扫描或者查找。如果后者(查找)更高效,考虑索引查找操作就像一个部分扫描,例如它能查找到第一个符合条件的行然后扫面范围的结尾。

测试场景:

◆如果被定义了任何修复选项,生成批处理在它包含一个单表时停止。这会确保修复被正确地排序。

执行上下文

    与手动并行例子的机制相似,但是又与创建独立连接的串行查询,SQLServer
使用了一个轻量级的构造称之为“执行上下文”来实现并行。

   
一个执行上下文来自查询计划的一部分,该内容通过填写在计划重新编译和优化后的细节来产生。这些细节包括了直到运行才有的引用对象(如批处理中的临时表)和运行时的参数以及局部变量。这里就不展开讲了,微软的白皮书中由于详细的介绍。

    SQLServer
运行一个并行计划,通过为每一个查询计划的并行区域派生一个DOP执行上下文,利用独立的线程在上下文中运行串行计划包含的部分。为了帮助概念的理解,图6中展示了三个执行上下文,每个颜色区分执行上下文的范围。虽然并不是明显地展示出来,但是一个Parallel
Page Supplier 还是被用来协调索引扫描,避免重复读取。

澳门金沙vip 6

图6: 并行计划执行上下文

 

   
为了更具体的观察抽象概念,图7展示了并行行计数查询包含的信息,在SSMS的选项中,“Actual
Execution Plan”(实际执行计划),打开左侧扩展+。

澳门金沙vip 7

图7: 并行计划行计数

   
两个图片对比,行处理的数字一个是3一个是113443。信息来自于属性窗口,通过点击操作符(或者链接线)然后按下F4,或者右键属性。右键操作符或者线,并且选择弹出菜单的属性。

   
右边的插图中我们能看到每个线程读取的行数和总行数;注意两个线程处理了相似的行数(40000左右),但是第三个线程值处理了32000行。如上所述,基于需求的架构取决于每个线程时间因素和处理器负载等等,及时是轻负载的机器也会有不平衡的现象。

   
左侧的这个图展示了三个结果结被收集在一起的过程,汇总了每个进程的结果集。它的元素是并行执行线程的数量。

  这里先不考虑索引之类的性能问题,
  如图是一个测试结果的示例,可以看到,两个查询的条件是完全一样的,都没有显式指定排序列,默认结果的排序是完全不一样的

◆当一个表被加到一个批处理,批处理中的所有表的索引的总数达到512个,生成批处理终止。

Schedulers, Workers, 以及Tasks

这篇文章到目前为止‘thread’
和‘worker’理解上是一致的。现在我们需要定义更加精确,如下。

澳门金沙vip 8

◆当一个表被加到一个批处理,总的、最坏情况下,tempdb用于批处理中的所有表的所有事实所需的空间达到32MB,生成批处理终止。

Schedulers

一个scheduler 在SQLserver
中代表一个逻辑处理器,或者是一个物理CPU,或许是一个处理核心,或许是在一个核(超线程)上运行的多个硬件线程之一。调度器的主要目的就是允许SQLServer精确控制线程调度,而不是依赖Windows操作系统的泛型算法。每个调度器确保仅有一个协调执行线程在运行(就操作系统而言)在指定时间内。这样做的重要好处就是减少了上下文切换,并且减少了调用windows内核的次数。串行的三个部分覆盖了任务调度和执行的内部详细信息。

   
关于任务调度在可以在DMV(sys.dm_os_schedulers)中查看。

    

一旦批处理生成,事实生成(fact-Generation)和事实聚集(fact-aggregation)算法被在批处理的所有表上运行。当一个批处理完成时,各种deep-dive的算法可能被触发以找到不匹配的文本时间戳值,或不匹配的非聚集索引记录。此时,未经核查的程序集(assemblies)可能也会被清除。如果一个表依赖于一个(用于实现一个CLR用户定义数据类型(UDT)或计算列的)CLR程序集,随后,程序集被使用带有With
unchecked Data选项的alter
assembly改变,所有依赖于程序集的所有表被在系统目录中标记为有未经核查的程序集。清除这个设置的机制是为了运行DBCC一致性检查时尽可能不影响表。如果没有错误出现,未经检查的程序集设置被清除。

Workers 和Threads

   一个SQLServer
工作线程是一个抽象表示一个单一的操作系统线程或者一个光纤。很少系统运行光纤模式任务调度,因此大部分文档都是使用了工作线程来强调对于大多数实际目的而言,一个worker就是一个线程。一个工作线程绑定一个具体的调度。关于工作线程的信息可以通过DMVsys.dm_os_workers来查看。

  甚至可以用同样的条件做三次查询(可以更多次),结果依然都是完全不一致的

■读取页到进程

Tasks

可以这样定义Tasks:

一个任务表示一个被SQLServer
调度的线程的单位。一个批处理能映射一个或者多个任务。例如,一个并行查询将被多个任务执行。

    扩展这个简单的定义,一个任务就被SQLServer
工作线程运行的一件工作。一个批处理仅包含一个串行执行计划就是单任务,并且将被单一连接提供的线程执行(从开始到结束)。这种情况下,执行必须等待另一个事件(例如从硬盘读取)完成。单线程被分配一个任务,然后直到被完全完成否则不能运行其他任务单元。

  澳门金沙vip 9

 事实生成和事实聚集算法的性能部分取决于包含批处理的表和索引的页的事实是否被有效读取。正如之前所说,页并不需要被以任何指定的顺序读取,当所有相关页被读取时,事实被聚集,所有事实生成。

执行上下文

   
如果一个任务描述被完成的工作,一个执行上下文就是工作发生的地方。每个任务在一个执行上下文内运行,标识在DMVsys.dm_os_tasks中的exec_context_id列中(你也可以看到执行上下文使用ecid
列在sys.sysprocesses视图中)

 

从数据文件中读取页集最快的方式是以allocation
order(页在数据文件中的物理顺序)读取。这允许磁头在磁盘间作连续的移动而不是做随机的IOs和经受磁头的长时间过度寻道。

交换操作符

   
简要回顾,我们已经看到SQLServer通过并发执行一个串行计划的多个实例来执行一个并行计划。每个串行计划都是一个单独的任务,在各自的执行上下文内独立运行各自的线程。最终这些线程的结果成为交换操作符的组成部门,就是将并行计划的执行上下文连接在一起。一般来说,一个复杂的查询计划可以包含多个串行或者并行区域,这些区域由交换操作符来连接。

到目前为止,我们已经看到只有一种形式的连接操作符,叫做流聚合,但是它能以另外两种进化的形式继续出现如下:

澳门金沙vip 10

图8: 交换逻辑操作符

这些形式的交换操作符就是在一个或者多个线程内移动行,分配独立的行给多个线程。不同的逻辑形式的操作符要么是引入新的串行或者并行区域,要么是分配重定向行给在两个并行区域的接口。

不仅可以分割、合并、重定向行在多线程上,还可以做到如下事情:

  • 使用五中不同的策略来确定输出输入行的路线。
  • 如果需要,可以保留输入行的顺序。
  • Much of this flexibility stems from its internal design, so we will
    look at that first. 灵活源自其内部设计,因此我们要先观察

 

包含批处理的表和索引的页和分区(Extent)被(用于各种表管索引的分配单元的)IAM链跟踪。一旦批处理被生成,所有这些IAM链被合并为一个大的位图(bitmap),它被一个DBCC
CHECKED内部的一个扫描对象管理。该位图以已排序的物理顺序设置(包含批处理中的所有平和索引的)所有的页和分区(extent)。

交换操作符内部

交换操作符有两个完全不同的子组件:

  • 生产者, 连接输入端的线程
  • 消费者, 连接输出端的线程

图9 展示了一个流聚合操作符的放大视图(图6)

澳门金沙vip 11

图9: 流聚合内部构造

    每个生产者
收集它的输入行并且将输入包装成一个或者多个内存中的缓存。一旦缓存满了,生产者将会将其推入到消费者端。每个生产者和消费者都运行在相同的线程作为其连接执行上下文(如同连接的颜色暗示)。消费者端的交换操作符当它被上级操作符要求就从缓存中读取一行数据(如同本例中的红色的阴影数据流聚合)。

   
主要好处之一就是复杂度通常与分享数据的多个执行的线程有关,而这些线程由SQLServer一个内部操作符处理。另外,在计划中的非交换操作符是完全串行执行的,并且不需要去关心这些问题。

   
交换操作符使用缓存来减少开销,并且为了实现控制基本种类的流(例如为了阻止快速生产者比慢速消费者快太多)。精确分配缓冲区,随着交换的不同缓存区也变化,不论是否需要保留顺序,并且决定如何匹配生产者和消费者的数据行,