尤其是多核处理器的提升,两个完全一样的查询

作者: www.9159.com  发布:2019-11-29

  

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

 

6.1 为什么查询速度会慢

本文出处: 

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

 

 

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

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

查询的生命周期大致可按照顺序来看:从客户端,到服务器,然后在服务器上进行解析,生成执行计划,执行,并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段。这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理,包括排序、分组等。

 

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

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

 

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

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

什么是并行?

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

在完成这些任务时,查询需要在不同的地方花费时间,包括网络、CPU计算、生成统计信息和执行计划、锁等待等操作,尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作、CPU操作和内存不足时导致的IO操作上消耗时间。根据存储引擎不同,可能还会产生大量的上下文切换以及系统调用。

 

■批处理

数糖豆

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

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

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

 

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

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

使用SQLServer 完成“数糖豆”

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

图片 1

图1  串行执行计划:

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

图片 2

图2 并行计数计划

 

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

6.2 慢查询基础:优化数据访问

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

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

并行如何工作?

 

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

图片 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 运行三个独立串行的计划流(这个表示是我自己起的不是很精确。)

图片 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来形象的展示他的连接方式:

图片 5

图5:  Parallel Page Supplier

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

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

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

 

测试场景:

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

执行上下文

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

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

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

图片 6

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

 

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

图片 7

图7: 并行计划行计数

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

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

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

对于低效查询的分析步骤:

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

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

Schedulers, Workers, 以及Tasks

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

 

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

  • 确认MySQL服务器层是否在分析大量超过需要的数据行。

  图片 9

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

执行上下文

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

 

 

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

交换操作符

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

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

图片 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)

图片 11

图9: 流聚合内部构造

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

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

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

 

原因分析:

使用该位图,所有必需的页可以被接近连续地读取。扫描对象在页上执行预读(read-ahead),以确信CPU(s)从不等待下个要处理的页被读进缓冲池。预读机制,与存储引擎的其余部分非常类似,只不过,它自逻辑数据文件被创建起被在物理值之间轮询调度。这样做是尽量扩展I/O工作负荷,DBCC CHECKED会处理更多的I/O。

路由行

    如上所述,一个交换操作符能决定一个生产者应该匹配哪一个特定的行数据。这个决定依赖于被交换操作符指定的分块类型。并且有五个可选类型,

 

类型 描述
Hash

最常见,通过计算当前行的一个或者多个列上的哈希函数来选择消费者。

轮循

每个新的行按照固定的序列被发送给下一个消费者
广播 每一行被发送给所有消费者。
请求 每一行被发送给第一个请求的消费者。这是仅有的通过消费者内部的交换符拉出行的分割类型。
范围 每一个消费者被分配一个不重叠的范围值。特定的输入列分成范围决定消费者获得的行。

 

请求和范围分割类型是比前面三种更少见的,并且一般只在操作分区表的查询计划中能看到。请求类型是用来收集分区的连接来分配分区ID给下一个工作线程。例如,当创建分区索引的时候使用范围分割类型,那么如果要想查到属于哪种类型需要在查询计划中查找:

图片 12

图10: 交换操作分割类型

 

有些查询会请求超过实际需要的数据,然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担,并增加网络开销,另外也会消耗应用服务器的CPU和内存资源。

  为什么一样的查询,每次查询结果的排序都不一样,正如上面所说,这种情况下是并行查询导致的。
  查询引擎采用什么样的执行计划是基于代价考虑的,如果一旦发现一个查询的执行代价超过一定的阈值,就有可能采用并行的方式来处理,
  如果采用了并行查询的方式,就会采用多个线程来分解整个查询任务,而每一个线程分配的任务量是无法固定的,同时,合并每个线程的结果顺序也是不固定的
  这就导致了最终的查询结果的顺序是不固定的。
  截图即为并行查询的每个线程分配的任务量示例。

无论何时,下个页需要被处理时,一个调用被转化为扫描对象,返回一个页给调用者。页返回的类型,对象或索引是完全相关的,因为事实生成和聚集算法的天性(nature)。

保留输入顺序

一个交换操作符可以选择配置来保留排序顺序。在计划中输入的行已经排序的时候对后面的操作符是很有用的(沿用开始的排序,或者作为一个从索引中读取的已经排序的序列)。如果交换操作符没有保留上顺序,在交换器需要重新建立排序后优化器将必须引入额外的排序操作符。普通的请求排序输入的操作符包括流聚合、分段和合并连接。图11展示一个需要重新分配流的排序操作:

图片 13

图11: 保留顺序的重新分配流

 

 

