如何优化MySQL JOIN查询？解决专辑歌曲统计查询速度不稳定问题

我有4张表：artist、album、song_cover和song，需要查询专辑信息及其对应的歌曲总数。当前用的查询语句被记录在mysql-slow.log里，执行速度不稳定，有时候只需要0.0005秒，慢的时候要2秒甚至更久。

当前使用的查询语句 #

SELECT /*+ MAX_EXECUTION_TIME(1000) */ album.*, 
artist_id, artist_aka, artist_slug, artist_profile_image, cover_filename,
(
   SELECT COUNT(*)
   FROM song
   WHERE song.song_album_id = album.album_id
) AS TotalSongs
FROM album 
LEFT JOIN artist ON album.album_artist = artist.artist_id 
LEFT JOIN song_cover ON album.album_cover_id = song_cover.cover_id 
ORDER BY album_id DESC LIMIT 0, 11

各表数据量 #

• artist：15,978行
• album：14,167行
• song：67,559行
• song_cover：12,668行

EXPLAIN执行计划 #

从截图的执行计划来看：

• 主查询里album表走了全表扫描（type为ALL），还做了文件排序（Extra显示Using filesort），扫描行数约14167行；
• artist和song_cover表通过主键关联，用eq_ref类型的索引查询，每次只扫描1行；
• 子查询是依赖型子查询（DEPENDENT SUBQUERY），song表通过song_album_id字段做ref查询，每次扫描约4行。

优化方案 #

1. 替换子查询为JOIN分组统计 #

当前的子查询会跟着主查询的每一行执行一次（总共11次），缓存失效时容易变慢。改成提前统计所有专辑的歌曲数，再关联查询：

SELECT /*+ MAX_EXECUTION_TIME(1000) */ album.*, 
       artist.artist_id, artist.artist_aka, artist.artist_slug, artist.artist_profile_image, 
       song_cover.cover_filename,
       COALESCE(song_count.TotalSongs, 0) AS TotalSongs
FROM album 
LEFT JOIN artist ON album.album_artist = artist.artist_id 
LEFT JOIN song_cover ON album.album_cover_id = song_cover.cover_id 
LEFT JOIN (
    SELECT song_album_id, COUNT(*) AS TotalSongs
    FROM song
    GROUP BY song_album_id
) AS song_count ON album.album_id = song_count.song_album_id
ORDER BY album.album_id DESC LIMIT 0, 11

这种方式只需要对song表做一次分组统计，效率比逐行子查询高很多。

2. 补全必要的索引 #

• 检查song表的song_album_id字段有没有索引，没有的话创建：

  CREATE INDEX idx_song_album_id ON song(song_album_id);
  

• album表的album_artist和album_cover_id字段如果没索引，也建议加上，能让JOIN操作更高效：

  CREATE INDEX idx_album_artist ON album(album_artist);
  CREATE INDEX idx_album_cover_id ON album(album_cover_id);
  

3. 避免用SELECT * #

album.*会查询专辑表的所有字段，如果不需要某些字段，明确列出需要的字段，减少数据传输和内存消耗，能小幅提升查询速度。

4. 优化排序逻辑 #

album_id是主键，默认有主键索引，但EXPLAIN显示用了文件排序，可能是MySQL优化器选择了全表扫描。可以尝试强制使用主键索引：

SELECT /*+ MAX_EXECUTION_TIME(1000) INDEX(album PRIMARY) */ album.*, 
-- 后续语句与优化后的JOIN版本一致

或者确认主键索引的状态是否正常，有没有碎片需要整理。

内容的提问来源于stack exchange，提问作者Busker McGreen Brian