如何分析 SQL 查询以获得更好的性能

已发表: 2023-03-16

在 Servebolt，我们以性能为生。

数据库性能也不例外。

在网站访问者单击链接后执行低效查询会显着降低用户体验。 他们将需要等待慢速查询的整个持续时间执行，这可能需要几秒钟，然后才会发生任何其他操作，例如页面呈现。此等待时间不仅包括查询运行所需的时间，还包括预处理和后处理所需的任何额外时间。因此，设计不当的查询会显着降低网站的整体性能，从而导致令人沮丧的用户体验。

Time to First Byte (TTFB)是一种衡量在用户向网站发出请求后接收到第一个字节数据所需时间的方法。它也是搜索引擎在评估网站时使用的关键指标。 当触发慢查询时，会对 TTFB 产生负面影响。慢速查询运行的时间越长，TTFB 就越高，从而导致整体网站性能变慢，用户体验也不太令人满意。

在本指南中，我们将向您介绍如何分析 SQL 查询——这是维护依赖于数据库响应的 Web 应用程序性能的关键部分。这是一个设置基础的过程，然后能够开始优化这些查询以提高它们的性能。

了解 SQL 查询分析

当您开发 Web 应用程序并开始大规模运行时，曾经顺利运行的 SQL 查询可能会导致性能问题。一般来说，随着每秒请求数的增加，越来越多的查询针对越来越多的数据运行。当性能受到影响时，用户在与您的网站、软件或服务交互时的体验也会受到影响。

查询分析是一种分析数据库查询、评估其性能和识别潜在问题的方法。

通过分析和识别这些有问题的查询，您可以进行特定的改进，从而对其数据库的性能产生可衡量的差异。反过来，这将允许在未来提高可扩展性以及整体客户满意度，因为应用程序和网站将更具响应性。

MariaDB（和 MySQL）提供了多种用于查询分析的工具和技术，我们将在本文中进行介绍。一旦识别出慢速查询，下一步就是优化它们。此过程包括确定问题的根本原因，并更改查询的结构以提高其效率。

如何分析 SQL 查询（7 种方法）

让我们首先分解可用于识别缓慢和低效查询的不同工具和技术，以便您知道应该在哪里集中改进工作：

1 – EXPLAIN EXTENDED命令

可用于分析 SQL 查询的工具之一是EXPLAIN命令。

通过对查询运行 EXPLAIN 命令，您可以看到查询是如何执行的，包括使用了哪些索引，以及检查的行数。

EXPLAIN SELECT * FROM orders

JOIN customers ON orders.customer_id = customers.customer_id

WHERE customers.name = 'John Smith';

当您对查询运行EXPLAIN命令时，它会返回一个包含多个列的结果集，包括：

id: 查询在执行计划中的唯一标识
select_type ：查询的类型，例如 SIMPLE 或 SUBQUERY
table: 正在查询的表
type ：使用的连接类型，例如 JOIN 或 INDEX
possible_keys：MariaDB 或 MySQL 可以用来处理查询的索引
key : MariaDB 或 MySQL 实际用来处理查询的索引
key_len ：使用的密钥的长度
rows ：MariaDB 或 MySQL 估计将为查询检查的行数

Extra ：这包含有关查询的其他信息，例如是否执行了全表扫描或是否使用了临时表。

通过分析EXPLAIN命令的输出，您通常能够识别潜在的性能瓶颈，例如糟糕的索引、次优的连接类型或大量检查的行。

例如，如果类型列显示“ALL”而不是“index”，则查询正在执行全表扫描，这几乎肯定会导致性能下降。如果键列为 NULL，则 MySQL 没有使用任何索引，这也会很慢。如果 rows 列的值很高，则意味着正在检查许多行，从而导致性能进一步下降。

我们更喜欢使用EXPLAIN EXTENDED变体来帮助提供额外信息。

注意：虽然这在 MySQL 中已被弃用，但它在 MariaDB 中仍然可用。

通过使用 EXTENDED 选项，您将能够看到有用的信息，例如检查的行数、返回的行数、有关使用的 JOIN 类型的信息、扫描表的顺序、使用的索引以及持续时间查询被执行。

以下是使用 EXPLAIN EXTENDED 命令的方式：

EXPLAIN EXTENDED SELECT * FROM your_table WHERE column_name = 'value';

在此示例中，EXPLAIN 命令将显示数据库为执行查询将采取的步骤列表，以及它将使用的资源列表。

通过使用此命令，您将能够更轻松地发现查询中的瓶颈，允许您进行任何必要的更改，这将有助于缓解瓶颈并加快查询的性能。

