MYSQL优化

MySQL的万能嵌套循环并不是对每种查询都是最优的。不过MySQL查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让MySQL高效的完成工作

1 关联子查询

MySQL的子查询实现的非常糟糕。最糟糕的一类查询时where条件中包含in()的子查询语句。因为MySQL对in()列表中的选项有专门的优化策略，一般会认为MySQL会先执行子查询返回所有in()子句中查询的值。一般来说，in()列表查询速度很快，所以我们会以为sql会这样执行

select * from tast_user where id in (select id from user where name like '王%');
我们以为这个sql会解析成下面的形式
select * from tast_user where id in (1,2,3,4,5);
实际上MySQL是这样解析的
select * from tast_user where exists 
(select id from user where name like '王%' and tast_user.id = user.id);

MySQL会将相关的外层表压缩到子查询中，它认为这样可以更高效的查找到数据行。

这时候由于子查询用到了外部表中的id字段所以子查询无法先执行。通过explin可以看到，MySQL先选择对tast_user表进行全表扫描，然后根据返回的id逐个执行子查询。如果外层是一个很大的表，那么这个查询的性能会非常糟糕。当然我们可以优化这个表的写法：

select tast_user.* from tast_user inner join user using(tast_user.id) where user.name like '王%'

另一个优化的办法就是使用group_concat()在in中构造一个由逗号分隔的列表。有时这比上面使用关联改写更快。因为使用in()加子查询，性能通常会非常糟糕。所以通常建议使用exists()等效的改写查询来获取更好的效率。

2 UNION的限制

有时，MySQL无法将限制条件从外层下推导内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先排好序在合并结果集的话，就需要在union的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后在取前20条，那么MySQL会将两个表都存放到一个临时表中，然后在去除前20行。

(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;

这条查询会将actor中的记录和customer表中的记录全部取出来放在一个临时表中，然后在取前20条，可以通过在两个子查询中分别加上一个limit 20来减少临时表中的数据。
现在中间的临时表只会包含40条记录了，处于性能考虑之外，这里还需要注意一点：从临时表中取出数据的顺序并不是一定，所以如果想获得正确的顺序，还需要在加上一个全局的order by操作

3 索引合并优化

前面文章中已经提到过，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

4 等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的in()列表，而MySQL优化器发现存在where/on或using的子句，将这个列表的值和另一个表的某个列相关联。
那么优化器会将in()列表都赋值应用到关联的各个表中。通常，因为各个表新增了过滤条件，优化器可以更高效的从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢。

5 并行执行

MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库鞥能够提供这个特性，但MySQL做不到。这里特别指出是想提醒大家不要花时间去尝试寻找并行执行查询的方法。

6 最大值和最小值优化

对于MIN()和MAX()查询，MySQL的优化做的并不好，例：

select min(actor_id) from actor where first_name = 'wang'

因为在first_name字段上并没有索引，因此MySQL将会进行一次全表扫描。如果MySQL能够进行主键扫描，那么理论上，当MySQL读到第一个太满足条件的记录的时候就是我们需要的最小值了，因为主键是严哥按照actor_id字段的大小排序的。但是MySSQL这时只会做全表扫描，我们可以通过show status的全表扫描计数器来验证这一点。一个区县优化办法就是移除min()函数，然后使用limit 1来查询。

这个策略可以让MySQL扫描尽可能少的记录数。这个例子告诉我们有时候为了获得更高的性能，就得放弃一些原则。

7 在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新。这并不是优化器的限制，如果清楚MySQL是如何执行查询的，就可以避免这种情况。例：

update table set cnt = (select count(*) from table as tb where tb.type = table.type);

这个sql虽然符合标准单无法执行，我们可以通过使用生成表的形式绕过上面的限制，因为MySQL只会把这个表当做一个临时表来处理。

    update table inner join
    (select type,count(*) as cnt from table group by type) as tb using(type) 
    set table.cnt = tb.cnt;

实际上这执行了两个查询：一个是子查询中的select语句，另一个是夺标关联update，只是关联的表时一个临时表。子查询会在update语句打开表之前就完成，所以会正常执行。