一、查询为什么会变慢？

需要明确的是编写快速的slq，真正重要的是响应时间
可以把查询看做是一个任务，其中包含一系列子任务，要优化查询，实际上是要优化子任务：

要么删除某些子任务。
要么减少子任务执行次数。
要么让子任务执行更快。

通常来讲，msql查询生命周期大致可以按照：从客户端到服务器，然后服务器解析、生成执行计划、然后调用存储引擎API执行查询，存储引擎检索、排序、分组处理，最后返回客户端。在完成查询任务时，查询任务需要在不同地方花费时间：

网络耗时
CPU计算
生成统计信息和执行计划
锁等待（互斥）
存储引擎检索数据
磁盘I/O
上下文切换等
每次出现消耗大量时间的查询通常是出现一些不必要的额外操作、某些操作被执行多次、某操作执行太慢。优化查询的目的就是消除或减少这些操作花费的时间。

二、优化数据访问

查询性能最基本的原因是访问的数据过多。大部分性能地下的查询都可以通过减少数据访问量的方式进行优化。通过下面2种方法分析宗师很有效：

确认程序是否存在检索大量超过需要的数据。这通常意味着访问了太多行或列。
mysql服务器是否存在分析大量超过需要的行。

分析是否向数据库请求了不需要的数据

有些查询会会请求超过需要的数据，然后这些多余的数据会被应用丢掉。这会给mysql服务器带来额外负担，并增加网络开销，同时也会消耗内存和CPU资源。

典型案例

查询不必要记录
常见的错误就是会误以为mysql只会返回需要的数据，需要开发设计人员习惯使用这样的设计，先使用select语句查询大量数据，然后获取前面的N行后关闭结果集（例如查询1000比商品信息，然后前端页面只展示前面10条）。开发人员会任务msql只会查询需要的10笔记录，然后停止查询。实际mysql会检索出全部的结果集，客户端程序会接收全部结果集然后抛弃其中大部分数据。最简单的优化手段就是在这样的查询加上LIMIT。
多表关联时返回全部的列
在设计中会经常看到类似这样的sql，查询电影的演员表：

 explain select * from actor inner join film_actor using(actor_id)
inner join film using(film_id) where film.title='ACE GOLDFINGER';

该查询将返回三个表全部的数据，在开发中应该避免出现这样的sql。正确的查询应该是：

 explain select actor.* from actor inner join film_actor using(actor_id)
inner join film using(film_id) where film.title='ACE GOLDFINGER';

总是取出所有列
每次看到select * 时都应该审视，是否需要返回全部的列？实际应该中大部分场景是不需要的。取出全部列，会让优化器无法使用覆盖索引扫描这类优化，同时还会对服务器带来额外I/O、CPU、内存网络消耗。尤其是应用和数据库服务器不是不是部署在一个节点，网络开销就别人明显了。
重复查询相同数据
在应用中经常会出现重复执行相同查询并返回相同结果。比如一个投票应用，投票者访问参赛者个人简介的时候可能会反复查询这个数据，比较好的方案就是初次查询时将该数据缓存起来，需要时从缓存取出，避免重复执行数据库访问。
分析是否在扫描额外的记录
确定查询只返回需要的数据后，接下来可以分析查询是否扫描了不需要的记录。最简单的衡量查询开销的三个指标：

响应时间
扫描行数
返回的行数

没有那个指标能够完美衡量查询开销，但它们大致反映了在数据库中内部执行查询需要扫描多少数据，并大致推算出查询运行时间。这三个指标会记录在慢查询日志中。

响应时间
响应时间是衡量查询开销最重要指标，响应时间分为服务时间和排队时间两部分：服务时间是数据库处理这个查询真正花费的时间，排队时间是指服务器因为资源等待没有执行真正查询的时间-I/O等待、锁等待等。
当看到一个查询响应时间时，首先需要分析响应时间是否是一个合理的值（快速上限估计）。
扫描行数和返回的行数
分析查询时，查看该查询扫描的行数和返回的行数是非常有帮助的。当扫描行数与返回行数比值太大，在一定程度能够说明查询找到需要的数据效率并不是太高。理想情况扫描行数和返回行数应该相等，但实际不可能出现。比如关联查询，数据库服务器必须扫描多行才能生成结果集中的一行。
扫描行数和访问类型
在评估查询开销时，需要考虑从数据库表中找到一行数据的成本。mysql有多种访问方式可以查找并返回一行结果。有些访问需要扫描多行才能返回一行结果，有的可能无需扫描就可以返回。explain语句中的type列反映了访问方式。访问方式有多种：
全表扫描（ALL）
索引扫描(index)
范围扫描(range)
多列等值扫描(range)
唯一索引\主键(const)
常数引用

