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.将处理后的文件导入目标库 |
alter table 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秒
思路：
1）使用--where=" user%1024=0001"导出成按拆分规则命名的文件，该例子对应文件名为0001.sql，一共会产生1024个这样的文件。
2）然后根据导出的文件名用sed命令替换表名（sed是shell命令）
具体脚本如下：
需要修改的配置文件：server.xml、schema.xml、rule.xml及其对应的partition-hash-int.txt。具体修改请查看附件conf.zip
conf.zip
注意：
1)需要提前创建1024个逻辑库
2)这里是根据方案一提到的函数索引对应的虚拟列vis_user_id来分的，这样可以直接用mycat的枚举分库，如果不想用虚拟列，可以用mycat hash来划分，这个对于数值划分方式等同于user%1024，这个详情参考mycat权威指南
3)需要提前在四个逻辑库里创建好用于mycat访问的数据库用户
4)在创建完1024个逻辑库后，登入mycat，再创建favourite表，这样每个逻辑库都有该表
为了能快速导入mycat，故根据mycat分成4个实例规则（可以有误差，不一定要完全一样），导出4份不同数据，以便可以同时4份文件灌入mycat
使用mysqldump导出4个文件，以下备份同时进行，耗时20分钟：
注意：
1)请在从库或业务不去访问的数据库上进行备份
2)上面设置的参数请根据实际情况调整，一定要加上-c --skip-add-locks参数，否则导入mycat会异常
将步骤2导出的四个备份文件同时灌入mycat，整个耗费时间不足90分钟。
注意:
1)请将该操作在后台执行，可以用screen命令来实现。因为mycat是长连接，即使中途断掉后，后面的语句可能还会继续执行，以防出现这样情况，请放到后台执行
此处耗时30分钟
1）在1024分库上初始化id,去掉虚拟列，具体脚本如下
此处耗时30分钟
将处理后的备份文件导入生产新库，并行导入，并行度最好不要超过3，因为dump导出后insert一次是3万左右，并行度太高，机器IO会hold不住，脚本如下：
点击(此处)折叠或打开
由于方案二速度比方案一节省至少2小时以上的时间，且导入目标库后无需其他处理，故生产环境迁移使用的方案二
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
