mysql查询优化之四：优化特定类型的查询

本文将介绍如何优化特定类型的查询。

1.优化count()查询
count()聚合函数，以及如何优化使用了该函数的查询，很可能是mysql中最容易被误解的前10个话题之一 

count() 是一个特殊的函数，有两种非常不同的作用。它可以统计某个列值的数量，也可以统计行数。
统计列值 要求列值是非空的。(不统计null，即null值计数为0)

count()的另一个用处是统计结果集的行数。当mysql确认括号的表达式值不可能为空时，实际上就是统计行数。最简单的就是当我们使用count(*)的时候，这种情况下统配符*并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

假如在同一个查询中统计同一个列不同值得数量

select sum(if(color = 'blue', 1, 0)) as BLUE, sum(if(color = 'red', 1, 0)) as RED from items

select count(color= 'blue' or null) blue, count(color= 'red' or null) red, from items

 

2.优化关联查询
这个话题讨论的比较频繁，这里需要特别提到的是：

2.1、确保on或者using子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。

　　　　当表A和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析）。

2.2、确保任何的group by 和order by 的表达式只涉及到一个表中的列。这样mysql才有可能使用索引来优化这个过程
2.3、当升级mysql的时候需要注意：关联语法，运算符优先级等其他可能会发生变化的地方。

示例说明：

SELECT A.xx,B.yy   
FROM A INNER JOIN B USING(c)  
WHERE A.xx IN (5,6)  

假设MySQL按照查询中的关联顺序A、B来进行关联操作，那么可以用下面的伪代码表示MySQL如何完成这个查询：

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);  
outer_row = outer_iterator.next;  
while(outer_row) {  
    inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;  
    inner_row = inner_iterator.next;  
    while(inner_row) {  
        output[inner_row.yy,outer_row.xx];  
        inner_row = inner_iterator.next;  
    }  
    outer_row = outer_iterator.next;  
}  

可以看到，最外层的查询是根据A.xx列来查询的，A.c上如果有索引的话，整个关联查询也不会使用。再看内层的查询，很明显B.c上如果有索引的话，能够加速查询，因此只需要在关联顺序中的第二张表的相应列上创建索引即可。

3.优化子查询
尽可能用关联代替。

4.优化group by 和 distinct
在很多场景下，mysql都使用同样的方法优化这两种查询，事实上，mysql优化器会在内部处理的时候相互转化这两类查询。他们都可以使用索引来优化，这也是最有效的优化方法。

优化 group by with rollup
分组查询的一个变种就是要求mysql对返回的分组结果再做一次超级聚合。你可以使用with rollup来实现这种优化。最好的办法是尽可能将with rollup 功能转移到应用程序处理。

distinct用group by优化

当数据量非常大，在同一个query中计算多个不相关列的distinct时，往往很容易出现数据倾斜现象，导致运行半天都不能得到结果。

比如以下的SQL语句（a, b, c没有相关性）：

select distinct(a), distinct(b), distinct(c) from tableName;

改进方案：把计算的distinct先group by，然后再计算：

select distinct(t.a), distinct(t.b), distinct(t.c) 
from(
    select a, b, c from tableName
    group by a, b, c;
)t;

原理：distinct是在reduce里面计算的，再进行merge，而group by的字段会在map这里分N路hash。group by越多，hash分的越随机。

group by中avg以及sum的处理方式：

select 
    count(distinct a) as a, count(distinct b) as b, 
    count(distinct c) as c, count(distinct d) as d,
    avg(e) as e, 
    sum(f) as f
from tablename;

处理方式如下：

5.优化limit和offset
在系统需要进行分页操作的时候，我们通常会使用LIMIT加OFFSET的办法来实现，同时加上合适order by子句。
如果有对应的索引，通常效率会不错，否则，mysql需要做大量的文件排序。

在OFFSET大的时候，查询会很糟糕。要优化这种查询，要么是在页面中限制分页的数量，要么优化大偏移量查询的性能。

优化此类分页查询最简单的一个方法是使用索引覆盖扫描，而不是查询所有的列。
然后根据一次关联操作再返回需要的列。在偏移量很大的时候，这样会大大提升效率：

mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
--优化后
mysql> SELECT film.film_id, film.description
　　-> FROM sakila.film
　　-> INNER JOIN (
　　-> SELECT film_id FROM sakila.film
　　-> ORDER BY title LIMIT 50, 5
　　-> ) AS lim USING(film_id);

这里的"延迟查询"将大大提升查询效率，它让mysql扫描尽可能少的页面，获取需要访问的记录后再
根据关联列回原表查询需要的列。这个技术也可以用于优化关联查询中的limit子句。

有时候也可以将limit查询转换为已知位置的查询，让mysql通过范围扫描获得到对应的结果。

6.优化 SQL_CALC_FOUND_ROWS
分页的时候，另一个常用的技巧是在limit语句中加上SQL_CALC_FOUND_ROWS提示(hint),这样就可以获取去掉limit以后满足条件的行数，因此可以作为分页的总数。看来，mysql做了非常"高深"的优化，像是通过某种方法预测了总行数。但实际上，mysql只有再扫描所有满足条件的行以后才知道行数，所以加上这个提示以后，不管是否需要，mysql都会扫描所有满足条件的行，然后再抛弃不需要的行，而不是在满足limit的行数后就终止扫描。所以该提示的代价可能非常高。

7.优化union查询
mysql总是通过创建并填充临时表的方式来执行union查询。因此很多优化策略在union查询中都没法很好的使用。经常需要手工地将where，limit，order by等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化。

除非确实需要服务器消除重复的行，否则一定要使用union all ，这一点很重要。
如果没有ALL关键字，mysql会给临时表加上distinct选项，这会导致对整个临时表的数据唯一检查。这样的代价非常高。即使有ALL关键字，mysql仍然会使用临时表存储结果。
事实上，mysql总是将结果放入临时表，然后再读出，再返回给客户端，很多时候这样做是没有必要的。

8.静态查询分析

9.使用用户自定义变量
set @one := 1