如何合并数百个同结构MySQL表为单表并保障读写性能？

现有数据库服务包含数百个对象，每个对象每隔数秒向专属的同结构无索引表插入数据，所有表仅内容不同，均包含timestamp列。

单表查询示例：

select timestamp from object1 limit 3;
+---------------------+
| timestamp           |
+---------------------+
| 2004-01-01 00:00:55 |
| 2004-01-01 00:01:03 |
| 2004-01-01 00:01:05 |
+---------------------+

通常的单表查询为按时间范围筛选数据：

select timestamp from object1 where timestamp > "2004-01-01 00:00:55" and timestamp < "2004-01-01 00:02:00";
+---------------------+
| timestamp           |
+---------------------+
| 2004-01-01 00:01:03 |
| 2004-01-01 00:01:05 |
| 2004-01-01 00:01:14 |
+---------------------+

现计划将所有表合并为含objectId列的单表（预计存储10亿条记录），目标查询语句为：

select timestamp from objects where timestamp > "2004-01-01 00:00:55" and timestamp < "2004-01-01 00:02:00" and objectid = 12;

拟采用InnoDB引擎，并执行以下索引创建操作：

alter table objects engine = InnoDB;
create index index_timestamp_objectid on objects (timestamp, objectid) using BTREE;

该表每分钟需插入数千条记录，担忧插入性能，尚未确定采用批量INSERT还是单条INSERT，现咨询两个问题：

1. 该方案是否适配需求，还有哪些优化措施？
2. 可额外配置哪些MySQL服务参数与Linux主机参数？

问题1：方案适配性与优化措施 #

方案适配性判断 #

当前方案基本适配需求：

• 合并单表后，通过objectId区分不同对象，符合业务逻辑；
• 创建的(timestamp, objectid)联合索引，能匹配目标查询的过滤条件，查询时可直接通过索引获取数据，避免全表扫描，适配10亿级数据规模的查询需求。

额外优化措施 #

• 调整索引顺序：将索引改为(objectid, timestamp)。目标查询先指定objectid再按时间范围筛选，此索引顺序可先定位特定objectId的数据集，再在子集内过滤时间范围，匹配度更高，查询效率更好；同时同objectId的记录集中存储，索引维护开销更可控。
• 采用批量INSERT：单条INSERT会频繁触发事务日志写入、索引更新，批量INSERT（每次插入500-1000条）能大幅减少事务提交次数和索引碎片生成，显著提升插入性能；注意批量大小不宜过大，避免单次事务占用过多资源。
• 分区表优化：针对10亿级数据，按timestamp做范围分区（按天/小时分区）。查询时仅扫描目标时间分区，无需遍历全表；删除历史数据直接DROP分区，比DELETE高效数倍；同时分区可分散存储压力，提升插入、查询的并行性。
• 优化事务特性：若业务允许，将表的事务隔离级别调整为READ COMMITTED，减少InnoDB锁开销；关闭autocommit，批量插入时手动控制事务提交，减少日志刷盘次数。
• 数据类型精简：objectid用整数类型（INT/BIGINT）替代字符串，减少存储空间与索引维护成本；timestamp采用DATETIME(3)或TIMESTAMP(3)存储毫秒级时间，平衡精度与存储大小。
• 定期整理索引碎片：业务低峰期执行OPTIMIZE TABLE或ALTER TABLE ... FORCE整理索引碎片，避免碎片过多影响插入、查询性能；分区表可通过分区自动管理碎片，无需频繁手动操作。

问题2：MySQL与Linux参数配置 #

MySQL服务参数优化 #

1. InnoDB缓存相关
   • innodb_buffer_pool_size：设置为服务器物理内存的50%-70%（如32G内存机器设为20G），让更多数据、索引缓存到内存，减少磁盘IO。
   • innodb_log_buffer_size：调整为64M-256M，提升批量插入时的日志缓存能力，降低刷盘频率。
   • innodb_log_file_size：设置为1G-4G（不超过buffer pool的1/4），增大redo log容量，减少checkpoint频率，提升写入性能。
2. 写入性能相关
   • innodb_flush_log_at_trx_commit：若业务可接受秒级数据丢失风险，设为2；需严格ACID则保持1。
   • sync_binlog：设为0或100，减少binlog同步磁盘的频率，提升写入速度。
   • innodb_write_io_threads：设为8-16，提升InnoDB写入IO的并行处理能力。
3. 其他参数
   • max_allowed_packet：调整为64M-256M，支持更大的批量INSERT语句。
   • table_open_cache：设为2048以上，避免频繁打开关闭表的开销。

Linux主机参数优化 #

1. 磁盘IO优化
   • 使用SSD存储InnoDB日志与数据分区，大幅提升随机读写性能。
   • 挂载磁盘时添加noatime和nodiratime选项，减少文件系统访问时间记录开销，编辑/etc/fstab后重新挂载生效。
   • 调整sysctl参数：
     • vm.dirty_ratio：设为20，内存脏页占比达20%时触发刷盘；*vm.dirty_background_ratio*设为10，设置后台刷盘阈值。
     • vm.swappiness：设为10，降低系统使用swap的频率，优先使用物理内存。
     • net.core.somaxconn：设为1024，提升MySQL连接队列长度，避免连接超时。
2. CPU与内存优化
   • 关闭不必要的系统服务，释放CPU、内存资源。
   • 开启CPU性能模式，执行cpupower frequency-set -g performance命令，提升CPU处理能力。

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