mysql 分表实践

公司有一个有 6000w 数据量的大表,在查询插入时慢,希望通过分表解决这个问题.

总共分为如下步骤:

• 分表数规划
• 分表主键选择
• 同步脚本编写
• 增量数据处理
• 上线迁移

分表数量规划

现有 6000w 数据是 3-4 年的积累, 规划为 单表上线为 500w,现在业务高速发展的时期,希望能支撑未来 2-3 年的使用,
取 2^5=32 张表,可容纳的总数据量为 32*500w= 1.6亿,满足业务需求.

主键选择

使用 openid(28 位字符串) 作为分表主键,使用 hashcode函数,对 32 取余,分到各个表中.
ps: 用户的数据不多的话，有的用户数据多，有的用户数据少. 不一定能保证数据的均匀性.

同步脚本的编写

开始编写脚本时,发现了两个问题

• 1. 在取旧数据时使用的 offset 加 limit 的方式,由于 offset 越到后面需要计算的数据量越大,同步特别的慢,
• 2. 使用单个线程跑同步脚本,并且还是单条循环插入

第一次改善如下:

• 1. 使用主键id区间的模式获取数据,提升的查询的速度

     SELECT * from tablename where id > prevId and id <= nextId

• 2. 开启了 32 个 goroutine 同时插入

第一次改善提升了查询的速度,但是插入的速度还是很慢.

第二次改善如下:

• 将单条插入改为拼接 sql 语句插入,大大提高了插入速度
  但是这个引起了另外一个问题:原来的数据里面有特殊的字符,如:',\等,在插入时会引起 sql语句语法错误,所以需要将他们进行转义.
  最开始将'转义成'',没什么问题,但是 gorm 在执行时会将 \ 转义掉,所以需要将 \ 转义成 \\\\.
  最开始没有发现转义的问题,测试同步完成检验数据的时候发现数据量对不上,然后在同步时将打印错误 log 时才发现.
  脚本如下 ./sync.sh 2>> logs.

增量数据处理

因为业务一直在跑,不能中断,增量数据处理同步写入旧表和新表,待业务低峰期(😂😂半夜) 切换数据表.

上线迁移

上线迁移时,发现磁盘容量不足,需要先扩容磁盘,防止磁盘写满.