希望优化查询性能的时候,最好的办法是弄清楚MySQL是如何优化和执行查询的。了解了内部机制,才能更好的实施设计。
当MySQL执行一个查询时,到底做了什么,先看一副图吧:
1、客户端发送一条查询给服务器。
2、服务器先检查查询缓存,如果命中了缓存,则立刻返回缓存中的结果,否则进入下一步。
3、服务器端进行SQL解析、预处理、再由优化器生成对应的执行计划。
4、MySQL根据优化器生成的执行执行计划,调用存储引擎的API来进行查询。
5、将结果返回给客户端
上面的每一步都很复杂,特别是查询优化器这部分,接下来介绍每一个步骤的具体内容。
1、客户端/服务器通信协议
MySQL客户端和服务端之间的通信协议是“半双工”的,意思是在任何一个时刻,要么是由服务端向客户端发送数据,要么是客户端向服务器发送数据,这2个动作不能同时发生。一旦一端开始发生消息,另一端要接收完整个消息才能响应对方。
客户端用一个单独的数据包传给服务器,这也是为什么当查询的语句很长的时候,参数max_allowed_packet就特别重要了,服务器会拒绝太大的数据抛出相应错误。
相反的,服务器给客户端是数据一般比较多,由多个数据包组成,这也是为什么查询时必须加上LIMIT限制的原因。
多数连接MySQL的库函数都可以获得全部结果集并缓存到内存中,默认一般是获得全部结果集并缓存在内存中。但是如果需要返回一个很大的结果集,这样做并不好,因为库函数会花费很多时间来存储所有结果集,但是这样也会在客户端交互的整个过程一直占用服务器资源。
我们举例PHP来说明一下:<?php
$link = mysql_connect('localhost','root','123456');
$result = mysql_query('select * from emp',$link);
while($row = mysql_fetch_array($result)){
//do something
}
不是需要的时候,才去服务器端取数据,而是mysql_query的时候,php就已经将整个结果缓存到内存中,然后从内存中取出数据,如果不想缓存可以使用mysql_unbuffered_query()代替mysql_query()<?php
$link = mysql_connect('localhost','root','123456');
$result = mysql_unbuffered_query('select * from emp',$link);
while($row = mysql_fetch_array($result)){
//do something
}
对于一个MySQL连接,或者说一个线程,任何时候都有一个状态,可以使用showfullprocesslist命令来查看,结果中Command字段显示了这一类的状态
这里对状态做一下简单解释
Sleep:等待客户端发送请求。
Query:线程正在执行查询或者正在将结果发送给客户端。
Locked:线程正在等待表锁,对于MyISAM是一个比较典型的状态,InnoDB的行锁不会体现出来
Analyzingandstatistics:线程正在收集存储引擎的索引统计信息,并生成执行计划。
Copyingtotmptable[ondisk]:正在执行查询,并将结果复制到一个临时表,一般是groupby,文件排序,union操作,如果出现ondisk,表示正在将一个内存临时表放到磁盘临时表上。
Sortingresult:线程正在对结果进行排序。
Sendingdata:这个包括多个状态间,或者生成结果集,或者给客户端返回数据多个情况。
2、查询缓存
在解析一个语句之前,如果查询是打开的,MySQL会先检查这个查询是否会命中缓存中的数据,如果命中,直接从缓存中拿结果给客户端,这种情况查询不会被解析,不会生成执行计划,也不会执行。
3、查询优化器
这个过程是非常复杂的,包括解析SQL,预处理,优化SQL执行计划。
a、语法解析和预处理
语法解析,主要是检测语法规则,比方说错误的关键字,关键字顺序等。
预处理会检测表,列是否存在,这个过程通常很快。
b、语法合格的SQL会由优化器转化成执行计划,一个查询可以有很多执行方式,优化器的作用就是找到最好的执行计划。
MySQL使用基于成本的优化器,选择一个成本最小的。可以通过查询当前会话的last_query_cost的值来看查询成本。
这是根据一系列的统计信息计算得来的,但是很多原因会导致mysql优化器选择错误的执行计划。
比方说统计信息不准确,而且预算的成本不一定等于实际的成本,也不定是最优的执行计划。
下面一些是MySQL能够优化的类型:
》重新定义关联表的顺序
》将外连接转换成内连接
》使用等价变换规则,例如(5=5anda>5)将被改写为a>5
》优化count(),min(),max(),比如查找一个最大的值,只需读取B-Tree索引的最后一条,如果看到EXPLAIN下的Extra中看到Selecttablesoptimizeaway。
有点必须说明:没有任何条件的count(*)会使用引擎提供的一些优化,比方说MyISAM维护了一个变量来存放总行数,所以速度非常快,看下图
》预估并转换常数表达式,表达式会被转化为常数,特别是数学表达式,甚至一个查询也能够转化为一个常数。比方说:
explain select film.film_id,film_actor.actor_id
from film
inner jion film_actor using(film_id)
where film.film_id=1;
》覆盖索引扫描
》子查询优化
》提前终止查询,当发现以满足查询条件的时候,或者条件不成立的时候。
》等值传播select film.film_id
from film
inner join film_actor using(film_id)
where film.film_id > 500;
》列表IN()的比较,用二分查找代替转化成多个等值.
等等优化器所能做的远不止这些,有兴趣的朋友可以深入研究下。