《高性能MySQL》查询性能优化（上），慢查询的原因，重构查询的方式，查询的流程。

为什么会慢

由于查询任务是由多个子任务组成，慢肯定是因为某一或许某些子任务比较慢，想要优化的话，就要从子任务入手。
查询的整个生命流程可大致划分为：
1.客户端->服务器
2.服务器解析
3.生成执行计划
4.执行
5.返回结果
执行是比较会占用时间。这些操作会在网络、CPU计算、生成统计信息和执行计划、锁等待等消耗时间。尤其向底层存储引擎检索数据的调用操作，这些操作需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。

慢查询基础

基本原因就是访问了过多的行/列。
可通过下面两个步骤进行分析：

1.是否检索大量超过需要的行、列
2.确认MySQL服务器层是否在分析大量超过需要的数据行。

是否向数据库请求了无用的数据

1.查询不需要的记录
MySQL是先返回所有的结果集然后再进行计算的。最简单有效的方式就是在这样的查询后面加上LIMIT。
2.多表关联返回全部列
只返回需要的列。
3.总是取出全部列
对SELECT *有怀疑的眼光去看待，因为 *很可能无法使用覆盖索引，进而占用I/O,内存，CPU等资源。
4.重复查询相同的结果
缓存用起来

MySQL是否在扫描额外的记录

有三个衡量查询开销的三个指标：1.响应时间 2.扫描的行数 3.返回的行数。
这三个查询会记录到MySQL慢日志中。

1.响应时间
响应时间分为：1.服务时间 2.排队时间（等待资源的时间，并非真正执行的时间）
看到响应时间应该预估一下，这个时间是否合理(可使用快速上限估计法)

2.扫描的行数和返回的行数
理想情况下，应该相同，不过多表查询时，在查询了多行数据才能生成一行数据。

3.扫描的行数和访问类型
访问类型可通过explain命令中的type查看，从优到劣分别为1.const(常数引用) 2.唯一索引查询(ref_eq) 3.范围扫描(range) 4.索引扫描(ref) 5.全表扫描（all）
如果一个查询没办法找到更合适的访问类型，最后的解决办法通常是增加一个合适的索引。

另外就是应用WHERE的好坏
1.在索引中使用WHERE来过滤不匹配的记录，这是在存储引擎层完成的。
2.使用索引覆盖扫描(Using index)，从索引中过滤不需要的记录并命中结果,服务器曾完成，无须回表。
3.从数据表中返回数据然后过滤掉不满足条件的结果(Using where).服务器层完成，先读记录后过滤。

好的索引可以让查询使用合适的访问类型，尽可能值扫描需要的行。
如果发现查询须要扫描大量数据但只返回少数的行，通常可采用下面的技巧。
1。使用覆盖索引，避免回表
2.该表表库结构，例如设计单独的汇总表
3.重写这个复杂的查询（下一节介绍）

重构查询的方式

复杂查询还是多个简单查询

大多情况下复杂查询，个别情况下也可尝试多个简单查询。
复杂查询的原因是，MySQL自身扫描内存的效率是很高的，但是响应客户端的速率不是很高，查询次数一般来说越少越好。

切分查询

在什么情况下，拆分为小数据会比较好呢？
需要加锁的时候(比如删除数据)，如果一次性处理大量数据，就会给很多行都加锁阻塞后续操作，那么就应该将这个大查询拆分为小查询。
用LIMIT加以限定。

分解关联查询

有时候会需要将一个关联查询进行分解。
比如：

SELECT * FROM tag
	JOIN tag_post ON tag_post.tag_id=tag_id
	JOIN post ON tag_post.post_id = post_id
WHERE tag.tag = 'mysql';

就可以拆分为：

SELECT * FROM WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = tag字段为mysql时，的id;
SELECT * FROM post WHERE post.id IN (满足上述俩条件的id)

这俩最终的结果都是一样的，那么拆分它们有什么好处呢？

• 更高效的利用查询缓存，多表联合查询一旦一个条件变了缓存就全不能用了，而拆分为简单查询，缓存的复用率会比较高。
• 减少锁竞争
• 在应用层做关联更容易做到高性能和可拓展
• 可减少冗余查询。
• 可在应用中实现哈希关联来解决问题

查询执行基础

1.客户端发送一条查询给服务器
2.服务器先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。
3.服务端进行解析，预处理，再由优化器生成对应的执行计划
4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5.结果返回客户端

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

MySQL是半双工的，不能同时收发，只有完整的接受完消息，才能去响应它。
查询语句不能太大，要不然可能会拒绝服务(max_allow_packet决定)。
而返回数据一般会比较多，由多个数据包组成，当服务器开始响应客户端请求时，客户端必须完整地接受整个返回结果，这也是为啥要使用LIMIT的原因之一，减少网络资源。
多数连接数据库的库函数都可以通过获得全部结果集并缓存于内存里，1.也可以逐行获取需要的数据，第一种对服务器的资源占用时间比较短，但是应用层处理时间相对较长，且需要使用内存空间，2.而直接处理就会相对快一些，不过对MySQL服务器资源的占用就会久一些。(MyMaits开启缓存与否就是直接处理或者缓存于内存中（一级二级不影响，开了就是，一级是SqlSessio级别，二级是Mapper级别）)

