时尚领域
新浪微博
微信
当前位置:时尚领域网 » 历史

MySQL:查询性能相关(其四)

在尝试编写快速的查询之前,需要清楚一点,真正重要的是响应时间。如果把查询看作是一个任务,那么它由一系列子任务组成,每个子任务都会消耗一定的时间。

如果要优化查询,实际上就是要优化他的子任务:要么消除一些子任务、要么减少子任务的执行次数、要么让子任务运行得更快。

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

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

在每一个消耗大量时间的查询中,我们都可以看到一些不必要的操作,优化查询的目的就是减少和消除这些操作花费的时间。

 

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

查新性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化:

1.确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行、或者也可能是太多的列。有些查询会请求超过实际需要的数据,然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的辅导,并增加网络开销,另外也会消耗应用服务器的CPU和内存资源。

>查询不需要的记录

>多表关联时返回全部列

>总是取出全部列

>重复查询相同的数据

2.确认MySQL服务器层是否在分析大量超过需要的数据行。在确定查询只返回需要的数据后,接下来应该看查询为了返回结果是否扫描了过多的数据。其中有三个指标:响应时间、扫描的行数、返回的行数。

如果发现查询需要扫描大量的数据但只返回少数行,通常有如下优化方法:

>使用索引覆盖扫描,把所有需要用的列都放到索引中

>改变库表结构

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

 

II.重构查询的方式

在优化有问题的查询时,目标应该是找到一个更优的方法获得实际需要的结果,而不是一定总是需要从MySQL获取一模一样的结果集。有时可以将查询转化一种写法让其返回的结果一样,但是性能更好;也可以通过修改应用代码,用另外一种方式完成查询,最终达到一样的目的。

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

2.切分查询

3.分解关联查询

 

III.查询执行的基础

当希望MySQL能够以更高的性能运行查询时,最好的办法就是弄清楚MySQL是如何优化和执行查询的。

1.客户端发送一条查询给服务器。

2.服务器先检查查询缓存,如果有就立刻返回存储在缓存中的结果;否则进入下一个阶段。

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

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

5.将结果返回给客户端。

这里步骤看起来很简单,但其中具体细节十分复杂。

第一步:MySQL客户端/服务器通信协议

MySQL的客户端和服务器之间的通信协议是半双工的,也就是说在任何一个时刻:要么是由服务器向客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生。所以我们无法也不用把一个消息分成多次发送。

客户端用一个单独的数据包将查询传给服务器,所以查询语句很长的时候max_allowed_packet参数就很重要了。一旦客户端发送了请求,那么接下来只能等待服务器的返回结果了。

相反的,一般服务器响应给用户的数据通常很多,由多个数据包组成。当服务器开始响应客户端请求时,客户端必须完整地接收整个返回结果,而不能简单的只取前面几条结果就让服务器停止发送数据(这也是在必要的时候再查询中使用LIMIT的原因)。

多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里,还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源,所以接收全部结果并缓存通常可以减少服务器的压力,让查询能够早点结束、早点释放相应的资源。

 

对于一个MySQL连接,或者说一个线程,任何时刻都有一个状态,表示MySQL当前正在做什么。

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

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

>Locked:在MySQL服务器层,该线程正在等待表锁(在存储引擎级别实现的锁如InnoDB行锁不会体现在线程状态中)。

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

>Copyingto tmp table:线程正在执行查询,并且将其结果都复制到一个临时表中。此时要么在做GROUP BY操作、要么是文件排序操作、要么是UNION操作。如果还有on disk标记,表示MySQL正在将一个内存临时表放到硬盘上。

>Sortingresult:线程正在对结果集进行排序。

>Sendingdata:线程可能在多个状态之间传递数据、或者在生成结果集、或者在向客户端返回数据。

 

第二步:查询缓存

在解析一个查询语句之前,如果查缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据,通过一个对大小写敏感的哈希查找实现的。如果有就立刻返回存储在缓存中的结果;否则进入下一个阶段。具体细节之后再进行深入探讨。

 

第三步:查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划,MySQL再依照这个执行计划和存储引擎进行交互。包括多个子阶段:解析SQL、预处理、优化SQL执行计划。具体细节之后再深入探讨。

 

第四步:查询执行引擎

在查询的执行阶段,MySQL只是简单地根据执行计划给出的指令逐步执行。为了执行查询,MySQL只需要重复执行计划中的各个操作,直到完成所有数据查询。

 

第五步:返回结果给客户端

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

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

MySQL将结果集返回给客户端是一个增量、逐步返回的过程。如在关联操作时,一旦服务器完成最后一个关联,开始生成第一条结果时,MySQL就可以开始向客户端逐步返回结果集了。这样服务器端无需存储太多的结果,避免消耗太多内存;也让MySQL的客户端第一时间后的返回的结果。

结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送,再通过TCP协议进行传输。在传输过程中可能对MySQL的封包进行缓存然后批量传输。


随笔,是记忆的一种延伸