深入解析MySQL查询的方式和基础

3.重构查询的方式

1.一个复杂查询还是多个简单查询

1. 设计查询的时候一个需要考虑的重要问题，是否需要将一个复杂的查询分成多个简单的查询。mysql从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。现代的网络速度比以前要快很多，无论是带宽还是延迟。 mysql内部每秒能够扫描内存中上百万行数据，相比之下，mysql响应数据给客户端就慢得多了。
2. 不过，在应用设计的时候，如果一个查询能够胜任时还写成多个独立查询是不明智的。

2.切分查询

1. 删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响mysql性能，同时还可以减少mysql复制的延迟。

3.分解关联查询

1. 很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。
2. 用分解关联查询的方式重构查询有如下的优势：
   1、让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。另外，对mysql的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了。
   2、将查询分解后，执行单个查询可以减少锁的竞争。
   3、在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
   4、查询本身效率也可能会有所提升。
   5、可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。
   6、更进一步，这样做相当于在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联，某些场景哈希关联的效率要高很多。
3. 在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，这样的场景比如：当应用能够方便地缓存单个查询的结果的时候；当可以将数据分布到不同的mysql服务器上的时候；当能够使用IN()的方式代替关联查询的时候；当查询中使用同一个数据表的时候。

4.查询执行的基础

	上图是查询执行过程

	1、客户端发送一条查询给服务器

	2、服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则，进入下一阶段

	3、服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。

	4、MYSQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。

	5、将结果返回给客户端。

1.mysql客户端/服务端通信协议

1. mysql客户端和服务端之间的通信协议是半双工的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这2个动作不能同时发生。
2. 这种协议让mysql通信简单快速，但是也从很多地方限制了mysql。一个明显的限制是，这意味着没法进行流量控制。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。
3. 客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候，参数max_allowed_packet就特别重要了，服务器端会拒收更多的数据并抛出响应错误。
4. 相反的，一般服务器响应给用户的数据通常更多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果。这也是需要在查询中加上LIMIT限制的原因。
5. 多数连接mysql的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。mysql通常需要等所有的数据都已经发送给客户端才能释放这条查询所占有的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能早点结束，早点释放相应的资源。
6. 但如果需要返回一个很大的结果集时，库函数缓存并不好。因为库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集，就能够大大减少内存的消耗，这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是：对服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占有的。
7. 查询状态：
   对于一个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标记，那么表示正在将一个内存临时表放在磁盘上。
   Sorting result：线程正在对结果集进行排序
   Sending data：这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。
8. 在一个繁忙的服务器上，可能会看到大量的不正常的状态，例如statistics正占用大量的时间，这表示，某个地方有异常了。

2.查询缓存

1. 在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果。
2. 如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前mysql会检查一次用户权限。这仍然是无须解析查询sql语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，mysql会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。

3.查询优化处理

1. 这一步是将一个sql转换成一个执行计划，mysql再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析sql、预处理、优化sql执行计划。这个过程中任何错误都可能终止查询。在实际执行中，这几部分可能一起执行也可能单独执行。

2. 语法解析器和预处理：
   mysql通过关键字将sql语句进行解析，并生成一颗对应的解析树。解析器将使用mysql语法规则验证和解析查询。例如：它将验证是否使用错误的关键字，或者使用的关键字的顺序是否正确，验证引号是否能前后正确匹配。
   预处理器则根据一些mysql规则进一步检查解析树是否合法，例如，将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。
   下一步预处理器会验证权限。

