什么是查询离群值及其解决方法_数据库_博客_测评&博客

在MySQL的数据库工作负载是由它处理查询的数量来确定。在许多情况下，MySQL速度缓慢都可能产生。第一种可能性是，如果有任何查询未使用正确的索引。当查询无法使用索引时，MySQL服务器必须使用更多的资源和时间来处理该查询。通过监视查询，您可以查明是减速的根本原因的SQL代码，并在整体性能降低之前进行修复。

在此博客文章中，我们将重点介绍ClusterControl中可用的“查询异常值”功能，并了解它如何帮助我们提高数据库性能。通常，ClusterControl通过两种方式执行MySQL查询采样：

从性能模式中获取查询（推荐）。
解析MySQL慢查询的内容。

如果禁用了性能架构，则ClusterControl将默认为慢查询日志。要了解有关ClusterControl如何执行此操作的更多信息，请查看此博客文章“如何对MySQL，MariaDB和Percona Server使用ClusterControl查询监视器”。

什么是查询离群值？

离群值是比该类型的正常查询时间更长的查询时间。不要从字面上将其视为“写得不好”的查询。应该将其视为可以改进的潜在次优常见查询。经过大量采样后，当ClusterControl具有足够的统计信息时，它可以确定延迟是否高于正常值（2 sigma + average_query_time），那么它是一个离群值，将被添加到查询离群值中。

此功能取决于“热门查询”功能。如果启用了“查询监视”并且捕获并填充了“热门查询”，则“查询异常值”将汇总这些内容并提供基于时间戳的过滤器。要查看需要注意的查询列表，请转到ClusterControl-> Query Monitor-> Query Outliers，并应列出列出的一些查询（如果有）：

从上面的屏幕快照中可以看到，异常值基本上是比平均查询时间长至少2倍的查询。首先是第一个条目，平均时间是34.41 ms，而异常值的查询时间是140 ms（比平均时间长2倍多）。同样，对于下一个条目，“查询时间”和“平均查询时间”列是两个重要的事实，可用来证明特定异常查询的优劣。

通过查看更长的时间段（例如一周前），相对容易找到特定查询异常值的模式，如以下屏幕截图所示：

通过单击每一行，您可以看到完整的查询，这对于查明和理解问题确实很有帮助，如下一节所示。

修复查询异常值

要修复异常值，我们需要了解查询的性质，表的存储引擎，数据库版本，集群类型以及查询的影响力。在某些情况下，异常查询并没有真正降低整体数据库性能。如本例所示，我们已经看到查询已经整整一个星期脱颖而出，并且它是唯一捕获的查询类型，因此如果可能的话，修复或改进此查询可能是一个好主意。

在本例中，异常查询为：

1个

SELECT i2l.country_code AS country_code, i2l.country_name AS country_name 

FROM ip2location i2l 

WHERE (i2l.ip_to >= INET_ATON('104.144.171.139') 

AND i2l.ip_from <= INET_ATON('104.144.171.139')) 

LIMIT 1 

OFFSET 0;

查询结果为：

1个

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

| country_code | country_name |

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

| US | United States |

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

使用EXPLAIN

该查询是只读范围选择查询，用于确定表ip2location上IP地址的用户地理位置信息（国家/地区代码和国家/地区名称）。使用EXPLAIN语句可以帮助我们了解查询执行计划：

1个

mysql> EXPLAIN SELECT i2l.country_code AS country_code, i2l.country_name AS country_name 

FROM ip2location i2l 

WHERE (i2l.ip_to>=INET_ATON('104.144.171.139') 

AND i2l.ip_from<=INET_ATON('104.144.171.139')) 

LIMIT 1 OFFSET 0;

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

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

| 1 | SIMPLE | i2l | NULL | range | idx_ip_from,idx_ip_to,idx_ip_from_to | idx_ip_from | 5 | NULL | 66043 | 50.00 | Using index condition; Using where |

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

使用具有50％潜在行（已过滤）的索引idx_ip_from对表进行范围扫描来执行查询。

适当的存储引擎

查看ip2location的表结构：

1个

mysql> SHOW CREATE TABLE ip2locationG

*************************** 1. row ***************************

Table: ip2location

