一、MySQL查询执行的路径

当希望MySQL能够以更高的性能运行查询时,最好的办法就是知道MySQL是如何优化和执行查询的。下图展示是当客户端向MySQL发送一个请求时,MySQL到底做了什么 :

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

2. 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的数据。否则,进入下一阶段

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

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

5. 返回结果给客户端

查询缓存

1.查询缓存默认是关闭的

2.使用查询 show variables like '%cache%' 查看缓存信息

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

MySQL客户端和服务器之间的通信协议是 "半双工"的。这意味着: 在任何一个时刻, 要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据,两个动作不能同时发生。所以,我们无法也无须将一个消息切成小块独立来发送

这种通信协议让MySQL通信简单快速,但是这也意味着没法进行流量控制,一旦一端开始发送消息,另一端要接收完整消息才能响应它。

查询状态

对于一个MySQL连接,或者说一个线程,任何时刻都有一个状态,该状态表示了MySQL当前正在做什么。有很多方式能查看当前的状态,最简单的是使用SHOW FULL PROCESSLIST命令（Command列表示当前的状态）

查询状态的解释

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: 这种表示多种情况: 线程可能在多个状态之间传送数据, 或者在生成结果集, 或者在向客户端返回数据。

三、查询缓存

在解析一个查询语句之前,如果查询缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同,那也不会匹配缓存结果

如果当前的查询恰好命中了查询缓存,那么在返回查询结果之前MySQL会检查一下用户权限。这仍然是无须解析查询SQL语句,因为在查询缓存中已经存放了当前查询需要访问表的信息。如果权限没问题,MySQL会直接从缓存中拿到结果返回给客户端。这种情况下,查询不会被解析,不用生成执行计划,不会被执行

四、查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划,MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段: 解析SQL、预处理、优化SQL执行计划。这个过程中任何错误（如：语法错误）都可能终止查询

语法解析器和预处理

1.MySQL通过关键字将SQL语句进行解析,并生成一棵对应的 "解析树"。 MySQL解析器将使用MySQL语法规则验证和解析查询。（例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等）

2.预处理则根据一些MySQL规则进一步检查解析树是否合法(例如，检查数据表和数据列是否存在,还会解析名字和别名,看看它们是否有歧义) 下一步的预处理器会验证权限。这通常很快,除非服务器上有很多的权限配置。

查询优化器

到这一步,语法树被认为是合法的了,并且由优化器将其转化为执行计划。一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基本成本的优化器,它将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。最初,成本的最小单位是随机读取一个4K数据页的成本,后来（成本计算公式）变得更加复杂,并且引入了一些“因子”来估算某些操作的代价,如当执行一次WHERE条件比较的成本。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。

这个结果标识MySQL的优化器认为大概需要做1040个数据页的随机查找才能完成上图所示的查询。这是根据一系列的统计信息计算得来的:每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存,它假设读取任何数据都需要一次磁盘I/O。

导致MySQL优化器选择错误的执行计划的原因

统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本,但是有的引擎提供的信息是准确的,有的偏差很大。例如，InnoDB因为其MVCC的架构,并不能维护一个数据表的行数和精确统计信息。
执行计划中的成本估算不等同于实际执行的成本。所有即使统计信息精准,优化器给出的执行计划也可能不是最优的。
MySQL的最优可能和我们想的最优不一样,我们可能希望执行时间尽可能的短,但是MySQL只是基于其成本模型选择最优的执行计划,而有时候并不是最快的执行方式
MySQL不会考虑其他并发执行的查询,这可能会影响到当前查询的速度。
MySQL也并不是任何时候都是基于成本的优化。有时候也会基于一些固定的规则。
MySQL不会考虑不受其控制的操作成本,例如执行存储过程或者用户自定义函数的成本。
优化器有时候无法去估算所有可能的执行计划,所以它可能错过实际上最优的执行计划。

查询执行引擎

在解析和优化阶段,MySQL将生成查询对应的执行计划,MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构,而不是很多其他的关系型数据库那样会生成对应的字节码

MySQL只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，即 "handler API"的接口。查询中的每一个表由一个handler的实例表示。 MySQL在优化阶段就会为每一个表创建一个handler实例,优化器根据这些实例的接口可以获取表的相关信息,包括列名,索引统计信息。

返回结果给客户端

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

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

    优点 ：
        1.服务器无需存储太多的结果，也不会因为返回太多结果消耗太多内存
        2.客户端能第一时间获取返回的结果

MySQL执行查询和优化的过程