为何MySQL未使用前缀索引？IP地址关联查询索引失效排查

这里的核心问题在于索引定义和查询条件的表达式不匹配，数据库优化器没法识别你用SUBSTR(Range,1,3)的操作和你创建的前缀索引是等价的，所以直接跳过了索引。

让我拆解一下你忽略的关键点：

1. 前缀索引的匹配规则 #

你创建的是CREATE INDEX My_range ON ranges ( Range (3))——这是一个前缀索引，它存储的是Range字段前3个字符的直接值。但当你在查询中用SUBSTR(Range,1,3)时，数据库会认为这是一个对字段的函数运算，默认情况下，优化器不会自动关联这个函数结果和前缀索引的存储值，哪怕它们的逻辑完全一致。

简单说：数据库看到SUBSTR(Range,1,3)，只会觉得你在修改字段的值，而不是在匹配它已经建好的前缀索引。

2. JOIN条件的双重函数调用雪上加霜 #

你的JOIN条件是SUBSTR(IPAddress,1,3) = SUBSTR(Range,1,3)，两边都是函数运算。这种情况下，优化器更难判断如何利用索引——它没法把其中一侧转换为常量去匹配另一侧的索引，只能做全表扫描。

解决办法 #

针对这个问题，有几个直接可行的方案：

方案一：让查询条件匹配前缀索引的定义 #

把查询中SUBSTR(rt.Range,1,3)替换成和索引定义一致的写法，比如用LEFT(rt.Range, 3)（大多数数据库中，Range(3)和LEFT(Range,3)是等价的），或者用前缀匹配的LIKE语句：

EXPLAIN SELECT * 
FROM ips 
JOIN ranges rt ON rt.Range LIKE CONCAT(SUBSTR(ips.IPAddress,1,3), '%')
WHERE IPID = 17054;

这样优化器就能识别出可以用你创建的My_range前缀索引了。

方案二：创建函数索引（更直接匹配查询） #

如果你的数据库支持函数索引（比如PostgreSQL、MySQL 8.0+等），直接创建和查询中函数一致的索引：

CREATE INDEX My_range ON ranges ( SUBSTR(Range, 1, 3) );

这样你原来的查询语句就能直接用上这个索引，不需要修改查询逻辑。

方案三：先提取常量再关联 #

既然你的WHERE条件是IPID = 17054，可以先单独获取这个IP的前3个字符，再用这个常量去关联ranges表，这样优化器肯定会用前缀索引：

-- 先获取目标IP的前3位
SET @prefix = (SELECT SUBSTR(IPAddress,1,3) FROM ips WHERE IPID = 17054);

-- 再关联查询
EXPLAIN SELECT * 
FROM ips 
JOIN ranges rt ON LEFT(rt.Range,3) = @prefix
WHERE IPID = 17054;

或者用子查询的方式整合在一起，效果是一样的。

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