MariaDB中基于ENUM列分区的可行替代方案咨询

针对你在MariaDB 10.6中无法直接基于ENUM列分区的问题，以下是几种数据库层面的解决方案，按优先级排序：

1. 利用ENUM的内部整数特性实现LIST分区（最优解） #

MariaDB的ENUM类型在底层是以整数形式存储的：第一个枚举值对应1，第二个对应2，以此类推。你可以直接基于ENUM列的内部整数值创建LIST分区，不需要额外添加列或触发器，性能最优。

实现代码 #

DROP TABLE IF EXISTS test_performance_data;

CREATE TABLE test_performance_data
(
    id            INT AUTO_INCREMENT PRIMARY KEY,
    resource_type ENUM ('campaign', 'ad_group', 'ad_item', 'keyword'),
    resource_id   INT, -- 对应多态关联的ID
    -- 其他业务字段...
)
PARTITION BY LIST(resource_type) (
    PARTITION campaigns VALUES IN (1), -- 对应ENUM值 'campaign'
    PARTITION ad_groups VALUES IN (2), -- 对应ENUM值 'ad_group'
    PARTITION ad_items VALUES IN (3), -- 对应ENUM值 'ad_item'
    PARTITION keywords VALUES IN (4)  -- 对应ENUM值 'keyword'
);

注意事项 #

• 必须保证resource_type的ENUM值顺序固定，不要随意在现有枚举值中间插入新值，否则会打乱内部整数映射关系，导致分区数据错乱。如果需要新增枚举类型，只能追加到ENUM列表末尾，并同步新增对应的分区。
• 查询时依然可以使用ENUM字符串值（如WHERE resource_type = 'campaign'），数据库会自动转换为内部整数，不影响查询逻辑和性能。

2. 触发器同步分区ID列（备选方案） #

如果担心ENUM顺序变更的风险，可以添加独立的partition_id整数列，通过触发器自动同步resource_type对应的数值。虽然会有轻微性能开销，但可以避免ENUM顺序依赖。

实现代码 #

DROP TABLE IF EXISTS test_performance_data;

CREATE TABLE test_performance_data
(
    id            INT AUTO_INCREMENT PRIMARY KEY,
    resource_type ENUM ('campaign', 'ad_group', 'ad_item', 'keyword'),
    partition_id  INT NOT NULL,
    resource_id   INT,
    -- 其他业务字段...
)
PARTITION BY LIST(partition_id) (
    PARTITION campaigns VALUES IN (1),
    PARTITION ad_groups VALUES IN (2),
    PARTITION ad_items VALUES IN (3),
    PARTITION keywords VALUES IN (4)
);

-- 插入触发器
DELIMITER //
CREATE TRIGGER sync_partition_id_insert BEFORE INSERT ON test_performance_data
FOR EACH ROW
BEGIN
    SET NEW.partition_id = CASE NEW.resource_type
        WHEN 'campaign' THEN 1
        WHEN 'ad_group' THEN 2
        WHEN 'ad_item' THEN 3
        WHEN 'keyword' THEN 4
    END;
END //
DELIMITER ;

-- 更新触发器（如果允许修改resource_type）
DELIMITER //
CREATE TRIGGER sync_partition_id_update BEFORE UPDATE ON test_performance_data
FOR EACH ROW
BEGIN
    SET NEW.partition_id = CASE NEW.resource_type
        WHEN 'campaign' THEN 1
        WHEN 'ad_group' THEN 2
        WHEN 'ad_item' THEN 3
        WHEN 'keyword' THEN 4
    END;
END //
DELIMITER ;

优缺点 #

• 优点：分区逻辑与ENUM顺序解耦，后续调整ENUM顺序不会影响分区。
• 缺点：触发器会增加INSERT/UPDATE操作的性能开销，批量写入时尤为明显；需要维护额外的触发器代码。

3. 配合CHECK约束手动维护分区ID（折中方案） #

如果不想依赖触发器，也可以在表中添加partition_id列，并通过CHECK约束强制其与resource_type的对应关系，由应用层（如Laravel模型）自动填充该值。这种方式兼顾数据一致性和性能，但需要应用层配合。

实现代码 #

DROP TABLE IF EXISTS test_performance_data;

CREATE TABLE test_performance_data
(
    id            INT AUTO_INCREMENT PRIMARY KEY,
    resource_type ENUM ('campaign', 'ad_group', 'ad_item', 'keyword'),
    partition_id  INT NOT NULL,
    resource_id   INT,
    -- 其他业务字段...
    CONSTRAINT chk_resource_partition CHECK (
        (resource_type = 'campaign' AND partition_id = 1)
        OR (resource_type = 'ad_group' AND partition_id = 2)
        OR (resource_type = 'ad_item' AND partition_id = 3)
        OR (resource_type = 'keyword' AND partition_id = 4)
    )
)
PARTITION BY LIST(partition_id) (
    PARTITION campaigns VALUES IN (1),
    PARTITION ad_groups VALUES IN (2),
    PARTITION ad_items VALUES IN (3),
    PARTITION keywords VALUES IN (4)
);

注意事项 #

• 在Laravel中，可以通过模型的boot方法添加观察者，在保存时自动设置partition_id，避免手动维护：

// 在PerformanceData模型中
protected static function boot()
{
    parent::boot();

    static::saving(function ($model) {
        $model->partition_id = match($model->resource_type) {
            'campaign' => 1,
            'ad_group' => 2,
            'ad_item' => 3,
            'keyword' => 4,
        };
    });
}

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