执行查询的基础(一)

mysql是如何优化和执行查询的？

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

mysql客户端/服务器通信协议

查询状态
对于一个mysql连接，或者说一个线程，任何时刻都有一个状态，该状态表示mysql当前正在做什么。有很多种方式能查看当前的状态，最简单的是使用show full processlist 命令。在一个查询的生命周期中，状态会变化很多次。
sleep：线程正在等待客户端发送新的请求
query：线程正在执行查询或者正在将结果发送客户端
locked:在mysql服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态，但在其他没有行锁的引擎中也经常会出现。
Analyzing and statistics:线程正在收集存储引擎的统计信息，并生成查询的执行计划。
copying to tmp table [on disk]:线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做group by操作，要么是文件排序操作，或者是union操作。如果这个状态后面还有“on disk”标记，那表示mysql正在将一个内存临时表放到磁盘上。
sorting result:线程正在对结果集进行排序。
sending data：这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。如果命中了查询缓存，那么在返回查询结果之前mysql会检查一次用户权限。这仍然无需解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，mysql会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种能够情况下，查询不会被解析，不用生成执行计划，不会被执行。

查询优化处理

查询生命周期的下一步是将一个SQL转换成一个执行计划，mysql在依照这个执行计划和存储引擎进行交互。这里包括解析SQL（根据关键字生成对应的解析树，这里会设计到关键字和语法的验证。），预处理（根据一些mysql的规则进一步检查解析树是否合法。如查看数据表和数据列是否存在，名字和别名是否有歧义），优化SQL执行计划。
查询优化器
当通过sql的解析和预测里，这时的语法树被认为是合法的了，这时将由优化器将其转化为执行计划。优化器可以找到一条语句的最好的执行计划。但是也会有很多原因导致mysql优化器选择错误的执行计划。

• 统计信息不准确：因为mysql依赖存储引擎统计信息评估成本，但是InnoDB因为其mvcc架构，并不能维护一个数据表的行数的精确统计信息。所以会偏差很大。
• 成本估算不等同于实际执行成本
• mysql的最优可能和你想的最优不一样。因为衡量的表准可能不同，你可能是需要尽可能短的时间，但mysql可能是基于其成本模型选择最优的执行计划。而有些时候这并不是最快的执行方式。
• mysql从不考虑其它并发执行的查询，这可能会影响到当前你的查询速度。
• mysql有事也会基于一些固定规则进行优化，例如，如果存在全文搜索的match子句，则在存在全文索引的时候就会使用全文索引。即使有时候使用别的索引和where条件可以远比这种方式要快，Mysql也仍然会使用对应的全文索引。
• mysql不会考虑不受其控制的操作的成本，例如执行存储过程或者用户自定义函数的成本。
• 优化器无法估算所有可能的执行计划。
  重新定义关联表的顺序
• 将外连接转换为内连接：并不是所有的outer Jon语句都必须有外链接的方式执行。如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.
• 优化count()、min()和max()
• 预估并转换为常数表达式：当mysql检测到一个表达式可以转换为常数的时候，就会一直把该表达式作为常数进行优化处理。这里能转换为常数的可以使一个表达式，也可以是一个查询。
• 覆盖索引扫描：当索引中年的列包含所有查询中需要使用的列的时候，mysql就可以使用索引返回需要的数据，而无须查询对应的数据行
• 子查询优化：MySQL了在某些情况下可以将子查询转换一种高效更高的形式，从而减少多个查询多次对数据进行访问。
• 提前终止查询：如limit、发现一个不成立的条件等都会提前终止查询
• 等值传播：如果两个列的值通过等式关联那么mysql能够把其中一个列的where条件传递到另一列上。
• 列表In的比较：在mysql中会对in列表中的数据进行排序，然后通过二分查找的方式进行匹配，这样时间复杂度就由O(n）,变为O（logn）,当数据量足够大的时候，mysql的处理速度会更快
• 等等

执行计划

和很多其他关系数据库不同，mysql并不会生成查询字节码来执行查询。mysql生成查询的一颗指令树，然后通过存储引擎执行完成这颗指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

执行引擎

在解析优化阶段，mysql将生产查询对应的执行计划，mysql的查询执行引擎则根据这个执行计划来完成整个查询。这里的查询执行引擎会根据mysql给出的执行计划指令逐步执行。这个过程会调用存储引擎实现的接口来完成（handler api）。
##返回结果给客户端
查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，mysql仍然会返回这个查询的一些信息，如该查询影响到的行数。这里mysql返回结果集给客户端是一个增量、逐步的过程，这样的化服务端不需要存储太多的结果，也就不会因为要返回太多的结果而消耗太多的内存。另外这样的处理也会让mysql客户端第一时间获得返回的结果。如果查询可以被缓存，那么mysql在这个阶段也会将结果存放到查询缓存中。