MySQL查询过程解析

MySQL的三层逻辑架构

1. 最上层主要负责连接处理、授权认证、安全等功能。每个客户端的连接都对应着服务器的一个线程。当客户端连接到MySQL服务器时，服务器对客户端进行认证，认证方式可以为用户名和密码，也可以是证书。登录验证过后，服务器还会检验客户端是否有执行某个查询的权限。
2. 第二层负责解析查询。MySQL大多数的核心服务均在这一层，包括查询解析、分析、优化、缓存、函数等。存储过程、触发器、视图等也都在这一层实现。
3. 最下层为存储引擎，负责MySQL中的存储数据、提取数据、开启事务等，和操作系统中的文件系统类似。存储引擎与上层通过API进行通信，不同的存储引擎有不同的差异。这些API屏蔽了不同存储引擎之间的差异，使得这些查询对上层来说是透明的。

客户端与服务器的通信

MySQL的客户端与服务器的通信方式为半双工。在任一时刻，要么客户端向服务器发送数据，要么服务器向客户端发送数据，两者不能同时进行。
一旦一方开始发送消息，则另一放只能在对方发送完整个消息之后才能响应。所以，MySQL的通信方式不能做到流量控制。
因此，在实际过程中，如果服务端相应给客户端的数据很多时，客户端必须接受完全部的返回数据，并不能接受到几条数据后让服务端停止发送。

MySQL查询过程

通过下面我们可以了解到，当MySQL客户端向服务器发送一个SQL请求，MySQL服务器执行了哪些过程

建立连接

客户端通过验证后，服务器会读取权限表确定该连接有哪些权限（mysql.db可以看到哪些用户拥有对哪些数据库的权限，mysql.tables_priv可以查看用户拥有哪些表的操作权限，mysql.user表则可以看到能登录到MySQL的用户信息）。
此后的所有操作都基于该连接的权限读取。如果你在建立连接后，再用管理员账号修改该用户的权限，也不会对该连接后的权限造成影响，除非再次建立连接。

查询缓存

在收到一条SQL后，在解析之前，如果缓存是打开的，MySQL会检查该查询是否命中缓存中的数据。如果在缓存中查到了该语句，则会将结果直接返回给客户端。如果没有查询到，则会继续执行后面的流程，同时会将查询的结果放到缓存中。
MySQL的缓存是将查询语句和结果已键值对的形式存储在一个类似于HashMap的数据结构中。其中，键值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以，两个查询任何字符上的不同（如：空格，注释）都会导致不能命中缓存
如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果。
既然是缓存，就会失效。MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。
缓存同时也会带来一些系统上的消耗，不仅如上面说的数据发生变化的写操作。读操作也不例外：
- 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
- 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，这也是额外的消耗

所以只当缓存带来的资源节约大于他本身的资源消耗时，开启缓存才能给系统带来性能提升。

SQL解析

首先，解析器会对SQL语句做词法分析，MySQL需要把SQL语句识别出来，哪个字符串代表什么，select会被识别为查询，tb会被识别为表，字符串id会被识别为列。
然后再进行语法分析，根据词法分析的结果，语法分析会根据语法分析的结果判断SQL是不是满足MySQL语法规则，如果不满足，则会抛出一个错误。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。
最后在进行预处理，比如检查要查询的数据表和数据列是否存在等。

查询优化

经过SQL解析生成的语法树合法之后，优化器将其转为查询计划。大多情况，一条查询可以有多种执行方式，返回相同的结果。优化器会找到最好的计划执行。
MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。
MySQL实例：

select * from order limit 10;
***数据***

mysql> show status like 'last_query_cost';
+-----------------+--------------+
| Variable_name   | Value        |
+-----------------+--------------+
| Last_query_cost | 95448.399000 |
+-----------------+--------------+
1 row in set (0.00 sec)

示例中的结果表示，优化器认为大概需要做95448个页面的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。
有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

查询执行

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

返回客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如改查询影响到的行数以及执行时间等等。
结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

总结

MySQL的整个查询过程分为5个步骤：

• 客户端向MySQL服务器发送一条查询请求
• 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
• 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
• MySQL根据执行计划，调用存储引擎的API来执行查询
• 将结果返回给客户端，同时缓存查询结果

[1] 素材来自于 CHEN川
[2] Baron Scbwartz 等著；宁海元 周振兴等译；高性能MySQL（第三版）; 电子工业出版社， 2013