这些扫描方式速度是从慢到快，扫描的行数也是从大到小。
explain语句的Extra列反映where条件使用，mysql提供三种方式应用where条件：

在索引中使用where过滤不匹配的数据，这是在存储引擎层完成的。
使用索引覆盖扫描来返回记录（Extra出现Using index），直接从索引中过滤不需要的数据并返回命中的结果。这是在服务器层完成的，但无需在回表查询记录。
从数据表中返回数据，然后使用where过滤不满足条件的记录（在Extra显示 Using where），这在mysql服务器层完成。

 explain select last_name from actor;--使用覆盖索引，虽然扫描了全部索引，但是无需回表查询直接命中结果
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       index       idx_actor_last_name 137     200 100.00  Using index

 explain select * from actor where last_name like 'A%';--使用索引过滤
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       range   idx_actor_last_name idx_actor_last_name 137     7   100.00  Using index condition

 explain select * from actor where first_name like 'A%';-- 使用where在服务器层过滤
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       ALL                 200 11.11   Using where

常见的优化查询需要扫描大量数据的技巧和方法:

使用索引覆盖扫描，把所有需要查询的列都放到索引中，这样存储引擎无需回表访问对应的行就可以直接返回结果。
改变库表结果，使用单独的汇总表。
重写复杂的查询。

三、重构查询方式

mysql在设计上让连接和断开连接很轻量级，在返回一个很小的查询结果方面很高效。mysql内部每秒能够扫描百万级数据，相比之下mysql响应数据给客户端就慢的多。在其他情况都相同时，使用尽可能少的查询是非常好的策略。

切分查询

有时候对于一个大查询我们需要分而治之，将大查询拆分为多个相同功能的小查询，每个小查询只完成一分部任务。例如数据库表复制、清理数据等。如果用一个大查询一次性完成的话，可能会一次性锁定大量行，赞满整个事务日志、耗尽系统资源、阻塞很小但很重要的查询等。
例如：每个月定期清理消息表数据
优化前

delete from message where create_time < date_sub(now(),INTERVAL 3 MONTH);

优化后

rows_affected = 0
do {
 rows_affected = do_query(
"delete from message where create_time < date_sub(now(),INTERVAL 3 MONTH) limit 1000"
)} while rows_affected > 0

分解关联查询

很多高性能应用都会对关联查询进行分解。简单的就是多每个表进行一次查询，然后在应用程序中进行关联。用分解关联的方式拆分查询有如下优势：

让缓存效率更高。应用程序可以方便的缓存单表查询对应的结果。另外对于mysql的查询缓存来说，如果关联查询某个表发生改变，那么msyql缓存就不能使用了，而拆分关联查询后，经常不发生改变的表的查询就可以重复利用该表的缓存了。
将查询分解后，执行单个查询可以减少锁竞争。
在应用层做数据管理，可以更容易对数据库进行拆分，更容易做到高性能和扩展性。
查询效率本身也会提升，例如使用in()列表等值查询，可以使msql根据ID顺序查询，比使用随机查询高效的多。
可以减少冗余记录的查询。在应用层做关联意味着某些记录只需要查询一次，而做关联查询，可能需要重复访问一部分数据。
在应用中做关联相当于实现hash关联，而不是msql的循环关联。

四、查询执行的基础

当希望mysql能够以更高性能运行查询时，最好的办法是弄清楚mysql是如何优化和执行查询的。

向mysql发起一个查询请求，mysql执行路径以上如所示：

客户端发起一条查询给服务器。

服务器先检查缓存，如果缓存命中，则立即返回缓存中结果，否则进行下一步。

服务器进行sql解析、预处理，在由优化器生成执行计划。

