5.mysql调优--mysql查询优化分析（1节）

• 查询慢的原因：
• 优化数据访问：
• 执行过程的优化：
• 优化特定类型的查询：

文件排序：查询的数据量多

mysql> explain select rental_id,staff_id from rental where rental_date > '2005-05-25' order by rental_date,inventory_id;

索引排序：查询的数据量少

mysql> explain select rental_id,staff_id from rental where rental_date > '2006-05-25' order by rental_date,inventory_id;

• 向数据库请求不需要的数据（这些都是不可取的）

查询不需要的数据

多表关联的时候返回全部列

总是取出全部数据

重复查询相同的数据

• 执行过程的优化

查询缓存：

查询优化处理：语法解析器和预处理

mysql查询完缓存之后，会经过以下几个步骤，解析sql、预处理、SQL优化执行计划，这几个步骤出现任何错误，都会终止查询；

语法解析器：mysql通过关键字将sql进行解析，并生成一棵解析树SAB TREE，mysql解析器将使用mysql语法规则进行验证和解析查询，例如验证使用了错误的关键字或者顺序是否正确，预处理会进一步验证解析树是否合法，例如：表名和列名是否存在，是否有歧义、还会验证权限等等；

查询优化器：当语法树没有问题之后，由优化器将其转化成执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到相应的结果，但是不同的执行计划带来的执行效率是不同的，优化器最主要的目的是选择最有效的执行方式。mysql使用的是基于成本（CBU）的优化器，在优化的时候，会尝试预测一个查询使用查询计划的成本，并选择其中一个查询最小的成本。

• 优化器的优化策略

静态优化（只优化一次）：直接对解析树进行分析，并完成优化

动态优化（每次执行都需要重新评估）：跟查询的上下文有关，也可能跟取值、索引对应的条数有关

• 优化器的优化类型：

1. 重新定义关联表的优化顺序
2. 将外连接转化为内连接，内连接的效率要高于内连接
3. 使用等价变换规则，mysql可以使用一些等价变换规则来简化并优化表达式
4. 优化聚合函数 count(),min(),max(),
5. 预估并转化为常数表达式，当mysql检测到一个表达式可以转化为常数的时候，就会把该表达式作为常数处理
6. 索引覆盖扫描，当索引中的列包含所有查询中需要使用列的时候，可以使用覆盖索引
7. 子查询优化
8. 等值传播：explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500 and film_actor.film_id > 500;

• 排序优化

两次传输排序：第一次数据读取是将需要排序的字段取出来，然后进行排序，第二次是将排序好的结果，按照需要去取读取数据行，这种方式效率比较低，原因是第二次读取数据行的时候因为是已经排好序，要去读取所有记录而此时更多的是随机IO，读取成本会比较高，两次传输的优势：在排序的时候，存储尽可能少的数据，让排序缓冲区有尽可能多的容量来容纳排序的行数。

单次传输排序：先读取查询所需要的所有行，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序io读取所有数据，无需任何随机的io，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据。

当需要排序的列的总大小加上order by的列大小超过max_length_for_sort_data定义的字符，mysql会选择两次传输排序，反之使用单次传输排序，用户也可以设置此参数的值来选择排序的方式。

查询缓冲区空间：mysql> show variables like '%max_length_for_sort_data%';

查询所需要的时间：mysql> show status like 'last_query_cost';

• 优化count()查询：

1. 总有人认为myisam的count查询比较快，这是有前提条件的，只有where后面没有查询条件的时候才会快；
2. 使用近似值；
3. 更复杂的优化；直接从缓存取数据

• 优化关联查询：

1. 确保on或者using子句上的列有索引，在创建索引的时候，就要考虑到关联的顺序；
2. 确保任何的groupby或者orderby的表达式只关联到一个表中的列；

• 优化子查询：

1. 使用关联查询代替子查询（子查询查出来的数据放在临时表里，临时表也是io）

• 优化group by 和 distinct

如果对关联查询做分组，并且是对查询表中的某个列进行分组，那么可以使用查询表中的列进行分组效率会高些

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

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

• limit分页优化

直接limit 效率比较低，全表检索所有数据再取5条数据

mysql> select rental_id from rental order by rental_id limit 10000,5;

使用关联查询效率比较高，先用rental_id 查出5条数据，再用rental_id取匹配所有行数据

mysql> select * from rental a join (select rental_id from rental order by rental_id limit 10000,5) b on a.rental_id = b.rental_id;

• union优化：

除非确定不需要 重复的行，否则一定要使用union all，因为没有all关键子，会在零时表上添加关键子 distinct的关键字，这个操作代价很高。