MySQL大表内连接取最大检查日期效率低及去重需求求助

看起来你在处理两个超大房产相关表的关联和去重问题——既要保留每个房屋对应最新检查日期的唯一记录，又要解决内连接求最大日期时效率过低的痛点。结合你的场景（Property表2200万行、EPC表1400万行），我给你两个高效的解决方案，再搭配索引优化，应该能完美解决问题：

方案一：窗口函数法（推荐支持窗口函数的数据库） #

这种方法先在EPC表内部给每个房屋的记录按检查日期倒序排序，只保留排名第一的最新记录，再和Property表关联。相比先全表关联再过滤，能大幅减少关联的数据量，提升效率。

WITH latest_epc AS (
    SELECT 
        ADDRESS1,
        POSTCODE,
        TOTAL_FLOOR_AREA,
        INSPECTION_DATE,
        -- 按房屋唯一标识分组，检查日期降序排列，标记每条记录的排名
        ROW_NUMBER() OVER (
            PARTITION BY ADDRESS1, POSTCODE 
            ORDER BY INSPECTION_DATE DESC
        ) AS rn
    FROM epc
)
SELECT 
    p.paon, 
    p.saon, 
    p.street, 
    p.postcode, 
    p.lastSalePrice, 
    DATE(p.lastTransferDate), 
    le.ADDRESS1, 
    le.POSTCODE, 
    le.TOTAL_FLOOR_AREA, 
    le.INSPECTION_DATE,
    GLENGTH(LINESTRINGFROMWKB(...)) -- 保留你原有的空间计算函数
FROM property p
JOIN latest_epc le 
    -- 这里的关联条件要根据实际地址匹配逻辑调整，确保是同一房屋
    ON p.postcode = le.POSTCODE 
    AND CONCAT(p.paon, ' ', p.saon, ' ', p.street) = le.ADDRESS1
WHERE le.rn = 1; -- 只保留每个房屋的最新记录

小提示： #

如果同一房屋存在多条记录的检查日期完全相同（都是最大值），可以把ROW_NUMBER()换成RANK()，这样会保留所有同日期的最新记录；如果只需要一条，ROW_NUMBER()就足够。

方案二：预聚合最大日期再关联（兼容旧版本数据库） #

如果你的数据库不支持窗口函数，或者你更倾向于传统聚合方式，可以先在EPC表预聚合出每个房屋的最新检查日期，再通过这个中间结果关联EPC表获取完整字段，最后关联Property表。

-- 第一步：预计算每个房屋的最新检查日期
WITH epc_max_dates AS (
    SELECT 
        ADDRESS1,
        POSTCODE,
        MAX(INSPECTION_DATE) AS latest_inspection_date
    FROM epc
    GROUP BY ADDRESS1, POSTCODE
)
-- 第二步：关联获取最新日期对应的完整EPC记录，再关联Property表
SELECT 
    p.paon, 
    p.saon, 
    p.street, 
    p.postcode, 
    p.lastSalePrice, 
    DATE(p.lastTransferDate), 
    e.ADDRESS1, 
    e.POSTCODE, 
    e.TOTAL_FLOOR_AREA, 
    e.INSPECTION_DATE,
    GLENGTH(LINESTRINGFROMWKB(...))
FROM property p
JOIN epc_max_dates emd 
    ON p.postcode = emd.POSTCODE 
    AND CONCAT(p.paon, ' ', p.saon, ' ', p.street) = emd.ADDRESS1
JOIN epc e 
    ON emd.ADDRESS1 = e.ADDRESS1 
    AND emd.POSTCODE = e.POSTCODE 
    AND emd.latest_inspection_date = e.INSPECTION_DATE;

关键：大表索引优化 #

不管用哪种方案，索引都是提升大表查询效率的核心，一定要加上：

• 给EPC表创建复合索引：

  CREATE INDEX idx_epc_address_date ON epc(ADDRESS1, POSTCODE, INSPECTION_DATE DESC) INCLUDE (TOTAL_FLOOR_AREA);
  

  这个索引能让窗口函数或聚合查询直接从索引获取数据，不用全表扫描。
• 给Property表创建地址相关的复合索引：

  CREATE INDEX idx_property_address ON property(paon, saon, street, postcode) INCLUDE (lastSalePrice, lastTransferDate);
  

  关联时能快速定位匹配的房屋记录。
• 优化地址匹配：如果用CONCAT拼接地址，建议在Property表新增一个预计算的full_address字段（存储拼接后的地址），并给这个字段建索引，避免关联时实时计算字符串，进一步提升效率。

额外注意点 #

地址匹配时要注意两个表的地址格式差异（比如多余空格、大小写、缩写），如果直接匹配不准确，需要先做地址标准化处理（比如统一转换为小写、去掉多余空格、替换常见缩写），否则会导致关联错误或遗漏数据。

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