查询优化是基于理解mysql基础知识的基础上的，如果对mysql基础不熟的优建议先阅读上一篇mysql基础知识篇。
6.1 查询为什么会变慢
查询的生命周期：客户端，到服务器，解析，生成执行计划，执行，返回结果给客户端。
其中执行最重要，包括大量为了检索数据岛存储引擎的调用以及调用后的数据处理，包括排序，分组等。
很多地方都需要消耗时间，一个查询我们可能会看到一些不必要的额外操作，某些操作被重复了很多次，某些操作执行得太慢等，优化查询目的就是为了减少和消除这些操作所花费的时间。
6.2 慢查询基础：优化数据访问
查询性能低下最根本原因是访问数据太多。很多查询都能减少访问数据量方式来进行优化。以下两个步骤分析很有效：
1 确认应用程序是否执行在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
2 确认mysql服务器层是否在分析大量超过需要的数据行。
综上，每次遇到慢查询的时候第一件事是查看应用程序或者sql是否查询了不需要的数据行
6.2.1 是否向数据库请求了不需要的数据
典型案例：
查询不需要的记录：返回了应用程序不需要的行，应用程序在把数据丢弃。最简单有效的办法就是加上limit；
多表关联时返回全部列：千万不要select * from A join B ... 而是要select A.a,B.b from ...，明确返回需要的列，看到select * 的写法就要怀疑了；
重复查询相同的数据：可以利用缓存
补充：
比如没分页就把所有数据加载进来；
比如说不需要总数的时候可以不count总数，加快效率；
比如count的时候，计算a left join b left join c， 那么计算count的时候只需要计算a即可，避免检索了BC没必要的数据
6.2.2 mysql是否在扫描额外的记录
对于mysql，最简单的衡量查询开销的三个指标如下：
响应时间，扫描的行数，返回的行数
响应时间：响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多少时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——比如IO或锁，当然实际情况更加复杂。
看到相应时间的时候要问问自己这是否是个合理的值，可以用“快速上限估计”法来估算。
扫描行数和返回的行数。
扫描的行数和访问类型：explain的type反应了访问类型，从慢到快有全表扫描到索引扫描index，范围扫描range，唯一索引扫描，常数引用等。
mysql能用如下三种方式应用where条件，从好到坏为：
在索引中用where条件来过滤不匹配的记录，这是在存储引擎完成的；
在索引中用where条件来过滤不匹配的记录，这是在存储引擎完成的；
用索引覆盖扫描（搜索的多列都是索引，而且where条件也是索引，extra列中为using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果，这是在mysql服务层完成的，但无需在返回表查询记录，如图第一条：（索引：KEY `crea_uninst` (`createdate`,`uninstalldate`)）
从数据库表中返回数据，然后过滤不满足条件的记录（extra列中出现using where）这在mysql服务层完成，mysql需要先从数据库表读出记录然后过滤，如图第二条
综上，尽量让查询能让mysql在存储引擎完成，也就是尽量查询列和where的内容都能使用索引
如果发现扫描大量数据但只返回少数行，那么可以尝试：
使用索引覆盖扫描，这样存储引擎无需返回表获取对应行就可以返回结果了；
改变库表结构，比如使用单独的汇总表；
重写这个复杂的查询，让mysql优化器能以更优化的方式执行这个查询。
6.3 重构查询的方式
6.3.1 一个复杂查询还是多个简单查询
mysql连接和断开都很轻量级，运行多个小查询现在已经不是大问题了。
mysql相应数据给客户端就慢多了，其他条件相同时，用尽可能少的查询当然是更好的，但有时候将一个大查询分解为多个小查询是有必要的。
6.3.2 切分查询
删除旧数据就是个很好的例子。一个大数据分批删除
横切：![]()
，大任务分批执行
，大任务分批执行6.3.3 分解关联查询
可以对表进行一次单表查询，然后将结果在应用中关联。比如：
- 让缓存效率更高。
- 将查询分解后，执行单个查询可以减少锁的竞争。
- 在应用层做关联，可以容易对数据库进行拆分，更容易做到高性能和可扩展。
- 查询本身效率也可能会有所提升。
- 可以减少冗余记录的查询。
- 更进一步，这样做相当于在应用层实现哈希关联，而不是mysql的嵌套循环关联。某些场合哈希关联的效率要高很多。
纵切：![]()
6.4 查询执行的基础
当向mysql发送一个请求时候，mysql做了什么：
6.4.1 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"标记，那表示MySQL正在将一个内存临时表放到磁盘上。
Sorting result
线程正在对结果集进行排序
Sending data
这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在
向客户端返回数
了解这些状态能让你了解当前客户端和服务器谁在等谁，在繁忙的服务器上看到大量不正常的状态时说明某个地方有异常了，可用第3章方法诊断哪里出问题。
6.4.2 查询缓存
解析查询语句前如果查询缓存打开的，那么mysql会先检查是否命中缓存，通过大小写敏感的哈希查找实现的，所以即使有一个字节不同也不会匹配结果。如果命中，那么mysql会检查权限，如果权限通过，mysql会跳过其他所有阶段直接从缓存拿出结果给客户端。
6.4.3 查询优化处理
sql转换成一个执行计划，mysql依照这个执行计划和存储引擎交互，包括子阶段：解析sql，预处理，优化sql执行计划。
语法解析器和预处理：生成解析树
查询优化器
语法树如果合法，则由优化器优化执行过程，转为执行计划。
可以通过查询当前回话的Last_query_cost的值来知道mysql计算当前查询的成本。
这个结果表明mysql优化器认为大概要1040个数据页的随机查找才能完成上面的查询。评估因素有：每个表或索引的页面个数，索引的基数（索引中不同值的数量），索引和数据行的长度，索引分布情况，优化器在评估成本时候不考虑页面缓存。
有很多原因会使得mysql优化器选择错误的执行计划：
统计信息不准执行计划中的成本估算不同于实际的成本mysql的最优可能和你想的最优不同mysql从不考虑其他并发执行的查询mysql不是任何时候都是基于成本的优化mysql不会考虑不受其控制的操作成本，如存储过程和自定义函数
查询优化包括静态优化和动态优化。
下面是mysql能处理的优化类型：
重新定义关联表的顺序。
将外部连接转化为内部连接。如发现关联对象为not null
使用等价变换规则。如(5==5 and a>5) 转为a>5
优化count(),min()和max()。explain看到select tables optimized away表示优化器已经从执行计划中移除了该表，并用常数取代之。
预估并转化为常数表达式。
索引覆盖扫描。说了很多了
子查询优化。
提前终止查询。典型例子就是limit
等值传播。如：select film.flim_id from film inner join film_actor using(film_id) where film.film_id >500 不必写为 ...where film.film_id >500 and film_actor.film_id>500
列表in()的比较。先对in()列表的数据排序，然后通过二分查找方式确定列表中的值是否满足条件，对in()列表中有大量取值时候mysql处理速度会更快。
有时候可以给优化器添加hint提示帮助优化。
mysql如何执行关联查询：
对任何关联都执行嵌套循环关联操作，即mysql先在一个表中循环取出单条记录，然后再嵌套换到下一个表中寻找匹配的行，直到找出所有表中匹配的行位置。然后根据各个表匹配的行，返回查询中需要的各个列。mysql在最后一个关联表无法找到更多的行后，会返回上一层关联表，看能否找到更多匹配记录，以此类推迭代执行。
从本质上说．MySQL对所有的类型的查询都以同样的方式运行。例如，MySQL在FROM子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中（注16），然后将这个临时表当作一个普通表对待（正如其名“派生表”）。MySQL在执行UNION查询时也使用类似的临时表，在遇到右外连接的时候，MySQL将其改写成等价的左外连接。简而言之，当前版本的MySQL会将所有的查询类型都转换成类似的执行计划。’不过，不是所有的查询都可以转换成上面的形式。例如，全外连接就无法通过嵌套循环和回溯的方式完成，这时当发现关联表中没有找到任何匹配行的时候，则可能是因为关联是恰好从一个没有任何匹配的表开始。这大概也是MySQL并不支持全外连接的原因。还有些场景，虽然可以转换成嵌套循环的方式，但是效率却非常差，后面我们会看一个这样的例子。
执行计划：
mysql先生成查询指令树，然后通过存储引擎完成这指令树并返回结果。如果对某个查询执行explain exetended后在执行show warning 就可以看到重构出的查询。
在计算机科学中，这被称为一颗平衡树。但是这并不是mysql执行查询的方式。正如我们前面章节介绍的，mysql总是从一个表开始一直嵌套循环、回溯完成所有表关联。
所以，mysql的执行计划总是如下图所示，是一颗左侧深度优先的树。
关联查询优化器：
决定表关联时的顺序。通过评估不同顺序时的成本来选择一个代价最小的关联顺序。
有时候可以用straight_join让优化器按你的关联顺序执行，不过绝大多数时候，优化器做出选择比普通人更准。
排序优化：
排序用快排结合归并排序的算法。有两种排序算法：
两次传输排序：
对于filesort，MySQL有两种排序算法。
(1)两遍扫描算法(Two passes)
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。
注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。
(3) 一次扫描算法(single pass)
该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。
注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。
当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。
(1)两遍扫描算法(Two passes)
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。
注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。
(3) 一次扫描算法(single pass)
该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。
注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。
当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。
mysql文件排序时候用到临时存储空间可能会比想象的大得多，因为会对每一个排序记录都分配一个足够长的定长空间来存放。
6.4.4 查询执行引擎
mysql查询阶段只是简单根据计划给出的指令逐步执行，有大量操作都要通过存储引擎实现的接口来完成。
存储引擎有丰富的接口但底层只有几十个。
6.5 mysql查询局限性
6.5.1 关联子查询
mysql子查询实现得糟糕，如where条件包含in的子查询语句。
extra列有“not exists”，这是前面提到的提前终止算法
6.5.2 union的限制
union用临时表来完成的，所以无法把限制条件从外层“下推”到内层，所以限制应该加在内层查询语句上。
6.5.3 索引合并优化
5.0和更新版本中，where自居包含多个复杂条件时候，可以访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。
6.5.5 并行执行
mysql无法利用多核特性来并行执行查询。
6.5.6 哈希关联
mysql不支持哈希关联——所有关联都是嵌套循环关联，不过可以建立哈希索引来曲线实现哈希关联。
6.6.6 松散型索引扫描
联合索引无法跳过某个索引。
mysql通过全表扫描找到需要的索引：
其实可以用松散索引扫描，但mysql还不支持：
5.0之后版本某些场合可以使用松散索引扫描，在explain的extra提示 using index for group-by 表示这里使用松散索引扫描。
在mysql很好支持松散索引扫描之前，一个简单的绕过问题的办法就是给前面的列加上可能的常数值。
6.5.8 最大值最小值优化
无法根据主键来优化
6.5.9 在同一个表上查询和更新
还不支持
6.6 查询优化器的提示（hint）
6.7 优化特定类型的查询
count（）：统计列或列的表达式，则统计是表达式幼稚的结果数，另外一个作用就是统计行数，当使用count(*)时候就是统计行数，而不是扩展为所有的列，实际上他会忽略所有的列直接统计所有行数。
关于myisam的神话
myisam的count函数快是有前提的：没有任何where条件。有where条件时候和其他存储引擎没任何不同。
很多时候使用精确值成本非常高，而使用近似值则非常简单。比如计算活跃用户数等。
6.7.2 优化关联查询
一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
确保任何group by 和order by 中的表达式只涉及到一个表中的列，这样mysql才可能使用索引来优化这个过程。
6.7.3 优化子查询
优化子查询最重要的优化建议就是尽可能使用关联查询替代，至少当前的mysql版本需要这样。mysql5.6或者更新版本就可以忽略这方法了。
6.7.4 优化group by和distinct
当无法使用索引的时候，group by使用两种策略来完成：使用临时表或者文件排序来分组。
子查询创建的临时表没有任何索引。
没有order by子句显示指定排序列，当查询使用group by子句的时候，结果集会自动按照分组的字段排序，如果不关心结果集顺序，可以用order by null。也可以在groupby子句中直接使用desc或asc关键字，使分组按需要方向排序。
6.7.5 优化limit分页
当偏移量大的时候效率会很低，优化的最简单方法就是尽可能使用索引覆盖扫描，而不是扫描所有列，然后根据需要做一次关联操作在返回所需要的列，对于偏移量很大的时候，这样做的效率会提升非常大。如：
select id,desc from film order by title limit 10000,50;
select id,desc from film order by title limit 10000,50;
-->
select id,desc from film inner join (
select id,desc from film inner join (
select id from film order by title limit 10000,50
) as lim using(id);
这里延迟关联会大大提升查询效率，让mysql扫描尽可能少的页面，获取要访问的记录后在根据关联回表查询需要的列。
延迟关联的其他例子：
版权系统的订单管理：要列出每张订单的影片，因为先展示了订单内容，由于每页限制了订单数量，所以获取到订单数量很少，然后再关联影片。当然如果按影片名搜索则相反，先获取影片在关联订单。这样比一开始的先全部关联在做搜索要快无数倍。
版权系统的我的任务：和上面类似，先获取我的任务再连接影片名，而不是先做关联再获取我的任务。
有时候可以吧limit转换为已知位置的查询，让mysql通过范围扫描获得对应的结果。比如预算出了边界值，可以：
select id,desc from film where position between 50 an 54 order by position
还有用书签记录上次数据的位置，比如select * from rental order by id desc limit 20;
假设查询返回了16049到16030的记录，那么下一次查询就可以用16030开始：
假设查询返回了16049到16030的记录，那么下一次查询就可以用16030开始：
select * from rental where id<16030 order by id desc limit 20;
6.7.6 优化SQL_CALC_FOUND_ROWS
计算总数的时候可以估算值
6.7.7 优化UNION查询
mysql总是用填充临时表方式来执行UNION查询，因此很多优化策略在UNION中无法很好使用，经常要手工把where，limit，order by等字句下推到union各个子查询中，以便优化器可以充分利用这些条件进行优化。
除非要消除重复行，否则要用union all，没有ALL关键字，mysql会加上distinct。mysql总是把结果放入临时表然后读出然后返回给客户端，虽然很多时候没必要。
6.7.8 静态查询分析
percona toolkit 中的pt-query-advisor能分析查询问题，给出建议。
6.7.9 使用用户自定义变量
6.8 案例学习
总的来说，优化可以考虑点：
1选少行：应用程序和sql都要尽量先选中对的值
2避免低效排序：外排，临时表
3考虑表结构：索引，汇总表，这点和12点有关系