查询状态

使用SHOW FULL PROCESSLIST命令来查询，有几个指令。

状态	解释
Sleep	线程正在等待客户端发送新的请求
Query	线程正在执行查询或者正在将结果发送给客户端
Locked	被阻塞
Analyzing and statistics	线程正在收集存储引擎的统计信息，并生成查询的执行计划
Copying to tmp table 【on disk】	正在执行查询，并将其结果集都复制到一个临时表中()，on disk将临时表放在磁盘里
Sorting result	线程正在对结果集进行排序
Sending data	可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先使用一个大小写敏感的Hash查找实现。
如果查询命中缓存，那么在返回查询结果之前MySQL会检查一次用户权限。如果没有问题，从缓存中取出结果。

查询优化处理

然后是将SQL转换为一个执行计划，MySQL再按照这个执行计划和存储引擎进行交互。这过程包含：解析SQL，预处理、优化SQL执行计划。其中任何错误都可能终止查询。

语法解析器和预处理
会生成一棵解析树(这让我想起Javac中编译代码也是将源码字符转化为一棵抽象语法树，每个节点中都是一个语法结构，之后只需对语法树进行即可，无需使用源码，如果有兴趣可以看看这篇文章，Java前端编译过程)，
语法解析器会对它的语法进行验证，
然后生成解析树，预处理器则再根据一些MySQL规则检查解析树中的数据是否合法(比如表、列不存在，解析名字和别名之类)。
最后预处理器会验证权限。

查询优化器
优化器将其转化为执行计划。一条查询可能会对应多种执行方式，最后都返回相同的结果。优化器则是从中选择一个最优的出来。
不过也不是完全准确的。

• 统计信息不准确，比如InnoDB大多情况下使用MVCC维护行锁，并不能维护一个数据表的行数的准确统计信息。
• 执行计划中的成本估算不等同于实际执行的成本(比如如果数据已经存于内存当中了，规模稍大的数据可能比存储于磁盘的快)
• 由于MySQL是基于成本模型的估算所以可能和预期的(一般期望时间耗费少)不一致。
• 不会考虑并发执行的查询。
• 有时会基于一些特定的规则
• 不会考虑不受控制的操作的成本(比如存储过程或者自定义的函数)
• 有时无法估计所有的执行计划

优化器分为静态优化和动态优化，
静态优化与Java中的前端编译器优化是相似的，基于编译树优化，一旦优化完成后会一直有效。
动态优化则根据查询的上下文有关，也可能和很多其他因素有关。这需要每次查询时重新评估(运行时优化的感觉，后端优化2333)。

MySQL可以处理的优化类型如下所示：

• 重新定义关联表的顺序
• 将外链接转换为内连接
• 使用等价变换规则
• 优化COUNT()、MIN()、MAX()(min\max,使用BTREE可直接读取，MIN/MAX ，如果使用的MyISAM，单独使用一个值来维护)
• 预估并转化为常数表达式，比如根据需要查询的索引是主键索引。
• 覆盖索引扫描
• 子查询优化：减少对数据的访问次数
• 提前终止查询 ：limit语句和发现一个不成立的条件。
• 等值传播：如果两列的值通过等式关联，那么MySQL能够把其中一个列的WEHER条件传递到另一列上。
• 列表IN()的比较，MySQL中首先将IN列表中的元素进行排序，然而二分查找合法的范围，然后再转换为OR语句。
  …

总之优化器很机智…一般情况下，让它自己完成工作就好了。

mysql如何实现关联查询：
或者叫嵌套循环关联，外部表（关联的表）作为内部循环，内部表(被关联的表)作为内部循环。这大概就是要小表驱动大表的原因吧。
执行计划
MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树
多表查询时的结构并非是一棵平衡二叉树，而是一棵形如：

所以阿里禁止超过三表join，应该就是这个道理吧。

关联查询优化器
它决定了表关联的顺序。
由于小表命中的语句概率上相对于大表来说要少一些，所以最好用小表驱动大表。优化器也是选择最后迭代次数相对较小的顺序来选择的，有时也会出现不是最优的情况（这时候就可以用STRAICHT_JOIN关键字）。

排序优化
无论如何排序都是个成本很高的操作。
数据量小在内存中进行(快排)，内存里大则在磁盘（将数据分块，快排+合并），无论哪一种，都统称为文件排序。

MySQL如下两种排序算法：
两次传输排序(旧版本)：
读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
单次传输排序(4.1之后)：
先读取查询所需的所有列，然后再根据给定列进行排序，最后直接返回排序结果。

排序时产生的临时表可能很大，因为每一个记录都会分配一个足够长的定长空间来存放。

执行查询引擎

生成执行计划后再调用对应的存储引擎的接口(handler API)即可。

返回结果给客户端

如果允许缓存，可将结果放在查询缓存中。
一旦执行计划制定完毕，处理完最后一个关联表，这时就开始给客户端返回结果，这样服务端无需存储过多的结果也可以第一时间返回(也可以使用SQL_BUFFER_RESULT最后一次性刷新返回)。
通过TCP协议传输。