记一次mycat日活量数亿级别分库分表方案及数据迁移

先说一下业务场景。项目是负责公司短信下发的，目前每日下发量大概在千万级别，采取了每天一张表的分表方案，但为了后续新的发送方接入，需要支持每日近亿级别的发送量，此时每天一张发送日志表的分表方案明显已不再支持。经过激烈讨论，我们引入了mycat的解决方案。

考虑到发送量的大小，我们对发送日志表进行了分库分区操作，保证每一个库和区能够均匀承担数据压力，我们决定对两个字段进行取模。即手机号对200取模决定在哪一个库（分了两百个库，每天一个分区），再传入日期（比如20200127），决定数据落在哪一个分区。

mycat多表查询时会先拿到数据再进行逻辑处理,所以我们设计的时候秉持着尽量分表单表定向查询的原则，把原来一些需要联合查询的字段都放到了rcs_send_log这张分库表中。

partition_name

varchar(12) DEFAULT NULL COMMENT ‘表分区字段-年月日’,

db_split

varchar(20) DEFAULT NULL COMMENT ‘分库字段 （手机号）’

对于知道 partition_name和db_split的增删改查语句，我们能够很快的查询出该记录。但对于批量查询，能避免则避免，无法避免时，通过查询每个库去得到执行结果。

通过在sql语句前加下面这一行，去指定库

类似这种

数据割接

因为牵扯到割接的数据量过大，且mycat不支持select into模式的批量查询。这里进行割接采用的是load data。能实现百万数据十几秒导入。

具体导入导出脚本如下。

#从原库导出脚本
SELECT send_log_id,task_id,sm_type,msg_id,send_port,phone_no,create_op_id,product_id,create_date,create_org_id,send_state,sm_scan_time,send_date
recv_date,deliver_time,deliver_stat,downd_state,downd_date,data_state,remarks,product_attr,sp_type,province_id,SPLIT_COUNT,batch_no,city_id,main_prod_flag FROM rcs_send_log_20201211 INTO OUTFILE '/data/backup/rcs_send_log_20201211.txt' LINES TERMINATED BY '\n’;

#退出原库，连接mycat，切换到虚拟库
 use database1;
#导入数据 terminated 表示指定分隔符 ignore表示忽略冲突的数据
load data infile '/data/backup/rcs_send_log_20201211.txt' ignore into rcs_send_log fields terminated by ',' lines terminated by '\n' (log_id,task_id,sm_type,msg_id,send_port,phone_no,create_op_id,product_id,create_date,create_org_id,send_state,sm_scan_time,send_date
recv_date,deliver_time,deliver_stat,downd_state,downd_date,data_state,remarks,product_attr,sp_type,province_id,SPLIT_COUNT,batch_no,city_id,main_prod_flag ) ;
           

ps：这里的导出目录，需要在被迁移的库配置文件中指定，

secure_file_priv =/data/backup/

如果数据量过大，mycat因为某些未知的问题不支持时，我们可以对导出的数据进行分割，然后导入。

可将数据全部导出到一个文件，通过linux命令将文件分割成50w一个的小文件。

split -l 500000 rcs_send_log_xxxx -d -a 3 rcs_send_log_xxxxx_

-l 500000 表示每500000行切割文件

-d 表示添加数字后缀

-a 表示以3位数数字做尾数

rcs_send_log_xxxxx_ 分割后文件的前缀