让数据库跑的更快的7个MySQL优化建议！

会员服务 ·

让数据库跑的更快的7个MySQL优化建议！

2017 年 12 月 12 日 51CTO博客 陈峻

“

随着容量和负载的增加，MySQL 的性能会日趋缓慢。这里有七点建议能够保证 MySQL 的平稳运行。

性能是我们衡量应用的一种方式，而应用性能的一项指标就是用户体验，也就是平时我们常说的：“用户需要等待超过合理的时间，才能获得他们想要的东西吗？”

在不同的情况和场景下，该指标会有所不同。比如说：对于移动购物应用来说，其响应时间不能超过几秒钟；而对于一个员工的人力资源页面而言，其响应时间则允许比几秒钟更长。

因此，不管是什么样的标准，维持应用程序的良好性能都是至关重要的，否则就会引发用户的抱怨（或更糟的是用户转而使用其他的应用）。而数据库性能就是影响应用程序性能的因素之一。

可以说，应用程序、网站和数据库之间的交互会直接影响到应用服务水平的确立。

这种交互的一个核心组成部分是：各种应用程序如何去查询数据库，以及数据库是如何响应各种请求的。

不论是哪一种标准，MySQL 都是时下最流行的数据库管理系统之一。越来越多的企业已将 MySQL（和其他开源的数据库）视为其生产环境中的数据库解决方案。

MySQL 有许多配置方法可以确保您的数据库能够快速地响应各种查询，同时仅对应用程序性能造成细微的下降。

以下就是能够帮助您优化 MySQL 数据库性能的 7 点必备技巧：

学习如何使用EXPLAIN
创建正确的索引
拒绝默认设置
将数据库载入内存中
使用SSD存储
横向扩展
追求可视性

学习如何使用 EXPLAIN

在您对数据库做任何设计决策时，有两个方面非常重要：

应用实体之间如何被映射到各个数据表（数据库模式架构）上。
应用程序如何获取（查询）到它们所需格式类型的数据。

复杂的应用程序必然有着复杂的模式架构和查询。如果您想让自己的各种应用具备所需的性能和扩展性，那就不能单纯依靠直觉去理解各种查询的执行机制。

建议您认真学习如何去使用 EXPLAIN 命令，而不是凭空猜想。该命令会向您展示查询是如何被执行的；并深入地演示有关性能的真实表现情况，以及查询是如何伴随着数据量的变化进行扩展的。

像许多 MySQL Workbench 之类的工具都可以将 EXPLAIN 的输出可视化地展示给您，不过您仍然需要了解与它相关的基本知识。

EXPLAIN 命令的输出有两种不同的格式：老式的表格形式和较新的、能够提供更为细节化的、结构化的 JSON 文档。

如下所示：

mysql> explain format=json select avg(k) from sbtest1 where id between 1000 and 2000 \G
*************************** 1. row ***************************
EXPLAIN: {
  “query_block”: {
    “select_id”: 1,
    “cost_info”: {
      “query_cost”: “762.40”
    },
    “table”: {
      “table_name”: “sbtest1”,
      “access_type”: “range”,
      “possible_keys”: [
        “PRIMARY”
      ],
      “key”: “PRIMARY”,
      “used_key_parts”: [
        “id”
      ],
      “key_length”: “4”,
      “rows_examined_per_scan”: 1874,
      “rows_produced_per_join”: 1874,
      “filtered”: “100.00”,
      “cost_info”: {
        “read_cost”: “387.60”,
        “eval_cost”: “374.80”,
        “prefix_cost”: “762.40”,
        “data_read_per_join”: “351K”
      },
      “used_columns”: [
        “id”,
        “k”
      ],
      “attached_condition”: “(`sbtest`.`sbtest1`.`id` between 1000 and 2000)”
    }
  }
}

其中您需要重点查看的部分是：查询成本。查询成本是指基于查询执行的总体成本和许多不同的因素考虑，MySQL 判定一次查询所付出的花销。

