如何优化mySQL：索引，慢速查询，配置

MySQL 依然是全球最流行的关系型数据库，然而，它也是最容易被低效使用的数据库——许多人使用默认设置，而不去进一步研究。本文将回顾之前介绍过的一些 MySQL 优化技巧，并结合最新的改进方法。

核心要点

• 通过调整关键参数（如 innodb_buffer_pool_size、innodb_log_file_size 和 innodb_flush_method）来优化 MySQL 配置，从而更好地利用服务器资源并提高数据库性能。
• 有效利用索引来加快查询处理速度；根据查询需求和数据唯一性，考虑使用唯一索引、主键索引和全文索引。
• 使用 Percona Toolkit for MySQL 等工具来自动识别和解决重复或未使用的索引等问题，从而优化数据库效率。
• 使用 MySQL 的慢查询日志和 pt-query-digest 等工具来监控和分析慢查询，从而检测瓶颈并优化查询性能。
• 定期运行 MySQL Tuner 和其他性能监控工具，以收集有关数据库操作的见解，并根据实际使用模式微调配置。

配置优化

对 MySQL 进行的第一个也是最容易被忽略的性能提升，就是调整配置。5.7 版本（当前版本）比之前的版本有了更好的默认值，但仍然可以进行改进。

我们假设您使用的是基于 Linux 的主机或像我们改进后的 Homestead 这样的 Vagrant 虚拟机，因此您的配置文件位于 /etc/mysql/my.cnf。您的安装程序可能会将辅助配置文件加载到该配置文件中，因此请检查一下——如果 my.cnf 文件内容不多，则可能是 /etc/mysql/mysql.conf.d/mysqld.cnf 文件。

编辑配置

您需要熟悉使用命令行。即使您以前没有接触过，现在也是个好时机。

如果您在 Vagrant 虚拟机上本地编辑，可以使用 cp /etc/mysql/my.cnf /home/vagrant/Code 命令将文件复制到主文件系统中的共享文件夹中，使用普通的文本编辑器进行编辑，完成后再复制回原处。否则，使用像 vim 这样的简单文本编辑器，执行 sudo vim /etc/mysql/my.cnf 命令。

注意：修改上述路径以匹配配置文件的实际位置——它可能实际上位于 /etc/mysql/mysql.conf.d/mysqld.cnf

手动调整

以下手动调整应该立即进行。根据这些技巧，在 [mysqld] 部分中将以下内容添加到配置文件：

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

• innodb_buffer_pool_size –缓冲池是用于在内存中缓存数据和索引的存储区域。它用于将频繁访问的数据保存在内存中，当您运行专用服务器或虚拟服务器且数据库经常成为瓶颈时，为应用程序的这部分分配最多的 RAM 是有意义的。因此，我们为其分配 50-70% 的所有 RAM。MySQL 文档中提供缓冲池大小调整指南。
• 日志文件大小在此处有很好的解释，但简而言之，它是在擦除日志之前要存储在日志中的数据量。请注意，在这种情况下，日志不是错误日志或您可能习惯的日志，而是指示检查点时间，因为对于 MySQL，写入发生在后台，但仍然会影响前台性能。较大的日志文件意味着更好的性能，因为创建的新的较小的检查点较少，但在发生崩溃时恢复时间较长（需要将更多内容重新写入数据库）。
• innodb_flush_log_at_trx_commit 在此处有解释，它指示日志文件会发生什么情况。使用 1，我们拥有最安全的设置，因为日志在每次事务后都会刷新到磁盘。使用 0 或 2，它 ACID 性较低，但性能更高。在这种情况下，差异不足以超过设置 1 的稳定性优势。
• innodb_flush_method –为了完成刷新工作，将其设置为 O_DIRECT 以避免双缓冲。除非 I/O 系统性能非常低，否则应该始终这样做。在大多数托管服务器（如 DigitalOcean Droplets）上，您将拥有 SSD，因此 I/O 系统的性能将很高。

还有另一个来自 Percona 的工具可以帮助我们自动查找剩余问题。请注意，如果我们在没有上述手动调整的情况下运行它，则只有 4 个修复中的 1 个可以手动识别，因为其他 3 个取决于用户偏好和应用程序的环境。

变量检查器

要在 Ubuntu 上安装变量检查器：

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

对于其他系统，请按照说明操作。

然后，使用以下命令运行工具包：

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

您应该会看到类似于以下输出：

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

这些都不是关键问题，不需要修复。我们唯一可以添加的是用于复制和快照的二进制日志记录。

注意：在较新版本中，binlog 大小将默认为 1G，并且 PT 不会注意到它。

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

• max_binlog_size 设置确定二进制日志的大小。这些日志记录您的事务和查询并创建检查点。如果事务大于最大值，则保存到磁盘时日志可能大于最大值——否则，MySQL 将将其保持在该限制内。
• log_bin 选项完全启用二进制日志记录。没有它，就没有快照或复制。请注意，这可能会对磁盘空间造成很大的压力。激活二进制日志记录时，服务器 ID 是一个必要的选项，因此日志知道它们来自哪个服务器（用于复制），格式只是写入日志的方式。

