MySQL子查询为何不创建索引？如何添加索引及优化查询

问题背景 #

我有一张名为admin_dash_work_record的InnoDB表，执行嵌套SQL查询时，单独运行子查询速度较快，但添加外层嵌套后查询速度明显变慢。想了解以下问题：

1. MySQL为何不为子查询创建索引？
2. 如何为子查询添加索引？
3. 其他可行的查询优化方法？

表结构 #

CREATE TABLE `admin_dash_work_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gameid` varchar(64) NOT NULL DEFAULT '',
  `work_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `watch_num` int(11) NOT NULL,
  `like_num` int(11) NOT NULL,
  `new_fans_num` int(11) NOT NULL DEFAULT '0',
  `update_time` int(11) NOT NULL,
  `areaid` varchar(64) NOT NULL DEFAULT '',
  `average_viewer_count` int(11) NOT NULL DEFAULT '0',
  `peak_viewer_count` int(11) NOT NULL DEFAULT '0',
  `video_duration_sec` int(11) NOT NULL DEFAULT '0',
  `share_num` int(11) NOT NULL DEFAULT '0',
  `comment_num` int(11) NOT NULL DEFAULT '0',
  `expo_num` int(11) NOT NULL DEFAULT '-1',
  `watch_seconds` int(11) NOT NULL DEFAULT '0',
  `submitted_time` bigint(20) NOT NULL DEFAULT '0',
  `channel_type` int(11) NOT NULL DEFAULT '0',
  `init_video_play_num` int(11) NOT NULL DEFAULT '0',
  `init_video_like_num` int(11) NOT NULL DEFAULT '0',
  `init_video_share_num` int(11) NOT NULL DEFAULT '0',
  `init_video_comment_num` int(11) NOT NULL DEFAULT '0',
  `stream_start_time` bigint(20) NOT NULL DEFAULT '0',
  `released_time` bigint(20) NOT NULL DEFAULT '0',
  `audit_status` int(11) NOT NULL DEFAULT '0',
  `streamer_task_id` int(11) NOT NULL DEFAULT '0',
  `uid` varchar(128) NOT NULL DEFAULT '',
  `video_url` varchar(255) NOT NULL DEFAULT '',
  `user_country` varchar(255) NOT NULL DEFAULT '',
  `user_region` varchar(255) NOT NULL DEFAULT '',
  `admin_task_id` int(11) NOT NULL DEFAULT '0',
  `applied_time` bigint(20) NOT NULL DEFAULT '0',
  `joined_time` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `work_id-date` (`work_id`,`date`) USING BTREE,
  KEY `idx_submitted_time` (`submitted_time`),
  KEY `idx_channel_type` (`channel_type`),
  KEY `idx_audit_status` (`audit_status`),
  KEY `idx_streamer_task_id` (`streamer_task_id`),
  KEY `idx_uid` (`uid`),
  KEY `idx_user_country` (`user_country`),
  KEY `idx_user_region` (`user_region`),
  KEY `idx_admin_task_id` (`admin_task_id`),
  KEY `idx_applied_time` (`applied_time`),
  KEY `idx_joined_time` (`joined_time`),
  KEY `idx_game_area` (`gameid`,`areaid`),
  KEY `idx_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=2901623 DEFAULT CHARSET=utf8mb4;

索引统计信息 #

执行的SQL语句 #

SELECT
  dash_work.admin_task_id,
  COUNT(distinct dash_work.streamer_task_id) AS join_num,
  SUM(dash_work.watch_num) AS watch_num,
  SUM(dash_work.like_num) AS like_num,
  ...
  dash_work.channel_type
