[技术笔记] 【成都校区】MySQL的性能调优二

本帖最后由小刀葛小伦于 2018-7-12 16:20 编辑

一、Schema 设计对系统的性能影响

前面，我们已经分析了在一个数据库应用系统的软环境中应用系统的架构实现和系统中与数据库交互的SQL 语句对系统性能的影响。在这一节我们再分析一下系统的数据模型设计实现对系统的性能影响，更通俗一点就是数据库的Schema 设计对系统性能的影响。

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

数据库Schema 设计真的有如上面所说的这么简单么？可以非常肯定的告诉大家，数据库Schema 设计所需要做的事情远远不止如此。如果您之前的数据库Schema 设计一直都是这么做的，那么在该设计应用于正式环境之后，很可能带来非常大的性能代价。由于在后面的“MySQL 数据库应用系统设计”中的“系统架构最优化“这一节中会介较为详细的从性能优化的角度来分析如何如何设计数据库Schema，所以这里暂时先不介绍如何来设计性能优异的数据库Schema 结构，仅仅通过一个实际的示例来展示Schema 结构的不一样在性能方面所带来的差异。

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

简要分析：

1、需要存放用户数据的表；

2、需要存放分组信息和存放用户与组关系的表

3、需要存放讨论信息的表；

解决方案：

原始方案一：分别用用四个表来存放用户，分组，用户与组关系以及各组的讨论帖子的信息如下：

user 用户表：

+-------------+---------------+------+-----+---------+-------+

+-------------+---------------+------+-----+---------+-------+

| id | int(11) | NO | | 0 | |

| msn | varchar(32) | YES | | NULL | |

+-------------+---------------+------+-----+---------+-------+

groups 分组表：

+--------------+---------------+------+-----+---------+-------+

+--------------+---------------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

+--------------+---------------+------+-----+---------+-------+

user_group 关系表：

+--------------+-------------+------+-----+---------+-------+

+--------------+-------------+------+-----+---------+-------+

| user_id | int(11) | NO | MUL | NULL | |

| group_id | int(11) | NO | MUL | NULL | |

| user_type | int(11) | NO | | NULL | |

+--------------+-------------+------+-----+---------+-------+

group_message 讨论组帖子表：

+--------------+--------------+------+-----+---------+-------+

+--------------+--------------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

| group_id | int(11) | NO | | NULL | |

| user_id | int(11) | NO | | NULL | |

+--------------+--------------+------+-----+---------+-------+

优化后方案二：

user 用户表：

+-------------+---------------+------+-----+---------+-------+

+-------------+---------------+------+-----+---------+-------+

| id | int(11) | NO | | 0 | |

+-------------+---------------+------+-----+---------+-------+

user_profile 用户属性表（记录与user 一一对应）：

+-------------+---------------+------+-----+---------+-------+

+-------------+---------------+------+-----+---------+-------+

| msn | varchar(32) | YES | | NULL | |

+-------------+---------------+------+-----+---------+-------+

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

group_message 讨论组帖子表：

+--------------+--------------+------+-----+---------+-------+

+--------------+--------------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

| group_id | int(11) | NO | | NULL | |

| user_id | int(11) | NO | | NULL | |

+--------------+--------------+------+-----+---------+-------+

group_message_content 帖子内容表（记录与group_message 一一对应）：

+--------------+---------+------+-----+---------+-------+

+--------------+---------+------+-----+---------+-------+

| group_msg_id | int(11) | NO | | NULL | |

+--------------+---------+------+-----+---------+-------+

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

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

+--------------+--------------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

| group_id | int(11) | NO | | NULL | |

| user_id | int(11) | NO | | NULL | |

+--------------+--------------+------+-----+---------+-------+

group_message_content 帖子内容表（记录与group_message 一一对应）：

+--------------+---------+------+-----+---------+-------+

+--------------+---------+------+-----+---------+-------+

| group_msg_id | int(11) | NO | | NULL | |

+--------------+---------+------+-----+---------+-------+

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

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

与第二个SQL 相比性能差距很大，尤其是如果第一个再写的差一点，性能更是非常糟糕，两者所带来的资源消耗就更相差玄虚了。

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

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

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

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

在之前的所有部分都是介绍的整个系统中的软件环境对系统性能的影响，这一节我们将从系统硬件环境来分析对数据库系统的影响，并从数据库服务器主机的角度来做一些针对性的优化建议。任何一个系统的硬件环境都会对起性能起到非常关键的作用，这一点我想每一位朋友都是非常清楚的。而数据库应用系统环境中，由于数据库自身的特点和在系统中的角色决定了他在整个系统中是最难以扩展的部分。所以在大多数环境下，数据库服务器主机（或者主机集群）的性能在很大程度上决定了整个应用系统的性能。

既然我们的数据库主机资源如此重要，肯定很多朋友会希望知道，数据库服务器主机的各部分硬件到底谁最重要，各部分对整体性能的影响各自所占的比例是多少，以便能够根据这些比例选取合适的主机机型作为数据库主机。但是我只能很遗憾的告诉大家，没有任何一个定律或者法则可以很准确的给出这个答案。当然，大家也不必太沮丧。虽然没有哪个法则可以准确的知道我们到底该如何选配一个主机的各部分硬件，但是根据应用类型的不同，总体上还是有一个可以大致遵循的原则可以参考的。

首先，数据库主机是存取数据的地方，那么其IO 操作自然不会少，所以数据库主机的IO 性能肯定是需要最优先考虑的一个因素，这一点不管是什么类型的数据库应用都是适用的。不过，这里的IO 性能并不仅仅只是指物理的磁盘IO，而是主机的整体IO 性能，是主机整个IO 系统的总体IO 性能。而IO 性能本身又可以分为两类，一类是每秒可提供的IO 访问次数，也就是我们常说的IOPS 数量，还有一种就是每秒的IO 总流量，也就是我们常说的IO 吞吐量。在主机中决定IO 性能部件主要由磁盘和内存所决定，当然也包括各种与IO 相关的板卡。