注意合并交换自身不会排序,它要求输入行必须进行排序吗。合并交换是效率更低比非保留顺序的,并且是有一定的性能问题的。

 

  如图,当前这个查询,第一个线程返回的行数是2,但是无法保证第二次查询的第一个线程返回的行数也是2,
  即便是第二次返回的行数是2,也无法保证返回的2行与第一次返回的两行数据一样的
  同时,在合并各个线程的结果集的时候,依据线程返回的时间来的,理论上讲也是不确定的,多个不确定因素在一起,就造成了最终的结果集排序(可以认为)是随机的。

应该注意到,有时随机I/Os是必需的,因为被读取的页上的某些行可能(因为行溢出内容)有部分(存储在不同页的)行,DBCC CHECKED在内存中实现一个完全的行(除了行外LOB列),它可能调用一个随机的I/O读取行的行溢出部分。  

最大并行度

微软给出的官方指导:

图片 14

请遵循以下准则:

1. 服务器的有8个或更少的处理器,使用下列配置其中N等于处理器数:MAXDOP=0到N。

  1. 对于具有NUMA配置的服务器,MAXDOP不应超过分配给每个NUMA节点的cpu数。

3. 超线程已启用的服务器的MAXDOP值不应超过物理处理器的数量。默认为0表示数据库引擎自行分配。

图片 15

 

查询不需要的行

  图片 16

■并行机制

总结

    通过一个简单的查询引入并行,并且对照了一个真实的数糖豆的案例,为了研究SQLServer中并行的使用的优点,暂时没有考虑与多线程设计相关的复杂情况。我们发现了并行查询计划可以包含多个并行和串行区域,通过交换操作符绑定在一起。并行区域扩展出多个串行查询,每个串行都使用了独立线程来处理执行上下文的任务。交换操作符被用来匹配线程之间的行并且在并行计划中实现与不止一个线程交互。最后,我们看到了SQLServer 提供了一个Parallel Page Supplier,当保证是正确的结果集时,允许多个线程可以协同扫描表和索引。

    除此之外还介绍了交换操作符以及操作符内部详细构造以及最佳实践中的并行度配置。这里都这是从概念上做了介绍,如果线下有问题可以一起研究选择出最好的实现方式。

 

  

DBCC CHECKED有能力使用多处理器内核并行运行,以更加有效使用系统资源,更快地处理数据库。如果下列条件为真,它可以并行运行当前的批处理:

  • 解决方式是在这样的查询后面加上LIMIT

  有人说,只要执行计划一样,查询默认排序就一样,其实也是不对的,因为即便是执行计划一样,只要SQLServer开启了并行查询,默认排序都是无法保证一直的   

◆SQL Server实例是企业版、企业评估版、开发版。

 

 

◆当前批处理的所有表和索引包含的页大于64个。

多表关联时返回全部列

 

◆在批处理中的表中没有基于T-SQL或基于CLR的计算列。

 

场景2:物理存储导致默认结果的随机性

◆并行机制没有(用跟踪标志2528)显式禁用。

  • 解决方式是在列前加上表名

  同样,先造一个测试数据的case,如下,创建一个堆表,

如果所有这些条件都为真,DBCC CHECKED发信号给查询处理器:当它遇到内部查询冲突时,可以被并行化。查询处理器最后决定是否使用并行线程。查询处理器基于以下因素决定是否并行化:

 

create table TestDefaultOrder2
(
    id int identity(1,1),
    col2 char(5000)
)
go

declare @i int =0 
begin tran
    while @i<50
    begin
        insert into TestDefaultOrder2 values (NEWID())
        set @i=@i+1
    end
commit

◆服务器的MAXDOP设置

总是取出全部列

 

◆针对并行的预期的查询成本

 

测试场景:

◆在批处理的DBCC CHECKED查询被编译用于执行时服务器的可用资源

  • 通常情况下禁用SELECT *

  这个场景排除了上述并行查询的影响,因为只有50条数据,根本不会启用并行查询
  如截图,两次查询之间执行了一次表的重建动作,同样是数据本身没有发生任何变化,两次查询的默认顺序完全不一样

决定是否并行化内部查询在每次查询冲突时被执行,这意味着DBCC CHECKED一个单个的执行中的不同的批处理可能运行不同的degree of paralleism。

 

图片 17

当内部查询运行在并行模式时,数据的概念流程如下:  

重复查询相同的数据

 

 图片 18

 

甚至在重建一次,查询结果仍然与上面两次还是都不一样的。

当内部查询并行运行时,每个并行度(degree of parallelism)的一个线程被创建。在算法的事实生成分配(portion)时期,每个线程负责从扫描对象请求到进程,在每一页处理完进程。一个页仅仅被单个线程处理。同时每个线程负责聚集一个隔离的,自包含的事实流(意味着一个对象结构的特殊部分的所有的恰当的事实,必须被设置到一个线程--没有跨线程的事实聚集)。

  • 当初次查询的时候将这个数据缓存起来,需要的时候从缓存中取出,这样性能更好

