南通个人网站制作,中英文的网站设计,外国排版网站,建设部资质网站查询点击上方 web项目开发#xff0c;选择 设为星标优质文章#xff0c;及时送达--Insert into select请慎用。这天xxx接到一个需求#xff0c;需要将表A的数据迁移到表B中去做一个备份。本想通过程序先查询查出来然后批量插入。但xxx觉得这样有点慢#xff0c;需要耗费大量的网… 点击上方 web项目开发选择 设为星标优质文章及时送达--Insert into select请慎用。这天xxx接到一个需求需要将表A的数据迁移到表B中去做一个备份。本想通过程序先查询查出来然后批量插入。但xxx觉得这样有点慢需要耗费大量的网络I/O决定采取别的方法进行实现。通过在Baidu的海洋里遨游她发现了可以使用insert into select实现这样就可以避免使用网络I/O直接使用SQL依靠数据库I/O完成这样简直不要太棒了。然后她就被开除了。事故发生的经过。由于数据数据库中order_today数据量过大当时好像有700W了并且每天在以30W的速度增加。所以上司命令xxx将order_today内的部分数据迁移到order_record中并将order_today中的数据删除。这样来降低order_today表中的数据量。由于考虑到会占用数据库I/O为了不影响业务计划是9:00以后开始迁移但是xxx在8:00的时候尝试迁移了少部分数据(1000条)觉得没啥问题就开始考虑大批量迁移。在迁移的过程中应急群是先反应有小部分用户出现支付失败随后反应大批用户出现支付失败的情况以及初始化订单失败的情况同时腾讯也开始报警。然后xxx就慌了立即停止了迁移。本以为停止迁移就就可以恢复了但是并没有。后面发生的你们可以脑补一下。# 事故还原在本地建立一个精简版的数据库并生成了100w的数据。模拟线上发生的情况。# 建立表结构订单表CREATE TABLE order_today (id varchar(32) NOT NULL COMMENT 主键,merchant_id varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 商户编号,amount decimal(15,2) NOT NULL COMMENT 订单金额,pay_success_time datetime NOT NULL COMMENT 支付成功时间,order_status varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 支付状态 S支付成功、F订单支付失败,remark varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 备注,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 修改时间 -- 修改时自动更新, PRIMARY KEY (id) USING BTREE,KEY idx_merchant_id (merchant_id) USING BTREE COMMENT 商户编号) ENGINEInnoDB DEFAULT CHARSETutf8;订单记录表CREATE TABLE order_record like order_today;今日订单表数据# 模拟迁移把8号之前的数据都迁移到order_record表中去。INSERT INTO order_record SELECT * FROM order_today WHERE pay_success_time 2020-03-08 00:00:00;在navicat中运行迁移的sql,同时开另个一个窗口插入数据模拟下单。从上面可以发现一开始能正常插入但是后面突然就卡住了并且耗费了23s才成功然后才能继续插入。这个时候已经迁移成功了所以能正常插入了。# 出现的原因在默认的事务隔离级别下insert into order_record select * from order_today 加锁规则是order_record表锁order_today逐步锁(扫描一个锁一个)。分析执行过程。通过观察迁移sql的执行情况你会发现order_today是全表扫描也就意味着在执行insert into select from 语句时mysql会从上到下扫描order_today内的记录并且加锁这样一来不就和直接锁表是一样了。这也就可以解释为什么一开始只有少量用户出现支付失败后续大量用户出现支付失败初始化订单失败等情况因为一开始只锁定了少部分数据没有被锁定的数据还是可以正常被修改为正常状态。由于锁定的数据越来越多就导致出现了大量支付失败。最后全部锁住导致无法插入订单而出现初始化订单失败。# 解决方案由于查询条件会导致order_today全表扫描什么能避免全表扫描呢很简单嘛给pay_success_time字段添加一个idx_pay_suc_time索引就可以了由于走索引查询就不会出现扫描全表的情况而锁表了只会锁定符合条件的记录。最终的sqlINSERT INTO order_record SELECT * FROM order_today FORCE INDEX (idx_pay_suc_time)WHERE pay_success_time 2020-03-08 00:00:00;执行过程# 总结使用insert into tablA select * from tableB语句时一定要确保tableB后面的whereorder或者其他条件都需要有对应的索引来避免出现tableB全部记录被锁定的情况。# 参考https://blog.csdn.net/asdfsadfasdfsa/article/details/83030011作者不一样的科技宅来源juejin.im/post/5e670f0151882549274a65ef--完--推荐案例SpringbootVue前后端分离实现Excle文件导入并在前端页面回显功能SpringbootVue实现从数据库中获取数据生成树状图在前端页面展示功能SpringbootVue实现从数据库中获取数据生成饼状图并在前端页面展示功能SpringbootVue实现批量文件上传(pdf、word、excel)并支持在线预览功能SpringbootVue实现滑动验证成功后登录功能SpringbootVue实现从数据库中获取数据生成折线图并在前端页面展示功能SpringbootVue实现网页内容生成图片功能SpringbootVue实现信息批量修改功能查看全部案例...温暖提示为了方便大家更好的学习本公众号经常分享一些完整的单个功能案例代码给大家去练习如果本公众号没有你要学习的功能案例你可以联系小编(微信xxf960513)提供你的小需求给我我安排我们这边的开发团队免费帮你完成你的案例。注意只能提单个功能的需求不能要求功能太多比如要求用什么技术有几个页面页面要求怎么样请长按识别二维码想学习更多的java功能案例请关注Java项目开发如果你觉得这个案例以及我们的分享思路不错对你有帮助请分享给身边更多需要学习的朋友。别忘了《留言点在看》给作者一个鼓励哦
