MYSQL查询性能优化

为什么查询会慢

查询的生命周期大致可以按照顺序来看:从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中"执行"可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

优化数据访问

对于低效的查询，通过下面两个步骤来分析总是很有效:
1.确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但是也有可能访问太多的列。
2.确认MySQL服务器层是否在分析大量超过需要的数据行。

是否向数据库请求了不需要的数据

查询不需要的记录:一个常见的错误先查出所有结果集，然后获取前面N行后关闭结果集，解决方法是加上LIMIT。
多表关联时返回全部列:应该按需求只返回需要的列
总是取出全部列:审视SELECT *的写法
重复查询相同数据

MySQL是否在扫描额外的记录

查询开销的三个指标:
1.响应时间
2.扫描的行数
3.返回的行数
从慢日志可以看到这三个指标。

扫描的行数和访问类型:
在EXPLAIN的TYPE列反应了访问类型。访问类型有多种:全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。
一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为:
1.在索引中使用WHERE条件来过滤不匹配的记录，这是在存储引擎完成的。
2.使用索引覆盖扫描(在Extra列中出现了Using index)来返回记录，直接从索引中过滤不需要的记录并返回命中的结果，这是在服务器层完成的，无需回表查询记录。
3.从数据表中返回数据，然后过滤不满足条件的记录(在Extra列中出现Using Where)。服务器层完成的。

具体优化的手段

切分查询
将大查询切分成小查询，每个查询功能完全一样，只完成一小部分。

分解关联查询

1.让查询缓存的效率更高，重复利用缓存。
2.将查询分解后，执行单个查询可以减少锁的竞争。
3.在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
4.查询本身效率也可能会有所提升。
5.减少冗余记录的查询
6.相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。

查询执行的基础:
1.客户端发送一条查询给服务器
2.服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3.服务端进行SQL解析、预处理、再由优化器生产对应的执行计划。
4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5.将结果返回给客户端。

优化器

关联子查询
SELECT * FROM sakila.film where film_id in (SELECT film_id FROM salila.film_actor WHERE actor_id = 1);
以为会这样执行:
SELECT * FROM sakila.film where film_id in (xxx,xxx,xxx);
实际上:
SELECT* FROM salila.film WHERE EXISTS (SELECT * FROM sakila.film_actor WHERE actor_id = 1 AND film_actor.film_id =
film.film_id);
改写方法:
1.改写成关联查询
SELECT film.* FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) WHERE actor_id = 1;
2.另一个优化的方法是使用函数GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表(使用IN+子查询，性能经常会非常糟)。有时这比使用关联改写更快。
3.也可以用优化器优化的方式，即上面第二种

优化特定类型的查询

1.优化COUNT():，MYISAM在COUNT(*)全表时可以直接获取这个值，非常快，可以利用这个进行简单优化。
2.优化关联查询:确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序，一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
3.优化子查询:尽可能使用关联查询代替
4.优化GROUP BY和DISTINCT:GROUP BY的列推荐使用标识列
5.优化LIMIT分页:LIMIT 10000，20这样的查询，MYSQL需要查询10020条记录然后只返回最后20条，前面10000都被抛弃，这样的代价非常高。优化方法:①利用延迟关联改写查询，即先查出主键，再用主键去查相应的列。②LIMIT查询转换为已知位置的查询。三、LIMIT和OFFSET的问题，其实是OFFSET的问题，所以想办法把OFFSET用已知位置替代，避免使用OFFSET。