含JOIN与WHERE子句的MySQL慢查询优化需求

原始查询与问题 #

当前执行耗时约1分钟的SQL语句：

SELECT COUNT(1) as total,
       FLOOR(UNIX_TIMESTAMP(tr.created_at)/(30 * 60)) AS _time,
       CAST(MIN(tr.created_at) AS CHAR) as created_at,
       AVG(case when tr.reached =0 then null else trd.nd_value end) as nd_value
FROM read tr 
JOIN readdetails as trd ON (tr.id=trd.trailerread_id)
WHERE tr.trailer_id=7 
  AND trd.traileroidtype_id=11
  AND tr.created_at between DATE_ADD(now(), INTERVAL -365 DAY) 
  AND now()
GROUP BY _time
ORDER BY _time; 

涉及表结构：

read表（220111行） #

CREATE TABLE `read` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `trailer_id` bigint(20) NOT NULL,
  `trailerrisk_id` smallint(6) DEFAULT NULL,
  `finished` bit(1) NOT NULL DEFAULT b'0',
  `hasdata` bit(1) NOT NULL DEFAULT b'0',
  `reached` bit(1) NOT NULL DEFAULT b'0',
  `created_at` datetime DEFAULT current_timestamp(),
  `lastlog_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `trailerreads_trailercreateat_idx` (`trailer_id`,`created_at`),
  KEY `ind_trailerreads_finish` (`finished`),
  CONSTRAINT `trailerreads_ibfk_1` FOREIGN KEY (`trailer_id`) REFERENCES `trailers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=227510 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

readdetails表（1767873行） #

CREATE TABLE `readdetails` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `trailerread_id` bigint(20) NOT NULL,
  `traileroidtype_id` int(11) NOT NULL,
  `range_id` int(11) DEFAULT NULL,
  `trailerrisk_id` smallint(6) DEFAULT NULL,
  `nd_value` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `trailerread_id` (`trailerread_id`),
  KEY `traileroidtype_id` (`traileroidtype_id`),
  KEY `trailerrisk_id` (`trailerrisk_id`),
  KEY `range_id` (`range_id`),
  KEY `trailer_value` (`nd_value`),
  KEY `trailerreaddetails_idx_traileroidtype_id` (`traileroidtype_id`),
  CONSTRAINT `trailerreaddetails_ibfk_1` FOREIGN KEY (`trailerread_id`) REFERENCES `trailerreads` (`id`),
  CONSTRAINT `trailerreaddetails_ibfk_2` FOREIGN KEY (`traileroidtype_id`) REFERENCES `traileroidtypes` (`id`),
  CONSTRAINT `trailerreaddetails_ibfk_3` FOREIGN KEY (`trailerrisk_id`) REFERENCES `trailerrisks` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1840745 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

优化方案 #

1. 针对性创建复合索引（核心优化） #

现有索引无法覆盖查询所需的全部字段，导致大量回表操作，这是性能瓶颈的主要原因。

针对read表创建覆盖索引

原索引trailerreads_trailercreateat_idx仅包含trailer_id和created_at，需扩展为包含查询用到的reached和JOIN所需的id，避免回表：

CREATE INDEX idx_read_trailer_created_reached_id ON read (trailer_id, created_at, reached, id);

创建后，查询可直接通过该索引获取过滤、JOIN、计算所需的所有字段，无需访问主键索引的表数据。

针对readdetails表创建复合索引

原索引为单字段索引，需创建先过滤traileroidtype_id、再匹配JOIN的trailerread_id、同时包含nd_value的复合索引：

CREATE INDEX idx_rd_type_readid_value ON readdetails (traileroidtype_id, trailerread_id, nd_value);

该索引可直接过滤出traileroidtype_id=11的行，快速匹配JOIN的trailerread_id，并直接获取nd_value用于计算，完全避免回表。

2. SQL语句改写（减少重复计算与隐式转换） #

提前计算时间范围

避免在WHERE子句中重复调用DATE_ADD函数，提前定义变量缓存时间范围：

SET @start_time = DATE_ADD(NOW(), INTERVAL -365 DAY);
SET @end_time = NOW();

SELECT COUNT(1) as total,
       _time,
       CAST(MIN(created_at) AS CHAR) as created_at,
       AVG(CASE WHEN reached = b'0' THEN NULL ELSE nd_value END) as nd_value
FROM (
    -- 子查询提前计算分组用的_time，避免分组阶段重复计算
    SELECT tr.created_at,
           tr.reached,
           trd.nd_value,
           FLOOR(UNIX_TIMESTAMP(tr.created_at)/(30 * 60)) AS _time
    FROM read tr 
    JOIN readdetails trd ON tr.id = trd.trailerread_id
    WHERE tr.trailer_id = 7 
      AND trd.traileroidtype_id = 11
      AND tr.created_at BETWEEN @start_time AND @end_time
) AS temp
GROUP BY _time
ORDER BY _time;

修正bit类型判断

注意read.reached是bit(1)类型，直接用reached=0会触发隐式转换，影响索引使用，应改为reached = b'0'。

3. 执行计划验证 #

创建索引后，执行EXPLAIN查看执行计划，确认：

• read表的type为range或ref，rows列数值大幅降低
• readdetails表的type为ref，rows列仅展示匹配traileroidtype_id=11的行数
• Extra列出现Using index，表示使用了覆盖索引，无回表操作

4. 可选：数据预处理（若查询频率高） #

如果该查询是高频执行的报表类查询，可考虑：

• 按30分钟粒度预计算统计结果，存储到汇总表中
• 通过定时任务（如MySQL事件或外部脚本）定期更新汇总表
• 查询时直接从汇总表读取数据，可将耗时降至毫秒级

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