求高效MySQL查询：找出与指定用户共用IP且仅用该IP一次的用户ID

这个需求我之前处理过，核心是要同时满足两个条件：一是用户和目标UserID（这里是200）共用至少一个登录IP，二是这些用户只使用过那个共用的IP（也就是他们的所有登录记录都只有这一个IP）。下面给你两种高效的查询写法，再说说优化要点：

方法一：自关联查询 #

这种写法通过表自关联直接匹配共用IP的用户，再过滤出仅用单个IP的用户：

SELECT t2.UserID
FROM login_table t1
JOIN login_table t2 ON t1.IP = t2.IP
WHERE t1.UserID = 200
  AND t2.UserID != 200
GROUP BY t2.UserID
HAVING COUNT(DISTINCT t2.IP) = 1;

逻辑解释： #

• 用t1代表目标用户（200）的登录记录，t2代表其他用户的登录记录，通过IP关联找到所有和200共用IP的用户
• 排除目标用户自己（t2.UserID != 200）
• 分组后用HAVING COUNT(DISTINCT t2.IP) = 1确保这些用户只使用过一个IP，也就是和200共用的那个

方法二：子查询缩小范围（更高效） #

先获取目标用户的所有IP，再基于这些IP筛选符合条件的用户，这种方式查询范围更小，性能更优：

SELECT UserID
FROM login_table
WHERE IP IN (SELECT IP FROM login_table WHERE UserID = 200)
  AND UserID != 200
GROUP BY UserID
HAVING COUNT(DISTINCT IP) = 1;

逻辑解释： #

• 子查询(SELECT IP FROM login_table WHERE UserID = 200)先拿到200使用过的所有IP，缩小后续查询的范围
• 过滤出这些IP下的其他用户，再分组检查他们的登录IP总数是否为1，确保他们只使用了和200共用的那个IP

性能优化建议 #

要让查询更高效，建议给表添加合适的复合索引：

• 如果常用UserID查询IP，创建(UserID, IP)复合索引，子查询获取IP的速度会大幅提升
• 如果常用IP关联用户，创建(IP, UserID)复合索引，自关联查询的匹配速度会更快

内容的提问来源于stack exchange，提问作者P.Henderson