图片 19

没有控制哪个线程处理哪个页,因此,组成单个对象的页可能被多个线程处理。这在事实生成期间可能导致问题,如果真的应该被聚集在一起的事实被包含在不同线程的已排序的和已聚集的事实流。因为这个原因,当内部查询并行时,所有的事实(fact)被它们的ROWSET_COLUMN_FACT_KEY元素哈希,通过互换在被排序前传递到per-thread哈希桶。这确保一个对象结构的特殊部分的所有事实被设置到仅仅一个线程。

 

 

注意:DBCC CHECKTABLE和DBCC CHECKFILEGROUP命令也能经这种方式使用并行机制。但DBCC CHECKALLOC不可以。

MySQL是否在扫描额外的记录

 原因分析:

如果确定并行命令设置太大的工作负荷在服务器上,可以在DBCC命令中使用跟踪标志2528以禁用并行。注意这个禁用会花费较长的时间

 

  堆表的特点决定了堆内的数据行和数据页没有任何固定的顺序,整个堆内的数据在物理存储发生了变化之后,
  在对查询(对堆表扫描)的过程中得不到一个与物理存储变化之前完全一样的顺序。
  除了上述的重建表会导致查询的默认顺序不一致,其他影响物理空间的操作,都会影响堆表数据页面的物理存储位置,

为了允许高效的并行机制和可伸缩性而不导致人为瓶颈,DBCC CHECKED内部的所有部分被设计为多线程访问而不引起伸缩性冲突,至少可以并行32个处理器内核。

在确定查询只返回需要的数据后,接下来应检查查询为了返回结构是否扫描了过多的数据。

  比如这里再执行一次数据库的收缩,收缩之后的查询与收缩之前的查询顺序依旧是不一样的,我可没有动你表和你表中的任何一条数据,但你不能阻止我正常的数据库维护操作。
  总之,一旦影响到物理存储位置,堆表的默认扫描结果顺序都有可能不一样。

 

  图片 20

对于MySQL,最简单的衡量查询开销的三个指标:

 

 

  以上仅仅通过单表查询来说明,如果未显式指定排序方式,即便是同样的查询条件,查询结果的顺序是无法保证每次都一致的,
  如果是多表关联,或者是考虑到索引,数据库维护等操作,情况将变得更加复杂,比如这个也比较有意思:
  比较特殊的是:没有显式指定排序方式,
    1,某段一个时间段内,查询结果可能是按照预期结果排序的,某个时间段内就不是了(物理存储改变的影响);
    2,某些查询条件下是按照预期结果排序的,改变一下查询条件,排序结果就变得面目全非了(执行计划改变的影响)。
  总之一句话:没有显式执行排序方式,不要期待查询结果每次都是预期的排序方式,甚至每次都不一样。

  • 响应时间;

 

 

 总结:

  • 扫描的行数;

   本文通过两个简单的示例,
  从执行计划和物理存储两个方面,说明了“如果查询SQL没有显式指定排序方式,查询结果的顺序是无法保证总是按照你的预期来的”。
  当然也不能局限于这两种情况,极有可能还有很多原因是我没有想到的。
  然而话不能说死,某些条件下没有显式指定排序方式,一定条件下(多次查询)可能会得到预期的排序结果,但是这种期待往往是不可靠的。   

 

  “昨天系统查询结果的排序还是好好的,今天怎么变了?”
  “为啥我用A条件查询是按照时间排序的,按照B条件查询就不是了?”
  如果没有显式指定排序方式,不要问我数据库是不是有问题(或者说SQL Server这个数据库“不行”,或者说DBA说是内部原因是忽悠人的)。

  • 返回的行数;

  所以同学,如果期望查询结果排序,不管默认是不是你预期的排序方式,都请显式指定排序方式。

 

 

响应时间

 

 

 

响应时间是两个部分之和:服务时间和排队时间。服务时间是指数据库处理这个查询真正花费的时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等IO操作完成,也可能是等待行锁。但实际上,这两者无法细分。

 

 

在不同类型的引用压力下,响应时间并没有什么一致的规律或公式。诸如存储引擎的锁(表锁、行锁)、高并发资源竞争、硬件响应等诸多因素都会影响响应时间。

 

扫描的行数和返回的行数

 

分析查询时,查看该查询扫描的行数 在一定程度上能够说明该查询找到需要数据的效率。扫描的行数和返回的行数之间的比率一般在1:1到10:1之间较为合理。

 

扫描的行数和访问类型

 

在explain语句中的type列反应了访问类型(即MySQL决定如何查找表中的行)。访问类型有很多种,从全表扫描到索引扫描,范围扫描、唯一索引查询、常数引用等。

 

