理解MySQL的Query Optimizer

MySQL Optimizer是一个专门负责优化SELECT 语句的优化器模块，它主要的功能就是通过计算分析系统中收集的各种统计信息，为客户端请求的Query 给出他认为最优的执行计划，也就是他认为最优的数据检索方式。

Query 语句优化基本思路和原则

在分析如何优化MySQL Query 之前，我们需要先了解一下Query 语句优化的基本思路和原则。一般来说，Query 语句的优化思路和原则主要体现在以下几个方面：
1. 优化更需要优化的Query；
2. 定位优化对象的性能瓶颈；
3. 明确的优化目标；
4. 从Explain 入手；
5. 多使用profile
6. 永远用小结果集驱动大的结果集；
7. 尽可能在索引中完成排序；
8. 只取出自己需要的Columns；
9. 仅仅使用最有效的过滤条件；
10. 尽可能避免复杂的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 好像有点慢，需要优化一下，然后就开始一个一个优化方法去轮番尝试。这样很可能整个优化过程会消耗大量的人力和时间成本，甚至可能到最后还是得不到一个好的优化结果。这就像看病一样，医生必须要清楚的知道我们病的根源才能对症下药。如果只是知道我们什么地方不舒服，然后就开始通过各种药物尝试治疗，那这样所带来的后果可能就非常严重了。
所以，在拿到一条需要优化的Query 之后，我们首先要判断出这个Query 的瓶颈到底是IO 还是CPU。到底是因为在数据访问消耗了太多的时间，还是在数据的运算（如分组排序等）方面花费了太多资源？

明确的优化目标

当我们定为到了一条Query 的性能瓶颈之后，就需要通过分析该Query 所完成的功能和Query 对系统的整体影响制订出一个明确的优化目标。

如何设定优化目标？一般来说，我们首先需要清楚的了解数据库目前的整体状态，同时也要清楚的知道数据库中与该Query 相关的数据库对象的各种信息，而且还要了解该Query 在整个应用系统中所实现的功能。了解了数据库整体状态，我们就能知道数据库所能承受的最大压力，也就清楚了我们能够接受的最悲观情况。把握了该Query 相关数据库对象的信息，我们就应该知道实现该Query 的消耗最理想情况下需要消耗多少资源，最糟糕又需要消耗多少资源。最后，通过该Query 所实现的功能点在整个应用系统中的重要地位，我们可以大概的分析出该Query 可以占用的系统资源比例，而且我们也能够知道该Query 的效率给客户带来的体验影响到底有多大。

当我们清楚了这些信息之后，我们基本可以得出该Query 应该满足的一个性能范围是怎样的，这也就是我们的优化目标范围，然后就是通过寻找相应的优化手段来解决问题了。如果该Query 实现的应用系统功能比较重要，我们就必须让目标更偏向于理想值一些，即使在其他某些方面作出一些让步与牺牲，比如调整schema 设计，调整索引组成等，可能都是需要的。而如果该Query 所实现的是一些并不是太关键的功能，那我们可以让目标更偏向悲观值一些，而尽量保证其他更重要的Query 的性能。这种时候，即使需要调整商业需求，减少功能实现，也不得不应该作出让步。

从Explain 入手

为什么从Explain 入手？因为只有Explain 才能告诉你，这个Query 在数据库中是以一个什么样的执行计划来实现的。

永远用小结果集驱动大的结果集

很多人喜欢在优化SQL 的时候说用小表驱动大表，个人认为这样的说法不太严谨。为什么？因为大表经过WHERE 条件过滤之后所返回的结果集并不一定就比小表所返回的结果集大，可能反而更小。

在这种情况下如果仍然采用小表驱动大表，就会得到相反的性能效果。其实这样的结果也非常容易理解，在MySQL 中的Join，只有Nested Loop 一种Join 方式，也就是MySQL 的Join 都是通过嵌套循环来实现的。驱动结果集越大，所需要循环的此时就越多，那么被驱动表的访问次数自然也就越多，而每次访问被驱动表，即使需要的逻辑IO 很少，循环次数多了，总量自然也不可能很小，而且每次循环都不能避免的需要消耗CPU ，所以CPU 运算量也会跟着增加。所以，如果我们仅仅以表的大小来作为驱动表的判断依据，假若小表过滤后所剩下的结果集比大表多很多，结果就是需要的嵌套循环中带来更多的循环次数，反之，所需要的循环次数就会更少，总体IO 量和CPU 运算量也会少。而且，就算是非Nested Loop 的Join 算法，如Oracle 中的Hash Join，同样是小结果集驱动大的结果集是最优的选择。