例如，使用 EXPLAIN EXTENDED 命令可以帮助确定是否需要添加索引、优化 JOIN 条件以及限制查询返回的总行数。

您还应该确保在执行此测试和优化时已禁用查询缓存，以确保获得准确的结果。为此，请在连接客户端时先运行此命令。

SET SESSION query_cache_type=0;

对查询进行这些更改后，再次测试其性能以确定已实现多少改进（如果有）。请记住，与查询的任何分析和优化一样，该过程是迭代的——期望多次使用 EXPLAIN EXTENDED 命令，然后进行性能测试。

2 – EXPLAIN ANALYZE命令

此命令用于分析查询的执行计划并返回性能指标，例如查询执行的实际时间和实际检查的行数。通过分析 EXPLAIN ANALYZE 命令的结果，您可以识别查询执行中的任何潜在瓶颈，例如缺少索引或需要检查的大量行。

3 – 慢查询日志

这是 MariaDB（和 MySQL）中的一个内置功能，它记录所有执行时间超过一定时间的查询。慢速查询日志可以配置为记录花费时间超过特定阈值（例如一秒）的查询。

在 Servebolt，慢速查询日志记录了所有执行时间超过 1 秒的查询。这是因为大多数查询应该在几分之一秒内执行。在 Web 应用程序的上下文中，例如运行 WordPress 的站点，加载单个页面需要 10 到 100 个数据库查询，所有这些查询都必须按顺序执行，然后才能将页面编译为 HTML 并返回给用户。

当前的 Servebolt Cloud 配置将慢速查询日志保存在全局日志服务器上。如果有需要，您只需与我们的支持团队联系，我们将过滤相关日志的文件并为您提供输出。

在您自己的环境中，您可以通过将以下行添加到您的 MariaDB 或 MySQL 配置文件（my.cnf 或 my.ini）来启用慢速查询日志：

log_slow_queries = /path/to/slow.log

long_query_time = 1

4 – 视觉解释计划

可视化解释计划提供了EXPLAIN 命令输出的图形表示，使理解查询的执行和检测任何性能问题变得更加容易。

注意：在开发 Web 应用程序的过程中，Visual Explain Plans 很有用。

它不是纯文本输出，而是以树结构显示查询执行，每个节点代表一个表、索引或操作，它们之间的连接描述了操作的顺序。

MySQL Workbench和EXPLAIN Analyzer等不同的工具可以生成可视化的解释计划，并提供交互式界面来导航执行计划和详细检查每个操作。

例如，在 MySQL Workbench 中，生成可视化解释计划就像执行查询并单击结果选项卡上的“解释计划”按钮一样简单。这提供了查询执行计划的图形表示，以及每个操作的详细信息。这使您能够识别任何性能问题，然后根据需要优化查询。

5 – MySQL 调谐器

MySQL Tuner是一个脚本，用于检查数据库服务器的性能和配置并提供改进建议。它提供了当前服务器状态的摘要，包括查询总数、慢查询数和当前缓冲池使用情况等信息。

它还可用于检查各种其他设置，例如数据库版本、正在使用的存储引擎和查询缓存配置，并根据当前工作负载提供优化这些设置的建议。

与其他工具的主要区别之一是它是一个命令行工具，可以在服务器本身或远程运行，从而可以轻松地自动执行监视和优化数据库性能的过程。

注意：如果您的 Web 应用程序（和数据库）已经托管在 Servebolt 云中——这是我们团队的专长，并且能够比工具能够提供的任何建议做得更好。

6 – 查询分析器

有第三方查询分析器可用于分析 SQL 查询，例如MariaDB Enterprise Query Analyzer 、 Dataedo和Percona Toolkit 。与 MariaDB（或 MySQL）中可用的内置工具相比，第三方查询分析器可以提供额外的特性和功能。

注意：当您处于开发 Web 应用程序的过程中时，Query Profiler 很有用。

例如，它们可能会提供有关查询性能的更详细信息，例如执行时间和锁定等待时间，并且可以以内置工具无法实现的方式提供数据可视化。

如果内置工具足以满足您的需求，则无需使用第三方查询分析器。但是，如果您需要更详细的信息或高级功能，那么可能值得考虑使用第三方分析器。

7 – 使用监控工具进行分析

还有许多监控工具，例如 Prometheus、Grafana 和 Nagios，可用于分析查询和监控数据库的性能。

Prometheus是一个高效的监控系统，可以收集、存储和查询指标数据，让您实时获得有价值的洞察。它与 MariaDB（和 MySQL）集成以存储收集的指标，并与 Grafana 一起提供以实现有效的可视化。

