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