8.1 优化概述

数据库性能取决于数据库级别的几个因素,例如 table,查询和配置设置。这些软件结构导致在硬件级别执行 CPU 和 I/O 操作,您必须将这些操作最小化并使其尽可能高效。在研究数据库性能时,首先要学习软件方面的高级规则和准则,并使用时钟时间来衡量性能。成为 maven 后,您将了解有关内部情况的更多信息,并开始测量诸如 CPU 周期和 I/O 操作之类的东西。

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

在数据库级别进行优化

使数据库应用程序快速运行的最重要因素是其基本设计:

  • table 格的结构是否正确?特别是,这些列是否具有正确的数据类型,并且每个 table 是否都具有适用于工作类型的列?例如,执行频繁更新的应用程序通常具有许多 table 而具有很少的列,而分析大量数据的应用程序通常具有较少的 table 而具有很多列。

  • 是否有合适的indexes可以提高查询效率?

  • 您是否为每个 table 使用了适当的存储引擎,并利用了所使用的每个存储引擎的优势和功能?特别是,对于性能和可伸缩性,选择事务存储引擎(例如InnoDB)或非事务性存储引擎(例如MyISAM)可能非常重要。

Note

InnoDB是新 table 的默认存储引擎。实际上,先进的InnoDB性能功能意味着InnoDBtable 经常胜过简单的MyISAMtable,尤其是对于繁忙的数据库。

  • 每个 table 都使用适当的行格式吗?该选择还取决于 table 使用的存储引擎。特别是,压缩 table 使用较少的磁盘空间,因此需要较少的磁盘 I/O 来读写数据。压缩适用于具有InnoDB个 table 的所有工作负载以及只读的MyISAMtable。

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

  • 所有用于缓存的内存区域的尺寸都正确吗?也就是说,足够大以容纳经常访问的数据,但又不能太大以至于它们会使物理内存过载并导致分页。要配置的主要内存区域是InnoDB缓冲池,MyISAM键高速缓存和 MySQL 查询高速缓存。

在硬件级别进行优化

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

  • 磁盘搜索。磁盘查找数据需要花费时间。对于现代磁盘,此操作的平均时间通常小于 10 毫秒,因此理论上我们可以执行约 100 秒钟的搜索。这段时间随着新磁盘的使用而缓慢改善,并且很难为单个 table 进行优化。优化寻道时间的方法是将数据分发到多个磁盘上。

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

  • CPU 周期。当数据位于主存储器中时,我们必须对其进行处理以获得结果。与内存量相比,拥有较大的 table 是最常见的限制因素。但是对于小桌子,速度通常不是问题。

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

平衡便携性和性能

要在可移植的 MySQL 程序中使用面向性能的 SQL 扩展,可以在/*! */Comments 定界符内的语句中包装特定于 MySQL 的关键字。其他 SQL Server 忽略 Comments 的关键字。有关编写 Comment 的信息,请参见第 9.6 节“Comments 语法”