mysql根据优化器生成的执行计划调用存储引擎API执行查询。

将结果返回客户端。

mysql客户端/服务端通讯协议

mysql客户端/服务端通讯协议是“半双工”的，在任一时刻，要么是服务端向客户端发送数据，要么是客户端向服务端发送数据，两个动作不能同时发生。也无法将一个消息切成小块独立来发送。这种协议方式使得mysql通讯简单快速，也从很多地方限制了mysql，一个明显的限制就是mysql无法做流量控制：一端开始发生消息，另一端必须接收整个完整消息才能相应它。
客户端请求
客户端使用一个单独的包将查询传输给服务端。当查询特别长时，参数max_allowed_packet（5.7版本默认4M）非常重要，如果查询过大，服务端会拒绝接收更多数据并抛出相应错误。

 show variables like 'max_allowed_packet';
    Variable_name   Value
    max_allowed_packet  4194304
 4*1024*1024

服务端响应
一般服务端响应给客户端数据通常非常多，由多个数据包组成。当服务端向客户端响应数据时，客户端必须完整的接收整个返回结果，而不能被简单的只取前几条记录就让服务端停止传输数据。这是为什么在必要的时候必须在查询中加上LIMIT的原因。mysql服务端在向客户端传输数据时，实际是向客户端推送数据的过程，客户端没法使服务端停止下来。
查询状态
对于一个sql连接或者一个线程，任何时刻都有一个状态，该状态表示了mysql目前正在做什么。可以使用SHOW FULL PROCESSLIST 命令查看当前连接的状态。

show full processlist;
   Id  User    Host    db  Command Time    State   Info
   5   root    localhost:10310     Sleep   271     
   6   root    localhost:10311 sakila  Query   0   starting    show full processlist

mysql查询状态解释：

Sleep:线程正在等待客户端发送新的请求。
Query:线程正在执行查询或者正在讲结果发送给客户端。
Locked：在服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，比如InnoDB实现的行锁，并不会体现在线程状态中。
Analyzing and statistics:线程正在收集存储引擎的统计信息并生成存储计划。
Copying to temp table:线程正在执行查询，并将结果都复制到一个临时表。这种状态要么是在执行group by、要么是在执行文件排序、要么是在执行UNION操作。如果后面加上[on disk]，那么表示mysql正在讲临时表存储到磁盘中。
Sorting result:线程正在对结果集排序。
Sending data:线程可能在多个状态之间传送数据，或者生成结果集，或者在向客户端发送数据。

了解这些状态非常重要，可以通过这些状态判断当前线程谁在“持球”。
查询缓存
在解析查询语句前，如果查询缓存是打开的，mysql会优先检查这个查询是否命中查询缓存中的数据，如果命中直接返回查询缓存中的结果。检查规则是根据大小敏感的哈希查找实现。
查询优化处理
查询在未命中缓存后，进入服务器sql转换为执行计划过程：包括解析sql、预处理、生成执行计划。过程出现任何错误都可能终止查询。
语法解析器和预处理器

mysql首先通过关键字将sql语句进行解析，生成一颗对应的“解析树”，mysql解析器使用mysql语法规则验证和解析查询。解析器主要验证sql关键字是否使用错误或者关键字顺序是否正确以及引号是否能够前后正确匹配。
预处理器根据mysql规则进一步验证“解析树”是否合法：验证数据表和数据列是否存在，数据别名是否存在歧义，以及权限验证（这一步通常非常快，除非服务器设置了许多复杂验证规则）。

查询优化器
sql语法检查通过后，由优化器将其转换为sql执行计划，一个查询可以有多种执行方式，最后都返回相同结果，优化器的作用就是找到最合适的执行计划。
mysql使用基于成本的优化器。将尝试预测一个查询使用某种计划的成本，并选择一个成本最小的一个。优化器基于存储引擎的统计信息:每个表或索引的页面个数、索引的基数（索引中不同值得数量）、索引与数据行的长度、索引的分布情况等来评估成本。优化器在评估成本是不会考虑任何层面的缓存。
导致优化器选择错误执行计划原因：

