解DBA之惑：数据库承载能力评估及优化手段

作为DBA，有时会被挑战类似这样的问题：

如果现有业务规模增加10倍、100倍，数据库是否能够支撑？

下个月我们搞大促，数据库这边没问题吧？

计划进行去O工作，代码逻辑不变，数据库从Oracle切换到MySQL，MySQL能支撑业务吗？

服务器采购选型，到底哪款服务器更适合我们呢？

面对诸如上面的这些质疑，DBA应该如何面对？

身为DBA该如何评估现有资源使用情况？

如果现有数据库资源确实无法支撑，又该本着什么原则进行改造呢？

本文是针对上面问题的一些经验总结，供大家参考。

一、评估工作

面对这样的问题，首先要进行评估工作，可遵循下面的步骤：

1、建立性能基线

针对系统运行现状，建立性能基线。将业务指标与性能指标建立起对应关系。这里所说的性能指标包括CPU、MEM、DISK、NET等。在诸多资源中，肯定存在不均衡的情况，短板的资源最有可能成为业务增长后的瓶颈。在具体操作上，可首先确定一个业务高峰时间段，通过监控平台或监控工具收集系统各资源的使用情况。然后依据收集的信息，分析可能的性能短板在哪里。

对于DBA来说，对自己掌管系统的性能使用情况要了然于胸。通过对业务的了解，将业务指标映射到性能指标上，就可以很容易地推断出现有系统可承载的最大业务量。此外，对于可能影响承载业务增长的短板，也会有比较清晰的认识。

一般来说，数据库类的应用是重资源消耗类的应用。对CPU、MEM、DISK、NET等，均有较大的消耗。但由于不同硬件发展水平不均衡，各数据库资源消耗特点也不同，因此需要具体问题具体分析。

下面谈谈我对硬件发展及与数据库关系的一点个人观点:

CPU

相对于其他硬件而言，CPU技术发展较快。随着CPU主频提高及多核CPU技术的发展，CPU提供的计算能力往往不会成为系统的性能瓶颈。但我们需要注意的是，有些数据库是无法完全利用CPU的能力(例如MySQL就是这样)。此时，为了充分利用CPU的资源，可以考虑诸如"多实例混跑"的方案，提高CPU利用率。

MEM

随着内存技术的发挥，内存的价格越来越便宜。现在我们在生产环境中，可以见到128、256GB，甚至TB级的内存也不罕见。一般来说，数据库通常会利用内存作为缓冲区，大内存的配置对数据库的性能有着比较明显的提升。此外，数据库自身技术也在适应着大内存的场景，通常采用的策略是划分子池。将管理的单位进一步细分，例如Oracle中的Sub Pool、MySQL中的多instance buffer pool。

NET

随着GigE、10GbE、InfiniBand技术的飞速发展，低延迟、高带宽的服务品质给数据库乃至整个IT系统带来了很多变化。常见的应用领域有：

加速分布式数据库，例如Oracle RAC。

加速大数据处理，例如提升Hadoop MapReduce处理。

存储架构的变革，从Scale-Up向Scale-Out演变。

容灾方案，主备策略…

DISK

相对于其他硬件技术发展而言，传统的机械式磁盘是相对而言发展最慢的，其往往也是最容易成为数据库的性能瓶颈。随着闪存技术的横空出世，为存储技术带来的一种变革。下面我们来看看主要性能指标的对比：

从上述指标来看，使用闪存技术后，存储能力大大提高，消除了系统最大的瓶颈。这也是为什么很多DBA都在不同场合，大力推荐使用闪存，其对于数据库性能的提升会带来质的飞跃。但与此同时，我们也应该注意到，传统关系型数据库是按照磁盘IO模型设计的，没有考虑到闪存技术，现在属于软件落后于硬件的阶段；相对而言，闪存技术对于非关系型模型更有优势。

很多基于传统设计的优化理论发生了变化，例如: 索引聚簇因子的问题。这一点是需要我们在考虑数据库优化时，主要注意的。此外，NoSQL的性能优势因为传统数据库结合闪存技术，而变得不明显。需要在架构选择时加以分析。

2、建立业务压力模型

根据业务特征，建立业务压力模型。简单理解就是将业务模拟抽象出来，便于后面进行压力放大测试。要做到这一步，需要对业务有着充分的了解和评估。

下面通过一个小例子说明一下:

这个表格模拟了某个类电商的业务，其包含的主要模块及模块中的主要操作。针对不同的操作其交易复杂度不同 （交易复杂度可理解为执行SQL语句的个数）。根据不同的读写情况，区分是数据读还是数据写。在估算了业务总量(交易量)的情况下，很容易推算出数据操作的量。通过这种方式将业务压力模型转化为数据压力模型。此处的难点在于对业务逻辑的抽象能力及对模块业务量的比例评估。

