MySQL的insert into select 引发锁表

HelloCoder-HaC 2020-11-10 09:13:45
java Mysql InnoDB cos Next


上周五HaC我要上线,有一个脚本需要执行,执行前需要备份一个表。

运维大佬:“这个表的备份为什么要这么久,,??”

1秒过去了......2秒过去了......

期间运营反馈系统出现大量订单超时情况。

大佬找到我,问:“你怎么备份的?”

我:“insert into select * from 呀!”

大佬:“??你是不是不想混了?”

又是被大佬嫌弃的一天,为了不卷铺盖走人,我决定去学习一下表备份的常见方法。

MySQL一般我们在生产上备份数据通常会用到 这两种方法:

  1. INSERT INTO SELECT
  2. CREATE TABLE AS SELECT

注:本文仅针对MySQL innodb引擎,事务是可重复读RR,数据库版本为5.5

1.INSERT INTO SELECT

insert into Table2(field1,field2,...) select value1,value2,... from Table1

注意

(1)要求目标表Table2必须存在,并且字段field,field2...也必须存在

(2)注意Table2的主键约束,如果Table2有主键而且不为空,则 field1, field2...中必须包括主键

在执行语句的时候,MySQL是逐行加锁的(扫描一个锁一个),直至锁住所有符合条件的数据,执行完毕才释放锁。所以当业务在进行的时候,切忌使用这种方法。

在RR隔离级别下,还会加行锁和间隙锁

举个栗子吧:

CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;

执行

begin;
insert into t2(c,d) select c,d from t;

先不commit;这个语句对表 t 主键索引加了 (-∞,1] 这个 next-key lock

新开一个Navicat窗口,模拟新事务进入,此时执行下面这句sql就需要等待

insert into t values(-1,-1,-1);

锁住了

真就锁表了~无法写进去了,我终于知道为什么订单超时了。

背锅背锅。

如果实在要使用 INSERT INTO SELECT 这种方法,可以使用下面的方法进行优化:

  1. 加条件,强制走索引,不要全表扫描,例如
INSERT INTO Table2 SELECT
*
FROM
Table1 FORCE INDEX (create_time)
WHERE
update_time <= '2020-03-08 00:00:00';
  1. 加上limit 100,100 这种,限制数量

2. CREATE TABLE AS SELECT

create table as select 会创建一个不存在的表,也可以用来复制一个表。

1. create table t3 as select * from t where 1=2;
-- 创建一个表结构与t一模一样的表,只复制结构不复制数据;
2.create table t3 as select * from t ;
-- 创建一个表结构与t一模一样的表,复制结构同时也复制数据;(索引不会创建)
3.create table t3(`id`,`a`) as select `id`,`c` from t;
-- 创建一个表结构与t一模一样的表,复制结构同时也复制数据,但是指定新表的列名;

后面两种格式,如果后面跟上合适的查询条件,可以只复制符合条件的数据到新的表中。比如:

create table table1 as select * from table2 where columns1>=1;

针对大表多字段的表复制,考虑是否每一个字段都是必需的,如果不是必需的,可以自定义选择字段吗,这样复制的时间会大大提升。

CREATE table table1 as SELECT id FROM table2; -- 只复制id这一列

注意此建表过程全程锁表。语句执行完毕,才释放元数据锁

MDL全称为metadata lock,即元数据锁。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁,来保护表的元数据信息,用于解决或者保证DDL操作与DML操作之间的一致性。

注意:

  1. 新表不会自动创建创建和原表相同的索引。(即复制表的索引会消失)

3 .区别

  • 首先,最大的区别是二者属于不同类型的语句,INSERT INTO SELECT 是DML语句(数据操作语言,SQL中处理数据等操作统称为数据操纵语言),完成后需要提交才能生效,CREATE TABLE AS SELECT 是DDL语句(数据定义语言,用于定义和管理 SQL 数据库中的所有对象的语言 ),执行完直接生效,不提供回滚,效率比较高。

  • 其次,功能不同,INSERT INTO SELECT只是插入数据,必须先建表;CREATE TABLE AS SELECT 则建表和插入数据一块完成。

  • 当有大量数据的时候不推荐使用Insert into as,因为该语句的插入的效率很慢。

4.总结

以上对复制表来说,都不是很好的选择,分享几种平时常用的方法:

  1. 导出成excel,然后拼sql 成 insert into values(),(),()的形式。

  2. 定时任务,任务的逻辑是查询100条记录,然后多个线程分到几个任务执行,比如是个线程,每个线程10条记录,插入后,在查询新的100条记录处理。

  3. mysqldumb方法,例如

    mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
    
  4. 导出 CSV 文件

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

第3、4两种方法适合整个表导出。

​ 5. 业务少的情况(深夜什么的)下,可以使用 create table as select


知识又增加了。

福利:

Java学习资料

版权声明
本文为[HelloCoder-HaC]所创,转载请带上原文链接,感谢
https://my.oschina.net/HelloCoder/blog/4710397

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云