统计信息不齐备：比如InnoDB因为其MVCC架构，不可能维护一个数据表的精确统计信息。
执行计划成本估计不等于实际执行的成本。
mysql最优和实际期待的最优不一样。
优化器不考虑其他并发的查询。
优化器不考虑一些不受控制的成本，比如CPU计算、执行存储过程或自定义函数的消耗等。

mysql优化策略简单可以划分为两种，静态优化和动态优化。静态优化直接对解析树进行分析并完成优化。动态优化和查询的上下文有关。在执行语句或者存储过程的时候，静态优化和动态优化的区别非常重要，mysql对查询的静态优化在整个过程只执行一次，而动态优化则需要每次执行时都重新评估。
mysql能够处理以下优化类型：
重- 新定义关联表的顺序：数据关联表查询并不是总按sql指定的关联顺序进行。决定关联顺序是优化器很重要的一个功能。

外链接转换内连接
使用等价变换规则：mysql会使用等价变换规则简化并规范表达式。可以合并和减少一个比较以及移除一些恒成立和恒不成立的条件。例如（1=1 and a > 5）将变换为a > 5。类似（a<b and b=c） and a=5 则会改写成 b>5 and b=c and a=5。
优化count()、min()、max():索引和列是否为空可以帮助优化这些函数表达式。
预估并转换为常数表达式：当mysql检测到一个表达式可以转化为常数时，就会一直把该表达式当做常数进行优化处理。
覆盖索引扫描：当存在索引列覆盖查询所有列时，msyql会使用索引直接返回查询结果。
子查询优化
提前终止查询
等值传播
列表in的比较：在很多数据库系统in()完全等同于多个or条件，但是在mysql是完全不同等。mysql将in()列表中的数据先进行排序，然后使用二分查找的方式来确定列表中的值是否满足条件，其查询复杂度是O(logN)的操作，等价转换为OR查询的复杂度为O(N),对于In()列表中有大量取值的时候，mysql处理速度回更快。

mysql关联查询执行方式
在mysql中，任何一次查询都是一次“关联”—这不仅仅是一个查询需要两张表匹配才叫关联。在处理关联查询时，mysql会先将一系列单个查询的结果放到一个临时表中，然后在重新读取临时表中数据进行关联操作。
mysql执行关联策略很简单：对任何关联都执行嵌套循环关联操作，即msyql先在一个表中循环取出单挑数据，然后在嵌套循环到下一张表匹配数据，依次下去直到找到表中所有匹配为止。然后根据匹配的行，返回查询中需要的列。
执行计划
和大对数数据库系统不同，mysql不会生成查询字节码来执行查询。msyql服务器生成一颗查询指令树，然后通过存储引擎执行完成这颗指令树并返回结果。
排序优化
排序是成本很高的操作，从性能角度考虑，应该尽量避免排序或者避免大数据的排序。当不能使用索引排序时，mysql需要自己排序：数据量小的在内存中排序，数据大的在磁盘中排序，mysql将这过程统一称为文件排序。
如果需要排序的数据量小于排序缓存区大小mysql则直接在内存中快速排序。如果大于排序缓冲区，mysql会将数据分块，然后对每个独立的块进行排序，然后将各个块排序结果存储到磁盘，再将各个块的排序结果进行合并，最后返回排序结果。
查询执行引擎
在解析和优化器阶段，mysql服务器将sql生成对应的执行计划，而存储引擎则根据执行计划完成整个查询。这里的执行计划是一种数据结构（指令树）而不是字节码。

五、msyql查询优化器的局限

msyql查询优化器不是万能，对少部分查询不适用。对于不适合的场景我们可以重构改写sql让mysql高效的完成查询。
UNION限制
有时候mysql优化器无法将限制条件从外层“下推”到内层，这使得原本能够限制内层的条件无法应该到内层查询的优化中。
如果希望在UNION个子查询能够根据LIMIT只取部分结果集或者希望能够先排好序然后在合并结果集的话，需要在每个个子句分别使用LIMIT或者排序。
比如

 explain (select first_name,last_name from actor order by last_name)
union all
(select first_name,last_name from customer order by last_name) limit 20;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   PRIMARY actor       ALL                 200 100.00  
    2   UNION   customer        ALL                 599 100.00

