mysql查询优化详解

为什么查询慢

在编写快速的查询之前，需要清楚一点，真正重要的是响应时间，而且要知道在整个SQL语句的执行过程中每个步骤都花费了多长时间，要知道哪些步骤是拖垮执行效率的关键步骤，想要做到这点，必须要知道查询的生命周期，然后进行优化，不同的应用场景有不同的优化方式，不要一概而论，具体情况具体分析
如果一个sql查询比较慢，可能因为多方面的原因：

网络、cpu、io、上下文切换、系统调用、生成统计信息/临时表、锁等待时间

优化数据访问

查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化

• 确认应用程序是否在检索大量超过需要的数据
• 确认mysql服务器层是否在分析大量超过需要的数据行

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

• 查询不需要的数据

我们常常会误以为mysql会只返回需要的数据，实际上mysql却是先返回全部结果再进行计算，在日常的开发习惯中，经常是先用select语句查询大量的结果，然后获取前面的N行后关闭结果集。
优化方式是在查询后面添加limit

• 多表关联时返回全部列

select * from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title=‘Academy Dinosaur’;
select actor.* from actor…;

• 总是取出全部列

在公司的企业需求中，禁止使用select *,虽然这种方式能够简化开发，但是会影响查询的性能，所以尽量不要使用

• 重复查询相同的数据

如果需要不断的重复执行相同的查询，且每次返回完全相同的数据，因此，基于这样的应用场景，我们可以将这部分数据缓存起来，这样的话能够提高查询效率

执行过程的优化

• 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据，如果查询恰好命中了查询缓存，那么会在返回结果之前会检查用户权限，如果权限没有问题，那么mysql会跳过所有的阶段，就直接从缓存中拿到结果并返回给客户端

• 查询优化处理

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

语法解析器和预处理

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

查询优化器

查询优化器会统计每个表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况

但是在某些情况下mysql可能会选择到错误的执行计划，原因如下：

• 统计信息不准确

InnoDB因为其mvcc的架构，并不能维护一个数据表的行数的精确统计信息

• 执行计划的成本估算不等于实际执行的成本

有时候某个执行计划虽然需要读取更多的页面，但是他的成本却更小，因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很小，mysql层面并不知道哪些页面在内存中，哪些在磁盘，所以查询之际执行过程中到底需要多少次IO是无法得知的

• mysql的最优可能跟想象的不一样

mysql的优化是基于成本模型的优化，但是有可能不是最快的优化

• mysql不会考虑其他并发的查询

• mysql不会考虑不受其控制的操作成本

执行存储过程或者用户自定义函数的成本

优化器的优化策略

• 静态优化

直接对解析树进行分析，并完成优化

• 动态优化

动态优化与查询的上下文有关，也可能跟取值、索引对应的行数有关

mysql对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估

优化器的优化类型

• 重新定义关联表的顺序

数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序时优化器很重要的功能

• 将外连接转化为内连接（内连接效率高于外连接）

• 使用等价变换规则，mysql可以使用一些等价变化来简化并规划表达式

• 优化count()、min()、max()

索引和列是否可以为空通常可以帮助mysql优化这类表达式：例如，要找到某一列的最小值，只需要查询索引的最左端的记录即可，不需要全文扫描比较

• 预估并转化为常数表达式，当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行处理

explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1

• 索引覆盖扫描，当索引中的列包含所有查询中需要使用的列的时候，可以使用覆盖索引

• 子查询优化

mysql在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问，例如将经常查询的数据放入到缓存中

• 等值传播

如果两个列的值通过等式关联，那么mysql能够把其中一个列的where条件传递到另一个上：
explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;
这里使用film_id字段进行等值关联，film_id这个列不仅适用于film表而且适用于film_actor表
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;

关联查询

join的实现原理

• Simple Nested-Loop Join
  
• Index Nested-Loop Join
  
• Block Nested-Loop Join
  

排序算法的优化

• 两次传输排序

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

• 单次传输排序

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

当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式

其他方面的优化

• count查询优化

myisam引擎会维护一个字段可以直接获取到count()，但是前提是不带任何where条件
count(1)、count()没有任何效率上的区别，建议使用count(*)

• 关联查询优化

确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序
当表A和表B使用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要再B表的对应列上建上索引，没有用到的索引只会带来额外的负担，一般情况下来说，只需要在关联顺序中的第二个表的相应列上创建索引
确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程

• 子查询优化

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

• limit分页优化

优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列

• union查询优化

除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高