MySQL查询过程剖析

　　数据库的性能问题是我们一直需要考虑的一个问题。有人可能说建立最好的索引，设计最有的库表结构。但是这些在没有合理的设计查询的基础上，都无法实现高性能。
　　今天，我们就先聊一聊MySQL的查询过程。下图就是MySQL的查询过程图：

MySQL的查询过程

　　 首先，对其中的各个模块做一简单分析：
　　

1、客户端/服务端通信协议

　　首先，需要说明的是：MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。
　　
　　一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。
　　
　　客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。因为MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。

show VARIABLES like '%max_allowed_packet%';　　－－查看max_allowed_packet的大小
修改max_allowed_packet 参数：
1、修改mysql配置文件：
2、mysql命令行中修改：set global max_allowed_packet = 20*1024*1024

　　与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

２、查询缓存

　　在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行，从而提高查询效率。

　　需要说明的是：MySQL将缓存存放在一个引用表（类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。因为是依靠hash值查询 ，所以命中缓存就需要两者严格一致。两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不命中。

不会使用查询缓存的情况
　　如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。

// 不会使用查询缓存
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

// 使用查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

　　上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。

导致缓存失效的情况

　　既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

１、任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
2、如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

　　所以，我们需要知道的是：并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

1、用多个小表代替一个大表，注意不要过度设计
2、批量插入代替循环单条插入
3、合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
4、可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

注意：
　　不要轻易打开查询缓存，特别是写密集型应用。如果实在需要的话，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

3、语法解析和预处理

　　MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。
　　
　　预处理则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。
　　下一步预处理器会验证权限。这通常很快，除非服务器上有非常多的权限配置。

查询优化

　　上述说到，MySQL会解析查询，并创建内部数据结构（解析树）。接下来会对其进行各种优化。包括重写查询、决定表的读取顺序，以及选择合适的索引等。

　　MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个（注意；成本小，并不代表执行速度快）。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

select * from User;
...
show status like 'last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 7.999000 |
+-----------------+----------+

　　上述示例中的结果表示优化器认为大概需要做7个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。
　　
　　用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程。也可以通过请求优化器解释（explain）优化过程的各个因素，是用户可以知道服务器时如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关的配置，使应用尽可能的高效运行。

mysql> EXPLAIN SELECT * FROM user;
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL | 161    |       |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+

　　MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划，下面是一些常用的优化策略：
　　１、重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）；
　　2、优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值）；
　　3、提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）

　需要说明的是，优化器并不关心表使用的是什么存储引擎。但是，存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。例如：某些存储引擎的某种索引，可能对一些特定的查询有优化。

　　对于SELECT语句，在解析查询之前，服务器会先检查Query Cache（查询缓存），如果可以在其中找到对应的查询，服务器就不必在执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

4、查询执行引擎

　　在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。
　　实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

5、返回结果给客户端

　　查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。

　　如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。

　　结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

总结

MySQL整个查询执行过程：
1、客户端同数据库服务层建立TCP连接。
2、客户端向MySQL服务器发送一条查询请求。
3、连接线程接收到SQL语句之后，将语句交给SQL语句解析模块进行语法分析和语义分析。
4、先看查询缓存中是否有结果，如果有结果可以直接返回给客户端。
5、如果查询缓存中没有结果，就需要真的查询数据库引擎层了，于是发给SQL优化器，进行查询的优化，生成相应的执行计划。
6、MySQL根据执行计划，调用存储引擎的API来执行查询
7、使用存储引擎查询时，先打开表，如果需要的话获取相应的锁。 查询缓存页中有没有相应的数据，如果有则可以直接返回，如果没有就要从磁盘上去读取。
8、当在磁盘中找到相应的数据之后，则会加载到缓存中来，从而使得后面的查询更加高效，由于内存有限，多采用变通的LRU表来管理缓存页，保证缓存的都是经常访问的数据。
9、最后，获取数据后返回给客户端，关闭连接，释放连接线程。