MariaDB中temp表小时级聚合查询慢的优化求助

问题背景 #

每日向temp表插入3000万-5000万行统计数据，插入频率不规则。每日午夜后需按小时拆分执行分组计算，将结果插入computed表，但当前单小时分组查询耗时约90秒，性能亟待优化。

基础环境：MariaDB 10.6，InnoDB引擎。

表结构 #

CREATE TABLE temp (
    id                  char(36)              NOT NULL default uuid() PRIMARY KEY,
    device              tinyint    unsigned   NOT NULL,
    country_code        varchar(2)            NOT NULL,
    canvas_id           bigint     unsigned   NOT NULL,
    paid_configured     int        unsigned   NOT NULL,
    paid_count          int        unsigned   NOT NULL,
    non_paid_configured int        unsigned   NOT NULL,
    non_paid_count      int        unsigned   NOT NULL,
    timestamp           timestamp             NOT NULL default current_timestamp() 

) engine = InnoDB;

现有索引 #

create index temp_composite_index on temp (
    timestamp,
    canvas_id,
    device,
    country_code
);

待优化查询语句 #

SELECT  canvas_id AS canvas_id,
        device AS device,
        country_code AS country_code,
        SUM(paid_configured) AS paid_configured_sum,
        SUM(paid_count) AS paid_count_sum,
        SUM(non_paid_configured) AS non_paid_configured_sum,
        SUM(non_paid_count) AS non_paid_count_sum
FROM temp
WHERE timestamp BETWEEN '2023-12-02 12:00:00' AND '2023-12-02 12:59:59' 
GROUP BY canvas_id, device, country_code;

执行计划 #

{
    "query_block": {
        "select_id": 1,
        "filesort": {
            "sort_key": "temp.canvas_id, temp.device, temp.country_code",
            "temporary_table": {
                "table": {
                    "table_name": "temp",
                    "access_type": "range",
                    "possible_keys": [
                        "temp_composite_index"
                    ],
                    "key": "temp_composite_index",
                    "key_length": "4",
                    "used_key_parts": [
                        "timestamp"
                    ],
                    "rows": 2609006,
                    "filtered": 100,
                    "index_condition": "temp.timestamp between '2023-12-10 12:00:00.000000' and '2023-12-10 12:59:59.000000'"
                }
            }
        }
    }
}

关键数据 #

{
    "rows_total": 30000000,
    "rows_between_timestamps": 1249369,
    "unique_combinations": {
        "canvas_id": 20,
        "device": 2,
        "country_code": 4
    }
}

优化建议 #

1. 构建覆盖索引消除回表与文件排序 #

当前索引仅利用了timestamp字段过滤数据，后续分组字段未被有效利用，且需要回表获取聚合字段数据，同时触发文件排序。建议创建包含过滤、分组、聚合字段的覆盖索引：

CREATE INDEX idx_temp_optimized ON temp (
    timestamp,
    canvas_id,
    device,
    country_code,
    paid_configured,
    paid_count,
    non_paid_configured,
    non_paid_count
);

该索引可让数据库直接通过索引完成：时间范围过滤 → 按分组字段有序遍历 → 聚合计算，无需回表访问原数据，也避免文件排序，大幅提升查询效率。

2. 替换UUID主键为自增主键 #

InnoDB采用聚簇索引，UUID的无序性会导致插入时频繁页分裂，加剧索引碎片化，同时降低查询性能。建议将主键修改为BIGINT AUTO_INCREMENT：

ALTER TABLE temp MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

（注：修改主键需注意数据迁移，建议在业务低峰期操作，或新建表重新导入数据）

3. 优化时间范围查询逻辑 #

用>=和<代替BETWEEN，避免因timestamp毫秒级精度导致的边界数据异常，同时逻辑更严谨：

WHERE timestamp >= '2023-12-02 12:00:00' 
  AND timestamp < '2023-12-02 13:00:00'

4. 调整内存临时表参数 #

由于分组组合仅160种（2024），可将临时表限制调大，让临时表完全在内存中创建，避免磁盘IO：

SET GLOBAL tmp_table_size = 1024 * 1024 * 1024; -- 1GB
SET GLOBAL max_heap_table_size = 1024 * 1024 * 1024; -- 1GB

（根据服务器内存实际情况调整，建议不超过物理内存的1/4）

5. 开启并行查询 #

MariaDB 10.6支持并行查询，可开启该特性提升大结果集的分组计算速度：

SET GLOBAL optimizer_switch = 'parallel_query=on';

6. 考虑实时聚合替代批量计算 #

鉴于分组组合数极少，可在数据插入时直接更新computed表，避免每日批量处理：

INSERT INTO computed (canvas_id, device, country_code, paid_configured_sum, paid_count_sum, non_paid_configured_sum, non_paid_count_sum)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
paid_configured_sum = paid_configured_sum + VALUES(paid_configured_sum),
paid_count_sum = paid_count_sum + VALUES(paid_count_sum),
non_paid_configured_sum = non_paid_configured_sum + VALUES(non_paid_configured_sum),
non_paid_count_sum = non_paid_count_sum + VALUES(non_paid_count_sum);

（需在computed表上创建(canvas_id, device, country_code)的唯一索引）

内容的提问来源于stack exchange，提问作者Domagoj Samardžić