mysql亿级大表重构方案介绍

本文主要分享的博主将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,需要加函数索引

优点

不需要配置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小时以上的时间，且导入目标库后无需其他处理，故生产环境迁移使用的方案二

推荐内容：MySQL大表优化方案是什么免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@if98.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。开发云公众号手机网站二维码Copyright Yisu Cloud Ltd. All Rights Reserved. 2018 版权所有广州开发云计算有限公司粤ICP备17096448号-1
粤公网安备 44010402001142号增值电信业务经营许可证编号：B1-20181529

相关推荐: Shell脚步之MySql分库分表备份

数据备份的重要性已经不用过多强调了。这篇文章我们就以MySQL数据进行演示如何通过Shell脚步进行MySQL数据库的分库分表备份。Linux命令要求比较熟悉，Shell脚步语法规则也要熟悉，MySQL的数据库备份命令要比较熟悉。下面也会提到，如果有不懂得希望…

免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。