如您所见，新的 MySQL 具有合理的默认值，使事情几乎可以立即投入生产。当然，每个应用程序都不同，并且有额外的自定义调整适用。

MySQL Tuner

Tuner 将以较长的间隔监控数据库（在实时应用程序上每星期运行一次左右），并根据其在日志中看到的内容建议更改。

只需下载即可安装它：

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

使用 ./mysqltuner.pl 运行它将询问您数据库的管理员用户名和密码，并输出快速扫描的信息。例如，这是我的 InnoDB 部分：

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

同样，重要的是要注意，此工具应该在服务器运行后每星期运行一次左右。更改配置值并重新启动服务器后，应该从那时起的一周后运行它。最好设置一个 cron 作业来为您执行此操作并定期向您发送结果。

---

每次更改配置后，请确保重新启动 mysql 服务器：

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

索引

接下来，让我们关注索引——许多业余数据库管理员的主要痛点！特别是那些立即跳入 ORM 并因此从未真正接触过原始 SQL 的人。

注意：术语键和索引可以互换使用。

您可以将 MySQL 索引与书中的索引进行比较，它可以让您轻松找到包含您要查找主题的正确页面。如果没有索引，您就必须通读整本书才能搜索包含该主题的页面。

您可以想象，通过索引搜索比必须遍历每个页面要快得多。因此，通常情况下，向数据库添加索引可以加快 select 查询的速度。但是，索引也必须创建和存储。因此，更新和插入查询将变慢，并且会占用更多磁盘空间。通常，如果您正确地为表编制了索引，则不会注意到更新和插入的差异，因此建议在正确的位置添加索引。

仅包含几行的表实际上并不受益于索引。您可以想象，搜索 5 页并不比先去索引、获取页码然后打开特定页面慢多少。

那么，我们如何找出要添加哪些索引以及存在哪些类型的索引呢？

唯一/主键索引

主键索引是数据的索引，是寻址数据的默认方式。对于用户帐户，这可能是用户 ID 或用户名，甚至是主要电子邮件。主键索引是唯一的。唯一索引是不能在一组数据中重复的索引。

例如，如果用户选择了一个特定的用户名，则其他人都不应该能够使用它。向用户名列添加“唯一”索引可以解决此问题。如果其他人尝试插入具有已存在用户名的行，MySQL 将会报错。

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

主键/索引通常在表创建时定义，唯一索引是通过更改表来定义的。

主键和唯一键都可以在一个或多个列上创建。例如，如果您想确保每个国家/地区只有一个用户名可以定义，则可以在这两个列上创建唯一索引，如下所示：

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

唯一索引被添加到您经常会访问的列上。因此，如果经常请求用户帐户并且数据库中有许多用户帐户，这是一个很好的用例。

常规索引

常规索引简化查找。当您需要快速查找特定列或列组合的数据但该数据不需要唯一时，它们非常有用。

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

上述操作将加快按国家/地区搜索用户名的速度。

索引还有助于提高排序和分组速度。

全文索引

全文索引用于全文搜索。只有 InnoDB 和 MyISAM 存储引擎支持全文索引，并且仅支持 CHAR、VARCHAR 和 TEXT 列。

这些索引对于您可能需要执行的所有文本搜索都非常有用。全文索引擅长在文本正文中查找单词。如果您经常允许在应用程序中搜索帖子、评论、说明、评论等，请在这些内容上使用这些索引。

降序索引

不是一种特殊类型，而是一种更改。从 8.0 版本开始，MySQL 支持降序索引，这意味着它可以按降序存储索引。当您拥有经常需要先获取最后添加的数据的大型表或优先处理条目时，这会派上用场。始终可以按降序排序，但这会带来一点性能损失。这进一步加快了速度。

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

在处理写入数据库的日志、按从后到前的顺序加载的帖子和评论以及类似内容时，请考虑将 DESC 应用于索引。

辅助工具：Explain

在查看优化查询时，EXPLAIN 工具将是无价的。在简单查询前添加 EXPLAIN 将以非常深入的方式处理它，分析正在使用的索引，并显示命中和未命中的比率。您将注意到它必须处理多少行才能获得您要查找的结果。

<code>max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'</code>

您可以使用 EXTENDED 进一步扩展它：

<code class="language-bash">wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl</code>

请参阅如何使用它以及通过阅读这篇优秀的详细文章来应用发现。

辅助工具：Percona 用于检测重复索引

先前安装的 Percona Toolkit 还提供了一个用于检测重复索引的工具，当使用第三方 CMS 或只是检查是否意外添加了比需要更多的索引时，这会派上用场。例如，默认的 WordPress 安装在 wp_posts 表中具有重复索引：

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