如果查询没有办法找到合适的访问类型,那么通常就需要增加一个合适的索引。索引能让MySQL以最高效、扫描行数最少的方式找到需要的记录。

 

ALL 全表扫描,通常意味着MySQL必须扫描整张表。(例外,如果查询中使用了LIMIT,Extra列种会显示"Using distinct/not exists")
index 与全表扫描一样,只是MySQL扫描表时按索引次序进行而不是行。主要优点是避免了排序,最大的缺点是要承担按索引读取整个表的开销。这通常意味着若是按随即次序访问行,开销将会非常大。
range 范围扫描就是一个有限制的索引扫描,它开始于索引里的某一点,返回匹配这个值域的行。这比全索引扫描好一些,因为他用不着遍历全部索引,显而易见的范围扫描时带有between或在where子句里带有>的查询
ref 这是一种索引访问(有时也叫做索引查找),他返回所有匹配某个单个值得行。然而,他可能会找到多个符合条件的行,因此,它是查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做ref是因为索引要跟某个参考值相比较。这个参考值或者是一个常数,或者是来自多表查询钱一个表的结果值。
eq_ref 使用这种索引查找,MySQL知道最多只返回一条符合条件的记录。这个访问方法可以在MySQL使用主键或者唯一性索引查找时看到,它会将她们与某个参考值做比较。MySQL对于这类访问类型的优化做得非常好,因为他知道无须估计批评行的范围活在找到匹配行后再继续查找。
const/system 当MySQL能对查询的某部分进行优化并将其转换成一个常量时,它就会使用这个访问类型。
NULL 这种访问方式意味着MySQL能在优化阶段分解查询语句,在执行阶段甚至用不着再访问表或者索引。

 

一般MySQL能够使用如下三种方式应用where条件,从好到坏依次为:

 

  • 在索引中使用where条件来过滤不匹配的记录。这是在存储引擎层完成的。

 

  • 使用索引覆盖扫描(在Extra列中出现Using index)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的,但无须在回表查询记录。

 

  • 从数据表中返回数据,然后过滤不满足条件的记录(在Extra列中出现Using where)。这在MySQL服务器层完成,MySQL需要先从数据表读出记录然后过滤。

 

如果发现查询需要扫描大量的数据但只返回少数的行,那么通常可以尝试下面的技巧去优化它:

 

  • 使用索引覆盖扫描,把所有需要用的列都放在索引中,这样存储引擎无须回表获取对应行就可以返回结果。

 

  • 改变库表结构。

 

  • 重写这个复杂的查询,让MySQL优化器能够以更优化的方式执行这个查询。

 

6.3 重构查询的方式

 

在优化有问题的查询时,目标应该是在找到一个更优的方法获得实际需要的结果,而不是一定总是需要从MySQL获取一模一样的结果集。

 

一个复杂查询还是多个简单查询

 

在传统实现中,总是强调需要数据库层完成尽可能多的工作,这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。

 

但是这样的想法对于MySQL并不适用,MySQL从设计上让连接和断开连接都很轻量级,在返回一个小的查询结果方面很搞笑。现代网络速度比以前要快很多,无论是带宽还是延迟。所有运行多个小查询现在已经不是大问题。

 

MySQL内部每秒能够扫描内存中上百万行数据,相比之下,MySQL响应数据给客户端就慢得多。在其他条件都相同的时候,使用尽可能少的查询当然是更好的。但是有时候,将一个大查询分解为多个小查询是很有必要的。不过,在应用设计的时候,如果一个查询能够胜任时还写成多个独立查询是不明智的。

 

切分查询

 

有时候对于一个大查询我们需要“分而治之”,将大查询切分成小查询。每个查询功能完全一样,只完成一小部分,每次只完成一小部分,每次只返回一小部分查询结果。如删除旧数据的操作。

 

分解关联查询

 

很多高性能的应用都会对关联查询进行分解。简单地,可以对于每一个表进行一次单表查询,然后将结果在应用程序中进行关联。

 

用分解关联查询的方式重构查询有如下优势:

 

  • 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。另外,对MySQL的查询缓存来说,如果关联中的某个表发生了变化,那么久无法使用查询缓存了。而拆分后,如果某个表很少改变,那么基于改表的查询就可以重复利用查询缓存结果了。

 

  • 将查询分解后,执行单个查询可以减少锁的竞争。

 

  • 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。

 

  • 查询本身效率也可能会有所提升。

 

  • 可以减少冗余记录的查询。在应用层做关联查询,意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。这样的重构还可能会减少网络和内存的消耗。

 

  • 这样做相当于在应用中实现了哈希关联,而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

 

