如何优化此范围查询

Question

我有一个包含1500万条记录的表格，其中包含姓名、电子邮件地址和I地址。我需要使用IP地址用国家代码更新同一表中的另一列。我下载了一个包含所有ip范围和相关国家的小型数据库(ip2location lite - https://lite.ip2location.com/)。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索引(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。然后使用相等查询来获取行中的其余信息。

Answer 2

USE INDEX提供帮助的原因是因为MySQL不打算使用该索引。它的优化器选择了一个不同的，但它猜错了。有时会发生这种情况。

此外，我不确定这是否会对性能产生很大影响，但您应该只使用INET_ATON将IP地址字符串更改为整数。您不需要SUBSTRING_INDEX业务，而且它可能会更慢。

我在这里要做的是测量from和to之间的最大距离：

SELECT MAX(ip_from - ip_to) AS distance
FROM ip2location_db1;

假设这不是一个愚蠢的数字，那么您将能够正确地使用ip_from索引。检查将变为：

WHERE ipNumber BETWEEN ip_from AND ip_from + distance
    AND ipNumber <= ip_to

这里的目标是使用于查找一组狭窄行的所有信息都来自一列的值的有限范围: ip_from。那么ip_to只是一种准确性检查。

之所以要这样做，是因为在找到相应的ip_from值之前，不能使用ip_to值(索引的第二部分)。因此，它仍然必须扫描大多数索引记录，以查找没有上限的ip_from的低值。

否则，您可以考虑测量IP地址在您的1500万条记录中的唯一性。例如，如果只有500万个唯一的IP，最好提取一个唯一的列表，将它们映射到国家代码，然后使用该映射(在运行时或更新原始表)。视情况而定。

如果值是唯一的，但可能在本地化集群中，您可以尝试从ip2location_db1中删除不相关的行，甚至可以尝试水平分区来改进范围检查。我不确定这会带来什么好处，但是如果您可以在原始表上使用一些索引来仅查询特定的分区，那么您也许能够获得一些性能。