本节内容总结自《High Performance MySQL》
查询执行的基础
当向MySQL发送一个请求时，MySQL做了什么
- 客户端发送一条查询给服务器。
- 服务器先检查缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一个阶段。
- 服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划。
- MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
- 服务器将结果返回给客户端。
MySQL客户端/服务器通信协议
- MySQL客服端和服务器之间的通信方式
- MySQL客服端和服务器之间的通信协议是半双工的。这意味着我们无法将一个消息切分成小块独立发送。客户端用一个单独的数据包将查询传给服务器。相反服务器响应给用户的数据通常由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单地只取前面的几条语句，然后让服务器暂停发送数据，因此有时在查询中加上LIMIT是必要的。
- 使用缓存减小服务器的压力
- MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务压力，让查询能够早点结束，早点释放相应的资源，实际上服务器端默认也是这么做的。因此当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，实际上都是从这个库函数的缓存获取数据，但当结果集非常大时这样并不好，因为库函数会花很长时间和内存来存储所有的结果集，这种情况下可以不使用缓存来记录而直接处理查询，这样做的缺点是，对于服务器来说，需要查询完成才能释放资源。
- 查询状态：任何时刻的任何一个线程都有一个状态，该状态表示了MySQL当前正在做什么。最简单的方式就是使用SHOT FULLPROCESSLIST命令（该命令返回结果中的Command列就表示当前的状态）
- 状态分类
- Sleep：线程正在等待客户端发送新的请求。
- Query：线程正在执行查询或者正在将结果发送给客户端。
- Locked：在MySQL服务器层该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM和其他没有行锁的引擎中这是一个典型的状态。
- Analyzing and statistics：线程正在收集存储引擎的统计信息。并生成查询的执行计划。
- Copying to tmp table [on disk]：线程正在执行查询，并且将其结果集都复制到一个临时表中。后面还有 on disk 标记，这表示MySQL正在将一个内存临时表放到磁盘上。这种状态一般在做GROUP BY 操作，或者文件排序操作，获取UNION操作。
- Sorting result：线程正在对结果集进行排序。
- Sending data：这表示多种情况：线程可能在多个状态间传送数据，或者正在生成结果集，或者在向客户端返回数据。
查询缓存
- 在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会有先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，也不会匹配缓存结果。如果缓存命中，MySQL会检查一次用户权限。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存拿数据结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。
查询优化处理
- 查询生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL，预处理，优化SQL执行计划，这个过程中任何错误都会终止查询。
语法解析器和预处理
- 语法解析器：MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，他将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者他还会验证表达字符串的引号是否前后正确匹配等。
- 预处理器：根据MySQL规则进一步检查解析树是否合法，会检查数据表和数据列是否存在，还会解析名字和别名是否有歧义。下一步预处理器会验证权限。
查询优化器
- 优化器：优化器的作用就是找出一个查询所有执行计划中"最好"的执行计划。MySQL使用基于成本的优化器，他尝试预测一个查询使用某种执行计划时的成本，并选择其中最小的一个。这个成本计算是由一系列的统计信息和相对应的权重计算出来的：每个表或者索引的页面个数，索引的基数，索引和数据行的长度，索引的分布情况等，它假设读取任何数据都需要一次磁盘I/O。可以通过查询当前会话的Last_query_cost的值得知当前查询的成本。
- 导致优化器选择错误执行计划的原因
- 统计信息不准确。例如InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
- 执行计划中的成本估算不等同于实际执行的成本。
- MySQL的最优可能和你想的最优不一样。
- MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
- MySQL也并不是任何时候都是基于成本优化的。有时会基于一些固定的规则，例如，如果存在全文搜索MATCH()子句，则存在全文索引的时候就使用全文索引。即使有使用使用别的索引和WHERE条件可以远比这种方式快。
- MySQL不会考虑不受其控制的操作的成本，例如，执行存储过程或者用户自定义函数的成本。
- 优化器有时候可能无法去估算所有有可能的执行计划，导致错过了最优的执行计划。
- MySQL能够处理的优化类型
- 重新定义关联表的顺序：数据表的关联并不总是按照在查询中指定的顺序进行。
- 将外连接转化成内连接：并不是所有的OUTER JOIN语句都必须以外连接的方式执行。例如WHERE条件，库表结构都可能会导致会让外连接等价于一个内连接。MySQL能意识到这点并重写查询，让其可以调整关联顺序。
- 使用等价变换规则：MySQL可以使用一些等价变化来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。
- 优化COUNT()，MIN()和MAX()：索引和列是否可以为空通常可以帮助MySQL优化这类表达式。例如要找到一列的最小值，只需要查询对应索引的最左端的记录。优化器在生成执行计划的时候就可以利用这一点，在B-Tree索引中将这个表达式作为一个常数对待，最大值也类似于上述最小值。如果MySQL使用了这种优化可以在执行计划中看到"Select tables optimized away"。类似的，没有任何WHERE条件的COUNT(*)查询通常也会使用存储引擎提供的一些优化（例如，MyISAM维护了一个变量来存放数据表的行数）
- 预估并转化为常数表达式：当MySQL检测到一个表达式可以转换成常数时，就会一直把该表达式作为一个常数进行优化处理。
- 例如一个用户自定义变量在查询中没有发生变化的时候就可以转换为一个常数。
- 数学表达式是一个常数时。
- 有时候甚至一个查询也可以转换为一个常数。例如在索引列上执行MIN()函数等。
- 甚至是主键或者唯一键查找语句在WHERE语句中指定了常数条件之后，MySQL在查询开始阶段就先查找到这些值，这样优化器就会将其转换为常数表达式。
- 覆盖索引扫描：当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无需回表查询对应的数据行。
- 提前终止查询：在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。下面是一些典型的例子。
- 当使用LIMIT子句的时候。
- 当MySQL发现了一个不成立的条件时，会立刻返回一个空结果。
- 当需要检索"不同取值"或"判断存在性"时，MySQL执行过程中如果发现某些特殊的条件以致可以得出答案时会提前终止查询。
- 等值传播：如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一个列上。
- 列表IN()的比较：MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件。
- 如果能够确认优化器给出的不是最佳选择，并且清楚背后的原理，那么也可以在查询中添加hint提示来影响优化器的选择，也可以重写查询，或者重新设计库表结构和索引。