6.4 查询执行的基础

 

查询执行路径

 

  • 客户端发送一条查询给服务器;

 

  • 服务器先检查查询缓存,如果命中缓存,则立即返回存储在缓存中的结果。否则进入下一阶段;

 

  • 服务器端进行SQL解析、预处理、再由优化器生成对应的执行计划;

 

  • MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询; 

 

  • 将结果返回给客户端;

 图片 21

 

MySQL客户端/服务器通信协议

 

MySQL客户端和服务器之间的通信协议是“半双工”的(即 在任何时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器发送数据,这两个动作不能同时发生)。优点:简单快速;缺点:无法进行流量控制。

 

查询状态:对于一个MySQL连接,或者说一个线程,任何时刻都有一个状态,该状态标识MySQL当前正在做什么。命令:show full processlist。

 

  • Sleep:线程正在等待客户端发送新的请求。

 

  • Query线程正在执行查询或者正在将结果发送给客户端。

 

  • Locked在MySQL服务器曾,该线程正在等待表锁。在存储引擎级别实现的锁,例如InnoDB的行锁,并不会提现在线程状态中。对于M有ISAM来说这是一个比较典型的状态。

 

  • Analyzing and statistics: 线程正在收集存储引擎的统计信息,并生成查询执行计划。

 

  • Copying to tmp table [on disk]:线程正在执行查询,并且将其结果集都复制到一个临时表中,这种状态一般要么是在做group by操作,是文件排序操作,或者是union操作。如果这个状态后面还有on disk标记,则表示MySQL正在将一个内存临时表放在磁盘上。

 

  • Sorting result:线程正在对结果集进行排序。

 

  • Sending data:线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据。

 

查询缓存

 

缓存是通过一个对大小写敏感的哈希查找实现的。如果当前的查询恰好命中缓存,那么在返回查询结果之前MySQL还会检查一次用户权限。

 

查询优化处理

 

语法解析器和预处理

 

MySQL通过关键字将SQL语句进行解析,并生成一棵对应的"解析树",MySQL解析器将使用MySQL语法规则验证和解析查询。例如,它将验证是否使用错误的关键字,或者使用关键字的顺序是否正确,再或者它还会验证引号是否能前后正确匹配。

 

预处理器则根据一些MySQL规则进一步检查解析树是否合法,例如,这里将检查数据表和数据列是否存在,还会解析名字和别名,看看它们是否有歧义。下一步预处理器会验证权限。

 

查询优化器

 

在语法树合法的基础上,优优化器将其转化成执行计划。一次查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

 

MySQL使用基于成本的优化器,它将尝试预测一个查询是哟弄个某个执行计划时的成本,并选择其中成本最小的一个。可通过查询当前会话的last_query_cost的值来得知MySQL计算的当前查询的成本。SHOW STATUS LIKE 'last_query_cost'。返回值表示MySQL的优化器认为大概需要 做N个数据页的随机查找才能完成查询。

 

这是根据一系列的统计信息计算得出的:每个表或者索引的页面个数、索引的基数(索引中不同值得数量)、索引和数据行的长度、索引分布情况。优化器在评估成本时并不考虑任何层面的缓存,它假设读取任何数据都需要一次磁盘IO。

 

有如下多种原因会导致MySQL优化器选择错误的执行计划:

 

  • 统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本,但是有的存储引擎提供的信息是准确的,有的变差可能非常大。

 

  • 执行计划中的成本估算不等同实际执行的成本。所以即使统计信息精确,优化器给出的执行计划也可能不是最优的。

 

  • MySQL只是基于其成本模型下载最优的执行计划,而有些时候这并不是最快的执行方式,

 

  • MySQL从不考虑其他并发执行的查询,这可能会影响到当前查询的速度。

 

  • MySQL也并不是任何时候都是基于成本的优化。有时也会基于一些固定的规则。

 

  • MySQL不会考虑不受其控制的操作的成本,例如执行存储过程或者用户自定义函数的成本。

 

  • 优化器有时无法估算所有可能的执行计划,所以它可能错过实际上最优的执行计划。

 

MySQL查询优化器是一个非常复杂的不见,它使用了很多优化策略来生成一个最优的执行计划。优化策略可简单分为两种,一种是静态优化,一种是动态优化。

 

静态优化可以直接对解析树进行分析,并完成优化。例如,优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式。静态优化不依赖于特别的数值,如where条件中带入的一些常数等。静态优化在第一次完成后就一直有效,即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种"编译时优化"。

 

动态优化则和查询的上下文有关,也可能和很多其他因素有关,例如where条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估,可以认为是"运行时优化"。

 