有了上述概览的表格后，针对每一种业务操作，可细化其操作。最终将其抽象成SQL语句及对应的访问特征。其伪代码可描述为

可依据上述伪代码，编制压力测试代码。通过一些工具调用测试代码，产生模拟测试的压力。例如我经常使用的oradbtest/mydbtest(原阿里楼方鑫的一个测试工具)或sysbench等，都是不错的压力测试工具。

建议企业根据自身情况，整理出自己的业务压力模型。这在系统改造、升级、扩容评估、新硬件选型等多种场合都很有用处。它要比厂商提供的类似TPCC测试报告，更有意义。据我了解，很多规模较大的公司都有比较成熟的压力模型。

3、模拟压力测试

要想考察现有数据库能否承载增长后的业务压力，最好的方式就是模拟压力测试。观察在近似真实的压力下，数据库的表现。重点观察，数据库的承载力变化、主要性能瓶颈等。通常可以有两种方式，一种是从真实环境导流（并可根据需要放大流量，可利用类似TCPCOPY等工具）;一种是根据前面整理的业务压力模型，通过压力工具模拟压力。前者适用于已有项目的扩容评估、系统改造评估等，后者适用于新上项目原型方案评估、性能基准测试等场景。

上述模拟压力测试结果中，暴露出的性能瓶颈点，就是我们后面需要着重改进、优化的方向。

二、优化层次及步骤

针对上面的评估结果，来确定后面的改进、优化方案。可遵循如下一些步骤：

1、分析瓶颈点

根据上面的评测结果，分析性能瓶颈点。针对不同瓶颈点，可采取不同的一些策略。有时候性能测试时全流程的，对于一个复杂系统来说，要明确定位到性能瓶颈点比较困难。此时，可借助一些APM工具，量化整个访问路径，协助找到瓶颈。也可以类似上面的做法，做好抽象工作，只对数据库端施加压力，观察数据库行为，判读数据库是否为瓶颈。如判断就是数据库的承载能力不够，可按照不同层次进行考虑。

在整个评估数据库承载能力中，这一步骤是最复杂的、也是最难的一部分。要区分清楚是否是数据库承载能力不足，还是其他组件的问题。即使明确是数据库的问题，也要分清楚是整体or局部的问题；是单一业务功能慢，还是整体都比较慢；是偶尔会慢，还是一直都很慢等等。这些问题的界定有助于后面明确问题层次，采取不同的策略进行解决。

针对数据库承载能力不足，我将常见出现问题进行了层次划分，可简单分为语句级、对象级、数据库级、数据库架构级、应用架构级、业务架构级。不同层次采取的方式也有所不同，下面分别描述一下。

2、层次-语句级

如性能核心问题，只是某条SQL语句的问题，可有针对性地进行优化。这种方式是侵入性比较小的一种优化方式，其影响范围也比较小。下面对比常见的语句级优化方法。说明一下，下面方法已经排除了诸如统计信息不准确等其他因素，仅从SQL语句本身优化方式考虑。

改写SQL

通过改写语句，达到调整执行计划，提高运行效率的目的。这种方式的缺点是需要研发人员修改原代码，然后再进行部署上线的过程。此外，有些使用O/R Mapping工具产生的SQL，无法直接修改语句，也无法使用此方法。

使用Hint

很多种数据库都提供了提示（Hint）的功能。通过这种方式来指定语句的执行过程。这种方式同样需要修改源代码，经历部署上线的过程。此外，这种修改方式还存在适应性较差的问题。因为其指定了特有的执行过程，随着数据规模、数据特征的变化，固化的执行过程可能不是最佳方式了。这种方式实际上是放弃了优化器可能产生的最优路径。

存储概要、SQL概要、计划基线

在Oracle中还内置了一些功能，它们可以固化某一条语句的执行方式，从本质上来讲，其原理和上面使用Hint差不多。其缺点也类似上面。

调整参数

有时也可通过调整某些参数，进而改变语句的执行计划。但是这种方式要注意适用范围，不要在全局使用，避免影响较多的语句。在会话级使用也要控制范围，避免产生较大影响。

3、层次-对象级

如性能核心问题，在SQL层面无法解决，需要考虑对象层面的调整。这种情况要比较慎重，需要充分评估可能带来的风险及收益。一个对象的结构修改，可以涉及到数百条、甚至数千条和此相关语句的执行计划变更。如不做充分测试的情况下，很难保证不出问题。如果是oracle数据库，可考虑使用SPA评估一下。其他数据库的话，可提前手工收集一下相关语句，模拟修改后重放上述语句，评估性能变化。

