已索引字段上LEFT JOIN查询性能低下的原因排查

我需要从每10分钟写入一次数据的devicesData（别名dd）表中提取单日（24小时）的汇总数据，将144条记录聚合为一条。

索引详情 #

devicesData表已创建以下索引：

Table       Non_Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment   Visible Expression
devicesData 0   PRIMARY     1               id          A           165482      NULL    NULL        BTREE           YES NULL
devicesData 1   devesData_1 1               devId       A           21          NULL    NULL        BTREE           YES NULL
devicesData 1   fk_devicesD 1               roomId      A           16          NULL    NULL        BTREE           YES NULL
devicesData 1   createdAt_  1               createdAt   A           156304      NULL    NULL    YES BTREE           YES NULL
devicesData 1   dt_index    1               dt          A           164176      NULL     NULL       BTREE           YES NULL
devicesData 1   idx_roId_dt 1               roomId      A           17          NULL    NULL        BTREE           YES NULL
devicesData 1   idx_roId_dt 2               dt          A           164876      NULL    NULL        BTREE           YES NULL

当前查询语句 #

SELECT
    r.id AS `roomId`, 
    l.id AS `levelId`, 
    b.id AS `buildingId`, 
    s.id AS `siteId`, 
    date(CURDATE() - INTERVAL 1 DAY) AS `date`,
    r.`name` AS `roomName`, 
    l.`name` AS `levelName`, 
    b.`name` AS `buildingName`, 
    s.`name` AS `siteName`,
    r.`size` * r.`eui` * cdd.`value` / 3280 AS `referenceConsumption`,
    SUM(dd.activeEnergy) AS `energy`, 
    SUM(dd.coolingEnergy) AS `coolingEnergy`, 
    SUM(dd.fanTime) AS `fanTime`, 
    SUM(dd.comprTime) AS `comprTime`, 
    SUM(dd.presence) AS `presenceTime`,
    AVG(dd.temp1) AS `temp1`,
    COUNT(dd.id) * 10 AS `conTime`
FROM rooms r   
JOIN levels AS l ON l.id = r.levelId
JOIN buildings AS b ON b.id = l.buildingId
JOIN sites AS s ON s.id = b.siteId
LEFT JOIN Devices.coolingDegreeDays AS cdd
ON cdd.`day` = date(CURDATE() - INTERVAL 1 DAY)
LEFT JOIN devicesData dd
ON dd.roomId = r.id AND date(dd.dt) = date(CURDATE() - INTERVAL 1 DAY)
GROUP BY r.id;

性能问题 #

• 32个房间的小数据集（最多32×144条记录）：响应时间0.5秒
• 183个房间的数据集（共26350条记录）：响应时间长达4分钟，性能过慢

注：devicesData表中存在部分时间戳无数据的情况。

表结构（EXPLAIN结果） #

Field   Type    Null    Key Default Extra  
id      int     NO      PRI NULL    auto_increment  
devId   varchar NO      MUL NULL      
roomId  int     NO      MUL NULL      
dt      datetim NO      MUL CURRENT_TIMESTAMP   DEFAULT_GENERATED  
temp1   float   YES         NULL      
temp2   float   YES         NULL      
hum1    float   YES         NULL      
relay1  tinyint YES         NULL      
relay2  tinyint YES         NULL      
presencefloat   YES         NULL    
fanTime int     YES         NULL    
comprTimint     YES         NULL    
activeEnfloat   YES         NULL    
reactEnefloat   YES         NULL    
airflowAfloat   YES         NULL    
coolingEfloat   YES         NULL    
createdAint uns YES    MUL  NULL    

尝试的修改 #

怀疑LEFT JOIN中date(dd.dt)的函数操作无法利用索引，改为基于索引字段createdAt的范围查询：

LEFT JOIN devicesData dd
ON dd.roomId = r.id
AND dd.createdAt > unix_timestamp(CURDATE() - INTERVAL 1 DAY)
AND dd.createdAt < unix_timestamp(CURDATE()) 

但性能更差：32个房间的查询时间从0.5秒增至2.5秒。

