慢查询基础：为什么查询慢？

介绍

如果您曾经使用过数据库，那么您可能会遇到一些查询，它们的执行时间似乎比它们应该的要长。由于各种原因，从索引使用不当到存储引擎本身的错误，查询可能会变慢。然而，在大多数情况下，查询变得缓慢是因为开发人员或 MySQL 数据库管理员忽略了监控它们并密切关注它们的性能。在本文中，我们将弄清楚如何避免这种情况。

什么是查询？

要了解是什么导致查询变慢并提高其性能，我们必须从最底层开始。首先，我们必须从根本上理解查询是什么——一个简单的问题，对吧？但是，许多开发人员甚至非常有经验的数据库管理员可能无法回答。

MySQL查询执行顺序

查询是由较小的任务组成的任务。如果我们想提高它们的性能，我们可以缩短那些较小的任务或消除它们。以下是 MySQL 执行查询的方式：

首先，MySQL 将 SQL 语句发送到服务器执行。
MySQL 检查查询缓存。如果那里存在结果，则返回它们。如果没有，MySQL 继续下一步。
服务器解析给定的 SQL 查询，并制定查询优化计划。
执行计划，调用存储引擎。
存储引擎返回行——我们看到了结果！

然而，要观察 MySQL 如何在更深层次上执行其查询，我们可能应该对它们进行分析，而不是简单地查看它们的外部。这就是真正的乐趣开始的地方。

分析查询

到目前为止，我们已经触及了查询运行的外部。尽管如此，为了优化我们的慢查询，在尝试优化任何东西之前，我们必须首先了解它们做了什么以及它们经历了哪些执行阶段——换句话说，分析它们。

值得庆幸的是，如果我们想利用 MySQL 中分析提供的功能，我们不会有麻烦——这真的非常容易做到。

在 MySQL 中使用显示配置文件

通过运行启用分析SET profiling = 1;，运行您的查询，然后SHOW PROFILES;在 MySQL 上发出 a 。您现在应该能够查看您的 MySQL 查询经历了哪些阶段以及每个阶段执行所需的时间（下表表示SELECT分析查询时返回的状态和持续时间- 整个表实际上要大一些，但由于我们只对状态和持续时间感兴趣，我们将观察到）：

状态	期间
开始	0.000017
等待查询缓存锁定	0.000004
等待查询缓存互斥锁	0.000006
检查查询缓存以进行查询	0.000027
检查权限	0.000005
开桌	0.000276
系统锁	0.000010
等待查询缓存锁定	0.000022
等待查询缓存互斥锁	0.000021
在里面	0.000007
优化	0.000005
统计数据	0.000004
准备	0.000055
执行	0.000042
发送数据	0.000004
结尾	0.000002
查询结束	0.000067
收尾表	0.000005
释放物品	0.000056
记录慢查询	0.000001
打扫干净	0.000002

明白我们所说的“查询是由较小的任务组成的任务”是什么意思吗？上面列出了这些较小的任务。既然我们知道我们的查询在结果到达我们的屏幕之前经历了哪些任务，优化应该会更容易。

优化您的查询

为了优化您的查询性能，请缩短这些任务的执行时间或让 MySQL 根本不执行它们。“我怎么做？” – 你问？有几种方法可以在这个复杂的丛林中导航：

行动	解释
确保使用适当的权限	检查权限是执行查询时首先发生的事情之一。通过确保您运行查询的用户具有适当的权限，您可以缩短任务。
保持查询缓存足够大	`query_cache_size`如果您正在运行 MySQL <= 5.7.20，请使用该变量。但请注意，此变量已在 MySQL 8 中删除。
正确使用索引和分区	虽然它们都会降低`INSERT`s、`UPDATE`s 和`DELETE`s的性能，但它们会加快 read ( `SELECT`) 查询的速度，有时会提高一个数量级。使用索引，MySQL 将能够`SELECT`更快地执行查询，因为它将扫描更少的数据——因此，您的查询将更快。如果需要，您还可以使用`log_queries_not_using_indexes`参数记录所有不使用索引的查询，但为此，您需要启用该`slow_query_log`参数（将其设置为的值`ON`）。
不要向 MySQL 询问您不需要的数据	不要使用`SELECT *`，而只选择查询成功执行所需的列。换句话说，不要让 MySQL 检查不必要的数据。
如果需要，请启用慢查询日志。	正如你所看到的，如果一个查询很慢，它会被记录在慢查询日志中（它是最后执行的任务之一）——保持你的`slow_query_log`变量`ON`，也许可以调整 MySQL 理解什么是“慢查询”的方式：默认情况下， MySQL 会记录运行时间超过 10 秒的任何查询，但可以通过调整`long_query_time parameter`.

分析后做什么

一旦您分析了您的查询，您就应该走上一条良好的道路。您应该走上一条良好的道路，因为分析可以帮助您查看查询的过去，使它们在未来具有更高的性能。但是，您还需要注意以下几点：

需要注意什么？	为什么？
架构和数据类型	正确优化架构和数据类型对于高性能至关重要；永远不要忽视它们——你会让你自己作为 DBA 的生活变得更加艰难。如果您忽略了这些事情，很可能会浪费硬盘空间，并可能在未来降低数据库实例的性能。
字符集和排序规则	MySQL 支持许多不同的字符集和排序规则。它们中的每一个都必须小心使用，以节省空间并避免对性能产生负面影响。在这种情况下，请避免使用未知血统的字符集和排序规则；查阅 MySQL 手册或像这样的博客，学习然后应用你的知识。
索引	MySQL 支持多种索引，这些索引可以通过某种方式进行扭曲，以帮助实现各种目标：当然，它们有自己的优点和缺点。然而，在大多数用例中，它们可以显着提高性能——我们在一篇较旧的博客文章中深入研究了索引，所以为了进一步解释，请阅读。
分区	分区当然是索引的好朋友，它们也有助于提高查询性能——但是，只有在正确使用的情况下。例如，它们可以让 MySQL 一次备份或恢复与一个分区相关的数据，而不是所有数据。我们之前已经深入研究了分区，因此如果您想在更深层次上弄清楚它是如何工作的，请阅读。
服务器设置	服务器设置是 MySQL 实例的基石——时刻注意它们。我们将有一篇关于这个特定主题的博客文章，但就目前而言，学习如何为大数据集优化 InnoDB 应该可以解决问题。
硬件	硬件位于你在 MySQL 中所做的一切——通过拥有合适的硬件，你可以正确优化服务器设置以获得高性能。因此，请始终根据需要使用尽可能多的硬件，并为 MySQL 分配足够的资源，使其以最佳状态运行。
博客和文档	当然，像 Arctype 博客这样的博客也是扩展知识的理想场所。他们可以告诉您应该如何使用数据库实例来随时随地提高数据库性能。始终关注文档，这对您的数据库现在和将来都至关重要。

处理好上述所有事情，一旦有了，请确保使用正确的 SQL 客户端！