MySQL亿级大表性能优化咨询：HASH分区有效性及优化方案

先直接给你结论：按HASH(direction_id)分区基本无效，甚至会因为MySQL的分区限制无法实施，我来给你详细拆解原因，再推荐几个更有效的优化手段。

一、HASH(direction_id)分区的问题 #

1. MySQL分区的硬性限制：MySQL要求所有唯一键（包括你的主键id）必须包含分区键。你的主键是单独的id，没有包含direction_id，所以根本无法直接创建HASH(direction_id)分区——尝试执行分区语句会直接报错。
2. 性能提升有限：退一步说，假设你修改了主键（比如改成(id, direction_id)），HASH分区后，同一个direction_id的所有数据会落在同一个分区里。但你的direction_id选择性极差，意味着单个direction_id的数据量本身就很大，查询时还是要扫描整个分区内的该direction_id数据，和原来的全表索引扫描相比，性能提升微乎其微，反而会增加分区管理的额外开销。

二、更有效的优化手段 #

1. 创建覆盖联合索引（优先级最高） #

你的查询需要direction_id过滤、created_at分组、rate求平均值，直接创建包含这三个字段的联合索引：

CREATE INDEX idx_dir_created_rate ON statistics (direction_id, created_at, rate);

这个索引的优势：

• 覆盖索引：查询时不需要回表读取原表数据，直接从索引中获取所有需要的字段，大幅减少IO开销。
• 有序性优化：索引中direction_id相同的记录，created_at是有序排列的，分组时不需要Using temporary和Using filesort（这两个是当前执行计划的性能瓶颈），直接按顺序计算平均值即可。

创建后，你的执行计划会变成type: range或ref，Extra字段会显示Using index，性能会有数量级的提升。

2. 构建汇总表（预处理数据） #

如果你的查询是周期性的（比如按天/小时统计），可以预先计算汇总数据，避免每次查询都扫描1亿条记录：

• 首先创建汇总表：

CREATE TABLE statistics_summary (
    direction_id INT(10) UNSIGNED NOT NULL,
    stat_date DATE NOT NULL, -- 按天统计，若需更细粒度可改为 DATETIME 按小时
    avg_rate DECIMAL(16,6) NOT NULL,
    record_count INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (direction_id, stat_date)
);

• 定时更新汇总表（比如每天凌晨用事件或脚本执行）：

INSERT INTO statistics_summary (direction_id, stat_date, avg_rate, record_count)
SELECT 
    direction_id,
    DATE(created_at) AS stat_date,
    AVG(rate) AS avg_rate,
    COUNT(*) AS record_count
FROM statistics
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY direction_id, stat_date
ON DUPLICATE KEY UPDATE 
    avg_rate = (avg_rate * record_count + VALUES(avg_rate) * VALUES(record_count)) / (record_count + VALUES(record_count)),
    record_count = record_count + VALUES(record_count);

• 查询时直接从汇总表取数，速度会快几十倍甚至上百倍。

3. 按created_at做RANGE分区 #

如果你的查询总是指定created_at的时间范围，可以按created_at做RANGE分区，让MySQL只扫描涉及的分区：

ALTER TABLE statistics 
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    -- 根据你的数据时间范围添加更多分区
);

这个方案配合上面的联合索引使用，能进一步减少扫描的数据量——查询时MySQL会自动跳过不涉及时间范围的分区，只在目标分区内使用索引查询。

4. 临时配置调整（治标不治本） #

如果暂时无法修改表结构，可以调整MySQL的内存参数，缓解Using temporary和Using filesort的压力：

• 增大sort_buffer_size（比如设置为64M），让排序操作在内存中完成。
• 增大tmp_table_size和max_heap_table_size，让临时表优先在内存中创建。
  但这只是临时缓解，无法从根本上解决问题，还是推荐前面的索引和汇总表方案。

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