1）影响因素

在对象级进行调整，除了考虑对其他语句的性能影响外，还需要考虑其他因素。常见的以下这些：

数据库维护成本

常见的例如索引。通过添加索引，往往可以起到加速查询的目的；但是增加索引，会导致数据DML成本的增加。

运维成本

常见的例如全局分区索引。全局分区索引在进行分区维护动作后，会导致索引失效，需要自动或手动进行维护索引动作。

存储成本

常见的索引，索引结构是数据库中真实占据空间的结构。在以往的一些案例中，甚至出现过索引总大小超过表大小的情况，因此新增时要评估其空间使用。

2）全生命周期管理

这里还有另外一个很重要的概念——“对象全生命周期管理”，简单来说就是对象的生老病死。在很多系统中，对象从新建开始，数据不断增加、膨胀，当数据规模达到一定量级后，各种性能问题就出现了。对一个百万级的表和亿万级的表，其查询性能肯定不能同日而语。因此，在对象设计初期，就要考虑相关的归档、清理、转储、压缩策略，将存储空间的评估与生命周期管理一起考虑。

很多性能问题，在做了数据清理后都迎刃而解。但数据清理往往是需要代价的，必须在设计之初就考虑这个问题。在做数据库评审的时候，除了常规的结构评审、语句评审外，也要考虑这部分因素。

4、层次-数据库级

到了这个层面，问题往往已经比较严重了。一般情况下，数据库的初始配置都是基于其上面运行系统的负载类型进行专门配置的。如果运行一段时间后，出现性能问题，经评估是属于全局性问题的，可以考虑进行数据库级别的调整。但是这种配置往往代价也比较大，例如需要专门的停机窗口操作等。而且这种操作的风险性也比较大，有可能会带来很多不确定因素，因此要慎而又慎。

5、层次-数据库架构级

如性能核心问题，无法在上述层面解决，可能就需要调整数据库架构。常见的例如采取读写分离的访问方式、分库分表存储方式等。这种对应用的侵入性很强了，有些情况下甚至不亚于重构整个系统。

例如，随着业务的发展，系统的数据量或访问量超出了预期，通过单一数据库无法满足空间或性能要求。此时，可能就需要考虑采用一种分库分表策略，来满足这部分的需求。但其改造难度，往往比重新开发一套系统还要大。

比如，我们可能需要一个数据中间层，来屏蔽后面的分库分表细节。这个中间层可能需要完成语句解析、访问路由、数据聚合、事务处理等一系列功能。即使使用了中间层产品，对于应用来说，数据库的功能也会相对“弱化”，应用级代码不得不进行很多的调整来适应这种变化。此外，如何把一个线上正在运行的系统，顺利平稳地迁移到新的结构下，这无疑又是一个给飞驰的跑车换轮胎的问题等等。

如果项目在运行中，出现了数据库架构级的调整，很有可能说明在前期项目设计规划阶段出现了失误，或者对项目的业务预期出现了偏差。因此，这两点一定在初始阶段进行充分的评估，并在设计上保留有充分的“弹性”。

6、层次-应用架构级

有些情况下，单纯依靠数据库是无法解决的，需要综合考虑整个应用架构。在整个系统架构中，数据库往往处于系统的最末端，其扩展性是最差的。因此，在应用架构设计初期，就应该本着尽量不要对数据库产生压力的原则进行设计。或者即使有大的压力，系统可以采取自动降级等方式保证数据库的平稳运行。

常见的例如增加缓存、通过MQ实现削峰填谷等。通过增加缓存，可以大幅度减少对数据库的访问压力，提高整体系统的吞吐能力。引入MQ，则可以将对数据库的压力以“稳态”的形式，向数据库持续施压，而不至于被某个异常高峰压死。

7、层次-业务架构级

最后一种情况是从业务角度进行一些调整。这往往是一种妥协，通过做适当的减法保证系统的整体运行。甚至不排除牺牲一部分用户体验等方式，来满足大部分用户的可用性。这就需要我们的架构师对系统能提供的能力要很清楚，对业务也要有充分的了解。对于承载什么样的业务，及为了承载业务所需要花费的代价成本有充分的认知，才可以做出一些取舍。

这里要避免一些误区，认为技术是“万能”的。技术可以解决一定的问题，但不能解决所有问题，或者解决所有问题的成本代价是难以接受的。这个时候，从业务角度稍作调整，就可以达到“退一步海阔天空”的结果。

拓展阅读：自制小工具大大加速MySQL SQL语句优化(附源码)

来源：宜信技术学院

本文转载自异步社区。

数据库 SQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。