mysql sql语句优化的几个原则和思路


在分析如何优化 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每小时执行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执行计划有误,那它带来的后果很可能就是灾难性的,很多时候可能连自救的机会都没有,就会让整个系统崩溃掉。我曾经就遇到过这样一个案例,一个 并发度较高的Query语句执行计划有误,系统顷刻间崩溃,当重新启动数据库提供服务时,系统负载直线飙升,甚至都来不及登录数据库查看有哪些 Active 的线程在执行哪些 Query。如果是遇到一个并发不太高的 Query执行计划有误,至少还可以控制整个系统,不至于系统被直接压跨,甚至连问题根源都难以抓到。

定位优化对象的性能瓶颈

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

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

一般来说,在MySQL 5.0系列版本中,可以通过系统自带的 PROFILING功能清楚地找出一个 Query的瓶颈。当然,如果读者朋友为了使用 MySQL 的某些在 5.1 版本中才有的新特性(如 Partition,EVENT等),抑或是早早使用MySQL 5.1 的预发布版本,可能就没办法使用这个功能了,因为该功能在MySQL 5.1 系列最初的版本中并不支持,不过让人非常兴奋的是该功能在最新的 MySQL 5.1 正式版(5.1.30)又已经提供了。如果读者朋友正在使用的是4.x版本,那就只能通过自行分析 Query 的各个执行步骤,找到性能损失最大的地方了。

发布日期:
分类:Mysql

发表评论

邮箱地址不会被公开。 必填项已用*标注