FROM(
    SELECT
      work_id,
      ANY_VALUE(admin_task_id) AS admin_task_id,
      ANY_VALUE(streamer_task_id) AS streamer_task_id,
      ANY_VALUE(channel_type) AS channel_type,
      ANY_VALUE(video_url) AS video_url,
      SUM(watch_num) + ANY_VALUE(init_video_play_num) AS watch_num,
      SUM(like_num) + ANY_VALUE(init_video_like_num) AS like_num,
      SUM(share_num) + ANY_VALUE(init_video_share_num) AS share_num,
      SUM(comment_num) + ANY_VALUE(init_video_comment_num) AS comment_num,
      SUM(
        CASE
          WHEN date >= '2021-12-14 00:00:00' THEN watch_num
          ELSE 0
        END
      ) AS incr_watch_num,
      SUM(
        CASE
          WHEN date >= '2021-12-14 00:00:00' THEN like_num
          ELSE 0
        END
      ) AS incr_like_num,
      SUM(
        CASE
          WHEN date >= '2021-12-14 00:00:00' THEN share_num
          ELSE 0
        END
      ) AS incr_share_num,
      SUM(
        CASE
          WHEN date >= '2021-12-14 00:00:00' THEN comment_num
          ELSE 0
        END
      ) AS incr_comment_num,
      SUM(new_fans_num) as new_fans_num,
      SUM(average_viewer_count * video_duration_sec) AS average_viewer_count_sum,
      MAX(peak_viewer_count) AS peak_viewer_count,
      MAX(average_viewer_count) AS average_viewer_count,
      (
        case
          when ANY_VALUE(channel_type) in (1, 2, 3, 5, 6) then SUM(video_duration_sec)
          else 0
        end
      ) as video_duration_sec,
      (
        case
          when ANY_VALUE(channel_type) in (4, 7, 9, 8, 10) then SUM(video_duration_sec)
          else 0
        end
      ) as live_duration_sec,
      CAST(ANY_VALUE(stream_start_time) AS CHAR) AS stream_start_time,
      CAST(ANY_VALUE(released_time) AS CHAR) AS released_time
    FROM
      admin_dash_work_record force INDEX (
        `work_id-date`,
        `idx_submitted_time`,
        `idx_admin_task_id`,
        `idx_channel_type`,
        `idx_user_region`,
        `idx_game_area`
      )
    WHERE
      date <= '2023-12-19 23:59:59' AND (
        gameid = '7'
        AND areaid = 'asia'
      )
      AND (
        submitted_time BETWEEN UNIX_TIMESTAMP('2021-12-14 00:00:00')
        AND UNIX_TIMESTAMP('2023-12-19 23:59:59')
        AND audit_status = 1
      )
      AND channel_type in (1, 5, 6, 8, 9)
      AND user_region in ('Africa')
    GROUP BY
      `work_id`
    ORDER BY admin_task_id, channel_type asc
  ) AS dash_work
GROUP BY
  dash_work.admin_task_id,
  dash_work.channel_type

问题解答 #

1. MySQL为何不为子查询创建索引？ #

子查询的结果是临时表，MySQL默认不会自动为临时表创建索引，核心原因：

• 索引创建会带来额外CPU、IO开销，优化器会评估收益比：如果子查询返回数据量小，建索引的收益不足以抵消开销，会选择跳过。
• 临时表是会话级别的短期对象，MySQL不会为这类生命周期极短的表自动维护索引。
• 外层GROUP BY等操作如果需要排序，MySQL可能直接使用文件排序代替索引，尤其是临时表数据量超过内存阈值时，会切换到磁盘临时表，进一步拉低性能。

2. 如何为子查询添加索引？ #

通过显式创建带索引的临时表是最可靠的方案：

