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

“

随着耗电量和阻抗的增加，MySQL 的操控性会渐趋较慢。这里有九点提议能确保 MySQL 的相对平稳运转。

操控性是他们来衡量应用领域的一类形式，而应用领域操控性的几项分项是采用者新体验，也是平常他们常说的：“采用者需要等待少于科学合理的时间，就可以赢得他们想的东西吗？”

在不同的情况和情景下，该分项会略有不同。比如：对终端网购应用领域而言，其积极响应时间不能少于几秒；而对两个雇员的老龄网页而言，其积极响应时间则容许比几秒较长。

因此，无论是怎样的国际标准，保持插件的较好操控性都是非常重要的，不然就会引起采用者的埋怨（或更为严重的是采用者急于采用其它的应用领域）。而资料库操控性是影响插件操控性的不利因素众所周知。

能说，插件、中文网站和数据库之间的可视化会直接影响到应用领域服务水准的逐步形成。

这种可视化的两个核心理念重要组成部分是：各式各样插件怎样去查阅资料库，以及资料库是怎样积极响应各式各样允诺的。

无论是哪一类国际标准，MySQL 都是当下最盛行的资料库信息系统之一。愈来愈多的企业已将 MySQL（和其它开放源码的资料库）视作其生产自然环境中的资料库软件系统。

MySQL 有许多实用性形式能升用的资料库能加速地积极响应各式各样查阅，同时仅对插件操控性导致微小的上升。

下列就是能协助您强化 MySQL 资料库操控性的 7 点必不可少基本功：

自学怎样采用EXPLAIN

创建正确的索引

拒绝默认设置

将资料库载入内存中

采用SSD存储

横向扩展

追求可视性

自学怎样采用 EXPLAIN

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

应用领域实体之间怎样被映射到各个数据表（资料库模式架构）上。

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

提议您认真自学怎样去采用 EXPLAIN 命令，而不是凭空猜想。该命令会向您展示查阅是怎样被执行的；并深入地演示有关操控性的真实表现情况，以及查阅是怎样伴随着数据量的变化进行扩展的。

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

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

如下所示：

其中您需要重点查看的部分是：查阅成本。查阅成本是指基于查阅执行的总体成本和许多不同的不利因素考虑，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

投稿：有投稿、寻求报道意向技术人请联络 [email protected]

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

精彩文章推荐：

Python将被纳入高考，小学生都在学Python，你慌了吗？

如果你在路上遇见了DevOps，干掉它！

硅谷践行工程文化的6条军规，看你的团队差在哪？