一、MySQL查询执行基础
1. MySQL查询执行流程原理
<1> 客户端发送一条查询给服务器。
<2> 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
<3> 服务器进行SQL解析、预处理，再由优化器生成对应的执行计划。
<4> MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
<5> MySQL将结果返回给客户端，同时保存一份到查询缓存中。
2. MySQL客户端/服务器通信协议
<1> 协议类型：半双工。
<2> Mysql通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源。
<3> 在PHP函数中，mysql_query()会将整个查询的结果集缓存到内存中，而mysql_unbuffered_query()则不会缓存结果，直接从mysql服务器获取结果。当结果集很大时，使用后者能减少内存的消耗，但服务器的资源会被这个查询占用比较长的时间。
3. 查询状态
可以使用命令来查询mysql当前查询的状态：show full processlist。返回结果中的“State”键对应的值就表示查询的状态，主要有以下几种：
<1> Sleep：线程正在等待客户端发送新的请求。
<2> Query：线程正在执行查询或正在将结果发送给客户端。
<3> Locked：在MySQL服务器层，该线程正在等待表锁。（在没行锁的引擎出现）
<4> Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
<5> Copying to tmp [on disk]：线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态要么是在做group by操作，要么是文件排序操作，或者是union操作。
<6> Sorting result：线程正在对结果集进行排序。
<7> Sending data：表示多种请况，线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。
4. 查询缓存
<1> 这个检查是通过一个对大小写敏感的哈希查找实现的。
<2> 命中查询缓存之后，检查用户权限，直接从缓存中返回数据给客户端，不需要解析查询。
5. 查询优化处理
语法解析器和预处理：
<1> 通过关键字对SQL语句进行解析，生成一棵“解析树”。
<2> 解析器使用MySQL语法规则验证和解析查询（关键字是否正确...）。
<3> 预处理器根据一些MySQL规则进一步检查解析树是否合法（表、列是否存在...）。
<4> 预处理器验证权限。
查询优化器：
MySQL使用基于成本的优化器。它将尝试预测一个查询使用各种执行计划时的成本，并选择其中成本最小的一个。其中，成本是根据存储引擎提供的数据和引擎的统计信息计算得来的，可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O。有很多原因会导致MySQL优化器选择并不是最优的执行计划。MySQL能处理的优化类型：重新定义关联表的顺序、将外连接转化成内连接、使用等价变换规则、优化count()、min()和max()、预估并转化为常数表达式、覆盖索引扫描、子查询优化、提前终止查询、等值传播、列表in()的比较等等。
数据和索引的统计信息：
统计信息由存储引擎实现。Mysql查询优化器在生成查询的执行计划时需要向存储引擎获取相应的统计信息。
MySQL如何执行关联查询：
<1> MySQL认为任何一个查询都是一次关联，对任何关联都执行嵌套循环关联操作，从一个表开始一直嵌套循环、回溯完成所有表关联。
<2> Union查询：先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成union查询。（临时表没有任何索引）
<3> MySQL在执行子查询的时候也是先将子查询的结果放到一个临时表中。
<4> 遇到右连接的时候mysql会将其改写成等价的左连接。
<5> 将所有的查询类型都转换成类似的执行计划。。
<6> 并不是所有的查询都可以通过嵌套循环和回溯的方式完成，例如全外连接，所以Mysql并不支持全外连接。
执行计划：
对某个查询执行explain extended后再执行show warnings就可以看到重构出的查询。因为mysql执行查询采用的总是嵌套循环关联操作，所以mysql的执行计划总是一棵左侧深度优先的树。
关联查询优化器：
MySQL优化器最重要的一部分就是关联查询优化。关联优化器通过评估多个表的不同关联顺序的成本来选择一个代价最小的关联顺序，如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行计划树的成本，最后返回最优的一个执行计划。但是当关联表的数量比较多的时候，这样做的成本太高，当需要关联的表数量超过optimizer_search_depth参数值的时候，优化器会选择使用“贪婪”搜索的方式查找“最优”关联顺序。有时候优化器给出的不是最优关联顺序，这时如果不希望关联优化器改变表的关联顺序的话，可以使用straight_join来强制表的连接顺序。
排序优化：
<1> 排序是一个成本很高的操作，应尽可能避免排序操作。
<2> Mysql的两种排序算法：
两次传输排序（旧版本使用）：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
单次传输排序（新版本使用）：先读取查询所需要的所有列，然后在根据给定列的值进行排序，最后直接返回结果。
两个算法各有优缺点，当查询需要所有列的总长度不超过参数max_length_for_sort的值时，mysql使用单次传输排序。
<3> 如果关联查询需要排序，MySQL会分两种情况来处理文件排序：
如果order by子句中的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表的时候就进行文件排序（Explain结果Extra字段“Using filesort”），否则，MySQL都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后再进行文件排序（Explain结果Extra字段“Using temporary;Using filesort”）。
二、MySQL查询优化器的局限性
1. 关联子查询
MySQL的子查询实现非常糟糕，最糟糕的一类查询是where条件中包含IN()的子查询语句。MySQL会将相关的外层表压到子查询中进行关联查询。
包含In的子查询优化：
<1> 使用group_concat()在in()中构造一个由逗号分隔的列表。
<2> 改写成关联查询或使用exists代替。
2. Union的限制
有时，MySQL无法将限制条件从外层“下推”到内层，使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。在union的各个子句中分别使用order by和limit，可以减少临时表中的数据，但想获取正确的顺序还需加上一个全局的order by和limit操作。
3. 索引合并优化
在5.0和更新的版本中，当where子句中包含多个复杂条件的时候，mysql能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。
4. 等值传递
Mysql优化器会将In()列表复制应用到关联的各个表中。
5. 并行执行
Mysql无法利用多核特性来并行执行查询。
6. 哈希关联
可以通过创建一个哈希索引来曲线实现哈希关联。
7. 松散索引扫描
Mysql并不支持松散索引扫描。
8. 最大值和最小值优化
在需要取最大/最小值的字段上创建索引，然后在查询语句中加入“use index”语句强制使用索引，当MySQL读到第一条满足条件的记录的时候就是我们需要找的最大/最小值了。
优化示例：
优化前：
优化后：
9. 在同一张表上查询和更新
MySQL不允许对同一张表同时进行查询和更新。可以通过使用生成表的形式来绕过这个限制，因为MySQL只会把这个表当成临时表来处理。
优化示例：
优化前：
优化后：
10. 查询优化器的提示
可以在查询语句中加入一些提示来控制查询的执行计划。
三、优化特定类型的查询
1. 优化count()查询
如果在count()的括号中指定了列或者列的表达式，那么统计的是这个表达式有值的结果数（不包含NULL）。统计行数使用count(*)意义更清晰，性能也会更好。
MyISAM执行没有任何where条件的count(*)非常快，因为可以利用存储引擎的特性直接获得这个值。如果mysql知道某个列col不可能为null值会将count(col)表达式优化为count(*)。可以利用MyISAM的这个特性来优化加速一些特定条件的count()查询。优化示例：
优化前（需要扫描很多的数据行）：
优化后（将需要扫描的数据行减少到5以内）：
（在查询优化阶段会将其中的子查询直接当做一个常数来处理）
在对精确值要求不高的情况下，可以通过一些途径取得近似值来达到优化查询的目的：
<1> 使用explain出来的优化器估算的行数来替代count(*)，不需要真正去执行查询。
<2> 去除一些对总数影响很小的where条件。
<3> 删除distinct约束避免文件排序。
<4> 更复杂的优化：使用汇总表、使用缓存系统等。
2. 优化关联查询
<1> 确保on或using子句中的列上有索引，只需要在关联顺序中的第二个表的相应列上创建索引。
<2> 确保任何的group by和order by中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
<3> 当升级MySQL的时候需要注意，关联语法、运算符优先级等其他可能会发生变化的地方。
3. 优化子查询
尽量使用关联查询替代（并不绝对，如果使用的是MySQL5.6或更新版本或MariaDB的话）。
4. 优化group by和distinct
优化这两种查询最有效的方法是使用索引来优化。
当无法使用索引的时候，group by使用临时表或文件排序来做分组，可以通过提示SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照我们希望的方式运行。如果需要对关联查询做分组，通常采用查找表的标识列分组的效率会比其他列更高。但如果查询语句无法写成在select中直接使用非分组列的形式或当前sql_mode禁止这样做的话（ONLY_FULL_GROUP_BY的sql_mode会返回错误），可以使用min()，max()函数来绕过这种限制。
如果没有通过order by子句显式指定排序列，当查询使用group by子句的时候结果集会自动按照分组的字段进行排序，可以在其后面直接使用asc或desc关键字指定排序方向。若不需要进行排序，可以加上order by null让MySQL不在进行文件排序，提高查询性能。
5. 优化group by width rollup
使用超级聚合的查询不够优化，可以在from子句中嵌套使用子查询来替代超级聚合，或者是通过一个临时表存放中间数据，然后和临时表执行union操作来得到最终结果。但最好的办法还是尽可能将width rollup功能转移到应用程序中进行处理。
6. 优化limit分页
在使用limit子句的查询中，如果没有对应字段的索引，当偏移量很大的时候，mysql需要查询大量数据行但是只返回一小部分数据，对这种情况进行优化的方法有：
<1> 使用索引覆盖扫描，然后再做一次关联操作返回所需的列。
<2> 想办法将limit查询转化为已知位置的查询。
7. 优化union查询
Mysql总是通过创建并填充临时表的方式来执行union查询，经常需要手工将where、limit、order by等子句“下推”到union的各个子查询中进行优化。除非确实需要服务器消除重复的行，否则一定要使用union all，不然mysql会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性检查。
8.优化select *查询
当使用select * from tbl_name语句进行查询的时候，mysql服务器会先从数据表中解析出全部字段名称，替换掉查询语句中的"*"，然后缓存解析替换之后的查询语句，最后再将解析替换之后的查询语句进行执行，并且以后遇到select * from tbl_name语句都会直接使用缓存中的包含数据表全部字段的语句进行查询。所以使用"*"而不指定字段名称有以下弊端：
（1）会增加SQL的解析成本；
（2）如果不是全部字段都有用的话，查询非必需字段还会造成资源浪费甚至影响服务器性能；
（3）无法利用索引覆盖查询，不利于查询的性能优化；
（4）若是数据表结构修改之后还使用缓存中的语句进行查询，还会发生字段映射错误问题。
9. 使用用户自定义变量优化查询
使用用户自定义变量的查询无法使用查询缓存，生命周期只在一个连接中有效，是一个动态类型，赋值符号:=优先级很低，赋值表达式应该使用括号。
优化排名语句：
（演过最多电影的前10名演员，使用子查询生成一个中间的临时表来解决自定义变量赋值时间和我们预料不同的问题）
避免重复查询刚刚更新的数据：
统计更新和插入的数量：
编写偷懒的union：
第一个子查询作为分支条件先执行，如果找到了匹配的行，则跳过第二个分支。
（将“热”数据和“冷”数据分别放在两个不同的表，使用union去查询）
有时优化器会把变量当作一个编译时常量对待而不是对其进行赋值，将函数放在类似least()这样的函数中通常可以避免这样的问题。