在《mysql查询优化之一：mysql查询优化常用方式》一文中列出了一些优化器常用的优化手段。查询优化器在提供这些特性的同时，也存在一定的局限性，这些局限性往往会随着MySQL版本的升级而得到改善，所以本文会列出一些常见的局限性，且不包含所有的。
MySQL的万能"嵌套循环"并不是对每种查询都是最优的。不过还好，mysql查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让mysql高效的完成工作。
在这我们先来看看mysql优化器有哪些局限性：
1.关联子查询
mysql的子查询实现得非常糟糕。最糟糕得一类查询是where条件中包含in()的子查询语句。
例如，我们希望找到sakila数据库中，演员Penlope Guiness参演的所有影片信息。
很自然的，我们会按照下面的方式用子查询实现：
select * from sakila.film
where film_id in (
select film_id from sakila.film_actor where actor_id = 1
)
你很容易认为mysql应该由内而外的去执行这个查询，通过子查询中的条件先找出所匹配的film_id。所以你看你会认为这个查询可能会是这样：
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
不幸的是，事实恰恰相反。MYSQL想通过外部的关联条件用来快速的筛选子查询，它可能认为这会让子查询更效率。mysql会这样重写查询：
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
这样的话，子查询将会依赖外部表的数据，而不会被优先执行。
mysql将会全表扫描film表，然后循环执行子查询。在外表很小的情况下，不会有什么问题，但在外表很大的情况下，性能将会非常差。幸运的是，很容易用关联查询来重写。
mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
其他的好的优化方法是用group_concat手工生成in()的列表。有时甚至会比JOIN查询更快。总之，虽然in()子查询在很多情况下工作不佳，但exist()或者其他等价的子查询有时也工作的不错。
关联子查询性能并不是一直都很差的。
子查询 VS 关联查询
--关联子查询
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 ，这个在执行计划中也一样，一旦匹配到一行
数据，就立刻停止扫描。
测试结果为：
查询 每秒查询数结果(QRS)
NOT EXISTS 子查询 360
LEFT OUTER JOIN 425
这里显示使用子查询会略慢些。
另一个例子：
不过每个具体地案例会各有不同，有时候子查询写法也会快些。例如，当返回结果只有一个表的某些列的时候。
听起来，这种情况对于关联查询效率也会很好。具体情况具体分析，例如下面的关联，我们希望返回所有包含同一个演员参演的电影
因为电影会有很多演员参演，所以可能返回一些重复的记录。
mysql-> select film.film_id from sakila.film
inner join sakila.film_actor using (film_id)
我们需要用distinct 和 group by 来移除重复的记录
mysql-> select distinct film.film_id from sakila.film
inner join sakila.film_actor using (film_id)
但是,回头看看这个查询，到底这个查询返回的结果意义是什么？至少这样的写法会让sql的意义不明显。
如果是有exists 则很容易表达"包含同一个参演演员"的逻辑。而且不需要使用 distinct 和 Group by，也不会有重复的结果集。
我们知道一旦使用了 distinct 和 group by 那么在查询的执行过程中，通常需要产生临时中间表。
mysql -> select film_id from sakila.film_actor
where exists(select * from sakila.film_actor
where film.film_id = film_actor.film_id)
测试结果为：
查询 每秒查询数结果(QRS)
INNER JOIN 185
EXISTS 子查询 325
这里显示使用子查询会略快些。
通过上面这个详细的案例，主要想说明两点：
一是不需要听取那些关于子查询的 "绝对真理"，(即别用使用子查询)
二是应该用测试来验证子查询的执行疾患和响应时间的假设。
2.union的限制
有时，mysql无法将限制条件从外层"下推"到内层，这使得一些可以限制结果集和附加的优化都无法运行。
如果你想任何单独的查询都可以从一个limit获益，或者你想order by也是基于所有子查询一次结合，则你需要在每个子查询加上相应的子语句。
例如：
(SELECT first_name, last_name
FROM sakila.actor
ORDER BY last_name)
UNION ALL
(SELECT first_name, last_name
FROM sakila.customer
ORDER BY last_name)
LIMIT 20;
这个查询将会保存200行从actor查出来的数据和customer表的599行数据，然后放入一个临时表，然后选取靠前的20条数据。
你可以通过在每个查询都加上limit 20 来预防这个情况。
如下：
(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;
这样只会查出40条数据了，大大提升了查询效率。
3.索引合并优化
4.等值传递
有时候等值传递也会造成很大的性能消耗。例如，有一个非常大的In()列表，而mysql优化器发现存在WHERE、ON或者USING的子句，将这个列表的值和另一个表的某个列关联。
那么优化器会将IN()列表都复制应用到关联的各个表中。通常因为各个表新增了过滤条件，优化器可以更高效地从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变得很慢。
5.并行执行
mysql不能并行执行一个单独的查询在不同的cpu.可能其他数据库会提供这个特性，但mysql没有提供。
我们提及这个就是希望你们不要花时间去弄怎么在mysql配置并行查询。
6.哈希关联
mysql并不是完全支持哈希关联，大部分关联都是嵌套循环关联。不过可以通过建立一个哈希索引来曲线地实现哈希关联。
7.松散索引扫描
由于历史原因，mysql并不支持松散索引扫描（相当于oracle中的跳跃索引扫描），也就无法按照不连续的方式扫描一个索引。通常，mysql的索引扫描需要定义一个起点和一个重点，即使需要的数据只是这段索引中很少数的几个，mysql仍需要扫描这段索引中的每个条目。
8.最大值和最小值优化
mysql> SELECT MIN(actor_id) FROM sakila.actor WHERE first_name='PENELOPE'
mysql> SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
-> WHERE first_name = 'PENELOPE' limit 1;
9.在同一个表查询和更新
mysql不容许对同一表同时进行查询和更新。这其实并不是优化器的限制，如果清楚mysql是如何执行查询的，就可以避免这种情况。下面是一个无法运行的sql，虽然这是一个符合标注的sql语句。这个sql语句尝试将两个表中相似的行的数量记录到字段cnt中
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只会把这个表当做一个临时表处理。实际上，
这执行了两个查询：一个是子查询的select语句，另一个是多表关联update，只是关联的表是一个临时表。
子查询会在update语句打开表之前就完成，所以下面的查询将正常运行。
mysql> UPDATE tbl
-> INNER JOIN(
-> SELECT type, count(*) AS cnt
-> FROM tbl
-> GROUP BY type
-> ) AS der USING(type)
-> SET tbl.cnt = der.cnt;