使用 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 内存和 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 确保数据库使用索引进行查询。