本篇内容,Query语句优化的思路及基本原则。

思路和原则是两个范畴的内容。思路是告诉我们怎么做,第一步做什么、第二步做什么之类的。原则呢,是我们在做的过程中所要依据的准则。明白了这个,下面我们来依次做介绍。

一、Query语句优化的思路

Query优化到底该如何着手?苦李通常的思路是这样的,优化哪里收益最大(定位)、定位地方的性能瓶颈是什么、优化所要达到的目标是什么、使用Explain做具体分析。下面对苦李的这个优化思路做简单介绍,希望对各位能有所启发。

1、找出最需要优化的Query

什么是最需要优化的Query?简单来说,就是收益最大化。

对于很多项目经验不足的研发 or DBA来说,提到性能优化,很可能首先想到的通过慢查询日志,看看哪个query语句耗时最长,然后对其加以优化。但是,这种case的优化是不是我们所说的最需要优化的Query呢?不一定!

举个栗子,假设有一个Query每小时执行10 000次,每次需要20个IO,而另外一个Query每小时执行10次,每次需要20 000个IO。

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

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

最后,从对整个系统的影响来分析。一个频繁执行的高并发Query的危险性比一个低并发的Query要大很多。当一个低并发的Query执行计划有误时,所带来的影响只是该Query请求者的体验会变差,对整体系统的影响并不会特别突出,至少还属于可控范围。但是,如果一个高并发的Query执行计划有误,那它带来的后果很可能就是灾难性的,很多时候可能连自救的机会都没有,就会让整个系统崩溃掉。

所以,定位优化的目标的时候,一定不能想当然,要根据自己应用系统做全面分析,然后得出结论,确定优化方向。

2、最需要优化的Query的性能瓶颈是什么

通过第一步确定我们要优化哪个query,接下来,应该做什么?反问自己一句,这个Query有什么问题。找到问题,才能对症下药。

通常我们在确定这个Query的性能瓶颈时,首先考虑到的应该是CPU瓶颈还是IO瓶颈,数据运算过多消耗的CPU资源,数据访问过多消耗的是IO资源,确定了这个问题,我们即可头痛医头脚痛医脚了。

一般来说,在MySQL 5.0系列版本中,可以通过系统自带的PROFILING功能很容易地找出一个Query的瓶颈所在。

3、明确优化目标

没有目标的行为,将会是一个漫无目的且及其低效的行为。

对应到我们的优化过程中,如果不给自己设定一个明确、合理的优化目标,那你的优化工作将会永远进行下去,且效果极为不明显。

如何设定我们的优化目标?这在一个难以量化的过程中,是很蛋疼的一件事,尤其在项目庞大,设计模块众多的时候。

但是,迎难而上作为一个优秀程序员的基本素养,目标还是必须要有滴,这个目标不能过于理想化(不切实际),也不能过于放任(达不到优化的目标)。

通常来说,设定优化目标时,要做到以下几点:

  • 首先,要对当前的数据库有充分的认识;
  • 其次,要对优化的目标query所涉及到的数据对象有十足的把握(这里的数据对象一般来说是指数据表的一些情况);
  • 最后,还要了解这个query在整个应用系统中的影响范围(这里通常是指应用的哪些功能用到了这个query)。

在充分了解以上信息之后,基本可以得出该Query应该满足的一个性能范围,这也就是优化目标范围,然后就是寻找相应的优化手段来解决问题了。

如果该Query实现的应用系统功能比较重要,则必须让目标更偏向于理想值,即使在其他某些方面作出一些让步与牺牲也是需要的,比如调整schema设计,调整索引组成等。

而如果该Query所实现的是一些并不是太关键的功能,那可以让目标偏向悲观值,尽量保证其他更重要的Query性能。这种时候,即使须要调整商业需求,减少功能实现,也不得不作出让步。

4、使用Explain对Query进行具体分析

至此,优化的目标确定了,开干吧。

如何开始呢?苦李建议各位,从Explain开始。因为只有Explain才能告诉你,这个Query在数据库中是以一个怎样的执行计划来实现的。

但是,有一点必须清楚,Explain只是用来获取一个Query在当前状态的数据库中的执行计划的,在优化之前,我们必须根据优化目标在头脑中有一个清晰的目标执行计划。只有这样,优化的目标才有意义。对于不符合预期的执行计划须要不断分析Query的写法和数据库对象的信息,继续调整尝试,直至得到预期的结果。

当然,并不一定每次预设的执行计划都是最优的,在不断调整测试的过程中,如果发现MySQLQuery Optimizer所选择的执行计划实际执行效果确实比自己预设的好,则应该选择使用MySQLQuery Optimizer所生成的执行计划。这种情况,在实际的生产过程中往往是最为常见的。

