影响性能的相关因素

Published on 2016 - 06 - 06

Query语句对系统性能的影响

我想对于读者来说,肯定都清楚Query语句的优劣对性能的影响,但是到底有多大影响可能每个人都会有不同的体会,每个Query语句在优化之前和优化之后的性能差异也各不相同,所以对此问题这里就不做详细分析了。我们重点分析实现同样功能的不同Query语句在性能方面会产生较大差异的根本原因,并通过一个较为典型的示例来对分析做出相应的验证。

为什么返回完全相同结果集的不同Query语句,在执行性能方面存在差异呢?这里我们先从Query语句在数据库中执行并获取所需数据的过程来做一个大概的分析。

当MySQL Server的连接线程接收到Client端发送过来的Query请求时,会经过一系列的分解Parse,并进行相应的分析。然后,MySQL会通过查询优化器模块(Optimizer)根据该Query所涉及的数据表相关统计信息进行计算分析,再得出一个MySQL认为最合理最优化的数据访问方式,也就是常说的“执行计划”,然后根据所得到的执行计划通过调用存储引擎接口来获取相应数据。最后将存储引擎返回的数据进行相关处理,并以Client端所要求的格式作为结果集返回给Client端的应用程序。

注意:这里所说的统计数据是通过ANALYZE TABLE命令通知MySQL对表的相关数据做分析之后获得的。这些统计数据对MySQL优化器而言是非常重要的,优化器所生成的执行计划的好坏,主要就是由这些统计数据所决定的。实际上,在其他一些数据库管理软件中也有类似相应的统计数据。

我们都知道,在数据库管理软件中,最大的性能瓶颈就是在于磁盘IO,也就是数据的存取操作上面。而对于同一份数据,当以不同方式去寻找其中某一点内容的时候,须要读取的数据量可能会有天壤之别,所消耗的资源自然也是区别甚大。所以,当须要从数据库中查询某个数据的时候,所消耗资源的多少主要取决于数据库以一个什么样的数据读取方式来完成查询请求,也就是取决于Query语句的执行计划。

对于唯一一个Query语句来说,经过MySQL Parse之后分解的结构都是固定的,只要统计信息稳定,其执行计划基本上都比较固定。而不同写法的Query语句,经过MySQL Parse之后分解的结构结构就可能完全不同,即使优化器使用完全一样的统计信息来进行优化,最后所得出的执行计划也可能完全不一样。而执行计划又是决定一个Query语句最终资源消耗量的主要因素。所以,实现功能完全一样的Query语句,在性能上面可能会有差别巨大的性能消耗。当然,如果不同Query语句功能一样,而且经过MySQL的优化器优化之后的执行计划也完全一致,那它们在资源消耗方面可能就相差很小了。当然这里的消耗主要是指IO资源的消耗,并不包括CPU的消耗。

下面将通过一两个具体的示例来分析写法不一样而功能完全相同的两条Query在性能方面的差异。

示例一

需求:取出某个group(假设id为100)下的用户编号(id),用户昵称(nick_name)、用户性别(sexuality)、用户签名(sign)和用户生日(birthday),并按照加入组的时间(user_group.gmt_create)进行倒序排列,取出前20个。如示例代码4所示。

执行计划对比的分析如下。

解决方案一中的执行计划显示MySQL对两个参与Join的表都用到了索引,user_group表用了到user_group_gid_ind索引(Key: user_group_gid_ind),user表用到了主键索引(Key: PRIMARY),MySQL在参与Join前通过WHERE过滤的结果集与user表进行Join,最后通过排序取出Join后的“LIMIT 100,20”条的结果返回。

解决方案二的Query语句用到了子查询,所以执行计划会稍微复杂一些,首先可以看到这两个表和解决方案一一样都用到了索引(所使用的索引也完全一样),执行计划显示该子查询以user_group为驱动,也就是先通过user_group过滤并马上将这一轮的结果集排序,这样也就取得了Query中的“LIMIT 100,20”条结果,然后与user表进行Join,得到相应的数据。这里可能有人会怀疑自查询中从user_group表所取得的user表参与Join的记录条数并不是20条,而是group_id=1的所有结果。那么请大家看看该执行计划中的第一行,该行内容充分说明了外层查询中所有的20条记录全部被返回。

