MySQL百万级数据下查询各分类前3张关联照片的优化方法

你当前用UNION拼接子查询的写法，本质是对每个传入的lightbox_id单独执行一次连表逻辑，当分类数量增长到100个以上时，就会触发上百次连表扫描，在百万级数据量下性能会随分类数线性下跌，可按以下优先级优化：

1. 优先补全核心索引（成本最低，收益最高） #

索引是这个场景下性能提升的核心，没有合适的索引，再怎么改SQL效果都有限：

• 给中间表lightboxphotos创建联合覆盖索引：(lightboxphoto_lightbox_id, lightboxphoto_sortorder, lightboxphoto_photo_id)。这个索引可以直接按lightbox_id过滤数据、按sortorder排序，不需要回表就能拿到关联的photo_id，能把单lightbox_id的查询扫描量降到个位数。
• 给categories表的lightbox_id字段创建唯一索引：你提到categories和lightboxes是一对一关系，唯一索引既可以保证数据一致性，也能加速两表关联。
• photos表的主键photo_id默认就是聚簇索引，不需要额外调整。

索引建完后可以对单个lightbox_id的子查询跑EXPLAIN，确认执行计划走了新建的覆盖索引，没有全表扫描，单查询耗时应该直接降到毫秒级。

2. 改写SQL逻辑，替换UNION拼接方案 #

不要用N个UNION子查询重复扫描表，一次扫描过滤所有目标lightbox_id、分组取前3条即可，性能不会随分类数增长线性下跌。
如果你的环境是MySQL 8.0及以上版本，直接用窗口函数实现，写法最简洁性能最好：

SELECT
    photo_id,
    photo_name,
    category_name,
    category_id,
    category_parent_id,
    lightbox_id
FROM (
    SELECT
        p.photo_id,
        p.photo_name,
        c.category_name,
        c.category_id,
        c.category_parent_id,
        lp.lightboxphoto_lightbox_id AS lightbox_id,
        ROW_NUMBER() OVER (
            PARTITION BY lp.lightboxphoto_lightbox_id
            ORDER BY lp.lightboxphoto_sortorder ASC
        ) AS rn
    FROM lightboxphotos lp
    INNER JOIN photos p ON p.photo_id = lp.lightboxphoto_photo_id
    INNER JOIN categories c ON c.lightbox_id = lp.lightboxphoto_lightbox_id
    WHERE lp.lightboxphoto_lightbox_id IN (123, 345 /* 填入所有需要查询的lightbox_id即可 */)
) t
WHERE rn <= 3;

几个调整点说明：

• 把原SQL的LEFT JOIN改成INNER JOIN：你的业务需求是查绑定了lightbox的分类下的照片，不存在无对应lightbox、无对应照片的有效数据，INNER JOIN可以过滤掉无效匹配，比LEFT JOIN效率更高。
• 把驱动表从photos换成lightboxphotos：因为查询是按lightbox_id过滤，驱动表选过滤后数据量最小的lightboxphotos，关联效率远高于全表扫photos表。

如果你的环境是MySQL 5.x不支持窗口函数，可以用关联子查询实现，性能依然远好于UNION拼接：

SELECT
    p.photo_id,
    p.photo_name,
    c.category_name,
    c.category_id,
    c.category_parent_id,
    lp.lightboxphoto_lightbox_id AS lightbox_id
FROM lightboxphotos lp
INNER JOIN photos p ON p.photo_id = lp.lightboxphoto_photo_id
INNER JOIN categories c ON c.lightbox_id = lp.lightboxphoto_lightbox_id
WHERE lp.lightboxphoto_lightbox_id IN (123, 345 /* 填入所有目标lightbox_id */)
AND (
    SELECT COUNT(*)
    FROM lightboxphotos lp2
    WHERE lp2.lightboxphoto_lightbox_id = lp.lightboxphoto_lightbox_id
    AND lp2.lightboxphoto_sortorder <= lp.lightboxphoto_sortorder
) <= 3
ORDER BY lp.lightboxphoto_lightbox_id, lp.lightboxphoto_sortorder;

3. 业务层冗余优化（适合高访问量、分类规模大的场景） #

如果后续分类规模涨到几百上千，读请求量又很高，可以直接在categories表新增3个字段存储预览图ID，比如preview_photo1、preview_photo2、preview_photo3。当lightbox内照片排序调整、照片绑定关系变动时，同步把对应分类下排序前3的照片ID更新到这几个字段里。
这样前台查询时不需要连lightboxphotos和photos表，直接查categories表就能拿到所有预览数据，查询耗时可以降到几毫秒，完全不受分类数增长影响。这个方案是把查询时的计算成本转移到写入时，对于读多写少的分类预览场景性价比极高，配合你计划做的缓存方案，基本不会有性能瓶颈。

按前两步优化完，10个分类的查询耗时应该能从1.8秒降到100毫秒以内，就算查询100个分类，耗时也基本能控制在300毫秒内，完全满足业务需求。

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