-- 1. 创建临时表存储子查询结果（移除无用的ORDER BY）
CREATE TEMPORARY TABLE temp_work_record
ENGINE=InnoDB
AS
(
    SELECT
      work_id,
      ANY_VALUE(admin_task_id) AS admin_task_id,
      ANY_VALUE(streamer_task_id) AS streamer_task_id,
      ANY_VALUE(channel_type) AS channel_type,
      SUM(watch_num) + ANY_VALUE(init_video_play_num) AS watch_num,
      SUM(like_num) + ANY_VALUE(init_video_like_num) AS like_num,
      SUM(share_num) + ANY_VALUE(init_video_share_num) AS share_num,
      SUM(comment_num) + ANY_VALUE(init_video_comment_num) AS comment_num,
      SUM(CASE WHEN date >= '2021-12-14' THEN watch_num ELSE 0 END) AS incr_watch_num,
      SUM(CASE WHEN date >= '2021-12-14' THEN like_num ELSE 0 END) AS incr_like_num,
      SUM(CASE WHEN date >= '2021-12-14' THEN share_num ELSE 0 END) AS incr_share_num,
      SUM(CASE WHEN date >= '2021-12-14' THEN comment_num ELSE 0 END) AS incr_comment_num,
      SUM(new_fans_num) as new_fans_num,
      SUM(average_viewer_count * video_duration_sec) AS average_viewer_count_sum,
      MAX(peak_viewer_count) AS peak_viewer_count,
      MAX(average_viewer_count) AS average_viewer_count,
      CASE WHEN ANY_VALUE(channel_type) IN (1,2,3,5,6) THEN SUM(video_duration_sec) ELSE 0 END AS video_duration_sec,
      CASE WHEN ANY_VALUE(channel_type) IN (4,7,9,8,10) THEN SUM(video_duration_sec) ELSE 0 END AS live_duration_sec,
      CAST(ANY_VALUE(stream_start_time) AS CHAR) AS stream_start_time,
      CAST(ANY_VALUE(released_time) AS CHAR) AS released_time
    FROM
      admin_dash_work_record
    WHERE
      date <= '2023-12-19' 
      AND gameid = '7' AND areaid = 'asia'
      AND submitted_time BETWEEN UNIX_TIMESTAMP('2021-12-14') AND UNIX_TIMESTAMP('2023-12-19 23:59:59')
      AND audit_status = 1
      AND channel_type IN (1,5,6,8,9)
      AND user_region = 'Africa'
    GROUP BY work_id
);

-- 2. 添加外层查询需要的索引
ALTER TABLE temp_work_record ADD INDEX idx_admin_channel (admin_task_id, channel_type);
ALTER TABLE temp_work_record ADD INDEX idx_streamer_task (streamer_task_id);

-- 3. 执行外层查询
SELECT
  admin_task_id,
  COUNT(DISTINCT streamer_task_id) AS join_num,
  SUM(watch_num) AS watch_num,
  SUM(like_num) AS like_num,
  ...
  channel_type
FROM temp_work_record
GROUP BY admin_task_id, channel_type;

-- 会话结束后临时表自动销毁，也可手动删除
DROP TEMPORARY TABLE IF EXISTS temp_work_record;

3. 其他可行的查询优化方法 #

（1）创建覆盖索引，减少回表

针对当前查询的过滤、分组、聚合需求，创建包含所有必要字段的覆盖索引，彻底避免回表操作：

CREATE INDEX idx_query_optimized ON admin_dash_work_record (
    gameid, areaid, audit_status, channel_type, user_region, submitted_time, date,
    work_id, admin_task_id, streamer_task_id, watch_num, like_num, share_num, comment_num,
    new_fans_num, average_viewer_count, video_duration_sec, peak_viewer_count,
    init_video_play_num, init_video_like_num, init_video_share_num, init_video_comment_num
);

（2）移除FORCE INDEX强制索引

强制索引会限制优化器的选择逻辑，可能导致它无法匹配更高效的索引组合。去掉FORCE INDEX，让MySQL根据统计信息自动选最优索引。

（3）合并嵌套查询，减少聚合次数

子查询按work_id聚合，外层按admin_task_id, channel_type聚合，可尝试直接在主表完成两层聚合，避免临时表开销：

SELECT
  admin_task_id,
  channel_type,
  COUNT(DISTINCT streamer_task_id) AS join_num,
  SUM(SUM_watch_num + init_video_play_num) AS watch_num,
  SUM(SUM_like_num + init_video_like_num) AS like_num
FROM (
    SELECT
      admin_task_id,
      channel_type,
      streamer_task_id,
      SUM(watch_num) AS SUM_watch_num,
      SUM(like_num) AS SUM_like_num,
      ANY_VALUE(init_video_play_num) AS init_video_play_num,
      ANY_VALUE(init_video_like_num) AS init_video_like_num
    FROM admin_dash_work_record
    WHERE [原WHERE条件]
    GROUP BY work_id, admin_task_id, channel_type, streamer_task_id
) t
GROUP BY admin_task_id, channel_type;

注：需确认admin_task_id, channel_type与work_id是一一对应关系。

（4）优化临时表配置

调整MySQL参数，让临时表尽量在内存中处理，避免磁盘IO开销：

tmp_table_size = 256M
max_heap_table_size = 256M

（5）简化聚合逻辑

如果同一个work_id下的channel_type等字段是唯一的，可将ANY_VALUE替换为MIN或MAX，优化器对这类函数的处理效率更高。

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