mysql优化特定类型的查询

1.优化count()查询

count()是特殊的函数，有两种不同的作用，一种是某个列值的数量，也可以统计行数

总有人认为myisam的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的

实际上count（1）与count（*）与count（主键）效率是一样的

1.2使用近似值

在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如可以使用explain来获取近似的值
其实在很多OLAP的应用中，需要计算某一个列值的基数，有一个计算近似值的算法叫hyperloglog。

1.3更复杂的优化

一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统。

2.优化关联查询

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

当表A和表B使用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要再B表的对应列上建上索引，没有用到的索引只会带来额外的负担，一般情况下来说，只需要在关联顺序中的第二个表的相应列上创建索引

确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程

3.优化子查询

子查询的优化最重要的优化建议是尽可能使用关联查询代替

4.优化group by和distinct

很多场景下，mysql使用相同的方法来优化group by和distinct的查询，使用索引是最有效的方式，当时有很多情况下无法使用索引，可以使用临时表和文件排序来分组

如果对关联查询做分组，并且按照查找表中的某个列进行分组，那么可以采用查找表的标识列分组的效率比其他列高 

使用以下sql的列中actor.first_name,actor.last_name包含重复的值，在执行的时候会将重复的值进行合并，导致查询结果有问题

select actor.first_name,actor.last_name ,count(*) from film_actor inner join actor using(actor_id) group by actor.first_name,actor.last_name;

 

 使用主键ID进行查询时由于主键时唯一的所以查询不会合并

select actor.first_name,actor.last_name ,count(*) from film_actor inner join actor using(actor_id) group by actor.actor_id;

5.优化union查询

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

6.自定义变量的使用

6.1自定义变量的限制

1、无法使用查询缓存

2、不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名或者limit子句

3、用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信

4、不能显式地声明自定义变量地类型

5、mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行

6、赋值符号：=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号

7、使用未定义变量不会产生任何语法错误 

6.2优化排名语句

查询获取演过最多电影的前10名演员，然后根据出演电影次数做一个排名

在给一个变量赋值的同时使用这个变量

select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;

避免重新查询刚刚更新的数据

当需要高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么

 

确定取值的顺序

在赋值和读取变量的时候可能是在查询的不同阶段 (在使用自定义变量的时候执行顺序未必是我们自己想的那样)

set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;

 由于where条件先执行第一次判断rownum为0时查出来一条，然后再执行rownum+1，在执行where条件，这时就会出现2条记录

当引入了orde;r by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而where条件是在文件排序操作之前取值的

set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name

解决这个问题的关键在于让变量的赋值和取值发生在执行查询的同一阶段：

set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;