使用 INET_ATON 时的 MySQL 性能问题

Question

我有一个MySQL查询

SELECT * FROM table WHERE INET_ATON("10.0.0.1") BETWEEN INET_ATON(s_ip) AND INET_ATON(e_ip);

这里的"10.0.0.1"在用户访问网站时动态出现，s_ip是起始 IP 地址列，它可能将"10.0.0.0"作为起始 IP 地址范围，e_ip是结束 IP 地址。

现在，问题是我有几乎~350K的记录，当执行此查询时，它们只做一件事，那就是为我获取访问者的国家/地区代码。

当执行此查询时，MySQL的CPU消耗峰值为1100%，并将其乘以1000个请求/分钟，我的服务器无法处理它。

我的伺服器运行的是 CentOS 7，具有 100 GB 的 RAM 和 24 个时钟频率为 3.0 GHz 的内核，但性能仍然成为我的噩梦。

我正在考虑将此功能外包给第三方服务，但我只想确保我这边无法解决此问题。

(来自评论(

CREATE TABLE ip` (
ip_ip varbinary(16) NOT NULL, 
ip_last_request_time timestamp(3) NULL DEFAULT NULL, 
ip_min_timeSpan_get smallint(5) unsigned NOT NULL, 
ip_min_timeSpan_post smallint(5) unsigned NOT NULL, 
ip_violationsCount_get smallint(5) unsigned NOT NULL, 
ip_violationsCount_post smallint(5) unsigned NOT NULL, 
ip_maxViolations_get smallint(5) unsigned NOT NULL, 
ip_maxViolations_post smallint(5) unsigned NOT NULL, 
ip_bannedAt timestamp(3) NULL DEFAULT NULL,
ip_banSeconds mediumint(8) unsigned NOT NULL DEFAULT '300', 
ip_isCapatchaResolved tinyint(1) NOT NULL DEFAULT '0', 
ip_isManualBanned tinyint(1) NOT NULL DEFAULT '0', 
ip_city varchar(45) DEFAULT '', 
ip_region varchar(45) DEFAULT '', 
ip_regionCode varchar(5) DEFAULT '', 
ip_regionName varchar(45) DEFAULT '', 
ip_countryCode varchar(3) DEFAULT '', 
ip_countryName varchar(45) DEFAULT '', 
ip_continentCode varchar(3) DEFAULT '', 
ip_continentName varchar(45) DEFAULT '', 
ip_timezone varchar(45) DEFAULT '', 
ip_currencyCode varchar(4) DEFAULT '', 
ip_currencySymbol_UTF8 varchar(5) DEFAULT '', 
PRIMARY KEY (ip_ip), 
KEY countryCode_index (ip_countryCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`
CREATE TABLE country` ( co_id char(2) COLLATE utf8mb4_unicode_ci NOT NULL, 
co_re_id smallint(6) DEFAULT NULL, 
co_flag_id char(4) COLLATE utf8mb4_unicode_ci NOT NULL, 
co_english_name varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL, 
PRIMARY KEY (co_id), 
KEY fk_country_region1_idx (co_re_id), 
CONSTRAINT fk_country_region1 FOREIGN KEY (co_re_id)
REFERENCES region (re_id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Answer 1

目前，您正在对每个查询执行全表扫描。您可以尝试以下几种方法。

• 将INET_ATON(s_ip(存储在表中，以便在查询期间不计算它。e_ip也一样。
• 添加包含这两个新列和国家/地区代码的索引。
• 将查询更改为仅选择国家/地区代码，并使用两个新列。

使用EXPLAIN 确保数据库使用查询的索引。

Answer 2

优化程序不知道您有一组非重叠范围，它可以基于它进行一些优化。 因此，您必须更加努力地优化查询。

此处描述的代码将"立即"执行典型查询，而不是执行表扫描。

说白了，如果不重构数据，就无法优化查询。 我也在对所有提供答案和评论的人说话。

(对模式的批评(

ip非常笨重。 建议将city及其后的所有字段移动到另一个表，以便"规范化"该数据。

在同一表中同时具有..code和..name是"错误的"(规范化表除外(。

几个字段可以(并且应该(是ascii，而不是utf8mb4。 示例：国家/地区代码。

关于另一个话题... 您将如何处理AOL IP地址？ 据我了解，这些是在其客户之间共享的。 也就是说，"违规者"将四处移动，污染所有AOL IP。

10.， 11.， 172.16.， 192.168. 所有这些都来自 NAT 后面，不能与给定的国家/地区或给定的计算机相关联。