通过比较两个解决方案的执行计划,可以看到第一种解决方案中MySQL通过统计数据估算出来的user表参与Join的记录数是31156,也就是通过user_group表返回的所有满足group_id=1的记录数(系统中的实际数据是20000)。而第二种解决方案的执行计划中,user表参与Join的数据就只有20条,两者相差很大,通过本节最初的分析,我们认为第二种解决方案应该明显优于第一种。

下面通过对比两个解决方案的Query实际执行的Profile信息,来验证上面的判断,如示例代码5。由于Query语句执行所消耗的最大两部分资源就是IO和CPU,所以这里仅列出BLOCK IO和CPU两项Profile信息(Query Profiler将在后面章节中详细介绍)。

先打开profiling功能,然后分别执行两个解决方案的Query语句。

查看系统中的Profile信息,刚刚执行的两个Query语句的Profile信息已经被记录下来了,如示例代码6所示:

先看看两条Query执行中的IO消耗,两者的区别就在于“Sorting result”,回顾一下前面的执行计划,两个解决方案排序过滤数据的时机不一样,排序后须要取得的数据量一个是20000,一个是20,正好和这里的Profile信息吻合,第一种解决方案的“Sorting result”的IO值将近是第二种的500倍。

然后再来看看CPU消耗,所有消耗中,消耗最大的也是“Sorting result”这一项,第一个消耗多的缘由和上面IO消耗差异是一样的。

结论:通过对上面两条功能完全相同的Query语句的执行计划的分析,以及实际执行后的profile数据的验证,都证明了第二种解决方案优于第一种。同时后者的实际验证,也再次证明了前面所做的执行计划基本决定了SQL语句性能。

Schema设计对系统的性能影响

在很多人看来,数据库Schema设计是一件非常简单的事情,大体按照系统设计时候的相关实体对象对应成一个一个表格就可以了。为了在功能上尽可能容易扩展,根据数据库范式规则进行调整,做到第三范式或第四范式,基本就算完事了。

数据库Schema设计真的如上面所说的这么简单么?可以非常肯定地告诉大家,数据库Schema设计须要做的事情远远不止如此。如果您之前的数据库Schema设计一直都是这么做的,那么在该设计应用于正式环境之后,它很可能会带来非常大的性能代价。

示例一

需求概述:一个简单的讨论区系统,需要有用户、用户组、组讨论区这三部分基本功能
简要分析:

  1. 须要存放用户数据的表;
  2. 须要存放分组信息和用户与组关系的表;
  3. 须要存放讨论信息的表。

解决方案:

原始方案一:分别用4个表来存放用户、分组、用户与组关系,以及各组的讨论帖子的信息,如下所示。

user用户表:

groups分组表:

user_group关系表:

group_message讨论组帖子表:

优化后方案二如下。
user用户表:

user_profile用户属性表(记录与user一一对应):

groups和user_group这两个表和方案一完全一样。

group_message讨论组帖子表:

group_message_content帖子内容表(记录与group_message一一对应):

先来比较一下两个解决方案所设计的Schema的区别。区别主要体现在两点上,一个是在group_message表中增加了author字段来存放发帖作者的昵称,与user表的nick_name相对应,另外一个就是第二个解决方案将user表和group_message表都分拆成了两个表,关系分别是一一对应的。

方案二看上去比方案一要更复杂一些,首先是表的数量多了2个,然后是在group_message中冗余存放了作者昵称。试想一下,一个讨论区系统,访问最多的页面会是什么?我想大家都会很清楚是帖子标题列表页面。而帖子标题列表页面最主要的信息都来自group_message表中,同时帖子标题后面的作者一般都是通过用户名(昵称)来展示的。按照第一种解决方案设计的Schema,须要执行类似示例代码7的Query语句来得到数据。

但是第二种解决方案就会简单很多,如示例代码8:

两个Query相比较,大家就能很明显地看出谁优谁劣了,第一个须要读取两个表的数据进行Join,这与第二个Query相比性能差距很大,尤其是如果第一个再写得差一点,性能更是糟糕,两者所带来的资源消耗就相差更远了。