Create Table: CREATE TABLE `ip2location` (

`ip_from` int(10) unsigned DEFAULT NULL,

`ip_to` int(10) unsigned DEFAULT NULL,

`country_code` char(2) COLLATE utf8_bin DEFAULT NULL,

`country_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,

KEY `idx_ip_from` (`ip_from`),

KEY `idx_ip_to` (`ip_to`),

KEY `idx_ip_from_to` (`ip_from`,`ip_to`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

该表基于 IP2location数据库，很少更新/写入，通常仅在日历月的第一天（由供应商推荐）。因此，一种选择是将表转换为具有固定行格式的MyISAM（MySQL）或Aria（MariaDB）存储引擎，以获得更好的只读性能。请注意，这仅适用于在MySQL或MariaDB独立或复制上运行的情况。在Galera群集和组复制上，请坚持使用InnoDB存储引擎（除非您知道自己在做什么）。

无论如何，要将表从InnoDB转换为具有固定行格式的MyISAM，只需运行以下命令：

1个	`ALTER` `TABLE` `ip2location ENGINE=MyISAM ROW_FORMAT=FIXED;`

在我们的测量中，通过1000次随机IP地址查找测试，使用MyISAM和固定行格式，查询性能提高了约20％：

平均时间（InnoDB）：21.467823 ms
平均时间（固定了MyISAM）：17.175942毫秒
改善：19.992157565301％

您可以期望该结果在更改表后立即生效。无需在更高层（应用程序/负载平衡器）上进行修改。

调整查询

另一种方法是检查查询计划，并使用更有效的方法来制定更好的查询执行计划。也可以使用子查询来编写相同的查询，如下所示：

1个

SELECT `country_code`, `country_name` FROM 

(SELECT `country_code`, `country_name`, `ip_from`

FROM `ip2location`

WHERE ip_to >= INET_ATON('104.144.171.139') 

LIMIT 1) 

AS temptable 

WHERE ip_from <= INET_ATON('104.144.171.139');

调整后的查询具有以下查询执行计划：

1个

mysql> EXPLAIN SELECT `country_code`,`country_name` FROM 

(SELECT `country_code`, `country_name`, `ip_from`

FROM `ip2location`

WHERE ip_to >= INET_ATON('104.144.171.139') 

LIMIT 1) 

AS temptable 

WHERE ip_from <= INET_ATON('104.144.171.139');

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

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

| 2 | DERIVED | ip2location | NULL | range | idx_ip_to | idx_ip_to | 5 | NULL | 66380 | 100.00 | Using index condition |

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

使用子查询，我们可以通过使用专注于一个索引的派生表来优化查询。查询应仅返回ip_to值大于或等于IP地址值的1条记录。这样可使潜在行（已过滤）达到100％，这是最有效的。然后，检查ip_from小于或等于IP地址值。如果是，那么我们应该找到记录。否则，ip2location表中不存在IP地址。

在我们的评估中，使用子查询的查询性能提高了约99％：

平均时间（InnoDB +范围扫描）：22.87112 ms
平均时间（InnoDB +子查询）：0.14744 ms
改善：99.355344207017％

通过上面的优化，我们可以看到这种查询类型的亚毫秒级查询执行时间，考虑到以前的平均时间为22毫秒，这是一个巨大的改进。但是，我们需要对更高层（应用程序/负载平衡器）进行一些修改，以便从此优化查询中受益。

修补或查询重写

修补您的应用程序以使用调整后的查询或在异常查询到达数据库服务器之前对其进行重写。我们可以通过使用MySQL负载均衡器（例如ProxySQL（查询规则）或MariaDB MaxScale（语句重写过滤器））或使用MySQL Query Rewriter插件来实现此目的。在下面的示例中，我们在数据库集群的前面使用ProxySQL，我们可以简单地创建一条规则以将较慢的查询重写为较快的查询，例如：

保存查询规则并监视ClusterControl中的“查询异常值”页面。激活查询规则后，此修复程序显然将从列表中删除异常查询。

结论

查询离群值是一种主动的查询监视工具，可以帮助我们在性能问题失去控制之前理解并解决性能问题。随着应用程序的增长和要求越来越高，此工具可以帮助您始终保持良好的数据库性能。