Grafana是一种功能强大的开源分析工具，可用于监控和可视化从 Prometheus 收集的数据。设置自定义仪表板和警报可让您实时关注数据库的性能。

Nagios可帮助您始终关注数据库的健康状况。它可以设置为监控关键资源，如 CPU、RAM 和磁盘空间，同时还跟踪其他服务和网络设备。因为它是高度可配置的，所以它是一个用于主动数据库查询监控的好工具。

借助这些服务器监控工具，您可以跟踪性能问题并迅速采取行动，从而确保您的数据库服务器平稳运行。

常见的查询优化技术

有几种常见的查询优化技术可用于提高 SQL 查询的性能：

1 – 索引

索引是一种加快查询速度的方法——尤其是那些使用过滤器(WHERE) 的查询。 使用索引会在特定表之外的数据库引擎（MariaDB 或 MySQL）中产生数据结构，并指向您尝试查询的数据。我们不会在这篇文章中介绍太多细节，因为使用索引来改进数据库查询值得单独写一篇文章——我们计划在未来涵盖这些内容。

例如，考虑一个名为“订单”的大表，其中包含数百万行数据，包括订单 ID、客户 ID 和订单日期等信息。如果执行查询以检索特定客户下的所有订单，而客户 ID 列上没有索引，则 MariaDB 将不得不扫描整个表以找到相关数据。这可能会占用大量时间和资源，尤其是对于大型表。

从广义上讲，只要您确信您将重复运行特定查询并阅读性能问题，创建索引（或多个索引）可能是加快查询速度的正确方法。

在 WordPress 的上下文中，这很常见。许多插件是由开发人员构建的，他们（出于方便）使用通用的共享表而不使用索引。因此，这也是一个经常有非常显着的性能提升的领域。

要查看特定表上存在的任何索引，

您可以使用SHOW INDEX FROM查看特定表上存在的任何索引——例如wp_postmeta表的以下示例：

MariaDB [db_name] > SHOW INDEX FROM wp_postmeta;

在一个场景中，我们最近为 wp_postmeta 表创建了两个索引： sb_postid_metakey和sb_postid_metakey_metaval。

添加这些索引的基础是查看最慢的查询并发现它们都是相对相似的，因为它们是 SELECT 语句的特征，除了大量 (AND/OR) 比较条件外，还使用 WHERE 进行过滤。看到这一点后，我查看了所用表的当前索引，并对查询运行EXPLAIN EXTENDED以进一步验证我的方法。

查询主要是工作并使用wp_postmeta表使用JOIN 。根据发生的顺序，添加这些索引将允许 MariaDB（或 MySQL）从索引中获取响应，而不是扫描整个表及其所有行。

CREATE INDEX sb_postid_metakey ON wp_postmeta (post_id, meta_key);

CREATE INDEX sb_postid_metakey_metaval ON wp_postmeta (post_id, meta_key, meta_value);

这是通过使用您拥有的工具（如上所述）“弄清楚事情”以及数据库的数据类型和内容的知识的组合。这绝不总是有效。即使这样做，也并不总能带来 500% 的性能提升。拥有一个巨大的索引最终可能比扫描所有行更慢，因此必须在应用索引之前和之后测试查询以确保。

注意：尝试测试索引速度时，您需要禁用会话的查询缓存，使用：

SET SESSION query_cache_type=0;

