MySQL查询执行的基础

　　当希望MySQL能够以更高的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。一旦理解这一点，很多查询优化实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行。

　　换句话说，是时候回头看看我们之前讨论的内容了：MySQL执行一个查询的过程。当向MySQL发送一个请求的时候，MySQL到底做了什么。

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

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

　　3 服务器进行sql解析，预处理，再由优化生成器生成对应的执行计划。

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

　　5 强结果返回给客户端。

　　上面的每一步都比想象的负载，我们在后续章节中将继续讨论。我们会看到在每一个阶段查询出来处于何种状态。查询优化器是其中特别复杂也是特别难理解的部分。还有很多例外的情况，例如，当查询使用绑定变量之后，执行路径会有所不同，我们将在下一章讨论这一点。

 

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

　　一般来说，不需要去理解MySQL通信协议的内部实现细节，只需要大致理解通信协议是如何工作的。MySQL客户端和服务器之间的通信协议是“半双工 ”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切成小块来独立发送。

　　这种协议让MySQL通信简单快速，但是也从很多地方限制住了MySQL。一个明显的限制是，这意味着无法进行流量控制。一旦一端开始发生消息，另一端要接收完整个消息才能响应它。这就像来回的抛球游戏：任何时刻只有一个人能控制球，而且只有控制球的一方才能将球抛回去（发送消息）。

　　客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候参数max_allowed_packet 就特别重要了。一旦客户端发送了请求，它能做的事情，就只是等待结果了。

　　相反的，一般服务器响应给客户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整的接受整个返回结果，而不能简单的只取前面几条结果，然后然服务器停止发送数据，这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果后，就粗暴的断开连接，都不是好主意。这也是在必要的时候一定要在查询语句中加上limit限制的原因。

　　换一种方式解释这种行为：当客户端从服务器取数据时，看起来是一个数据拉去的过程，但实际上是MySQL在向客户端推送数据的过程。客户端不断的接收从服务器推送的数据，，客户端也无法让服务器停下来。

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

　　当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取的数据，而实际上都是从这个库函数的缓冲读取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集的时候，这样走并不好，因为库函数会花费很多时间和内存来存储所有的结果集。如果能尽早的开始处理这些结果集，就能大大减少内存的消耗，这种情况下可以不使用缓存记录结果而是直接处理。这样走的缺点是，对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的。

　　查询状态

　　对于一个MySQL的连接，或者说是一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。有很多种方式能查看当前的状态，最贱的的是使用SHOW FULL PROCESSLIST 命令（该命令返回结果中的Command列就表示当前的状态）。在一个查询的生命周期中，转台会变回很多次。MySQL官方收藏对这些状态值的含义有最权威的解释，下面将这些状态列出来，并做一个简单的解释。

　　Sleep

　　线程正在等待客户端发送新的请求

　　Query

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

　　Locked

　　在MySQL服务器层，该线程正在等待表锁。在存储引擎实现的锁，例如Innodb的行锁，并不会体现在该线程状态中。对于myisam来说这是一个比较典型的状态，但在其他没有行锁的引擎中也会长出现。

　　Analyzing and statistics

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

　　Copying to tmp table【on disk】

　　线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是做Group by 操作，要么是文件排序操作，或者是UNION 操作。如果这个状态后面还有on disk 标记，那么表示MySQL正在将一个内存临时表放到磁盘上。

　　Sorting result

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

　　Sending data

　　这表示多种情况：线程可能是在多个状态之间传送数据，或者结果集，或者在向客户端返回数据。

　　了解这些状态的戒备含义非常有用，这可以让你更快的了解当前谁正在持球。在一个繁忙的服务器上，可能会看到大量的不正常状态，例如statistics 正在占用大量的时间。这通常表示，某个地方有异常了。