MySQL优化--官方文档翻译

本章介绍如何优化MySQL性能并提供示例。优化包括在多个级别上配置、调优和度量性能。根据您的工作角色（开发人员、DBA或两者的组合），您可以在单个SQL语句、整个应用程序、单个数据库服务器或多个联网数据库服务器的级别上进行优化。有时，您可以积极主动地提前计划性能，而有时，您可能会在出现问题后对配置或代码问题进行故障排除。优化CPU和内存使用也可以提高可伸缩性，允许数据库在不降低速度的情况下处理更多负载。

8.1优化概述

8.2优化SQL语句

8.3优化与指标

8.4优化数据库结构

8.5 InnoDB表的优化

8.6优化MyISAM表

8.7内存表优化

8.8了解查询执行计划

8.9控制查询优化器

8.10缓冲和缓存

8.11优化锁定操作

8.12优化MySQL服务器

8.13绩效衡量（基准）

8.14检查服务器线程（进程）信息

8.1优化概述

数据库性能取决于数据库级别的几个因素，例如表、查询和配置设置。这些软件结构会导致在硬件级别执行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扩展，可以在/*！*/注释分隔符。其他SQL服务器忽略已注释的关键字。有关撰写评论的信息，请参阅第9.7节“评论”。