在执行语句和存储过程的时候,动态优化和静态优化的区别非常重要。MySQL对查询的静态优化只需要做一次,但对查询的动态优化则在每次执行时都需要重新评估。有时候甚至在查询的执行过程中也会重新优化。

 

下面是一些MySQL能够处理的优化类型:

 

  • 重新定义关联表的顺序。数据表的关联并不总是按照在查询中指定的顺序进行。

 

  • 将外连接转化成内连接。并不是所有的outer join语句都必须以外连接的方式执行。诸多因素,例如where条件、库表结果都可能会让外连接等价于一个内连接。MySQL能够识别这点并重写查询,让其可以调整关联顺序。

 

  • 使用等价变换规则。MySQL可以使用一些等价变化来简化并规范表达式。它可以合并和减少一些比较,还可以移除一些恒成立和一些恒不成立的判断。

 

  • 优化COUNT()、MIN()、MAX()。索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如,要找到某一列的最小值,只需要查询对应B-Tree索引最左端的记录,MySQL剋直接获取所有的第一行记录。在优化器生成执行计划的时候就可以利用这一点,在B-Tree索引中,优化器会将这个表达式作为一个常数对待。如果MySQL使用了这种类型的优化,那么在EXPLAIN中就可以看到"Select tables optimized away",它表示优化器已经从执行计划中移除了该表,并以一个常数取而代之。

 

  • 预估并转化为常量表达式。当MySQL检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行优化处理。

 

  • 覆盖索引扫描。当索引中的列包含所有查询中需要使用的列的时候,MySQL就可以使用索引返回需要的数据,而无须查询对应的数据行。

 

  • 子查询优化。MySQL在某些情况下可以将子查询转换成一种效率更高的形式,从而减少多个查询多次对数据进行访问。

 

  • 提前终止查询。在发现已经满足查询需求的时候,MySQL总是能够立即终止查询。例如查询中包含LIMIT子句,或者where条件不成立,这时MySQL可以立即返回一个空结果。

 

  • 等值传播。如果两个列的值通过等式关联,那么MySQL能够把其中一个列的where条件传递到另一列上。

 

  • 列表IN()的比较。在很多数据库系统中,IN()完全等同于多个OR条件的子句,因为这两个是完全等价的。在MySQL中这点事不成立的,MySQL将IN()列表中的数据线进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件,这是一个o(log n)复杂度的操作,等家底转化成OR查询的复杂度为O(n),对于IN()列表中有大量取值的时候,MySQL的处理速度将会更快。

 

数据和索引的统计信息

 

在服务器层有查询优化器,却没有保存数据和索引的统计信息。统计信息由存储引擎层实现。MySQL查询优化器在生成查询的执行计划时,需要想存储引擎获取相应的统计信息。包括:每个表或者索引有多少个页面、每个表的每个索引的技术是多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。

 

MySQL如何执行关联查询

 

MySQL中的"关联JOIN"一词所包含的意义比一般意义上理解的要更广泛。MySQL认为任何一次查询都是一次"关联"——并不仅仅是一个查询需要到两个表匹配才叫关联,所以在MySQL中,每一个查询,每一个片段(包括子查询,甚至基于单表的select)都可能是关联。

 

对于UNION查询,MySQL先将一系列的单个查询结果放到一个临时表中,然后再重新读出临时表数据来完成UNION查询。在MySQL的概念中,每个查询都是一次关联,所以读取结果临时表也是一次关联。

 

MySQL关联执行的策略:MySQL对任何关联都执行嵌套循环操作,即MySQL先在一个表中循环去除单条数据,然后再嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为止(类似foreach嵌套foreach)。然后根据各个表匹配的行,返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行,如果最后一个关联表无法找到更多的行以后,MySQL返回到上一层次关联表,看是否能够找到更多的匹配记录,以此类推迭代执行。

 

MySQL对所有的类型的查询都是同样的方式运行。例如,MySQL在FROM子句中遇到子查询时,先执行子查询并将其结果放到一个临时表(缺点:临时表没有索引)中,然后将这个临时表当作一个普通表(派生表)对待。MySQL在执行UNIION查询时也使用类似的临时表,在遇到右外连接时,MySQL会将其改写成等价的左外连接。

 

执行计划

 

MySQL生成查询的一棵指令树,然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。对某个查询执行EXPLAIN EXENDED后,再执行SHOW WARNINGS,就可以看到重构出的查询。

 

MySQL如何实现多表关联:

 

图片 22

 

关联查询优化器

 

MySQL优化器最重要的一部分就是关联查询优化,它决定了多个表关联时的顺序。通常多表关联的时候,可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序(更少的嵌套循环和回溯操作)。顺序第一的表可被称为驱动表。可使用STRAIGHT_JOIN关键字让MySQL按照SQL语句书写的顺序执行。

 

排序优化

 

