mysql 亿级_mysql亿级大表重构方案介绍

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值