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

Anonymity | | 访问(188)

  一般来说,Query语句的优化思路和原则主要体现在以下几个方面:

  (1)优化更需要优化的Query;

  (2)定位优化对象的性能瓶颈;

  (3)明确优化目标;

  (4)从Explain入手;

  (5)多使用Profile;

  (6)永远用小结果集驱动大的结果集;

  (7)尽可能在索引中完成排序;

  (8)只取自己需要的Columns;

  (9)仅仅使用最有效的过滤条件;

  (10)尽可能避免复杂的Join和子查询。

  一、优化更须要优化的Query

  一般来说,高并发低消耗(相对)的Query对整个系统的影响远比低并发高消耗的大。

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

  二、定位优化对象的性能瓶颈

  当我们拿到一条须要优化的Query时,第一件事情是什么?是反问自己这条Query有什么问题?我为什么要优化他?只有明白了这些问题,才能知道须要做什么,才能够找到问题的关键。不能只是觉得某个Query好像有点慢,须要优化一下,然后就开始一个一个优化方法去轮番尝试。这样很可能会消耗大量的人力和时间成本,甚至可能到最后还是得不到一个好的优化结果。在拿到一条须要优化的Query之后,首先要判断出这个Query的瓶颈到底是IO还是CPU,到底是因为在数据访问上消耗了太多的时间,还是在数据的运算(如分组排序等)方面花费了太多资源。

  三、明确的优化目标

  在定位了一条Query的性能瓶颈之后,就须要通过分析该Query所完成的功能和Query对系统的整体影响制订出一个明确的优化目标。没有一个明确的目标,优化过程将是一个漫无目的而且低效的过程,很难达到一个理想的效果,尤其是对于一些实现应用中较为重要功能点的Query。

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

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

  四、从Explain入手

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

  但是,有一点必须清楚,Explain只是用来获取一个Query在当前状态的数据库中的执行计划的,在优化之前,我们必须根据优化目标在头脑中有一个清晰的目标执行计划。只有这样,优化的目标才有意义。

  在优化任何一个SQL语句之前,都应该在自己头脑中有一个预定的执行计划,然后不断地调整尝试,再借助Explain来验证调整的结果是否满足预定的执行计划。对于不符合预期的执行计划须要不断分析Query的写法和数据库对象的信息,继续调整尝试,直至得到预期的结果。

  五、多使用Profile

  将一个臃肿的UserInfo表拆分开,分为UserInfo和UserProfile,其中UserInfo内只放基本信息和经常调用的信息,而UserProfile则存放用户表的大数据信息和一些不经常用到的信息,从而在使用中提高了效率!

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

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

  其实这也非常容易理解,在MySQL中,只有Nested Loop一种Join方式,也就是说MySQL的Join都是通过嵌套循环来实现的。驱动结果集越大,所需要循环就越多,那么被驱动表的访问次数自然也就越多,而每次访问被驱动表,即使需要的逻辑IO很少,循环次数多了,总量也不可能小,而且每次循环都不能避免消耗CPU,所以CPU运算量也会跟着增加。如果仅仅以表的大小来作为驱动表的判断依据,假若小表过滤后所剩下的结果集比大表多很多,结果就会在嵌套循环中带来更多的循环次数,反之,所需要的循环次数就会更少,总体IO量和CPU运算量也会更少。在非Nested Loop的Join算法中,如Oracle中的 HashJoin,小结果集驱动大的结果集同样是最优的选择。所以,在优化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语句的性能优劣最关键的就是要让它选择一条最佳的数据访问路径,做到通过访问最少的数据量完成自己的任务。

  十、尽可能避免复杂的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的执行计划,可MySQL 6.0离我们投入生产环境使用恐怕还有很遥远的一段时间。所以,在Query优化的过程中,能不用子查询就尽量不要用。