前言：
在之前“影响 MySQL 应用系统性能的相关因素”一章中我们就已经分析过了Query语句对数据库性能的影响非常大，所以本章将专门针对 MySQL 的 Query 语句的优化进行相应的分析。
8.1 理解MySQL的Query Optimizer
8.1.1 MySQL Query Optimizer是什么？
在“MySQL 架构组成”一章中的 “MySQL 逻辑组成”一节中我们已经了解到，在MySQL中有一个专门负责优化SELECT 语句的优化器模块，这就是我们本节将要重点分析的MySQL Optimizer，其主要的功能就是通过计算分析系统中收集的各种统计信息，为客户端请求的 Query 给出他认为最优的执行计划，也就是他认为最优的数据检索方式。
当 MySQL Optimizer 接收到从 Query Parser （解析器）送过来的 Query 之后，会根据 MySQL Query 语句的相应语法对该 Query 进行分解分析的同时，还会做很多其他的计算转化工作。如常量转化，无效内容删除，常量计算等等。所有这些工作都只为了Optimizer工作的唯一目的，分析出最优的数据检索方式，也就是我们常说的执行计划。
8.1.2 MySQL Query Optimizer基本工作原理
在分析MySQL Optimizer的工作原理之前，先了解一下 MySQL 的 Query Tree。MySQL的Query Tree是通过优化实现 DBXP 的经典数据结构和Tree构造器而生成的一个指导完成一个 Query 语句的请求所需要处理的工作步骤，我们可以简单的认为就是一个的数据处理流程规划，只不过是以一个Tree的数据结构存放而已。通过 Query Tree 我们可以很清楚的知道一个 Query 的完成需要经过哪些步骤的处理，每一步的数据来源在哪里，处理方式是怎样的。在整个DBXP 的Query Tree 生成过程中，MySQL 使用了 LEX 和 YACC这两个功能非常强大的语法（词法）分析工具。MySQL Query Optimizer的所有工作都是基于这个 Query Tree 所进行的。各位读者朋友如果对MySQL Query Tree 实现生成的详细信息比较感兴趣，可以参考Chales A. Bell的《Expert MySQL》这本书，里面有比较详细的介绍。
MySQL Query Optimizer 并不是一个纯粹的CBO（Cost Base Optimizer），而是在CBO的基础上增加了一个被称为Heuristic Optimize（启发式优化）的功能。也就是说，MySQL Query Optimizer在优化一个 Query 选择出他认为的最优执行计划的时候，并不一定完全按照系数据库的元信息和系统统计信息，而是在此基础上增加了某些特定的规则。其实我个人的理解就是在CBO的实现中增加了部分RBO（Rule Base Optimizer）的功能，以确保在某些特别的场景下控制 Query 按照预定的方式生成执行计划。
当客户端向 MySQL 请求一条 Query ，到命令解析器模块完成请求分类区别出是 SELECT 并转发给 Query Optimizer 之后，Query Optimizer 首先会对整条 Query 进行，优化处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或者显而易见的条件，结构调整等等。然后则是分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或者 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。
Query Optimizer 是一个数据库软件非常核心的功能，虽然在这里说起来只是简单的几句话，但是在 MySQL 内部，Query Optimizer 实际上是经过了很多复杂的运算分析，才得出最后的执行计划。对于 MySQL Query Optimizer 更多的信息，各位读者可以通过 MySQL Internal 文档进行更为全面的了解。
8.2 Query 语句优化基本思路和原则
在分析如何优化 MySQL Query 之前，我们需要先了解一下 Query 语句优化的基本思路和原则。一般来说，Query 语句的优化思路和原则主要提现在以下几个方面：
- 优化更需要优化的Query；
- 定位优化对象的性能瓶颈；
- 明确的优化目标；
- 从 Explain 入手；
- 多使用profile
- 永远用小结果集驱动大的结果集；
- 尽可能在索引中完成排序；
- 只取出自己需要的Columns；
- 仅仅使用最有效的过滤条件；
- 尽可能避免复杂的Join和子查询；
上面所列的几点信息，前面4点可以理解为 Query 优化的一个基本思路，后面部分则是我们优化中的基本原则。
下面我们先针对 Query 优化的基本思路做一些简单的分析，理解为什么我们的 Query 优化到底该如何进行。
优化更需要优化的 Query
为什么我们需要优化更需要优化的 Query？这个地球人都知道的“并不能成为问题的问题”我想就并不需要我过多解释吧，哈哈。
那什么样的 Query 是更需要优化呢？对于这个问题我们需要从对整个系统的影响来考虑。什么 Query 的优化能给系统整体带来更大的收益，就更需要优化。一般来说，高并发低消耗（相对）的 Query 对整个系统的影响远比低并发高消耗的 Query 大。我们可以通过以下一个非常简单的案例分析来充分说明问题。
假设有一个 Query 每小时执行10000次，每次需要 20个 IO。另外一个 Query 每小时执行10次，每次需要20000个IO。
我们先通过IO消耗方面来分析。可以看出，两个 Query 每小时所消耗的 IO总数目是一样的，都是 200000 IO/小时。假设我们优化第一个 Query，从20个IO降低到18个IO，也就是仅仅降低了 2 个IO，则我们节省了2 * 10000 = 20000 （IO/小时）。而如果希望通过优化第二个 Query达到相同的效果，我们必须要让每个 Query 减少 20000 / 10 = 2000 IO。我想大家都会相信让第一个 Query 节省2个IO远比第二个 Query 节省2000个IO来的容易。
其次，如果通过 CPU 方面消耗的比较，原理和上面的完全一样。只要让第一个 Query 稍微节省一小块资源，就可以让整个系统节省出一大块资源，尤其是在排序，分组这些对 CPU 消耗比较多的操作中尤其突出。
最后，我们从对整个系统的影响来分析。一个频繁执行的高并发 Query 的危险性比一个低并发的 Query 要大很多。当一个低并发的 Query 走错执行计划，所带来的影响主要只是该 Query 的请求者的体验会变差，对整体系统的影响并不会特别的突出，之少还属于可控范围。但是，如果我们一个高并发的 Query 走错了执行计划，那所带来的后可很可能就是灾难性的，很多时候可能连自救的机会都不给你就会让整个系统 Crash 掉。曾经我就遇到这样一个案例，系统中一个并发度较高的 Query 语句走错执行计划，系统顷刻间 Crash，甚至我都还没有反应过来是怎么回事。当重新启动数据库提供服务后，系统负载立刻直线飙升，甚至都来不及登录数据库查看当时有哪些 Active 的线程在执行哪些 Query。如果是遇到一个并发并不太高的 Query 走错执行计划，至少我们还可以控制整个系统不至于系统被直接压跨，甚至连问题根源都难以抓到。
定位优化对象的性能瓶颈
当我们拿到一条需要优化的 Query 之后，第一件事情是什么？是反问自己，这条 Query有什么问题？我为什么要优化他？只有明白了这些问题，我们才知道我们需要做什么，才能够找到问题的关键。而不能就只是觉得某个 Query 好像有点慢，需要优化一下，然后就开始一个一个优化方法去轮番尝试。这样很可能整个优化过程会消耗大量的人力和时间成本，甚至可能到最后还是得不到一个好的优化结果。这就像看病一样，医生必须要清楚的知道我们病的根源才能对症下药。如果只是知道我们什么地方不舒服，然后就开始通过各种药物尝试治疗，那这样所带来的后果可能就非常严重了。
所以，在拿到一条需要优化的 Query 之后，我们首先要判断出这个 Query 的瓶颈到底是IO还是CPU。到底是因为在数据访问消耗了太多的时间，还是在数据的运算（如分组排序等）方面花费了太多资源？
一般来说，在MySQL 5.0系列版本中，我们可以通过系统自带的 PROFILING功能很清楚的找出一个 Query 的瓶颈所在。当然，如果读者朋友为了使用 MySQL 的某些在 5.1 版本中才有的新特性（如 Partition，EVENT等）亦或者是比较喜欢尝试新事务而早早使用的 MySQL 5.1 的预发布版本，可能就没办法使用这个功能了，因为该功能在MySQL5.1 系列刚开始的版本中并不支持，不过让人非常兴奋的是该功能在最新出来的 MySQL 5.1 正式版（5.1.30）又已经提供了。而如果读者朋友正在使用的MySQL是4.x版本，那可能就只能通过自行分析 Query 的各个执行步骤，找到性能损失最大的地方。
明确的优化目标
当我们定为到了一条 Query 的性能瓶颈之后，就需要通过分析该 Query 所完成的功能和 Query 对系统的整体影响制订出一个明确的优化目标。没有一个明确的目标，优化过程将是一个漫无目的而且低效的过程，也很难达收到一个理想的效果。尤其是对于一些实现应用中较为重要功能点的 Query 更是如此。
如何设定优化目标？这可能是很多人都非常头疼的问题，对于我自己也一样。要设定一个合理的优化目标，不能过于理想也不能放任自由，确实是一件非常头疼的事情。一般来说，我们首先需要清楚的了解数据库目前的整体状态，同时也要清楚的知道数据库中与该 Query 相关的数据库对象的各种信息，而且还要了解该 Query 在整个应用系统中所实现的功能。了解了数据库整体状态，我们就能知道数据库所能承受的最大压力，也就清楚了我们能够接受的最悲观情况。把握了该 Query 相关数据库对象的信息，我们就应该知道实现该 Query 的消耗最理想情况下需要消耗多少资源，最糟糕又需要消耗多少资源。最后，通过该 Query 所实现的功能点在整个应用系统中的重要地位，我们可以大概的分析出该 Query 可以占用的系统资源比例，而且我们也能够知道该 Query 的效率给客户带来的体验影响到底有多大。
当我们清楚了这些信息之后，我们基本可以得出该 Query 应该满足的一个性能范围是怎样的，这也就是我们的优化目标范围，然后就是通过寻找相应的优化手段来解决问题了。如果该 Query 实现的应用系统功能比较重要，我们就必须让目标更偏向于理想值一些，即使在其他某些方面作出一些让步与牺牲，比如调整schema设计，调整索引组成等，可能都是需要的。而如果该 Query 所实现的是一些并不是太关键的功能，那我们可以让目标更偏向悲观值一些，而尽量保证其他更重要的 Query 的性能。这种时候，即使需要调整商业需求，减少功能实现，也不得不应该作出让步。
从 Explain 入手
现在，优化目标也已经明确了，自然是奥开始动手的时候了。我们的优化到底该从何处入手呢？答案只有一个，从Explain 开始入手。为什么？因为只有Explain才能告诉你，这个 Query 在数据库中是以一个什么样的执行计划来实现的。
但是，有一点我们必须清楚，Explain只是用来获取一个 Query 在当前状态的数据库中的执行计划，在优化动手之前，我们比需要根据优化目标在自己头脑中有一个清晰的目标执行计划。只有这样，优化的目标才有意义。一个优秀的SQL 调优人员（或者成为 SQL Performance Tuner），在优化任何一个SQL语句之前，都应该在自己头脑中已经先有一个预定的执行计划，然后通过不断的调整尝试，再借助Explain来验证调整的结果是否满足自己预定的执行计划。对于不符合预期的执行计划需要不断分析 Query 的写法和数据库对象的信息，继续调整尝试，直至得到预期的结果。
当然，人无完人，并不一定每次自己预设的执行计划都肯定是最优的，在不断调整测试的过程中，如果发现MySQL Optimizer 所选择的执行计划的实际执行效果确实比自己预设的要好，我们当然还是应该选择使用 MySQL optimizer 所生成的执行计划。
上面的这个优化思路，只是给大家指了一个优化的基本方向，实际操作还需要读者朋友不断的结合具体应用场景不断的测试实践来体会。当然也并不一定所有的情况都非要严格遵循这样一个思路，规则是死的，人是活的，只有更合理的方法，没有最合理的规则。
在了解了上面这些优化的基本思路之后，我们再来看看优化的几个基本原则。
永远用小结果集驱动大的结果集
很多人喜欢在优化SQL的时候说用小表驱动大表，个人认为这样的说法不太严谨。为什么？因为大表经过 WHERE 条件过滤之后所返回的结果集并不一定就比小表所返回的结果集大，可能反而更小。在这种情况下如果仍然采用小表驱动大表，就会得到相反的性能效果。
其实这样的结果也非常容易理解，在MySQL中的Join，只有Nested Loop一种Join方式，也就是MySQL的Join都是通过嵌套循环来实现的。驱动结果集越大，所需要循环的此时就越多，那么被驱动表的访问次数自然也就越多，而每次访问被驱动表，即使需要的逻辑IO很少，循环次数多了，总量自然也不可能很小，而且每次循环都不能避免的需要消耗 CPU ，所以 CPU 运算量也会跟着增加。所以，如果我们仅仅以表的大小来作为驱动表的判断依据，假若小表过滤后所剩下的结果集比大表多很多，结果就是需要的嵌套循环中带来更多的循环次数，反之，所需要的循环次数就会更少，总体IO量和 CPU 运算量也会少。而且，就算是非 Nested Loop 的Join 算法，如 Oracle 中的 Hash Join，同样是小结果集驱动大的结果集是最优的选择。
所以，在优化 Join Query 的时候，最基本的原则就是“小结果集驱动大结果集”，通过这个原则来减少嵌套循环中的循环次数，达到减少IO总量以及 CPU 运算的次数。
Nested Loop（http://www.cnblogs.com/crazylqy/p/7614903.html）
尽可能在索引中完成排序
只取出自己需要的Columns
任何时候在 Query 中都只取出自己需要的Columns，尤其是在需要排序的 Query 中。为什么？
对于任何 Query，返回的数据都是需要通过网络数据包传回给客户端，如果取出的 Column 越多，需要传输的数据量自然会越大，不论是从网络带宽方面考虑还是从网络传输的缓冲区来看，都是一个浪费。
如果是需要排序的 Query 来说，影响就更大了。在 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 是非常有必要的。
仅仅使用最有效的过滤条件
很多人在优化 Query 语句的时候很容易进入一个误区，那就是觉得 WHERE 子句中的过滤条件越多越好，实际上这并不是一个非常正确的选择。其实我们分析 Query 语句的性能优劣最关键的就是要让他选择一条最佳的数据访问路径，如何做到通过访问最少的数据量完成自己的任务。
为什么说过滤条件多不一定是好事呢？请看下面示例：
需求： 查找某个用户在所有group中所发的讨论message基本信息。
场景： 1、知道用户ID 和用户 nick_name
2、信息所在表为group_message
3、group_message中存在用户ID(user_id)和nick_name(author)两个索引
方案一：将用户 ID 和用户 nick_name 两者都作为过滤条件放在WHERE子句中来查询，Query 的执行计划如下：
sky@localhost : example 11:29:37> EXPLAIN SELECT * FROM group_message -> WHERE user_id = 1 AND author='1111111111'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: group_message type: ref possible_keys: group_message_author_ind,group_message_uid_ind key: group_message_author_ind key_len: 98 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec)
方案二：仅仅将用户 ID 作为过滤条件放在WHERE子句中来查询，Query 的执行计划如下：
sky@localhost : example 11:30:45> EXPLAIN SELECT * FROM group_message -> WHERE user_id = 1\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: group_message type: ref possible_keys: group_message_uid_ind key: group_message_uid_ind key_len: 4 ref: const rows: 1 Extra: 1 row in set (0.00 sec)
方案二：仅将用户 nick_name 作为过滤条件放在WHERE子句中来查询，Query 的执行计划如下：
sky@localhost : example 11:38:45> EXPLAIN SELECT * FROM group_message -> WHERE author = '1111111111'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: group_message type: ref possible_keys: group_message_author_ind key: group_message_author_ind key_len: 98 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec)
初略一看三个执行计划好像都挺好的啊，每一个 Query 的执行类型都利用到了索引，而且都是“ref”类型。可是仔细一分析，就会发现，group_message_uid_ind 索引的索引键长度为4（key_len: 4），由于 user_id 字段类型为int，所以我们可以判定出 Query Optimizer 给出的这个索引键长度是完全准确的。而 group_message_author_ind 索引的索引键长度为98（key_len: 98），因为author字段定义为varchar(32) ，而所使用的字符集是utf8，32 * 3 + 2 = 98。而且，由于 user_id 与 author（来源于nick_name）全部都是一一对应的，所以同一个 user_id 有哪些记录，那么所对应的 author 也会有完全相同的记录。所以，同样的数据在 group_message_author_ind 索引中所占用的存储空间要远远大于 group_message_uid_ind 索引所占用的空间。占用空间更大，代表我们访问该索引所需要读取的数据量就会更多。所以，选择 group_message_uid_ind 的执行计划才是最有的执行计划。也就是说，上面的方案二才是最有方案，而使用了更多的 WHERE 条件的方案一反而没有仅仅使用 user_id 一个过滤条件的方案一优。
可能有些人会说，那如果将 user_id 和 author 两者建立联合索引呢？告诉你，效果可能比没有这个索引的时候更差，因为这个联合索引的索引键更长，索引占用的空间将会更大。
这个示例并不一定能代表所有场景，仅仅是希望让大家明白，并不是任何时候都是使用的过滤条件越多性能会越好。在实际应用场景中，肯定会存在更多更复杂的情形，怎样使我们的 Query 有一个更优化的执行计划，更高效的性能，还需要靠大家仔细分析各种执行计划的具体差别，才能选择出更优化的 Query。
尽可能避免复杂的Join和子查询
我们都知道，MySQL 在并发这一块做的并不是太好，当并发量太高的时候，系统整体性能可能会急剧下降，尤其是遇到一些较为复杂的 Query 的时候更是如此。这主要与 MySQL 内部资源的争用锁定控制有关，如读写相斥等等。对于 Innodb 存储引擎由于实现了行级锁定可能还要稍微好一些，如果使用的 MyISAM 存储引擎，并发一旦较高的时候，性能下降非常明显。所以，我们的 Query 语句所涉及到的表越多，所需要锁定的资源就越多。也就是说，越复杂的 Join 语句，所需要锁定的资源也就越多，所阻塞的其他线程也就越多。相反，如果我们将比较复杂的 Query 语句分拆成多个较为简单的 Query 语句分步执行，每次锁定的资源也就会少很多，所阻塞的其他线程也要少一些。
可能很多读者会有疑问，将复杂 Join 语句分拆成多个简单的 Query 语句之后，那不是我们的网络交互就会更多了吗？网络延时方面的总体消耗也就更大了啊，完成整个查询的时间不是反而更长了吗？是的，这种情况是可能存在，但也并不是肯定就会如此。我们可以再分析一下，一个复杂的 Join Query 语句在执行的时候，所需要锁定的资源比较多，可能被别人阻塞的概率也就更大，如果是一个简单的 Query，由于需要锁定的资源较少，被阻塞的概率也会小很多。所以 较为复杂的 Join Query 也有可能在执行之前被阻塞而浪费更多的时间。而且，我们的数据库所服务的并不是单单这一个 Query 请求，还有很多很多其他的请求，在高并发的系统中，牺牲单个 Query 的短暂响应时间而提高整体处理能力也是非常值得的。优化本身就是一门平衡与取舍的艺术，只有懂得取舍，平衡整体，才能让系统更优。
对于子查询，可能不需要我多说很多人就明白为什么会不被推荐使用。在 MySQL 中，子查询的实现目前还比较差，很难得到一个很好的执行计划，很多时候明明有索引可以利用，可 Query Optimizer 就是不用。从 MySQL 官方给出的信息说，这一问题将在 MySQL6.0 中得到较好的解决，将会引入 SemiJoin 的执行计划，可 MySQL6.0 离我们投入生产环境使用恐怕还有很遥远的一段时间。所以，在 Query 优化的过程中，能不用子查询的时候就尽量不要使用子查询。
上面这些仅仅只是一些常用的优化原则，并不是说在 Query 优化中就只需要做到这些原则就可以，更不是说 Query 优化只能通过这些原则来优化。在实际优化过程中，我们还可能会遇到很多带有较为复杂商业逻辑的场景，具体的优化方法就只能根据不同的应用场景来具体分析，逐步调整。其实，最有效的优化，就是不要用，也就是不要实现这个商业需求。
8.3 充分利用 Explain 和 Profiling
8.3.1 Explain 的使用
说到Explain，肯定很多读者之前都都已经用过了，MySQL Query Optimizer 通过我让们执行 EXPLAIN 命令来告诉我们他将使用一个什么样的执行计划来优化我们的 Query。所以，可以说 Explain 是在优化 Query 时最直接有效的验证我们想法的工具。在本章前面部分我就说过，一个好的 SQL Performance Tuner 在动手优化一个 Query 之前，头脑中就应该已经有一个好的执行计划，后面的优化工作只是为实现该执行计划而作出各种调整。
在我们对某个 Query 优化过程中，需要不断的使用 Explain 来验证我们的各种调整是否有效。就像本书之前的很多示例都会通过 Explain 来验证和展示结果一样，所有的 Query 优化都应该充分利用他。
我们先看一下在 MySQL Explain功能中给我们展示的各种信息的解释：
◆ ID：Query Optimizer 所选定的执行计划中查询的序列号；
◆ Select_type：所使用的查询类型，主要有以下这几种查询类型
◇ DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集；
◇ DEPENDENT UNION：子查询中的UNION，且为UNION中从第二个SELECT开始的后面所有SELECT，同样依赖于外部查询的结果集；
◇ PRIMARY：子查询中的最外层查询，注意并不是主键查询；
◇ SIMPLE：除子查询或者UNION之外的其他查询；
◇ SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集；
◇ UNCACHEABLE SUBQUERY：结果集无法缓存的子查询；
◇ UNION：UNION语句中第二个SELECT开始的后面所有SELECT，第一个SELECT为PRIMARY
◇ UNION RESULT：UNION 中的合并结果；
◆ Table：显示这一步所访问的数据库中的表的名称；
◆ Type：告诉我们对表所使用的访问方式，主要包含如下集中类型；
◇ all：全表扫描
◇ const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次；
◇ eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；
◇ fulltext：
◇ index：全索引扫描；
◇ index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge之后再读取表数据；
◇ index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或者唯一索引；
◇ rang：索引范围扫描；
◇ ref：Join语句中被驱动表索引引用查询；
◇ ref_or_null：与ref的唯一区别就是在使用索引引用查询之外再增加一个空值的查询；
◇ system：系统表，表中只有一行数据；
◇ unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束；
◇
◆ Possible_keys：该查询可以利用的索引. 如果没有任何索引可以使用，就会显示成null，这一项内容对于优化时候索引的调整非常重要；
◆ Key：MySQL Query Optimizer 从 possible_keys 中所选择使用的索引；
◆ Key_len：被选中使用索引的索引键长度；
◆ Ref：列出是通过常量（const），还是某个表的某个字段（如果是join）来过滤（通过key）的；
◆ Rows：MySQL Query Optimizer 通过系统收集到的统计信息估算出来的结果集记录条数；
◆ Extra：查询中每一步实现的额外细节信息，主要可能会是以下内容：
◇ Distinct：查找distinct 值，所以当mysql找到了第一条匹配的结果后，将停止该值的查询而转为后面其他值的查询；
◇ Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用使用；
◇ Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果；
◇ No tables：Query 语句中使用 FROM DUAL 或者不包含任何 FROM子句；
◇ Not exists：在某些左连接中 MySQL Query Optimizer 所通过改变原有 Query 的组成而使用的优化方法，可以部分减少数据访问次数；
◇ Range checked for each record (index map: N)：通过 MySQL 官方手册的描述，当 MySQL Query Optimizer 没有发现好的可以使用的索引的时候，如果发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。
◇ Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的时候，MySQL Query Optimizer 会通过索引而直接一次定位到所需的数据行完成整个查询。当然，前提是在 Query 中不能有 GROUP BY 操作。如使用MIN()或者MAX（）的时候；
◇ Using filesort：当我们的 Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。
◇ Using index：所需要的数据只需要在 Index 即可全部获得而不需要再到表中取数据；
◇ Using index for group-by：数据访问和 Using index 一样，所需数据只需要读取索引即可，而当 Query 中使用了 GROUP BY 或者 DISTINCT 子句的时候，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by；
◇ Using temporary：当 MySQL 在某些操作中必须使用临时表的时候，在 Extra 信息中就会出现Using temporary 。主要常见于 GROUP BY 和 ORDER BY 等操作中。
◇ Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where 信息；
◇ Using where with pushed condition：这是一个仅仅在 NDBCluster存储引擎中才会出现的信息，而且还需要通过打开 Condition Pushdown 优化功能才可能会被使用。控制参数为 engine_condition_pushdown 。
这里我们通过分析示例来看一下不同的 Query 语句通过 Explain 所显示的不同信息：
我们先看一个简单的单表 Query：
sky@localhost : example 11:33:18> explain select count(*),max(id),min(id) -> from user\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Select tables optimized away
对user表的单表查询，查询类型为SIMPLE，因为既没有 UNION 也不是子查询。聚合函数 MAX MIN 以及 COUNT 三者所需要的数据都可以通过索引就能够直接定位得到数据，所以整个实现的 Extra 信息为 Select tables optimized away。
再来看一个稍微复杂一点的 Query，一个子查询：
sky@localhost : example 11:38:48> explain select name from groups -> where id in ( select group_id from user_group where user_id = 1)\G
*************************** 1. row ***************************
id: 1 select_type: PRIMARY table: groups type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 50000 Extra: Using where
*************************** 2. row ***************************
id: 2 select_type: DEPENDENT SUBQUERY table: user_group type: ref possible_keys: user_group_gid_ind,user_group_uid_ind key: user_group_uid_ind key_len: 4 ref: const rows: 1 Extra: Using where
通过 id 信息我们可以得知 MySQL Query Optimizer 给出的执行计划是首先对 groups 进行全表扫描，然后第二步才访问 user_group 表，所使用的查询方式是DEPENDENT SUBQUERY，对所需数据的访问方式是索引扫描，由于过滤条件是一个整数，所以索引扫描的类型为 ref，过滤条件是 const。可以使用的索引有两个，一个是基于 user_id，另一个则是基于 group_id 的。为什么基于 group_id 的索引 user_group_gid_ind 也被列为可选索引了呢？是因为与子查询的外层查询所关联的条件是基于 group_id 的。当然，最后 MySQL Query Optimizer 还是选择了使用基于 user_id 的索引 user_group_uid_ind。
由于篇幅关系，这里就不再继续举例了，大家可以通过自行通过 Explain 功能分析各自应用环境中的各种 Query，了解他们在我们的 MySQL 中到底是怎么运行的。
8.3.2 Profiling 的使用
在本章第一节中我们还提到过通过 Query Profiler 来定位一条 Query 的性能瓶颈，这里我们再详细介绍一下 Profiling 的用途及使用方法。
要想优化一条 Query，我们就需要清楚的知道这条 Query 的性能瓶颈到底在哪里，是消耗的 CPU 计算太多，还是需要的的 IO 操作太多？要想能够清楚的了解这些信息，在 MySQL 5.0 和 MySQL 5.1 正式版中已经可以非常容易做到了，那就是通过 Query Profiler 功能。
MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条 Query 在整个执行过程中多种资源的消耗情况，如 CPU，IO，IPC，SWAP等，以及发生的 PAGE FAULTS，CONTEXT SWITCHE 等等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。下面我们看看 Query Profiler 的具体用法。
1、 开启 profiling 参数
root@localhost : (none) 10:53:11> set profiling=1; Query OK, 0 rows affected (0.00 sec)
通过执行 “set profiling”命令，可以开启关闭 Query Profiler 功能。
2、 执行 Query
... ...
root@localhost : test 07:43:18> select status,count(*) -> from test_profiling group by status; +----------------+----------+ | status | count(*) | +----------------+----------+ | st_xxx1 | 27 | | st_xxx2 | 6666 | | st_xxx3 | 292887 | | st_xxx4 | 15 | +----------------+----------+ 5 rows in set (1.11 sec)
... ...
在开启 Query Profiler 功能之后，MySQL 就会自动记录所有执行的 Query 的profile 信息了。
3、获取系统中保存的所有 Query 的 profile概要信息
root@localhost : test 07:47:35> show profiles; +----------+------------+------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------------------------------------+ | 1 | 0.00183100 | show databases | | 2 | 0.00007000 | SELECT DATABASE() | | 3 | 0.00099300 | desc test | | 4 | 0.00048800 | show tables | | 5 | 0.00430400 | desc test_profiling | | 6 | 1.90115800 | select status,count(*) from test_profiling group by status | +----------+------------+------------------------------------------------------------+ 3 rows in set (0.00 sec)
通过执行 “SHOW PROFILE” 命令获取当前系统中保存的多个 Query 的 profile 的概要信息。
4、针对单个 Query 获取详细的 profile 信息。
在获取到概要信息之后，我们就可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中详细的 profile 信息了，具体操作如下：
root@localhost : test 07:49:24> show profile cpu, block io for query 6; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000349 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | Creating tmp table | 0.000035 | 0.000999 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Copying to tmp table | 1.900619 | 1.030844 | 0.197970 | 347 | 347 | | Sorting result | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | removing tmp table | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000029 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+
上面的例子中是获取 CPU 和 Block IO 的消耗，非常清晰，对于定位性能瓶颈非常适用。希望得到取其他的信息，都可以通过执行 “SHOW PROFILE *** FOR QUERY n” 来获取，各位读者朋友可以自行测试熟悉。
8.4 合理设计并利用索引
索引，可以说是数据库相关优化尤其是在 Query 优化中最常用的优化手段之一了。但是很多人在大部分时候都只是大概了解索引的用途，知道索引能够让 Query 执行的更快，而并不知道为什么会更快。尤其是索引的实现原理，存储方式，以及不同索引之间的区别等就更不是太清楚了。正因为索引对我们的 Query 性能影响很大，所以我们更应该深入理解 MySQL 中索引的基本实现，以及不同索引之间的区别，才能分析出如何设计出最优的索引来最大幅度的提升 Query 的执行效率。
在 MySQL 中，主要有四种类型的索引，分别为：B-Tree 索引，Hash 索引，Fulltext 索引和 R-Tree 索引，下面针对这四种索引的基本实现方式及存储结构做一个大概的分析。
B-Tree 索引
B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型，除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引。不仅仅在 MySQL 中是如此，实际上在其他的很多数据库管理系统中 B-Tree 索引也同样是作为最主要的索引类型，这主要是因为 B-Tree索引的存储结构在数据库的数据检索中有非常优异的表现。
一般来说，MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的Leaf Node，而且到任何一个 Leaf Node的最短路径的长度都是完全相同的，所以我们大家都称之为 B-Tree 索引当然，可能各种数据库（或 MySQL 的各种存储引擎）在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+Tree，也就是在 B-Tree 数据结构的基础上做了很小的改造，在每一个 Leaf Node 上面出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 Leaf Node 的指针信息，这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。
在 Innodb 存储引擎中，存在两种不同形式的索引，一种是 Cluster 形式的主键索引（Primary Key），另外一种则是和其他存储引擎（如MyISAM存储引擎）存放形式基本相同的普通 B-Tree 索引，这种索引在 Innodb 存储引擎中被称为 Secondary Index。下面我们通过图示来针对这两种索引的存放形式做一个比较。
图示中左边为 Clustered 形式存放的 Primary Key，右侧则为普通的 B-Tree 索引。两种索引在 Root Node 和Branch Nodes 方面都还是完全一样的。而 Leaf Nodes 就出现差异了。在 Primary Key 中，Leaf Nodes 存放的是表的实际数据，不仅仅包括主键字段的数据，还包括其他字段的数据，整个数据以主键值有序的排列。而 Secondary Index 则和其他普通的 B-Tree 索引没有太大的差异，只是在 Leaf Nodes 出了存放索引键的相关信息外，还存放了 Innodb 的主键值。
所以，在 Innodb 中如果通过主键来访问数据效率是非常高的，而如果是通过 Secondary Index 来访问数据的话，Innodb 首先通过 Secondary Index 的相关信息，通过相应的索引键检索到 Leaf Node 之后，需要再通过 Leaf Node 中存放的主键值再通过主键索引来获取相应的数据行。
MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空的键而已。而且 MyISAM 存储引擎的索引和 Innodb 的 Secondary Index 的存储结构也基本相同，主要的区别只是 MyISAM 存储引擎在 Leaf Nodes 上面出了存放索引键信息之外，再存放能直接定位到 MyISAM 数据文件中相应的数据行的信息（如Row Number），但并不会存放主键的键值信息。
Hash 索引
Hash 索引在 MySQL 中使用的并不是很多，目前主要是 Memory 存储引擎使用，而且在 Memory 存储引擎中将 Hash 索引作为默认的索引类型。所谓 Hash 索引，实际上就是通过一定的 Hash 算法，将需要索引的键值进行 Hash 运算，然后将得到的 Hash 值存入一个 Hash 表中。然后每次需要检索的时候，都会将检索条件进行相同算法的 Hash 运算，然后再和 Hash 表中的 Hash 值进行比较并得出相应的信息。
在 Memory 存储引擎中，MySQL 还支持非唯一的 Hash 索引。可能很多人会比较惊讶，如果是非唯一的 Hash 索引，那相同的值该如何处理呢？在 Memory 存储引擎的 Hash 索引中，如果遇到非唯一值，存储引擎会将他们链接到同一个 hash 键值下以一个 链表的形式存在，然后在取得实际键值的时候时候再过滤不符合的键。
由于 Hash 索引结构的特殊性，其检索效率非常的高，索引的检索可以一次定位，而不需要像 B-Tree 索引需要从根节点再到枝节点最后才能访问到页节点这样多次IO访问，所以 Hash 索引的效率要远高于 B-Tree 索引。
可能很多人又会有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，，Hash 索引也一样，虽然 Hash 索引检索效率非常之高，但是 Hash 索引本身由于其实的特殊性也带来了很多限制和弊端，主要有以下这些：
- Hash 索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
由于 Hash 索引所比较的是进行 Hash 运算之后的 Hash 值，所以 Hash 索引只能用于等值的过滤，而不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证还和 Hash 运算之前完全一样。
- Hash 索引无法被利用来避免数据的排序操作；
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值的完全一样，所以数据库无法利用索引的数据来避免任何和排序运算；
- Hash 索引不能利用部分索引键查询；
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并之后再一起计算 Hash 值，而不是单独计算 Hash 值，所以当我们通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用到；
- Hash 索引在任何时候都不能避免表扫面；
前面我们已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，而且由于存在不同索引键存在相同 Hash 值的可能，所以即使我们仅仅取满足某个 Hash 键值的数据的记录条数，都无法直接从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较而得到相应的结果。
- Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；
对于选择性比较低的索引键，如果我们创建 Hash 索引，那么我们将会存在大量记录指针信息存与同一个 Hash 值相关连。这样要定位某一条记录的时候就会非常的麻烦，可能会浪费非常多次表数据的访问，而造成整体性能的地下。
Full-text 索引
Full-text 索引也就是我们常说的全文索引，目前在 MySQL 中仅有 MyISAM 存储引擎支持（
MySQL 5.6.4里才添加了InnoDB引擎的Full-Text索引支持。
不能升级MySQL版本的话：推荐用Lucene（ElasticSearch比较容易部署）或Sphinx这样的第三方解决方案。
），而且也并不是所有的数据类型都支持全文索引。目前来说，仅有CHAR，VARCHAR和TEXT这三种数据类型的列可以建 Full-text 索引。
一般来说，Fulltext 索引主要用来替代效率低下的 LIKE '%***%' 操作。实际上，Full-text 索引并不只是能简单的替代传统的全模糊 LIKE 操作，而且能通过多字段组合的 Full-text 索引一次全模糊匹配多个字段。
Full-text 索引和普通的 B-Tree 索引的实现区别较大，虽然他同样是以 B-Tree 形式来存放索引数据，但是他并不是通过字段内容的完整匹配，而是通过特定的算法，将字段数据进行分隔后再进行的索引。一般来说 MySQL 系统会按照四个字节来分隔。在整个 Full-text 索引中，存储内容被分为两部分，一部分是分隔前的索引字符串数据集合，另一部分是分隔后的词（或者词组）的索引信息。所以，Full-text 索引中，真正在 B-Tree 索引细细中的并不是我们表中的原始数据，而是分词之后的索引数据。在 B-Tree 索引的节点信息中，存放了各个分隔后的词信息，以及指向包含该词的分隔前字符串信息在索引数据集合中的位置信息。
Full-text 索引不仅仅能实现模糊匹配查找，在实现了基于自然语言的的匹配度查找。当然，这个匹配读到底有多准确就需要读者朋友去自行验证了。Full-text 通过一些特定的语法信息，针对自然语言做了各种相应规则的匹配，最后给出非负的匹配值。
此外，有一点是需要大家注意的，MySQL 目前的 Full-text 索引在中文支持方面还不太好，需要借助第三方的补丁或者插件来完成。而且 Full-text 的创建所消耗的资源也是比较大的，所以在应用于实际生产环境之前还是尽量做好评估。
关于 Full-text 的实际使用方法由于不是本书的重点，感兴趣的读者朋友可以自行参阅 MySQL 关于 Full-text 相关的使用手册来了解更为详尽的信息。
R-Tree 索引
R-Tree 索引可能是我们在其他数据库中很少见到的一种索引类型，主要用来解决空间数据检索的问题。
在 MySQL 中，支持一种用来存放空间信息的数据类型GEOMETRY，且基于 OpenGIS 规范。在 MySQL5.0.16 之前的版本中，仅仅 MyISAM 存储引擎支持该数据类型，但是从 MySQL5.0.16 版本开始，BDB，Innodb，NDBCluster 和 Archive 存储引擎也开始支持该数据类型。当然，虽然多种存储引擎都开始支持 GEOMETRY 数据类型，但是仅仅之后 MyISAM 存储引擎支持 R-Tree 索引。
在 MySQL 中采用了具有二次分裂特性的 R-Tree 来索引空间数据信息，然后通过几何对象（MRB）信息来创建索引。
虽然仅仅只有 MyISAM 存储引擎支持空间索引（R-Tree Index），但是如果我们是精确的等值匹配，创建在空间数据上面的 B-Tree 索引同样可以起到优化检索的效果，空间索引的主要优势在于当我们使用范围查找的时候，可以利用到 R-Tree 索引，而这时候，B-Tree 索引就无能为力了。
对于 R-Tree 索引的详细介绍和使用信息清参阅 MySQL 使用手册。
索引的利弊与如何判定是否需要索引
相信没一位读者朋友都知道索引能够极大的提高我们数据检索的效率，让我们的 Query 执行的更快，但是可能并不是每一位朋友都清楚索引在极大提高检索效率的同时，也给我们的数据库带来了一些负面的影响。下面我们就分别对 MySQL 中索引的利与弊做一个简单的分析。
索引的利处
索引能够给我们带来的最大益处可能读者朋友基本上都有一定的了解，但是我相信并不是每一位读者朋友都能够了解的比较全面。很多朋友对数据库中的索引的认识可能主要还是只限于“能够提高数据检索的效率，降低数据库的IO成本”。
确实，在数据库中个表的某个字段创建索引，所带来的最大益处就是将该字段作为检索条件的时候可以极大的提高检索效率，加快检索时间，降低检索过程中所需要读取的数据量。但是索引所给我们带来的收益只是提高表数据的检索效率吗？当然不是，索引还有一个非常重要的用途，那就是降低数据的排序成本。
我们知道，每个索引中索引数据都是按照索引键键值进行排序后存放的，所以，当我们的 Query 语句中包含排序分组操作的时候，如果我们的排序字段和索引键字段刚好一致，MySQL Query Optimizer 就会告诉 mysqld 在取得数据之后不用排序了，因为根据索引取得的数据已经是满足客户的排序要求。
那如果是分组操作呢？分组操作没办法直接利用索引完成。但是分组操作是需要先进行排序然后才分组的，所以当我们的 Query 语句中包含分组操作，而且分组字段也刚好和索引键字段一致，那么 mysqld 同样可以利用到索引已经排好序的这个特性而省略掉分组中的排序操作。
排序分组操作主要消耗的是我们的内存和 CPU 资源，如果我们能够在进行排序分组操作中利用好索引，将会极大的降低 CPU 资源的消耗。
索引的弊端
索引的益处我们都已经清楚了，但是我们不能光看到索引给我们带来的这么多益处之后就认为索引是解决 Query 优化的圣经，只要发现 Query 运行不够快就将 WHERE 子句中的条件全部放在索引中。
确实，索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。假设我们在Table ta 中的Column ca 创建了索引 idx_ta_ca，那么任何更新 Column ca 的操作，MySQL 都需要在更新表中 Column ca的同时，也更新 Column ca 的索引数据，调整因为更新所带来键值变化后的索引信息。而如果我们没有对 Column ca 进行索引的话,MySQL 所需要做的仅仅只是更新表中 Column ca 的信息。这样，所带来的最明显的资源消耗就是增加了更新所带来的 IO 量和调整索引所致的计算量。此外，Column ca 的索引 idx_ta_ca 是需要占用存储空间的，而且随着 Table ta 数据量的增长，idx_ta_ca 所占用的空间也会不断增长。所以索引还会带来存储空间资源消耗的增长。
如何判定是否需要创建索引
在了解了索引的利与弊之后，我们知道了索引并不是越多越好，知道了索引也是会带来副作用的。那我们到底该如何来判断某个索引是否应该创建呢？
实际上，并没有一个非常明确的定律可以清晰的定义出什么字段应该创建索引什么字段不该创建索引。因为我们的应用场景实在是太复杂，存在太多的差异。当然，我们还是仍然能够找到几点基本的判定策略来帮助我们分析是否需要创建索引。
◆ 较频繁的作为查询条件的字段应该创建索引；
提高数据查询检索的效率最有效的办法就是减少需要访问的数据量，从上面所了解到的索引的益处中我们知道了，索引正是我们减少通过索引键字段作为查询条件的 Query 的 IO 量的最有效手段。所以一般来说我们应该为较为频繁的查询条件字段创建索引。
◆ 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
唯一性太差的字段主要是指哪些呢？如状态字段，类型字段等等这些字段中存方的数据可能总共就是那么几个几十个值重复使用，每个值都会存在于成千上万或是更多的记录中。对于这类字段，我们完全没有必要创建单独的索引的。因为即使我们创建了索引，MySQL Query Optimizer 大多数时候也不会去选择使用，如果什么时候 MySQL Query Optimizer 抽了一下风选择了这种索引，那么非常遗憾的告诉你，这可能会带来极大的性能问题。由于索引字段中每个值都含有大量的记录，那么存储引擎在根据索引访问数据的时候会带来大量的随机IO，甚至有些时候可能还会出现大量的重复IO。
这主要是由于数据基于索引扫描的特点所引起的。当我们通过索引访问表中的数据的时候，MySQL 会按照索引键的键值的顺序来依序进行访问。一般来说每个数据页中大都会存放多条记录，但是这些记录可能大多数都不会是和你所使用的索引键的键值顺序一致。
假如有以下场景，我们通过索引查找键值为A和B的某些数据。当我们先通过 A 键值找到第一条满足要求的记录后，我们会读取这条记录所在的 X 数据页，然后我们继续往下查找索引，发现 A 键值所对应的另外一条记录也满足我们的要求，但是这条记录不在 X 数据页上面，而在 Y 数据页上面，这时候存储引擎就会丢弃 X 数据页，而读取 Y 数据页。如此继续一直到查找完 A 键值所对应的所有记录。然后轮到 B 键值了，这时候发现正在查找的记录又在 X 数据页上面，可之前读取的 X 数据页已经被丢弃了，只能再次读取 X 数据页。这时候，实际上已经出现重复读取 X 数据页两次了。在继续往后的查找中，可能还会出现一次又一次的重复读取。这无疑极大的给存储引擎增大了 IO 访问量。
不仅如此，如果一个键值对应了太多的数据记录，也就是说通过该键值会返回占整个表比例很大的记录的时候，由于根据索引扫描产生的都是随机 IO，其效率比进行全表扫描的顺序 IO 的效率要差很多，即使不会出现重复 IO 的读取，同样会造成整体 IO 性能的下降。
很多比较有经验的 Query 调优专家经常说，当一条 Query 所返回的数据超过了全表的 15% 的时候，就不应该再使用索引扫描来完成这个 Query 了。对于“15%”这个数字我们并不能判定是否很准确，但是之少侧面证明了唯一性太差的字段并不适合创建索引。
◆ 更新非常频繁的字段不适合创建索引；
上面在索引的弊端中我们已经分析过了，索引中的字段被更新的时候，不仅仅需要更新表中的数据，同时还要更新索引数据，以确保索引信息是准确的。这个问题所带来的是 IO 访问量的较大增加，不仅仅影响更新 Query 的响应时间，还会影响整个存储系统的资源消耗，加大整个存储系统的负载。
当然，并不是存在更新的字段就比适合创建索引，从上面判定策略的用语上面也可以看出，是“非常频繁”的字段。到底什么样的更新频率应该算是“非常频繁”呢？每秒，每分钟，还是每小时呢？说实话，这个还真挺难定义的。很多时候还是通过比较同一时间段内被更新的次数和利用该字段作为条件的查询次数来判断，如果通过该字段的查询并不是很多，可能几个小时或者是更长才会执行一次，而更新反而比查询更频繁，那这样的字段肯定不适合创建索引。反之，如果我们通过该字段的查询比较频繁，而且更新并不是特别多，比如查询十几二十次或是更多才可能会产生一次更新，那我个人觉得更新所带来的附加成本也是可以接受的。
◆ 不会出现在 WHERE 子句中的字段不该创建索引；
不会还有人会问为什么吧？自己也觉得这是废话了，哈哈！
单键索引还是组合索引
在大概了解了一下 MySQL 各种类型的索引以及索引本身的利弊与判断一个字段是否需要创建索引之后，我们就需要着手创建索引来优化我们的 Query 了。在很多时候，我们的 WHERE 子句中的过滤条件并不只是针对于单一的某个字段，而是经常会有多个字段一起作为查询过滤条件存在于 WHERE 子句中。在这种时候，我们就必须要作出判断，是该仅仅为过滤性最好的字段建立索引还是该在所有字段（过滤条件中的）上面建立一个组合索引呢？
对于这种问题，很难有一个绝对的定论，我们需要从多方面来分析考虑，平衡两种方案各自的优劣，然后选择一种最佳的方案来解决。因为从上一节中我们了解到了索引在提高某些查询的性能的同时，也会让某些更新的效率下降。而组合索引中因为有多个字段的存在，理论上被更新的可能性肯定比单键索引要大很多，这样可能带来的附加成本也就比单键索引要高。但是，当我们的 WHERE 子句中的查询条件含有多个字段的时候，通过这多个字段共同组成的组合索引的查询效率肯定比仅仅只用过滤条件中的某一个字段创建的索引要高。因为通过单键索引所能过滤的数据并不完整，和通过组合索引相比，存储引擎需要访问更多的记录数，自然就会访问更多的数据量，也就是说需要更高的 IO 成本。
可能有些朋友会说，那我们可以通过创建多个单键索引啊。确实，我们可以将 WHERE 子句中的每一个字段都创建一个单键索引。但是这样真的有效吗？在这样的情况下，MySQL Query Optimizer 大多数时候都只会选择其中的一个索引，然后放弃其他的索引。即使他选择了同时利用两个或者更多的索引通过 INDEX_MERGE 来优化查询，可能所收到的效果并不会比选择其中某一个单键索引更高效。因为如果选择通过 INDEX_MERGE 来优化查询，就需要访问多个索引，同时还要将通过访问到的几个索引进行 merge 操作，所带来的成本可能反而会比选择其中一个最有效的索引来完成查询更高。
在一般的应用场景中，只要不是其中某个过滤字段在大多数场景下都能过滤出90%以上的数据，而且其他的过滤字段会存在频繁的更新，我一般更倾向于创建组合索引，尤其是在并发量较高的场景下更是应该如此。因为当我们的并发量较高的时候，即使我们为每个 Query 节省很少的 IO 消耗，但因为执行量非常大，所节省的资源总量仍然是非常可观的。
当然，我们创建组合索引并不是说就需要将查询条件中的所有字段都放在一个索引中，我们还应该尽量让一个索引被多个 Query 语句所利用，尽量减少同一个表上面索引的数量，减少因为数据更新所带来的索引更新成本，同时还可以减少因为索引所消耗的存储空间。
此外，MySQL 还为我们提供了一个减少优化索引自身的功能，那就是前缀索引。在 MySQL 中，我们可以仅仅使用某个字段的前面部分内容做为索引键来索引该字段，来达到减小索引占用的存储空间和提高索引访问的效率。当然，前缀索引的功能仅仅适用于字段前缀比较随机重复性很小的字段。如果我们需要索引的字段的前缀内容有较多的重复，索引的过滤性自然也会随之降低，通过索引所访问的数据量就会增加，这时候前缀索引虽然能够减少存储空间消耗，但是可能会造成 Query 访问效率的极大降低，反而得不偿失。
Query 的索引选择
在有些场景下，我们的 Query 由于存在多个过滤条件，而这多个过滤条件可能会存在于两个或者更多的索引中。在这种场景下，MySQL Query Optimizer 一般情况下都能够根据系统的统计信息选择出一个针对该 Query 最优的索引完成查询，但是在有些情况下，可能是由于我们的系统统计信息的不够准确完整，也可能是 MySQL Query Optimizer 自身功能的缺陷，会造成他并没有选择一个真正最优的索引而选择了其他查询效率较低的索引。在这种时候，我们就不得不通过认为干预，在 Query 中增加 Hint 提示 MySQL Query Optimizer 告诉他该使用哪个索引而不该使用哪个索引，或者通过调整查询条件来达到相同的目的。
我们这里再次通过在本章第2节“Query 语句优化基本思路和原则”的“仅仅使用最有效的过滤条件”中示例的基础上将 group_message 表的索引做部分调整，然后再进行分析。
在 group_message 上增加如下索引：
create index group_message_author_subject on group_message(author,subject(16));
调整后的索引信息如下（出于篇幅考虑省略了主键索引）：
sky@localhost : example 07:13:38> show indexes from group_message\G
......
*************************** 2. row ***************************
Table: group_message Non_unique: 1 Key_name: group_message_author_subject Seq_in_index: 1 Column_name: author Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment:
*************************** 3. row ***************************
Table: group_message Non_unique: 1 Key_name: group_message_author_subject Seq_in_index: 2 Column_name: subject Collation: A Cardinality: NULL Sub_part: 16 Packed: NULL Null: Index_type: BTREE Comment:
*************************** 4. row ***************************
Table: group_message Non_unique: 1 Key_name: idx_group_message_uid Seq_in_index: 1 Column_name: user_id Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment:
*************************** 5. row ***************************
Table: group_message Non_unique: 1 Key_name: idx_group_message_author Seq_in_index: 1 Column_name: author Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment:
从索引的Sub_part中，我们可以看到 subject 字段是取前16个字符的前缀作为索引键。下面假设我们知道某个用户的 user_id ，nick_name 和 subject 字段的部分前缀信息（weiurazs），希望通过这些条件查询出所有满足上面存在于 group_message 中的信息。我们知道存在三个索引可以被利用：idx_group_message_author ， idx_group_message_uid 和group_message_author_subject，而且也知道每个 user_id 实际上都是和 一个author 分别唯一对应的。所以实际上，无论是使用user_id 和 author（nick_name）中的某一个来作为条件或者两个条件都使用，所得到的数据都是完全一样的。当然，我们还需要subject LIKE 'weiurazs%' 这个条件来过滤 subject 相关的信息。
根据三个索引的组成，和我们的查询条件，我们知道group_message_author_subject 索引可以让我们得到最高的检索效率，因为只有他索引了 subject 相关的信息，subject是我们的查询必须包含的过滤条件。下面我们分别看看使用 user_id ，author 和 两者共同使用时候的执行计划。
sky@localhost : example 07:48:45> EXPLAIN SELECT * FROM group_message -> WHERE user_id = 3 AND subject LIKE 'weiurazs%'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: group_message type: ref possible_keys: idx_group_message_uid key: idx_group_message_uid key_len: 4 ref: const rows: 8 Extra: Using where 1 row in set (0.00 sec)
很明显，这不是我们所期望的执行计划，当然我们并不能责怪 MySQL，因为我们都没有使用 author 来进行过滤，Optimizer 当然不会选择 group_message_author_subject 这个索引，这是我们自己的错。
sky@localhost : example 07:48:49> EXPLAIN SELECT * FROM group_message -> WHERE author = '3' AND subject LIKE 'weiurazs%'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: group_message type: range possible_keys: group_message_author_subject,idx_group_message_author key: idx_group_message_author key_len: 98 ref: NULL rows: 8 Extra: Using where 1 row in set (0.00 sec)
这次我们改为使用 author 作为查询条件了，可MySQL Query Optimizer 仍然没有选择 group_message_author_subject 这个索引，即使我们通过 analyze 分析也是同样的结果。
sky@localhost : example 07:48:57> EXPLAIN SELECT * FROM group_message -> WHERE user_id = 3 AND author = '3' AND subject LIKE 'weiurazs%'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: group_message type: range possible_keys: group_message_author_subject,idx_group_message_uid, idx_group_message_author key: idx_group_message_uid key_len: 98 ref: NULL rows: 8 Extra: Using where 1 row in set (0.00 sec)
同时使用 user_id 和 author 两者的时候，MySQL Query Optimizer 又再次选择了 idx_group_message_uid 这个索引，仍然不是我们期望的结果。
sky@localhost : example 07:51:11> EXPLAIN SELECT * FROM group_message -> FORCE INDEX(idx_group_message_author_subject) -> WHERE user_id = 3 AND author = '3' AND subject LIKE 'weiurazs%'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: group_message type: range possible_keys: group_message_author_subject key: group_message_author_subject key_len: 148 ref: NULL rows: 8 Extra: Using where
在最后，我们不得不利用 MySQL 为我们提供的在优化 Query 时候所使用的高级功能，通过显式告诉 MySQL Query Optimizer 我们要使用哪个索引的 Hint 功能。强制 MySQL 使用 group_message_author_subject 这个索引来完成查询，才达到我们所需要的效果。
或许有些读者会想，会不会是因为选择 group_message_author_subject 这个索引本身就不是一个最有的选择呢？大家请看下面通过mysqlslap进行的实际执行各条 Query 的测试结果：
sky@sky:~$ mysqlslap --create-schema=example --query="SELECT * FROM group_message WHERE user_id = 3 AND subject LIKE 'weiurazs%'" --iterations=10000 Benchmark Average number of seconds to run all queries: 0.021 seconds Minimum number of seconds to run all queries: 0.010 seconds Maximum number of seconds to run all queries: 0.030 seconds Number of clients running queries: 1 Average number of queries per client: 1
sky@sky:~$ mysqlslap --create-schema=example --query="SELECT * FROM group_message WHERE author = '3' AND subject LIKE 'weiurazs%'" --iterations=10000 Benchmark Average number of seconds to run all queries: 0.025 seconds Minimum number of seconds to run all queries: 0.012 seconds Maximum number of seconds to run all queries: 0.031 seconds Number of clients running queries: 1 Average number of queries per client: 1
sky@sky:~$ mysqlslap --create-schema=example --query="SELECT * FROM group_message WHERE user_id = 3 AND author = '3' AND subject LIKE 'weiurazs%'" --iterations=10000 Benchmark Average number of seconds to run all queries: 0.026 seconds Minimum number of seconds to run all queries: 0.013 seconds Maximum number of seconds to run all queries: 0.030 seconds Number of clients running queries: 1 Average number of queries per client: 1
sky@sky:~$ mysqlslap --create-schema=example --query="SELECT * FROM group_message force index(group_message_author_subject) WHERE author = '3' subject LIKE 'weiurazs%'" --iterations=10000 Benchmark Average number of seconds to run all queries: 0.017 seconds Minimum number of seconds to run all queries: 0.010 seconds Maximum number of seconds to run all queries: 0.027 seconds Number of clients running queries: 1 Average number of queries per client: 1
我们可以清晰的看出，通过我们添加 Hint 之后选择 group_message_author_subject 这个索引的 Query 确实比其他的三条要快很多。
通过这个示例，我们可以看出在优化 Query 的时候，选择合适的索引是非常重要的，而且我们也同时实例证明了 MySQL Query Optimizer 并不是任何时候都能够选择出最佳的执行计划，在有些时候，我们不得不通过人为的手工干预来让 MySQL Query Optimizer 改变他的“想法”，而按照我们的思路走。
当然，这个示例仅仅只是告诉了我们选择合适索引的重要性，并且不能任何时候都完全相信 MySQL Query Optimizer，但并没有告诉我们到底该如何来选择一个更合适的索引。下面是我对于选择合适索引的几点建议，并不一定在任何场景下都合适，但在大多数场景下还是比较适用的。
- 对于单键索引，尽量选择针对当前 Query 过滤性更好的索引；
- 在选择组合索引的时候，当前 Query 中过滤性最好的字段在索引字段顺序中排列越靠前越好；
- 在选择组合索引的时候，尽量选择可以能够包含当前 Query 的 WHERE 子句中更多字段的索引；
- 尽可能通过分析统计信息和调整 Query 的写法来达到选择合适索引的目的而减少通过使用 Hint 人为控制索引的选择，因为这会使后期的维护成本增加，同时增加维护所带来的潜在风险。
MySQL中索引的限制
在使用索引的同时，我们还应该了解在 MySQL 中索引存在的限制，以便在索引应用中尽可能的避开限制所带来的问题。下面列出了目前 MySQL 中索引使用相关的限制。
- MyISAM存储引擎索引键长度总和不能超过1000字节；
- BLOB和TEXT类型的列只能创建前缀索引；
- MySQL 目前不支持函数索引；
- 使用不等于（!= 或者 <>）的时候 MySQL 无法使用索引；
- 过滤字段使用了函数运算后（如abs(column)），MySQL无法使用索引；
- Join 语句中 Join 条件字段类型不一致的时候 MySQL 无法使用索引；
- 使用 LIKE 操作的时候如果条件以通配符开始（ '%abc...'）MySQL 无法使用索引；
- 使用非等值查询的时候 MySQL 无法使用 Hash 索引；
在我们使用索引的时候，需要注意上面的这些限制，尤其是要注意无法使用索引的情况，因为这很容易让我们因为疏忽而造成极大的性能隐患。
8.5 Join 的实现原理及优化思路
前面我们已经了解了 MySQL Query Optimizer 的工作原理，学习了 Query 优化的基本原则和思路，理解了索引选择的技巧，这一节我们将围绕 Query 语句中使用非常频繁，且随时可能存在性能隐患的 Join 语句，继续我们的 Query 优化之旅。
Join 的实现原理
在寻找 Join 语句的优化思路之前，我们首先要理解在 MySQL 中是如何来实现 Join 的，只要理解了实现原理之后，优化就比较简单了。下面我们先分析一下 MySQL 中 Join 的实现原理。
在 MySQL 中，只有一种 Join 算法，就是大名鼎鼎的 Nested Loop Join，他没有其他很多数据库所提供的 Hash Join，也没有 Sort Merge Join。顾名思义，Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。
下面我们将通过一个三表 Join 语句示例来说明 MySQL 的Nested Loop Join 实现方式。
注意：由于要展示 Explain 中的一个在 MySQL 5.1.18 才开始出现的输出信息（在之前版本中只是没有输出信息，实际执行过程并没有变化），所以下面的示例环境是 MySQL5.1.26。
Query如下：
select m.subject msg_subject, c.content msg_content from user_group g,group_message m,group_message_content c where g.user_id = 1 and m.group_id = g.group_id and c.group_msg_id = m.id
为了便于示例，我们通过如下操作为 group_message 表增加了一个 group_id 的索引：
create index idx_group_message_gid_uid on group_message(group_id);
然后看看我们的 Query 的执行计划：
sky@localhost : example 11:17:04> explain select m.subject msg_subject, c.content msg_content -> from user_group g,group_message m,group_message_content c -> where g.user_id = 1 -> and m.group_id = g.group_id -> and c.group_msg_id = m.id\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: g type: ref possible_keys: user_group_gid_ind,user_group_uid_ind,user_group_gid_uid_ind key: user_group_uid_ind key_len: 4 ref: const rows: 2 Extra:
*************************** 2. row ***************************
id: 1 select_type: SIMPLE table: m type: ref possible_keys: PRIMARY,idx_group_message_gid_uid key: idx_group_message_gid_uid key_len: 4 ref: example.g.group_id rows: 3 Extra:
*************************** 3. row ***************************
id: 1 select_type: SIMPLE table: c type: ref possible_keys: idx_group_message_content_msg_id key: idx_group_message_content_msg_id key_len: 4 ref: example.m.id rows: 2 Extra:
我们可以看出，MySQL Query Optimizer 选择了 user_group 作为驱动表，首先利用我们传入的条件 user_id 通过 该表上面的索引 user_group_uid_ind 来进行 const 条件的索引 ref 查找，然后以 user_group 表中过滤出来的结果集的 group_id 字段作为查询条件，对 group_message 循环查询，然后再通过 user_group 和 group_message 两个表的结果集中的 group_message 的 id 作为条件 与 group_message_content 的 group_msg_id 比较进行循环查询，才得到最终的结果。
这个过程可以通过如下表达式来表示：
for each record g_rec in table user_group that g_rec.user_id=1{ for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{ for each record c_rec in group_message_content that c_rec.group_msg_id=m_rec.id pass the (g_rec.user_id, m_rec.subject, c_rec.content) row combination to output; } }
下图可以更清晰的标识出实际的执行情况：
假设我们去掉 group_message_content 表上面的 group_msg_id 字段的索引，然后再看看执行计划会变成怎样：
sky@localhost : example 11:25:36> drop index idx_group_message_content_msg_id on group_message_content; Query OK, 96 rows affected (0.11 sec) sky@localhost : example 10:21:06> explain -> select m.subject msg_subject, c.content msg_content -> from user_group g,group_message m,group_message_content c -> where g.user_id = 1 -> and m.group_id = g.group_id -> and c.group_msg_id = m.id\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: g type: ref possible_keys: idx_user_group_uid key: idx_user_group_uid key_len: 4 ref: const rows: 2 Extra:
*************************** 2. row ***************************
id: 1 select_type: SIMPLE table: m type: ref possible_keys: PRIMARY,idx_group_message_gid_uid key: idx_group_message_gid_uid key_len: 4 ref: example.g.group_id rows: 3 Extra:
*************************** 3. row ***************************
id: 1 select_type: SIMPLE table: c type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 96 Extra: Using where; Using join buffer
我们看到不仅仅 user_group 表的访问从 ref 变成了 ALL，此外，在最后一行的 Extra信息从没有任何内容变成为 Using where; Using join buffer，也就是说，对于从 ref 变成 ALL 很容易理解，没有可以使用的索引的索引了嘛，当然得进行全表扫描了，Using where 也是因为变成全表扫描之后，我们需要取得的 content 字段只能通过对表中的数据进行 where 过滤才能取得，但是后面出现的 Using join buffer 是一个啥呢？
实际上，这里的 Join 正是利用到了我们在之前 “MySQL Server 性能优化”一章中所提到的一个 Cache 参数相关的内容，也就是我们通过 join_buffer_size 参数所设置的 Join Buffer。
实际上，Join Buffer 只有当我们的 Join 类型为 ALL（如示例中），index，rang 或者是 index_merge 的时候 才能够使用，所以，在我们去掉 group_message_content 表的 group_msg_id 字段的索引之前，由于 Join 是 ref 类型的，所以我们的执行计划中并没有看到有使用 Join Buffer。
当我们使用了 Join Buffer 之后，我们可以通过下面的这个表达式描述出示例中我们的 Join 完成过程：
for each record g_rec in table user_group{ for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{ put (g_rec, m_rec) into the buffer if (buffer is full) flush_buffer(); } } flush_buffer(){ for each record c_rec in group_message_content that c_rec.group_msg_id = c_rec.id{ for each record in the buffer pass (g_rec.user_id, m_rec.subject, c_rec.content) row combination to output; } empty the buffer; }
当然，如果通过类似于上面的图片来展现或许大家会觉得更容易理解一些，如下：
通过上面的示例，我想大家应该对 MySQL 中 Nested Join 的实现原理有了一个了解了，也应该清楚 MySQL 使用 Join Buffer 的方法了。当然，这里并没有涉及到 外连接的内容，实际对于外连接来说，可能存在的区别主要是连接顺序以及组合空值记录方面。
Join 语句的优化
在明白了 MySQL 中 Join 的实现原理之后，我们就比较清楚的知道该如何去优化一个一个 Join 语句了。
- 尽可能减少 Join 语句中的 Nested Loop 的循环总次数；
如何减少 Nested Loop 的循环总次数？最有效的办法只有一个，那就是让驱动表的结果集尽可能的小，这也正是在本章第二节中的优化基本原则之一“永远用小结果集驱动大的结果集”。
为什么？因为驱动结果集越大，意味着需要循环的次数越多，也就是说在被驱动结果集上面所需要执行的查询检索次数会越多。比如，当两个表（表 A 和 表 B） Join 的时候，如果表 A 通过 WHERE 条件过滤后有 10 条记录，而表 B 有20条记录。如果我们选择表 A 作为驱动表，也就是被驱动表的结果集为20，那么我们通过 Join 条件对被驱动表（表 B）的比较过滤就会有10次。反之，如果我们选择表 B 作为驱动表，则需要有 20 次对表 A 的比较过滤。
当然，此优化的前提条件是通过 Join 条件对各个表的每次访问的资源消耗差别不是太大。如果访问存在较大的差别的时候（一般都是因为索引的区别），我们就不能简单的通过结果集的大小来判断需要 Join 语句的驱动顺序，而是要通过比较循环次数和每次循环所需要的消耗的乘积的大小来得到如何驱动更优化。
- 优先优化Nested Loop 的内层循环；
不仅仅是在数据库的 Join 中应该做的，实际上在我们优化程序语言的时候也有类似的优化原则。内层循环是循环中执行次数最多的，每次循环节约很小的资源，在整个循环中就能节约很大的资源。
- 保证 Join 语句中被驱动表上 Join 条件字段已经被索引；
保证被驱动表上 Join 条件字段已经被索引的目的，正是针对上面两点的考虑，只有让被驱动表的 Join 条件字段被索引了，才能保证循环中每次查询都能够消耗较少的资源，这也正是优化内层循环的实际优化方法。
- 当无法保证被驱动表的 Join 条件字段被索引且内存资源充足的前提下，不要太吝惜 Join Buffer 的设置；
当在某些特殊的环境中，我们的 Join 必须是 All，Index，range或者是 index_merge类型的时候，Join Buffer就会派上用场了。在这种情况下，Join Buffer的大小将对整个 Join 语句的消耗起到非常关键的作用。
8.6 ORDER BY，GROUP BY 和 DISTINCT 优化
除了常规的 Join 语句之外，还有一类 Query 语句也是使用比较频繁的，那就是 ORDER BY，GROUP BY 以及 DISTINCT 这三类查询。考虑到这三类查询都涉及到数据的排序等操作，所以我将他们放在了一起，下面就针对这三类 Query 语句做基本的分析。
ORDER BY 的实现与优化
在 MySQL 中，ORDER BY 的实现有如下两种类型：
◆ 一种是通过有序索引而直接取得有序的数据，这样不用进行任何排序操作即可得到满足客户端要求的有序数据返回给客户端；
◆ 另外一种则需要通过 MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回给客户端。
下面我们就针对这两种实现方式做一个简单的分析。首先分析一下第一种不用排序的实现方式。同样还是通过示例来说话吧：
sky@localhost : example 09:48:41> EXPLAIN -> SELECT m.id,m.subject,c.content -> FROM group_message m,group_message_content c -> WHERE m.group_id = 1 AND m.id = c.group_msg_id -> ORDER BY m.user_id\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: m type: ref possible_keys: PRIMARY,idx_group_message_gid_uid key: idx_group_message_gid_uid key_len: 4 ref: const rows: 4 Extra: Using where
*************************** 2. row ***************************
id: 1 select_type: SIMPLE table: c type: ref possible_keys: group_message_content_msg_id key: group_message_content_msg_id key_len: 4 ref: example.m.id rows: 11 Extra:
看看上面的这个 Query 语句，明明有 ORDER BY user_id，为什么在执行计划中却没有排序操作呢？其实这里正是因为 MySQL Query Optimizer 选择了一个有序的索引来进行访问表中的数据（idx_group_message_gid_uid），这样，我们通过 group_id 的条件得到的数据已经是按照 group_id 和 user_id 进行排序的了。而虽然我们的排序条件仅仅只有一个user_id，但是我们的 WHERE 条件决定了返回数据的 group_id 全部一样，也就是说不管有没有根据 group_id 来进行排序，返回的结果集都是完全一样的。我们可以通过如下的图示来描述整个执行过程：
图中的 Table A 和 Table B 分别为上面 Query 中的 group_message 和 gruop_message_content 这两个表。
这种利用索引实现数据排序的方法是 MySQL 中实现结果集排序的最佳做法，可以完全避免因为排序计算所带来的资源消耗。所以，在我们优化 Query 语句中的 ORDER BY 的时候，尽可能利用已有的索引来避免实际的排序计算，可以很大幅度的提升 ORDER BY 操作的性能。在有些 Query 的优化过程中，即使为了避免实际的排序操作而调整索引字段的顺序，甚至是增加索引字段也是值得的。当然，在调整索引之前，同时还需要评估调整该索引对其他 Query 所带来的影响，平衡整体得失。
如果没有索引利用的时候，MySQL 又如何来实现排序呢？这时候 MySQL 无法避免需要通过相关的排序算法来将存储引擎返回的数据进行排序运算了。下面我们再针对这种实现方式进行相应的分析。
在 MySQL 第二种排序实现方式中，必须进行相应的排序算法来实现数据的排序。MySQL 目前可以通过两种算法来实现数据的排序操作。
- 取出满足过滤条件的用于排序条件的字段以及可以直接定位到行数据的行指针信息，在 Sort Buffer 中进行实际的排序操作，然后利用排好序之后的数据根据行指针信息返回表中取得客户端请求的其他字段的数据，再返回给客户端；
- 根据过滤条件一次取出排序字段以及客户端请求的所有其他字段的数据，并将不需要排序的字段存放在一块内存区域中，然后在 Sort Buffer 中将排序字段和行指针信息进行排序，最后再利用排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配合并结果集，再按照顺序返回给客户端。
上面第一种排序算法是 MySQL 一直以来就有的排序算法，而第二种则是从 MySQL4.1 版本才开始增加的改进版排序算法。第二种算法与第一种相比较，主要优势就是减少了数据的二次访问。在排序之后不需要再一次回到表中取数据，节省了 IO 操作。当然，第二种算法会消耗更多的内存，正是一种典型的通过内存空间换取时间的优化方式。下面我们同样通过一个实例来看看当 MySQL 不得不使用排序算法的时候的执行计划，仅仅只是更改一下排序字段：
sky@localhost : example 10:09:06> explain -> select m.id,m.subject,c.content -> FROM group_message m,group_message_content c -> WHERE m.group_id = 1 AND m.id = c.group_msg_id -> ORDER BY m.subject\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: m type: ref possible_keys: PRIMARY,idx_group_message_gid_uid key: idx_group_message_gid_uid key_len: 4 ref: const rows: 4 Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 1 select_type: SIMPLE table: c type: ref possible_keys: group_message_content_msg_id key: group_message_content_msg_id key_len: 4 ref: example.m.id rows: 11 Extra:
大概一看，好像整个执行计划并没有什么区别啊？但是细心的读者朋友可能已经发现，在 group_message 表的 Extra 信息中，多了一个“Using filesort”的信息，实际上这就是 MySQL Query Optimizer 在告诉我们，他需要进行排序操作才能按照客户端的要求返回有序的数据。执行图示如下：
这里我们看到了，MySQL 在取得第一个表的数据之后，先根据排序条件将数据进行了一次 filesort，也就是排序操作。然后再利用排序后的结果集作为驱动结果集来通过 Nested Loop Join 访问第二个表。当然，大家不要误解，这个 filesort 并不是说通过磁盘文件进行排序，仅仅只是告诉我们进行了一个排序操作。
上面，我们看到了排序结果集来源仅仅只是单个表的比较简单的 filesort 操作。而在我们实际应用中，很多时候我们的业务要求可能并不是这样，可能需要排序的字段同时存在于两个表中，或者 MySQL 在经过一次 Join 之后才进行排序操作。这样的排序在 MySQL 中并不能简单的里利用 Sort Buffer 进行排序，而是必须先通过一个临时表将之前 Join 的结果集存放入临时表之后在将临时表的数据取到 Sort Buffer 中进行操作。下面我们通过再次更改排序要求来示例这样的执行计划，当我们选择通过 group_message_content 表上面的 content 字段来进行排序之后：
sky@localhost : example 10:22:42> explain -> select m.id,m.subject,c.content -> FROM group_message m,group_message_content c -> WHERE m.group_id = 1 AND m.id = c.group_msg_id -> ORDER BY c.content\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: m type: ref possible_keys: PRIMARY,idx_group_message_gid_uid key: idx_group_message_gid_uid key_len: 4 ref: const rows: 4 Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1 select_type: SIMPLE table: c type: ref possible_keys: group_message_content_msg_id key: group_message_content_msg_id key_len: 4 ref: example.m.id rows: 11 Extra:
这时候的执行计划中出现了“Using temporary”，正是因为我们的排序操作需要在两个表 Join 之后才能进行，下图展示了这个 Query 的执行过程：
首先是 Table A 和 Table B 进行 Join，然后结果集进入临时表，再进行 filesort，最后得到有序的结果集数据返回给客户端。
上面我们通过两个不同的示例展示了当 MySQL 无法避免要使用相应的排序算法进行排序操作的时候的实现原理。虽然在排序过程中所使用的排序算法有两种，但是两种排序的内部实现机制大体上差不多。
当我们无法避免排序操作的时候，我们又该如何来优化呢？很显然，我们应该尽可能让 MySQL 选择使用第二种算法来进行排序。这样可以减少大量的随机IO操作，很大幅度的提高排序工作的效率。
- 加大 max_length_for_sort_data 参数的设置；
在 MySQL 中，决定使用第一种老式的排序算法还是新的改进算法的依据是通过参数 max_length_for_sort_data 来决定的。当我们所有返回字段的最大长度小于这个参数值的时候，MySQL 就会选择改进后的排序算法，反之，则选择老式的算法。所以，如果我们有充足的内存让 MySQL 存放需要返回的非排序字段的时候，可以加大这个参数的值来让 MySQL 选择使用改进版的排序算法。
- 去掉不必要的返回字段；
当我们的内存并不是很充裕的时候，我们不能简单的通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法，因为如果那样可能会造成 MySQL 不得不将数据分成很多段然后进行排使用序，这样的结果可能会得不偿失。在这种情况下，我们就需要去掉不必要的返回字段，让我们的返回结果长度适应 max_length_for_sort_data 参数的限制。
- 增大 sort_buffer_size 参数设置；
增大 sort_buffer_size 并不是为了让 MySQL 可以选择改进版的排序算法，而是为了让 MySQL 可以尽量减少在排序过程中对需要排序的数据进行分段，因为这样会造成 MySQL 不得不使用临时表来进行交换排序。
GROUP BY 的实现与优化
由于 GROUP BY 实际上也同样需要进行排序操作，而且与 ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。
在 MySQL 中，GROUP BY 的实现同样有多种（三种）方式，其中有两种方式会利用现有的索引信息来完成 GROUP BY，另外一种为完全无法使用索引的场景下使用。下面我们分别针对这三种实现方式做一个分析。
- 使用松散（Loose）索引扫描实现 GROUP BY
何谓松散索引扫描实现 GROUP BY 呢？实际上就是当 MySQL 完全利用索引扫描来实现 GROUP BY 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。
下面我们通过一个示例来描述松散索引扫描实现 GROUP BY，在示例之前我们需要首先调整一下 group_message 表的索引，将 gmt_create 字段添加到 group_id 和 user_id 字段的索引中：
sky@localhost : example 08:49:45> create index idx_gid_uid_gc -> on group_message(group_id,user_id,gmt_create); Query OK, rows affected (0.03 sec) Records: 96 Duplicates: 0 Warnings: 0 sky@localhost : example 09:07:30> drop index idx_group_message_gid_uid -> on group_message; Query OK, 96 rows affected (0.02 sec) Records: 96 Duplicates: 0 Warnings: 0
然后再看如下 Query 的执行计划：
sky@localhost : example 09:26:15> EXPLAIN -> SELECT user_id,max(gmt_create) -> FROM group_message -> WHERE group_id < 10 -> GROUP BY group_id,user_id\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: group_message type: range possible_keys: idx_gid_uid_gc key: idx_gid_uid_gc key_len: 8 ref: NULL rows: 4 Extra: Using where; Using index for group-by 1 row in set (0.00 sec)
我们看到在执行计划的 Extra 信息中有信息显示“Using index for group-by”，实际上这就是告诉我们，MySQL Query Optimizer 通过使用松散索引扫描来实现了我们所需要的 GROUP BY 操作。
下面这张图片描绘了扫描过程的大概实现：
要利用到松散索引扫描实现 GROUP BY，需要至少满足以下几个条件：
◆ GROUP BY 条件字段必须在同一个索引中最前面的连续位置；
◆ 在使用GROUP BY 的同时，只能使用 MAX 和 MIN 这两个聚合函数；
◆ 如果引用到了该索引中 GROUP BY 条件之外的字段条件的时候，必须以常量形式存在；
为什么松散索引扫描的效率会很高？
因为在没有WHERE子句，也就是必须经过全索引扫描的时候， 松散索引扫描需要读取的键值数量与分组的组数量一样多，也就是说比实际存在的键值数目要少很多。而在WHERE子句包含范围判断式或者等值表达式的时候， 松散索引扫描查找满足范围条件的每个组的第1个关键字，并且再次读取尽可能最少数量的关键字。
- 使用紧凑（Tight）索引扫描实现 GROUP BY
紧凑索引扫描实现 GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取恶的数据来完成 GROUP BY 操作得到相应结果。
sky@localhost : example 08:55:14> EXPLAIN -> SELECT max(gmt_create) -> FROM group_message -> WHERE group_id = 2 -> GROUP BY user_id\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: group_message type: ref possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc key: idx_gid_uid_gc key_len: 4 ref: const rows: 4 Extra: Using where; Using index 1 row in set (0.01 sec)
这时候的执行计划的 Extra 信息中已经没有“Using index for group-by”了，但并不是说 MySQL 的 GROUP BY 操作并不是通过索引完成的，只不过是需要访问 WHERE 条件所限定的所有索引键信息之后才能得出结果。这就是通过紧凑索引扫描来实现 GROUP BY 的执行计划输出信息。
下面这张图片展示了大概的整个执行过程：
在 MySQL 中，MySQL Query Optimizer 首先会选择尝试通过松散索引扫描来实现 GROUP BY 操作，当发现某些情况无法满足松散索引扫描实现 GROUP BY 的要求之后，才会尝试通过紧凑索引扫描来实现。
当 GROUP BY 条件字段并不连续或者不是索引前缀部分的时候，MySQL Query Optimizer 无法使用松散索引扫描，设置无法直接通过索引完成 GROUP BY 操作，因为缺失的索引键信息无法得到。但是，如果 Query 语句中存在一个常量值来引用缺失的索引键，则可以使用紧凑索引扫描完成 GROUP BY 操作，因为常量填充了搜索关键字中的“差距”，可以形成完整的索引前缀。这些索引前缀可以用于索引查找。而如果需要排序GROUP BY结果，并且能够形成索引前缀的搜索关键字，MySQL还可以避免额外的排序操作，因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。
- 使用临时表实现 GROUP BY
MySQL 在进行 GROUP BY 操作的时候要想利用所有，必须满足 GROUP BY 的字段必须同时存放于同一个索引中，且该索引是一个有序索引（如 Hash 索引就不能满足要求）。而且，并不只是如此，是否能够利用索引来实现 GROUP BY 还与使用的聚合函数也有关系。
前面两种 GROUP BY 的实现方式都是在有可以利用的索引的时候使用的，当 MySQL Query Optimizer 无法找到合适的索引可以利用的时候，就不得不先读取需要的数据，然后通过临时表来完成 GROUP BY 操作。
sky@localhost : example 09:02:40> EXPLAIN -> SELECT max(gmt_create) -> FROM group_message -> WHERE group_id > 1 and group_id < 10 -> GROUP BY user_id\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: group_message type: range possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc key: idx_gid_uid_gc key_len: 4 ref: NULL rows: 32 Extra: Using where; Using index; Using temporary; Using filesort
这次的执行计划非常明显的告诉我们 MySQL 通过索引找到了我们需要的数据，然后创建了临时表，又进行了排序操作，才得到我们需要的 GROUP BY 结果。整个执行过程大概如下图所展示：
当 MySQL Query Optimizer 发现仅仅通过索引扫描并不能直接得到 GROUP BY 的结果之后，他就不得不选择通过使用临时表然后再排序的方式来实现 GROUP BY了。
在这样示例中即是这样的情况。 group_id 并不是一个常量条件，而是一个范围，而且 GROUP BY 字段为 user_id。所以 MySQL 无法根据索引的顺序来帮助 GROUP BY 的实现，只能先通过索引范围扫描得到需要的数据，然后将数据存入临时表，然后再进行排序和分组操作来完成 GROUP BY。
对于上面三种 MySQL 处理 GROUP BY 的方式，我们可以针对性的得出如下两种优化思路：
- 尽可能让 MySQL 可以利用索引来完成 GROUP BY 操作，当然最好是松散索引扫描的方式最佳。在系统允许的情况下，我们可以通过调整索引或者调整 Query 这两种方式来达到目的；
- 当无法使用索引完成 GROUP BY 的时候，由于要使用到临时表且需要 filesort，所以我们必须要有足够的 sort_buffer_size 来供 MySQL 排序的时候使用，而且尽量不要进行大结果集的 GROUP BY 操作，因为如果超出系统设置的临时表大小的时候会出现将临时表数据 copy 到磁盘上面再进行操作，这时候的排序分组操作性能将是成数量级的下降；
至于如何利用好这两种思路，还需要大家在自己的实际应用场景中不断的尝试并测试效果，最终才能得到较佳的方案。此外，在优化 GROUP BY 的时候还有一个小技巧可以让我们在有些无法利用到索引的情况下避免 filesort 操作，也就是在整个语句最后添加一个以 null 排序（ORDER BY null）的子句，大家可以尝试一下试试看会有什么效果。
DISTINCT 的实现与优化
DISTINCT 实际上和 GROUP BY 的操作非常相似，只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以，DISTINCT 的实现和 GROUP BY 的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成 DISTINCT 的时候，MySQL 只能通过临时表来完成。但是，和 GROUP BY 有一点差别的是，DISTINCT 并不需要进行排序。也就是说，在仅仅只是 DISTINCT 操作的 Query 如果无法仅仅利用索引完成操作的时候，MySQL 会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行 filesort 操作。当然，如果我们在进行 DISTINCT 的时候还使用了 GROUP BY 并进行了分组，并使用了类似于 MAX 之类的聚合函数操作，就无法避免 filesort 了。
下面我们就通过几个简单的 Query 示例来展示一下 DISTINCT 的实现。
- 首先看看通过松散索引扫描完成 DISTINCT 的操作：
sky@localhost : example 11:03:41> EXPLAIN SELECT DISTINCT group_id -> FROM group_message\G
*************************** 1. row ***************************
id: 1 SELECT_type: SIMPLE table: group_message type: range possible_keys: NULL key: idx_gid_uid_gc key_len: 4 ref: NULL rows: 10 Extra: Using index for group-by 1 row in set (0.00 sec)
我们可以很清晰的看到，执行计划中的 Extra 信息为“Using index for group-by”，这代表什么意思？为什么我没有进行 GROUP BY 操作的时候，执行计划中会告诉我这里通过索引进行了 GROUP BY 呢？其实这就是于 DISTINCT 的实现原理相关的，在实现 DISTINCT的过程中，同样也是需要分组的，然后再从每组数据中取出一条返回给客户端。而这里的 Extra 信息就告诉我们，MySQL 利用松散索引扫描就完成了整个操作。当然，如果 MySQL Query Optimizer 要是能够做的再人性化一点将这里的信息换成“Using index for distinct”那就更好更容易让人理解了，呵呵。
- 我们再来看看通过紧凑索引扫描的示例：
sky@localhost : example 11:03:53> EXPLAIN SELECT DISTINCT user_id -> FROM group_message -> WHERE group_id = 2\G
*************************** 1. row ***************************
id: 1 SELECT_type: SIMPLE table: group_message type: ref possible_keys: idx_gid_uid_gc key: idx_gid_uid_gc key_len: 4 ref: const rows: 4 Extra: Using WHERE; Using index 1 row in set (0.00 sec)
这里的显示和通过紧凑索引扫描实现 GROUP BY 也完全一样。实际上，这个 Query 的实现过程中，MySQL 会让存储引擎扫描 group_id = 2 的所有索引键，得出所有的 user_id，然后利用索引的已排序特性，每更换一个 user_id 的索引键值的时候保留一条信息，即可在扫描完所有 gruop_id = 2 的索引键的时候完成整个 DISTINCT 操作。
- 下面我们在看看无法单独使用索引即可完成 DISTINCT 的时候会是怎样：
sky@localhost : example 11:04:40> EXPLAIN SELECT DISTINCT user_id -> FROM group_message -> WHERE group_id > 1 AND group_id < 10\G
*************************** 1. row ***************************
id: 1 SELECT_type: SIMPLE table: group_message type: range possible_keys: idx_gid_uid_gc key: idx_gid_uid_gc key_len: 4 ref: NULL rows: 32 Extra: Using WHERE; Using index; Using temporary 1 row in set (0.00 sec)
当 MySQL 无法仅仅依赖索引即可完成 DISTINCT 操作的时候，就不得不使用临时表来进行相应的操作了。但是我们可以看到，在 MySQL 利用临时表来完成 DISTINCT 的时候，和处理 GROUP BY 有一点区别，就是少了 filesort。实际上，在 MySQL 的分组算法中，并不一定非要排序才能完成分组操作的，这一点在上面的 GROUP BY 优化小技巧中我已经提到过了。实际上这里 MySQL 正是在没有排序的情况下实现分组最后完成 DISTINCT 操作的，所以少了 filesort 这个排序操作。
- 最后再和 GROUP BY 结合试试看：
sky@localhost : example 11:05:06> EXPLAIN SELECT DISTINCT max(user_id) -> FROM group_message -> WHERE group_id > 1 AND group_id < 10 -> GROUP BY group_id\G
*************************** 1. row ***************************
id: 1 SELECT_type: SIMPLE table: group_message type: range possible_keys: idx_gid_uid_gc key: idx_gid_uid_gc key_len: 4 ref: NULL rows: 32 Extra: Using WHERE; Using index; Using temporary; Using filesort 1 row in set (0.00 sec)
最后我们再看一下这个和 GROUP BY 一起使用带有聚合函数的示例，和上面第三个示例相比，可以看到已经多了 filesort 排序操作了，因为我们使用了 MAX 函数的缘故。
对于 DISTINCT 的优化，和 GROUP BY 基本上一致的思路，关键在于利用好索引，在无法利用索引的时候，确保尽量不要在大结果集上面进行 DISTINCT 操作，磁盘上面的IO操作和内存中的IO操作性能完全不是一个数量级的差距。
8.7 小结
本章重点介绍了 MySQL Query 语句相关的性能调优的部分思路和方法，也列举了部分的示例，希望能够帮助读者朋友在实际工作中开阔一点点思路。虽然本章涉及到的内容包含了最初的索引设计，到编写高效 Query 语句的一些原则，以及最后对语句的调试，但 Query 语句的调优远不只这些内容。很多的调优技巧，只有到在实际的调优经验中才会真正体会，真正把握其精髓。所以，希望各位读者朋友能多做实验，以理论为基础，以事实为依据，只有这样，才能不断提升自己对 Query 调优的深入认识。
扫一扫
250
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
