为了提高性能，调整MySQL系统变量 HTML标签

对于大多数应用程序开发者来说，数据库就像是一个不可接近的恶魔神的祭坛。但事实并非如此！

其他条件相同的情况下，开发人员对底层数据库的熟悉程度决定了他们的资深程度。对数据库和编码经验了解较少=初级开发人员；对数据库了解较少但编码经验丰富=中级开发人员；对数据库和编码经验都很好=高级开发人员。

令人惊讶的是，即使是有着6-8年经验的开发人员也很难解释查询优化器的复杂性，并且在被问及database tuning时宁愿仰望天空。

为什么呢？

出乎意料的是，并不完全是因为懒惰（尽管在某种程度上是这样）。

问题在于，数据库是一个独立的力量，需要应对。即使在传统上，当只有关系型数据库需要处理时，掌握它们也是一个奇迹和职业道路；如今，我们有链接_1>，不可能指望一个人掌握所有这些。

话虽如此，你很有可能仍然满意关系型数据库，或者是作为一个团队的一部分，有一个在关系型数据库上运行良好的产品。在十个案例中，你使用的是MySQL（或MariaDB）。对于这些情况，深入了解一下底层原理将大大提高应用程序性能，并且绝对值得学习。

好奇吗？那就开始吧！

不好奇？好吧，无论如何都要开始，因为你的职业生涯取决于它！ 😛

优化MySQL查询缓存

在计算机领域，几乎所有的优化都归结为缓存。一方面，CPU维护着多层缓存以加速计算，另一方面，Web应用程序大量使用缓存解决方案（如Redis）向用户提供预先计算的结果，而不是每次都访问数据库。

但是，甚至可怜的MySQL数据库也有自己的查询缓存！也就是说，每次查询时，如果数据仍然是过时的，MySQL将提供这些缓存结果，而不是再次运行查询，从而使应用程序变得非常快速。

你可以在数据库控制台中运行以下查询来检查是否有查询缓存可用（注意，可用，而不是已启用）：

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

所以，你可以看到我正在运行MariaDB，并且我可以启用查询缓存。如果使用标准的MySQL安装，很不可能将其关闭。

现在让我们看看是否实际上已经启用了查询缓存：

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+

是的，我已经启用了。但是如果你没有启用，可以通过以下方式来启用：

MariaDB [(none)]> SET GLOBAL query_cache_type = ON;

有趣的是，这个变量还接受第三个值，表示“按需”，这意味着MySQL只缓存我们告诉它缓存的那些查询，但我们不会在这里详细介绍。

通过这个操作，你已经启用了查询缓存，并迈出了打造更强大的MySQL环境的第一步！我说的是第一步，因为虽然启用查询缓存是一项重大改进，但我们仍然需要调整查询缓存以适应我们的设置。所以让我们学习如何做到这一点。

这里还有一个有趣的变量query_cache_size，它的功能是不言而喻的：

MariaDB [(none)]> SHOW VARIABLES LIKE ‘query_cache_size';
+——————+———-+
| Variable_name | Value |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

所以，我有一个大约16 MB大小的查询缓存。请注意，即使打开了查询缓存，但如果这个大小为零，缓存实际上是关闭的。这就是为什么仅仅检查一个变量是不够的。现在，你应该设置一个查询缓存大小，但应该设置多大呢？首先，请注意查询缓存功能本身需要4 KB的内存来存储元数据，所以你选择的大小应该在这之上。

假设你将查询缓存大小设置为500 KB：

MariaDB [(none)]> SET GLOBAL query_cache_size = 500000;

这样做足够吗？实际上不够，因为查询引擎的实际表现取决于更多的因素：

首先，query_cache_size变量必须足够大，以容纳你的查询结果。如果太小，什么都不会被缓存。
其次，如果query_cache_size设置为太大的数值，将会出现两种问题：1）引擎将不得不在这个海量的内存区域中进行额外的存储和定位查询结果的工作。2）如果大多数查询结果的大小要小得多，缓存将会变得碎片化，使用缓存的好处将会丧失。

如何知道缓存是否被碎片化了？可以通过以下方式检查缓存中的总块数：

MariaDB [(none)]> show status like 'Qcache_total_blocks';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Qcache_total_blocks | 33    |
+---------------------+-------+

如果这个数字很高，说明缓存已经碎片化了，需要刷新缓存。

所以，为了避免这些问题，确保选择明智的query_cache_size大小。如果你感到沮丧，因为我没有给你一个具体的数字，恐怕一旦你超越了开发进入工程领域，事情就是这样。你必须研究你正在运行的应用程序，看看重要查询结果的查询大小是多少，然后设置这个数字。即使这样，你可能还是会犯错误。 🙂

线程、线程池、等待和超时

这可能是MySQL工作方式中最有趣的部分，正确地理解它可以让你的应用程序运行速度提高数倍！

线程

MySQL是一个多线程服务器。这意味着每当有一个新的连接到MySQL服务器时，它会打开一个新的线程，带有连接数据，并将一个句柄传递给客户端（如果你想知道为什么，请阅读这篇文章）。然后，客户端通过这个线程发送所有查询并接收结果。这引出了一个自然的问题：MySQL可以启动多少个线程？答案在下一部分中。

线程池

