MYSQL 数据库大太的解决方案

#数据存储/MYSQL

面对问题

插入查询慢、且需要时效性比较强的情况

原因

MYSQL 容量上千万条以上就会很慢。

解决方法

分区

对应用透明，相对操作比较简单。

对主键有要求：所有主键里必须包含分区主键，如果又想用 id 查，又想用年份查就比较难。

查询会受一些影响：批量查时快不了多少，插入可以提速。

分库

比如不同年份可以放在不同主机上，以加快速度。

分表

一般都使用分表策略。

需要看按什么分，比如按用户 id 分，或者按年份分表。

按时间分：热表（当前一年数据）、冷表（往年数据），最后一个年份的冷表可能看需要看是否保存最新数据；需要确定冷热表是否重叠，如果重叠。

如按年份分表，注意按业务时间而不是编辑时间（这个时间可能变化）

迁移场景

需要定义迁移规则：比如每个月迁一次（新表、本年表、前一年表），对时间字段建索引

  • 历史数据迁移：如果表巨大，则不使用查询，从第一条开始向其它表里分流；选改表，再改其上应用。
  • 新表间迁移：假设每月迁一次，每月把这个月数据从热表里迁出来（同样是分流）；也可以双写，即每次写入冷表和热表，定期删热表最后的数据。
  • 补采数据：在修修补补的情况下（补采数据），又写可能更合适。
  • 数据表结构修改：不分表时操作一次，分表后需要同一操作，执行多次。
  • 注意一：单批次迁移每次 1000-10000 条，不要太大。
  • 注意二：在复杂的场景下，双写最好。

注意事项

  • 索引

    对于在巨大表中可能查询的数据一定要建索引

  • 表碎片清理

    热表不断地写入删除，需要定期清理，方法如下：

1
$ alter table 表名 engine=innodb;