MySQL中利用存储函数创建带索引生成列的最优方法

针对你遇到的用自定义函数myFunc处理dateCol后分组速度慢、无法用生成列创建索引的问题，根据不同MySQL版本，提供以下几种简洁可行的方案：

一、MySQL 8.0.13及以上版本：直接创建函数索引 #

这是最理想的方案，无需额外列或表，直接基于函数计算结果创建索引：

-- 创建函数索引
CREATE INDEX idx_datePrime ON baseTable((myFunc(dateCol)));

之后执行分组插入时，直接使用函数表达式即可，MySQL会自动利用该索引加速：

CREATE TABLE derivedTable ...;

INSERT INTO derivedTable
SELECT id, myFunc(dateCol) AS datePrime, ...
FROM baseTable
GROUP BY id, myFunc(dateCol);

二、MySQL 5.7及以下版本：用触发器维护计算列 #

由于低版本不支持函数索引，也不允许生成列使用存储函数，可通过触发器+普通索引列的方式替代，避免冗余表：

1. 新增普通列存储计算结果：

ALTER TABLE baseTable ADD COLUMN datePrime INT;

2. 创建触发器，确保插入/更新dateCol时自动同步datePrime：

-- 插入前自动计算
DELIMITER //
CREATE TRIGGER trg_baseTable_insert
BEFORE INSERT ON baseTable
FOR EACH ROW
BEGIN
    SET NEW.datePrime = myFunc(NEW.dateCol);
END //
DELIMITER ;

-- 更新前自动计算
DELIMITER //
CREATE TRIGGER trg_baseTable_update
BEFORE UPDATE ON baseTable
FOR EACH ROW
BEGIN
    SET NEW.datePrime = myFunc(NEW.dateCol);
END //
DELIMITER ;

3. 初始化已有数据的datePrime值：

UPDATE baseTable SET datePrime = myFunc(dateCol);

4. 为datePrime创建索引：

CREATE INDEX idx_datePrime ON baseTable(datePrime);

后续分组操作直接使用datePrime即可，性能与生成列一致，且数据会自动保持同步。

三、一次性生成派生表场景：使用临时表 #

如果只是一次性生成多张派生表，不需要长期维护计算列，可使用临时表避免永久冗余：

-- 创建临时表并预计算datePrime
CREATE TEMPORARY TABLE temp_baseTablePrime AS
SELECT id, dateCol, myFunc(dateCol) AS datePrime, ...
FROM baseTable;

-- 为临时表创建索引
CREATE INDEX idx_temp_datePrime ON temp_baseTablePrime(datePrime);

-- 批量生成派生表
INSERT INTO derivedTable1 ... SELECT ... FROM temp_baseTablePrime GROUP BY id, datePrime;
INSERT INTO derivedTable2 ... SELECT ... FROM temp_baseTablePrime GROUP BY id, datePrime;

临时表会在会话结束后自动销毁，不会产生永久冗余表。

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