MySQL查询：找出未出现在DNS请求中的陈旧DNS条目

首先，你的LEFT JOIN查询能不能实现需求，核心取决于是否处理了两个表中record字段的格式差异，以及查询的性能优化：

核心问题：字段格式不匹配 #

dns_scan表的record是FQDN格式（比如server1.example.com），而dns_entries表的record是短名称（比如server1）。如果你的原查询直接用这两个字段做JOIN关联，数据库根本无法匹配到正确的条目，要么返回错误结果，要么因为全表扫描导致性能极差，跑几小时都出不来。

正确的实现逻辑 #

要实现需求，必须先将两个表的记录格式统一，再做关联：

1. 从dns_scan的FQDN中提取短名称（取第一个.之前的部分）；
2. 用提取后的短名称与dns_entries的record做关联；
3. 筛选出关联不到的dns_entries记录，就是陈旧条目。

性能优化方案 #

直接在JOIN条件中使用字符串函数（比如SUBSTRING_INDEX）会导致数据库无法利用索引，触发全表笛卡尔积，数据量大时极慢。建议先预处理dns_scan的数据：

步骤1：生成预处理临时表（以MySQL为例） #

-- 提取dns_scan中的唯一短名称，生成临时表并加索引
CREATE TEMPORARY TABLE dns_scan_short AS
SELECT DISTINCT SUBSTRING_INDEX(record, '.', 1) AS short_name
FROM dns_scan;

-- 给临时表加索引，加速关联
CREATE INDEX idx_short_name ON dns_scan_short(short_name);

步骤2：关联查询陈旧条目 #

SELECT de.record AS stale_entry
FROM dns_entries de
LEFT JOIN dns_scan_short dss 
  ON de.record = dss.short_name
WHERE dss.short_name IS NULL;

如果使用PostgreSQL，提取短名称的函数改为SPLIT_PART(record, '.', 1)即可。

原查询的问题总结 #

• 若未处理格式匹配：完全无法得到正确结果（比如server5、server6根本不会被识别为未匹配）；
• 若处理了格式但未优化性能：会因为无索引的全表扫描导致查询超时或极慢。

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