在计算机系统中，没有任何程序可以随意打开任意数量的线程。原因有两个：1）线程占用内存（RAM），操作系统不会允许你疯狂地使用并消耗所有内存。2）管理，比如说一百万个线程本身就是一个巨大的任务，如果MySQL服务器可以创建那么多线程，它会因为处理该负担而崩溃。

为了避免这些问题，MySQL配备了一个线程池——一组在开始时就作为池中一部分的固定数量的线程。新的连接请求会导致MySQL选择其中一个线程并返回连接数据，如果所有线程都已被使用完，则自然拒绝新的连接。让我们看看线程池的大小：

ariaDB [(none)]>显示变量如'线程池大小';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| thread_pool_size | 4     |
+------------------+-------+

因此，我的计算机同时允许最多四个连接。有趣的是，数字4是因为我有一个四核处理器，这意味着我的计算机一次只能运行4个并行任务（这里我说的是真正的并行任务，而不是并发任务）。理想情况下，这个限制应该驱动thread_pool_size的值，但在性能更强的机器上增加它确实会有一定的好处。如果你不希望所有新连接都等待，并且可以承受一些性能损失（再次强调，根据你的应用在负载下的性能，这是一个你可以最好判断的领域），将其增加到8可能是一个不错的主意。

然而，除非你有一台32核的机器，否则将其设置超过16是一个可怕的主意，因为性能会显著下降。MySQL中的线程池的坑很深，但如果你感兴趣，还有一个更详细的讨论。

等待和超时

一旦一个线程被创建并附加到一个客户端，如果客户端在接下来的几秒钟（或几分钟）内没有发送查询，那么这将是一种资源的浪费。因此，MySQL在一段时间的不活动后终止连接。这由wait_timeout变量控制：

MariaDB [(none)]>显示变量如'等待%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

结果值以秒为单位。是的，默认情况下，MySQL设置为在切断连接之前等待8个小时以上！如果你有长时间运行的查询并且确实希望等待它们（但即使是这样，八个小时也太荒谬了！），这可能是好事，但在大多数情况下是可怕的。当运行查询时，该值被设置为0（意味着永远），但通常情况下，应将其设置为一个非常低的值（例如5秒，甚至更短）以释放连接供其他进程使用。

调整临时表

让我们从MySQL中的临时表开始。

假设我们有一个结构上像这样的MySQL：表A UNION（表B INNER JOIN C）。也就是说，我们对表B和C进行连接，然后将结果与表A进行联合。现在，MySQL首先进行表B和C的连接，但在执行联合操作之前，它需要将这些数据存储在某个地方。这就是临时表的用途 – MySQL使用它们在复杂查询的中间阶段临时存储数据，一旦查询结束，这个临时表就会被丢弃。

现在的问题是：为什么我们要费心这些呢？

简单地说，临时表只是一个查询结果，它是MySQL在计算中使用的数据，其访问速度（以及其他限制）将决定查询执行的速度。例如，将临时表存储在RAM中比将其存储在磁盘上快几倍。

有两个变量控制这种行为：

MariaDB [(none)]>显示变量如'MariaDB [(none)]>显示变量如'tmp_table_size'；
+----------------+----------+

| Variable_name  | Value    |

+----------------+----------+

| tmp_table_size | 16777216 |

+----------------+----------+
'；
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+

MariaDB [(none)]>显示变量如'tmp_table_size'；
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+

第一个变量max_heap_table_size告诉我们一个MySQL表可以使用多少RAM（这里的“heap”是指在RAM分配和管理中使用的数据结构 — 在这里了解更多信息 here），而第二个变量tmp_table_size则显示了临时表的最大大小。在我的情况下，这两个变量都设置为16 MB，但我想要表达的是，仅增加tmp_table_size并不起作用，因为总体而言，MySQL仍然会受到max_table_heap_size的限制。

现在来到关键点：如果正在创建的临时表大于这些变量允许的限制，MySQL将被迫将它们写入硬盘，导致性能极差。我们现在的任务很简单：尽力猜测临时表的最准确数据大小，并调整这些变量到该限制。然而，我想警告一下不要荒谬：当你的大部分临时表的大小都小于24 MB时，将此限制设置为16 GB（假设你有这么多RAM）是愚蠢的 —— 你只是在浪费可能被其他查询或系统的一部分（例如缓存）使用的RAM。

结论

一篇文章不可能涵盖所有系统变量，甚至在MySQL的官方文档本身涵盖了几千个字的情况下，也不可能只在一篇文章中涵盖所有重要的变量。虽然我们在这里涵盖了一些通用的变量，但我鼓励你查阅你所使用的引擎的系统变量（InnoDB或MyISAM）。

我写这篇文章最希望实现的目标是让你记住三件事：

MySQL是一个在操作系统设置的限制下工作的典型软件。它不是一些做着神秘的神仙操作并且无法被驯服的程序。而且，值得庆幸的是，理解它是如何设置和通过系统变量进行控制的并不那么困难。
没有单一的设置可以让你的MySQL安装飞一般的运行。你只能在你运行的系统中寻找答案（记住，优化是在应用程序投入生产之后进行的，而不是之前），做出最好的猜测和测量，并接受它永远不会完美的现实。
调整变量并不是优化MySQL的唯一方法 —— 高效编写查询也是一个重要的因素，但这是我将在另一篇文章中讨论的内容。但关键是，即使你已经进行了类似神一样的分析，并将这些参数调整到最佳状态，仍然有可能使一切陷入停顿。

你最喜欢调整的系统变量是什么？🙂