在这种情况下，在使用索引之前，查询需要 10.437 秒才能执行。在创建这两个索引之后，同一个查询花费了 [# 秒]。

2 – 减少数据访问

减少数据访问，即最小化要访问以执行查询的行数和列数。这可以通过过滤查询检索的数据、使用索引和对大型表进行分区来实现。虽然这不是大多数人需要（或能够）做的事情，但从头开始设计数据库查询时要牢记这一点。

例如，如果数据库查询正在查找有关用户的数据以用于登录目的，则查询应为 LIMIT 1，因为显然需要的用户数据永远不会超过一个。

注意：这更多地涉及数据库设计而不是优化。虽然保持性能很重要，但与大多数最终用户相比，这项工作与插件开发人员（在 WordPress 的上下文中）更相关。

请记住，在对数据访问进行任何更改后测试速度之前，您应该确保已通过运行以下命令禁用查询缓存：

SET SESSION query_cache_type=0;

3 – 使用数据分区

通过将数据划分为更小的块，数据库变得更高效且管理起来更省时。此策略有助于减少备份和更新等维护过程所花费的时间，并限制需要管理的数据量。总体而言，它有助于提高性能和优化资源使用。

要对数据库中的数据进行分区，您可以按照以下步骤操作：

选择要分区的表时，请确保选择一个包含大量数据并且会从拆分中受益的表。这将有助于优化您的系统并提高查询性能。
为您的数据库选择正确的分区方法至关重要。您可以从范围、列表、散列或键分区中进行选择，具体取决于您的数据结构和您计划执行的查询。确保选择最适合您优化性能和结果需求的那一款。
1. 当您拥有可以划分为特定范围的数据时，范围分区是理想的选择。例如，如果您有一个包含多年数据的表，您可以创建一个范围分区来更好地组织它。它可以基于相关列的日期或数值。
2. 列表分区是一种处理数据的有效技术，这些数据可以根据特定参数轻松分为不同的组。例如，您有一个表，其中包含按部门分类的员工信息；这需要使用列表分区。
3. 哈希分区是一种基于特定列的哈希值将数据排列成大小相等的簇的有效策略。这允许跨多个分区均匀分布数据，使其成为高效分布数据的绝佳选择。
4. 键分区与散列分区类似，但主要区别在于它使用特定列值作为将数据划分为不同组的基础。这使得它成为可以根据唯一标识符或自然键分成不同组的数据集的理想选择。
通过创建分区表，您可以有效地将原始表分成更小的表。这是通过在 CREATE TABLE 语句中添加分区子句来实现的，您可以在其中指定所需的分割方法和条件。这样做可以帮助提高查询性能，还可以提高数据管理效率。
您可以使用 INSERT INTO ... SELECT 语句将数据从原始表快速复制到新分区的表中。这将很容易地用所有相关信息填充您的分区表。
现在必须重新配置应用程序以利用分区表。这将替换原始表并使您的应用程序更有效率。
在运行任何测试以评估潜在的性能改进之前，必须先通过运行以下命令禁用查询缓存： SET SESSION query_cache_type=0;
为确保分区表平稳运行，密切关注其性能非常重要。如果您发现任何问题，调整分区条件或切换到另一种方法可能会有所帮助。定期监控您的分区将帮助您最大限度地发挥其潜力。

关于脚本升级和分区表的重要说明

虽然分区数据库可以在效率方面产生积极的影响，但重要的是要记住运行升级脚本以更改数据库架构所导致的潜在问题。在编写这些升级脚本时，必须考虑分区表。如果升级脚本中未考虑分区表，则可能存在几乎肯定会导致站点出现故障的潜在问题。

例如，如果创建一个脚本来向分区表中添加一个新列，它可能只会更改一个分区，从而在数据中产生不一致和问题。同样，如果创建升级脚本为分区表添加索引，它只能在一个分区上生成索引，从而导致性能下降和结果不一致。

为避免此类问题，升级脚本的设计必须考虑分区表。这可能涉及在每个分区上单独运行脚本或修改脚本以使用分区表。进行全面测试以确保升级过程不会产生任何意外问题或数据丢失也很重要。

4 – 雷迪斯

对于 Servebolt 客户， Redis是一个（付费）插件，可以帮助优化查询。

Redis（有时称为远程字典服务器）是一种开源解决方案，可将数据存储在内存中，可用于缓存、数据库，甚至用作消息代理。它可以与数据库集成以提高性能，充当应用程序和数据库之间的有效中介。

它通过减少数据库的负载来提高应用程序的性能和响应时间。这是通过将频繁使用的数据存储在 Redis 中而不是每个请求的数据库中来完成的，从而节省了大量时间。

通过正确配置插件，Redis 可以与数据库一起使用以优化查询执行。当 Redis 中不存在所需数据时，应用程序将从数据库中检索它并将其存储在 Redis 中以备将来使用。这使得数据检索更快、更高效。

通过使用这种方法，应用程序可以受益于 Redis 的快速内存访问，还可以根据需要存储和访问数据库中的数据。

请记住，如果您是第一次实施 Redis，则需要在运行任何性能测试之前禁用查询缓存。为此，请使用以下命令：

SET SESSION query_cache_type=0;

结论

MariaDB 和 MySQL 生态系统拥有广泛的工具和方法，可以更轻松地发现数据库查询执行中的瓶颈，从而提高 Web 应用程序的性能。

在运行任何应用程序的整个生命周期中都可能发生减速。尽量避免它们很好，但您最终需要知道在开始诊断性能问题时要查看的位置。根据您运行的数据库的大小和性质，这是一个迭代过程，需要持续监控、故障排除和持续改进，以保持您的数据库以高标准运行。