一、Query Optimizer
1、MySQL Query Optimizer是什么
是MySQL查询语句优化器，其主要的功能就是通过计算分析系统中收集的各种统计信息，为客户端请求的Query给出他认为最优的执行计划，也就是他认为最优的数据检索方式。
2、MySQL Query Optimizer基本工作原理
MySQL的Query Tree是通过优化实现DBXP的经典数据结构和Tree构造器而生成的一个指导完成一个Query语句的请求所需要处理的工作步骤，我们可以简单的认为就是一个的数据处理流程规划，只不过是以一个Tree的数据结构存放而已。通过Query Tree我们可以很清楚的知道一个Query的完成需要经过哪些步骤的处理，每一步的数据来源在哪里，处理方式是怎样的。
当客户端向MySQL请求一条Query ，到命令解析器模块完成请求分类区别出是SELECT并转发给Query Optimizer之后，Query Optimizer首先会对整条Query进行，优化处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或者显而易见的条件，结构调整等等。然后则是分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或者Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。
二、Query语句优化基本思路和原则
一般来说，Query语句的优化思路和原则主要体现在以下几个方面：
- 优化更需要优化的Query；
- 定位优化对象的性能瓶颈；
- 明确的优化目标；
- 从Explain入手；
- 多使用profile；
- 永远用小结果集驱动大的结果集；
- 尽可能在索引中完成排序；
- 只取出自己需要的Columns；
- 仅仅使用最有效的过滤条件；
- 尽可能避免复杂的Join和子查询；
1、优化更需要优化的Query
那什么样的Query是更需要优化呢？对于这个问题我们需要从对整个系统的影响来考虑。什么Query的优化能给系统整体带来更大的收益，就更需要优化。一般来说，高并发低消耗（相对）的Query对整个系统的影响远比低并发高消耗的Query大。
一个频繁执行的高并发Query的危险性比一个低并发的Query要大很多。当一个低并发的Query走错执行计划，所带来的影响主要只是该Query的请求者的体验会变差，对整体系统的影响并不会特别的突出，之少还属于可控范围。但是，如果我们一个高并发的Query走错了执行计划，那所带来的后可很可能就是灾难性的，很多时候可能连自救的机会都不给你就会让整个系统Crash掉。
从IO消耗和CPU消耗方面比较，高并发的Query稍微节约一小块资源，就可以让整个系统节省出一大块资源。
2、定位优化对象的性能瓶颈
当我们拿到一条需要优化的Query之后，第一件事情是什么？是反问自己，这条Query有什么问题？我为什么要优化他？只有明白了这些问题，我们才知道我们需要做什么，才能够找到问题的关键。而不能就只是觉得某个Query好像有点慢，需要优化一下，然后就开始一个一个优化方法去轮番尝试。
所以，在拿到一条需要优化的Query之后，我们首先要判断出这个Query的瓶颈到底是IO还是CPU。到底是因为在数据访问消耗了太多的时间，还是在数据的运算（如分组排序等）方面花费了太多资源？
3、明确的优化目标
当我们定位到了一条Query的性能瓶颈之后，就需要通过分析该Query所完成的功能和Query对系统的整体影响制订出一个明确的优化目标。没有一个明确的目标，优化过程将是一个漫无目的而且低效的过程，也很难达收到一个理想的效果。尤其是对于一些实现应用中较为重要功能点的Query更是如此。
如何设定优化目标？
要设定一个合理的优化目标，不能过于理想也不能放任自由，确实是一件非常头疼的事情。一般来说，我们首先需要清楚的了解数据库目前的整体状态，同时也要清楚的知道数据库中与该Query相关的数据库对象的各种信息，而且还要了解该Query在整个应用系统中所实现的功能。了解了数据库整体状态，我们就能知道数据库所能承受的最大压力，也就清楚了我们能够接受的最悲观情况。把握了该Query相关数据库对象的信息，我们就应该知道实现该Query的消耗最理想情况下需要消耗多少资源，最糟糕又需要消耗多少资源。最后，通过该Query所实现的功能点在整个应用系统中的重要地位，我们可以大概的分析出该Query可以占用的系统资源比例，而且我们也能够知道该Query的效率给客户带来的体验影响到底有多大。
4、从Explain入手
只有Explain才能告诉你，这个Query在数据库中是以一个什么样的执行计划来实现的。
5、永远用小结果集驱动大的结果集
在MySQL中的Join，只有Nested Loop一种Join方式，也就是MySQL的Join都是通过嵌套循环来实现的。驱动结果集越大，所需要循环的此时就越多，那么被驱动表的访问次数自然也就越多，而每次访问被驱动表，即使需要的逻辑IO很少，循环次数多了，总量自然也不可能很小，而且每次循环都不能避免的需要消耗CPU ，所以CPU运算量也会跟着增加。所以，如果我们仅仅以表的大小来作为驱动表的判断依据，假若小表过滤后所剩下的结果集比大表多很多，结果就是需要的嵌套循环中带来更多的循环次数，反之，所需要的循环次数就会更少，总体IO量和CPU运算量也会少。而且，就算是非Nested Loop的Join算法，如Oracle中的Hash Join，同样是小结果集驱动大的结果集是最优的选择。
所以，在优化Join Query的时候，最基本的原则就是“小结果集驱动大结果集”，通过这个原则来减少嵌套循环中的循环次数，达到减少IO总量以及CPU运算的次数。
6、尽可能在索引中完成排序
order by字句，尽量使用index方式，避免使用file sort方式排序，尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀法则（要有带头大哥）
7、只取出自己需要的Columns
任何时候在Query中都只取出自己需要的Columns，尤其是在需要排序的Query中。为什么？
对于任何Query，返回的数据都是需要通过网络数据包传回给客户端，如果取出的Column越多，需要传输的数据量自然会越大，不论是从网络带宽方面考虑还是从网络传输的缓冲区来看，都是一个浪费。
在MySQL中存在两种排序算法，一种是在MySQL4.1之前的老算法，实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在我们所设定的排序区（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需要的Columns，也就是说这种算法需要访问两次数据。第二种排序算法是从MySQL4.1版本开始使用的改进算法，一次性将所需要的Columns全部取出，在排序区中进行排序后直接将数据返回给请求客户端。
改进算法只需要访问一次数据，减少了大量的随机IO，极大的提高了带有排序的Query语句的效率。但是，这种改进后的排序算法需要一次性取出并缓存的数据比第一种算法要多很多，如果我们将并不需要的Columns也取出来，就会极大的浪费排序过程所需要的内存。在MySQL4.1之后的版本中，我们可以通过设置max_length_for_sort_data参数大小来控制MySQL选择第一种排序算法还是第二种排序算法。当所取出的Columns的单条记录总大小max_length_for_sort_data设置的大小的时候，MySQL就会选择使用第一种排序算法，反之，则会选择第二种优化后的算法。为了尽可能提高排序性能，我们自然是更希望使用第二种排序算法，所以在Query中仅仅取出我们所需要的Columns是非常有必要的。
8、仅仅使用最有效的过滤条件
很多人在优化Query语句的时候很容易进入一个误区，那就是觉得WHERE子句中的过滤条件越多越好，实际上这并不是一个非常正确的选择。其实我们分析Query语句的性能优劣最关键的就是要让他选择一条最佳的数据访问路径，如何做到通过访问最少的数据量完成自己的任务。
9、尽可能避免复杂的Join和子查询
我们的Query语句所涉及到的表越多，所需要锁定的资源就越多。也就是说，越复杂的Join语句，所需要锁定的资源也就越多，所阻塞的其他线程也就越多。相反，如果我们将比较复杂的Query语句分拆成多个较为简单的Query语句分步执行，每次锁定的资源也就会少很多，所阻塞的其他线程也要少一些。