mysql的query优化

我们统称由客户端发送给服务端的命令叫query。mysql server的连接线程接收到客户端发送过来的query之后会对query进行相应的分析，根据分析数据，mysql的查询优化器(QueryOptimizer)会得出一个mysql认为最优化的数据访问方式，即“执行计划”，然后根据得出的“执行计划”通过存储引擎的相应的接口来获取数据，将取出的数据进行一定的处理后按照客户端要求的格式返回。

mysql最大的性能瓶颈就是在数据的存取操作上面，即所谓的磁盘io。对于取出同一份数据，可以有很多种不同的query，不同的query得到不同的执行计划，这就导致mysql执行这些不同query读取量会不同，所消耗的系统资源也不同，所以实现功能一样的不同query，在性能上会有很大的差别。

接下来就来分析怎样对query进行优化。

QueryOptimizer

QueryOptimizer即mysql的select语句优化器，它主要的作用就是根据分析数据为query制定它认为最优的执行计划，即数据的检索方式。用“它认为”描述主要是因为有时候它会出问题，给出的执行计划并不是最优的，这个时候就得人为的去干预了。

优化步骤：

1.优化常数关系。即会优化处理一些常量表达式的预算，直接换算成常量值。比如：where column1= column2 and column2='a'，该查询条件会被优化成where column1=‘a’ and column2='a'，where column1 = 1+2会被优化成where column1=3；

2.对查询条件进行简化和转换，删除一些无用条件，并调整结构。比如where 0=0 and column1=‘a’，会被优化成 where column='a'；

3.优化join连接，决定join连接类型；

4. 换位，比如：where -5 = column1，会转换成 where column1 = -5，这是为了更好的利用索引；

5. Hint优化，hint便是上面所说的人为的去干预执行计划的生成，常用功能有：强制索引(force index)，有时候执行计划不会按照我们希望的索引去获取数据，使用强制索引便会解决该问题，比如：select * from table1 force index(column1) where...。除了强制索引之外还有忽略索引(ignore index)，关闭查询缓冲(sql_no_cache)等，详细了解可以去google一下。

经过机上几步优化步骤，optimizer便会给出它认为最优的执行计划。

query优化方法

1. 优化更需要优化的query，即优化那些对性能照成影响更大的query；

2. 分析定位query的瓶颈，找出问题所在；

3. 明确优化目标，根据实际情况，不能太过于理想；

4. 使用explain和profile分析，使用explain和profile能验证对query调整的结果，不断的调整与验证，直到达到预期；

5.在做join时，永远用小结果集驱动大结果集，不是指用小表驱动大表；

6.尽可能用索引完成排序，如果有order by等需要排序的话，因为索引本来就已经是排好序的，所以根据索引取出的数据也是排好序的，如果没有使用到索引，mysql在取出数据后还需要对数据进行排序；

7. 只取出需要的column，取出的数据会占用内存，消耗网络传输量，所以只取出需要的column；

8. 使用有效的过滤条件；

9. 避免复杂的语句，复杂的语句意味着执行时间长，在高并发情况下，必然会引起性能问题。

explain介绍

使用explain可以知道一个query经过optimizer之后得到怎样的执行计划，可以验证得出的执行计划与我们预期的效果是否一致，以及我们对query调整是否有效，所以explain是个非常有用的工具。

explain输出结果包括几个部分：

1. id，在执行计划中的序列号；

2. select_type，使用的查询类型，包括以下几种：

• dependent subquery：子查询中内层的第一个select，依赖于外部查询的结果集；
• dependent union：子查询中的union，且为union中从第二个select开始的后面所有select，同样依赖于外部查询的结果集；
• primary：子查询中的最外层查询，注意并不是主键查询；
• simple：出子查询或者union查询以外的所有查询；
• subquery：子查询内层查询的第一个select，结果不依赖与外部查询结果集；
• uncacheable subquery：结果集无法缓存的子查询；
• union：union查询中的第二个select开始的后面所有查询，第一个select为primary；
• union result：union中的合并结果

3. table，显示访问的数据库表；