如最后一行所示，它还会提供有关如何删除重复索引的建议。

辅助工具：Percona 用于检测未使用的索引

Percona 还可以检测未使用的索引。如果您正在记录慢查询（请参见下面的“瓶颈”部分），则可以运行该工具，它将检查这些记录的查询是否正在使用与查询相关的表中的索引。

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

有关此工具的详细用法，请参见此处。

瓶颈

本节将解释如何检测和监控数据库中的瓶颈。

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

上述内容应添加到配置中。它将监控执行时间超过 1 秒的查询以及那些未使用索引的查询。

一旦此日志有一些数据，您可以使用前面提到的 pt-index-usage 工具或 pt-query-digest 工具来分析其索引使用情况，该工具会生成如下结果：

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

如果您更喜欢手动分析这些日志，也可以这样做——但首先您需要将日志导出为更易于“分析”的格式。这可以通过以下方式完成：

<code>max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'</code>

其他参数可以进一步过滤数据并确保仅导出重要内容。例如：按平均执行时间排序的前 10 个查询。

<code class="language-bash">wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl</code>

有关其他参数，请参见文档。

结论

在这篇全面的 MySQL 优化文章中，我们研究了各种使 MySQL 运行速度更快的方法。

我们处理了配置优化，我们完成了索引，并且我们摆脱了一些瓶颈。然而，这大部分都是理论上的——有关在实际应用程序上应用这些技术的实际用例，请关注我们即将推出的性能提升项目！

我们错过了任何技术和技巧吗？请告诉我们！

MySQL 索引和慢查询优化常见问题解答 (FAQ)

MySQL 索引在查询优化中的重要性是什么？

MySQL 索引对于查询优化至关重要，因为它们可以显著加快数据检索速度。它们的工作方式类似于书中的索引，允许数据库查找和检索数据，而无需扫描表中的每一行。这会导致查询执行速度更快，尤其是在大型数据库中。但是，需要注意的是，虽然索引提高了读取速度，但它们可能会降低写入速度，因为在插入或更新数据时也需要更新索引。

如何识别 MySQL 中的慢查询？

MySQL 提供了一个名为慢查询日志的有用工具。此工具记录有关所有执行时间超过指定时间的 SQL 查询的信息。您可以在 MySQL 配置文件中启用它，并将 long_query_time 设置为查询在被视为慢查询之前应花费的秒数。

MySQL 索引有哪些不同类型？

MySQL 支持多种类型的索引，包括 B 树、哈希、R 树和全文索引。B 树是默认的索引类型，适用于各种查询。哈希索引用于相等比较，并且对于此类查询的速度比 B 树快。R 树索引用于空间数据类型，全文索引用于全文搜索。

如何优化我的 MySQL 配置？

MySQL 配置优化涉及调整各种服务器变量以提高性能。这包括调整缓冲池大小、日志文件大小和查询缓存大小等。重要的是要定期监控服务器的性能，并根据需要调整这些变量。

哪些工具可以帮助我优化 MySQL 查询和索引？

有几个工具可用于 MySQL 查询和索引优化。这些工具包括 MySQL 的内置 EXPLAIN 语句（提供有关 MySQL 如何执行查询的信息）以及 Percona Toolkit 和 MySQL Workbench 等第三方工具。

EXPLAIN 语句如何帮助查询优化？

MySQL 中的 EXPLAIN 语句提供有关 MySQL 如何执行查询的信息。这包括有关访问的表、访问表的顺序、使用的特定索引以及读取的行数估计的信息。这些信息可以帮助识别潜在的性能问题并指导索引优化。

索引对 MySQL 中的写入操作有什么影响？

虽然索引通过加快数据检索速度来显著提高读取操作，但它可能会降低写入操作的速度。这是因为每次插入或更新数据时，都需要更新相应的索引。因此，在创建索引时，重要的是要在读取和写入操作之间取得平衡。

如何使用索引优化 MySQL 中的 JOIN 操作？

索引可以显著提高 MySQL 中 JOIN 操作的性能。通过在 JOIN 条件中使用的列上创建索引，MySQL 可以快速找到已连接表中的匹配行。这减少了对全文扫描的需求，并导致查询执行速度更快。

查询缓存在 MySQL 性能优化中的作用是什么？

MySQL 中的查询缓存存储 SELECT 查询的结果以及查询本身。当接收到相同的查询时，MySQL 可以从缓存中检索结果，而不是再次执行查询。这可以显著提高性能，尤其对于复杂的查询或频繁执行的查询。

如何监控我的 MySQL 服务器的性能？

MySQL 提供了多个用于监控服务器性能的工具。这些工具包括性能模式（提供详细的性能指标）和信息模式（提供有关数据库元数据的信息）。此外，SHOW STATUS 命令可用于获取有关服务器运行状态的信息。

以上是如何优化mySQL：索引，慢速查询，配置的详细内容。更多信息请关注PHP中文网其他相关文章！