-
2021-04-25 00:14:11
在MYSQL分库分表中我们一般是基于数据量比较大的时间对mysql数据库一种优化的做法,下面我简单的介绍一下mysql分表与分库的简单做法。
1、分库分表
很明显,一个主表(也就是很重要的表,例如用户表)无限制的增长势必严重影响性能,分库与分表是一个很不错的解决途径,也就是性能优化途径,现在的案例是我们有一个1000多万条记录的用户表members,查询起来非常之慢,同事的做法是将其散列到100个表中,分别从members0到members99,然后根据mid分发记录到这些表中,牛逼的代码大概是这样子:
代码如下 复制代码
for($i=0;$i< 100; $i++ ){
//echo "CREATE TABLE db2.members{$i} LIKE db1.members
";echo "INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}
";}
?>
2、不停机修改mysql表结构
同样还是members表,前期设计的表结构不尽合理,随着数据库不断运行,其冗余数据也是增长巨大,同事使用了下面的方法来处理:
先创建一个临时表:
代码如下 复制代码
/*创建临时表*/
CREATE TABLE members_tmp LIKE members
然后修改members_tmp的表结构为新结构,接着使用上面那个for循环来导出数据,因为1000万的数据一次性导出是不对的,mid是主键,一个区间一个区间的导,基本是一次导出5万条吧,这里略去了
接着重命名将新表替换上去:
代码如下 复制代码
/*这是个颇为经典的语句哈*/
RENAME TABLE members TO members_bak,members_tmp TO members;
就是这样,基本可以做到无损失,无需停机更新表结构,但实际上RENAME期间表是被锁死的,所以选择在线少的时候操作是一个技巧。经过这个操作,使得原先8G多的表,一下子变成了2G多
另外还讲到了mysql中float字段类型的时候出现的诡异现象,就是在pma中看到的数字根本不能作为条件来查询.感谢zj同学的新鲜分享。
更多相关内容 -
如何不停机分库分表迁移?
2018-04-18 00:00:00本文转载自公众号 匠心零度需求...随着业务的发展,单表容量超过千万甚至达到亿级别以上,这时候就需要考虑分库分表这个问题了,而不停机分库分表迁移,这应该是分库分表最基本的需求,毕竟互联网项目不可能挂个广告牌点击上方“程序员小灰”,选择“置顶公众号”
有趣有内涵的文章第一时间送达!
本文转载自公众号 匠心零度
需求说明
类似订单表,用户表这种未来规模上亿甚至上十亿百亿的海量数据表,在项目初期为了快速上线,一般只是单表设计,不需要考虑分库分表。随着业务的发展,单表容量超过千万甚至达到亿级别以上,这时候就需要考虑分库分表这个问题了,而不停机分库分表迁移,这应该是分库分表最基本的需求,毕竟互联网项目不可能挂个广告牌"今晚10:00~次日10:00系统停机维护",这得多low呀,以后跳槽面试,你跟面试官说这个迁移方案,面试官怎么想呀?
借鉴codis
笔者正好曾经碰到过这个问题,并借鉴了codis一些思想实现了不停机分库分表迁移方案;codis不是这篇文章的重点,这里只提及借鉴codis的地方--rebalance:
当迁移过程中发生数据访问时,Proxy会发送“SLOTSMGRTTAGSLOT”迁移命令给Redis,强制将客户端要访问的Key立刻迁移,然后再处理客户端的请求。( SLOTSMGRTTAGSLOT 是codis基于redis定制的)
分库分表
明白这个方案后,了解不停机分库分表迁移就比较容易了,接下来详细介绍笔者当初对
installed_app
表的实施方案;即用户已安装的APP信息表;1. 确定sharding column
确定
sharding column
绝对是分库分表最最最重要的环节,没有之一。sharding column直接决定整个分库分表方案最终是否能成功落地;一个合适的sharding column的选取,基本上能让与这个表相关的绝大部分流量接口都能通过这个sharding column访问分库分表后的单表,而不需要跨库跨表,最常见的sharding column就是user_id
,笔记这里选取的也是user_id
;2. 分库分表方案
根据自身的业务选取最合适的sharding column后,就要确定分库分表方案了。笔者采用
主动迁移
与被动迁移
相结合的方案:主动迁移就是一个独立程序,遍历需要分库分表的
installed_app
表,将数据迁移到分库分表后的目标表中。被动迁移就是与
installed_app
表相关的业务代码自身将数据迁移到分库分表后对应的表中。
接下来详细介绍这两个方案;
2.1 主动迁移
主动迁移就是一个独立的外挂迁移程序,其作用是遍历需要分库分表的
installed_app
表,将这里的数据复制到分库分表后的目标表中,由于主动迁移
和被动迁移
会一起运行,所以需要处理主动迁移和被动迁移碰撞的问题,笔者的主动迁移
伪代码如下:public void migrate(){
// 查询出当前表的最大ID, 用于判断是否迁移完成
long maxId = execute("select max(id) from installed_app");
long tempMinId = 0L;
long stepSize = 1000;
long tempMaxId = 0L;
do{
try {
tempMaxId = tempMinId + stepSize;
// 根据InnoDB索引特性, where id>=? and id<?这种SQL性能最高
String scanSql = "select * from installed_app where id>=#{tempMinId} and id<#{tempMaxId}";
List<InstalledApp> installedApps = executeSql(scanSql);
Iterator<InstalledApp> iterator = installedApps.iterator();
while (iterator.hasNext()) {
InstalledApp installedApp = iterator.next();
// help GC
iterator.remove();
long userId = installedApp.getUserId();
String status = executeRedis("get MigrateStatus:${userId}");
if ("COMPLETED".equals(status)) {
// migration finish, nothing to do
continue;
}
if ("MIGRATING".equals(status)) {
// "被动迁移" migrating, nothing to do
continue;
}
// 迁移前先获取锁: set MigrateStatus:18 MIGRATING ex 3600 nx
String result = executeRedis("set MigrateStatus:${userId} MIGRATING ex 86400 nx");
if ("OK".equals(result)) {
// 成功获取锁后, 先将这个用户所有已安装的app查询出来[即迁移过程以用户ID维度进行迁移]
String sql = "select * from installed_app where user_id=#{user_id}";
List<InstalledApp> userInstalledApps = executeSql(sql);
// 将这个用户所有已安装的app迁移到分库分表后的表中(有user_id就能得到分库分表后的具体的表)
shardingInsertSql(userInstalledApps);
// 迁移完成后, 修改缓存状态
executeRedis("setex MigrateStatus:${userId} 864000 COMPLETED");
} else {
// 如果没有获取到锁, 说明被动迁移已经拿到了锁, 那么迁移交给被动迁移即可[这种概率很低]
// 也可以加强这里的逻辑, "被动迁移"过程不可能持续很长时间, 可以尝试循环几次获取状态判断是否迁移完
logger.info("Migration conflict. userId = {}", userId);
}
}
if (tempMaxId >= maxId) {
// 更新max(id),最终确认是否遍历完成
maxId = execute("select max(id) from installed_app");
}
logger.info("Migration process id = {}", tempMaxId);
}catch (Throwable e){
// 如果执行过程中有任何异常(这种异常只可能是redis和mysql抛出来的), 那么退出, 修复问题后再迁移
// 并且将tempMinId的值置为logger.info("Migration process id="+tempMaxId);日志最后一次记录的id, 防止重复迁移
System.exit(0);
}
tempMinId += stepSize;
}while (tempMaxId < maxId);
}这里有几点需要注意:
第一步查询出max(id)是为了尽量减少max(id)的查询次数,假如第一次查询max(id)为10000000,那么直到遍历的id到10000000以前,都不需要再次查询max(id);
根据
id>=? and id<?
遍历,而不要根据id>=? limit n
或者limit m, n
进行遍历,因为limit性能一般,且会随着遍历越往后,性能越差。而id>=? and id<?
这种遍历方式即使会有一些踩空,也没有任何影响,且整个性能曲线非常平顺,不会有任何抖动;迁移程序毕竟是辅助程序,不能对业务程序有过多的影响;根据id区间范围查询出来的
List<InstalledApp>
要转换为Iterator<InstalledApp>
,每迭代处理完一个userId,要remove掉,否则可能导致GC异常,甚至OOM;
2.2 被动迁移
被动迁移就是在正常与
installed_app
表相关的业务逻辑前插入了迁移逻辑,以新增用户已安装APP为例,其伪代码如下:// 被动迁移方法是公用逻辑,所以与`installed_app`表相关的业务逻辑前都需要调用这个方法;
public void migratePassive(long userId)throws Exception{
String status = executeRedis("get MigrateStatus:${userId}");
if ("COMPLETED".equals(status)) {
// 该用户数据已经迁移完成, nothing to do
logger.info("user's installed app migration completed. user_id = {}", userId);
}else if ("MIGRATING".equals(status)) {
// "被动迁移" migrating, 等待直到迁移完成; 为了防止死循环, 可以增加最大等待时间逻辑
do{
Thread.sleep(10);
status = executeRedis("get MigrateStatus:${userId}");
}while ("COMPLETED".equals(status));
}else {
// 准备迁移
String result = executeRedis("set MigrateStatus:${userId} MIGRATING ex 86400 nx");
if ("OK".equals(result)) {
// 成功获取锁后, 先将这个用户所有已安装的app查询出来[即迁移过程以用户ID维度进行迁移]
String sql = "select * from installed_app where user_id=#{user_id}";
List<InstalledApp> userInstalledApps = executeSql(sql);
// 将这个用户所有已安装的app迁移到分库分表后的表中(有user_id就能得到分库分表后的具体的表)
shardingInsertSql(userInstalledApps);
// 迁移完成后, 修改缓存状态
executeRedis("setex MigrateStatus:${userId} 864000 COMPLETED");
}else {
// 如果没有获取到锁, 应该是其他地方先获取到了锁并正在迁移, 可以尝试等待, 直到迁移完成
}
}
}
// 与`installed_app`表相关的业务--新增用户已安装的APP
public void addInstalledApp(InstalledApp installedApp) throws Exception{
// 先尝试被动迁移
migratePassive(installedApp.getUserId());
// 将用户已安装app信息(installedApp)插入到分库分表后的目标表中
shardingInsertSql(installedApp);
}无论是CRUD中哪种操作,先根据缓存中
MigrateStatus:${userId}
的值进行判断:如果值为COMPLETED,表示已经迁移完成,那么将请求转移到分库分表后的表中进行处理即可;
如果值为MIGRATING,表示正在迁移中,可以循环等待直到值为COMPLETED即迁移完成后,再将请求转移到分库分表后的表中进行处理处理;
否则值为空,那么尝试获取锁再进行数据迁移。迁移完成后,将缓存值更新为COMPLETED,最后再将请求转移到分库分表后的表中进行处理处理;
3.方案完善
当所有数据迁移完成后,CRUD操作还是会先根据缓存中
MigrateStatus:${userId}
的值进行判断,数据迁移完成后这一步已经是多余的。可以加个总开关,当所有数据迁移完成后,将这个开关的值通过类似TOPIC的方式发送,所有服务接收到TOPIC后将开关local cache化。那么接下来服务的CRUD都不需要先根据缓存中MigrateStatus:${userId}
的值进行判断;4.遗留工作
迁移完成后,将
主动迁移
程序下线,并将被动迁移
程序中对migratePassive()
的调用全部去掉,并可以集成一些第三方分库分表中间件,例如sharding-jdbc
,可以参考sharding-jdbc集成实战回顾总结
回顾这个方案,最大的缺点就是如果碰到sharding column(例如userId)的总记录数比较多,且主动迁移正在进行中,被动迁移与主动迁移碰撞,那么被动迁移可能需要等待较长时间。
不过根据DB性能,一般批量插入1000条数据都是10ms级别,并且同一sharding column的记录分库分表后只属于一张表,不涉及跨表。所以,只要在迁移前先通过sql统计待迁移表中没有这类异常sharding column即可放心迁移;
笔者当初迁移
installed_app
表时,用户最多也只拥有不超过200个APP,所以不需要过多考虑碰撞带来的性能问题;没有万能的方案,但是有适合自己的方案;如果有那种上万条记录的sharding column,可以把这些sharding column先缓存起来,迁移程序在夜间上线,优先迁移这些缓存的sharding column的数据,就可以尽可能的降低迁移程序对这些用户的体验。当然你也可以使用你想出来的更好的方案。
—————END—————
喜欢本文的朋友们,欢迎长按下图关注订阅号程序员小灰,收看更多精彩内容
-
如何实现不停机分库分表迁移
2019-06-03 19:59:32随着业务的发展,单表容量超过千万甚至达到亿级别以上,这时候就需要考虑分库分表这个问题了,而不停机分库分表迁移,这应该是分库分表最基本的需求,毕竟互联网项目不可能挂个广告牌"今晚10:00~次日10:00系统停机...业务场景
类似订单表,用户表这种未来规模上亿甚至上十亿百亿的海量数据表,在项目初期为了快速上线,一般只是单表设计,不需要考虑分库分表。随着业务的发展,单表容量超过千万甚至达到亿级别以上,这时候就需要考虑分库分表这个问题了,而不停机分库分表迁移,这应该是分库分表最基本的需求,毕竟互联网项目不可能挂个广告牌"今晚10:00~次日10:00系统停机维护",这得多low呀,所以需要不停机数据迁移啊。
借鉴codis
笔者正好曾经碰到过这个问题,并借鉴了codis一些思想实现了不停机分库分表迁移方案;codis不是这篇文章的重点,这里只提及借鉴codis的地方--rebalance:当迁移过程中发生数据访问时,Proxy会发送“SLOTSMGRTTAGSLOT”迁移命令给Redis,强制将客户端要访问的Key立刻迁移,然后再处理客户端的请求。( SLOTSMGRTTAGSLOT 是codis基于redis定制的)
分库分表
明白这个方案后,了解不停机分库分表迁移就比较容易了,接下来详细介绍笔者当初对
installed_app
表的实施方案;即用户已安装的APP信息表;也可以类似用户订单表的业务场景;1. 确定sharding column
确定
sharding column
绝对是分库分表最最最重要的环节,没有之一。sharding column直接决定整个分库分表方案最终是否能成功落地;一个合适的sharding column的选取,基本上能让与这个表相关的绝大部分流量接口都能通过这个sharding column访问分库分表后的单表,而不需要跨库跨表,最常见的sharding column就是user_id
,笔记这里选取的也是user_id
;2. 分库分表方案
根据自身的业务选取最合适的sharding column后,就要确定分库分表方案了。笔者采用
主动迁移
与被动迁移
相结合的方案:-
主动迁移就是一个独立程序,遍历需要分库分表的
installed_app
表,将数据迁移到分库分表后的目标表中。 -
被动迁移就是与
installed_app
表相关的业务代码自身将数据迁移到分库分表后对应的表中。
接下来详细介绍这两个方案;
2.1 主动迁移
主动迁移就是一个独立的外挂迁移程序,其作用是遍历需要分库分表的
installed_app
表,将这里的数据复制到分库分表后的目标表中,由于主动迁移
和被动迁移
会一起运行,所以需要处理主动迁移和被动迁移碰撞的问题,笔者的主动迁移
伪代码如下:public void migrate(){ // 查询出当前表的最大ID, 用于判断是否迁移完成 long maxId = execute("select max(id) from installed_app"); long tempMinId = 0L; long stepSize = 1000; long tempMaxId = 0L; do{ try { tempMaxId = tempMinId + stepSize; // 根据InnoDB索引特性, where id>=? and id<?这种SQL性能最高 String scanSql = "select * from installed_app where id>=#{tempMinId} and id<#{tempMaxId}"; List<InstalledApp> installedApps = executeSql(scanSql); Iterator<InstalledApp> iterator = installedApps.iterator(); while (iterator.hasNext()) { InstalledApp installedApp = iterator.next(); // help GC iterator.remove(); long userId = installedApp.getUserId(); String status = executeRedis("get MigrateStatus:${userId}"); if ("COMPLETED".equals(status)) { // migration finish, nothing to do continue; } if ("MIGRATING".equals(status)) { // "被动迁移" migrating, nothing to do continue; } // 迁移前先获取锁: set MigrateStatus:18 MIGRATING ex 3600 nx String result = executeRedis("set MigrateStatus:${userId} MIGRATING ex 86400 nx"); if ("OK".equals(result)) { // 成功获取锁后, 先将这个用户所有已安装的app查询出来[即迁移过程以用户ID维度进行迁移] String sql = "select * from installed_app where user_id=#{user_id}"; List<InstalledApp> userInstalledApps = executeSql(sql); // 将这个用户所有已安装的app迁移到分库分表后的表中(有user_id就能得到分库分表后的具体的表) shardingInsertSql(userInstalledApps); // 迁移完成后, 修改缓存状态 executeRedis("setex MigrateStatus:${userId} 864000 COMPLETED"); } else { // 如果没有获取到锁, 说明被动迁移已经拿到了锁, 那么迁移交给被动迁移即可[这种概率很低] // 也可以加强这里的逻辑, "被动迁移"过程不可能持续很长时间, 可以尝试循环几次获取状态判断是否迁移完 logger.info("Migration conflict. userId = {}", userId); } } if (tempMaxId >= maxId) { // 更新max(id),最终确认是否遍历完成 maxId = execute("select max(id) from installed_app"); } logger.info("Migration process id = {}", tempMaxId); }catch (Throwable e){ // 如果执行过程中有任何异常(这种异常只可能是redis和mysql抛出来的), 那么退出, 修复问题后再迁移 // 并且将tempMinId的值置为logger.info("Migration process id="+tempMaxId);日志最后一次记录的id, 防止重复迁移 System.exit(0); } tempMinId += stepSize; }while (tempMaxId < maxId); }
这里有几点需要注意:
-
第一步查询出max(id)是为了尽量减少max(id)的查询次数,假如第一次查询max(id)为10000000,那么直到遍历的id到10000000以前,都不需要再次查询max(id);
-
根据
id>=? and id<?
遍历,而不要根据id>=? limit n
或者limit m, n
进行遍历,因为limit性能一般,且会随着遍历越往后,性能越差。而id>=? and id<?
这种遍历方式即使会有一些踩空,也没有任何影响,且整个性能曲线非常平顺,不会有任何抖动;迁移程序毕竟是辅助程序,不能对业务程序有过多的影响; -
根据id区间范围查询出来的
List<InstalledApp>
要转换为Iterator<InstalledApp>
,每迭代处理完一个userId,要remove掉,否则可能导致GC异常,甚至OOM;
2.2 被动迁移
被动迁移就是在正常与
installed_app
表相关的业务逻辑前插入了迁移逻辑,以新增用户已安装APP为例,其伪代码如下:// 被动迁移方法是公用逻辑,所以与`installed_app`表相关的业务逻辑前都需要调用这个方法; public void migratePassive(long userId)throws Exception{ String status = executeRedis("get MigrateStatus:${userId}"); if ("COMPLETED".equals(status)) { // 该用户数据已经迁移完成, nothing to do logger.info("user's installed app migration completed. user_id = {}", userId); }else if ("MIGRATING".equals(status)) { // "被动迁移" migrating, 等待直到迁移完成; 为了防止死循环, 可以增加最大等待时间逻辑 do{ Thread.sleep(10); status = executeRedis("get MigrateStatus:${userId}"); }while ("COMPLETED".equals(status)); }else { // 准备迁移 String result = executeRedis("set MigrateStatus:${userId} MIGRATING ex 86400 nx"); if ("OK".equals(result)) { // 成功获取锁后, 先将这个用户所有已安装的app查询出来[即迁移过程以用户ID维度进行迁移] String sql = "select * from installed_app where user_id=#{user_id}"; List<InstalledApp> userInstalledApps = executeSql(sql); // 将这个用户所有已安装的app迁移到分库分表后的表中(有user_id就能得到分库分表后的具体的表) shardingInsertSql(userInstalledApps); // 迁移完成后, 修改缓存状态 executeRedis("setex MigrateStatus:${userId} 864000 COMPLETED"); }else { // 如果没有获取到锁, 应该是其他地方先获取到了锁并正在迁移, 可以尝试等待, 直到迁移完成 } } } // 与`installed_app`表相关的业务--新增用户已安装的APP public void addInstalledApp(InstalledApp installedApp) throws Exception{ // 先尝试被动迁移 migratePassive(installedApp.getUserId()); // 将用户已安装app信息(installedApp)插入到分库分表后的目标表中 shardingInsertSql(installedApp); }
无论是CRUD中哪种操作,先根据缓存中
MigrateStatus:${userId}
的值进行判断:-
如果值为COMPLETED,表示已经迁移完成,那么将请求转移到分库分表后的表中进行处理即可;
-
如果值为MIGRATING,表示正在迁移中,可以循环等待直到值为COMPLETED即迁移完成后,再将请求转移到分库分表后的表中进行处理处理;
-
否则值为空,那么尝试获取锁再进行数据迁移。迁移完成后,将缓存值更新为COMPLETED,最后再将请求转移到分库分表后的表中进行处理处理;
3.方案完善
当所有数据迁移完成后,CRUD操作还是会先根据缓存中
MigrateStatus:${userId}
的值进行判断,数据迁移完成后这一步已经是多余的。可以加个总开关,当所有数据迁移完成后,将这个开关的值通过类似TOPIC的方式发送,所有服务接收到TOPIC后将开关local cache化。那么接下来服务的CRUD都不需要先根据缓存中MigrateStatus:${userId}
的值进行判断;4.遗留工作
迁移完成后,将
主动迁移
程序下线,并将被动迁移
程序中对migratePassive()
的调用全部去掉,并可以集成一些第三方分库分表中间件,例如sharding-jdbc
,可以参考sharding-jdbc集成实战回顾总结
回顾这个方案,最大的缺点就是如果碰到sharding column(例如userId)的总记录数比较多,且主动迁移正在进行中,被动迁移与主动迁移碰撞,那么被动迁移可能需要等待较长时间。
不过根据DB性能,一般批量插入1000条数据都是10ms级别,并且同一sharding column的记录分库分表后只属于一张表,不涉及跨表。所以,只要在迁移前先通过sql统计待迁移表中没有这类异常sharding column即可放心迁移;
笔者当初迁移
installed_app
表时,用户最多也只拥有不超过200个APP,所以不需要过多考虑碰撞带来的性能问题;没有万能的方案,但是有适合自己的方案;如果有那种上万条记录的sharding column,可以把这些sharding column先缓存起来,迁移程序在夜间上线,优先迁移这些缓存的sharding column的数据,就可以尽可能的降低迁移程序对这些用户的体验。当然你也可以使用你想出来的更好的方案。
-
-
不停机分库分表迁移
2018-03-26 14:50:17随着业务的发展,单表容量超过千万甚至达到亿级别以上,这时候就需要考虑分库分表这个问题了,而不停机分库分表迁移,这应该是分库分表最基本的需求,毕竟互联网项目不可能挂个广告牌”今晚10:00~次日10:00系统停机...需求说明
类似订单表,用户表这种未来规模上亿甚至上十亿百亿的海量数据表,在项目初期为了快速上线,一般只是单表设计,不需要考虑分库分表。随着业务的发展,单表容量超过千万甚至达到亿级别以上,这时候就需要考虑分库分表这个问题了,而不停机分库分表迁移,这应该是分库分表最基本的需求,毕竟互联网项目不可能挂个广告牌”今晚10:00~次日10:00系统停机维护”,这得多low呀,以后跳槽面试,你跟面试官说这个迁移方案,面试官怎么想呀?借鉴codis
笔者正好曾经碰到过这个问题,并借鉴了codis一些思想实现了不停机分库分表迁移方案;codis不是这篇文章的重点,这里只提及借鉴codis的地方–rebalance:当迁移过程中发生数据访问时,Proxy会发送“SLOTSMGRTTAGSLOT”迁移命令给Redis,强制将客户端要访问的Key立刻迁移,然后再处理客户端的请求。( SLOTSMGRTTAGSLOT 是codis基于redis定制的)
分库分表
明白这个方案后,了解不停机分库分表迁移就比较容易了,接下来详细介绍笔者当初对installed_app表的实施方案;即用户已安装的APP信息表;确定sharding column
确定sharding column绝对是分库分表最最最重要的环节,没有之一。sharding column直接决定整个分库分表方案最终是否能成功落地;一个合适的sharding column的选取,基本上能让与这个表相关的绝大部分流量接口都能通过这个sharding column访问分库分表后的单表,而不需要跨库跨表,最常见的sharding column就是user_id,笔记这里选取的也是user_id;分库分表方案
根据自身的业务选取最合适的sharding column后,就要确定分库分表方案了。笔者采用主动迁移与被动迁移相结合的方案:
主动迁移就是一个独立程序,遍历需要分库分表的installed_app表,将数据迁移到分库分表后的目标表中。
被动迁移就是与installed_app表相关的业务代码自身将数据迁移到分库分表后对应的表中。接下来详细介绍这两个方案;
2.1 主动迁移
主动迁移就是一个独立的外挂迁移程序,其作用是遍历需要分库分表的installed_app表,将这里的数据复制到分库分表后的目标表中,由于主动迁移和被动迁移会一起运行,所以需要处理主动迁移和被动迁移碰撞的问题,笔者的主动迁移伪代码如下:public void migrate(){ // 查询出当前表的最大ID, 用于判断是否迁移完成 long maxId = execute("select max(id) from installed_app"); long tempMinId = 0L; long stepSize = 1000; long tempMaxId = 0L; do{ try { tempMaxId = tempMinId + stepSize; // 根据InnoDB索引特性, where id>=? and id<?这种SQL性能最高 String scanSql = "select * from installed_app where id>=#{tempMinId} and id<#{tempMaxId}"; List<InstalledApp> installedApps = executeSql(scanSql); Iterator<InstalledApp> iterator = installedApps.iterator(); while (iterator.hasNext()) { InstalledApp installedApp = iterator.next(); // help GC iterator.remove(); long userId = installedApp.getUserId(); String status = executeRedis("get MigrateStatus:${userId}"); if ("COMPLETED".equals(status)) { // migration finish, nothing to do continue; } if ("MIGRATING".equals(status)) { // "被动迁移" migrating, nothing to do continue; } // 迁移前先获取锁: set MigrateStatus:18 MIGRATING ex 3600 nx String result = executeRedis("set MigrateStatus:${userId} MIGRATING ex 86400 nx"); if ("OK".equals(result)) { // 成功获取锁后, 先将这个用户所有已安装的app查询出来[即迁移过程以用户ID维度进行迁移] String sql = "select * from installed_app where user_id=#{user_id}"; List<InstalledApp> userInstalledApps = executeSql(sql); // 将这个用户所有已安装的app迁移到分库分表后的表中(有user_id就能得到分库分表后的具体的表) shardingInsertSql(userInstalledApps); // 迁移完成后, 修改缓存状态 executeRedis("setex MigrateStatus:${userId} 864000 COMPLETED"); } else { // 如果没有获取到锁, 说明被动迁移已经拿到了锁, 那么迁移交给被动迁移即可[这种概率很低] // 也可以加强这里的逻辑, "被动迁移"过程不可能持续很长时间, 可以尝试循环几次获取状态判断是否迁移完 logger.info("Migration conflict. userId = {}", userId); } } if (tempMaxId >= maxId) { // 更新max(id),最终确认是否遍历完成 maxId = execute("select max(id) from installed_app"); } logger.info("Migration process id = {}", tempMaxId); }catch (Throwable e){ // 如果执行过程中有任何异常(这种异常只可能是redis和mysql抛出来的), 那么退出, 修复问题后再迁移 // 并且将tempMinId的值置为logger.info("Migration process id="+tempMaxId);日志最后一次记录的id, 防止重复迁移 System.exit(0); } tempMinId += stepSize; }while (tempMaxId < maxId); }
这里有几点需要注意:
第一步查询出max(id)是为了尽量减少max(id)的查询次数,假如第一次查询max(id)为10000000,那么直到遍历的id到10000000以前,都不需要再次查询max(id);
根据id>=? and id// 被动迁移方法是公用逻辑,所以与`installed_app`表相关的业务逻辑前都需要调用这个方法; public void migratePassive(long userId)throws Exception{ String status = executeRedis("get MigrateStatus:${userId}"); if ("COMPLETED".equals(status)) { // 该用户数据已经迁移完成, nothing to do logger.info("user's installed app migration completed. user_id = {}", userId); }else if ("MIGRATING".equals(status)) { // "被动迁移" migrating, 等待直到迁移完成; 为了防止死循环, 可以增加最大等待时间逻辑 do{ Thread.sleep(10); status = executeRedis("get MigrateStatus:${userId}"); }while ("COMPLETED".equals(status)); }else { // 准备迁移 String result = executeRedis("set MigrateStatus:${userId} MIGRATING ex 86400 nx"); if ("OK".equals(result)) { // 成功获取锁后, 先将这个用户所有已安装的app查询出来[即迁移过程以用户ID维度进行迁移] String sql = "select * from installed_app where user_id=#{user_id}"; List<InstalledApp> userInstalledApps = executeSql(sql); // 将这个用户所有已安装的app迁移到分库分表后的表中(有user_id就能得到分库分表后的具体的表) shardingInsertSql(userInstalledApps); // 迁移完成后, 修改缓存状态 executeRedis("setex MigrateStatus:${userId} 864000 COMPLETED"); }else { // 如果没有获取到锁, 应该是其他地方先获取到了锁并正在迁移, 可以尝试等待, 直到迁移完成 } } } // 与`installed_app`表相关的业务--新增用户已安装的APP public void addInstalledApp(InstalledApp installedApp) throws Exception{ // 先尝试被动迁移 migratePassive(installedApp.getUserId()); // 将用户已安装app信息(installedApp)插入到分库分表后的目标表中 shardingInsertSql(installedApp); }
无论是CRUD中哪种操作,先根据缓存中MigrateStatus:${userId}的值进行判断:
如果值为COMPLETED,表示已经迁移完成,那么将请求转移到分库分表后的表中进行处理即可;
如果值为MIGRATING,表示正在迁移中,可以循环等待直到值为COMPLETED即迁移完成后,再将请求转移到分库分表后的表中进行处理处理;
否则值为空,那么尝试获取锁再进行数据迁移。迁移完成后,将缓存值更新为COMPLETED,最后再将请求转移到分库分表后的表中进行处理处理;3.方案完善
当所有数据迁移完成后,CRUD操作还是会先根据缓存中MigrateStatus: userId的值进行判断,数据迁移完成后这一步已经是多余的。可以加个总开关,当所有数据迁移完成后,将这个开关的值通过类似TOPIC的方式发送,所有服务接收到TOPIC后将开关localcache化。那么接下来服务的CRUD都不需要先根据缓存中MigrateStatus: u s e r I d 的 值 进 行 判 断 , 数 据 迁 移 完 成 后 这 一 步 已 经 是 多 余 的 。 可 以 加 个 总 开 关 , 当 所 有 数 据 迁 移 完 成 后 , 将 这 个 开 关 的 值 通 过 类 似 T O P I C 的 方 式 发 送 , 所 有 服 务 接 收 到 T O P I C 后 将 开 关 l o c a l c a c h e 化 。 那 么 接 下 来 服 务 的 C R U D 都 不 需 要 先 根 据 缓 存 中 M i g r a t e S t a t u s : {userId}的值进行判断;4.遗留工作
迁移完成后,将主动迁移程序下线,并将被动迁移程序中对migratePassive()的调用全部去掉,并可以集成一些第三方分库分表中间件,例如sharding-jdbc,可以参考sharding-jdbc集成实战回顾总结
回顾这个方案,最大的缺点就是如果碰到sharding column(例如userId)的总记录数比较多,且主动迁移正在进行中,被动迁移与主动迁移碰撞,那么被动迁移可能需要等待较长时间。不过根据DB性能,一般批量插入1000条数据都是10ms级别,并且同一sharding column的记录分库分表后只属于一张表,不涉及跨表。所以,只要在迁移前先通过sql统计待迁移表中没有这类异常sharding column即可放心迁移;
笔者当初迁移installed_app表时,用户最多也只拥有不超过200个APP,所以不需要过多考虑碰撞带来的性能问题;没有万能的方案,但是有适合自己的方案;
如果有那种上万条记录的sharding column,可以把这些sharding column先缓存起来,迁移程序在夜间上线,优先迁移这些缓存的sharding column的数据,就可以尽可能的降低迁移程序对这些用户的体验。当然你也可以使用你想出来的更好的方案。
文章来源:https://mp.weixin.qq.com/s/uKGdK-1jP0q6xiJyczGwFw
相关博文推荐阅读:http://www.roncoo.com/article/index?title=%E6%95%B0%E6%8D%AE%E5%BA%93
-
48_你们当时是如何把系统不停机迁移到分库分表的?.zip
2020-11-13 23:22:54https://mp.csdn.net/console/uploadResources?spm=1011.2124.3001.4171 -
面试官:如何做到不停机分库分表迁移?
2019-05-08 17:44:56随着业务的发展,单表容量超过千万甚至达到亿级别以上,这时候就需要考虑分库分表这个问题了,而不停机分库分表迁移,这应该是分库分表最基本的需求,毕竟互联网项目不可能挂个广告牌"今晚10:00~次日10:00系统停机... -
分库分表后,如何不停机迁移数据?
2021-09-23 13:30:42前文我们讲了如何分库分表,现在假设我们已经做好了分库分表,把原来的单库单表的设计改造成了多库多表结构,那么如何进行数据迁移呢? 一般会有这几种方案: 停机迁移方案 双写迁移方案 停机迁移方案 ... -
MYSQL性能优化分享-分库分表,不停机修改mysql表结构
2021-04-25 00:15:13下午的分享,同事讲了下关于mysql性能优化方面几个心得,很有意义,贴出来1、分库分表很明显,一个主表(也就是很重要的表,例如用户表)无限制的增长势必严重影响性能,分库与分表是一个很不错的解决途径,也就是性能... -
Mysql如何不停机迁移到分库分表
2021-01-20 00:33:04开篇提问:假如现在有一个未分库分表的系统,未来要分库分表,如何设计才可以让系统从未分库分表动态切换到分库分表上?当我们已经明白为啥要分库分表了,也知道常用的分库分表中间件了,也设计好你们如何分库分表的... -
分布式数据库-分库分表01-ShardingJDBC
2021-12-27 11:13:441.MYSQL分库分表的原理 为什么要分库分表 一般的机器(4核16G),单库的MySQL并发(QPS+TPS)超过了2k,系统基本就完蛋了。最好是并发量控制在1k左右。这里就引出一个问题,为什么要分库分表? 1、高并发情况下,... -
oracle迁移到mysql分库分表方案之——ogg(goldengate)
2021-05-06 07:08:47之前文章主要介绍了oracle 迁移到mysql,主要是原表原结构迁移,但是实际运维中会发现,到mysql以后需要分库和分表的拆分操作,这个时候,用ogg来做,也是很强大好用的。主要结合ogg的2个参数参数1:filterUse a ... -
分库分表的中间件和迁移方案
2020-05-30 19:18:28分库分表 一、为什么要分库分表 单机mysql带来的问题: 1、mysql单机,扛不住并发 2、mysql单机磁盘容量快满 3、mysql单表数据量太大,sql执行越跑越慢 利用分库分表将单机拆分成多机,带来的好处。 1、可承受的并发... -
数据库分库分表面试突击二:如何把系统不停机迁移到分库分表的
2020-08-12 15:30:24你看看,你现在已经明白为啥要分库分表了,你也知道常用的分库分表中间件了,你也设计好你们如何分库分表的方案了(水平拆分、垂直拆分、分表),那问题来了,你接下来该怎么把你那个单库单表的系统给迁移到分库分表... -
如何把系统不停机迁移到分库分表的?
2019-09-15 21:34:33假设你已经选择了一个分库分表的数据库中间件,sharding-jdbc,mycat,都可以 (1)停机迁移方案 我先给你说一个最low的方案,就是很简单,大家伙儿凌晨12点开始运维,网站或者app挂个公告,说0点到早上6点进行运维... -
43、如何把系统不停机迁移到分库分表的?
2019-01-11 09:15:59现在有一个未分库分表的系统,未来要分库分表,如何设计才可以让系统从未分库分表动态切换到分库分表上? 2、面试官心里分析 你看看,你现在已经明白为啥要分库分表了,你也知道常用的分库分表中间件了,你也设计... -
不停机迁移+无感降级,日均百万订单系统的分库分表咋做更靠谱?
2022-01-27 16:16:34根据监控,我们的每秒最高订单量已经达到了2000笔(不包括秒杀,秒杀TPS已经上万了。秒杀我们有一套专门的解决方案,详见《秒杀系统设计~亿级用户》)。不过,直到此时,订单系统还是单库单表,幸好当时数据库... -
你们当时是如何把系统不停机迁移到分库分表的?
2020-01-21 08:50:42现在有一个未分库分表的系统,未来要分库分表,如何设计才可以让系统从未分库分表(也就是单库)动态切换到“分库分表”上? 2、面试官心里分析 你看看,你现在已经明白为啥要分库分表了,你也知道常用的分库分表... -
mysql- 如何对数据库进行分库分表,不允许停止服务
2022-02-07 11:46:39如何对数据库进行分库分表,不允许停止服务 第一阶段: 编写代理层和DAO层,代理层动态开关,决定写的是新表还是旧表,此时流量仍然是访问旧表 第二阶段: 开启双写,增量数据同时在旧表和新表进行新增和修改,... -
Mysql分库分表
2022-04-09 20:23:49当前互联网发展速度越来越快,很多应用的用户量也越来越多,很多大的互联网项目的用户量甚至破亿,日活跃用户也在几千万,用户的活动信息一般都记录到了数据库中,那么...本文简单讲解了一下Mysql中的分库分表方案。 -
分库分表及数据迁移
2022-03-23 20:31:551.何为分库? 分库就是将数据库中的数据分散到不同的数据库上。 下面这些操作都涉及到了分库: 你将数据库中的用户表和用户订单表分别放在两个不同的数据库。 由于用户表数据量太大,你对用户表进行了水平切分,... -
MySQL 分库分表的基本概念和常见问题
2021-09-04 10:59:21简单介绍了分库分表的概念以及相关问题。 -
SQLSERVER分库分表
2021-03-04 05:51:44单库单表单库单表是最常见的数据库设计,例如,有一张用户(user)表放在数据库db中,所有的用户都可以在db库中的user表中查到。单库多表随着用户数量的增加,user表的数据量会越来越大,当数据量达到一定程度的时候对... -
多图详解:如何不停服分库分表
2021-08-30 12:56:40分库分表确实可以解决单表数据量大这个问题,但是并非首选。因为分库分表至少引入了三个必须解决的突出问题。 第一是分库分表方案本身具有的复杂性。第二是本地事务失效问题,原本在同一个数据库中可以保证强一致性...