这个查询会将actor的200条记录和customer的599条记录放到临时表中，然后在LIMIT 20条记录返回结果。
对这个查询的优化是分别在两个子查询使用LIMIT 20减少临时表的数据

 explain (select first_name,last_name from actor order by last_name limit 20)
union all
(select first_name,last_name from customer order by last_name limit 20) limit 20;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   PRIMARY actor       ALL                 200 100.00  Using filesort
    2   UNION   customer        ALL                 599 100.00  Using filesort

索引合并优化
mysql能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行，在合并过程需要消耗大量CPU计算和内存资源。
等值传递
某些时候等值传递会出现意想不到的额外消耗，比如当有一个非常大的in()列表，而mysql优化器发现存在where、using、on的子句，将这个列表的值和另一个表的某个列关联。优化器会将in()列表的值复制应该到关联的表中。通常如果in()列表小的时候，增加了过滤条件会提高效率，但是当in列表过大时，反而会导致查询变慢。
无法并行执行
mysql无法利用多核特性来并行执行查询任务。
最大值和最小值优化
mysql对min()、max()的优化做的并不好，可以看一个例子：

 explain select min(actor_id) from actor where first_name like 'A%';
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       ALL                 200 11.11   Using where

因为first_name上没有索引，因此会执行一次全表扫描。actor_id是actor表主键，理论上mysql读到的第一个值就是我们需要的最小值了，因为主键是严格按照大小排序的。但是实际mysql只会做全表扫描。比较曲线的优化就是去掉min使用LIMIT

 explain select actor_id from actor where first_name like 'A%' order by actor_id asc limit 1;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       index       PRIMARY 2       1   11.11   Using where

不允许在同一个表上查询和更新
mysql不允许同一时刻在同一张表上同时进行查询和更新。

六、查询优化器的提示

如果对查询优化器的优化结果不满意可以根据优化器的几个提示来控制最终执行计划。

delayed:这个提示对insert和replace有效，mysql会将使用该提示的语句立即返回给客户端，并将插入的行数据放入缓冲区，然后在闲时批量执行写入。日志系统或者客户端不需要等待单条记录完成I/O的应用非常适合这个提示。需要注意并不是所有存储引擎支持该提供，同时可能导致LAST_INSERT_ID()函数无法使用。

straight_jion:这个提示可以放到select语句的select关键字后面，也可以放到任何关联表的前面。该提示有两个作用：让查询中所有表安装sql语句中的关联顺序进行关联以及固定前后两个表的关联顺序。当mysql优化器无法正确选择关联顺序，或者关联太多优化器无法评估关联顺序时，straight_jion都很有用。可以使用explain查询关联查询顺序和执行效率，然后使用straight_jion提示固定查询顺序，在使用explain查询执行效率，选择最佳的顺序。

sql_mall_result和sql_big_result：该提示只有对select语句有效，它告诉优化器对group by和distinct查询如何使用临时表和排序。sql_mall_result告诉优化器这个结果集很小，可以直接使用排序缓冲区排序，sql_big_result告诉优化器这个结果集很大，直接使用磁盘临时表做排序操作。

sql_buffer_result：告诉优化器将查询放入临时表，然后尽可能释放锁。

sql_cache和sql_no_cache：告诉优化器是否将查询结果放入缓存。

for update和lock in share mode：这两个提示不是优化器提示，这两个提示控制了select 语句的锁机制，但只对行级锁存储引擎有效。

七、优化特定类型查询（优化案例汇总）

count()查询优化
count()聚合函数，是一个特殊函数，有两种不同作用：