问题 #

请问是索引存在问题，还是有其他原因导致该查询性能不佳？

核心原因分析 #

1. 索引利用不充分

   • 原查询中date(dd.dt)属于函数操作字段，无法直接匹配dt的单值索引；虽然有idx_roId_dt（roomId+dt）联合索引，但MySQL对date(dt)的处理无法高效触发该索引。
   • 改用createdAt范围查询后性能下降，是因为createdAt是单字段索引，查询同时需要匹配roomId和时间范围，单字段索引无法同时过滤两个条件，导致MySQL选择低效的扫描方式。

2. 关联与聚合顺序不合理
   当前查询先关联所有房间层级数据，再左联devicesData后聚合，房间数量增加时，会加载大量未过滤的数据到内存中进行计算，导致性能线性下降。

3. 数据类型与查询条件的适配问题
   createdAt是int unsigned类型，unix_timestamp()返回int，隐式转换会影响索引效率；而dt是datetime类型，直接用范围查询比date()函数更友好。

具体优化方案 #

1. 创建联合覆盖索引 #

创建包含关联条件和所有聚合字段的联合覆盖索引，让MySQL无需回表即可完成过滤和聚合：

CREATE INDEX idx_roomid_dt_covering ON devicesData (roomId, dt, activeEnergy, coolingEnergy, fanTime, comprTime, presence, temp1, id);

这个索引直接覆盖了查询中需要的所有字段，能大幅减少IO操作。

2. 修改查询条件，避免函数操作字段 #

将date(dd.dt) = ...改为dt的范围查询，直接利用idx_roId_dt或新创建的覆盖索引：

LEFT JOIN devicesData dd
ON dd.roomId = r.id 
AND dd.dt >= CURDATE() - INTERVAL 1 DAY 
AND dd.dt < CURDATE()

3. 调整查询逻辑：先聚合再关联 #

先对devicesData按日期和房间聚合，得到汇总结果后再关联房间层级信息，减少关联数据量：

WITH daily_dd AS (
    SELECT
        roomId,
        SUM(activeEnergy) AS energy,
        SUM(coolingEnergy) AS coolingEnergy,
        SUM(fanTime) AS fanTime,
        SUM(comprTime) AS comprTime,
        SUM(presence) AS presenceTime,
        AVG(temp1) AS temp1,
        COUNT(id) * 10 AS conTime
    FROM devicesData
    WHERE dt >= CURDATE() - INTERVAL 1 DAY 
      AND dt < CURDATE()
    GROUP BY roomId
)
SELECT
    r.id AS roomId, 
    l.id AS levelId, 
    b.id AS buildingId, 
    s.id AS siteId, 
    date(CURDATE() - INTERVAL 1 DAY) AS date,
    r.name AS roomName, 
    l.name AS levelName, 
    b.name AS buildingName, 
    s.name AS siteName,
    r.size * r.eui * cdd.value / 3280 AS referenceConsumption,
    COALESCE(dd.energy, 0) AS energy, 
    COALESCE(dd.coolingEnergy, 0) AS coolingEnergy, 
    COALESCE(dd.fanTime, 0) AS fanTime, 
    COALESCE(dd.comprTime, 0) AS comprTime, 
    COALESCE(dd.presenceTime, 0) AS presenceTime,
    COALESCE(dd.temp1, 0) AS temp1,
    COALESCE(dd.conTime, 0) AS conTime
FROM rooms r   
JOIN levels l ON l.id = r.levelId
JOIN buildings b ON b.id = l.buildingId
JOIN sites s ON s.id = b.siteId
LEFT JOIN Devices.coolingDegreeDays cdd
    ON cdd.day = date(CURDATE() - INTERVAL 1 DAY)
LEFT JOIN daily_dd dd
    ON dd.roomId = r.id;

用COALESCE处理无数据房间的聚合值，避免出现NULL。

4. 优化coolingDegreeDays表索引 #

确保coolingDegreeDays表的day字段有索引，避免全表扫描：

CREATE INDEX idx_cdd_day ON Devices.coolingDegreeDays (day);

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