高性能MYSQL（学习笔记）-查询性能优化篇3

MySQL查询优化器的局限性

关联子查询

MySQL的关联子查询比较糟糕，特别是像where in（）这样的写法，例如select * from sakila.filmwhere film_id in(select film_id from sakila.film_actor where actor_id = 1)；

Mysql不会先执行子查询，然后执行in操作，mysql会将上面语句改写成：

Select * from sakila.film whereexists(select * from sakila.film_actor where actor_id = 1 and film_actor.film_id= film.film_id)；

我们可以做如下改写：select film.* from sakila.film innerjoin sakila.film_actor using(film_id) where actor_id = 1

如何用好关联子查询

UNION的限制

如果希望union各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION各个子句中分别使用这些子句，例如(select first,from sakila.actor order by first) union all(select firstfrom sakila.customer order by first)limit 20;

这条查询会把actor 中的200条记录和customer中的500条记录放到一个临时表中，然后从临时表中查询出20条。可以通过在union的两个子查询中分别加上一个LIMIT 20来减少临时表中的数据(select first,from sakila.actor order by first limit 20 ) unionall(select first from sakila.customer order by first limit 20)limit 20;

索引合并优化

当where子句中包含多个复杂条件的时候，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查询的行。

优化特定类型的查询

优化COUNT()查询

Count()是个特殊的函数，有两种非常不同的作用，他可以统计某个列值的数量，也可以统计行数，在统计列值时要求列值是非空的，另外一个作用是统计结果集的行数，count(*)会忽略所有列，直接统计行数。在括号内指定了一个列却希望统计结果集的行数，如果希望知道的是结果集行数，那么最好用count(*)。

简单的优化

可以像下面来写这个查询：

Select count(*) from world.city where id>5；通过show status 知道需要查询大量的数据，如果将条件反转一下先查找id<5的城市数，然后用count(*)减掉，可以将扫描行数减少到5行以内：select (select count(*) from world.city)-count(*) from world.citywhere id<=5;