所以，在优化Join Query 的时候，最基本的原则就是“小结果集驱动大结果集”，通过这个原则来减少嵌套循环中的循环次数，达到减少IO 总量以及CPU 运算的次数。

可以看看这篇：联表查询时始终以小结果集驱动大结果集

尽可能在索引中完成排序

只取出自己需要的Columns

仅仅使用最有效的过滤条件

很多人在优化Query 语句的时候很容易进入一个误区，那就是觉得WHERE 子句中的过滤条件越多越好，实际上这并不是一个非常正确的选择。

为什么说过滤条件多不一定是好事呢？请看下面示例：

需求：查找某个用户在所有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 优化的过程中，能不用子查询的时候就尽量不要使用子查询。

充分利用 Explain 和 Profi l ing

Explain的使用

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 。

Profiling 的使用

MySQL 的Query Profiler 是一个使用非常方便的Query 诊断分析工具，通过该工具可以获取一条Query 在整个执行过程中多种资源的消耗情况，如CPU，IO，IPC，SWAP 等，以及发生的PAGE FAULTS，CONTEXT SWITCHE 等等，同时还能得到该Query 执行过程中MySQL 所调用的各个函数在源文件中的位置。下面我们看看Query Profiler 的具体用法。

开启profiling 参数

root@localhost : (none) 10:53:11> set profiling=1; 
Query OK, 0 rows affected (0.00 sec)

通过执行“set profiling”命令，可以开启关闭Query Profiler 功能。

执行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 信息了。

获取系统中保存的所有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 的概要信息。

针对单个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” 来获取，各位读者朋友可以自行测试熟悉。

合理设计并利用索引

因为索引对我们的Query 性能影响很大，所以我们更应该深入理解MySQL 中索引的基本实现，以及不同索引之间的区别，才能分析出如何设计出最优的索引来最大幅度的提升Query 的执行效率。

在MySQL 中，主要有四种类型的索引，分别为：B-Tree 索引，Hash 索引，Fulltext 索引和RTree索引，下面针对这四种索引的基本实现方式及存储结构做一个大概的分析。

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 相邻的后一个LeafNode 的指针信息，这主要是为了加快检索多个相邻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 索引结构的特殊性，其检索效率非常的高，索引的检索可以一次定位，而不需要像BTree索引需要从根节点再到枝节点最后才能访问到页节点这样多次IO 访问，所以Hash 索引的效率要远高于B-Tree 索引。

可能很多人又会有疑问了，既然Hash 索引的效率要比B-Tree 高很多，为什么大家不都用Hash索引而还要使用B-Tree 索引呢？任何事物都是有两面性的，，Hash 索引也一样，虽然Hash 索引检索效率非常之高，但是Hash 索引本身由于其实的特殊性也带来了很多限制和弊端，主要有以下这些：

1. Hash 索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；

由于Hash 索引所比较的是进行Hash 运算之后的Hash 值，所以Hash 索引只能用于等值的过滤，而不能用于基于范围的过滤，因为经过相应的Hash 算法处理之后的Hash 值的大小关系，并不能保证还和Hash 运算之前完全一样。

2. Hash 索引无法被利用来避免数据的排序操作；

由于Hash 索引中存放的是经过Hash 计算之后的Hash 值，而且Hash 值的大小关系并不一定和Hash 运算前的键值的完全一样，所以数据库无法利用索引的数据来避免任何和排序运算；

3. Hash 索引不能利用部分索引键查询；

对于组合索引，Hash 索引在计算Hash 值的时候是组合索引键合并之后再一起计算Hash 值，而不是单独计算Hash 值，所以当我们通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用到；

4. Hash 索引在任何时候都不能避免表扫面；

前面我们已经知道，Hash 索引是将索引键通过Hash 运算之后，将Hash 运算结果的Hash 值和所对应的行指针信息存放于一个Hash 表中，而且由于存在不同索引键存在相同Hash 值的可能，所以即使我们仅仅取满足某个Hash 键值的数据的记录条数，都无法直接从Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较而得到相应的结果。

