MySQL查询过程
Last updated
Last updated
我们写了一条SQL语句，MySQL从接收到这条语句，直到返回给我们结果的这个过程中，MySQL内部做了什么。
下图展示了MySQL的逻辑架构图：
图1（来源：《高性能MySQL》page2）
第一层：连接处理、授权认证、安全等
第二层：查询解析、分析、优化、缓存、内置函数以及所有的跨存储引擎的功能：存储过程、触发器、视图等。
第三层：存储引擎，负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。
我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。
当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？
图2（来源：《高性能MySQL》page204）整个过程如下：
（1）客户端向MySQL服务器发送一条查询请求
（2）服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
（3）服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
（4）MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
（5）将结果返回给客户端
下面，将详细介绍各个阶段MySQL的具体工作，以及我们在使用时，可以进行优化的地方。
MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。
客户端发送SQL给服务端
客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数：如果查询语句的大小超出该大小，服务端会拒绝接收更多数据并抛出异常。
服务端发送结果给客户端
服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。
优化原则：尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量，尽量避免使用SELECT *以及加上LIMIT限制。
此外，可以通过show full processlist来查询每个MySQL连接（或线程）的状态：
Sleep：线程正在等待客户端发送新的请求。
Query：线程正在执行查询或者正在将结果发送给客户端。
Locked：线程正在等待表锁（主要是针对MyISAM，InnoDB中的行锁不会体现在线程的状态中）。
Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
Copying to tep table：线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做 GROUP BY操作，要么是文件排序操作，或者是 UNION操作。如果这个状态后面还有“ on disk”标记，那表示 MYSQL正在将一个内存临时表放到磁盘上。
Sorting result ：线程正在对结果集进行排序。
Sending data：这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。
在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据：该查询是通过哈希查找实现。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。
但在实际应用中，查询缓存是否开启，有很多不同意见，参考：理解MySQL数据库查询缓存以及Query Cache，看上去很美。
如果数据表被更改，那么和这个数据表相关的全部Cache全部都会无效，并删除。这里“数据表更改”包括:INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等。如果你的数据表更新频繁的话，那么Query Cache将会成为系统的负担。如果你的应用对数据库的更新很少，那么Query Cache将会作用显著。
解析器：使用MySQL语法规则验证和解析查询，并生成一棵对应的“解析树”，这些解析包含但不限于如下内容：
验证是否使用错误的关键字
使用关键字的顺序是否正确等
引号是否能前后正确匹配。
预处理：根据一些MySQL规则进一步检查解析树是否合法，包含但不限于如下内容：
检查数据表和数据列是否存在
解析名字和别名，看看它们是否有歧义
验证权限，这一步通常很快，除非服务器上有非常多的权限配置。
查询优化器：将前面步骤生成的语法树转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。
MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。但是有非常多的原因会导致MySQL选择错误的执行计划：①统计信息不准确（比如InnoDB并不能精确维护一个表的的行数）；②执行计划中的成本估算不等同于实际的执行计划的成本；③ MySQL的最优可能与你想的最优不一样（即成本最低并不一定是时间最短）；④ MySQL从不考虑其他并发的查询，这可能会影响当前查询的速度；⑤ MySQL也不是任何时候都是基于成本的优化，有时候也会基于一些固定的规则；⑥ MySQL不会考虑不受其控制的成本（例如执行存储过程或者用户自定义的函数的成本）。
在MySQL可以通过查询当前会话的
last_query_cost的值来得到其计算当前查询的成本。
重新定义关联表的顺序
数据表的关联并不总是按照在查询中指定的顺序进行，决定关联的顺序是优化器很重要的一部分功能。
将外连接转化成内连接
并不是所有的outer join语句都必须以外连接的方式执行。诸多因素，例如where条件、库表结构都可能会让外连接等价于一个内连接。MySQL能够识别这点并重写查询，让其可以调整关联顺序。
使用等价变换规则
使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。
（5=5 and a>5）将被改写为a>5。
（a<b and b=c）and a=5 将会被改写为 b>5 and b=c and a=5。
4. 优化count()、min()和max()
min()：只查询对应B-tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点，在B-tree索引中，优化器会将这个表达式最为一个常数对待。
max()：只需要读取B-tree索引的最后一个记录。如果MySQL使用了这种类型的优化，那么在explain中就可以看到“select tables optimized away”。从字面意思可以看出，它表示优化器已经从执行计划中移除了该表，并以一个常数取而代之
count(*)：没有任何where条件的count(*)查询可以使用存储引擎提供的优化，例如，MyISAM维护了一个变量来存放数据表的行数（但InnoDB中没有）。
5. 预估并转化为常数表达式
6. 覆盖索引扫描
当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无需查询对应的数据行。
7. 子查询优化
MySQL在某些情况下可以将子查询转换成一种效率更高的形式，从而减少多个查询多次对数据进行访问。
8. 提前终止查询
在发现已经满足查询需求的时候，MySQL总是能够立即终止查询。一个典型的例子就是当使用了limit子句的时候。除此之外，MySQL还有几种情况也会提前终止查询，例如发现了一个不成立的条件，这时MySQL可以立即返回一个空结果。
9. 等值传播
10. in()
在很多数据库系统中，in()完全等同于多个or条件的字句，因为这两者是完全等价的。
在MySQL中这点是不成立的，MySQL将in()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个o(log n)复杂度的操作，等价转换成or的查询的复杂度为o(n)，对于in()列表中有大量取值的时候，MySQL的处理速度会更快。
在完成解析和优化阶段以后，MySQL会生成对应的执行计划（一个数据结构，而非字节码），查询执行引擎根据执行计划给出的指令逐步执行得出结果。
整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。
查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等等。
如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。
结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了，这样做的好处是：
服务端就无须存储太多结果而消耗过多内存；
可以让客户端第一时间获得返回结果。
内容来源
《高性能MySQL》
