本章围绕着查询性能优化这个话题展开，优化查询的目的：减少和消除查询操作所花费的时间。既然要去优化，首先必须去对整个查询的生命周期有个最基本的了解：结合下图简要来说就是**从客户端到服务器，然后再服务器上进行解析，生成执行计划，执行，并返回结果给客户端。**其中执行计划这个环节可以说是查询整个生命周期里最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等等。
6.1查询执行的基础（明白查询操作到底做了什么）
上图就是查询的整个过程，简单总结为以下几条：
1.客户端发送一条查询给服务器。
2.服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3.服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5.将结果再返回给客户端。
6.1.1 MySQL客户端/服务器通信协议
MySQL客户端和服务器之间的通信协议是“半双工”的，在任何一个时刻，要么由服务器向客户端向服务端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。
一旦客户端发送了请求，它能做的事情就只是等待结果了，如果查询太大，服务端会拒绝接收更多的数据并抛出相应错误，所以参数max_allowed_packet就特别重要。相反，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后主服务器停止发送数据。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果然后粗暴地断开连接，都不是好主意。这也是必要的时候需要在查询中加上limit限制的原因。
6.1.2 查询缓存
在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这是检查是通过一个对大小写敏感的哈希查找实现的。如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。如果权限没有问题，MySQL会跳过执行阶段，直接从缓存中拿到结果并返回给客户端。
6.1.3查询优化处理
查询生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。
1、语法解析器和预处理首先MySQL通过关键字将SQL语句进行解析，并生成一棵解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。例如是否使用错误的关键字，或者使用关键字的顺序是否正确，引号是否能前后正确匹配等。
2、预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名看它们是否有歧义。
3、一下步预处理会验证权限。
1.查询优化器
一条语句 可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到最好的执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。成本的最小单位是随机读取一个4K的数据页的成本，并加入一些因子来估算某引动操作的代价。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。
这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。
当然很多原因会导致MySQL优化器选择错误的执行计划：例如统计信息不准确或执行计划中的成本估算不等同于实际执行的成本。
MySql能够处理的优化类型
1.重新定义关联表的顺序
2.将外连接转化成内连接
3.使用等价变换规则，举例如果有(a<b AND b=c) AND a=5 改写为b>5 AND b=c AND a=5
4.优化COUNT()、MIN()、 MAX()：举例要找到某一列的最小值，只需要查询对应B-Tree索引最左端的记录，MySQL可以直接获取索引的第一行记录，在优化器生成执行计划的时候就可以利用这一点，在B-Tree索引中，优化器会将这个表达式作为一个常数对待。
5.预估并转化为常数表达式
6.覆盖索引扫描
7.子查询优化
8.提前终止查询
9.等值传播
10.列表In的比较
MySQL如何执行关联查询
MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到一个表中寻找匹配的行，依次下去直到找到的有匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。（嵌套循环关联）
执行计划：
MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED，再执行SHOW WARNINGS，就可以看到重构出的查询。MySQL总是从一个表开始一直嵌套循环、回溯完成所有表关联。如下图：
排序优化：
无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。如果需要排序的数据量小于排序缓冲区，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排序的块进行合并，最手返回排序结果。
MySQL有两种排序方法（了解）：
两次传输排序（旧版），读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。显然是两次传输，特别是读取排序后的数据时（第二次）大量随机I/O，所以两次传输成本高。
单次传输排序（新版），一次读取出所有需要的或SQL查询指定的列，然后根据排序列，排序，直接返回排序后的结果。顺序I/O，缺点：如果列多，额外占用空间。
MySQL在进行文件排序时需要使用的临时存储空间可能会比想象的要大得多，因为MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够以容纳其中最长的字符串。
在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果ORDER BY子句的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表时就进行文件排序。如果是这样那么在MySQL的EXPLAIN结果中可以看到Extra字段会有Using filesort。除此之外的所有情况，MySQL都会将关联的结果存放在一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下Extra字段可以看到Using temporary;Using filesort。如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。
6.1.4 查询执行引擎
相对于查询优化，查询执行简单些了，MySQL只根据执行计划输出的指令逐步执行。指令都是调用存储引擎的API来完成，一般称为 handler API，实际上，MySQL优化阶段为每个表都创建了一个 handler 实例，用 handler 实例获取表的相关信息（列名、索引统计信息等）。
存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像搭积木一样能够完成查询的大部分操作。例如，有一个查询某个索引的第一行的接口，再有一个查询某个索引条件的下一条目的功能，有了这两个功能就可以完成全索引扫描操作。
6.1.5 返回结果给客户端
查询执行的最后一个阶段就是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，例如该查询影响到的行数。
MySQL将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。
这样处理有两个好处：服务端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让MySQL客户端第一时间获得返回的结果。
至此就是整个查询的生命周期涉及到的基本知识点。下面就是如何优化查询了。
6.2慢查询基础：优化数据访问
查询慢的最基本原因是访问的数据太多，所以优化查询第一步就是先确认是否需要这么大的数据。
1.确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
2.确认MySQL服务器层是否在分析大量超过需要的数据行。
6.2.1 排查是否请求了不需要的数据
可以重点从以下几点排查：
1.查询不需要的记录
最简单有效的解决方法就是在这样的查询后面加上LIMIT。
2.多表关联时返回全部列
慎用select *
3.避免重复查询相同的数据
相同的数据可以初次查询时存进缓存中。（如何做？目前大部分系统的解决方案是将需要重复使用的数据缓存在Redis中）
**这里为什么不用MySql数据库自身的缓存呢？**我思考了一下有三个原因：
1.mysql缓存是完全凭借数据库自己运作，不需要我们程序员操作，但完全基于数据库服务器资源处理连接请求，极其消耗CPU和内存以及线程资源，这样对于大公司系统来说，就无法满足高并发的需求。可能缓存的数据，并不是我们全部需要的，可能我只需要其中的一个字段，但是公用一个sql已经查出来了，redis缓存更精确，耗用资源少。
2.由于更新频繁的表，缓存命中率很低，那就需要去指定哪些数据库甚至哪些表需要用缓存，哪些不要用，这也很麻烦。
3.由于分布式系统很多已经分库分表了，使用mysql数据库的缓存是不利于多台机器上的缓存共享的。
6.2.2 MySQL是否在扫描额外的记录
对于MySQL，最简单的衡量查询开销的三个指标如下：
1.响应时间
2.扫描的行数
3.返回的行数
响应时间
响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁。
扫描的行数
指的是做一个关联查询时，服务器必须要扫描多行才能生成结果集中的一行，扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间，不过有时候这个值也可能非常非常大。
访问类型
在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。在EXPLAIN语句中的type列反应了访问类型，访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引，索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。
一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：
1.在索引中使用WHERE条件来过滤不匹配的记录，这是在存储引擎层完成的。
2.使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
3.从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。
总结：如果发现查询需要扫描大量但只返回少数的行，那么通常可以尝试下面的技巧：
1.使用索引覆盖扫描
2.改变库表结果
3.重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。
6.3重构查询
6.3.1一个复杂查询还是多个简单查询
这个得具体看场景。
6.3.2切分查询
对于一个比较大的查询有时候会将它切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大查询语句就需要锁住很多数据、占满真个事务日志、耗尽系统资源、阻塞很多小查询。所以切分成多个小查询可能效果会更好。
6.3.3分解关联查询
追求高性能时，可以将关联查询进行分解。举例下面的查询：
将多表的关联查询分解成多个简单的单表查询，优点是：
1.让缓存效率跟高。上面的例子中，可能最后一条SQL语句执行时，缓存已经存入了ID为123/567/9098的内容了，那就不需要回表查询了，但如果是关联查询的话，如果关联的表任何一个表发生了改变就无法使用查询缓存了。
2.将查询分解成多个小查询，也减少了锁的竞争。
3.在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
4.查询本身效率也提高了，比如上面用In()代替关联查询，让mySql按照ID顺序进行查询，这可能比随机关联要更高效。
5.可以减少冗余记录的查询。
的内容了，那就不需要回表查询了，但如果是关联查询的话，如果关联的表任何一个表发生了改变就无法使用查询缓存了。
2.将查询分解成多个小查询，也减少了锁的竞争。
3.在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
4.查询本身效率也提高了，比如上面用In()代替关联查询，让mySql按照ID顺序进行查询，这可能比随机关联要更高效。
5.可以减少冗余记录的查询。