mysql亿级大表重构方案介绍

发布时间：2020-08-11 13:23:32 作者：caoyutingtjpu
来源：ITPUB博客阅读：318

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,需要加函数索引	<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

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秒

Step2.在从库使用mysqldump的--where参数导出

思路：

1）使用--where=" user%1024=0001"导出成按拆分规则命名的文件，该例子对应文件名为0001.sql，一共会产生1024个这样的文件。
2）然后根据导出的文件名用sed命令替换表名（sed是shell命令）

具体脚本如下：

mycat分表是一个逻辑库对应一个分表，所以需要创建1024个逻辑库对应1024个分表，考虑到机器IO，打算将1024个库分到4个实例（数据库均是单实例，如果有条件，可以分到更多的机器上，速度会快些）

需要修改的配置文件：server.xml、schema.xml、rule.xml及其对应的partition-hash-int.txt。具体修改请查看附件conf.zip

mysql亿级大表重构方案介绍 conf.zip

注意：

1)需要提前创建1024个逻辑库

2)这里是根据方案一提到的函数索引对应的虚拟列vis_user_id来分的，这样可以直接用mycat的枚举分库，如果不想用虚拟列，可以用mycat hash来划分，这个对于数值划分方式等同于user%1024，这个详情参考mycat权威指南

3)需要提前在四个逻辑库里创建好用于mycat访问的数据库用户

4)在创建完1024个逻辑库后，登入mycat，再创建favourite表，这样每个逻辑库都有该表

Step2.使用mysqldump备份文件

为了能快速导入mycat，故根据mycat分成4个实例规则（可以有误差，不一定要完全一样），导出4份不同数据，以便可以同时4份文件灌入mycat

使用mysqldump导出4个文件，以下备份同时进行，耗时20分钟：

mysqldump -u$USERNAME -p$PASSWORD -S $SOCKET --default-character-set=utf8mb4 -c --set-gtid-purged=OFF --skip-add-locks --skip-quick --no-create-db --log-error=/data/cyt0324.log --skip-add-drop-table kuaikan favourite --where=" mod(user_id,1024)<256 " > /data/favourite_256.sql
mysqldump …………………… --where=" mod(user_id,1024)>=256 and mod(user_id,1024) <512 " > /data/favourite_512.sql
mysqldump …………………… --where=" mod(user_id,1024)>=512 and mod(user_id,1024)< 768 " > /data/favourite_768.sql
mysqldump …………………… --where=" mod(user_id,1024) >=768" > /data/favourite_1024.sql

注意：

1)请在从库或业务不去访问的数据库上进行备份

2)上面设置的参数请根据实际情况调整，一定要加上-c --skip-add-locks参数，否则导入mycat会异常

Step3.将备份文件导入mycat

将步骤2导出的四个备份文件同时灌入mycat，整个耗费时间不足90分钟。

注意:

1)请将该操作在后台执行，可以用screen命令来实现。因为mycat是长连接，即使中途断掉后，后面的语句可能还会继续执行，以防出现这样情况，请放到后台执行

Step4.在mycat对应库初始化id

此处耗时30分钟

1）在1024分库上初始化id,去掉虚拟列，具体脚本如下

5.将处理后的文件导入目标库

此处耗时30分钟

将处理后的备份文件导入生产新库，并行导入，并行度最好不要超过3，因为dump导出后insert一次是3万左右，并行度太高，机器IO会hold不住，脚本如下：

点击(此处)折叠或打开

function instance()
{
log
echo "-----端口号为"$port"的mysql实例开始按表并发倒入:开始时间为"`date "+%Y-%m-%d %H:%M:%S"`
#调用执行函数
dumpAllTable
}
#将要备份的单表从大到小输出到日志里面
function log()
{
BACKUP_DIR="/data/backup/"
#过滤掉MySQL自带的DB
if [ -e ${BACKUP_DIR}/cyt.log ];
then rm -rf ${BACKUP_DIR}/cyt.log;
fi;
for filename in `du -sk ${BACKUP_DIR}/*.sql | sort -nr |awk '{print $2}'`
do
a="kk_favourite"
echo "mysql -u${DB_USER} -p${DB_PASSWORD} --socket=${socket} --host=${host} -A ${a} --tee=/data/pat.log -e \"source ${filename}\"">>$BACKUP_DIR/cyt.log;
done
}
#调用函数log，查看log日志调用并发函数实现多线程备份
function dumpAllTable()
{
local schemaFile="${BACKUP_DIR}/cyt.log"
#最大的表先备份（因多进程并发，最短完成时间依赖于最大表的完成）
allTable=`cat $schemaFile | wc -l`
i_import=0
declare -a array_cmds
i_array=0
while read file; do
i_import=`expr $i + 1`
array_cmds[i_array]="${file}"
i_array=`expr ${i_array} + 1`
done < ${BACKUP_DIR}/cyt.log
execConcurrency "${threadsNum}" "${array_cmds[@]}"
delta_ret_val=`echo $?`
}
#并发函数
function execConcurrency()
{
#并发数据量
local thread=$1
#并发命令
local cmd=$2
#定义管道，用于控制并发线程
tmp_fifofile="/tmp/$$.fifo"
mkfifo $tmp_fifofile
#输入输出重定向到文件描述符6
exec 6<>$tmp_fifofile
rm -f $tmp_fifofile
#向管道压入指定数据的空格
for ((i=0;i<$thread;i++)); do
echo
done >&6
#遍历命令列表
while [ "$cmd" ]; do
#从管道取出一个空格（如无空格则阻塞，达到控制并发的目的）
read -u6
#命令执行完后压回一个空格
{ eval $2;echo >&6; } & #> /dev/null 2>&1 &
shift
cmd=$2
done
#等待所有的后台子进程结束
wait
#关闭df6
exec 6>&-
}
#主函数
function main()
{
#获取本地IP地址
host=""
port=
DATE=`date +%F`
socket=""
DB_USER=''
#数据库用户对应的密码
DB_PASSWORD=''
#记录开始的时间
BEGIN=`date "+%Y-%m-%d %H:%M:%S"`
BEGIN_T=`date -d "$BEGIN" +%s`
echo '--------------开始按表并发备份:开始时间为 '$BEGIN
#设置并发备份的线程数
threadsNum=8
#调用instance函数
instance
echo '--------------backup all database successfully！！！结束时间:' `date "+%Y-%m-%d %H:%M:%S"`
}
main

【小节】

由于方案二速度比方案一节省至少2小时以上的时间，且导入目标库后无需其他处理，故生产环境迁移使用的方案二

mysql亿级大表重构方案介绍

【情况简述】

【背景说明】

【思路说明】

【方案说明】

【方式一:mysqldump】

Step1.在从库建立函数索引,耗时3Hour

Step2.在从库使用mysqldump的--where参数导出

Step2.使用mysqldump备份文件

Step3.将备份文件导入mycat

Step4.在mycat对应库初始化id

相关阅读