一般简单查询的成本会小于 1000。介于 1000 到 100,000 的成本值被视为中等成本的查询。

因此，如果您每秒只是运行上百个（并非几万个）此类查询的话，一般速度应该比较快。

查询成本如果是超过 100,000 的话，那么开销就比较大了。而通常当您的系统只有单个用户时，此类查询仍然可以被迅速地执行。

当然，您需要仔细考虑一下在交互式应用程序中，使用此类查询的频率（尤其在用户数量增长的时候）。

虽然这些只是大概的数字，但是它们却能够反映出总体的规律。实际情况下，您的系统在处理查询请求负载时会表现得更好还是更糟，完全取决于自身的架构与配置。

决定查询成本的一个首要因素是：查询是否正确地使用了各种索引。如果您没有使用索引进行查询，那么会被 EXPLAIN 命令所指出来，通常源于索引是如何在数据库中被创建的，以及查询本身是如何被设计的。

这也正是为什么 EXPLAIN 值得去好好学习和使用的原因。

创建正确的索引

索引是通过减少在数据库里查询时，必须扫描的数据量来提高查询的自身效率。

在 MySQL 中，索引被用于加快对数据库的访问，并有助于遵循数据库的各种约束（例如 UNIQUE 和 FOREIGN KEY）。

数据库索引就像书的索引一样，它们的位置信息被保存，并且包含有数据库的主要信息。

它们是数据位置的一种参考方法或映射，因此索引并不会更改数据库中的任何数据。它们只是指向数据存放的位置而已。

不过，索引并不总能匹配上任何的负载请求。在系统运行中，您应当不断为查询的上下文环境创建各种索引。

虽然有着良好索引的数据库会运行更快速，但是如果出现单个索引的缺失，则会拖慢整个数据库的效率。

因此，我们需要使用 EXPLAIN 来查找缺失的索引，并将其添加上去。

需要注意的是：不要添加您所不需要的索引，因为不必要的索引会反过来拖慢数据库。

拒绝默认设置

就像其他任何软件那样，MySQL 也能通过各种可配置的设置，来修改其行为并最终优化其性能。

同时这些配置的设置经常会被管理员所忽略，并一直保持着默认值的状态。

为了让 MySQL 获得最佳的性能，了解如何配置 MySQL，以及将它们设置为最适合您的数据库环境的状态是非常重要的。

在默认情况下，MySQL 是针对小规模的发布、安装进行调优的，而并非真正的生产环境规模。

因此，通常您需要将 MySQL 配置为使用所有可用的内存资源，并且能允许您的应用程序所需的最大连接数。

这里有三个有关 MySQL 性能优化的设置，值得您去仔细地配置：

innodb_buffer_pool_size

数据和索引被用作缓存的缓冲池。当您的数据库服务器有着大量的系统内存时，可以用到该设置。

如果您只运行 InnoDB 存储引擎，那么您通常可以分配 80％左右的内存给该缓冲池。

而如果您要运行非常复杂的查询或者您有大量的并发数据库连接，亦或您有非常大的数据表的情况，那么就可能需要将此值下调一个等级，以便为其他的调用分配更多的内存。

您在设置 InnoDB 缓冲池大小的时候，要确保其设置既不要过大，也不要频繁引起交换（swapping），因为这些绝对会降低您的数据库性能。有一个简单的检查方法就是在“Percona 监控和管理”。

如图所示，如果你看到有大于 1MB 每秒的持续交换活动的话，您就需要减少缓冲池的大小了，或者使用其他的内存。

如果您一开始并没有将 innodb_buffer_pool_size 的值设置正确，也不必担心。

从 MySQL 5.7 开始，您可以动态地改变 InnoDB 缓冲池的大小，而不需要重新启动数据库服务器了。

innodb_log_file_size