当不能使用索引生成排序结果的时候,MySQL需要自己进行排序,如果数据量小则在内存中进行,如果数据量大则需要使用磁盘,不过MySQL将这个过程统一称为文件排序(filesort)。

 

如果需要排序的数据量小于"排序缓冲区",MySQL使用内存进行"快速排序"操作。如果内存不够排序,那么MySQL会先将数据分块,对每个独立的块使用"快速排序"进行排序,并将各个块的排序结果存放在磁盘上,然后将各个排好序的块进行合并(merge),最后返回排序结果。

 

MySQL在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多(即 排序消耗的临时空间比磁盘上的原表要大很多倍)。原因在于MySQL在排序时,对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够长以容纳其中最长的字符串。

 

在关联查询的时候如果需要排序,MySQL会分两种情况来处理这样的文件排序。如果order by子句中的所有列都来自关联的第一个表(驱动表),那么MySQL在关联处理第一个表的时候就进行文件排序,EXPLAIN的Extra字段会有"Using filesort"信息。除此之外的所有情况,MySQL都会先将关联的结果存放到一个临时表中,然后在所有的关联都结束后,再进行文件排序。这种情况下,在MySQL的EXPLAIN结果的Extra字段可以看到"Using temporaya, Using filesort"信息。如果查询中有LIMIT的话,LIMIT也会在排序之后应用,所以即使需要返回较少的数据,临时表和需要排序的数据量仍然会非常大。

 

新版本的MySQL对此做了很多改进。当只需要返回部分排序结果的时候,MySQL不再对所有的结果进行排序,而是根据实际情况,选择抛弃不满足条件的结果,然后再进行排序。

 

查询执行引擎

 

在解析和优化阶段,MySQL将生成查询对应的执行计划(MySQL的执行计划是一个数据结构),MySQL的查询执行引擎则根据这个执行计划来完成整个查询。

 

在根据执行计划逐步执行的过程中,有大量的操作需要通过调用存储引擎实现的接口来完成。

 

返回结果给客户端

 

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端,MySQL仍然会返回这个查询的一些信息,如该查询影响到的行数。

 

如果查询可以被缓存,那么MySQL在这个阶段也会将结果存放在查询缓存中。

 

MySQL将结果集返回客户端是一个增量、逐步返回的过程。例如,在关联查询操作中,一旦服务器处理完最后一个关联表,开始生成第一条结果时,MySQL就可以开始向客户端逐步返回结果集了。

 

6.5 MySQL查询优化器的局限性

 

UNION的限制

 

有时,MySQL无法将限制条件从外层"下推"到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。如果希望union的各个子句能够根据limit只取部分结果集,或者希望先排好序再合并结果集的话,就需要在union的各个子句中分别使用这些子句。

 

(

    SELECT

        order_id,

        create_time

    FROM

        order_a

    WHERE

        phone = 12345

    ORDER BY

        create_time DESC

    LIMIT 10

)

UNION ALL

    (

        SELECT

            order_id,

            create_time

        FROM

            order_b

        WHERE

            phone = 12345

        ORDER BY

            create_time DESC

        LIMIT 10

    )

ORDER BY

    create_time DESC

LIMIT 10

 

 

索引合并优化

 

当where子句中包含多个负责条件的时候,MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

 

 

6.6 查询优化器的提示(hint)

 

如果对优化器选择的执行计划不满意,可以使用优化器提供的几个提示(hint)来控制最终的执行计划。

 

  • HIGH_PRIORITY 和 LOW_PRIORITY:控制MySQL访问某个数据表的队列顺序,仅对使用表锁的存储引擎有效。不支持InnoDB。

 

  • DELAYED:这个提示对insert和replace有效。MySQL会将使用该提示的语句立即返回给客户端,比ing将插入的行数据放入到缓存区,然后再表空闲时批量将数据写入。日志系统使用这样的提示非常有效,或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成IO的应用。不支持InnoDB。

 

  • STRAIGHT_JOIN:这个提示可以放置在select语句的select关键字之后,也可以放置在任何两个关联表的名字之间。第一个用法是让查询中的所有的表按照在语句中出现的顺序进行关联。第二个用法则固定其前后两个表的关联顺序。

 

  • SQL_SMALL_RESULT 和 SQL_BIG_RESULT:这两个提示只对select语句有效。他们告诉优化器对group by或者distinct查询如何使用临时表及排序。SQL_SMALL_RESULT告诉优化器结果集会很小,可以将结果放在内存中的索引临时表,以避免排序操作。SQL_BIG_RESULT则告诉优化器结果集可能会非常大,建议使用磁盘临时表做排序操作。

 

  • SQL_BUFFER_RESULT:这个提示告诉优化器将查询结果放入到一个临时表,然后尽可能快地释放表锁。代价是需要更多的内存。

 

  • SQL_CACHE 和 SQL_NO_CACHE:这个提示告诉MySQL这个结果集是否应该缓存在查询缓存中。

 

  • SQL_CALC_FOUND_ROWS:它会让MySQL返回的结果集包含更多的信息。查询中加上该提示MySQL会计算出去LIMIT子句后这个查询要返回的结果集的总数,而实际上只返回limit要求的结果集。

 

  • FOR UPDATE 和 LOCK IN SHARE MODE:控制select语句的锁机制。

 

  • USE INDEX、IGNORE INDEX 和 FORCE INDEX:告诉优化器使用或者不使用哪些索引来查询记录。

 

 

