MySQL性能调优金字塔

本文介绍的三种调优方法是按照金字塔的调优顺序排列的，如下图所示。一般来说，自底向上调优的效果是成反比的，而越往下层调优效果越好，但是难度也越大。

按照依赖关系（架构调优要求DBA对MySQL本身有一定的了解，MySQL调优依赖于系统和硬件的相关知识）和对专业知识要求的难易程度，我们按照自上而下的顺序（硬件和系统调优、MySQL调优、架构调优）描述案例，而DBA在实际应用过程中接触和优化的顺序其实是相反的。在进行优化时，首先需要关注和优化的应该是架构，如果架构不合理，那么DBA能做的事情其实是比较有限的。

对于架构调优，在系统设计时首先需要充分考虑业务的实际情况，是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做；然后考虑写的并发量有多大，是否需要采用分布式；最后考虑读的压力是否很大，是否需要读写分离。对于核心应用或者金融类的应用，需要额外考虑数据安全因素，数据是否不允许丢失，是否需要采用Galera或者MGR。

对于MySQL调优，需要确认业务表结构设计是否合理，SQL语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引，数据库的参数优化是否足够。

最后确定系统、硬件有哪些地方需要优化，系统瓶颈在哪里，哪些系统参数需要调整优化，进程资源限制是否提到足够高；在硬件方面是否需要更换为具有更高I/O性能的存储硬件，是否需要升级内存、CPU、网络等。如果在设计之初架构就不合理，比如没有进行读写分离，那么后期的MySQL和硬件、系统优化的成本就会很高，并且还不一定能最终解决问题。如果业务性能的瓶颈是由于索引等MySQL层的优化不够导致的，那么即使配置再高性能的I/O存储硬件或者CPU也无法支撑业务的全表扫描。

硬件和系统调优

对于硬件和系统的调优需要在系统上线前，甚至在数据库选型阶段和设计阶段就需要考虑起来，如果等验证测试和上线以后再去考虑提升硬件性能或者调整系统参数，要做的工作就太多了。

• 硬件优化

更高频率的 CPU 能让复杂的SQL语句在MySQL上运行的速度更快；更大的内存能让更多的热点数据缓存在内存中，使得并发效率更高；更快的存储系统能让 MySQL 及时存取数据，提升客户端的响应效率；更高的网络带宽和更低的网络延迟能让 MySQL 提供更大的吞吐率。硬件优化对数据库效率的提升非常关键。

数据库以前都运行在小型机上，资源相对较为充足，之后迁移到 x86 物理机上，大多数时候也能独占整个物理机资源，后来由于互联网的流行，虚拟化、云化带来了非常大的灵活性，但是对于数据库来说，资源缩减得非常多，而 MySQL 越来越“应用化”，一个开发人员或者系统管理员就可以将其部署起来，压力不大时使用起来也不会有问题。这样带来的问题是，其他的业务压力或者I/O压力可能就让数据库变得很缓慢，甚至一条复杂的 SQL语句或者一个SQL语句执行计划走错都会让数据库响应时间增加几十倍。要达到小型机+存储的数据库时代的稳定性和效率，对底层硬件的选型、验证、资源隔离以及优化就必不可少。CPU、内存、网络受限于企业环境，可调整和优化的空间比较小，而数据库最关键和最值得关注的就是I/O存储系统的优化，是选择普通的机械磁盘还是Flash介质存储，RAID怎么做、怎么分区，是write back还是write through，对数据库的影响非常大。

• 系统优化

由于硬件资源的限制，也为了让系统中运行的各个组件能均衡地使用硬件资源，Linux系统设计和实现了各种资源使用策略。数据库的操作系统优化从某种程度来说就是理解操作系统的资源使用策略，充分让数据库使用更多的硬件资源，发挥硬件性能。例如，为了避免内存空间使用不足而发生崩溃，Linux系统设计了swap（交换区），并且提供了一个swappiness参数，用来设置在什么情况下使用swap。当该参数设置为0时，系统在几乎没有内存的情况下才会使用swap；当其设置为100时，进程申请的内存很快就会被交换出去，在数据库场景下，应该将swappiness设置得尽可能小，以保证热点数据尽量保留在物理内存中。

MySQL调优

• 参数调优

参数调优的目的就在于如何适配硬件和系统，在MySQL的服务器层和InnoDB层最大程度地发挥底层的性能，保证业务系统高效。

