如何优化含BETWEEN的MySQL JOIN语句及索引失效问题

现有两张数据库表，结构如下：

ip_info表 #

CREATE TABLE `ip_info` (
  `start_ip` int(10) unsigned NOT NULL,
  `end_ip` int(10) unsigned NOT NULL,
  `country_code` varchar(3) DEFAULT NULL,
  `country_name` varchar(255) DEFAULT NULL,
  `continent_code` varchar(3) DEFAULT NULL,
  `continent_name` varchar(255) DEFAULT NULL,
  `asn` int(10) unsigned DEFAULT NULL,
  `as_name` varchar(255) DEFAULT NULL,
  `as_domain` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`start_ip`,`end_ip`),
  KEY `country_code_idx` (`country_code`),
  KEY `asn_idx` (`asn`)
)

servers表 #

CREATE TABLE `servers` (
  `ipport` varchar(255) NOT NULL,
  `ip` varchar(255) NOT NULL,
  `port` int(11) NOT NULL,
  `ip_as_int` int(10) unsigned NOT NULL,
  `version` text NOT NULL,
  `protocol` int(11) NOT NULL,
  `online_count` int(11) NOT NULL,
  `max_count` int(11) NOT NULL,
  `description` text NOT NULL,
  `favicon` text DEFAULT NULL,
  `last_seen` int(11) NOT NULL,
  `cracked` tinyint(1) DEFAULT NULL,
  `joined_on` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`ip`,`port`),
  KEY `ip_as_int_idx` (`ip_as_int`)
)

执行以下查询（统计美国地区在线人数≥5的服务器数量）时耗时长达18秒：

SELECT count(*) FROM
    (SELECT * FROM servers AS d JOIN ip_info i ON d.ip_as_int BETWEEN i.start_ip AND i.end_ip WHERE i.country_code = "us") 
AS s WHERE (s.online_count > 5) ;

查询执行计划如下：

+------+-------------+-------+------+--------------------------+------------------+---------+-------+---------+------------------------------------------------+
| id   | select_type | table | type | possible_keys            | key              | key_len | ref   | rows    | Extra                                          |
+------+-------------+-------+------+--------------------------+------------------+---------+-------+---------+------------------------------------------------+
|    1 | SIMPLE      | i     | ref  | PRIMARY,country_code_idx | country_code_idx | 15      | const |  761917 | Using where; Using index                       |
|    1 | SIMPLE      | d     | ALL  | ip_as_int_idx            | NULL             | NULL    | NULL  | 1035230 | Range checked for each record (index map: 0x2) |
+------+-------------+-------+------+--------------------------+------------------+---------+-------+---------+------------------------------------------------+

可见ip_as_int_idx索引未被使用，导致MySQL对servers表进行全表扫描，求优化方案。

执行计划中Range checked for each record说明：MySQL无法提前确定d.ip_as_int需要匹配的固定范围，而是要对每个ip_info（美国地区的IP段）逐一检查servers表的ip_as_int是否在该段内，这种情况下无法有效利用ip_as_int_idx索引，只能走全表扫描。

同时原查询先关联两张表再过滤online_count，会导致关联的数据量过大，进一步拖慢速度。

1. 调整查询逻辑，先过滤再关联 #

先筛选出online_count>5的服务器，再关联IP信息表，减少关联的数据量：

SELECT COUNT(*)
FROM servers d
JOIN ip_info i ON d.ip_as_int BETWEEN i.start_ip AND i.end_ip
WHERE i.country_code = 'us'
  AND d.online_count > 5;

更高效的写法是用子查询先提取需要的字段，避免不必要的列参与关联：

SELECT COUNT(*)
FROM (SELECT ip_as_int FROM servers WHERE online_count > 5) d
JOIN ip_info i ON d.ip_as_int BETWEEN i.start_ip AND i.end_ip
WHERE i.country_code = 'us';

2. 优化ip_info表的索引 #

创建包含country_code、start_ip、end_ip的覆盖索引，让MySQL可以直接通过索引完成过滤和范围匹配，无需回表查询其他字段：

CREATE INDEX idx_country_ip_range ON ip_info (country_code, start_ip, end_ip);

该索引会先按country_code分组，同一国家的IP段按start_ip排序，能大幅提升IP范围匹配的效率。

3. 预计算存储国家信息（最优方案，适合高频查询） #

如果该查询非常频繁，可在servers表中新增country_code字段，提前将服务器对应的国家代码写入，后续查询直接过滤即可：

-- 新增字段
ALTER TABLE servers ADD COLUMN country_code VARCHAR(3);

-- 一次性初始化数据
UPDATE servers d
JOIN ip_info i ON d.ip_as_int BETWEEN i.start_ip AND i.end_ip
SET d.country_code = i.country_code;

-- 后续查询直接执行，速度极快
SELECT COUNT(*) FROM servers WHERE country_code = 'us' AND online_count > 5;

注意：如果服务器IP会变动，需要配合定时任务或触发机制更新country_code字段，保证数据一致性。

内容的提问来源于stack exchange，提问作者Damian