关联查询两张表触发max execution time错误的原因排查求助

问题根源 #

从EXPLAIN结果能看出核心问题：

• 两张表都在做全表扫描（type: ALL），没有利用索引加速连接和排序
• 原查询先对两张表做全量连接，生成海量中间数据后再排序取前100条，触发了Using temporary / filesort（临时表+文件排序），加上join buffer（内存缓冲连接），导致查询效率极低，最终超时。

1. 创建必要索引 #

给连接和排序的核心字段DatabaseID创建覆盖索引，避免全表扫描和回表查询：

Game表索引

-- MySQL 8.0+ 支持INCLUDE语法，仅索引排序字段，包含查询所需字段
CREATE INDEX idx_game_db_id ON Game (DatabaseID) INCLUDE (Name);

-- MySQL 8.0以下用联合索引替代
CREATE INDEX idx_game_db_id ON Game (DatabaseID, Name);

GameAlternateName表索引

CREATE INDEX idx_gamealt_db_id ON GameAlternateName (DatabaseID) INCLUDE (AlternateName);

-- MySQL 8.0以下版本
CREATE INDEX idx_gamealt_db_id ON GameAlternateName (DatabaseID, AlternateName);

GameImage表索引（后续关联时使用）

CREATE INDEX idx_gameimg_db_id ON GameImage (DatabaseID) INCLUDE (FileName, Type);

-- MySQL 8.0以下版本
CREATE INDEX idx_gameimg_db_id ON GameImage (DatabaseID, FileName, Type);

2. 优化查询语句 #

先获取排序后的前100条Game数据，再关联其他表，避免先全量连接再筛选：

仅关联Game和GameAlternateName

SELECT 
    game.Name, 
    game.DatabaseID, 
    gameAlt.AlternateName, 
    gameAlt.DatabaseID
FROM (
    -- 先利用索引快速获取前100个Game
    SELECT Name, DatabaseID
    FROM Game
    ORDER BY DatabaseID
    LIMIT 100
) AS game
INNER JOIN GameAlternateName AS gameAlt 
    ON game.DatabaseID = gameAlt.DatabaseID
ORDER BY game.DatabaseID;

关联三张表的优化写法

如果需要同时查询GameImage，可按同样逻辑处理。若单个Game对应多条AlternateName/Image，可选择保留所有行，或用GROUP_CONCAT合并信息：

-- 保留所有关联行
SELECT 
    game.Name, 
    game.DatabaseID, 
    gameAlt.AlternateName, 
    gameImg.FileName, 
    gameImg.Type
FROM (
    SELECT Name, DatabaseID
    FROM Game
    ORDER BY DatabaseID
    LIMIT 100
) AS game
LEFT JOIN GameAlternateName AS gameAlt 
    ON game.DatabaseID = gameAlt.DatabaseID
LEFT JOIN GameImage AS gameImg 
    ON game.DatabaseID = gameImg.DatabaseID
ORDER BY game.DatabaseID;

-- 合并同一Game的AlternateName和Image
SELECT 
    game.Name, 
    game.DatabaseID, 
    GROUP_CONCAT(DISTINCT gameAlt.AlternateName SEPARATOR ', ') AS AlternateNames,
    GROUP_CONCAT(DISTINCT CONCAT(gameImg.FileName, '(', gameImg.Type, ')') SEPARATOR ', ') AS Images
FROM (
    SELECT Name, DatabaseID
    FROM Game
    ORDER BY DatabaseID
    LIMIT 100
) AS game
LEFT JOIN GameAlternateName AS gameAlt 
    ON game.DatabaseID = gameAlt.DatabaseID
LEFT JOIN GameImage AS gameImg 
    ON game.DatabaseID = gameImg.DatabaseID
GROUP BY game.Name, game.DatabaseID
ORDER BY game.DatabaseID;

优化效果说明 #

• 索引让数据库能快速定位目标数据，避免全表扫描
• 先取前100个Game再关联，大幅减少中间结果集大小，消除低效的临时表排序，查询速度会显著提升，不会再触发超时。

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