在Oracle占据大部分数据库市场的年代，多个DBA会共同维护一套Oracle数据库，这套Oracle数据库承载着多个业务系统，多个Oracle业务系统之间的参数为了适配业务或者底层硬件，在配置之间不尽相同。在MySQL在互联网上大放异彩的时代，一个DBA管理着几套甚至几十套MySQL数据库，越来越多的MySQL DBA发现，与其为每个业务系统进行特殊的参数调优，还不如确定一个能适配80%业务场景的数据库版本和数据库配置模板，并且对应地规范硬件和系统配置，保证多个MySQL系统的标准化和一致性。其实理由很简单，当规模化以后，必须要进行标准化（比如之前一个人就可以做一双皮鞋，为每个人定制，价格和成本相对比较高；如果一个工厂每天要做一万双皮鞋，就不可能为每个人定制了，而必须标准化，通过流水线提升效率），避免排查问题、升级、运维等工作不可控。比如5个不标准的MySQL系统升级到新版本需要准备5套方案，而5个标准的MySQL系统升级到新版本只需要准备1套方案，并且还可以自动化实现。

当然，并不是要非黑即白地去理解这个问题，也不是说MySQL的参数调优就不需要关注了。笔者曾经遇到过一个128GB内存的服务器，由于MySQL的buffer_pool参数只配置为128MB导致性能特别差的案例。随着硬件性能的提升、MySQL数据库版本的升级、DBA经验的提升和DBA在实际硬件上的并发测试，你可能会发现有更加适合对应硬件和操作系统的MySQL配置参数值，当验证通过后，就可以统一调整升级了。这里有一个小技巧：将[mysqld]的配置写在最后。由于写在后面的配置会直接覆盖前面的配置，如果要对MySQL服务器配置进行参数调整，那么直接在结尾添加参数就可以了，自动化程序修改起来非常方便，不容易出错。示例如下：

[client]

port=3306

...

[mysqldump]

default-character-set = utf8

...

[mysql]

no-auto-rehash

...

[mysqld]

default-storage-engine = INNODB

...

# 保证[mysqld]是最后一个MySQL配置组，所有需要调整的参数都添加在此位置后（利用mysqld配置项后面的覆盖前面的特性）

...

innodb_log_buffer_size = 128M

• SQL/索引调优

SQL/索引调优要求DBA对业务和数据流非常清楚。在阿里巴巴内部，有三分之二的DBA是业务DBA，从业务需求讨论到表结构审核、SQL语句审核、上线、索引更新、版本迭代升级，甚至哪些数据应该放到非关系型数据库中，哪些数据放到数据仓库、搜索引擎或者缓存中，都需要这些DBA跟踪和复审。他们甚至可以称为数据架构师（Data Architecher）。开发人员的更替或者业务的迭代导致一些业务逻辑和代码很难跟踪，但是没关系，DBA熟悉每个表、每个字段的含义，他们跟踪业务模块关系、更新迭代的缘由、业务高峰/低谷时哪里最耗资源、是否还有优化空间等。如果这些数据模型都在的话，就很方便对业务逻辑和代码诊断和修改了。

架构调优

如文章开头的图所示，金字塔的底部是架构调优，采用更适合业务场景的架构能最大程度地提升系统的扩展性和可用性。在设计中进行垂直拆分能尽量解耦应用的依赖，对读压力比较大的业务进行读写分离能保证读性能线性扩展，而对于读写并发压力比较大的业务在MySQL上也有采用读写分离的大量案例。

作为金字塔的底部，在底层硬件系统、SQL语句和参数都基本定型的情况下，单个MySQL数据库能提供的性能、扩展性等就基本定型了。但是通过架构设计和优化，却能承载几倍、几十倍甚至百倍于单个MySQL数据库能力的业务请求能力。

小结

本文从整体上介绍了性能调优的几个方面，并借用“金字塔”理论依次介绍了硬件和系统调优、MySQL 调优以及架构调优的一些原则和方法。在实际工作中，读者可以按照调优介入的时间、问题的紧急程度选择对应的优化方法。例如，在业务设计时介入可以选择架构调优和硬件调优，而在业务上线后大部分只能考虑系统调优和 MySQL 调优了。

本文节选自博文视点新书《千金良方——MySQL性能优化金字塔法则》。作为沃趣核心团队的呕心力作，本书汇聚他们多年运维经验及宝贵案例。蒸汽机的改进不是一蹴而就的，性能的改进也是贯穿整个MySQL发展史的，就像瓦特改进蒸汽机引发了工业革命一样，本书也旨在进一步促进技术界对MySQL/MariaDB性能优化的关注。左下阅读原文，开辟反霸权通道，支持技术独立，让我们一起为开源数据库MySQL/MariaDB大厦添砖加瓦。