mysql查询性能优化
来源：互联网 发布：可视化分析软件 编辑：程序博客网 时间：2024/05/17 22:16
第三章讲解了如何优化数据库架构（Schema），它是获得高性能的必要条件。但是只有架构是不够的，还需要很好地设计查询。如果查询设计得不好，那么即使是最好的架构也无法获得高性能。
查询优化、索引优化和架构优化三者相辅相成。
本章首先讨论设计查询的基本原则，它也是查询性能不佳时最先考虑的因素；然后深入讲解了查询优化和服务器的内部机制，这部分展示了 MySQL 如何执行特定查询，从中也可以知道如何更改查询执行计划（Query Execution Plan）；最后介绍了 MySQL在优化查询方面的不足之处，并且探索了一些让查询获得更高执行效率的模式。
基本原则：优化数据访问
查询性能低下最基本原因就是访问了太多数据。一些查询不可避免地要筛选大量的数据，但这并不常见。大部分性能欠佳的查询都可以用减少数据访问的方式进行修改。在分析性能欠佳的查询的时候，下面两个步骤比较有用：
- 查明应用程序是否正在获取超过需要的数据。这通常意味着访问了过多的行或列。
- 查明 MySQL 服务器是否分析了超过需要的行。
向服务器请求了不需要的数据？
一些查询先向服务器请求不需要的数据，然后再丢掉它们。这给服务器造成了额外的负担，增加了网络开销[注 1]（Overhead），消耗了内存和 CPU 资源。
下面是一些典型的错误。
提取超过需要的列
多表联接（Multitable Join）时提取所有列
mysql> SELECT* FROM sakila.actor ->INNER JOINsakila.film_actor USING(actor_id) ->INNER JOINsakila.film USING(film_id) ->WHERE sakila.film.title ='Academy Dinosaur';这个查询返回了所有表中的所有列，正确是方式应该是这样：
mysql> SELECTsakila.actor.* FROM sakila.actor …;提取所有的列
基于这个原因，一些数据库管理员在所有地方都禁止使用 SELECT * ， 这样可以减少由于表中列改造而造成的问题。
当然，请求超过需要的数据也不总是坏事。在许多案例中，开发人员告诉使用这样浪费的方式可以简化开发，增加代码的复用性。如果明白这么做对性能的影响，那么这种做法也无口厚非。如果有其他的好想法，或者应用程序使用了某种缓存机制，那么这对获取超过实际需要的数据是很有好处的。运行大量只获取对象部分数据的单个查询时，有优先考虑获取对象的全部信息，然后缓存起来。
MySQL 检查了太多数据吗
一旦确定只获取了所需要的数据，那么接下来就应该检查在生成查询结果时是否检查了过多的数据。在 MySQL 中，最简单的开销指标（Cost Metrics）有下面 3 个：
- 执行时间
- 检查的行数
- 返回的行数
它们都不是衡量开销的完美指标，但它们大致反映了 MySQL 在内部执行查询的时候要访问多少数据，而且也大致说明了查询运行的时间。这 3 个指标别写入了慢速查询日志（Slow Query Log），所以浏览该日志是检索查找了过多数据的查询的最佳方式。
执行时间
第 2 章已经解释了 MySQL 5.0 及其以前的版本在标准慢速查询日志上有很大的缺陷，其中包括不能进行精细记录。幸运的是，补丁可以让 MySQL 以微妙的速度来记录查询日志。MySQL 5.1 内置了这些补丁，但以前的版本可以通过单独的补丁包得到这个功能。要明白的是不要过多的强调执行时间。它是一个不错的客观指标，但是它在不同的负载下是不一样的。另外的一些因素，比如存储引擎（表锁和行锁）、高并发和硬件都对执行时间有相当大的影响。这个指标可以用于寻找应用程序响应时间最大的查询，或者给服务器造成最大负载的查询，但是它不能说明某个查询在特定复杂度下执行时间是合理的。执行时间可以是问题的表现或原因，但是这并不明显。
检查和返回的行
在分析查询的时候，考虑检查的行是有用的，因为可以从中知道找到所需要的数据的效率。
但是，和执行时间一样，它也不是发现问题查询的完美指标。并非所有的行访问都是一样的。较短的行有更快的访问速度，从内存中提取行数据要远快于从磁盘上提取。
在理想情况下，返回的行和检查的行应该是一样，但是在实际中，这基本不可能。例如，使用联接来构造行时，必须要访问很多行才能产生一行输出。通常来说，检查的行和返回的行之间的比率通常较小，在 1:1 到 10:1 之间，但是偶尔它们之间也会差几个数量级。
检查的行和访问类型
在考虑查询开销的时候，想想从一个表中找一行的情况。MySQL 使用了几种方式来返回一行，某些需要查很多行，某些却一行都不用检查。
访问方式出现在 EXPLAIN 的 type 列，访问类型包括全表扫描（Full Table Scan）、索引扫描（Index Scan）、范围扫描（Range Scan），唯一索引查找（Unique Index Lookup）和常量（Constant）。访问它们的速度依次递增。不需要记住所有的类型，但是应该了解扫描表、索引、范围和单个值得概念。
如果没有得到好的访问类型，那么最好的解决办法是加一个索引。第 3 章已经对索引做了详细的解释，现在就来看看为什么索引对查询优化是如此地重要。索引让 MySQL 更有效地查找到所需要的行，访问的数据会更少。
例如，下面代码的的功能是对 sakila 数据进行一次简单查询：
mysql> EXPLAIN SELECT * FROMsakila.film_actor WHEREfilm_id = 1 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_actor type: refpossible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: const rows: 10 Extra:这个查询返回了 10 行结果， EXPLAIN 列出 MySQL 使用了 ref 类型访问了 idx_fk_film_id 索引。
EXPLAIN 说明了它只需要访问 10 行数据，换句话说，查询优化器知道选定的访问类型能有效地满足查询。如果没有索引，会出现什么样的情况？MySQL 会使用欠优化的查询，就像下面这样：
mysql> ALTERTABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;mysql> ALTERTABLE sakila.film_actor DROP KEY idx_fk_film_id;mysql> EXPLAIN SELECT * FROMsakila.film_actor WHEREfilm_id = 1 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_actor type:ALLpossible_keys: NULL key:NULL key_len:NULL ref:NULL rows: 5581 Extra: Usingwhere正如预料中的那样，访问类型成了全表扫描（ALL），而且 MySQL 现在必须检查 5073 行数据。Extra 列的 『Usign Where』 表明 MySQL 使用了 Where 子句来过滤掉多余的行。
通常来说，MySQL 会在 3 中情况下使用 Where 子句，从最好到最坏依次是：
- 对索引查找应用 Where子句来消除不匹配的行，这发生在存储引擎层。
- 使用覆盖索引（Extra 列是『Using Index』来避免访问行，并且从索引取得数据后过滤掉不匹配的行，这发生在服务器层，但是它不需要从表中读取行。
- 从表中检索出数据，然后过滤掉不匹配的行（在 Extra 列中是『Using Where』）。这发生在服务器端并且要求在过滤之前读取这些行。
这个例子表明好的索引很重要。好的索引让给查询有良好的访问类型并且只检查需要的行。然而添加索引并不总是意味着 MySQL 会访问并且返回同样的行。比如，下面的代码使用了 COUNT( ) 函数[注 2]的查询。
mysql> SELECT actor_id, COUNT(*) FROM sakila.film_actor GROUP BY actor_id;
这个查询只返回 200 行，但是它需要读取上千行数据。对于这种查询，索引不能减少检查的行数。
不幸的是，MySQL 不会列出生成最终结果需要多少行数据，它只会列出生成最终结果的过程中访问的总数据行数。许多数据行被 Where 子句剔除了，对最终结果没有贡献。在前一个例子中，在移除了 sakila.film_actor 上的索引之后，查询访问了整张表，并且 Where 子句只留下了 10 行数据。只有最后的 10 行数据用来生成最终结果。要理解服务器访问了多少行数据，以及实际上有多少行数据用于生成最终结果，需要对查询进行分析。
如果发现访问的数据行数很大，而生成的结果中数据行很少，那么可尝试更复杂的修改。
- 使用覆盖索引，它存储了数据，所有存储引擎不会去获取完整的行（参阅第 3 章相关章节）。
- 更改架构，一个例子就是使用汇总表（Summary Table）（参阅第 3 章相关章节）。
- 重写复杂的查询，让 MySQL 的有花旗可以优化的方式执行它（本章稍后会讨论该话题）。
重构查询的方式
当优化有问题的查询时，一个目标也许是找到一个替代的方案，但是这并不意味这要从 MySQL 得到完全一样的结果。偶尔可以用完全等价的方式得到更好的性能。但是如果不同的查询能提供更高的效率，尽管得到的结果不同，也可以考虑重写查询。
复杂查询和多个查询
一个重要的查询设计问题就是是否可以把一个复杂查询分解成多个简单的查询。传统设计理论强调用尽可能少的查询做尽可能多的事情。这种处理方式在以前有积极意义，因为它可以节省网络通信开销，以及减少查询解析和优化的步骤。
但是，这个想法对 MySQL 不是很适用。MySQL被设计成可以很高效地连接和断开服务器，而且能很快地响应精简的查询。现代网络比以前快了很多，延迟也小了很多。MySQL 在一般的服务器上每秒钟可以处理 500000 个查询，如果只有一个通信对象，在千兆网络上每秒钟可以处理 2000 个查询。因此运行多个查询并不是一很糟糕的事情。
但是对连接的响应比起服务器内部每秒能遍历的数据行还是少很多，后者对于内存中的数据来说每秒以百万计。在其他条件相同的情况下，使用尽可能少的查询仍然是个好主意，但是有时可以通过分解查询让它得到更高的效率。
尽管如此，在应用程序中使用太多的查询是一个普遍的错误。
缩短查询
一种处理查询的方式是分治法，让查询在本质上不变，但是每次只执行一次，以减少受影响的行数。
清理陈旧数据是一个很好的例子。周期性的清理工作需要移除大量的数据，如果用一个很大的查询来做这个工作，就会长时间地锁住多行数据，塞满事务日志，耗尽资源，打断一些本不该被打断的查询。采用细化 DELETE 语句并使用中等大小的查询会极大地改进性能，并且在复制的时候减少延迟。比如，下面是一个巨大的查询：
mysql> DELETEFROM messaeg WHEREcreated < DATE_SUB(NOW( ), INTERVAL 3 MONTH);应该类似于下面的伪代码的查询代替它。
rows_affected = 0do {rows_affected = do_query(“DELETE FROM message WHERE created < DATE_SUM(NOW( ), INTERVAL 3 MONTH) LIMIT 10000”)) while rows_affected > 0对于一个高效的查询来说，一次删除 10 000 行数据的任务已经足够大了。足够短的任务对服务器的影响最小[注 3]（事务存储引擎从较小的事务中得意）。在 DELETE 语句中加入休眠语句也是一个好主意，它可以分摊负载，并且减少锁住资源的时间。
分解联接
许多高效性能的网站都用了『分解联接』技术，可以把一个多表联接分解成多个单个查询，然后在应用程序端实现联接操作，比如下面的多表联接语句：
mysql> SELECT* FROM tag -> JOIN tag_post ON tag_post.tag_id = tag.id -> JOIN post ONtag_post.post_id = post.id -> WHERE tag.tag = 'mysql';可以用如下语句代替：
mysql> SELECT* FROM tag WHERE tag = 'mysql';mysql> SELECT* FROM tag_post WHERE tag_id = 1234;mysql> SELECT* FROM post WEHRE post.id IN (123, 456, 567, 9098, 8904);第一眼看上去比较浪费，因为这只是增加了查询的数量而已。但是这种重构的方式有下面的重大的性能优势：
- 缓存的效率更高。许多应用程序都直接缓存了表。在这例子中，如果有 mysql 这个标签的对象已经被缓存了，那么第一个查询就可以跳过。如果在缓存中找到了 id 为 123、567、或9098 的信息，那么就可以从 IN( ) 中把它们移除。查询缓存也可以从中得益，如果只有一个表经常改变，那么分解联接就可以减少缓存失效的次数。
- 对 MyISAM 表来说，每个表一个查询就可以更有效地利用表锁，因为查询会锁住单个表较短时间，而不是把所有表长时间锁住。
- 在应用程序端进行联接可以更方便地扩展数据库，把不同的表放在不同的服务器上面。
- 查询本身会更高效。在这个例子中，使用 IN( ) 而不是联接让 MySQL 可以对行ID 进行排序，并且更高效地取得数据。
- 可以减少多余的行访问。在应用程序端进行联接意味着每行数据只会访问一次，而联接从本质上来说是非正则化（Denormalization）的，它会反复地访问同一行数据。基于同样的原因，这种重构方式可以减少网络流量和内存消耗。
- 在某种意义上，可以认为这种方式是手工执行了哈希联接，而不是 MySQL 内部执行联接操作时采用的嵌套循环算法。哈希联机效率更高。
小结：什么时候在应用程序端进行联接效率更高
在下面对场景中，在应用程序端进行联接效率更高：
- 可以缓存早期查询的大量数据。
- 使用了多个 MyISAM 表。
- 数据分布在不同的服务器上。
- 对于大表使用 IN( ) 替换联接。
- 一个联接应用了同一个表很多次。
查询执行基础知识
想得到高性能，最佳的方法是学习 MySQL 如何优化和执行查询。
提示：阅读本节需先阅读第 2 章，它提供了 MySQL 查询执行引擎的基础知识。
图4-1 显示了MySQL执行查询的一般性过程。
简述过程：
- 客户端将查询发送到服务器。
- 服务器检查查询缓存。如果找到了，就从缓存中返回结果，否则进行下一步。
- 服务器解析、预处理和优化查询，生成执行计划。
- 执行引擎调用存储引擎 API 执行查询。
- 服务器将结果发送回客户端。
上面的每一步都有一些额外的复杂性。
图 4-1：查询的执行路径
MySQL 客户端/服务器协议
MySQL 客户端/服务器协议是半双工的，这意味着 MySQL 服务器在某个给定的时间，可以发送或接收数据，但是不能同时发送和接收。这也意味着没有办法截断消息。
这种协议让 MySQL的沟通简单而又快捷，但是它也有一些限制。其中一个就是无法进行流程控制，一旦一方发送消息，另一方在发送回复之前就必须提取完整的消息。这像来回抛球的游戏：在任意时刻：只有在某一方有球，而且除非有球在手上，否则就不能把球抛回去（发送消息）。
客户端用一个数据包将查询发送到服务器，这就是为什么 max_packet_size 这个配置参数对于大查询[注 4]很重要的原因。一旦客户端发送了数据，那就意味着『球』已经不在自己手上了，唯一能做的就是等待结果。
但是，服务器发送的响应由许多数据包组成。服务器发送响应的时候，客户端就必须接受完整的结果集。它不能只提取几行数据行就要求服务器停止发送剩下的数据。如果客户端只需要其中的几行数据，要么等待所有数据都传送完毕后丢掉不用的数据，要么就笨拙地断开连接。这两种办法都不好，这就是为什么 LIMIT 子句很重要的原因。
还有另外一种理解方式，当客户端从服务器提取数据的时候，它认为所有数据都是从服务器『拉』过来的，但实际情况是服务器在产生这些数据的同时就把它们『推』到客户端。客户端只需要接收推出来的数据，根本就没办法告诉服务器停止发送数据。
绝大多数连接 MySQL的类库能让你提取完整的结果后缓存到内存中，或者只提取需要的数据。默认的行为通常是提取所有的数据然后缓存。这很重要，因为 MySQL 只有在所有数据别提取之后才会释放掉所有的锁和资源。查询的状态会是『发送数据』（参阅『查询状态』）。如果客户端类库一次性提取了所有的数据，那么就可以减少服务器所做的工作，让服务器可以尽可能地完成所有的清理工作。
大部分客户端类库可以让使用者像直接从服务器上提取数据一样处理结果，但是它实际上只是在类库的内存中处理数据。这种机制在大多数时候都工作良好，但是对于很庞大的结果集也许会需要很长的的时间和很多内存。如果不缓存数据，那么就可以使用较少的内存，并且尽快开始工作。这么做的缺点就是在应用程序和类库交互的时候，服务器端的锁和资源都是被锁定的[注 5]。
下面是一个PHP 的例子，首先，是在 PHP 中使用 MySQL的惯用方式。
<?php$link = mysql_connect('localhost','user', 'password');$result = mysql_query('SELECT * FROM HUGE_TABLE',$link);while($row= mysql_fetch_array($result)) { // Do something with result}?>在 while 循环中，看上去好像只是在需要的时候才从数据库提取数据，但是实际上代码利用 mysql_query() 从缓存中提取数据。 while 循环只是在简单地遍历缓存。下面的代码不会缓存数据，它使用的是 mysql_unbuffered_query()：
<?php$link = mysql_connect('localhost','user', 'password');$result = mysql_unbuffered_query('SELECT * FROM HUGE_TABLE',$link);while($row= mysql_fetch_array($result)) { // Do something with result}?>编程语言有不同的方式来处理缓存。比如， Perl 的 DBD::mysql 驱动会要求定义 C 客户端库的 mysql_use_result 属性，默认值是 mysql_buffer_result，下面是一个实例：
#! /usr/bin/perluse DBI;my $dbh = DBI->connect('DBI:mysql:;host=localhost:','user', 'password');my $sth = $dbh->prepare('SELECT * FROM HUGE_TABLE', {mysql_use_result => 1});$sth->execute();while(my$row = $sth-> fetchrow_array()) { #Do something with result}主意，调用 prepare( ) 函数让结果不缓存。也可以在连接的时候定义这个参数，这样每条语句都是不缓存的。
my $dbh = DBI->connect('DBI:mysql:;mysql_user_result=1','user', 'password');查询状态
每个 MySQL 连接，或者叫线程（Thread），在任意一个给定的时间都有一个状态来标识正在进行的事情。有几种方法可以查看状态，但是最容易地是使用 SHOW FULL PROCESSLIST 命令（状态会出现在 Command 列）。当一个查询处于其生命周期中的时候，它的状态会改变多次。一共有 12 种状态。MySQL 手册定义了所有状态。
休眠 （Sleep）
查询（Query ）
锁定（Locked）
分析和统计（Analyzing and Statistics）
拷贝到磁盘上临时表（Copying to tmp table [on disk]）
排序结果（Sorting result）
发送数据（Sending Data）
知道基本状态是很有帮助的，这样就可以知道『球在谁手上』，在非常繁忙的服务器上，有可能看到不常见的状态，或者常见的简报状态（比如 statistics）占据了很长的时间。这通常意味着有问题发生。
查询缓存
在解析一个查询之前，如果开启了缓存，MySQL 会检查查询缓存，进行大小写敏感的哈希查找。即使查询和缓存中的查询只有一个字节的差异，也表示不匹配。查询就会进入到下一个状态。
如果 MySQL 在缓存中发现了一个匹配，那么在返回缓存之前,必须检查查询的权限。因为 MySQL 在缓存中存储了表达信息，所以这时可能根本不用解析查询。如果权限没有问题，那么 MySQL 就从缓存中提取数据，然后返回给客户端。这样就绕过了查询执行的所有步骤，不需要对查询进行解析、优化和执行。
第 5 章详细介绍了查询缓存。
查询优化过程
查询生命周期的下一步是把查询转变成执行计划。它有几个过程：解析、预处理和优化。错误（比如语法错误）在这个过程的任何一步都可能出现。
解析器和预处理器
首先，MySQL 解析器将查询分解成一个个标识（Token），然后构造一棵『解析树（Parse Tree）』。解析器使用 MySQL 的语法解析和验证查询。比如，它保证查询中的标志都是有效的，并且在适当的位置上。它也会检查其中的错误，比如字符串上面的引号没有闭合。
然后，预处理器（Preprocessor）检查解析器生成的结果树，解决解析器无法解析的语义。比如，它会检查表和列是否存在，它也会检查名字和列名，确保引用没有歧义。
最后，预处理器检查权限。这通常是很快的的过程，除非机器上有大量的权限（第 12 章有更多关于权限和安全的内容）
查询优化器
解析树现在已经通过了验证，并且准备让优化器把它变成执行计划。一个查询通常可以有很多执行方式，并且返回同样的结果，优化器的任务就是找到最好的方式。
MySQL 使用的是基于开销（Cost）的优化器，这意味着它会预测不同执行计划的开销，并且选择开销最小的一个。开销的单位是一次对大小为 4KB 的页面的随机读取。下面是一个使用 Last_query_cost 变量来了解查询开销的例子：
mysql> SELECTSQL_NO_CACHE COUNT(*)FROM sakila.film_actor;+----------+| COUNT(*) |+----------+| 5462 |+----------+1 row inset (0.00 sec)mysql> SHOW STATUS LIKE 'last_query_cost'';+-----------------+-------------+| Variable_name | Value |+-----------------+-------------+| Last_query_cost | 1127.199000 |+-----------------+-------------+结果表示优化认为这个查询会造成 1127 次随机读取。它来自对统计数据的估计，这些统计数据包括每个表或索引的页面数量、索引的基数性（Cardinality）、键和行的长度，以及键的分布。优化器不会考虑任何缓存因素，它认为每次读取都会有相同的 IO 开销。
由于种种原因，优化器并不总是选择最好的方案：
- 统计数量可能是错误的。服务器依赖于存储引擎提供的统计，它可能非常准确，也可能非常不准确，比如 InnoDB 存储引擎因为 MVCC 架构的缘故不会维护表行数的精确统计。
- 开销指标和运行查询的实际开销并不精确地相等。所以即使统计数据是准确的，查询的开销也可能和 MySQL 的估计不一致。一个读取更多页面的查询在某些情况下开销可能更小，比如读取磁盘上的顺序数据时 I/O 效率更高，或者数据已经被缓存在内存中。
- MySQL 的优化并总是和我们想的一样。我们有时候需要更快的执行时间，但是 MySQL 并不真正地理解『快』的含义，它只考虑开销。就像看到的那样，它对开销的预计也不是完全精确的。
- MySQL 不会考虑正在并发运行的其他查询，而并发查询会影响查询运行的速度。
- MySQL 并不总是根据开销来进行优化。有时候它仅仅遵从一些原则，比如『如果这儿有一个全文 MATCH( ) 子句，并且存在 FULLTEXT 索引，那么使用它』。即使使用不同的索引，或者使用带WHERE 子句的非 FULLTEXT 查询更快的情况下也是如此。
- 优化器不会考虑不受它控制的操作的开销，比如执行存储函数和用户定义的函数。
- 稍后会看到，优化器不会总是估算每一个可能的执行计划，所以它可能会错过优化方案。
MySQL 的优化器是相当复杂的，它使用了很多优化技巧把查询转换为执行计划。有两种基本的优化方案：静态优化和动态优化。静态优化可以简单地通过探测解析树（Parse Tree）来完成。比如，优化器可以通过运行代数化法则（Algebraic Rules）把 WHERE 子句转换成相等的形式。静态优化和值无关，比如 WHERE 子句中的常量，它可以被应用一次，然后始终都有效，即使有不同的参数重新执行查询也不会改变。可以把这个优化看成是『编译时优化』。
相反，动态优化根据上下文而定，和很多因素有关，比如 WHERE 子句中的值和索引中的行数。每次查询中执行的时候都会重新执行优化。可以把它看成『运行时优化』。
执行 MySQL 语句和存储过程的区别是很重要的。MySQL 只执行一次静态优化，然后每次运行查询时候都会进行动态优化。MySQL 有时甚至在执行的时候还会进行优化[注 6]。
下面是 MySQL 能够处理的一些优化类型：
对联接中的表重新排序
将外联接转换成内联接
代数等价法则
优化 COUNT( ) 、MIN( ) 和 MAX( )
同样地，没有 WHERE 子句的 COUNT( *) 通常也会被一些存储引擎优化掉（比如 MyISAM 总是保留着表行数的精确值）。
计算和减少常量表达式
也许会让人惊讶的是，一些查询语句在优化过程中也会被简化成产量。一个例子就是应用在索引上的 MIN( ) 函数。它可以被扩展成在主键或唯一索引上的常量查找。如果 WHERE 子句对索引使用了常量条件，那么优化器在查询开始的时候就可以查找它的值，然后在查询的剩余部分把它当成常量。下面是一个例子：
mysql> EXPLAIN SELECT film.film_id, film_actor.actor_idFROM sakila.film INNER JOIN sakila.film_actor USING(film_id) WHERE film.film_id = 1;+----+-------------+------------+-------+----------------+----------------+---------+-------+------+-------------+| id | select_type |table | type | possible_keys |key | key_len | ref |rows | Extra |+----+-------------+------------+-------+----------------+----------------+---------+-------+------+-------------+| 1 | SIMPLE | film | const |PRIMARY | PRIMARY | 2 | const | 1 | Usingindex || 1 | SIMPLE | film_actor | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 10 | Usingindex |+----+-------------+------------+-------+----------------+----------------+---------+-------+------+-------------+2 rowsin set (0.00 sec)MySQL 分两步执行该查询，它们分别对应结果中的两行。第 1 步是 film 表中找到需要的行。因为 film_id 列有主键，所以优化器在优化过程中就可以查询索引，然后知道要找的数据只有 1 行。因为优化器用一个已知的数据去进行查找，所以表的 ref 类型就是 const。
第 2 步中，因为优化器知道所有从第一步来的数据，所以它可以把第一步查找到的 film_id 列当成已知量。要注意， film_actor 表的 ref 类型也是 const ，和 film 表一样。
在 WHERE、USING、ON 这些强制值相等的子句中，常量具有传递性，这样是应用常量条件的一种情况。在本例中，优化器知道 USING 子句强制 film_id 在查询中的所有地方都是相等的，它必须等于 WHERE 子句中的常量值。
覆盖索引
子查询优化
早期终结
mysql> EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id = -1\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table:NULL type:NULLpossible_keys: NULL key:NULL key_len:NULL ref:NULL rows:NULL Extra: ImpossibleWHERE noticed after reading const tables这个查询在优化阶段就停止了。在某些情况下，MySQL 也能很快地停止查询。比如当执行引擎知道需要取得唯一的值，或者值不存在的时候，服务器就可以使用这种优化手段。比如，下面的查询试图查找一部没有演员的电影[注 7]：。
mysql> SELECTfilm.film_id FROMsakila.film LEFT OUTER JOIN sakila.film_actor USING(film_id)WHERE film_actor.film_idIS NULL;+---------+| film_id |+---------+| 257 || 323 || 803 |+---------+这个查询的策略是剔除有演员的电影。每部电影可能都有很多演员，但是只要发现有一名演员，它就立即停止处理这部电影，然后马上转向下一部，因为执行引擎知道 WHERE 子句不允许这部电影出现在结果中。类似的『不同值/不存在（DISTINCT/NOT-EXISTS）』优化方式可以应用到某些使用了 DISTINCT、NOT EXISTS( ) 和 LEFT JOIN的查询中。
相等传递
mysql> SELECTfilm.film_id FROMsakila.film INNERJOIN sakila.film_actor USING(film_id)WHERE film.film_id > 500;MySQL 知道 WHERE 子句不仅被应用到 film 表，也被应用到了 film_actor 表，因为 USING 子句强制两个表中的 film_id 列相等。
如果其它的数据库服务器不支持这种方式，那么就需要自己帮助优化器优化查询，那么 WHERE 子句就可能像下面这样：
WHERE film.film_id > 500 AND film_actor.film_id > 500MySQL 里面不需要这么做，这只会让查询更难维护。
比较 IN( ) 里面的数据
尽管优化器很明智，但它有时候也不能给出最佳答案。有时候你比优化器更了解数据，比如因为应用程序的逻辑，某些判断必定为真。同样，优化器有时候也没有需要的功能，比如哈希索引。再者，就像前面说说的那样，它选择的方案的开销也许比替代方案更高。
如果知道优化器没有给出好的结果，而且知道为什么，那么就可以帮助优化器做更多优化。可以把一些选项加到查询里面作为给优化器的提示，也可以重写查询，重新设计数据库架构或加上索引。
表和索引统计
回想一下图 1-1 中 MySQL 服务器架构的各种层次。服务器层有查询优化器，却没有保存数据和索引的统计数据。存储引擎负责统计这些数据，每个存储引擎都应该包含各种统计数据（也可能是以不同的方式把它们保存在一个存储引擎中）。诸如 Archive 这样的引擎根本就不保存任何数据。
因为服务器没有保存统计数据，所以 MySQL 优化器就不得不向存储引擎询问查询所使用的表的统计数据。存储引擎会向优化器提供每个表或索引的页面数量、表和索引的基数性、键和行的长度及键的分布信息。优化器可以使用这样信息来决定最佳的执行计划。
MySQL 的联接执行策略
MySQL 在执行过程中非常广泛地使用了『联接』这一术语。总的来说，它将每个查询都看成一个联接，这并不仅仅是指在两个表中查找匹配的查询，而是指每一个查询语句，包括子查询、甚或针对单个表的 SELECT 语句。这样说来，理解 MySQL 如何执行联接自然是显得非常的重要。
考虑一下联合（UNION）查询。MySQL 将 UNION 看成一系列的单个查询，它们将结果写入临时表中，最后最读取出来组成最终结果。在 MySQL 中，每个单个查询都是一个联接，所以从临时表读取数据实际也是联接。
MySQL 的联接执行策略很简单，它把每个联接看成一个嵌套循环，这意味着 MySQL 用一个循环从表中读取数据，然后再利用一个嵌套循环从下一个表中发现匹配数据。它不停地持续这个过程，当发现一行匹配的数据时，再根据 SELECT 子句中的列输出。接着它试着查找更多匹配的行，如果没有找到，就回溯到前一个表，用新的行开始下一轮迭代。如果前一个表中没有行了，它就会不停地回溯，直到有新的可用行为止。这时候，它按照上面的嵌套循环过程在下一个表中查找匹配数据，依次执行迭代[注 8].
查找行、探测下一个表、回溯的过程可以写成一个嵌套循环，因此可以把它叫做『嵌套循环联接』，下面是一个简单的例子：
mysql> SELECTtabl1.col1, tbl2.col2 ->FROM tb1 INNERJOIN tbl2 USING(col3) ->WHERE tbl1.col1 IN(5, 6);假设 MySQL 不会改变查询中表的顺序，那么 MySQL 执行查询的伪代码大致如下：
outer_iter = iterator over tbl1 where col1 IN (5, 6)outer_row = outer_iter.nextwhile outer_rowinner_iter = iterator over tbl2 where col3 = outer_row.col3inner_row = inner_iter.nextwhile inner_rowouter [outer_row.col1, inner_.col2]inner_row = inner_iter.nextendouter_row = outer_iter.nextend
这个执行计划适用于单表查询和多表查询，这也是为什么单表查询可以被看成联接的原因。单表联接是多表复杂联接的基础，它也能支持外联接（OUTER JOIN）。如果把查询改成下面这样：
mysql> SELECTtabl1.col1, tbl2.col2 ->FROM tb1 LEFTOUTER JOIN tbl2 USING(col3) ->WHERE tbl1.col1 IN(5, 6);相应的伪代码如下：
outer_iter = iterator over tbl1 where col1 IN (5, 6)outer_row = outer_iter.nextwhile outer_rowinner_iter = iterator over tbl2 where col3 = outer_row.col3inner_row = inner_iter.nextif inner_rowwhile inner_rowouter [outer_row.col1, inner_row.col2]inner_row = inner_iter.nextendelseoutput [outer_row.col1, NULL]endouter_row = outer_iter.nextend
另一种形象地显示执行计划的方式是『泳道图（Swin-Lane Diagram）』。如图 4-2 所示：它显示了上面采用内联接（INNER JOIN）的例子的执行计划。请按照从上到下、从左到右的顺序阅读『泳道图』。
图 4-2：『泳道图』显示了如何执行联接取得数据
从本质上说，MySQL 以同样的方式执行每一种查询。例如，在处理 FROM 子句的子查询时，它会先执行子查询，并且把结果反倒临时表[注 9]里面，然后把临时表当成普通表进行下一步处理（因而叫它『衍生表（Derived Table）』。MySQL 也使用临时表来处理联合（UNION），它还会把所有的右外联接（RIGHT OUTER JOIN）改写成等价的左外联接（LEFT OUTER JOIN），简言之，MySQL 把所有的查询都强行转换为联接来执行。
但是这种方式并是对所有的合法查询都有效。比如，全外联接（FULL OUTER JOIN）不能用嵌套循环，以及没发现匹配的数据时进行回溯的方式来执行，原因是检索的第一个表内可能根本没有匹配的数据。这也解释了 MySQL 为什么不支持全外联接。某些查询可以用嵌套循环的方式来执行，但是效率极差。后文会对其中一些情况进行解释。
执行计划
MySQL 不会产生字节码（Byte-code）来执行查询，这和其它许多数据库不一样。实际上，MySQL 执行计划是树形结构，目的是指导执行引擎产生结果。最终的计划中包含了足够的信息来重建查询。如果对某个查询使用 EXPLAIN EXTENDED 命令，并加上 SHOW WARNINGS 常数，就可以看到重建后的查询[注 10]。
从概念上说，任何多表查询都用树（Tree）来表示。例如，一个四表查询的执行计划可能如图 4-3 所示。
图 4-3：一种多表联接的方式
这是所谓的『平衡树（Balanced Tree）』。但 MySQL 不会按这样的方式来执行。在上一节已经说过，MySQL 总是从一个表开始，然后在下一个表中寻找匹配的行。因此，MySQL 的执行计划总是采用『左深度树（Left Deep Tree）』，如图 4-4 所示：
图 4-4：MySQL 进行多表联接的方式
联接优化器
MySQL 优化器中最重要的部分是联接优化器，它决定了多表查询的最佳执行顺序。通常可以用不同的顺序联接几个表，然后得到同样的结果。联接优化器评估不同执行计划的开销，并且选择开销最低的计划。
下面的查询可以用不同的顺序联接表，但结果都是一样的。
mysql> SELECTfilm.film_id, film.title, film.release_year, actor.actor_id,actor.first_name, actor.last_nameFROM sakila.film INNER JOIN sakila.film_actor USING(film_id) INNER JOIN sakila.actor USING(actor_id);这个查询可以有不同的执行。比如，可以从 film 表开始，使用 film_actor 表中 film_id 列的索引来查找 actor_id 的值，然后再检索 actor 表的主键以得到需要的数据。使用 EXPLAIN 看 MySQL 是如何执行这个查询：
mysql> EXPLAIN SELECT film.film_id, film.title, film.release_year, actor.actor_id,actor.first_name, actor.last_nameFROM sakila.film INNER JOIN sakila.film_actor USING(film_id) INNER JOIN sakila.actor USING(actor_id)\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type:ALLpossible_keys: PRIMARY key:NULL key_len:NULL ref:NULL rows: 953 Extra:*************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: refpossible_keys: PRIMARY,idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Usingindex*************************** 3. row *************************** id: 1 select_type: SIMPLE table: actor type: eq_refpossible_keys: PRIMARY key:PRIMARY key_len: 2 ref: sakila.film_actor.actor_id rows: 1 Extra:3 rowsin set (0.01 sec)这和预料的完全不一样。MySQL 从 actor 表开始（它位于 EXPLAIN 输出的第一行），执行的顺序和预料的相反。这样做地效率更高么？关键字 STRAIGHT_JOIN 强制按照查询中出现的顺序来进行联接操作。下面是 EXPLAIN 的输出：
mysql> EXPLAIN SELECT STRAIGHT_JOIN film.film_id, film.title, film.release_year, actor.actor_id,actor.first_name, actor.last_nameFROM sakila.film INNER JOIN sakila.film_actor USING(film_id) INNER JOIN sakila.actor USING(actor_id)\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type:ALLpossible_keys: PRIMARY key:NULL key_len:NULL ref:NULL rows: 953 Extra:*************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: refpossible_keys: PRIMARY,idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Usingindex*************************** 3. row *************************** id: 1 select_type: SIMPLE table: actor type: eq_refpossible_keys: PRIMARY key:PRIMARY key_len: 2 ref: sakila.film_actor.actor_id rows: 1 Extra:这说明了 MySQL为什么要颠倒联接顺序，因为这样可以减少第 1 个表中读取的行数[注 11]。在这两个例子中，执行引擎可以对第 2 个和第 3 个表进行快速索引查找。区别是它们会进行多少次索引查找：
- 将 film 表放在第一位， 对 film 表的的每一行都会读取 951 次 film_actor 表和 actor 表。
- 如果服务器首先扫描 actor 表，对后续的表就只有 200 次索引查找。
换句话说，颠倒联接顺序会减少回溯和重新读取。为了确认优化器的选择，实际执行这两个查询，然后查看last_query_cost 变量。联接顺序相反的查询的开销是241，而联接顺序不变的查询开销是 1154 次。
这只是一个简单的例子，用于说明MySQL联接优化器可以通过对重新排序，以得到较小的开销。对联接重新排序通常是一种非常有效的优化手段。重新排序有时不会得到优化的方案，这时就可以使用 STRAIGHE_JOIN 参数，并且按照认为最佳的方式的来组织联接的顺序，但是这种情况是很少见的。联接优化器比人更能准确地计算开销。
联接优化器试着产生最低开销的查询计划。在可能的时候，它会从单表计划开始，检查所有可能的子树的组合。
不幸的是，对于 n 个表联接，那么要检查组合的数量就是 n 的阶乘。这个数量被称为『搜索空间（Search Space）』，它增长得非常快。如果一个查询要联接 10 个表，那么检查的数量将是 3 628 800 种（10！ = 3 628 800）。当搜索空间非常巨大的时候，优化耗费的时间就会非常长，这时服务器就不会执行完整的分析。当表的数量超过 optimizer_search_depth 的值时，它就会走捷径，比如执行所谓的『贪婪』搜索。
MySQL 在多年的研究和实验中积累了很多经验，这通常有助于加速优化过程。这些经验是有好处的，但也意味着 MySQL 也许会因为没有检查所有的查询计划而错过优化方案（这种情况很少）。
有时查询不能被重新排序。联接优化器会利用这种客观情况，除掉一些选择，从而减小『搜索空间』。和关联查询一样，LEFT JOIN 也是一个很好的例子。其原因某张表的结果依赖于另一张表中取得的数据。这种依赖有助于联接优化器通过消除组合来减少搜索的空间。
排序优化
对结果进行排序可能会有较大开销，所以常常可以用不排序或只对较少的行排序来改变性能。
第 3 章阐述了如何利用索引来排序。当 MySQL 不能使用索引时，它就必须自己对结果进行排序。如果 MySQL 不能在内存中排序，它就会在磁盘上对数据进行分块，并且对每一块数据都使用快速排序算法，然后把所有块的数据合并到结果中。
有两种文件排序方法：
双路排序（Two Passes）—— 表算法
双路排序的开销可能会非常巨大，因为它会读取表两次，第二次读取引发了大量的随机 I/O。对于 MyISAM 表来说，这个操作的代价尤其高昂。MyISAM 表利用系统调用去提取每行的数据（它依赖于系统缓存来存储数据）。在另一方面，它在排序期间保存了最少的数据，所以如果被排序的行都在内存里面，那么它就可以在产生最终结果的时候保存和重新读取较少的数据。
单路排序（Single Pass）—— 新算法
MySQL可能会使用多于预料的临时存储空间，因为它为每一个排序的元组分配了固定的大小。这些记录可能会足够大，以保存最大的数据元组，包括每一个VARCHAR 列的最大长度，并且，如果使用 UTF-8 ，MySQL 将为每一个字符分配 3 个字节。这样某些优化很差的架构会占用比磁盘上表实际大小大得多的临时存储空间。
在排序联接的时间，MySQL 在执行查询的期间可能会分两步来执行文件排序。如果 ORDER BY 子句只引用了联接中的第一个表，MySQL 会先对该表进行排序，然后处理联接。如果是这种情况，那么 EXPLAIN 中的 Extra 列就会显示『使用文件排序（Using filesort）』。否则，MySQL 必须把结果保存到临时表中，然后对临时表进行排序。在这种情况下，EXPLAIN 在Extra 列中显示的是『使用临时表、使用文件排序（Using temporary, Using filesort）』。如果有 LIMIT 子句，它将在排序之后生效，所以临时表和文件排序可能会非常大。
参阅之后『优化 filesort』了解如何为文件进行服务器调优，以及如何改变服务器使用的排序算法。
查询执行引擎
解析和优化步骤生成了查询执行计划，MySQL 执行引擎使用它来处理查询。查询计划是一个数据结构，而不是其他数据库用于执行查询的可执行字节码。
和优化部分相反，执行部分通常不会很复杂。MySQL 简单地按照执行计划的指令进行查询，计划中的许多操作都是通过存储引擎提供的方法来完成的，这些方法叫『处理器 API（Handler API）』。查询中的每一个表都用一个处理器的实例代替。例如，一个表在查询中出现了 3 次，那么服务器就会创建 3 个处理器。尽管前文没有提及该内容，但是要知道处理器实例是在优化阶段早期产生的，优化器使用它们来获取一些表的信息，比如列名和索引统计。
存储引擎有许多功能，但是它仅仅需要 12 种或所谓的『积木（Buildingblock）』操作来执行大部分查询。比如，一个操作读取索引中的第 1 行，另一个操作读取第 2 行，这对于执行索引扫描的查询足够了。这种简单的执行方式让 MySQL 的执行引擎结构变得简易，但是也造成了一些优化器的限制。
提示：并不是所有的操作都是处理器操作，比如服务器管理表锁就不是。处理器可能会执行自己的低层次锁定，就像 InnoDB 处理行锁那样，但是这不会代替服务器自己的锁定实现。第 1 章已经说过，所有存储引擎共享的资源都在服务器内部实现，例如日期和时间函数、视图和触发器。
为了执行查询，服务器不停地重复指令，直到没有更多的行需要检查。
返回结果到客户端
执行查询的最后一步是发送结果到客户端。即使查询没有结果要返回，服务器也会对客户端的联接进行应答，比如有多少行受到了影响。
如果查询是可缓存的，MySQL 会在这时缓存查询。
服务器增量地产生和发送结果。回想一下 MySQL 的执行机制就可以知道，一旦它处理了一个表并且成功地产生了一行输出，它就会把这个结果发送到客户端。
这么做有两个好处：一是服务器不用把这一行保存在内存中，二是客户端可以尽快地开始工作[注 12]。
MySQL 查询优化器的限制
MySQL 中『每个查询都是一个嵌套循环联接』的方式并不是优化所有类型联接的最佳方法。幸运的是，MySQL 查询优化器只对很少的查询不使用，而这些查询都可以改写成更有效的方式。
提示：这部分的信息只是用于写本书时的 MySQL 版本，也就是 MySQL 5.1。一些限制在未来的版本中可能会被完全取消掉，还有一些限制已经被修复。尤其值得一提的是，MySQL 6 包含相当数量对子查询的优化，而且还有更多的优化正在进行中。
关联子查询（Correlated Subqueries）
MySQL 有时把子查询优化得很差。最差的就是在 WHERE 子句中使用IN。下面有个例子，它就会从 sakila 数据库的 sakila.film 表中找到所有包含女演员 Penelope Guiness （actor_id = 1）的电影。这自然会想到使用子查询：
mysql> SELECT* FROM sakila.film ->WHERE film_id IN( ->SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);按照设想，MySQL会由内向外地执行查询，先发现以系列的 film_id，然后替换掉 IN 里面的内容。通常 IN 列表都很快，所以我们期望这个查询被优化成下面这样：
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;-- Result:1, 23, 25…SELECT * FROM sakila.filmWHERE film_idIN(1, 23, 25, …);不幸的是，实际情况和这正好想法。MyQL试着让它和外面的表联系来『帮助』优化查询，它认为这样检索行为更有效率，所有查询会被重写成：
SELECT * FROM sakila.filmWHERE EXISTS( SELECT* FROM sakila.film_actor WHERE actor_id = 1 ANDfilm_actor.film_id = film.film_id);现在子查询从外部 film 表中提取 film_id ，它不能被最先执行。ExPLAIN 显示它是 DEPENDENT SUBQUERY。可以用 EXPLAIN EXTENDED 查看这个查询是如何被该写的。
mysql> EXPLAIN EXTENDEDSELECT * FROMsakila.film WHEREfilm_id IN(SELECT film_id FROM sakila.film_actor WHERE actor_id = 1)\G*************************** 1. row *************************** id: 1 select_type:PRIMARY table: film type:ALLpossible_keys: NULL key:NULL key_len:NULL ref:NULL rows: 953 filtered: 100.00 Extra: Usingwhere*************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: film_actor type: eq_refpossible_keys: PRIMARY,idx_fk_film_id key:PRIMARY key_len: 4 ref: const,func rows: 1 filtered: 100.00 Extra: Usingindexmysql> SHOW WARNINGS\G*************************** 1. row *************************** Level: Note Code: 1003Message: select`sakila`.`film`.`film_id` AS`film_id`,`sakila`.`film`.`title` AS`title`,`sakila`.`film`.`description` AS`description`,`sakila`.`film`.`release_year` AS`release_year`,`sakila`.`film`.`language_id` AS`language_id`,`sakila`.`film`.`original_language_id` AS `original_language_id`,`sakila`.`film`.`rental_duration`AS `rental_duration`,`sakila`.`film`.`rental_rate`AS `rental_rate`,`sakila`.`film`.`length`AS `length`,`sakila`.`film`.`replacement_cost`AS `replacement_cost`,`sakila`.`film`.`rating`AS `rating`,`sakila`.`film`.`special_features`AS `special_features`,`sakila`.`film`.`last_update`AS `last_update` from `sakila`.`film` where <in_optimizer>(`sakila`.`film`.`film_id`,<exists>(select1 from `sakila`.`film_actor` where ((`sakila`.`film_actor`.`actor_id` = 1) and(<cache>(`sakila`.`film`.`film_id`) = `sakila`.`film_actor`.`film_id`))))EXPLAIN 的输出表明 MySQL 将会对 film 表做全表扫描，并且对每一行执行一次子查询。对于较小的表，这不会对性能造成显著的影响，但是如果外部表很大，那么性能就会非常差。幸运的是，这个子查询可改写成联接的方式：
mysql> SELECTfilm.* FROM sakila.film ->INNER JOINsakila.film_actor USING(film_id)-> WHEREactor_id = 1;另外一种优化方式是手工产生 IN 列表，用 GROUP_CONCAT( ) 单独执行子查询。有时候这种方式比联接快。
MySQL 因为某些特定类型的子查询的执行方案受到了广泛的批评。尽管这肯定需要修复，但是批评往往混淆了两个不同的问题：执行顺序和缓存。从里面向外执行是一种优化的方式，缓存内部查询的结果是另外一种方式。自己重写查询可以兼顾这两方面。MySQL 的新版本应该会大幅度地优化这种查询。
什么时候选择关联子查询（When a correlated subquery is good）
MySQL 不会总是把关联子查询优化得很差。如果别人告诉你要避免子查询。不要听从这个意见。相反地，应该进行评测并且做出自己的决定。有时关联查询是一种可以得到结果的、极为合理的，甚至最优的方式。看下面例子：
mysql> EXPLAIN SELECT film_id, language_idFROM sakila.film ->WHERE NOTEXISTS( ->SELECT * FROMsakila.film_actor ->WHERE film_actor.film_id = film.film_id -> )\G*************************** 1. row *************************** id: 1 select_type:PRIMARY table: film type:indexpossible_keys: NULL key: idx_fk_language_id key_len: 1 ref:NULL rows: 953 Extra: Usingwhere; Using index*************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: film_actor type: refpossible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Usingindex对于这个查询的标准建议就是写一个左外联接（LEFT OUTER JOIN），而不是使用子查询。从理论上，MySQL 对这两者生成的执行计划应该是一样的，如下面所示：
mysql> EXPLAIN SELECT film.film_id, film.language_id ->FROM sakila.film ->LEFT OUTERJOIN sakila.film_actor USING(film_id) ->WHERE film_actor.film_idIS NULL\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type:indexpossible_keys: NULL key: idx_fk_language_id key_len: 1 ref:NULL rows: 953 Extra: Usingindex*************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: refpossible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: sakila.film.film_id rows: 2 Extra: Usingwhere; Using index; Notexis两个计划基本完全一致，但还是有一些区别：
一个查询中 film_actor 的 SELECT 类型是 DEPENDENT SUBQUERY，而另一个是 SIMPLE。这两种区别是查询语法的简单反映，因为第 1 个查询使用了子查询，而第 2 个没有。在实际处理上，它不会造成什么不同。
对于 film 表，第2 个查询在 Extra 列没有『Using Wher』。但是这也没有什么不同，第 2 个查询的 USING 子句 和 WHERE 完全是一样的
第 2 个查询在 film_actor 表的 Extra 列的值是『Not Exists』，这是本章开头提到的早期终结的一个例子。它意味着 MySQL 正在使用『不存在』这种优化手段，以避免在 film_actor 表这中 idx_fk_film_id 索引上读取过多的列。这和 NOT EXISTS 等价，因为它也是一旦发现匹配就立即停止处理当前的行。
所以，从理论上来说，MySQL 几乎用完全一样的执行计划来运行查询。那么在现实中就只能用基准测试来分辨真正地快慢。使用标准设置对这两个查询进行评测，结果列在表 4-1 中。
表 4-1：NOT EXISTS 和 LEFT OUTER JOIN 的对比
测试表明子查询要慢得多。
然而，事情并不总是这样。有时候子查询会快一些，例如查看某个表和另外一个表的记录相匹配的数据。尽管这听上去完全就是一个联接，但是并非总是如此。下面的联接查询的目的是找到一部有演员的电影，因为有些电影有多个演员，所有它会返回重复的值：
mysql> SELECTfilm.film_id FROMsakila.film-> INNERJOIN sakila.film_actor USING(film_id);这时候需要使用 DISTINCT 和 GROUP BY 来消除重复值：
mysql> SELECTDISTINCT film.film_id FROM sakila.film-> INNERJOIN sakila.film_actor USING(film_id);但是这个查询真正想表达的是什么呢？查询语句能很好地表达意图么？EXISTS 在逻辑上很好地表达了『有一个匹配』这个概念，它不会产生任何重复的行，也能够避免使用 GROUP BY 和 DISTINCT，这两个操作也许会需要临时表。下面用一个子查询来替换这个查询：
mysql> SELECTfilm.film_id FROMsakila.film ->WHERE EXISTS (SELECT* FROM sakila.film_actor ->WHERE film.film_id = film_actor.film_id);再对这两个查询运行测试，结果如表 4-2 中
表 4-2：EXISTS 和INNER JOIN
在这个例子中，子查询比联接快得多。
这个详细的例子有两个意图：对待子查询不能有绝对的态度，应该用测试来证明对查询计划和执行速度的假设。
联合的限制（Union limitation）
MySQL 有时不能把 UNION 外地一些条件『下推』到 UNION 的内部，而这些外部条件本来用于限制或者产生优化。
如果认为 UNION 内的每一个查询都可以从外部的 LIMIT 子句获益，或者认为对于某一个使用 ORDER BY，然后所有和它在一起的查询都可以使用这个条件，那么就错了，这时候应该把条件都添加到 UNION 内部的每一个子句上。例如，对两个巨大的表示使用 UNION，并且使用 LIMIT 子句得到前 20 条记录，MySQL 会把这两个表都放到临时表中，然后取前 20 条记录，可以通过把 LIMIT 放到每一个子查询上面避免来这种情况。
索引合并优化（Index merge optimization）
MySQL 5.0 中引入了索引合并算法，它让 MySQL可以在查询中对一个表使用多个索引。MySQL的早期版本只能使用一个索引，所以在单个索引不能完全满足 WHERE子句中的所有限制条件的时候，MySQL 通常会全表扫描。例如，film_actor 表在 film_id 和 actor_id 上都有索引，但是它们对下面这个查询都不是最好的选择：
mysql> SELECTfilm_id, actor_id FROMsakila.film_actor WHEREactor_id = 1 OR film_id = 1;在老版本的 MySQL 中，这个查询会进行全表扫描，除非把它写到一个 UNION 中。
mysql> SELECTfilm_id, actor_id FROMsakila.film_actor WHEREactor_id = 1 ->UNION ALL ->SELECT film_id, actor_idFROM sakila.film_actorWHERE film_id = 1AND actor <> 1;但是在 MySQL 5.0 及其以后的版本中，查询可以使用这两个索引，对它们同时扫描，并且合并结果。这种算法有 3 种变体，分别是：对 OR 取并集；对 AND 去交集；对 AND 和 OR 的组合取并集。下面的查询使用了两个索引扫描，可以从 EXPLAIN 的 Extra 列看到这一点：
mysql> EXPLAIN SELECT film_id, actor_idFROM sakila.film_actorWHERE actor_id = 1OR film_id = 1\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_actor type: index_mergepossible_keys: PRIMARY,idx_fk_film_id key:PRIMARY,idx_fk_film_id key_len: 2,2 ref:NULL rows: 29 Extra: Usingunion(PRIMARY,idx_fk_film_id); UsingwhereMySQL 可以对复杂的子句使用这种技巧，所有对有些查询来说，有可能在 Extra 列看到嵌套操作。这通常能很好地工作，但有时这种算法的缓冲、排序和合并操作使用了大量的 CPU 和内存资源。对于没有足够区分性的索引，并行扫描会返回大量合并操作的列，这种情况就更容易发生，优化器在意的只是随机页面读取，它并不考虑这种开销。这时候查询看上去开销较低，但实际比整表扫描还要慢。密集的内存和 CPU 使用也会影响并发的查询，但是在隔离的环境中运行查询的时候就不会观察到这种现象。这是另外一种需要真实性能测试的例子。
如果查询因为优化器的限制而运行得很慢，那么通过 IGNORE INDEX 命令禁止一些索引，或者使用老的 UNION 策略。
相等传递（Equality propagation）
相等传递可能会带来意想不到的开销。例如某个列上有一个很大的 IN 列表，优化器知道它会和另外的表中某些列相等，因为 WHERE、ON 和 USING 子句保证了它们之间必须相等。
优化器通过把相应的列拷贝到相关表里共享列表。这通常是有用的，因为它让优化器和执行引擎有更多的机会选择执行 IN 操作的时机。但是如果这个列表非常大，它就可能导致较慢的优化和执行。写本书的时候，MySQL 内部没办法来弥补这个缺陷，只能自己修改源代码。
并行执行 （Parallel execution）
MySQL 不能够在多个 CPU 上并行执行一个查询。其他一些数据库系统有这项特性，但是 MySQL没有。
哈希联接（Hash Join）
在写本书的时候，MySQL 还不能真正地执行哈希联接。所有的查询都是以嵌套联接的方式执行的。但是可以用哈希索引模拟哈希联接。如果不能用内存存储引擎，那么也就不得不模拟哈希索引。参阅『建立自己的哈希索引』。
松散索引扫描（Loose Index Scan）
MySQL 从来就不支持松散索引扫描，即扫描不不连续的索引。MySQL 索引扫描通常都需要一个确定的起点和终点，即使查询只需要其中一些不连续的行，MySQL 也会扫描起点到终点范围内的所有行。
下面这个例子有助于该问题，假设某个表在列（a,b ）上有索引，要执行的查询是：
mysql > SELECT… FROM tbl WHERE b BETWEEN 2 AND3;因为索引从 a 开始，但是 WHERE 子句中没有列 a ，MySQL 将会全表扫描并且去掉不匹配的行。如图 4-5 所示：
图 4-5：MySQL 通过整表扫描查找数据
很显然可以用较快的方式来执行该查询。索引的结果（但不是 MySQL 的存储引擎 API）可以查找到范围的开始，然后扫描直到结束，再然后进行回溯，跳到下一个范围进行扫描。如果 4-6 所示。
图 4-6：松散索引扫描，当前的 MySQL 还不支持这种更高效的方式
注意 WHERE 子句中没有列 a, 这是不需要的，因为它仅仅让给查询可以跳过不需要的列（MySQL 目前还未实现该功能）。
这肯定是一个很简单的例子，可以简单地通过添加一个不同的索引来优化这个问题。但是很多时候加索引来解决不了问题。下面这个例子就是一个查询在索引的第 1 列上有一个范围条件，而在第 2 个列上有一个相等条件。
在 MySQL 5.0 中，可以在某些有限的条件下使用松散索引扫描，例如在一个分组中找到最大和最小的值：
mysql> EXPLAIN SELECT actor_id, MAX(film_id) ->FROM sakila.film_actor ->GROUP BYactor_id\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_actor type: rangepossible_keys: NULL key:PRIMARY key_len: 2 ref:NULL rows: 408 Extra: Usingindex forgroup-byEXPLAIN 中『Using Index for group-by』显示查询使用了松散索引扫描。对于这个特定的查询，这是一种好的优化措施，但是它并不是通常意义上的松散索引扫描。所以叫它『松散索引探测』会更适合一些。
除非 MySQL 支持真正意义上的松散索引扫描，否则变通方案就是在索引的第一列提供一个常量或一系列的常量。
MIN( ) 和 MAX( )
MySQL 不能很好地优化 MIN( ) 和 MAX( ) 。下面是一个例子：
mysql> SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = ‘PENELOPE’;
因为在 first_name 上没有索引，所以查询会扫描整个表。从理论上说，如果 MySQL 扫描主键，它应该在发现第一个匹配之后就立即停止，因为主键是按照升序排序的，这意味着后续的行会有较大的 actor_id。但是，在这例子中，MySQL 会扫描整个表，可以通过分析（Profiling）查询证实这个结论。一个变通的方式就是去掉MIN( )，并且使用 LIMIT 来改写这个查询，就像下面这样：
mysql> SELECTactor_id FROM sakila.actor USE INDEX(PRIMARY) ->WHERE first_name ='PENELOPE' LIMIT 1;这个通用策略在 MySQL 试图扫描超过需要的行时能很好地工作。如果是完美主义者，可能认为这个查询背离了 SQL 的宗旨，但是我么应该明确地告诉服务器需要什么东西，而服务器也应该知道如何取得数据。因此，在这个例子中，我们告诉 MySQL 如何执行查询，那么与之对应的就是 MySQL 从查询中无法得知我们的真正目的是想得到一个最小值。确实是这样，但是有时候为了提高性能，我们不得不对原则进行一些让步。
对同一个表进行 SELECT 和UPDATE
MySQL 不会让你在同一个表进行 UPDATE 的同时进行 SELECT。实际上这不是优化器的限制。
但是知道 MySQL如何执行查询可以找到变通方案。下面是一个展示错误的例子，即使它是一个标准的 SQL 语句。这个查询的目的是找出表中相似行的数量，然后将数量更新到每一行：
mysql> UPDATEtbl AS outer_tbl ->SET cnt * ( -> SELECT count(*)FROM tbl ASinner_tbl -> WHERE innter_tbl.type = outer_tbl.type ->);ERROR 1093 (HY000): You can't specify target table 'outer_tbl'for updatein FROM clause一种变通的方式是衍生表。MySQL 把它当成临时表来处理。这样可以有效地处理两个查询，一是在子查询内部使用 SELECT，二是使用表和子查询的结果进行联接，然后进行更新。子查询在外部 UPDATE 打开表之前完成对表的操作，所以查询就可以成功。
mysql> UPDATEtbl INNSER JOIN ( ->SELECT type, count(*) AScnt ->FROM tbl ->GROUP BYtype -> )AS der USING(type) ->SET tbl.cnt = der.cnt;优化特定类型的查询
本节大部分内容跟 MySQL 版本有关。对未来版本，它们未必适用。
优化 COUNT
对聚合函数 COUNT 及如何优化使用 COUNT 的查询的误解，估计可以排到『MySQL 十大被误解主题 the top 10 most misunderstood topics in MySQL』的头名。
COUNT 会干什么（What COUNT( ) Does）
COUNT 是一个特殊的函数，它有两种不同工作方式：统计值的数量和统计行的数量。值是一个非空（Non-Null）的表达式（Null 意味着没有值）。如果在 COUNT( ) 的括号中定义了列名或其他表达式。COUNT 就会统计这个表达式有值的次数。很多人对这感到费解，部分原因是因为他们弄不清楚值和 NULL 的区别。
COUNT 的另外一种形式就是统计结果中行的数量。当 MySQL 知道括号中的表达式永远都不会为 NULL 的时候，它就会按这种方式工作。最明显的例子就是 COUNT(*)，它是 COUNT 的一种特例，正如我们所想的那样，它不会把通配符 * 展开成所有的列，而是忽略所有的列并统计行数。
一个最常见的错误就是在想统计行数的时候，在 COUNT 的括号中放入列名。如果想知道结果的行数，应该总是使用 COUNT(*)，这样可以清晰地说明意图，并且得到好的性能。
MyISAM 的迷思（Myths about MyISAM）
一个通常的误解就是 MyISAM 对于 COUNT 查询非常快。它确实很快，但是它只限于很特殊的情况，那就是有 WHERE 子句的 COUNT(*)，它仅仅是统计表中行的数量而已。MySQL 可以把这种情况优化掉，因为存储引擎总是知道表中行的数量。如果 MySQL 知道某列（col） 不可能为 NULL，那么它在内部也能把 COUNT(col) 转换为 COUNT(*)。
MyISAM 在查询有 WHERE 子句，或者统计值的时候，不会有魔幻般的优化速度。对于特定的查询，它可能比别的存储引擎快，但也可能慢。这依赖于很多因素。
简单优化（Simple Optimization）
有时可用利用 MyISAM 对 COUNT(*) 的优化对已经有索引的一小部分行做统计。下面的例子使用标准的 world 数据库，展示了如有效地查找 ID 大于 5 的城市。可把查询写成下面的形式：
mysql> SELECTCOUNT(*) FROMworld.City WHERE ID > 5;如果使用 SHOW STATUS 分析这个查询，就会发现它扫描了 4079 行。如果采用否定条件，从总数中减去 ID 小于 5 的城市，就可以把查询改写成这样：
mysql> SELECT(SELECT COUNT(*) FROMworld.City) - COUNT(*)FROM world.City WHERE ID <= 5;这个查询读取的行数更少，因为子查询在优化的时候变成了一个常量。在 EXPLAIN 中可看到：
mysql> EXPLAIN SELECT (SELECTCOUNT(*) FROMworld.City) - COUNT(*)FROM world.City WHERE ID <= 5;+----+-------------+-------+-------+---------------+-------------+---------+------+------+--------------------------+| id | select_type |table | type | possible_keys |key | key_len | ref |rows | Extra |+----+-------------+-------+-------+---------------+-------------+---------+------+------+--------------------------+| 1 | PRIMARY | City | range | PRIMARY | PRIMARY | 4 | NULL | 6 | Usingwhere; Using index || 2 | SUBQUERY | City |index | NULL | CountryCode | 3 | NULL| 4250 | Using index |+----+-------------+-------+-------+---------------+-------------+---------+------+------+--------------------------+有个问题经常在邮件列表和网络论坛里出现，那就是只适用一个查询统计同一列中不同值的数量，以减少查询的数量。例如，使用一个查询统计每种颜色的物品的具体数量，这不能使用 OR，比如（SELECT COUNT(color = ‘blue OR color = ‘red’) FROM items），这样不能区分每种颜色的数量，也不能把颜色放在 WHERE 子句中，比如 （SELECT COUNT(*) FROM items WHERE color = ‘red’ OR color = ‘blue’），因为颜色之间是互斥的。下面的查询可以解决这个问题：
mysql > SELECTSUM(IF(color = 'blue', 1, 0))AS blue, SUM(IF(color ='red', 1, 0)) AS red FROMitems;下面是另外一个等价的查询，但是没有 SUM， 而使用了 COUNT，并且保证了如果没有相应的颜色，表达式的值就为 NULL。
mysql> SELECTCOUNT(color = 'blue'OR NULL)AS blue, COUNT(color ='red' OR NULL) AS red-> FROMitems;更多复杂的优化
通常说来，使用了 COUNT 的查询很难优化，因为它们通常需要统计很多行（访问很多数据）。在 MySQL 内部优化它的唯一其他选择就是使用覆盖索引（参阅第 3 章）。如果这还不该，那么就需要更改应用程序架构，可以考虑使用汇总表（Summary Table）（参阅第 3 章），还可以利用外部缓存系统，比如数据库缓存服务器（Memcached）。在优化过程中，通常都会面临相似的窘境，那就只能在快速、简单、精确这 3 个特性中选择两个。
优化联接
值得强调的条目：
- 确保 ON 和 USING 使用的列上有索引（参阅『索引基础知识』）。在添加索引时要考虑联接的顺序。比如联接表 A 和 B 的时候使用了列C，并且优化器按照 B 到 A 的顺序联接，就不需要在表 B 上添加索引。没有使用的索引会带来额外的开销。通常说来，只需在联接中的第 2 个表上添加索引，除非因为其他的原因需要在第 1 个表上添加索引。
- 确保 GROUP BY 或 ORDER BY 只引用一个表中的列，这样，MySQL 可以尝试对这些操作使用索引。
- 要谨慎地升级 MySQL。因为在不同的版本中，联接的语法、运算符的优先级及其它行为会发生改变。过去曾经发生过一些意外的情况，比如一个普通的联接变成了一个叉积，原本等价的联接返回不同的值，甚至出现语法错误。
优化子查询
对于子查询最重要的建议就是尽可能地使用联接，至少在当前版本的 MySQL 中是这样。
子查询是优化器设计小组着力改进的部分，即将发布的 MySQL 版本也许会有更多的子查询优化。但是哪些优化会被最终发布，它们会造成多大的改变还未确定。现在的『联接优先』建议并不适用于以后的版本。
优化 GROUP BY 和 DISTINCT
在很多情况下，MySQL 对这两种方式的优化基本都是一样的。实际上，优化过程要求它们可以互相转化。这两种查询都可以从索引受益，通常来说，索引也是优化它们的最重要手段。
当不能使用索引时，MySQL 有两种优化 GROUP BY 的策略：使用临时表或文件排序进行分组。任何一组方式对于特定的查询都有可能是高效的。可以使用 SQL_SMALL_RESULT 强制 MySQL 选择临时表，或者使用 SQL_BIG_RESULT 强制它使用文件排序。
如果要对联接进行分组，那么通常对表的 ID 列进行分组会更加高效，例如下面的查询效率就不够高：
mysql> SELECTactor.first_name, actor.last_name, COUNT(*) ->FROM sakila.film_actor ->INNER JOINsakila.actor USING(actor_id) ->GROUP BYactor.first_name, actor.last_name;而下面的查询会更高：
mysql> SELECTactor.first_name , actor.last_name, COUNT(*) ->FROM sakila.film_actor ->INNER JOINsakila.actor USING(actor_id)-> GROUPBY film_actor.actor_id;按照 actor.actor_id 分组被 film_actor.actor 效率更高，可以通过对数据的分析或评测来证实这一点。
这个Chauncey利用了演员的姓名依赖于 actor_id 这一事实，所以它会返回同样的结果。但这并不意味着每次在 SELECT 中选择非分组的列都会得到同样的结果，可以通过配置 SQL_MODE 参数来禁止 SELECT 中使用未在 GROUP BY 中出现的列。如果根本不在意得到的值，或者知道每个分组中的数据都是不同的，那么就可以使用 MIN( ) 或 MAX( ) 绕过 SQL_MODE 的限制。就像下面这样：
mysql > SELECT MIN(actor.first_name), MAX(actor.last_name), …;
完美主义者会认为分组条件用错了，这种看法是正确的。虚假的 MIN( ) 或 MAX( ) 说明查询的结构有问题，但是有时候只想让 MySQL 尽可能快地执行查询。完美主义者会喜欢下面这个查询方案：
mysql> SELECTactor.first_name, actor.last_name, c.cnt ->FROM sakila.actor ->INNER JOIN( ->SELECT actor_id, COUNT(*) AScnt ->FROM sakila.film_actor ->GROUP BYactor_id-> ) ASc USING(actor_id);子查询会创建并填充临时表，有时这种方式带来的开销会比稍微变通一点的方案要高一些。要记住，子查询创建的临时表是没有索引的。
在一个分组查询中， SELECT 子句使用非分组的列通常都不是一个好主意，因为结果都不确定的，并且如果更改了索引或优化器采用了不同的策略，那么结果也可能被轻易地改变，大部分这样的查询就应该看成『事故』（服务器不会对这种查询发出警告信息），它们也可能是懒惰的结果，但是这肯定不是为了优化而故意设计的。最好可以显示地报告这种情况。建议在服务器的 SQL_MODE 参数中加上 ONLY_GROUP_BY，这样服务器就会对这种查询产生一个错误信息。
除非定义了 ORDER BY ，否则 MySQL 会自动对 GROUP BY 里面的列进行排序。如果不在意数据的顺序，可以使用 ORDER BY NULL 来跳过自动排序，也可以在 GROUP BY 后面加上 ASC 或者 DESC 来限定排序的类别。
mysql> EXPLAIN SELECT actor.first_name, actor.last_name,COUNT(*) FROMsakila.film_actor INNERJOIN sakila.actor USING(actor_id)GROUP BYactor.first_name, actor.last_name ORDERBY NULL\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type:ALLpossible_keys: PRIMARY key:NULL key_len:NULL ref:NULL rows: 200 Extra: Usingtemporary*************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: refpossible_keys: PRIMARY key:PRIMARY key_len: 2 ref: sakila.actor.actor_id rows: 14 Extra: Usingindex2 rowsin set (0.00 sec)加上 ORDER BY NULL，EXPLAIN 时Extra 列为『Using Temporary』，无时为『Using Temporary, Using filesort』。基准测试时，加与不加的 QPS 相差不已。
使用 ROLLUP 优化 GROUP BY
分组查询的一个变化就是要求 MySQL 在结果内部实现超级聚合（Super Aggregation）。可以在 GROUP BY 后面加上 WITH ROLLUP 来实现这个需求，但是它也许没有被很好地优化。可以使用解释器检查执行方法，确认分组是否已经通过文件排序或临时表完成，然后试着移除 WITH ROLLUP， 并且查看是否没有变化。
有时在应用程序里面进行超级聚合（Super Aggregation）会更好，尽管那意味着要从服务器提取更多列。也可以在 FROM 子句中使用子查询或临时表来保存中间结果。
最后的方式是把WITH ROLLUP 移到应用程序里面。
[root@localhost ~]# /usr/local/mysql/bin/mysqlslap --query=" SELECT actor.first_name, actor.last_name, COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY actor.first_name, actor.last_name" --number-of-queries=1000 --concurrency=10Benchmark Average number of seconds to run all queries: 25.465 seconds Minimum number of seconds to run all queries: 25.465 seconds Maximum number of seconds to run all queries: 25.465 seconds Number of clients running queries: 10 Average number of queries per client: 100[root@localhost ~]# /usr/local/mysql/bin/mysqlslap --query=" SELECT actor.first_name, actor.last_name, COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY actor.first_name, actor.last_name WITH ROLLUP" --number-of-queries=1000 --concurrency=10Benchmark Average number of seconds to run all queries: 8.779 seconds Minimum number of seconds to run all queries: 8.779 seconds Maximum number of seconds to run all queries: 8.779 seconds Number of clients running queries: 10 Average number of queries per client: 100上面是加上WITH ROLLUP 和没有时做的基准测试，从使用时间可看出，加上 WITH ROLLUP 时执行时间约为没加时的1/3。
EXPLAIN 这两个SQL，无 WITH ROLLUP 时使用了临时表。
mysql> EXPLAIN SELECT actor.first_name, actor.last_name,COUNT(*) FROMsakila.film_actor INNERJOIN sakila.actor USING(actor_id)GROUP BYactor.first_name, actor.last_name WITHROLLUP\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type:ALLpossible_keys: PRIMARY key:NULL key_len:NULL ref:NULL rows: 200 Extra: Using filesort*************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: refpossible_keys: PRIMARY key:PRIMARY key_len: 2 ref: sakila.actor.actor_id rows: 14 Extra: Usingindex2 rowsin set (0.00 sec)mysql> EXPLAIN SELECT actor.first_name, actor.last_name,COUNT(*) FROMsakila.film_actor INNERJOIN sakila.actor USING(actor_id)GROUP BYactor.first_name, actor.last_name\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type:ALLpossible_keys: PRIMARY key:NULL key_len:NULL ref:NULL rows: 200 Extra: Usingtemporary; Using filesort*************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: refpossible_keys: PRIMARY key:PRIMARY key_len: 2 ref: sakila.actor.actor_id rows: 14 Extra: Usingindex2 rowsin set (0.00 sec)优化 LIMIT 和 OFFSET
在分页系统中使用 LIMIT 和 OFFSET 是很常见的，它们通常也会和 ORDER BY 一起使用。索引对排序较有帮助，如果没有索引就需要大量文件排序。
一个常见的问题是偏移量较大，比如查询使用了 LIMIT 10000, 20 ，它就会产生 10020 行数据，并且丢掉前 10000 行。这个操作的代价非常高。假设所有页面的访问频率相等，平均每个查询扫描表的一半数据，为了这种查询，可以限制一个分页里访问的页面数目，或者让偏移量很大时查询效率更高。
一个提高效率的简单技巧就是在覆盖索引上进行偏移，而不是对全行数据进行偏移。可以将从覆盖索引上提取出来的数据和全行数据进行联接，然后取得需要的列。这会更有效率。看下面的查询：
mysql> SELECTfilm_id, description FROMsakila.film ORDERBY title LIMIT 50, 5;如果表非常大，这个查询最好写成下面这样：
mysql> SELECTfilm.film_id, film.description ->FROM sakila.film ->INNER JOIN( ->SELECT film_id FROM sakila.film ->ORDER BYtitle LIMIT 50, 5 -> )AS lim USING(film_id);这种方式效率更高，它让服务器在索引上面检查尽可能少的数据，一旦取得了需要的行，就把它们联接到完整的表上面，并取出其余的列。相似的技巧可以应用到有 LIMIT 子句的联接上面。
有时可以把 LIMIT 转换为为位置性查询，服务器可以以索引范围扫描的方式来执行。例如，如果预先计算并引领一个表示位置的列，那么就可以把查询写成下面这样：
mysql> SELECTfilm_id, description FROMsakila.film ->WHERE position BETWEEN 50 AND54 ORDER BY position;类似的问题还有对数据进行排名，但它往往和 GROUP BY 混合在一起，基本可以肯定的是需要预先计算和存储排名。
如果确实需要优化分页系统，也许应该利用预先计算好的汇总数据，作为替代方案，可以联接只含有 ORDER BY 子句需要的主键和列的冗余表。
优化 SQL_CALC_FOUND_ROWS
对于分页显示，另外一种常见的技巧就是对含有 LIMIT 的查询添加 SQL_CALC_FOUND_ROWS，这样就可以知道没有 LIMIT 的时候会返回多少行数据。这听上去好像是某种『魔法』，因为服务器预测了将会发现多少行数据。但不幸的是，服务器并不能真正地做到这一点。这个选项只是告诉服务器生成结果中不需要的部分，而不是在得到需要的数据后就立即停止。这个选项的代价很高。
一个较好的设计就是把页面调度放到『下一页』链接上。假设每页有 20 个结果，那么查询就应该 LIMIT 21 行数据，并且只显示 20 行。如果结果中有第 21 行，就会有下一页。
另外一种方法就是提取并缓存大量的数据，比如 1 000 行数据，然后从缓存中获取后续页面的数据。这种策略让应用程序知道一共有多少数据。如果结果少于 1 000 行，那么应用程序就知道有多少页；如果多于1 000 行，程序就可以显示『找到的结果多于 1 000 个』。这两种策略的效率都比重复产生完整的结果，然后丢弃绝大部分要高得多。
即使不能使用这两种策略，但可以使用覆盖索引，那么使用单独的 COUNT(*) 也比 SQL_CALC_FOUND_ROWS 快得多。
优化联合
MySQL 总是通过创建并填充临时表的方式执行 UNION，它不能对 UNION 进行太多的优化。
可能需要把 WHERE、LIMIT、ORDER BY 或其他条件手工地（比如将它们恰当地从外部查询拷贝到 UNION 的每个 SELECT 语句中）『下推』到 UNION 中，以帮助优化器优化它。
重要的是始终要使用 UNION ALL，除非需要服务器消除重复的行。如果忽略了 ALL 关键字，MySQL 就会向临时表添加 distinct 选项，它会利用所有行来决定数据的唯一行。这种操作开销很大，但是知道 ALL 不会删除临时表，MySQL 总是把结果放在临时表中，然后再把它们读取出来，即使在没有必要这么做（比如可以把数据直接返回给客户端）时也会如此。
查询优化提示
如果不满意 MySQL 优化器选择的优化方案，可以使用一些优化提示来控制优化器的行为。下面列出了一些提示，以及使用它们的好时机。可以将适当的将提示放入待修改的查询中，它只会影响当前查询。部分提示是和版本相关的，它们是：
HIGH_PRIORITY 和 LOW_PRIORITY
HIGH_PRIORITY 告诉 MySQL将一个 SELECT 语句放在其它语句的前面，以便它操纵数据。实际上，MySQL 将它放在队列的前面，而不是在队列中等待。也可以把它用于 INSERT 语句，这时它会取消服务器全局 LOW_PRIORITY 设定。
LOW_PRIORITY 正好相反。如果有其他语句需要访问数据，它就把当前语句放到队列的最后。它就像一个人很有礼貌地把住了旅馆的大门，只要有人等着，它自己就不会进门。可以将这个选项用于 SELECT、INSERT、UPDATE、REPLACE 和 DELETE。
这两个选项在有表锁的存储过程中有效，但是在 InnoDB 或其他有细粒度锁定，或者并发控制的存储引擎上无效。在 MyISAM 上要小心地使用它，因为它们会禁止并发插入，并且极大地降低性能。
HIGH_PRIORITY 和 LOW_PRIORITY 经常引起误解。它们并不是指在查询上分配较多或较少的资源，让查询工作得更好或不好。它们只是影响服务器对访问表的队列的处理。
DELAYED
STRAIGHT_JOIN
STRAIGHT_JOIIN 在 MySQL 没有选择好的联接顺序，或者当优化器花费很长时间确定联接顺序的时候很有用。在后一种情况下，线程将会在『统计（Statistics）』状态停留很长时间，添加这个提示将会减少优化器的搜索空间。
可以使用 EXPLAIN 查看优化器选择的联接顺序，然后按照顺序重写联接，并且加上 STRAIGHT_JOIN 提示。对于某些 WHERE 子句，如果认为固定顺序不会对性能有坏的影响，采用这种方式是好办法。但是，在升级 MySQL 之后最好重新检查一下这些联接，因为新的优化错误措施可能会因为这个提示而失败。
SQL_SMALL_RESULT 和 SQL_BIG_RESULT
SQL_BUFFER_RESULT
SQL_CACHE 和SQL_NO_CACHE
SQL_CALC_FOUND_ROWS
mysql> SELECTSQL_CALC_FOUND_ROWS id FROMuserinfo LIMIT 1;+----+| id |+----+| 1 |+----+mysql> SELECTFOUND_ROWS();+--------------+| FOUND_ROWS() |+--------------+| 100000 |+--------------+FOR UPDATE 和 LOCK IN SHARE MODE
INSERT … SELECT 查询不需要这两个提示。因为 MySQL 5.0 中要读取的行上默认就有读锁（可以禁止这种行为，但这并不好，具体原因请参阅第 8 章和第 11 章）。MySQL 5.1 在某些条件下会放松这种限制。
在写本书的时候，只有 InnoDB 支持这两个提示。现在还不清楚未来的其它存储引擎是否支持它们。在 InnoDB 上应用这两个提示时，要明白它们会禁止某些优化，比如覆盖索引。InnoDB 不能在不访问主键的情况下独占地锁定数据行，因为主键存储了数据行的版本信息。
USE INDEX、IGNORE INDEX 和 FORCE INDEX
FORCE INDEX 和 USE INDEX
在 MySQL 5.0 和新版本中，也有一些系统变量会影响优化器。
Optimizer_search_depth
Optimizer_prune_level
这两个提示控制了优化器在决定执行计划时的捷径。捷径对于复杂查询的性能是很重要的，但是它们也会因为效率的因素遗漏掉一些优化的方法。这就是为什么有时需要更改它们的原因。
用户自定义变量
用户自定义变量很容易被人遗忘，但它们是设计高效查询的利器。对某些过程化和关系化混合的逻辑，它们尤为有效。纯粹关系查询将任何数据都当成未排序的集合，并且一次性地操作它们。MySQL 采用了一种更加程序化的方式，这也许是一个弱点，但是如果知道如何利用它，这也是一种优势。用户自定义变量对程序化使用 MySQL 很有帮助。
可以把用户自定义变量看成是临时保留值的容器，只要和服务器的联接没有断开，它就是有效的。在定义它们的时候，可以使用 SET 或 SELECT 语句给他们赋值[注 13]。
mysql> SET@one := 1;mysql> SET@min_actor := (SELECTMIN(actor_id) FROMsakila.actor);mysql> SET@last_week := CURRENT_DATE- INTERVAL 1 WEEK;然后就可以在查询中使用：
mysql> SELECT… WHERE col <= @last_week;在了解它们的长处之前，先看看它们的特殊性和短处，这样就可以知道在什么时候不应是使用它们：
- 它们会禁止查询缓存。
- 不能在需要文字常量或标识的地方使用它们，例如表名、列名和 LIMIT 子句。
- 它们和联接相关，不能在跨联接通信中使用。
- 如果正在使用连接池或持久连接，它们会引起部分代码被隔离，从而无法交互。
- 在 MySQL 5.0 以前的版本中，它们是大小写敏感的。所以要注意兼容性问题。
- 不能显示地声明变量类型。决定变量是何种类型的时机在不同的 MySQL 版本中是不同的。最好的方式就是给变量显示地赋一个初始值，以决定其类型。如果需要整形，就赋 0， 浮点型则赋 0.0，字符串类型则赋 ‘（空字符串）。MySQL 用户自定义变量的类型是动态的，它在赋值的时候才会变化。
- 优化器有时可能会把变量优化掉，造成代码无法按照预定的想法工作。
- 赋值的顺序，甚至赋值的时机，都是不确定的，并且依赖于优化器选择的查询计划。最终结果可能会因此而让人非常困惑。
- := 运算符的优先级低于其他运算符，因此必须仔细地使用括号。
- 未定义的变量不会造成语法错误，所以很容易在不知情的情况下犯错。
变量的一个最重要的特性就是可以在变量赋值的同时使用新赋的值。换句话说，赋值是左值（L-VALUE）运算。下面的例子计算并同时输出了行号：
mysql> SET@rownum := 0;mysql> SELECTactor_id, @rownum := @rownum + 1 ASrownum ->FROM sakila.actor LIMIT 3;+----------+--------+| actor_id | rownum |+----------+--------+| 58 | 1 || 92 | 2 || 182 | 3 |+----------+--------+这并不是一个让人特定感兴趣的例子，因为它只复制了表的主键。但是可以把它用于排名。试着写一个查询，找出 10 名出演电影最多的男演员，另外还需要一个列表表示排名，如果出演的电影数量相同，那么名次也一样。先用一个查询找出男演员和它们出演的电影：
mysql> SELECTactor_id, COUNT(*)AS cnt ->FROM sakila.film_actor ->GROUP BYactor_id ->ORDER BYcnt DESC -> LIMIT 10;+----------+-----+| actor_id | cnt |+----------+-----+| 107 | 42 || 102 | 41 || 198 | 40 || 181 | 39 || 23 | 37 || 81 | 36 || 106 | 35 || 158 | 35 || 13 | 35 || 37 | 35 |+----------+-----+现在加上排名，出演了 35 部电影的演员排名应该相同。可以使用 3 个变量，一个变量可保存当前排名，一个变量保存前一个演员出演的电影数量，最后一个变量保存当前演员的电影数量。当电影数量变化的时候，排名也跟着改变：
mysql> SELECT@curr_cnt := 0, @prev_cnt := 0, @rank := 0;+----------------+----------------+------------+| @curr_cnt := 0 | @prev_cnt := 0 | @rank := 0 |+----------------+----------------+------------+| 0 | 0 | 0 |+----------------+----------------+------------+mysql> SELECTactor_id, -> @curr_cnt :=COUNT(*) AScnt, -> @rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank)AS rank, -> @prev_cnt := @curr_cntAS dummy ->FROM sakila.film_actor ->GROUP BYactor_id ->ORDER BYcnt DESC -> LIMIT 10;+----------+-----+------+-------+| actor_id | cnt | rank | dummy |+----------+-----+------+-------+| 107 | 42 | 0 | 0 || 102 | 41 | 0 | 0 || 198 | 40 | 0 | 0 || 181 | 39 | 0 | 0 || 23 | 37 | 0 | 0 || 81 | 36 | 0 | 0 || 144 | 35 | 0 | 0 || 106 | 35 | 0 | 0 || 60 | 35 | 0 | 0 || 13 | 35 | 0 | 0 |+----------+-----+------+-------+10 rowsin set (0.00 sec)排名全部都为零！
没有一个答案适合所有的情况。原因可能是简单的变量名拼写错误（这个例子没有），也可能是另外的因素。在这个例子中，EXPLAIN 表明生成了一个临时表，并且进行了文件排序，所以真正的原因是变量值计算的时间不同。
这个不可预知的问题在使用自定义变量的时候经常遇到。调试这种问题是很难的，但确实是值得的。比如将演员按照出演的电影数量进行排序，SQL 通常使用二次算法。但用户自定义变量可以达到线性算法，这是较大的进步。
针对这个例子，一个简单的解决办法就是加一个临时表，然后在 FROM 子句中使用子查询：
mysql> SELECTactor_id, -> @curr_cnt := cntAS cnt -> @rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank)AS rank, -> @prev_cnt := @curr_cntAS dummy ->FROM ( ->SELECT actor_id, COUNT(*) AScnt ->FROM sakila.film_actor ->GROUP BYactor_id ->ORDER BYcnt DESC -> LIMIT 10 -> )AS der;+----------+-----+------+-------+| actor_id | cnt | rank | dummy |+----------+-----+------+-------+| 107 | 42 | 1 | 42 || 102 | 41 | 2 | 41 || 198 | 40 | 3 | 40 || 181 | 39 | 4 | 39 || 23 | 37 | 5 | 37 || 81 | 36 | 6 | 36 || 106 | 35 | 7 | 35 || 158 | 35 | 7 | 35 || 13 | 35 | 7 | 35 || 37 | 35 | 7 | 35 |+----------+-----+------+-------+大部分关于自定义变量的问题都是由于在查询的不同阶段对变量进行赋值和读取造成的。比如，在 SELECT 语句中给它们赋值，但是在 WHERE 子句中对它们进行读取，这时它们的值就是不可预测的。下面的查询看上去可以返回一样，但实际却不会。
mysql> SELECT@rownum := 0;+--------------+| @rownum := 0 |+--------------+| 0 |+--------------+mysql> SELECTactor_id, @rownum := @rownum + 1 AScnt ->FROM sakila.actor ->WHERE @rownum <= 1;+----------+------+| actor_id | cnt |+----------+------+| 58 | 1 || 92 | 2 |+----------+------+这是因为 WHERE 和 SELECT 在查询的过程中是不同的阶段。如果加入 ORDER BY ，问题会变得更明显，因为 ORDER BY是另外一个阶段。
mysql> SELECT@rownum := 0;+--------------+| @rownum := 0 |+--------------+| 0 |+--------------+mysql> SELECTactor_id, @rownum := @rownum + 1 AScnt ->FROM sakila.actor ->WHERE @rownum <= 1 ->ORDER BYfirst_name;查询会返回表中的每一行，因为 GROUP BY 加入了文件排序并且 WHERE 子句是在文件排序之前计算的。解决的办法就是在查询执行的同一阶段对变量进行赋值和读取：
mysql> SELECTactor_id, first_name, @rownum ASrownum ->FROM sakila.actor ->WHERE @rownum <= 1 ->ORDER BYfirst_name, LEAST(0, @rownum := @rownum + 1);如果用户自定义变量的行为完全出乎预料，那么可以回到 EXPLAIN， 查看 Extra 列可能出现的 『使用 WHERE （Using Where）』，『使用临时表（Using Temporary）』或『使用文件排序（Using Filesort）』。它们会给答案。
最后一个例子引入了另外一种颇具特色的处理方式：在LEAST 函数中进行赋值。通过这种方式，变量的值被有效地掩盖了起来，并且不会破坏 ORDER BY 的结果（LEAST 函数总是返回 0）。如果只想对变量进行赋值，并且完全避免副作用，这种方式非常有用。它可以隐藏返回值并且避免访问多余的列，比如上个例子中的 dummy 列。GREATEST( )、LENGTH( )、ISNULL( )、NULLIF( )、COALESCE( ) 和 IF( ) 这些函数不管是单独使用，还是合起来使用，都能很好地达到这个目的，因为它们都有特殊性外，比如 COALESCE( ) 在发现参数有值的时候就立即停止执行。
不仅仅是 SELECT 语句，所有的语句中都可以对变量进行赋值。事实上，这是用户自定义变量最好的用处之一。比如可以用它来改写代价很高的查询。将使用子查询计算排名用变量进行改写后，代价就相当于执行一次单个的 UPDATE 语句。
但要得到想要的结果还是有点棘手。有时优化器会将变量当成运行时常量，并且拒绝执行赋值操作，这是将赋值放在 LEAST( ) 这样的函数中会比较有帮助。另外一个就是在执行前检查变量是否有确定的值。有时希望它有值，但是有时希望它没有值。
做一些小实验有助于了解自定义变量大所有有趣的特性，下面是实验方式：
- 计算总数和平均数。
- 对分组查询模拟 FIRST( ) 和 LAST( )。
- 对极大的数进行数学运算。
- 将整个表转换为一个 MD5 哈希值。
- 包装一个样值，当它超过某个边界时，对它进行解包。
- 模拟读写游标。123456789101112131415161718192021222324252627282930313233343536373839404142
mysql>SELECTfilm_id,COUNT(actor_id),MIN(actor_id),MAX(actor_id),-> (SELECTa.actor_id->FROMfilm_actor a->WHEREa.film_id = film_actor.film_idORDERBYa.actor_id LIMIT 1 )ASfirst_actor_id,-> (SELECTa.actor_id->FROMfilm_actor a->WHEREa.film_id = film_actor.film_idORDERBYa.actor_idDESCLIMIT 1)ASlast_actor_id->FROMfilm_actorGROUPBYfilm_id LIMIT 5;mysql> EXPLAINSELECTfilm_id,COUNT(actor_id),MIN(actor_id),MAX(actor_id), (SELECTa.actor_idFROMfilm_actor aWHEREa.film_id = film_actor.film_id LIMIT 1 )ASfirst_actor_id, (SELECTa.actor_idFROMfilm_actor aWHEREa.film_id = film_actor.film_id LIMIT 1)ASlast_actor_idFROMfilm_actorGROUPBYfilm_id LIMIT 5\G*************************** 1. row ***************************id: 1select_type:PRIMARYtable: film_actortype:indexpossible_keys:NULLkey: idx_fk_film_idkey_len: 2ref:NULLrows: 10Extra: Usingindex*************************** 2. row ***************************id: 3select_type: DEPENDENT SUBQUERYtable: atype: refpossible_keys: idx_fk_film_idkey: idx_fk_film_idkey_len: 2ref: funcrows: 2Extra: Usingindex*************************** 3. row ***************************id: 2select_type: DEPENDENT SUBQUERYtable: atype: refpossible_keys: idx_fk_film_idkey: idx_fk_film_idkey_len: 2ref: funcrows: 2Extra: Usingindex
谨慎地升级 MySQL
如前文所说，试图比 MySQL 更聪明不是好主意，这通常会造成更多的工作和维护开销，而且得到的益处很少。当升级 MySQL 时，这个问题特别突出，因为已有查询中的优化提示也许会禁用新的优化方案。
MySQL 处理索引的方式是不是一成不变的。新版 MySQL 会改变已有所有的使用方式，而且也许还要在新版本对索引进行调整。例如，MySQL 4.0 和更老的版本只能对查询使用的每个表使用一个索引，但是 MySQL 5.0 更新的版本可以使用索引合并。
除了 MySQL 偶尔会对优化器做大的变动之外，每次发布都会包含很多小的改变。这些改变通常影响一些小行为，例如不用考虑索引的某个条件，而且这些小的改变还会对 MySQL 优化更多的特殊情况。
尽管从理论上说，所有改动都是有益的，但是在实际情况中，一些查询在升级后性能反而会降低。如果已经对某个版本使用了较长时间，不管是否意识到，很可能已经对那个版本进行了某些调优。那些优化不一定适合新版本，而且有可能会造成性能降低。
如果在意高性能，那么就应该有适合工作负载的特定基础测试方案。这样就可以在对产品服务器进行升级之前在开发服务器上做一些评测。同样，在升级之前，应该仔细地阅读新版本的版本说明和已知缺陷的列表。
- mysql查询性能优化
- mysql性能优化(查询)
- MySQL查询性能优化
- mysql查询性能优化
- mysql查询性能优化
- [MySQL]--查询性能优化
- MySQL 查询性能优化
- 【mysql】查询性能优化
- mysql 查询性能优化
- MySql查询性能优化
- mysql查询性能优化
- Mysql性能优化-查询优化
- MySQL查询的性能优化
- MySQL查询的性能优化
- MySQL查询的性能优化
- MySQL查询的性能优化
- MySQL查询的性能优化
- MySQL查询的性能优化
- Linux下Apache中PHP无法解析终结办法
- iphone开发资源汇总
- 实现类似Instagram、qq中ListView每一项标题置顶的效果
- String path=new String(request.getParameter("excel").getBytes("ISO8859-1"),"gbk");不能传递中文？
- Nginx 命令启动停止
- mysql查询性能优化
- android raw读取超过1M文件的方法
- 关于.NET报表中图表控件线条颜色和粗细设置
- imagick API中文简介 ，php 环境
- Ubuntu10.10下编译Linux2.6.36内核
- s3c2410与s3c2440的部分细节区别整理
- 新浪微博开放平台开发总结
- 群里面的一道题
- usb 问题解决方法.txt
