1.优化特定类型的查询
1.1优化count()查询
count(),是一个特殊的函数，它可以统计列值得数量，也可以统计行数。在统计列值时要求列值不能为null。
当我们想要统计结果集的行数的时候直接使用count(*)，语义清晰，性能也更好。
在MyISAM引擎中在没有任何条where件下的count( *)的速度是非常快的，它无需计算行数，可以直接捕获这个值。但是当有where条件的时候就和其他引擎没有任何的区别了。
- 在MyISAM下的简单优化
例如，查询所有film_id大于5的数据行数。
explain select count(*) from film where film_id>5;
图片.png
此时扫面了30行数据。
EXPLAIN SELECT
(SELECT count(*) FROM film) - count(*)
FROM
film
WHERE
film_id <= 5;
图片.png
现在只扫描了6行！！！
- 统计同一列中不同值得数量
select count(color='red' or null) as red ,count(color='blue' or null)as blue from testcolor;
select sum(color='red') as red ,sum(color='blue')as blue from testcolor;
- 使用近似值
有时候我们统计数量的时候没有必要非常的精确，可以使用近似的值代替，但是却要比精确查询快很多。
1.2优化关联查询
- 确保在on或者using子句的列上有索引，在创建索引的时候需要考虑到关联的顺序，当表A和表B用列c关联的时候，如果优化器关联的顺序是B,A那么没有必要在B表对应的列上创建索引，一般来说，没有其他的理由，只需要在关联顺序中的第二个表上相应的列创建索引。
- 确保分组和排序中的表达式只涉及到一个表中的列。
1.3优化子查询
在mysql5.6版本之前尽量使用关联查询代替子查询，但是这并不是绝对的。
1.4优化limit
对于limit，应当尽量使用“延迟关联”，尽量扫描少的页。或者在程序中记录上一页，下一次分页的时候从记录开始。
1.5优化union
mysql总是通过创建并填充临时表的方式执行union查询，除非确实要消除重复的行，否则一定要使用union all ,否则mysql会给临时表加上distinct关键字，对临时数据做唯一性检查，这样的代价是非常高的。
- 特殊的union查询
一个union查询，第一个子查询先执行，第二个子查询后执行，如果第一个查询命中，则不执行第二个子查询，否则执行第二个查询。
select greatest(@found :=-1,id) as id ,'users' as which_tab1
from users where id=1
union all
select id from users_archived where id=1 and @found is NULL
union all
select 1 from dual where (@found :=null) is null;
1.6自定义变量
- 自定义变量做排名语句
select actor_id ,count(*) as cnt
from film
group by actor_id
order by cnt desc;
set @curr_cnt :=0,@prev_cnt :=0,@rank :=0;
select actor_id,
@curr_cnt :=cnt as cnt,
@rank :=if(@prev_cnt<>@curr_cnt ,@rank+1,@rank ) as rank,
@prev_cnt :=@curr_cnt as dummy
from (
select actor_id, count(*) as cnt from film
group by actor_id
order by cnt desc
)as der;
-避免重复查询刚刚更新数据
update t1 set lastUpdate=now() where id=1;
select lastUpdate from t1 where id =1;
改写
update t1 set lastUpdate=now() where id=1 and @now :=now();
select @now;
无需访问表就可以拿到数据会快很多。
但是在使用用户自定义变量的时候要注意取值的顺序。