4. type，显示对数据库表的访问方式，主要有以下几种类型：

• all：全表扫描；
• const：读常量，通常表示只会有一条匹配记录；
• eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；
• fulltext：通过全文索引获取；
• index：全索引扫描；
• index_merge：查询中同时使用两个或多个索引，然后对索引结果进行merge之后再读取表数据；
• index_subquery：子查询中返回的字段是一个索引或者组合索引，但不是一个主键或者唯一索引；
• rang：索引范围扫描；
• ref：join语句中被驱动表索引引用查询；
• ref_or_null：与ref的唯一区别就是在使用索引引用查询之外再增加一个空值的查询；
• system：系统表，表中只有一行数据；
• unique_subquery：子查询中的返回结果字段组合是主键或者唯一索引

5. possible_keys，查询中可以使用的索引，如果没有索引则是null；

6. key，从possible_keys中选择使用的索引；

7. key_len，被选中使用的索引长度；

8. ref，列出是通过常量（const），还是某个表的某个字段来过滤的；

9. extra，查询中每一步实现的额外细节信息，主要有以下内容：

• distinct：查找distinct值，所以当找到第一条匹配的结果后，将停止该值的查询而转为后面其他值的查询；
• full scan on null key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用；
• impossible where noticed after reading const tables：optimizer通过统计信息判断出不可能存在结果；
• no tables：query语句中使用from dual或者不包含任何from子句；
• not exists：在某些左连接中optimizer所通过改变原有query的组成而使用的优化方法，可以部分减少数据访问的次数；
• range checked for each record：没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一；
• select tables optimized away：当使用聚合函数来访问某个索引数据的时候，optimizer会通过索引而直接一次定位到所需的数据行完成整个查询；
• using filesort：当query中包含order by操作，而又无法使用索引来完成排序时，optimizer会选择相应的排序算法来完成排序；
• using index：所需要的数据只需要在index即可全部获得，而不需要再到表中去取数据；
• using index for group-by：当query中包含order by或者distinct排序操作时，而且所需字段都在index中；
• using temporary：表示操作必须使用到临时表；
• using where：表示不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据；

下面用一个简单的例子说明一下，从物品表里获取分类名字为test的所有物品，explain的执行结果如下：

 从结果里可以看出optimizer给出的执行计划首先是对product表进行全表扫描，extra显示using where，表示不是获取product表里的所有数据，这里没有用到索引，所以也无法使用索引就可以获取全部数据；接着访问item表，type显示ref，表示是索引扫描，ref显示mall.p.id，表示使用product的id字段进行扫描，因为我们只需要获取item的主键字段，使用索引就可以获取，所以extra显示using index。

profiling介绍

使用query profiler可以用来定位query的性能瓶颈，是消耗cpu计算太多，还是IO操作太多，使用profiler可以很容易的定位到问题。

具体用法：

1. 开启profiling参数

通过 set profiling =1 命令可以开启profiler功能。

2. 执行query

3. 获取系统中保存的所有query的profile信息，当开启profiler功能之后，mysql就会自动记录所有执行query的profiler信息。

通过执行 show profiles命令可以获取当前mysql系统中保存所有query执行的profile的信息。

4. 针对单个query获取详细的profile信息

通过show profiles命令获取相关query的query_id，通过query_id来获取某条query的详细profile信息。

可以通过详细的profile信息来定位性能瓶颈，然后便可以采取措施来进行优化了。

合理设计索引

索引的正确设计与使用可以大大的提高query执行的性能，我们需要深入理解索引的实现，原理，以及不同索引之间的区别，这样我们才能分析如何设计出合理的索引来提高query的执行性能。

mysql主要有四类索引：B-Tree索引，Hash索引，Fulltext索引和R-Tree索引，这里主要介绍常用的B-Tree索引。

B-Tree索引

mysql大部分存储引擎包括常用的MyIsam和Innodb都使用B-Tree索引，索引的物理文件使用平衡树（balance tree）的结构来存储，所需要的实际数据都存放在叶节点里，而且到任何一个的叶节点的最短路径的长度都完全相同。Innodb的索引结构对B-Tree做了略微的改造，我们称为B+Tree，它的叶节点除了存放索引键相关的信息之外，还存储了指向与该叶节点相邻的后一个叶节点的指针信息，这样便可以加快相邻叶节点的检索效率。

