MySQL多表JOIN查询缓慢，如何通过子查询优化并获取albums表的title_url字段

解决方案 #

可以通过关联子查询或者CTE的方式实现需求，不会损失原有单表全文检索的性能，整体耗时基本和单表查询持平。

优化后查询语句（匹配原逻辑取任意相册title_url） #

SELECT 
    p.media_extension, 
    p.media_vishash, 
    p.title_url AS photo_title_url, 
    p.media_time_created, 
    p.comments, 
    p.title, 
    p.text,
    MATCH(p.title,p.text,p.tag_list,p.comment_list,p.media_filename) AGAINST ('search query' IN BOOLEAN MODE) AS score,
    (
        SELECT ANY_VALUE(a.title_url) 
        FROM album_photo_map apm
        INNER JOIN albums a ON apm.album_id = a.album_id
        WHERE apm.media_vishash = p.media_vishash 
          AND a.level <= 5
    ) AS album_title_url
FROM media p
WHERE 
    MATCH(p.title,p.text,p.tag_list,p.comment_list,p.media_filename) AGAINST ('search query' IN BOOLEAN MODE)
    AND p.level <= 5
ORDER BY p.datetime_created DESC, p.media_vishash DESC

如果需要获取照片所属所有符合条件的相册title_url，可将子查询中的ANY_VALUE替换为GROUP_CONCAT(DISTINCT a.title_url SEPARATOR ',')。

性能保障说明 #

1. 外层查询完全保留了单表全文检索的逻辑，优先走search全文索引快速过滤符合条件的media记录，这部分耗时和你原来的0.01s单表查询一致。
2. 子查询仅对已经过滤完成的少量media记录执行，每次查询均走索引：
   • 先通过album_photo_map的media_vishash索引查找对应相册ID
   • 再通过albums的主键索引查询title_url，同时过滤level<=5的条件
     两次查询均为O(1)级别的索引查找，即使返回上百条结果，额外开销也可以忽略不计。
3. 避免了原联查的性能问题：原执行计划中MySQL优化器错误选择albums作为驱动表，全表扫描9千多条相册记录，还产生了临时表和文件排序；优化后的执行顺序强制先查media再关联，完全避免了这部分开销。

可选CTE写法（可读性更高，性能一致） #

MySQL 8.0支持CTE语法，你也可以用下面的写法，逻辑更清晰，性能和上面的子查询完全一致：

WITH filtered_media AS (
    SELECT 
        media_extension, 
        media_vishash, 
        title_url AS photo_title_url, 
        media_time_created, 
        comments, 
        title, 
        text,
        datetime_created,
        MATCH(title,text,tag_list,comment_list,media_filename) AGAINST ('search query' IN BOOLEAN MODE) AS score
    FROM media
    WHERE 
        MATCH(title,text,tag_list,comment_list,media_filename) AGAINST ('search query' IN BOOLEAN MODE)
        AND level <= 5
)
SELECT 
    fm.*,
    ANY_VALUE(a.title_url) AS album_title_url
FROM filtered_media fm
LEFT JOIN album_photo_map apm ON fm.media_vishash = apm.media_vishash
LEFT JOIN albums a ON apm.album_id = a.album_id AND a.level <=5
GROUP BY fm.media_vishash
ORDER BY fm.datetime_created DESC, fm.media_vishash DESC

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