MySQL超大规模报表表（数十亿行）配置与性能优化咨询

首先，咱们先梳理下你当前遇到的核心问题，再逐一拆解可行方案：

一、当前分区策略的问题根源 #

你现在用的是HASH分区+MyISAM引擎，刚好踩了两个关键坑：

1. HASH分区的裁剪（Pruning）失效：HASH分区是基于YEAR(date)*12+MONTH(date)的哈希值模96分配数据的，MySQL对HASH分区的精确值查询（比如WHERE date = NOW()），优化器经常无法正确触发分区裁剪，导致还是会扫描全部分区。换成按月份的RANGE分区就能彻底解决这个问题，因为WHERE date = '2024-08-01'可以直接定位到对应的月份分区。
2. MyISAM的表级锁硬伤：哪怕用了分区，MyISAM本身是表级锁，写操作（INSERT/UPDATE/DELETE）还是会锁定整个表，而非单个分区——这就是你遇到锁性能问题的核心原因，分区并不能改变MyISAM的锁机制。

另外，你的主键定义明显有复制错误：PRIMARY KEY (date,key_1,key_1,...)重复写了N个key_1，应该是date, key_1, key_2, ..., key_9。这个错误会导致主键索引完全不符合预期，严重拖慢查询性能，必须先修正！

二、InnoDB迁移后性能差的原因 #

你说迁移到InnoDB后SELECT慢数百倍，大概率是配置没跟上：

• InnoDB依赖缓冲池（innodb_buffer_pool_size）缓存数据和索引，如果你的缓冲池设置太小（比如默认的128M），对于300GB的表来说，根本无法缓存常用数据，每次查询都要做磁盘IO，自然比MyISAM慢。
• InnoDB的聚簇索引特性：主键就是聚簇索引，如果你的主键包含多个key列，聚簇索引的体积会很大；而MyISAM是非聚簇索引，查询走覆盖索引时不需要回表，这也是性能差距的重要原因。
• 日志、IO线程等配置没优化：比如innodb_log_file_size太小会导致频繁刷盘，innodb_read_io_threads不足会影响并发读性能。

三、分区VS分表（UNION/VIEW）的性能对比 #

1. 分表方案（按月份拆表）——适合继续用MyISAM的场景 #

如果不想放弃MyISAM，分表是比分区更好的选择：

• 锁粒度更小：每个月份是独立的表，写操作只会锁定当前月份的表，不会影响其他月份的查询/写入。
• 缓存命中率更高：每个子表的数据量只有原表的1/96左右，索引体积也更小，MySQL能把常用的子表数据/索引全部缓存到内存里，查询速度会大幅提升。
• 查询更灵活：可以直接查询指定月份的子表，也可以用UNION ALL创建视图统一查询：

  CREATE VIEW report_table AS
  SELECT * FROM report_table_202301
  UNION ALL SELECT * FROM report_table_202302
  ...
  UNION ALL SELECT * FROM report_table_202408;
  

  当你查询WHERE date = '2024-08-01'时，优化器会自动只扫描对应的子表，不会碰其他表。

缺点是需要手动/用存储过程自动创建新月份的表，代码层面可能需要适配写入逻辑（比如根据date自动路由到对应的子表）。

2. RANGE分区方案——适合迁移到InnoDB的场景 #

如果想转到InnoDB，一定要把HASH分区改成按月份的RANGE分区：

ALTER TABLE report_table 
PARTITION BY RANGE (TO_DAYS(date)) (
  PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
  PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
  ...
  PARTITION p202408 VALUES LESS THAN (TO_DAYS('2024-09-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

然后优化InnoDB配置：

• 设置innodb_buffer_pool_size为服务器内存的70%-80%（比如512GB内存的话设400GB），把常用数据/索引缓存起来。
• 开启innodb_file_per_table，每个分区对应独立的表空间，方便备份和维护。
• 调整innodb_log_file_size到4GB左右，减少日志刷盘频率。
• 增加innodb_read_io_threads和innodb_write_io_threads到16，提升并发IO性能。

InnoDB的行级锁会解决MyISAM的锁问题，配合RANGE分区的完美裁剪，性能会远超MyISAM。

四、额外的优化建议 #

• 归档旧数据：报表数据中，超过1-2年的旧数据查询频率极低，可以把这些数据归档到单独的冷表或者离线存储，减少主表的数据量。
• 优化查询语句：避免SELECT *，只查询需要的指标列，减少IO开销。
• 考虑OLAP引擎：如果报表以聚合查询为主（比如SUM/AVG/COUNT），可以考虑迁移到ClickHouse、MySQL ColumnStore这类列式存储引擎，性能会比行式存储的MyISAM/InnoDB高一个数量级。

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