不仅如此,由于第一个方案中的group_message表中还包含一个大字段“content”,该字段所存放的信息要占整个表的绝大部分存储空间,但在这条系统中执行最频繁的Query完全不需要该字段所存放的信息,可是这个Query没办法不访问group_message表的数据,所以第一条Query在数据读取过程中会须要读取大量没有任何意义的数据。

在系统中用户数据的读取也是比较频繁的,但是大多数地方需要的用户数据只是几个基本属性,如用户的id、昵称、密码、状态、邮箱等,所以将用户表的这几个属性单独分离出来,也会让大量的Query语句在运行的时候减少数据的检索量,从而提高性能。

可能有人会觉得,将一个表分成两个表,如果要访问被分拆出去的信息,性能不是就会变差了吗?是的,对于那些须要访问如user表的登录,查看IM工具信息等原来只要一个表就可以完成的Query来说,现在都须要用两条Query来完成,性能确实会有所降低,但是由于两个表都是一对一的关联关系,关联字段的过滤性也非常高,而且这样的查询需求在整个系统中所占有的比例也并不高,这里带来的性能损失实际上要远远小于在其他Query上节省出来的资源,所以完全不必为此担心。

硬件环境对系统性能的影响

典型OLTP应用系统

对于各种数据库系统环境中大家最常见的OLTP(On-Line Transaction Processing)系统,其特点是并发量大,整体数据量比较多,但每次访问的数据比较少,且访问的数据比较离散,活跃数据占总体数据的比例不是太大。这类系统的数据库实际上是最难维护,最难以优化的,其对主机整体性能要求也是最高的。因为它不仅访问量很高,数据量也不小。

针对上面的这些特点和分析,可以给OLTP一个大致的方向。

  • 虽然系统总体数据量较大,但是系统活跃数据在数据总量中所占的比例不大,那么可以通过扩大内存容量尽可能多地将活跃数据缓存(Cache)到内存中;
  • 虽然IO访问非常频繁,但是每次访问的数据量较少且很离散,那么对磁盘存储的要求是IOPS表现要很好,吞吐量是次要因素;
  • 并发量很高,CPU每秒所要处理的请求自然也就很多,所以CPU处理能力需要比较强劲;
  • 虽然每次与客户端交互的数据量并不是特别大,但是网络交互非常频繁,所以主机与客户端交互的网络设备对流量能力也不能太弱。

典型OLAP应用系统

用于数据分析的OLAP(On-Line Analytical Processing)系统的主要特点就是数据量非常大,并发访问不多,但每次访问须要检索的数据量都比较多,而且数据访问相对较为集中,没有太明显的活跃数据概念。

基于OLAP系统的各种特点和相应的分析,针对OLAP系统硬件优化的大致策略如下:

  • 数据量非常大,所以磁盘存储系统的单位容量应尽量大一些;
  • 单次访问数据量较大,而且访问数据比较集中,那么对IO系统的性能要求是要有尽可能大的每秒IO吞吐量,所以应该选用相应的磁盘;
  • 虽然IO性能要求也比较高,但是并发请求较少, CPU处理能力较难成为性能瓶颈,所以对CPU处理能力没有太苛刻的要求;
  • 虽然每次请求的访问量很大,但是执行过程中的数据大都不会返回给客户端,最终返回给客户端的数据量都较小,所以和客户端交互的网络设备要求并不是太高;

此外,由于OLAP系统每次运算过程较长,可以很好地并行,所以一般的OLAP系统都是由多台主机构成的一个集群,而集群中主机与主机之间的数据交互量一般来说都非常大,所以在集群中主机之间的网络设备要求很高。

特殊的应用系统

除了以上两个典型应用之外,还有一类比较特殊的应用系统,它们的数据量不是特别大,但是访问请求极其频繁,而且大部分是读请求。可能每秒须要提供上万甚至几万次请求,每次请求都非常简单,大部分都只有一条或几条比较小的记录返回,比如基于数据库的DNS服务就是这种类型的服务。

  • 上面所述这种情况,可以通过较大的内存来Cache住大部分的数据,这能够保证非常高的命中率,磁盘IO量比较小,所以磁盘也不需要特别高性能的;
  • 并发请求非常频繁,必须要较强的CPU处理能力才能处理;
  • 虽然应用与数据库交互量非常大,但是每次交互的数据较少;总体流量虽然会较大,但是一般来说普通的千兆网卡已经足够了。

参考文献