MySQL性能调优之查询优化

本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于性能优化的相关问题，包括了查询优化等内容，下面一起来看一下，希望对大家有帮助。

推荐学习：mysql视频教程

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

一、查询慢的原因

1、网络

2、CPU

3、IO

4、上下文切换

5、系统调用

6、生成统计信息

7、锁等待时间

二、优化数据访问

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

（1）确认应用程序是否在检索大量超过需要的数据

（2）确认mysql服务器层是否在分析大量超过需要的数据行

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

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

（2）多表关联时返回全部列（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…;）

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

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

三、执行过程的优化

1、查询缓存

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

2、查询优化处理

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

（1）语法解析器和预处理

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

当语法树没有问题之后，相应的要由优化器将其转成执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的，优化器的最主要目的就是要选择最有效的执行计划。

mysql使用的是基于成本的优化器，在优化的时候会尝试预测一个查询使用某种查询计划时候的成本，并选择其中成本最小的一个。

a、select count(*) from film_actor; show status like 'last_query_cost';

可以看到这条查询语句大概需要做1104个数据页才能找到对应的数据，这是经过一系列的统计信息计算来的.

(a) 每个表或者索引的页面个数

(b) 索引的基数

(c) 索引和数据行的长度

(d) 索引的分布情况

b、在很多情况下mysql会选择错误的执行计划，原因如下：

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

（b） 执行计划的成本估算不等同于实际执行的成本(有时候某个执行计划虽然需要读取