在Innodb存储引擎中，索引又分为两类，一种是聚簇索引形式的主键索引，另外一种是非聚簇索引形式的普通索引。两种索引的差别主要在于叶节点存放的数据，主键索引叶节点存放的是表的实际数据，这些数据以主键值有序排列。而普通索引的叶节点除了存放索引键的相关信息外，还存放了主键的相关信息指向主键索引。

这种不同索引存放数据的差别决定了按索引访问数据的性能是不一样的，在Innodb中按主键索引访问数据的效率是很高的，因为数据和主键存放在一起。而普通索引读取数据则要先找到主键存放的位置，然后根据主键的位置来访问数据。

MyIsam的主键索引和普通索引存放数据的方式一样，在B-Tree的叶节点中除了存放索引相关信息外再存放能直接定位到数据文件中存放数据信息的数据行。

索引的利弊

索引的好处除了可以提高检索效率之外，还可以降低数据的排序成本。在mysql中，数据时按照索引键值排序后存放的，所以如果query中含有排序操作而恰好排序字段是和索引字段一致时，optimizer给出的执行计划告诉mysql取出数据后不用再排序了，因为根据索引取出的数据已经是排好序的，这样便可以节省因为排序操作所消耗的内存和cpu资源。

当然索引也会带来一些弊端，因为创建索引需要单独存放一份索引数据，这样会占用一部分空间，除此之外，对数据库的增删改都会影响索引数据，导致索引数据重新排序，消耗IO量和cpu计算量。

如何创建索引

既然索引有利有弊，不可能完全不创建索引也不可能在所有条件字段上都创建索引，所以我们需要判断如何创建索引：

• 频繁的作为条件字段进行查询的字段应该创建索引；
• 唯一性太差的字段不要创建索引，比如表示性别的字段，因为很多时候optimizer不会选用这类字段作为索引；
• 更新频繁的字段不适合创建索引，因为索引是单独存放的数据，每一次更新都会导致索引文件重新排序；
• 没有作为条件字段的字段不要创建索引。

组合索引与前缀索引

当where条件中存在多个字段的时候我们就得考虑是否需要创建组合索引，我们不能对于每个字段都创建一个单键索引，因为optimizer很多时候都只会选择一个单键索引，即使optimizer会通过index_merge来优化查询，但是因为进行merge操作，所以性能未必会比单键索引的性能高。

那怎样决定是否要创建组合索引呢，在很多的应用场景中，只要不是一个单键索引就能过滤90%以上的数据，或者其它的字段不会频繁的被更新，都倾向于创建组合索引。在创建组合索引的时候还得考虑创建的组合索引被更多的query所使用，尽量减少同一个表上的索引，减少创建索引带来的性能开销。

前缀索引

http://blog.csdn.net/tonyxf121/article/details/7762570，这篇文章里有介绍。

索引的选择

在优化query的时候选择索引很重要，但是该怎样选择索引呢：

• 对于单键索引，尽量选择针对当前query过滤性更好的索引；
• 对于组合索引，过滤性更好的字段应该在索引字段顺序中更靠前；
• 在选择组合索引时，尽量选择可以包含当前query的where条件中更多字段的索引；
• 尽可能通过分析统计信息和调整query来达到选择合适索引的目的而减少通过使用Hint人为控制索引的选择。

mysql中索引的限制

• MyIsam索引键长度不能超过1000个字节；
• BLOB和TEXT类型的列只能创建前缀索引；
• mysql不支持函数索引；
• 使用不等于（!=或者<>）的时候mysql无法使用索引；
• 过滤字段使用了函数运算后，mysql无法使用索引；
• join语句中join条件字段类型不一致的时候mysql无法使用索引；
• 使用like操作的时候如果条件以通配符开始("%abc%")，mysql无法使用索引；

索引对于query的性能尤为重要，在mysql的使用中一定要合适使用索引。