【实现】查询执行的基础

mysql客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是有客户端想服务器发送数据，这两个动作不能同时发生。所以我们也无须将一个消息切成小块独立来发送。缺点是没法进行流量控制。max_allowed_packet控制请求大小。

 

查询状态：

对于一个mysql连接，或者说一个线程，任何时刻都有一个状态，该状态表示了mysql当前正在做什么。

最简单的是使用 show full processlist命令，该命令返回结果中的command列就表示当前的状态。

sleep：线程正在等待客户端发送新的请求。

query：线程正在执行查询或者正在将结果发送给客户端。

locked：在mysql服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态。

analyzing and statistics：线程正在手机存储引擎的统计信息，并生成查询的执行计划

copying to tmp table 【on disk】：线程正在执行查询，并且将其结果集豆腐知道一个临时表中，这种状态一般要么是在做group by操作，要么是文件排序操作，或者是union操作。如果这个状态后面还有“on disk”标记，那表示mysql正在将一个内存临时表放到磁盘上

sorting result：线程正在对结果集进行排序

sending data：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

 

查询缓存：

通过一个对大小写敏感的哈希查找实现，查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果。

 

查询优化处理：解析sql生成解析树，预处理器检查解析树是否合法，查询优化器将其转化成执行计划

mysql使用基于成本的优化器，依赖存储引擎提供的统计信息来评估成本。

 

数据和索引的统计信息

统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息。

统计信息包括：每个表或者索引有多少个页面，每个表的每个索引的基数是多少，数据行和索引长度，索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。

 

如何执行关联查询

mysql认为任何一个查询都是一次关联。

对于union查询，mysql先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成union查询。在mysql的概念中，每个查询都是一次关联，所以读取结果临时表也是一次关联。

关联执行的策略：

mysql对任何关联都执行嵌套循环关联操作，即mysql先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，知道找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。mysql会尝试在最后一个关联表中找到所有匹配的行，如果最后一个连表无法找到更多的行以后，mysql返回到上一层关联表，看是否能够找到更多的匹配记录，依次类推迭代执行。

从本质上说，mysql对所有的类型的查询都以同样的方式运行。例如，mysql在from子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中（mysql的临时表是没有任何索引的，在编写复杂的子查询和关联查询的时候需要注意这一点。这一点对union查询也一样），然后将这个临时表当做一个普通表对待。

 

查询计划：

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

 

关联查询优化器

重新定义关联的顺序，关联优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树。糟糕的是，当有超过n个表的关联，那么需要检查n的阶乘种关联顺序。当需要关联的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式了.

有时，各个查询的顺序并不能随意安排，这时关联优化器可以根据这些规则大大减少搜索空间。例如，左连接，相关资查询。这是因为，后面的表的查询需要依赖于前面表的查询结果。这种依赖关系通常可以帮助优化器大大减少需要扫描的执行计划数量

 

排序优化

无论如何排序都是一个成本很高的操作。如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过mysql将这个过程统一称为文件排序。

如果需要排序的数据小于“排序缓冲区”，MYSQL使用内存进行“快速排序”操作。如果内存不够排序，那么mysql会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。

mysql有两种排序算法：

两次传输排序，

排序列+指针，排序列排好序后，指针回表取数据。缺点：回表时产生大量的随机I/O

单词传输排序

先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。缺点：如果需要返回的列非常多，非常大，会额外占用大量的空间。

在关联查询的时候如果需要排序：

1.如果order by子句中的所有列都来自关联的第一个表，那么mysql在关联处理第一个表的时候就进行文件排序extra字端会有 Using filesort。除此之外的所有情况，mysql都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后，再进行文件排序，extra可以看到“Using temporary，Using filesort”。如果查询中有limit的话，limit也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据仍然会非常大。

 

返回结果给客户端

mysql将结果返回客户端是一个增量，逐步返回的过程。2个好处：服务器端无须存储太多的结果，也就不会因为要返回太多的结果而消耗太多内存。另外。这样的处理也让mysql客户端第一时间获得返回的结果。

 

关联子查询

资查询实现得非常糟糕，最糟糕的一类查询是where条件中包含in（）的子查询语句。

 

并行执行

mysql无法利用多核特性来并行执行查询。很多其他的关系型数据库能够提供这个特性，但是mysql做不到。