3. 查询优化器：
   1、现在语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行方式。
   2、mysql使用基于成本的优化器。它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。可以通过查询当前会话的Last_query_cost的值来得知mysql计算的当前查询的成本，value的数值单位是数据页。这是通过一系列的统计信息计算得来的：每个表或者索引页面个数、索引的基数、索引和数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘IO。
   3、有很多种原因会导致mysql优化器选择错误的执行计划：
   (1)统计信息不准确。mysql依赖存储引擎提供的统计信息来评估成本，但是可能有偏差。例如InnoDB因为其MVCC架构，并不能维护一个数据表的行数的精确统计信息。
   (2)执行计划中的成本估算不等同与实际执行的成本。
   (3)mysql的最优可能和设想不一样。mysql只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。
   (4)mysql从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
   (5)mysql也并不是任何时候都是基于成本的优化。有时也会基于一些固定的规则，例如：如果存在全文搜索的MATCH()子句，则在存在全文索引的时候就使用全文索引。即使有时候使用别的索引和WHERE条件可以远比这种方式要快。
   (6)mysql不会考虑不受其控制的操作的成本，例如执行存储过程或者用户自定义函数的成本。
   (7)优化器有时候无法去估算所有可能的执行计划
   4、优化器使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单地分为2种：
   静态优化：可以直接对解析树进行分析，并完成优化。在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。是一种编译时优化。
   动态优化：和查询的上下文有关，也可能和很多其他因素有关。是一种运行时优化。
   5、下面是一些mysql能够处理的优化类型
   (1)重新定义关联表的顺序
   数据表的关联并不总是按照在查询中指定的顺序进行。
   (2)将外连接转化成内连接
   并不是所有的OUTER JOIN语句都必须以外连接的方式执行。mysql能够识别并重写查询，让其可以调整关联顺序。
   (3)使用等价变换规则
   可以使用一些等价变换来简化并规范表达式。
   (4)优化COUNT(),MIN(),MAX()
   索引和列是否可为空可以帮助mysql优化这类表达式。 例如：要找到某一列的最小值，只需要查询对应索引最左端的记录。优化器会将这个表达式作为一个常数对待。如果使用了这种类型的优化，那么在EXPLAIN中就可以看到(Select tables optimized away); 没有任何WHERE条件的COUNT(*)查询也可以使用一些优化
   (5)预估并转化为常数表达式
   一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。
   (6)覆盖索引扫描
   当索引中的列包含所有查询中需要使用的列的时候，就可以使用索引返回。无需回表。
   (7)子查询优化
   在某些情况下可以将子查询转换为一种效率更高的形式，从而减少多个查询多次对数据进行访问。
   (8)提前终止查询
   当发现已经满足查询需求的时候，mysql总是能够立刻终止查询。例如当使用了LIMIT子句的时候；发现了一个不成立的条件时，这时可以立刻返回一个空结果。
   (9)等值传播
   如果两个列的值通过等式关联，能够把其中一个列的WHERE条件传递到另一列上。
   (10)列表IN()的比较
   在很多数据库中，IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。但是在mysql这点是不成立的。mysql将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件。
   6、如果能够确认优化器给出的不是最佳选择，并且清除背后的原理，那么也可以帮助优化器做进一步的优化。

4. 数据和索引的统计信息：
   因为服务器层没有任何统计信息，所以查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。

5. mysql如何执行关联查询
   1、mysql认为任何一个查询都是一次关联——并不仅仅是一个查询需要到2个表匹配才叫关联。
   对于UNION查询，先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询。每个查询都是一次关联，所以读取结果临时表也是一次关联。
   2、当前mysql关联执行的策略：对任何关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。mysql会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，返回到上一层次关联表，看是否能够找到更多的匹配记录，以此类推迭代执行。
   按照这样的方式查找第一个表记录，再嵌套查询下一个关联表，然后回溯到上一个表。
   3、不是所有的查询都可以转换。例如 ，全外连接就无法通过嵌套循环和回溯的方式完成，这时当发现关联表中没有找到任何匹配行的时候，则可能是因为关联时恰好从一个没有任何匹配的表开始。mysql不支持全外连接。

6. 执行计划：
   1、和很多其他关系数据库不同，mysql并不会生成查询字节码来执行查询。mysql生成查询的一颗指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED后，再执行WARNINGS，就可以看到重构后的查询
   2、任何多表查询都可以使用一棵平衡树表示
   3、但是，mysql的执行查询时一棵左侧深度优先的树。

7. 关联查询优化器
   1、通常多表关联的时候，可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。
   2、关联优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树。

8. 排序优化：
   1、当不能使用索引生成排序结果的时候，mysql需要自己进行排序，如果数据量小则在内存中进行，如果大则需要使用磁盘，不过mysql将这个过程统一为文件排序。
   2、如果需要排序的数据量小于排序缓冲区，mysql使用内存进行快速排序操作。如果内存不够排序，那么mysql会先将数据分块，对每个独立的块使用快速排序进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。
   3、mysql有2种排序算法：
   (1)两次传输排序(老版本)：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。这会产生大量的随机IO，但是优点是在排序的时候存储尽可能少的数据，这就让排序缓冲区中可能容纳尽可能多的行数进行排序。
   (2)单词传输排序(新版本)：先读取排序所需要的所有行，然后再根据给定列进行排序，最后直接返回排序结果。相比2次传输排序，只需要一次顺序IO,缺点是如果需要返回的列非常多，会额外占用大量的空间。因为单条排序记录很大，所以可能会有更多的排序块需要合并。
   4、mysql在文件排序的时候需要使用的临时存储空间可能会很大。原因在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够长以容纳其中最长的字符串。
   5、在关联查询的时候如果需要排序，mysql会分2种情况来处理这样的文件排序。如果ORDER BY子句的所有列都来自关联的第一个表，那么在关联处理第一个表的时候就进行文件排序。如果是这样，那么在mysql的EXPLAIN结果中可以看到Extra字段会有Using filesort。
   除此之外的所有情况，mysql都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下，在EXTRA字段可以看到Using temporary；Using filesort。如果查询中有LIMIT的话，LIMIT也会在排序之后应用。所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。
   PS：5.6版本在这里做了很多重要的改进，当只需要返回部分排序结果的时候，mysql不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后再进行排序。