对于大多数应用程序开发人员来说，数据库是恶魔之神的祭坛，最好不要靠近。 但它不必是这样！
在其他条件相同的情况下，开发人员对底层数据库的熟悉程度决定了他们的资历级别。 很少的数据库和很少的编码经验 = 初级开发人员； 很少的数据库和良好的编码经验 = 中级开发人员； 良好的数据库和良好的编码经验 = 高级开发人员。
这是一个严酷的现实，即使是拥有 6 到 8 年经验的开发人员也难以解释查询优化器的复杂性，并且在被问及 数据库调优 。
为什么？
令人惊讶的是，原因不是懒惰（尽管在某些方面确实如此）。
关键是数据库本身就是一股要与之抗衡的力量。 即使在传统上，当只有关系类型的数据库需要处理时，掌握它们本身就是一个奇迹和职业道路； 如今，我们拥有 如此多类型的数据库 ，以至于不可能指望一个凡人的灵魂掌握一切。
也就是说，很有可能您仍然对关系数据库感到满意，或者您是一个团队的一员，该团队的产品在关系数据库上运行了很长时间。 在十分之九的情况下，您使用的是 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 服务器建立新连接时，它都会使用连接数据打开一个新线程并将其句柄传递给客户端（以防万一您想知道线程是什么，请参阅 this ）。 然后客户端通过该线程发送所有查询并接收结果。 这就引出了一个很自然的问题：MySQL 可以启动多少个线程？ 答案在下一节。
线程池
计算机系统中没有任何程序可以打开任意数量的线程。 原因有两个：1）线程消耗内存（RAM），而操作系统不会让你发疯并吃掉所有的内存。 2）管理，比如说，一百万个线程本身就是一项艰巨的任务，如果 MySQL 服务器可以创建那么多线程，它会在试图处理开销时死掉。
为了避免这些问题，MySQL 附带了一个线程池——固定数量的线程在开始时属于池的一部分。 新的连接请求会导致 MySQL 选择其中一个线程并返回连接数据，如果所有线程都用完了，新连接自然会被拒绝。 让我们看看线程池有多大：
ariaDB [(none)]> show variables like 'thread_pool_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| thread_pool_size | 4 |
+------------------+-------+
所以，我的机器最多同时允许四个连接。 有趣的是，数字 4 来自于我有一个四核处理器的事实，这意味着我的计算机一次只能运行 4 个并行任务（我在这里谈论的是真正的并行任务，而不是并发任务）。 理想情况下，这是应该推动价值的限制 thread_pool_size，但在更强大的机器上增加它确实有好处。 如果您不想让所有新连接等待并且可以承受一些性能损失（同样，这是您可以根据应用在负载下的性能来判断的最佳区域），将其提高到 8 个可能是个好主意。
但是，除非您拥有 32 核机器，否则将其设置为超过 16 是一个糟糕的主意，因为性能会显着下降。 MySQL 中线程池的兔子洞很深，但如果你有兴趣， 这里 有更详细的讨论。
等待和超时
一旦创建了线程并将其附加到客户端，如果客户端在接下来的几秒钟（或几分钟）内没有发送任何查询，那将是资源的浪费。 因此，MySQL 在一段时间不活动后终止连接。 这是由 wait_timeout多变的：
MariaDB [(none)]> show variables like 'wait%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
结果值以秒为单位。 所以是的，默认情况下 MySQL 设置为等待 8 个多小时才能切断电源线！ 如果您有长时间运行的查询并且实际上想要等待它们，这可能会很好（但即便如此，八小时也是荒谬的！）但在大多数情况下会很糟糕。 运行查询时，此值设置为 0（意味着永远），但通常应将其设置为非常低的值（例如 5 秒，甚至可能更少）以释放连接以供其他进程使用。
调整临时表
让我们从 MySQL 中的临时表开始。
假设我们有一个结构如下的 MySQL：TABLE A UNION (TABLE B INNER JOIN C)。 也就是说，我们有兴趣连接表 B 和 C，然后将结果与表 A 合并。现在，MySQL 将首先连接表 B 和 C，但在执行合并之前，它需要将此数据存储在某处。 这就是临时表的用武之地——MySQL 使用它们在复杂查询的中间阶段临时存储数据，一旦查询结束，这个临时表就会被丢弃。
现在的问题是：我们为什么要为这一切烦恼？
仅仅是因为临时表，只是一个查询结果，是 MySQL 在计算中使用的数据，它的访问速度（以及其他限制）将决定查询执行的速度。 例如，将临时表存储在 RAM 中将比将其存储在磁盘上快几倍。
有两个变量控制这种行为：
MariaDB [(none)]> show variables like 'MariaDB [(none)]> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
MariaDB [(none)]> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
第一个， max_heap_table_size, 告诉我们一个 MySQL 表可以使用多少 RAM（这里的“heap”指的是 RAM 分配和管理中使用的数据结构—— 在此处 ），而第二个， 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 的唯一方法——高效编写查询是另一件大事，但我将在另一篇文章中讨论。 但关键是，即使您已经进行了神一般的分析并将这些参数调整到最佳状态，您仍然有可能让一切戛然而止。
你最喜欢调优的系统变量是什么？ ?