其次，由于数据库主机和普通的应用程序服务器相比，资源要相对集中很多，单台主机上所需要进行的计算量自然也就比较多，所以数据库主机的CPU 处理能力也不能忽视。

最后，由于数据库负责数据的存储，与各应用程序的交互中传递的数据量比其他各类服务器都要多，所以数据库主机的网络设备的性能也可能会成为系统的瓶颈。由于上面这三类部件是影响数据库主机性能的最主要因素，其他部件成为性能瓶颈的几率要小很多，所以后面我们通过对各种类型的应用做一个简单的分析，再针对性的给出这三类部件的基本选型建议。

1、典型OLTP 应用系统

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

针对上面的这些特点和分析，我们可以对OLTP 的得出一个大致的方向。

虽然系统总体数据量较大，但是系统活跃数据在数据总量中所占的比例不大，那么我们可以通过扩大内存容量来尽可能多的将活跃数据cache 到内存中；

虽然IO 访问非常频繁，但是每次访问的数据量较少且很离散，那么我们对磁盘存储的要求是IOPS 表现要很好，吞吐量是次要因素；

并发量很高，CPU 每秒所要处理的请求自然也就很多，所以CPU 处理能力需要比较强劲；

虽然与客户端的每次交互的数据量并不是特别大，但是网络交互非常频繁，所以主机与客户端交互的网络设备对流量能力也要求不能太弱。

2、典型OLAP 应用系统

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

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

数据量非常大，所以磁盘存储系统的单位容量需要尽量大一些；

单次访问数据量较大，而且访问数据比较集中，那么对IO 系统的性能要求是需要有尽可能大的每秒IO 吞吐量，所以应该选用每秒吞吐量尽可能大的磁盘；

虽然IO 性能要求也比较高，但是并发请求较少，所以CPU 处理能力较难成为性能瓶颈，所以CPU 处理能力没有太苛刻的要求；

虽然每次请求的访问量很大，但是执行过程中的数据大都不会返回给客户端，最终返回给客户端的数据量都较小，所以和客户端交互的网络设备要求并不是太高；

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

3、除了以上两个典型应用之外，还有一类比较特殊的应用系统

他们的数据量不是特别大，但是访问请求及其频繁，而且大部分是读请求。可能每秒需要提供上万甚至几万次请求，每次请求都非常简单，可能大部分都只有一条或者几条比较小的记录返回，就比如基于数据库的DNS 服务就是这样类型的服务。

虽然数据量小，但是访问极其频繁，所以可以通过较大的内存来cache 住大部分的数据，这能够保证非常高的命中率，磁盘IO 量比较小，所以磁盘也不需要特别高性能的；

并发请求非常频繁，比需要较强的CPU 处理能力才能处理；虽然应用与数据库交互量非常大，但是每次交互数据较少，总体流量虽然也会较大，但是一般来说普通的千兆网卡已经足够了。在很多人看来，性能的根本决定因素是硬件性能的好坏。但实际上，硬件性能只能在某些阶段对系统性能产生根本性影响。

当我们的CPU 处理能力足够的多，IO 系统的处理能力足够强的时候，如果我们的应用架构和业务实现不够优化，一个本来很简单的实现非得绕很多个弯子来回交互多次，那再强的硬件也没有用，因为来回的交互总是需要消耗时间。尤其是有些业务逻辑设计不是特别合理的应用，数据库Schema 设计的不够合理，一个任务在系统中又被分拆成很多个步骤，每个步骤都使用了非常复杂的Query 语句。

所以，在应用系统的硬件配置方面，我们应该要以一个理性的眼光来看待，只有合适的才是最好的。并不是说硬件资源越好，系统性能就一定会越好。而且，硬件系统本身总是有一个扩展极限的，如果我们一味的希望通过升级硬件性能来解决系统的性能问题，那么总有一天将会遇到无法逾越的瓶颈。到那时候，就算有再多的钱去砸也无济于事了。

三、小结

虽然本章是以影响MySQL Server 性能的相关因素来展开分析，但实际上很多内容都对于大多数数据库应用系统适用。数据库管理软件仅仅是实现了数据库应用系统中的数据存取操作，和数据的持久化。数据库应用系统的优化真正能带来最大收益的就是商业需求和系统架构及业务实现的优化，然后是数据库Schema 设计的优化，然后才是Query 语句的优化，最后才是数据库管理软件自身的一些优化。

通过经验，在整个系统的性能优化中，如果按照百分比来划分上面几个层面的优化带来的性能收益，可以得出大概如下的数据：

需求和架构及业务实现优化：55%

Query 语句的优化：30%

数据库自身的优化：15%

很多时候，大家看到数据库应用系统中性能瓶颈出现在数据库方面，就希望通过数据库的优化来解决问题，但不管DBA 对数据库多们了解，对Query 语句的优化多么精通，最终还是很难解决整个系统的性能问题。原因就在于并没有真正找到根本的症结所在。

所以，数据库应用系统的优化，实际上是一个需要多方面配合，多方面优化的才能产生根本性改善的事情。

简单来说，可以通过下面三句话来简单的概括数据库应用系统的性能优化：商业需求合理化，系统架构最优化，逻辑实现精简化，硬件设施理性化。

帐号		自动登录	找回密码
密码			加入黑马

[技术笔记] 【成都校区】MySQL的性能调优二

0 个回复