Mysql查询优化器的局限性

Mysql的万能“嵌套循环”并不是对每种查询都是最优的，对少部分查询不适用。所以需要我们通过改写Mysql高效的完成工作。且在Mysql5.6版本正式发布后，会消除很多Mysql原本的限制，让更多查询能够尽可能高效的完成。

1、关联子查询

Mysql的子查询实现非常糟糕，特别是where条件中有in()的嵌套子查询，如：

select * from film where film_id in (
    select film_id from dilm_actor where actor_id = 1)

前面的文章我们说过，mysql对in()子句做了专门的优化，一般会认为Mysql会先执行子查询返回需要的film_id，类似于这样的查询：select * from film where film_id in (1,2,3,4,...);   但实际上Mysql的操作是-----先将相关的外层表压到子查询中，它认为这样可以更高效的查找到数据行，也就是下面的查询语句：

select * from film where exists (
    select * from film_actor where actor_id = 1 
    and film_actor.film_id = film.film_id)

这时，子查询需要根据film_id来关联外部表film，mysql无法先执行子查询。通过EXPLAIN我们可以看到子查询是一个相关子查询：DEPENDENT SUBQUERY。可以使用explain extended 来查看这个查询被优化成什么样。

对于这种查询我们可以用内连接来做优化，或者使用GROUP_CONCAT() 函数在in()中构造一个由逗号分隔的列表（在结果优先的情况下效率较高）。

但是并非所有的关联子查询性能都会差，有些时候关联子查询是一种非常合理，且性能最好的写法。如下面的语句：

mysql> explain select film_id, language_id from sakila.film
　　　　where not exsits (
　　　　　　select * from sakila.film_actor
　　　　　　where film_actor.film_id = film.film_id
　　　　)

执行计划结果为：

********************* 1. row ***********************************
id : 1
select_type: PRIMARY
table: film
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 951
Extra: Using where

********************* 2. row ***********************************
id : 2
select_type: Dependent subquery
table: film_actor
type: ref
possible_keys: idx_fx_film_id
key: idx_fx_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where;Using index

使用左外连接重写该查询后：

mysql> explain select film.film_id, film.language_id from sakila.film
　　　　left outer join sakila.film_actor using(film_id)
　　　　where film_actor.film_id is null

执行计划为：

********************* 1. row ***********************************
id : 1
select_type: simple
table: film
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 951
Extra:

********************* 2. row ***********************************
id : 1
select_type: simple
table: film_actor
type: ref
possible_keys: idx_fx_film_id
key: idx_fx_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where;Using index;not exists;

通过对比可以发现，两次查询没有太多区别：

1. 表 film_actor的访问类型一个是Dependent subquery 另一是simple，这对底层存储引擎接口来说，没有任何不同；
2. 对 film表 第二个查询没有using where，但这不重要。using子句和where子句实际上是完全一样的。
3. 第二个表film_actor的执行计划的Extra 有 "Not exists" 这是我们先前提到的提前终止算法，mysql通过not exits优化来避免在表film_actor的索引中读取任何额外的行。这完全等效于直接使用 not exist ，这个在执行计划中也一样，一旦匹配到一行
数据，就立刻停止扫描。

这里主要想说明两点：一是不要听取那些关于子查询的绝对真理；二是应该用测试来验证子查询的执行计划和响应时间的假设。

2、UNION限制

Mysql有时不能将限制条件从外层“下推”到内层，使得原本能够限制的部分返回结果的条件无法应用到内层查询优化上。

UNION各个子句的查询，如果希望使用limit取部分结果集，或者先排好序在进行合并，那么只能在limit子句来完成，从UNION最外层的limit和order by 对子句没有任何意义。而在正常的UNION查询应用中，子句中的limit 和 order by 对最终的结果意义不大。下面是一组UNION查询的示例：

(SELECT first_name, last_name
　　FROM sakila.actor
　　ORDER BY last_name
　　LIMIT 20)
UNION ALL
(SELECT first_name, last_name
　　FROM sakila.customer
　　ORDER BY last_name
　　LIMIT 20)
LIMIT 20;

UNION的子查询结果会存放在一个临时表中，我们对子句加上limit限制，可以减少临时表中的数据，子句中的order by 也只是对当前的子查询有意义，如果我们想对最后的查询结果进行排序，需要加一个全局的order  by语句，和上面示例中最后一个limit子句一样。

3、索引合并优化

在mysql5.0以后的版本中，当where 子句中包含多个复杂条件的时候，Mysql能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

4、等值传递

等值传递是Mysql优化器的一个特性之一，但是有时候等值传递会带来一些意想不到的额外消耗，如有一个非常大的IN() 列表，而Mysql优化器发现存在where、on、using 的子句，将这个列的值与另一个表的某一列关联。这时优化器会将IN() 列表都复制应用到关联的各个表中。一般各个表新增了过滤条件，优化器可以更高效的从存储引擎过滤，但如果IN() 的列表过大，会导致优化和执行都变慢。

5、并行执行

Mysql无法利用多核特性来并行执行查询，很多其他类型的关系型数据库都能够提供这个特性，但mysql做不到，所以不要在mysql上尝试并行执行。

6、哈希关联

Mysql的所有关联都是嵌套循环关联的，它不支持哈希关联。但是我们可以通过建立哈希索引来实现哈希关联。如果使用的是Memory存储引擎，则索引都是哈希索引，关联的时候也类似于哈希关联。

7、松散索引扫描

由于历史原因，mysql并不支持松散索引扫描（相当于oracle中的跳跃索引扫描），也就无法按照不连续的方式扫描一个索引。通常，mysql的索引扫描需要定义一个起点和一个重点，即使需要的数据只是这段索引中很少数的几个，mysql仍需要扫描这段索引中的每个条目。

8、最大值、最小值优化

对于min()和max()查询，mysql的优化做的并不好。如：

mysql> SELECT MIN(actor_id) FROM sakila.actor WHERE first_name='PENELOPE'

first_name字段上没有索引，mysql将会进行一次全表扫描。如果mysql能够进行主键扫描，那么理论上，当mysql读到第一个满足条件的记录的时候就是我们需要找的最小值了，因为主键是严格按照actor_id字段的大小顺序排序的。但是mysql这时只会做全表扫描。一个曲线的优化方法是移除min()，然后使用limit来将查询重写如下：

mysql> SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = 'PENELOPE' limit 1;

这个策略可以让mysql扫描尽可能少的数据记录。

9、同一张表的查询和更新

Mysql不允许在对同一张表进行查询和更新。如下面的SQL就无法执行：

mysql> UPDATE tbl AS outer_tbl
　　-> SET cnt = (
　　-> 　　SELECT count(*) FROM tbl AS inner_tbl
　　-> 　　WHERE inner_tbl.type = outer_tbl.type
　　-> );
　　ERROR 1093 (HY000): You can’t specify target table 'outer_tbl' for update in FROM clause

我们可以通过生成表的形式绕过上面的限制，因为mysql只会把这个表当做一个临时表来处理。SQL语句如下：

mysql> UPDATE tbl 
    ->INNER JOIN( SELECT type, count(*) AS cnt FROM tbl GROUP BY type
　　 ->         ) AS der USING(type)
　　 -> SET tbl.cnt = der.cnt;

上面的SQL执行了两个查询：一个是子查询中的SELECT语句，另一个是多表关联UPDATE语句，只是关联的表是一个临时表。子查询会在update语句打开表之前完成