【翻译】MySQL 优化概述

原文链接:https://dev.mysql.com/doc/refman/8.0/en/optimize-overview.html


数据库性能取决于数据库层面的多个因素,例如表、查询和配置。这些软件构造会导致硬件层面的 CPU 和 I/O 操作,你必须尽可能最小化并提高效率。当你刚开始优化数据库性能的时候,首先你要学习软件方面的高级规则和准则,和使用系统时间测量性能。当你成为专家后,你将了解更多的内部细节,并开始测量 CPU 周期和 I/O 操作等指标。

普通用户的目标是从现有的软件和硬件配置中获得最佳的数据库性能。高级用户则会寻找机会来改进 MySQL 软件本身,或者开发自己的存储引擎和硬件设备来扩展 MySQL 生态系统。

数据库层面优化

使数据库应用变快的最重要因素是其基本设计:

表结构是否正常?特别是列是否具有正确的数据类型,并且每个表是否具有适用于工作类型的相应列?例如,需要频繁更新的应用程序通常有许多表,但列很少,而分析大量数据的应用程序通常只有很少的表,但有许多列。

是否使用了正确的索引来提高查询效率?

你是否为每个表选择适当的存储引擎,并且充分利用每种存储引擎的优势和功能?特别是选择事务性存储引擎(如 InnoDB)还是非事务性存储引擎(如 MyISAM)对于性能和可伸缩性非常重要。

注意
InnoDB 是新表的默认存储引擎。实际上,先进的 InnoDB 性能特点意味着 InnoDB 表通常优于更简单的 MyISAM 表,尤其是对于繁忙的数据库。

是否每个表都使用适当的行格式?此选项还取决于用于表的存储引擎。特别是,压缩表占用的磁盘空间较少,因此读取和写入数据所需的磁盘 I/O 更少。压缩可用于具有 InnoDB 表的所有类型的工作负载以及只读的 MyISAM 表。

应用程序是否使用适当的锁定策略?例如,在可能的情况下允许共享访问,以便数据库操作可以同时运行,并在适当时请求独占访问,以便关键操作获得最高优先级。同样,存储引擎的选择也非常重要。InnoDB 存储引擎无需您参与即可处理大多数锁定问题,从而在数据库中实现更好的并发性,并减少代码的实验和调优量。

所有用于缓存的内存区域大小是否正确?也就是说,足够大,可以容纳频繁访问的数据,但规模不够大,以至于使物理内存过载并导致分页。要配置的主要内存区域是 InnoDB 缓冲池和 MyISAM 密钥缓存。

硬件层面优化

随着数据库变得越来越繁忙,任何数据库应用程序最终都达到硬件限制。DBA 必须评估是否可以调整应用程序或重新配置服务器以避免这些瓶颈,或者是否需要更多的硬件资源。系统瓶颈通常来自以下来源:

磁盘查找。磁盘查找数据段需要时间。对于现代磁盘,其平均时间通常低于 10 毫秒,因此理论上我们可以做大约 100 个查找秒。此时间使用新磁盘进行缓慢改进,并且很难针对单个表进行优化。优化寻道时间的方法是将数据分发到多个磁盘上。

磁盘读取和写入。当磁盘处于正确位置时,我们需要读取或写入数据。使用现代磁盘,一个磁盘至少可提供 10*20MB/s 的吞吐量。这比查找更容易优化,因为可以从多个磁盘并行读取。

CPU 周期。当数据位于主内存中时,我们必须处理它以获得结果。与内存量相比,使用大型表是最常见的限制因素。但是,对于小桌子,速度通常不是问题。

内存带宽。当 CPU 需要的数据超过 CPU 缓存容量时,主内存带宽将成为瓶颈。对于大多数系统来说,这是一个不常见的瓶颈,但需要注意。

平衡可移植性和性能

要在便携式 MySQL 程序中使用面向性能的 SQL 扩展,你可以把语句中的 MySQL 特有关键字放到 /*! */ 注释分隔符里面。其他 SQL 服务器会忽略注释的关键字。有关撰写注释的信息,请看 Section 9.6, “Comment Syntax”

1,808 total views, 15 views today

Leave a Reply

Your email address will not be published. Required fields are marked *