如何替换SQL中的CASE语句优化800万条数据查询性能？

针对800万条记录的表，原CASE语句逐条判断会触发大量实时计算，拖慢查询速度并占用过多内存，以下是几种可行的替代方案：

方案1：预定义映射表+JOIN关联 #

通过创建存储规则的映射表，将条件判断转换为JOIN匹配，利用数据库的索引优化能力提升性能，同时方便后续规则修改。

步骤1：创建映射表并导入规则 #

CREATE TABLE symbol_mapping (
    symbol_key VARCHAR(10) PRIMARY KEY,
    category VARCHAR(20) NOT NULL
);

INSERT INTO symbol_mapping (symbol_key, category)
VALUES 
    ('D', 'Domain'),
    ('A_PREFIX', 'Alpha'), -- 用标识区分前缀匹配规则
    ('C', 'Charlie');

步骤2：关联查询并保持原CASE优先级 #

由于原CASE存在判断顺序优先级（D优先，其次A开头，最后C），使用窗口函数确保取到第一条匹配的规则：

SELECT category
FROM (
    SELECT 
        t.id,
        CASE
            WHEN sm.symbol_key = 'D' AND t.symbol = 'D' THEN sm.category
            WHEN sm.symbol_key = 'A_PREFIX' AND t.symbol LIKE 'A%' THEN sm.category
            WHEN sm.symbol_key = 'C' AND t.symbol = 'C' THEN sm.category
        END AS category,
        -- 按原CASE的判断顺序排序，确保优先级高的规则先匹配
        ROW_NUMBER() OVER (
            PARTITION BY t.id 
            ORDER BY 
                CASE sm.symbol_key 
                    WHEN 'D' THEN 1 
                    WHEN 'A_PREFIX' THEN 2 
                    WHEN 'C' THEN 3 
                    ELSE 4 
                END
        ) AS rn
    FROM your_table t
    CROSS JOIN symbol_mapping sm
) sub
WHERE rn = 1 AND category IS NOT NULL;

方案2：预处理分类字段（推荐大表场景） #

如果分类规则不频繁变动，直接在表中新增存储分类结果的字段，通过初始化更新和触发器维护，查询时直接读取字段值，彻底避免运行时计算。

步骤1：新增字段并初始化数据 #

ALTER TABLE your_table ADD COLUMN category VARCHAR(20);

-- 一次性初始化现有数据
UPDATE your_table 
SET category = 
    CASE 
        WHEN symbol = 'D' THEN 'Domain'
        WHEN symbol LIKE 'A%' THEN 'Alpha'
        WHEN symbol = 'C' THEN 'Charlie'
    END;

步骤2：创建触发器自动维护字段 #

确保新增或更新symbol时自动同步分类值：

-- MySQL 触发器示例
DELIMITER //
CREATE TRIGGER trg_sync_category_insert
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    SET NEW.category = 
        CASE 
            WHEN NEW.symbol = 'D' THEN 'Domain'
            WHEN NEW.symbol LIKE 'A%' THEN 'Alpha'
            WHEN NEW.symbol = 'C' THEN 'Charlie'
        END;
END //

CREATE TRIGGER trg_sync_category_update
BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN
    IF NEW.symbol != OLD.symbol THEN
        SET NEW.category = 
            CASE 
                WHEN NEW.symbol = 'D' THEN 'Domain'
                WHEN NEW.symbol LIKE 'A%' THEN 'Alpha'
                WHEN NEW.symbol = 'C' THEN 'Charlie'
            END;
    END IF;
END //
DELIMITER ;

之后查询直接读取字段：

SELECT category FROM your_table;

方案3：函数索引（数据库支持时使用） #

如果不想修改表结构但希望加速CASE逻辑的查询，可以创建基于CASE表达式的函数索引，将计算逻辑提前到索引构建阶段：

-- MySQL 8.0+/PostgreSQL 支持函数索引
CREATE INDEX idx_symbol_category ON your_table(
    CASE 
        WHEN symbol = 'D' THEN 'Domain'
        WHEN symbol LIKE 'A%' THEN 'Alpha'
        WHEN symbol = 'C' THEN 'Charlie'
    END
);

创建索引后，原查询会自动使用索引加速，减少运行时计算开销，但索引会占用额外存储空间，且插入/更新数据时会有少量性能损耗。

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