mysql中sql执行过程,mysql中SQL执行过程详解

mysql执行一个查询的过程，到底做了些什么：

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

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

服务器段进行SQL解析、预处理，在优化器生成对应的执行计划；

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

将结果返回给客户端。

实际上mysql执行的每一步都比较复杂，具体的过程如下

第一步：mysql客户端和服务器通讯

1. 通讯

mysql客户端和服务器之间的通讯协议是“半双工”的，这意味着，在任何一个时刻，要么由服务器向客户端发送数据，要么由客户端向服务器发送数据，这两个动作不能同时发生。这种协议让mysql通信简单快速，但也限制了mysql。一个明显的限制是，这意味着没办法进行流量限制。一旦一端开始发生消息，另一端要接收完整个消息才能响应他。

客户端用一个单独的数据包将查询传给服务器。一旦客户端发送了请求，他能做的事情就只是等待结果了。

相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整的接受整个返回结果，而不是简单的只收取前面几条结果，然后让服务器停止发送数据。

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

2.应用程序把查询SQL语句发送给服务器端执行

在数据库层执行SQL语句时，应用程序会连接到相应的数据库服务器，把SQL语句发送给服务器处理。

第二步：查询缓存

在解析一个查询语句之前，如果查询缓存(MySQL默认打开，可以使用have_query_cache查看)是打开的，在接收到查询请求后，mysql并不会直接去数据库查询，而是优先检查这个查询是否命中查询缓存中的数据(某条给定的查询语句在第一次执行时，服务器会缓存这条查询语句和他返回的结果)。

而其中是否命中缓存是将此查询语句和缓存中的查询语句进行比对，如果完全相同，那就认为它们是相同的，就认为命中缓存了(是通过一个对大小写敏感的哈希查找实现的)。

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

如果当前的查询没有命中查询缓存，这种情况下查询就会进入下一阶段的处理。

第三步：查询优化处理，生成执行计划

接下来服务器会将一个SQL转换成一个执行计划，而这个阶段包括：解析SQL、预处理、优化SQL执行计划，其中任何一个阶段出错都会导致查询进行不下去。然后mysql在依照这个执行计划和存储引擎进行交互。

解析SQL：Mysql通过将SQL语句进行解析(语法解析器)，并生成一棵对应的解析树。MySQL解析器将使用MySQL语法规则验证和解析查询，如将验证是否使用错误的关键字，或者关键字的顺序是否正确。

预处理：预处理器根据一些Mysql规则进一步检查解析树是否合法，如数据表和数据列是否存在，解析列名和别名，是否有歧义。接下来预处理器会验证用户权限。查看用户是否有相应的操作权限。。

优化SQL：当语法树被认为是合法的了，优化器将SQL语句转化成执行计划，一条查询可以有很多种执行方式，最后都返回相同的结果，最后找到其中最好的执行计划(Mysql使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划的成本，选择其中成本最小的一个)。

优化器的作用就是找到这其中最好的执行计划。

第四步：查询执行引擎

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

mysql简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口即为“handler API”接口。为了执行查询，mysql只需要重复执行计划中的各个操作，直到完成所有的数据查询。查询中的每一个表由一个handler的实例表示。(实际上，在优化阶段Mysql就为每一个表创建了一个handelr实例，优化器可以根据这些实例的接口获取表的相关信息，如表的所有列名、索引统计信息等)

第五步：将查询结果返回客户端

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

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

mysql将结果集返回客户端是一个增量、逐步返回的过程。这样有两个好处：服务器端无须存储太多的结果，也就不会因为返回太多结果而消耗太多的内存；这样处理也让msyql客户端第一时间获得返回的结果。

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

-------------------------------------------

解析：

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

Mysql的最优可能和你想的最优不一样。你可能希望执行时间最短，但Mysql根据其成本计算得出的最优计划，可能执行时间并不是最短的。

优化器有时候可能会无法估算所有的可能的执行计划，导致有可能错误实际上最优的执行计划。

执行计划中成本估算不等同于实际执行的成本。如有时候执行计划需要读取更多的页面，但它成本却更小。Mysql层面无法知道哪些页面在内存中，哪些在磁盘上，所以实际执行过程中需要多少次物理I/O无法得知。

Mysql不会考虑不受其控制的操作的成本，如执行用户自定义的函数的成本。

统计信息不准确：Mysql依赖存储引擎提供的统计信息(每个表有多少个页面、数据行和索引的长度、索引的分布等)来估计成本，有的存储引擎提供的信息偏差可能比较大。如InnoDB因为MVCC的架构，并不能维护一个数据表的行数的精确统计信息。

查询结果/语句不会被缓存的情况

如果表上有任何锁，对这个表的任何查询语句都是无法被缓存的。

查询语句中有一些不确定的数据。如now()。

有用户自定义函数、用户变量不会被缓存。

包含任何不确定函数的语句。

当查询的结果大于缓存大小时，结果不会被缓存

Mysql能够处理的优化类型(可以做出的优化措施)

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

使用等价转换规则。如移除一些恒成立或恒不成立的判断。

可能的表达式转换为常熟表达式

提前终止查询。如使用limit。

执行计划

Mysql并不会生成查询字节码来执行查询。Mysql生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。Mysql关联执行的策略很简单：Mysql对任何关联都执行嵌套循环关联操作，即Mysql先在一个表中循环取出单条数据，然后再嵌套到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要各个列。Mysql会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，Mysql返回到上一层此关联表，看能否找到更多的匹配记录，依次类推迭代执行。所以Mysql的执行计划总是一颗左侧深度优先的树。

来源：oschina

链接：https://my.oschina.net/u/3683692/blog/3039787