5. Hash 索引遇到大量Hash 值相等的情况后性能并不一定就会比B-Tree 索引高；

对于选择性比较低的索引键，如果我们创建Hash 索引，那么我们将会存在大量记录指针信息存与同一个Hash 值相关连。这样要定位某一条记录的时候就会非常的麻烦，可能会浪费非常多次表数据的访问，而造成整体性能的地下。

Full-text 索引

Full-text 索引也就是我们常说的全文索引，目前在MySQL 中仅有MyISAM 存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有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 资源的消耗。

索引的弊端

虽然，索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。

假设我们在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 所占用的空间也会不断增长。所以索引还会带来存储空间资源消耗的增长。

如何判定是否需要创建索引

下面列出一些基本的判定策略来帮助我们分析是否需要创建索引。

1.较频繁的作为查询条件的字段应该创建索引；

提高数据查询检索的效率最有效的办法就是减少需要访问的数据量，从上面所了解到的索引的益处中我们知道了，索引正是我们减少通过索引键字段作为查询条件的Query 的IO 量的最有效手段。所以一般来说我们应该为较为频繁的查询条件字段创建索引。

2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；

唯一性太差的字段主要是指哪些呢？如状态字段，类型字段等等这些字段中存方的数据可能总共就是那么几个几十个值重复使用，每个值都会存在于成千上万或是更多的记录中。对于这类字段，我们完全没有必要创建单独的索引的。因为即使我们创建了索引，MySQL Query Optimizer 大多数时候也不会去选择使用，如果什么时候MySQL Query Optimizer 抽了一下风选择了这种索引，那么非常遗憾的告诉你，这可能会带来极大的性能问题。由于索引字段中每个值都含有大量的记录，那么存储引擎在根据索引访问数据的时候会带来大量的随机IO，甚至有些时候可能还会出现大量的重复IO。

3.更新非常频繁的字段不适合创建索引；

上面在索引的弊端中我们已经分析过了，索引中的字段被更新的时候，不仅仅需要更新表中的数据，同时还要更新索引数据，以确保索引信息是准确的。这个问题所带来的是IO 访问量的较大增加，不仅仅影响更新Query 的响应时间，还会影响整个存储系统的资源消耗，加大整个存储系统的负载。

那如何定义“非常频繁”呢？每秒，每分钟，还是每小时呢？说实话，这个还真挺难定义的。很多时候还是通过比较同一时间段内被更新的次数和利用该字段作为条件的查询次数来判断。

4.不会出现在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 告诉他该使用哪个索引而不该使用哪个索引，或者通过调整查询条件来达到相同的目的。

SELECT * FROM group_message force 
index(group_message_author_subject) WHERE author = '3' subject LIKE 'weiurazs%'

下面是我对于选择合适索引的几点建议，并不一定在任何场景下都合适，但在大多数场景下还是比较适用的。

1. 对于单键索引，尽量选择针对当前Query 过滤性更好的索引；
2. 在选择组合索引的时候，当前Query 中过滤性最好的字段在索引字段顺序中排列越靠前越好；
3. 在选择组合索引的时候，尽量选择可以能够包含当前Query 的WHERE 子句中更多字段的索引；
4. 尽可能通过分析统计信息和调整Query 的写法来达到选择合适索引的目的而减少通过使用Hint 人为控制索引的选择，因为这会使后期的维护成本增加，同时增加维护所带来的潜在风险。

MySQL 中索引的限制

在使用索引的同时，我们还应该了解在MySQL 中索引存在的限制，以便在索引应用中尽可能的避开限制所带来的问题。下面列出了目前MySQL 中索引使用相关的限制。

1. MyISAM 存储引擎索引键长度总和不能超过1000 字节；
2. BLOB 和TEXT 类型的列只能创建前缀索引；
3. MySQL 目前不支持函数索引；
4. 使用不等于（!= 或者<>）的时候MySQL 无法使用索引；
5. 过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引；
6. Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引；
7. 使用LIKE 操作的时候如果条件以通配符开始（ ‘%abc…’）MySQL 无法使用索引；
8. 使用非等值查询的时候MySQL 无法使用Hash 索引；

在我们使用索引的时候，需要注意上面的这些限制，尤其是要注意无法使用索引的情况，因为这很容易让我们因为疏忽而造成极大的性能隐患。

