百万级试题库按难度筛选优化：高效查询方案求助

问题 #

现有试题表结构如下：

CREATE TABLE questionbank (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT NOT NULL,
    difficulty_level ENUM('easy', 'medium', 'hard')
);

示例数据（部分）：

INSERT INTO `questionbank` (`id`, `content`, `difficulty_level`)
VALUES
    ('79617', 'gf-07-01-01-01-i02-q186', 'easy'),
    ('36255', 'sc-01-02-01-01-q51', 'easy'),
    ('44286', 'bm-09-01-02-01-q432', 'easy'),
    -- 其余示例数据省略
    ('38810', 'bm-05-01-02-01-q821', 'hard');

该表包含超10万条数据，需筛选出5道随机试题：2道easy、2道medium、1道hard。原查询使用窗口函数加子查询，速度过慢，且不想使用UNION方案：

SELECT * FROM ( 
    SELECT 
        *, 
        CASE 
        WHEN difficulty_level = 'easy' THEN 1
        WHEN difficulty_level = 'medium' THEN 2
        WHEN difficulty_level = 'hard' THEN 3
        END AS difficulty_order,
        ROW_NUMBER() OVER (PARTITION BY difficulty_level ORDER BY RAND()) AS row_num
    FROM questionbank
    ) AS subquery
WHERE (difficulty_level = 'easy' AND row_num <= 2)
OR (difficulty_level = 'medium' AND row_num <= 2)
OR (difficulty_level = 'hard' AND row_num <= 1)
ORDER BY difficulty_order, RAND()
LIMIT 5;

优化方案 #

1. 先添加核心索引 #

给difficulty_level字段创建索引，这是所有优化的基础，能大幅降低按难度过滤数据的时间：

CREATE INDEX idx_questionbank_difficulty ON questionbank(difficulty_level);

2. 高性能替代查询（推荐） #

虽然你提到不想用UNION，但UNION ALL的性能远优于原查询——每个子查询都能利用索引快速抽取随机样本，避免全表排序操作：

SELECT *
FROM (SELECT * FROM questionbank WHERE difficulty_level = 'easy' ORDER BY RAND() LIMIT 2) AS easy_questions
UNION ALL
SELECT *
FROM (SELECT * FROM questionbank WHERE difficulty_level = 'medium' ORDER BY RAND() LIMIT 2) AS medium_questions
UNION ALL
SELECT *
FROM (SELECT * FROM questionbank WHERE difficulty_level = 'hard' ORDER BY RAND() LIMIT 1) AS hard_questions
ORDER BY FIELD(difficulty_level, 'easy', 'medium', 'hard'), RAND();

性能优势：每个子查询仅扫描对应难度的索引数据，通过ORDER BY RAND() LIMIT N快速获取随机行，无需处理全表数据，整体执行时间远低于原查询的全表窗口函数排序成本。

3. 不使用UNION的优化写法 #

如果坚持不用UNION，可以利用用户变量实现分区行号，避免窗口函数的全表排序开销：

SELECT id, content, difficulty_level
FROM (
    SELECT 
        *,
        @row := CASE 
            WHEN @current_diff = difficulty_level THEN @row + 1
            ELSE 1
        END AS row_num,
        @current_diff := difficulty_level
    FROM questionbank,
         (SELECT @current_diff := '', @row := 0) AS init_vars
    ORDER BY difficulty_level, RAND()
) AS ranked_questions
WHERE 
    (difficulty_level = 'easy' AND row_num <= 2)
    OR (difficulty_level = 'medium' AND row_num <= 2)
    OR (difficulty_level = 'hard' AND row_num <= 1)
ORDER BY FIELD(difficulty_level, 'easy', 'medium', 'hard'), RAND();

优化点：通过用户变量逐行计算每个难度级别的行号，避免窗口函数PARTITION BY带来的全表排序操作；结合索引，MySQL会按难度顺序读取数据，进一步提升效率。

原查询慢的根本原因 #

原查询中ROW_NUMBER() OVER (PARTITION BY difficulty_level ORDER BY RAND())需要对全表数据按难度分区，每个分区内还要按随机数排序，10万条数据的排序操作会占用大量CPU和IO资源，导致查询速度变慢。

内容的提问来源于stack exchange，提问作者Bahasaweb.com