统计某个列值的数量或统计行数。统计列值时，要求列值非空（不统计null）。如果在count函数中指定了列的表达式，那么就是统计这个表达式有值得结果数。
统计结果集的行数。当mysql确定统计的列值不可能为空时，实际上就是统计的行数，最简单的就是count()的时候通配符并不会扩展到每一列，实际上，mysql会忽略所有列而直接统计所有行。
如果我们是希望统计行数，那么直接使用count()，这样写更有意义，效率也更高。
误解MyISAM count()函数
MyISAM存储引擎的count()函数不是总是很快的，只有当没有任何where条件时才是最快的，因为根据MyISAM存取引擎特性，使用不带where条件的count(*)函数直接可以获得表的行数，而无需去统计。当统计带where条件的行数时，MyISAM和其他存储引擎就没任何区别了。
使用近似值
有时候某些业务并不需要精确的统计count值，这时可以使用近似值代替。explain出来的优化器估算行数就是一个不错的近似值，执行explain并不会真正执行查询，所以成本很低。
更复杂优化
使用count()函数本来就意味着需要扫描大量的行才能获得精确的结果，是比较难优化的。在mysql层面还能做的就是使用覆盖索引扫描。甚至修改应用实现，增加汇总表或者引入外部缓存系统了。
优化关联查询
确保using()和on子句上的列有索引。在创建索引是就要考虑关联顺序。当表A、B使用列C关联时，当关联的顺序是B、A，那么就不需要在B表上创建关联字段的索引了。一般来说，除非有其他理由，否则只应该在关联顺序的第二个表的相应列创建索引。
确保group by和order by表达式只涉及到一个表的列，这样的sql才能使用索引来优化排序和组合的过程。
升级mysql版本时需要主要：关联语法、运算符优先级等可能发生变化。
优化group by 和distinct
mysql对这两种查询都使用相同方法优化，这两种查询都可以使用索引来优化。当无法使用索引时，group by使用两种策略来优化：使用临时表或文件排序来分组。可以使用sql_big_result和sql_small_result提示告诉优化器按照希望的方式运行。
如果需要对关联查询进行分组，并且是按照查找表的某列进行分组，那么采用查找表的标识列分组会比其他列效率更高。
比如该查询效率可能不会太好：

 explain select first_name,last_name,count(*) from film_actor inner join actor using(actor_id) group by actor.first_name,actor.last_name;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       ALL PRIMARY             200 100.00  Using temporary; Using filesort
    1   SIMPLE  film_actor      ref PRIMARY PRIMARY 2   sakila.actor.actor_id   27  100.00  Using index
 explain select first_name,last_name,count(*) from film_actor inner join actor using(actor_id) group by film_actor.actor_id;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       ALL PRIMARY             200 100.00  Using temporary; Using filesort
    1   SIMPLE  film_actor      ref PRIMARY,idx_fk_film_id  PRIMARY 2   sakila.actor.actor_id   27  100.00  Using index

使用关联顺序第二张表效率会更高

 explain select first_name,last_name,count(*) from film_actor inner join actor using(actor_id) group by actor.actor_id;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  actor       index   PRIMARY,idx_actor_last_name PRIMARY 2       200 100.00  
    1   SIMPLE  film_actor      ref PRIMARY PRIMARY 2   sakila.actor.actor_id   27  100.00  Using index

优化LIMIT分页
在系统中进行分页操作时，我们通常使用LIMIT加上偏移量实现，同时加上合适的order by子句。如果order by 可以实现索引，效率通常会不错。如果不能使用索引那么只能使用文件排序。同时当偏移量特别大时，即时使用了索引，最后查询效率也会变得很慢，比如limit 100000 10。对于这类场景要么是限制客户分页，要么是优化大便宜量性能。
优化分页查询最好的手段就是使用覆盖索引扫描。然后在根据需要做一次关联查询获取需要的记录。

 explain select * from film order by title asc limit 50, 5;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  film        ALL                 1000    100.00  Using filesort

优化后：

 explain select * from film inner join (select film_id from film order by title asc limit 50, 5) t using(film_id) ;
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   PRIMARY <derived2>      ALL                 55  100.00  
    1   PRIMARY film        eq_ref  PRIMARY PRIMARY 2   t.film_id   1   100.00  
    2   DERIVED film        index       idx_title   767     55  100.00  Using index

另外也可以通过计算边界值的方式优化LIMIT或者使用一些冗余表的方式。
优化UNION
mysql总是创建并填充临时表的方式来执行UNION查询，因为没有过多优化策略。最主要的就是mysql无法将外部条件下沉到内部，因为子查询需要冗余的各自实现自己的过滤和排序等规则。另外除非必须消除重复的行，否则应该使用UNION ALL。
静态查询分析
Percona Toolkit中的pt-query-advior能够解析查询日志、分析查询模式，然后给出所有可能存在潜在危险的查询，然后给出足够详细建议。

mysql 查询优化