Join 的实现原理及优化思路

在MySQL 中，只有一种Join 算法，就是大名鼎鼎的Nested Loop Join。顾名思义，Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

三表Join示例

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:

这个过程可以通过如下表达式来表示：

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; 
} 
}

Join 语句的优化

1. 尽可能减少Join 语句中的Nested Loop 的循环总次数；

如何减少Nested Loop 的循环总次数？最有效的办法只有一个，那就是让驱动表的结果集尽可能的小

2. 优先优化Nested Loop 的内层循环；

3.保证Join 语句中被驱动表上Join 条件字段已经被索引；

4. 当无法保证被驱动表的Join 条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer 的设置；

ORDER BY，GROUP BY 和 DI STI NCT 优化

ORDER BY，GROUP BY 以及DISTINCT 这三类查询。考虑到这三类查询都涉及到数据的排序等操作，所以我将他们放在了一起。

ORDER BY 的实现与优化

在MySQL 中，ORDER BY 的实现有如下两种类型：
◆ 一种是通过有序索引而直接取得有序的数据，这样不用进行任何排序操作即可得到满足客户端要求的有序数据返回给客户端；
◆ 另外一种则需要通过MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回给客户端。

1. 加大max_length_for_sort_data 参数的设置；

2. 去掉不必要的返回字段；

3. 增大sort_buffer_size 参数设置；

GROUP BY 的实现与优化

在MySQL 中，GROUP BY 的实现同样有多种（三种）方式，其中有两种方式会利用现有的索引信息来完成GROUP BY，另外一种为完全无法使用索引的场景下使用。下面我们分别针对这三种实现方式做一个分析。

1. 使用松散（Loose）索引扫描实现GROUP BY

何谓松散索引扫描实现GROUP BY 呢？实际上就是当MySQL 完全利用索引扫描来实现GROUP BY 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。

2. 使用紧凑（Tight）索引扫描实现GROUP BY

紧凑索引扫描实现GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取恶的数据来完成GROUP BY 操作得到相应结果。

3. 使用临时表实现GROUP BY

MySQL 在进行GROUP BY 操作的时候要想利用所有，必须满足GROUP BY 的字段必须同时存放于同一个索引中，且该索引是一个有序索引（如Hash 索引就不能满足要求）。而且，并不只是如此，是否能够利用索引来实现GROUP BY 还与使用的聚合函数也有关系。

对于上面三种MySQL 处理GROUP BY 的方式，我们可以针对性的得出如下两种优化思路：

1. 尽可能让MySQL 可以利用索引来完成GROUP BY 操作，当然最好是松散索引扫描的方式最佳。在系统允许的情况下，我们可以通过调整索引或者调整Query 这两种方式来达到目的；

2. 当无法使用索引完成GROUP BY 的时候，由于要使用到临时表且需要filesort，所以我们必须要有足够的sort_buffer_size 来供MySQL 排序的时候使用，而且尽量不要进行大结果集的GROUP BY 操作，因为如果超出系统设置的临时表大小的时候会出现将临时表数据copy 到磁盘上面再进行操作，这时候的排序分组操作性能将是成数量级的下降；

DISTINCT 的实现与优化

DISTINCT 实际上和GROUP BY 的操作非常相似，只不过是在GROUP BY 之后的每组中只取出一条记录而已。

高效的模型设计

首先当然是三大范式

第一范式：要求有主键，并且要求每一个字段原子性不可再分

第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖

第三范式：所有非主键字段和主键字段之间不能产生传递依赖

适度冗余- 让Query 尽两减少Join

大字段垂直分拆- summary 表优化

什么样的字段适合于从表中拆分出去呢？

首要肯定是大字段，其次是和表中其他字段相比访问频率明显要少很多。

大表水平分拆- 基于类型的分拆优化

统计表- 准实时优化

合适的数据类型

优化数据类型提高性能的主要原理在于以下几个方面：

1. 通过选用更“小”的数据类型减少存储空间，使查询相同数据需要的IO 资源降低；
2. 通过合适的数据类型加速数据的比较；

时间类型

有DATETIME，DATE 和TIMESTAMP 这三种，TIMESTAMP存储空间最小，4个字节，但是不能存储1970 年之前的时间。

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/4746.html

MySQL 数据库 Query 的优化详解数据库