MySQL能否用单条语句查询指定IP所属用户对应的type为0的服务域名

你现有的写法已经可以正常实现需求，以下是两种更优的实现方式：

方案1：自连接实现（兼容性最好，性能最优） #

-- 注意核对实际表字段，你给出的表结构用户字段为name，若实际为owner可自行替换
SELECT DISTINCT s1.name, s1.domain
FROM server s1
INNER JOIN server s2 
  ON s1.name = s2.name
WHERE s2.ip = ? 
  AND s1.type = 0;

优势 #

• 无嵌套子查询，语法简洁易维护
• 绝大多数关系型数据库的查询优化器对自连接的优化效果优于IN子查询，执行效率更高
• 新增的DISTINCT可避免同一用户相同域名的重复记录，你原来的写法如果存在重复数据会返回冗余结果

方案2：CTE实现（适合支持公共表表达式的数据库，如MySQL 8.0+、PostgreSQL、SQL Server等） #

WITH target_users AS (
    SELECT DISTINCT name 
    FROM server 
    WHERE ip = ?
)
SELECT s.name, s.domain
FROM server s
INNER JOIN target_users tu 
  ON s.name = tu.name
WHERE s.type = 0;

优势 #

逻辑分层更清晰，先查询目标IP对应的用户，再查询这些用户的type为0的域名，可读性更强。

性能优化建议 #

如果要进一步提升查询速度，可以新增以下索引：

• 针对IP查询的索引：CREATE INDEX idx_server_ip ON server(ip);
• 针对用户和类型查询的联合索引：CREATE INDEX idx_server_name_type ON server(name, type);

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