希望优化查询性能的时候，最好的办法是弄清楚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_idfrom filminner jion film_actor using(film_id)where film.film_id=1;
》覆盖索引扫描
》子查询优化
》提前终止查询，当发现以满足查询条件的时候，或者条件不成立的时候。
》等值传播
select film.film_idfrom filminner join film_actor using(film_id)where film.film_id > 500;
》列表IN()的比较，用二分查找代替转化成多个等值.
等等优化器所能做的远不止这些，有兴趣的朋友可以深入研究下。
本文出自 “phper-每天一点点~” 博客，请务必保留此出处http://janephp.blog.51cto.com/4439680/1315464