mysql - 如何优化这个范围查询

Question

我有一个包含 1500 万条记录的表，其中包含姓名、电子邮件地址和 IP。我需要使用 IP 地址使用国家/地区代码更新同一表中的另一列。我下载了一个小型数据库(ip2location lite - https://lite.ip2location.com/)，其中包含所有 IP 范围和相关国家/地区。 ip2location表具有以下结构；

CREATE TABLE `ip2location_db1` (
  `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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

我正在使用以下函数从 IP 地址检索国家/地区代码；

CREATE DEFINER=`root`@`localhost` FUNCTION `get_country_code`(
    ipAddress varchar(30)
) RETURNS VARCHAR(2)
    DETERMINISTIC
    BEGIN
        DECLARE ipNumber INT UNSIGNED;
        DECLARE countryCode varchar(2);
        SET ipNumber = SUBSTRING_INDEX(ipAddress, '.', 1) * 16777216;
        SET ipNumber = ipNumber + (SUBSTRING_INDEX(SUBSTRING_INDEX(ipAddress, '.', 2 ),'.',-1) * 65536);
        SET ipNumber = ipNumber + (SUBSTRING_INDEX(SUBSTRING_INDEX(ipAddress, '.', -2 ),'.',1) * 256);
        SET ipNumber = ipNumber + SUBSTRING_INDEX(ipAddress, '.', -1 );

        SET countryCode = 
            (SELECT     country_code 
            FROM        ip2location.ip2location_db1
            USE INDEX   (idx_ip_from_to)
            WHERE       ipNumber >= ip2location.ip2location_db1.ip_from AND ipNumber <= ip2location.ip2location_db1.ip_to
            LIMIT       1);

        RETURN countryCode;
    END$$
DELIMITER ;

我运行了一个 EXPLAIN 语句，这是输出；

'1', 'SIMPLE', 'ip2location_db1', NULL, 'range', 'idx_ip_from_to', 'idx_ip_from_to', '5', NULL, '1', '33.33', 'Using index condition'

我的问题是，对 1000 条记录的查询大约需要 15 秒才能执行，这意味着对所有数据库运行相同的查询需要 2 天以上才能完成。有没有办法改进这个查询。

PS - 如果我删除 USE INDEX (idx_ip_from_to)，查询时间会增加两倍。你能解释一下原因吗？

此外，我不是数据库专家，所以请耐心等待:)

Answer 1

这可能非常棘手。我认为问题在于只能使用条件的 ip_from 部分。看看这是否能达到您想要的性能:

    SET countryCode = 
        (SELECT     country_code 
         FROM        ip2location.ip2location_db1 l
         WHERE       ipNumber >= l.ip_from 
         ORDER BY ip_to
         LIMIT       1
        );

我知道我放弃了ip_to。如果这有效，那么您可以分两部分进行全面检查。首先使用类似的查询获取ip_from。然后使用相等查询来获取该行中的其余信息。