二、Query语句优化的原则

在介绍MySQL Query语句优化的原则之前,我们先来了解一下MySQL的两种排序算法,因为了解其算法,才能让我们更易理解下面有些个原则的所以然。

第一种排序算法:这种算法主要应用于MySQL4.1之前的版本,其实现逻辑是,先将排序所需的字段及相关数据的指针信息取出,然后在指定区域进行排序,排序完成之后,再根据排序的索引依次取出真正所需的数据,很明显,这种算法有一个天然的弊端,无论如何都需要进行两次IO操作。

第二种排序算法:这个算法是MySQL4.1之后的升级版,通常情况来说,这种算法会一次性将所需数据取出,在排序区内完成排序后,将query结构返回给客户端。但是,当查询结果集的数据量大于max_length_for_sort_data(MySQL的一个配置项)的设置时,MySQL为节省内存资源,会自动转换为第一种排序算法。

两种算法相比,第二种算法由于减少了大量的IO操作,所以能大幅度提高Query性能,但是这种算法并非是毫无限制的,基于系统的通盘考虑,我们要合理使用内存资源,所以要通过MySQL的max_length_for_sort_data配置项,巧妙设定算法二到算法一的阈值。

介绍完MySQL的排序算法,我们接着看Query语句的优化原则:

原则一:用小结果集驱动大的结果集

首先,明确一点,小结果集不等于小表!小结果集不等于小表!!小结果集不等于小表!!!重要的事强调三遍。为什么?因为大表经过WHERE条件过滤之后返回的结果集并不一定就比小表所返回的大,也许更小。在这种情况下如果仍然采用小表驱动大表,就会得到相反的性能效果。

其次,我们再来聊聊,为什么要用小结果集驱动大结果集。

因为MySQL的Join查询是通过Nested Loop(循环嵌套)来实现的。驱动结果集越大,所需要循环就越多,那么被驱动表的访问次数自然也就越多,而每次访问被驱动表,即使需要的逻辑IO很少,循环次数多了,总量也不可能小。

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

注:以小结果集驱动大结果集,并非只是Nested Loop的算法的最佳选择,例如:在Oracle中的Hash join中,这种方式亦是最优。

原则二:在索引列完成排序

排序操作是非常消耗CPU资源的。为了尽可能的减少CPU的消耗,我们应尽可能在索引列完成排序,必要情况下,也可以为了排序专门针对某个Column做索引优化。

因为索引本身具有有序性,在通过索引查找数据的结果集里,依次读取结果集中的数据,即可以得到有序的数据列表,从而能够避免重新排序的操作,所以能够大幅度的提高查询性能。

原则三:用什么取什么Columns

如上面排序算法的介绍,当Query取出的字段过多时,很可能造成MySQL放弃性能较好的算法二,而不得不使用算法一。这样为了一些不需要的字段,一方面,极有可能大量增加系统的IO操作;另一方面,数据量大了,在做排序的时候还需要浪费不必要的内存占用。所以,无论从哪方面来讲,这种做法都是得不偿失的。

在这个原则上,很多开发者往往不是不知道、不是不明白、不是记不住,就是懒,明明用一个“*”就能解决的问题,为什么要我一个一个去写查询字段呢。

对于这种人,我只想回答一个字,大写的“滚”

原则四:尽可能不适用join和子查询

MySQL系统的内部锁机制,导致MySQL在高并发的场景中表现不是很好。InnoDB存储引擎由于实现了行级锁定可能还要稍微好一些,如果使用的是MyISAM存储引擎,并发一旦较高,性能下降非常明显。

Query语句所涉及的表越多,须要锁定的资源就越多。也就是说,越复杂的Join语句,锁定的资源也就越多,所阻塞的其他线程也就越多。相反,如果将比较复杂的Query语句分拆成多个较为简单的Query语句分步执行,每次锁定的资源也就会少很多,所阻塞的其他线程也要少一些。

基于以上原因,所以我们应尽可能避免使用join查询和子查询。

程序员日常

近日,有关一段“报BUG的礼仪”在朋友圈广为流传,原话如下:

不要对一个程序员说:你的代码有BUG

他的第一个反应:1、你的环境有问题吧;2、傻逼,你会用吗?

如果你委婉的说:你这个程序和预期的有点不一致,你看是不是我用的方法不对。

他本能的会想:卧草,是不是出BUG了

------------------ 分割 -----------------------

看,程序员这帮傻逼就是这么好骗!

扫码关注李苦李公众号

李苦李公众号

标签: 数据库, 性能优化