MySQL双LEFT JOIN仅一个走索引，ip范围关联强制索引失效如何解决

• 你使用的iprange联合索引是(ipStart, ipEnd)，关联条件属于双范围匹配，MySQL联合索引只能用到第一列的范围过滤，第二列ipEnd的过滤无法利用索引，优化器评估后认为遍历索引+回表的成本高于全表扫描，就算强制指定索引，执行效率也极低。
• 原查询先关联所有表再取LIMIT 500，需要对proxies扫描的2547行都做关联操作，关联次数过多进一步放大了ipdb查询的性能损耗。

方案1：先缩小驱动表范围再关联（成本最低，效果最明显） #

因为最终只需要取500条结果，优先把proxies符合条件的500条先查出来作为派生表，再关联另外两张表，将关联次数从2547次降至500次，优化器会优先选择走ipdb的索引：

SELECT tmp.*, qs.score, db.*
FROM (
    -- 先拿到符合条件的500条代理记录，走expiration_date索引
    SELECT p.*, INET_NTOA(p.ip) AS ipStr
    FROM proxies p
    WHERE p.expiration_date < '2021-09-18'
    ORDER BY p.expiration_date
    LIMIT 500
) tmp
LEFT JOIN ipqs qs ON qs.ip = tmp.ip
LEFT JOIN ipdb db ON db.ipStart <= tmp.ip AND db.ipEnd >= tmp.ip

如果还是不走索引，可以给ipdb的关联加上FORCE INDEX：

LEFT JOIN ipdb db FORCE INDEX (iprange) ON db.ipStart <= tmp.ip AND db.ipEnd >= tmp.ip

方案2：优化IP段匹配逻辑（适用于IP段无重叠的场景） #

绝大多数商用IP库的IP段都是非重叠的，你可以把双范围匹配改成单范围匹配+单值判断，完全用上ipStart的索引能力：

LEFT JOIN ipdb db ON db.id = (
    SELECT id FROM ipdb 
    WHERE ipStart <= tmp.ip 
    ORDER BY ipStart DESC 
    LIMIT 1
) AND db.ipEnd >= tmp.ip

这个写法每次查询ipdb只需要对ipStart做一次范围查找，拿到最接近的IP段再判断结束IP是否符合，查询效率比双范围匹配高3~10倍。

方案3：改造ipdb为覆盖索引 #

如果业务必须保留原关联逻辑，可以把iprange联合索引改成覆盖索引，把你查询用到的所有ipdb字段都加到索引中，避免回表开销，优化器会更倾向于走索引：

-- 示例：假设你需要查询ipdb的country、province、city三个字段，索引改成如下结构
ALTER TABLE ipdb ADD INDEX iprange_cover (ipStart, ipEnd, country, province, city);

方案4：批量查询+内存匹配（适合PHP拆分查询的场景） #

不要在循环里逐行查询ipdb，先把第一步查出来的500个IP收集为列表，批量查询ipdb的所有候选IP段，再在PHP内存中做IP归属匹配，只需要1次ipdb查询即可完成所有匹配：

// 第一步查符合条件的代理列表
$proxies = $pdo->query("SELECT p.*, INET_NTOA(p.ip) AS ipStr FROM proxies p WHERE p.expiration_date < '2021-09-18' ORDER BY p.expiration_date LIMIT 500")->fetchAll(PDO::FETCH_ASSOC);
// 收集所有IP的数值
$ips = array_column($proxies, 'ip');
$minIp = min($ips);
$maxIp = max($ips);
// 批量查询覆盖该IP范围的所有IP段
$ipRanges = $pdo->query("SELECT * FROM ipdb WHERE ipStart <= {$maxIp} AND ipEnd >= {$minIp}")->fetchAll(PDO::FETCH_ASSOC);
// 内存中匹配每个IP对应的IP段
foreach ($proxies as &$proxy) {
    foreach ($ipRanges as $range) {
        if ($proxy['ip'] >= $range['ipStart'] && $proxy['ip'] <= $range['ipEnd']) {
            $proxy = array_merge($proxy, $range);
            break;
        }
    }
}

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