MySQL查询优化求助：快速获取各社区最新入住率记录

首先，你的原查询慢的核心原因是相关子查询的逐行执行——WHERE子句里的那个子查询，会对NightlyReports中每一条符合region='GA'的记录都跑一遍，相当于重复执行了几百甚至几千次查询；再加上如果没有合适的索引，每次子查询都是全表扫描，3-4秒的耗时就很正常了。

下面是两个针对性的优化方案，再配合索引调整，性能会有质的提升：

第一步：先建关键索引（必做） #

不管用哪种查询写法，索引都是基础。给NightlyReports表创建这个复合索引：

CREATE INDEX idx_nightly_region_comm_ts ON NightlyReports (region, communityID, TIMESTAMP DESC);

如果想进一步优化，避免回表查询（也就是不用从索引再跳回原表取数据），可以把查询需要的字段也加进索引，做成覆盖索引：

CREATE INDEX idx_nightly_covering ON NightlyReports (region, communityID, TIMESTAMP DESC, reportID, percentOccupied);

另外，确认Communities表的communityID是主键（默认有索引），如果不是，也给它建个索引：

CREATE INDEX idx_communities_id ON Communities (communityID);

第二步：改写查询语句（二选一） #

方案1：用窗口函数（MySQL 8.0+推荐） #

窗口函数是最直观高效的写法，只需要扫描一次表就能完成分组取最新记录的逻辑：

SELECT 
    reportID, communityID, region, percentOccupied, TIMESTAMP, fullName
FROM (
    SELECT 
        t1.reportID, t1.communityID, t1.region, t1.percentOccupied, t1.TIMESTAMP,
        Communities.fullName,
        -- 按社区分组，每组内按时间倒序排，标记最新的那条为1
        ROW_NUMBER() OVER (PARTITION BY t1.communityID ORDER BY t1.TIMESTAMP DESC) AS rn
    FROM NightlyReports t1
    INNER JOIN Communities ON t1.communityID = Communities.communityID
    WHERE t1.region = 'GA'
) AS sub_query
WHERE rn = 1 -- 只取每组的第一条（最新记录）
ORDER BY percentOccupied DESC;

方案2：用JOIN子查询（兼容MySQL 5.x） #

如果你的MySQL版本低于8.0，没法用窗口函数，就用这种方式——先一次性找出每个社区的最新时间，再关联原表获取完整数据：

SELECT 
    t1.reportID, t1.communityID, t1.region, t1.percentOccupied, t1.TIMESTAMP, Communities.fullName
FROM NightlyReports t1
INNER JOIN Communities ON t1.communityID = Communities.communityID
-- 先查GA地区每个社区的最新时间，只执行一次
INNER JOIN (
    SELECT communityID, MAX(TIMESTAMP) AS latest_ts
    FROM NightlyReports
    WHERE region = 'GA'
    GROUP BY communityID
) AS latest_data 
    ON t1.communityID = latest_data.communityID 
    AND t1.TIMESTAMP = latest_data.latest_ts
WHERE t1.region = 'GA'
ORDER BY percentOccupied DESC;

为什么这些方案更快？ #

• 索引让数据库不用全表扫描，直接定位到需要的数据；
• 窗口函数和JOIN子查询都是一次性完成分组取最新记录的逻辑，不会像原查询那样重复执行子查询，大幅减少了数据库的计算量。

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