mysql亿级大表重构方案介绍
作者:sylar版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
【情况简述】
本文主要分享的博主将mysql生产环境上亿大表按照一定规则拆分成若干个小表并迁移的思路、实现方式、注意事项等等。
【背景说明】
生产环境favourite表5.8亿,情况如下:
表名
表结构
rows
数据库版本
favourite
CREATE
TABLE `favourite` (
`id`
int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`target_type` int(11) NOT NULL,
`target_id` int(11) NOT NULL,
`created_at` datetime NOT NULL,
`status` smallint(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_user_target`
(`user_id`,`target_type`,`target_id`),
KEY
`idx_targetid` (`target_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
587312519
5.7.12
下面sql因表的量级变的比较慢,已无法通过调整索引或调整sql进行优化:
SQL
time
SELECT
count(1) AS count_1 FROM `favourite` WHERE `favourite`.target_id = 636 AND
`favourite`.target_type = 1
4.7S
SELECT
`favourite`.target_id AS `favourite_target_id` FROM `favourite` WHERE
`favourite`.user_id = 338072 AND `favourite`.target_type = 0 AND
`favourite`.status = 0 ORDER BY `favourite`.id DESC
2.25S
DELETE FROM
favourite WHERE user_id = 17327373 AND target_id = 917 AND target_type = 1
0.9S
为了业务响应比较快,决定拆分favourite表。经过业务沟通,user_id使用较为频繁,故通过user_id拆分,拆分规则根据user_id%1024打算到1024表,映射关系如下:
user_id%1024 =0
=>favourite_0000
user_id%1024 =1
=>favourite_0001
user_id%1024 =2
=>favourite_0002
……
user_id%1024 =1023
=>favourite_1023
注意:
1)拆分一定要根据业务情况来决定,不能一概而论!
【思路说明】
1、配置好canal ,canal是阿里开源的获取binlog信息的软件。从第一步开始到最后结束,canal一直不停获取binlog信息。
2、在不影响业务的数据库上(此处用的从库)将favourite导出成1024个表对应的文件
3、将导出备份文件导入生产环境
4、将canal获取的数据导入到1024个分表(一直进行直到结束)
5、待分表数据与原大表数据差不多时,在业务不繁忙时,切favourite业务读操作
6、切生产favourite写操作
7、待canal无新的记录产生,整个业务切换完毕
8、结束
注意:
1)使用canal获取binlog信息,注意参数设置为
binlog_format=row
binlog_row_image=full(默认是FULL,以防有些实例设置为minimal)
binlog_row_image=minimal,此时主库进行delete
from a where target_id
=,在binlog记录的也是这样的操作,而如果target_id是唯一索引,则到了binlog记录的是delete from a where id(id是主键)。同样生产是delete favourite
是根据`user_id`,`target_type`,`target_id`,根据前面所说记录到binlog的是 delete from favourite
where id,canal需要在业务发生切换时候记录变更的情况,可是获取的是删除的id,新的分表的id不会跟旧的favouriteid相同(因为旧的favourite的id已经到了8亿多,新的分表沿用旧表id是很浪费的情况)并不知道user_id是哪个,无法对新表进行同步。
2)服务先切读再切写的这个方式,在切换时间内会有一定数据误差的。比如用户在业务切换读操作的同时,往favourite表插入了一条数据;可是写操作没有切过来,数据落入旧的favourite表,用户就会发现异常影响用户的体验。这个情况直到canal将所有信息同步到新的分表后才解决。如果自己的业务无法接受这个情况,可以根据实际情况调整,不一定按照此方式切换服务。
【方案说明】
根据上面思路,需要解决两个问题
1)如何配置canal。canal是阿里推出的获取binlog的开源产品,我们此次canal调用是java工程师帮忙写的代码,博主不懂java,故省略canal配置信息。
2)如何将favourite导出成1024个分表所需的数据,然后倒入指定库。
下面主要说明问题2实现的方式,一共有两种:
items
方案一
方案二
实现手段
mysqldump
mycat
拆分耗时
4.5Hour
2Hour
准备时间
3Hour,需要加函数索引
<1Hour,准备mycat环境和mycat对应的数据库
优点
不需要配置mycat环境
时间比方案一节省2Hour,导入目标环境后不需在初始化id
缺点
耗时太久、导入目标环境后还需要初始化id
需要熟悉mycat配置、分库规则
具体方式
Step1.在从库建立函数索引,耗时3Hour
Step2.在从库使用mysqldump的--where参数导出
Step3.导入目标库并初始化id
Step1.搭建mycat环境,并配置好相关规则
Step2.使用mysqldump备份文件
Step3.将备份文件导入mycat
Step4.在mycat对应库初始化id
Step5.将处理后的文件导入目标库
【方式一:mysqldump】
Step1.在从库建立函数索引,耗时3Hour
altertable favourite add
`vis_user_id` int(11) GENERATED ALWAYS AS ((`user_id` % 1024)) STORED;
注意:
1)要在从库建立函数索引,影响会降低很多,如果能把让生产不访问该从库更好。确保生产环境访问该从库时没有select * from favourite where
…..这样的命令
2)如果数据库版本低于5.7无法使用函数索引,那么step2.mysqldump备份一次开启4个并发进程,一次耗时230秒;如果有索引,则为30-60秒
Step2.在从库使用mysqldump的--where参数导出
思路:
1)使用--where=" user%1024=0001"导出成按拆分规则命名的文件,该例子对应文件名为0001.sql,一共会产生1024个这样的文件。
2)然后根据导出的文件名用sed命令替换表名(sed是shell命令)
具体脚本如下:
点击(此处)折叠或打开
#查看log日志调用并发函数实现多线程备份
function dumpAllTable()
{
local schemaFile="${parallel_log}"
#最大的表先备份(因多进程并发,最短完成时间依赖于最大表的完成)
allTable=`cat $schemaFi