千万级单表数据水平拆分实战

客官°小女子只卖身不卖艺 2022-01-26 07:49 177阅读 0赞

花了几天时间,做了一次生产数据迁移,记录下来

概述:

大背景:单表数据太大,导致查询特别慢,经讨论,要进行水平拆分

数据库:Mysql;mysql版本:测试环境5.65;生产环境5.7.22(这两个版本经多次测试,迁移数据速度几乎一样)

表:单表数据1300万,导出的文件有4.3G,主键自增;引擎:InnoDB;有三个索引(索引类型:Normal,索引方法:BTREE);有两个外键约束

目的:根据表中一个字段的值将该表数据分散到19个表中(迁移后老表废弃,用n个新表,代码已经做了修改)

要求:10分钟分散完毕;不能修改引擎;不能删除索引

具体过程:

经过多次尝试,发现一次性迁移数据是不能满足10分钟迁移完的,就转换一下思路,先迁移绝大多数数据,然后部署项目;然后再迁移剩余极少数据(在用户最不活跃的时间段迁移,我选择凌晨三点)

1.删除外键

删除所有的外键

2.迁移数据

经过不断尝试,发现每次插入50万条速率是最快的

2.1迁移绝大多数数据,具体limit后面start和end需要根据实际数据条数来确定,略大于实际数据即可(若数据产生特别迅速,适当改大)

\# 分散到表1

insert into 新表1(select \* from 老表 old where old.type\_id=1 and 主键约束条件1 limit 0,500000);

insert into 新表1(select \* from 老表 old where old.type\_id=1 and 主键约束条件1 limit 500000,500000);

...

nsert into 新表1(select \* from 老表 old where old.type\_id=1 and 主键约束条件1 limit 5000000,500000);

\# 分散到表2

insert into 新表2(select \* from 老表 old where old.type\_id=2 and 主键约束条件1 limit 0,500000);

insert into 新表1(select \* from 老表 old where old.type\_id=2 and 主键约束条件1 limit 500000,500000);

...

\# 分散到表n

insert into 新表n(select \* from 老表 old where old.type\_id=n and 主键约束条件1 limit 0,500000);

执行完需要40分钟左右

2.2.部署迁移后的应用

2.3.迁移少量数据

\# 分散到表1

insert into 新表1(select \* from 老表 old where old.type\_id=1 and 主键约束条件2 limit 0,500000);

insert into 新表1(select \* from 老表 old where old.type\_id=1 and 主键约束条件2 limit 500000,500000);

...

nsert into 新表1(select \* from 老表 old where old.type\_id=1 and 主键约束条件2 limit 5000000,500000);

\# 分散到表2

insert into 新表2(select \* from 老表 old where old.type\_id=2 and 主键约束条件2 limit 0,500000);

insert into 新表1(select \* from 老表 old where old.type\_id=2 and 主键约束条件2 limit 500000,500000);

...

\# 分散到表n

insert into 新表n(select \* from 老表 old where old.type\_id=n and 主键约束条件2 limit 0,500000);

执行完需要4分钟左右

3.校验数据是否完整,不完整的手动迁移

4.完成

ps1:如果允许修改引擎,将引擎修改为MyISAM可以在十分钟三十秒完成数据分散,但是修改引擎后再次修改引擎非常耗时(因为测试环境配置太差,这个时间没测出来,每次修改都是卡死,使用的是navicat客户端,估计是10分钟+,生产数据又不敢随便动,怕万一把数据库搞挂,无权操作数据库所在服务器)

ps2:不修改引擎,直接将全部索引干掉,也是十分钟三十秒左右迁移完全部数据,但是重建索引老卡死,也没采用这种方案,数据库服务器性能强悍的话可以试试这种方式,之前就这样干过

发表评论取消回复

表情：

评论列表（有 0 条评论，177人围观）

还没有评论，来说两句吧...

相关阅读

相关利用一致性哈希水平拆分MySql单表

利用一致性哈希水平拆分MySql单表 Sharding（切片）不是一门新技术，而是一个相对简朴的软件理念，就是当我们的数据库单机无法承受高强度的i/o时，我们就考虑利用

阳光穿透心脏的1/2处/ 2024年02月17日 22:22/ 0 赞/ 5 阅读

相关千万级数据查询

\---千万级数据查询 if (object\_id(N'syspr\_TablePaginationSearch',N'P') is not null) drop p

港控/mmm°/ 2022年09月26日 14:00/ 0 赞/ 209 阅读

相关 mysql百万级甚至千万级数据怎么进行快速分页

mysql我们分页都使用的是limit,但是limit有些不足之处比如我现在有一张表 ![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_s

古城微笑少年丶/ 2022年09月09日 02:24/ 0 赞/ 218 阅读

相关千万数据的分库分表(一)

千万数据的分库分表(一) 原创 2017年05月03日 11:41:36 标签： [数据库][Link 1] / [数据][Link 2] /

系统管理员/ 2022年05月29日 06:28/ 0 赞/ 119 阅读

相关千万数据的分库分表方案

> 这是项目中有一定量级的数据或用户，都会遇到的一个问题，故记录一下 > > 自己的开源项目集合，[见这里][Link 1] 单表数据量达到1000W以后，就要拆了背

今天药忘吃喽~/ 2022年05月19日 09:41/ 0 赞/ 147 阅读

相关千万级数据处理

千万级数据处理前言近日一次版本上线时，涉及到千万级数据处理，因一时疏漏造成Oracle数据库回滚段爆掉，造成版本重复发布。系统日常运行中，产

我会带着你远行/ 2022年05月14日 14:57/ 0 赞/ 190 阅读

相关千万数据的mysql分库分表

> 单表数据量达到1000W以后，就要拆了. 背景情况用户表达到了几千万级别，在做很多操作都比较吃力,.所以，考虑对其进行分表. 常用的切分方案数据的切分（

拼搏现实的明天。/ 2022年02月03日 14:29/ 0 赞/ 179 阅读

相关千万级单表数据水平拆分实战

花了几天时间,做了一次生产数据迁移,记录下来概述: 大背景:单表数据太大,导致查询特别慢,经讨论,要进行水平拆分数据库:Mysql;mysql版本:测试环境5.65;

客官°小女子只卖身不卖艺/ 2022年01月26日 07:49/ 0 赞/ 178 阅读

相关水平拆分

水平拆分概述水平分库示例水平拆分的优点水平拆分的缺点分片原则概述水平拆分指的是通过某种策略将数据分片存储，包含库内分表

你的名字/ 2021年11月09日 03:58/ 0 赞/ 317 阅读

相关表的垂直拆分和水平拆分

[https://www.kancloud.cn/thinkphp/mysql-design-optimalize/39326][https_www.kancloud.cn_t

迈不过友情╰/ 2021年06月10日 20:42/ 0 赞/ 452 阅读