MySQL Query优化:基本原则

Published on 2016 - 06 - 06

理解MySQL的Query Optimizer

MySQL Query Optimizer是什么?

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

当MySQL Query Optimizer接收到从Query Parser (解析器)过来的Query时,会根据MySQL Query语句的相应语法对该Query进行分解分析,同时还会做很多其他的计算转化工作,如常量转化,无效内容删除,常量计算等。所有这些工作都是为了Optimizer分析出最优的数据检索方式,也就是常说的执行计划。

MySQL Query Optimizer基本工作原理

在分析MySQL Query 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 Optimizer并不是一个纯粹的CBO(Cost Base Optimizer),而是在CBO的基础上增加了一个被称为Heuristic Optimize(启发式优化)的功能。也就是说,MySQL Query Optimizer在优化一个Query认为的最优执行计划时,并不一定完全按照系数据库的元信息和系统统计信息,而是在此基础上增加了某些特定的规则。其实就是在CBO的实现中增加了部分RBO(Rule Base Optimizer)的功能,以确保在某些特殊场景下控制Query按照预定的方式生成执行计划。

当客户端向MySQL请求一条Query,命令解析器模块完成请求分类,区别出是SELECT并转发给MySQL Query Optimizer时,MySQL Query Optimizer首先会对整条Query进行优化,处理掉一些常量表达式的预算,直接换算成常量值。并对Query中的查询条件进行简化和转换,如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有),看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据Query进行写相应的计算分析,然后再得出最后的执行计划。

Query Optimizer是一个数据库软件非常核心的功能,虽然说起来只是简单的几句话,但在MySQL内部,MySQL Query Optimizer实际上经过了很多复杂的运算分析,才得出最后的执行计划。

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每小时执行10000次,每次需要20个IO,而另外一个Query每小时执行10次,每次需要20000个IO。

首先通过IO消耗来分析。可以看出,两个Query每小时所消耗的IO总数目是一样的,都是200000IO/小时。假设优化第一个Query,从20个IO降低到18个IO,也就是降低了2个IO,则节省了2×10000=20000(IO/小时)。而如果希望通过优化第二个Query达到相同的效果,必须要让每个Query减少20000/10=2000IO。可以看出第一个Query节省2个IO即可达到第二个Query节省2000个IO相同的效果。

其次,通过CPU消耗来分析。原理和上面一样,只要让第一个Query节省一小块资源,就可以让整个系统节省出一大块资源,尤其是在排序、分组这些对CPU消耗比较多的操作中更加明显。

最后,从对整个系统的影响来分析。一个频繁执行的高并发Query的危险性比一个低并发的Query要大很多。当一个低并发的Query执行计划有误时,所带来的影响只是该Query请求者的体验会变差,对整体系统的影响并不会特别突出,至少还属于可控范围。但是,如果一个高并发的Query执行计划有误,那它带来的后果很可能就是灾难性的,很多时候可能连自救的机会都没有,就会让整个系统崩溃掉。我曾经就遇到过这样一个案例,一个并发度较高的Query语句执行计划有误,系统顷刻间崩溃,当重新启动数据库提供服务时,系统负载直线飙升,甚至都来不及登录数据库查看有哪些Active的线程在执行哪些Query。如果是遇到一个并发不太高的Query执行计划有误,至少还可以控制整个系统,不至于系统被直接压跨,甚至连问题根源都难以抓到。

定位优化对象的性能瓶颈

当我们拿到一条须要优化的Query时,第一件事情是什么?是反问自己这条Query有什么问题?我为什么要优化他?只有明白了这些问题,才能知道须要做什么,才能够找到问题的关键。不能只是觉得某个Query好像有点慢,须要优化一下,然后就开始一个一个优化方法去轮番尝试。这样很可能会消耗大量的人力和时间成本,甚至可能到最后还是得不到一个好的优化结果。这就像看病一样,医生必须要清楚病的根源才能对症下药。如果只是知道什么地方不舒服,然后就开始通过各种药物尝试治疗,那后果可能就非常严重了。

所以,在拿到一条须要优化的Query之后,首先要判断出这个Query的瓶颈到底是IO还是CPU,到底是因为在数据访问上消耗了太多的时间,还是在数据的运算(如分组排序等)方面花费了太多资源。

一般来说,在MySQL 5.0系列版本中,可以通过系统自带的PROFILING功能清楚地找出一个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 Query Optimizer所选择的执行计划实际执行效果确实比自己预设的好,则应该选择使用MySQL Query Optimizer所生成的执行计划。

上面的优化思路,只是一个优化的基本方向,实际操作还需要读者结合具体应用场景不断的测试实践。当然也并不一定所有的情况都要严格遵循这样一个思路,规则是死的,人是活的,只有更合理的方法,没有最合理的规则。

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

很多人喜欢在优化SQL的时候使用小表驱动大表,个人认为这不太严谨。为什么?因为大表经过WHERE条件过滤之后返回的结果集并不一定就比小表所返回的大,也许更小。在这种情况下如果仍然采用小表驱动大表,就会得到相反的性能效果。

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

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

尽可能在索引中完成排序

排序操作是非常消耗CPU的操作,当系统设置不当或Query取出的字段过多时,还可以造成MySQL不得不放弃优化后的排序算法,而使用较为古老的须要两次IO读取表数据的排序算法,使排序效率非常低下。

利用索引进行排序操作,主要是利用了索引的有序性。在通过索引进行检索的过程中,就已经得到了有序的数据访问顺序,依次读取结果数据后就不须要进行排序操作,进而避免了此操作,提高了需要有序结果集的Query的性能。

只取出自己需要的Columns

任何时候在Query中都只取出需要的Columns,尤其是在需要排序的Query中。为什么?

对于任何Query,返回的数据都须要通过网络数据包传回给客户端,取出的Column越多,须要传输的数据量自然会越大,不论是从网络带宽方面考虑还是从网络传输的缓冲区来看,这都是一个浪费。

如果是须要排序的Query,其影响就更大了。在MySQL中存在两种排序算法,一种是在MySQL 4.1之前的算法,实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出,然后在设定的排序区(通过参数sort_buffer_size设定)中进行排序,完成排序之后再次通过行指针信息取出所需的Columns,也就是说这种算法须要访问两次数据。第二种排序算法是从MySQL 4.1版本开始使用的改进算法,一次性将所需的Columns全部取出,在排序区排序后直接将数据返回给请求客户端。改行算法只须要访问一次数据,减少了大量的随机IO,极大地提高了排序Query语句的效率。但是,这种改进后的排序算法一次性取出并缓存的数据比第一种算法要多很多,如果我们将并不需要的Columns也取出来,就会极大地浪费排序过程所需要的内存。在MySQL 4.1之后的版本中,可以通过设置max_length_for_sort_data参数来控制MySQL选择第一种排序算法还是第二种。当取出的所有大字段总大小大于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的执行计划如代码示例1所示:

方案二:仅仅将用户ID作为过滤条件放在WHERE子句中来查询,Query的执行计划如示例代码2所示:

方案三:仅将用户nick_name作为过滤条件放在WHERE子句中来查询,Query的执行计划如示例代码3所示:

初略一看三个执行计划好像都挺好啊,每一个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的短暂响应时间而提高整体处理能力是非常值得的。优化本身就是一门平衡与取舍的艺术,只有懂得取舍,平衡整体,才能让系统更优。

参考文档