6.7 优化特定类型的查询

 

优化COUNT()查询

 

COUNT()是一个特殊的函数,有两个非常不同的作用:他可以统计某个列值的非空(值不为NULL)数量,也可以统计行数。

 

最简单的就是当我们使用COUNT(*)的时候,这种情况下通配符*并不是统计所有的列,实际上,它会忽略所有的列而直接统计所有的行数。

 

由于COUNT()需要扫描大量的行,可以考虑添加汇总表(计数器)。

 

优化关联查询

 

  • 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。

 

  • 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列,这样MySQL才有可能使用索引来优化这个过程。

 

优化子查询

 

尽可能使用关联查询来代替子查询。注意,MySQL5.6会将子查询直接重写为关联查询。

 

 

优化 GROUP BY 和 DISTINCT

 

在很多场景下,MySQL都是用同样的办法优化这两种查询。MySQL优化器会在内部处理的时候相互转化这两类查询。他们都可以使用索引来优化,这也是最有效的优化办法。

 

当无法使用索引时,GROUP BY使用两种策略来完成:使用临时表或者文件排序来做分组,对于任何查询语句,这两种策略的性能都有可以提升的地方。可以通过使用提示SQL_BIG_RESULT或SQL_SMALL_RESULT来让优化器按照你希望的方式运行。

 

如果需要对关联查询做分组,并且是按照查找表中的某个列进行分组,那么通常采用查找表的标识列分组的效率会比其他列更高。

 

如果没有通过order by子句显式地指定拍序列,当查询使用group  by子句的时候,结果集会自动按照分组的字段进行排序,如果不关心结果集的顺序,而这种默认排序又导致了需要文件排序,则可以使用order by null让MySQL不在进行文件排序。也可以在group by子句中直接使用desc或者asc关键字,使分组的结果集按需要的方向排序。

 

优化 GROUP BY WITH ROLLUP

 

分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合。可以使用with rollup子句来实现这种逻辑。

 

最好的优化方法是尽可能将with rollup功能转移到应用程序中处理。

 

优化 LIMIT 分页

 

分页操作通常会使用limit加上偏移量的办法实现,同时再加上合适的order by子句。但这会出现一个常见问题:当偏移量非常大的时候,它会导致MySQL扫描大量不需要的行然后再抛弃掉。

 

一个最简单的方法是尽可能地使用索引覆盖扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。

 

有时候也可以将limit查询转换为已知位置的查询,让MySQL通过范围扫描获得对应的结果。

 

优化 UNION 查询

 

MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没有很好使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句"下推"到UNION的各个子查询中,以便优化器可以充分利用这些条件进行优化。

 

除非确实需要服务器消除重复的行,否则就一定要使用UNION ALL。如果没有ALL关键字,MySQL会给临时表加上DISTINCT选项,这会导致地整个临时表的数据做唯一性检查。这样做的代价非常高。

 

使用用户自定义变量

 

SET @val := 1;

SET @maxId := (SELECT MAX(id) FROM table_name );

 

使用自定义变量的场景限制

 

  • 使用自定义变量的查询,无法使用查询缓存。
  • 不能再使用常量或者标识符的地方使用自定义变量,例如表名、列明和LIMIT子句中。
  • 用户自定义变量的生命周期是在一个连接(session)中有效,所以不能用它们来做连接间的通信。
  • 如果使用连接池或者持久化连接,自定义变量可能让看起来毫无关系的代码发送交互(如果是这样,通常是代码bug或者连接池bug)。
  • 不能显式地声明自定义变量的数据类型。
  • MySQL优化器在某些场景下可能会将这些变量优化掉。
  • 赋值的顺序和赋值的时间点并不总是固定的,这依赖于优化器的决定。
  • 赋值符号:=的优先级非常低,所以赋值表达式应该使用明确的括号。

 

 

 

 

 

 

 

 

 

本文由9159.com发布于www.9159.com,转载请注明出处:尤其是多核处理器的提升,两个完全一样的查询

关键词: