MySQL十万级blog表各子分类取Top6记录的性能与结果咨询

先直接回答你的两个问题，再展开细节：

1. 该查询能否生成预期结果？ #

基本可以，但有个容易踩的小坑。你的思路是对的：通过变量@rn和@prev给每个sub_cat_id分组编号，外层筛选出编号≤6的记录，确实能拿到每个目标分类下按created_date倒序的前6条。

但要注意：MySQL中如果不显式初始化用户变量，第一次运行时可能因为变量残留值导致编号错误（比如之前有其他查询用过@rn或@prev，初始值不是1或NULL）。建议修改成带变量初始化的版本，确保结果完全符合预期：

SELECT * FROM (
    SELECT 
        sub_cat_id, 
        title, 
        created_date,
        @rn:=IF(@prev = sub_cat_id, @rn + 1, 1) AS rn, 
        @prev:=sub_cat_id AS previd
    FROM 
        blog,
        (SELECT @rn:=1, @prev:=NULL) AS init -- 显式初始化变量
    WHERE 
        type = 'BLOG' 
        AND FIND_IN_SET(sub_cat_id, '1,2,8') 
        AND created_date <= NOW() 
    ORDER BY 
        sub_cat_id DESC, 
        created_date DESC
) AS records 
WHERE rn <= 6

2. 这是否是最优且最快的方式？ #

不是，尤其是在10万条记录的表中，性能可能存在瓶颈。

你的原查询需要先筛选出所有符合条件的记录，再做全局排序，最后逐一编号。如果这三个分类下的总记录数很多（比如每个分类有几万条），全局排序的开销会很大，大概率触发filesort，拖慢查询速度。

更优的替代方案：用UNION ALL拆分查询 #

针对固定的分类（1、2、8），直接对每个分类单独查询前6条，再用UNION ALL合并结果，性能会好很多：

(SELECT sub_cat_id, title, created_date 
 FROM blog 
 WHERE type='BLOG' AND sub_cat_id=1 AND created_date<=NOW() 
 ORDER BY created_date DESC LIMIT 6)
UNION ALL
(SELECT sub_cat_id, title, created_date 
 FROM blog 
 WHERE type='BLOG' AND sub_cat_id=2 AND created_date<=NOW() 
 ORDER BY created_date DESC LIMIT 6)
UNION ALL
(SELECT sub_cat_id, title, created_date 
 FROM blog 
 WHERE type='BLOG' AND sub_cat_id=8 AND created_date<=NOW() 
 ORDER BY created_date DESC LIMIT 6)

这个方案的优势：

• 每个子查询精准定位单个分类，配合索引可以直接快速获取前6条，不需要处理大量冗余数据
• 避免了全局排序和变量编号的额外开销，执行效率更高

索引优化（两种方案都适用） #

不管用哪种查询方式，都建议创建一个联合索引来最大化性能：

CREATE INDEX idx_blog_type_subcat_created ON blog (type, sub_cat_id, created_date DESC);

这个索引可以直接覆盖WHERE条件筛选和ORDER BY排序需求，让查询完全走索引，避免全表扫描或filesort，大幅提升速度。

方案选择总结 #

• 原方案更适合分类不固定（比如需要动态传入多个分类ID）的场景，但一定要做好变量初始化和索引优化
• UNION ALL方案在分类固定时性能最优，代码也更直观、易维护

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