这是指单个 InnoDB 日志文件的大小。默认情况下，InnoDB 使用两个值，这样您就可以通过将其增加一倍，来让 InnoDB 获得循环的重做日志空间，以确保交易的持久性。这同时也优化了对数据库的写入性能。

设置 innodb_log_file_size 的值是很值得推敲的：如果分配了较大的重做空间，那么对于写入密集型的工作负载来说性能会越好。

但是如果您的系统遭受到断电或其他问题导致崩溃的时候，那么其恢复时间则会越长。

您可能会问：怎么才能知道自己的 MySQL 性能是否受限于当前的 InnoDB 日志文件大小呢？

您可以通过查看未实际使用的重做日志空间大小来判定。最简单的方法就是查看“Percona 监控和管理”的 InnoDB 指标仪表板。

在下图中，InnoDB 的日志文件不够大，使用空间已经屡屡接近于可用的重做日志空间了，如红线所示：

因此，您的日志文件应该至少比使用量大 20％，从而保持系统处于最佳的性能状态。

max_connections

大型应用程序通常需要比默认数量多得多的连接。不同于其他的变量，如果您没能将该值设置正确，您就会碰到性能方面的问题。

也就是说，如果连接的数量不足以满足您的应用需求，那么应用程序将根本无法连接到数据库，在用户看来就像宕机了一样。由此可见，将它设置正确是非常重要的。

对于在多台服务器上运行着具有多个组件的复杂应用来说，您想获知到底需要多少个连接是非常困难的。

幸运的是，MySQL 能够在峰值操作时轻易地获悉所用到的连接数量。通常，您需要确保在应用程序所使用到的最大连接数和可用的最大连接数之间至少有 30％的差额。

查看这些数字的一个简单方法是：在“Percona 监控和管理”的系统概述界面中查看使用 MySQL 连接图。

下图显示了一个健康的系统，它有着足够数量的可用额外连接。

还有一点需要记住：如果您的应用程序所创建的连接数量过多，通常会导致数据库运行缓慢。

在这种情况下，您应该在数据库性能上做文章，而不是简单地允许建立更多的连接。更多的连接会使得潜在的性能问题更加恶化。

将数据库载入内存中

近年来，出现了固态硬盘（SSD）方向上的转变。尽管固态硬盘比传统机械旋臂硬盘快得多，但是它们仍然敌不过将数据存在内存里。

这种差别不仅来自于存储性能本身，还来自于数据库从磁盘或 SSD 里存取数据时所产生的额外工作。

随着近年来硬件技术的改进，不管您是运行在云端，还是管理着自己的硬件，将数据库载入内存已经变得可行。

更令人振奋的是：您并不需要将整个数据库载入内存以获得其性能优势，您只需要将最频繁访问的数据集放入其中便可。

您可能已经看过一些文章，有介绍将数据库多少比例（如：10％到 33％）载入到内存里。

而事实上并不存在着“一刀切”的规律，数据的访问量决定着载入内存所获得的最佳性能的提升程度。

您与其去寻找某个特定的“神奇”数字，不如去检查数据库达到稳定运行状态时的 I/O（通常是在它开始运行的几个小时之后）。

请查看一下数据的读取，因为如果您的数据库已载入到内存里的话，那么读取会完全结束；而只要有内存可用，写入操作总是会发生的。

下图是“Percona 监控和管理”的 InnoDB 指标仪表板中的 InnoDB I/O图：

如上图所示，那些峰值高达每秒 2,000 的 I/O 操作表明（至少是流量负载的一部分）它们与载入内存中数据库的数据集并不相配。

使用 SSD 存储

无论您的数据库是否已被载入内存，您都需要使用快速存储来处理写入操作，并且避免在数据库启动后（重启之后）出现性能问题。这里的快速存储就是指固态硬盘。

一些所谓的“专家”仍在基于成本和可靠性的基础上，主张使用机械旋臂硬盘。坦率地说，当涉及到数据库操作时，这些建议往往是过时的或是完全错误的。现如今，固态硬盘的性能已经非常卓越、可靠且价格低廉了。

