如何优化含多个UNION的多列bonus查询以降低CPU占用？

替代查询语句（更低CPU占用，避免多次全表扫描） #

方案1：UNION ALL + 单次去重（简单易上手）

SELECT DISTINCT bonus
FROM (
    SELECT bonus1 AS bonus FROM rolls WHERE gear_type = 0
    UNION ALL
    SELECT bonus2 AS bonus FROM rolls WHERE gear_type = 0
    UNION ALL
    SELECT bonus3 AS bonus FROM rolls WHERE gear_type = 0
    UNION ALL
    SELECT bonus4 AS bonus FROM rolls WHERE gear_type = 0
    UNION ALL
    SELECT bonus5 AS bonus FROM rolls WHERE gear_type = 0
    UNION ALL
    SELECT bonus6 AS bonus FROM rolls WHERE gear_type = 0
) AS all_bonuses
WHERE bonus != 0; -- 过滤默认无效的0值，减少数据处理量

方案2：行转列单表扫描（SQLite适配）

如果使用的是SQLite，这种方式只需扫描一次表，效率更高：

SELECT DISTINCT
    CASE col_num
        WHEN 1 THEN bonus1
        WHEN 2 THEN bonus2
        WHEN 3 THEN bonus3
        WHEN 4 THEN bonus4
        WHEN 5 THEN bonus5
        WHEN 6 THEN bonus6
    END AS bonus
FROM rolls, (VALUES(1), (2), (3), (4), (5), (6)) AS cols(col_num)
WHERE gear_type = 0
AND CASE col_num
        WHEN 1 THEN bonus1
        WHEN 2 THEN bonus2
        WHEN 3 THEN bonus3
        WHEN 4 THEN bonus4
        WHEN 5 THEN bonus5
        WHEN 6 THEN bonus6
    END != 0;

核心优化逻辑 #

1. 替换UNION为UNION ALL：原语句的UNION会对每个子查询结果单独排序去重，6次重复操作会大量消耗CPU；UNION ALL仅合并结果不做去重，最后通过单次DISTINCT完成去重，CPU负载会大幅降低。
2. 过滤无效值：bonus列默认值为0，过滤掉这些无效值能减少需要处理的数据量，加快查询速度。
3. 添加索引（最关键优化）：原查询每次都要全表扫描筛选gear_type=0的行，创建索引能让数据库快速定位目标数据：

-- 基础版：快速筛选gear_type=0的行
CREATE INDEX idx_rolls_gear_type ON rolls(gear_type);

-- 进阶版：包含所有bonus列的复合索引，进一步减少磁盘IO
CREATE INDEX idx_rolls_gear_bonuses ON rolls(gear_type, bonus1, bonus2, bonus3, bonus4, bonus5, bonus6);

效果预期 #

添加索引后，75万条数据的查询耗时能压缩到几百毫秒内，CPU占用也会显著下降，避免免费主机的CPU超限问题。

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