MySQL分区与索引性能对比及3000万数据存储选型咨询

我们有一张增量存储的报表数据表，将容纳数百万条记录，当前测试数据为1家公司10个用户共10万行。我们测试了三种优化方案：

• 为company_id、user_id添加单独索引（查询执行耗时687ms）
• 为(company_id, user_id)添加联合索引（耗时1.1s）
• 基于company_id做HASH分区，主键设为(company_id, user_id, id)并添加单独索引（耗时2.6s）

理论上分区性能应优于普通索引，但实际测试中分区查询扫描行数远多于非分区表，导致性能更慢，且分区索引占用空间更大。我们参考了分区相关文档进行配置，但结果未达预期。现咨询：当数据量达3000万条时，是否有必要使用分区，还是仅通过索引即可满足需求？

相关表结构及查询语句 #

(1) 非分区表（单独索引） #

CREATE TABLE `table1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `sensor` varchar(191) NOT NULL,
  `date` date DEFAULT NULL,
  `time_group` timestamp NULL DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_c_id` (`company_id`),
  KEY `idx_u_id` (`user_id`)
);

EXPLAIN 
select company_id, department, sum(value) as result  
from table1 
where company_id = 55 
  and user_id in (127, 128, 129, 130, 132, 133) 
  and (time_group between '2024-01-01 00:00:00' and '2024-01-30 23:59:59') 
group by sensor
order by result desc;

无分区、单独索引的查询执行计划结果

(2) 无分区、联合索引的查询执行计划结果 #

(3) 分区表 #

CREATE TABLE `table2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `sensor` varchar(191) NOT NULL,
  `date` date DEFAULT NULL,
  `time_group` timestamp NULL DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`company_id`, `user_id`, `id`),
  KEY `idx_id` (`id`),
  KEY `idx_c_id` (`company_id`),
  KEY `idx_u_id` (`user_id`)
) PARTITION BY HASH(company_id) PARTITIONS 11;

EXPLAIN 
select company_id, department, sum(value) as result  
from table2 
where company_id = 55 
  and user_id in (127, 128, 129, 130, 132, 133) 
  and (time_group between '2024-01-01 00:00:00' and '2024-01-30 23:59:59') 
group by sensor
order by result desc;

分区+单独索引的查询执行计划结果
分区+联合索引(company_id,user_id,time_group)的查询执行计划结果

索引空间对比 #

分区表的索引占用空间更大，用于统计索引大小的SQL语句如下：

SELECT 
    database_name, 
    table_name, 
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size' 
  AND index_name != 'PRIMARY' 
  and database_name = 'db'
ORDER BY size_in_mb DESC;

索引大小统计结果

MySQL版本信息 #

结论：3000万条数据规模下，优先优化索引而非使用HASH分区 #

你的测试结果已经给出明确信号：当前HASH分区方案不仅没提升性能，反而拖慢查询、增加索引空间占用，哪怕数据量涨到3000万，这种情况也很难逆转，核心原因如下：

1. HASH分区完全不匹配你的查询模式
   HASH分区的作用是均匀分散数据到各分区实现负载均衡，但它无法像RANGE/LIST分区那样直接过滤无关分区（分区剪枝）。你的查询是精准匹配company_id，但HASH分区是对company_id做哈希计算后分配分区，MySQL无法直接定位单个分区，仍需扫描多个分区，这就是分区表扫描行数更多的直接原因。

2. 现有索引还有极大优化空间
   你当前的索引没有覆盖查询全链路，导致查询需要回表、额外排序分组：

   • 针对你的查询语句，最优方案是创建覆盖索引：(company_id, user_id, time_group, sensor, value)，包含WHERE过滤、GROUP BY和SELECT所需的所有字段，MySQL可以直接通过索引完成查询，无需访问主表，能大幅降低开销。
   • 哪怕数据到3000万，只要索引设计合理，单表性能完全能满足需求。

3. 分区的适用场景和你的业务不匹配
   分区不是“万能优化工具”，它更适合这些场景：

   • 按时间范围归档数据（比如按time_group做RANGE分区，快速删除旧数据）
   • 数据量突破数亿条，单表索引无法有效缓存的情况
   • 不同分区需要存储在不同介质的场景
     你的报表数据核心是基于公司、用户、时间的聚合查询，索引优化的性价比远高于分区。

具体优化建议 #

• 直接废弃当前HASH分区方案，使用非分区表
• 创建覆盖索引：CREATE INDEX idx_c_u_t_s_v ON table1(company_id, user_id, time_group, sensor, value);
• 修正查询语句中的department字段——你的表结构里没有这个字段，会导致查询报错或额外开销
• 若后续数据量突破1亿条且索引缓存命中率明显下降，再考虑按company_id做LIST分区（直接匹配公司ID到固定分区，实现分区剪枝），而非HASH分区

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