并非所有的固态硬盘都是同等生产的。对于数据库服务器来说，您应该选用那些专供服务器工作负载、且能精心呵护数据的 SSD。

例如：防止断电损坏的，而避免使用那些专为台式和笔记本电脑设计的商用固态硬盘。

通过 NVMe 或英特尔 Optane 技术来直接连接的 SSD 往往能够提供最佳的性能。

即使远程连接到 SAN、NAS 或云端的块设备上，固态硬盘也能比机械旋臂硬盘提供更为优越的性能。

横向扩展

即使是性能最高的服务器也有局限性。业界一般用两种方法来进行扩展：纵向和横向。

纵向扩展意味着购买更多的硬件。这样做不但成本昂贵，而且硬件折旧速度快。

而横向扩展，则在处理负载方面有如下几点优势：

您可以从更小型、成本更低的系统中获益。
横向扩展使得系统的线性扩展更方便、更快捷。
由于数据库会横跨增长到多个物理机上，横向扩展在保护数据库的同时，消除了硬件单点故障。

尽管横向扩展有着诸多优势，不过它还是具有一定的局限性。横向扩展需要数据复制，例如基本的 MySQL Replication 或是用于数据同步的 Percona XtraDB 群集。

但是作为回报，您也会获得更高的性能和可用性。如果您需要更高级的扩展性，那么请考虑使用 MySQL 分片（sharding）。

另外，您还需要确保连接到群集架构的应用程序可以找到它们所需的数据。这通常是通过诸如 ProxySQL 或 HAProxy 的一些代理服务器和负载平衡器来实现的。

当然，过早地规划横向扩展，会增加分布式数据库的复杂性。最近发布的 MySQL 8 候选版本已声称自己能够在单一的系统上处理超过 200 万个简单查询。

追求可视性

可视性是系统设计的最佳境界，MySQL 也不例外。

一旦完成了 MySQL 环境的搭建、运行并调优，您千万不要认为已经万事大吉了。

数据库环境既会受到来自系统更改或流量负荷的影响，也会遇到例如流量高峰、应用程序错误以及 MySQL 自身的各种问题。

为了快速、有效地解决各种问题，您需要建立和实施一些监控机制，从而能获悉数据库环境的状态，并在出现错误时及时分析服务器上的数据。

因此理想情况就是在系统出现问题或是被用户所察觉之前就做到防范于未然。

常用的监测工具有：

MySQL企业监控器（Enterprise Monitor）。
Monyog。
具有免费与开源版本的 Percona 监控和管理（PMM）。

这些工具在监控和故障排除方面提供了很好的操作可视性。

随着越来越多的公司在大规模生产环境中使用开源的数据库（特别是MySQL）来管理和服务他们的业务数据，他们需要把工作重心放在保持数据库的调优和运行效率上。

MySQL 的确是一款能够提升您的应用程序和网站性能的优秀数据库，当然您需要通过对它进行调整，以满足业务需求，监测、发现并防止任何瓶颈和性能方面的问题。

作者：Peter Zaitsev，陈峻编译

编辑：陶家龙、孙淑娟

来源：https://www.infoworld.com/article/3234637/sql/7-keys-to-better-mysql-performance.html

投稿：有投稿、寻求报道意向技术人请联络 editor@51cto.com

陈峻（Julian Chen），有着十多年的 IT 项目、企业运维和风险管控的从业经验，日常工作深入系统安全各个环节。作为 CISSP 证书持有者，他在各专业杂志上发表了《IT运维的“六脉神剑”》、《律师事务所IT服务管理》和《股票交易网络系统中的安全设计》等论文。他还持续分享并更新《廉环话》系列博文和各种外文技术翻译，曾被(ISC)2 评为第九届亚太区信息安全领袖成就表彰计划的“信息安全践行者”和 Future-S 中国 IT 治理和管理的 2015 年度践行人物。