CREATE TABLE 表名 AS SELECT 语句 快速复制表但是锁表

2021-01-13 17:23:21

create table as select  能快速复制表,但是又个确定,它会锁表。哎

根据情况使用吧,小一点的表还是可以的

解决方案,只能类似这样 

create table new_table as select ... join ... group by ... limit 0;

insert into new_table as select ... join ... group by ...

参考地址 create table as select性能测试


MySQL的insert into select 引发锁表


Create table as select性能简介:


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

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.总结

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

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

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

mysqldumb方法,例如

导出 CSV 文件

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

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

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

知识又增加了。



  • 2018-12-04 15:30:01

    如何在Mac OS X上安装 Ruby运行环境

    ​ 对于新入门的开发者,如何安装 Ruby和Ruby Gems 的运行环境可能会是个问题,本页主要介绍如何用一条靠谱的路子快速安装 Ruby 开发环境。 此安装方法同样适用于产品环境!

  • 2018-12-04 15:31:15

    iOS--Pod install && Pod update

    许多人在最初接触CocoaPods时认为pod install只是在第一次为项目设置CocoaPods时使用,之后都应该使用pod update.看起来是这样,但也不是(But that's not the case at all.)。 这篇文章的目的就是教你啥时候用pod install,啥时候用pod update

  • 2018-12-04 15:33:19

    CocoaPods安装和使用教程

    当你开发iOS应用时,会经常使用到很多第三方开源类库,比如JSONKit,AFNetWorking等等。可能某个类库又用到其他类库,所以要使用它,必须得另外下载其他类库,而其他类库又用到其他类库,“子子孙孙无穷尽也”,这也许是比较特殊的情况。总之小编的意思就是,手动一个个去下载所需类库十分麻烦。另外一种常见情况是,你项目中用到的类库有更新,你必须得重新下载新版本,重新加入到项目中,十分麻烦。如果能有什么工具能解决这些恼人的问题,那将“善莫大焉”。所以,你需要 CocoaPods。

  • 2018-12-04 23:37:37

    pod install 和 pod update

    当我们新建一个Podfile文件运行后,会自动生成一个Podfile.lock文件,Podfile.lock文件里存储着我们已经安装的依赖库(pods)的版本。 当我们第一次运行Podfile时,如果对依赖库不指定版本的话,cocoapods会安装最新的版本,同时将pods的版本记录在Podfile.lock文件中。这个文件会保持对每个pod已安装版本的跟踪,并且锁定这些版本。

  • 2018-12-04 23:40:26

    pod删除已导入的第三方库和移除项目中的cocoapods

    CocoaPods是一个负责管理iOS项目中第三方开源库的工具。CocoaPods的项目源码在Github上管理。在我们有了CocoaPods这个工具之后,只需要将用到的第三方开源库放到一个名为Podfile的文件中,然后在命令行执行$ pod install命令。CocoaPods就会自动将这些第三方开源库的源码下载下来,并且为我的工程设置好相应的系统依赖和编译参数. 但是如果我们导入的某个第三方不适用,或者我们又不想使用该第三方,那我们又该如何将这些相关的东西从我们的项目中清理出去呢?

  • 2018-12-04 23:41:47

    制作自己的Pod库(公有/私有)

    目的:1.管理自己常用的类;2.组件化开发步骤:1.想一个比较酷的名字,在桌面简历文件夹。2.打开terminal,cd到这个文件夹下面,执行pod lib create  xxx(这里我们以JJCategoryKit为例子,下同)命令,如下图。这个过程会问几个问题,根据实际情况输入回答即可。这里我们选择添加demo,结束的时候会自动Lanuch这个app. 作者:深水日月 链接:https://www.jianshu.com/p/ece0b5721461 來源:简书 简书著作权归作者所有,任何形式的转载都请联系作者获得授权并注明出处。

  • 2018-12-05 06:08:26

    CocoaPods建立私有仓库 spec repo

    好多项目里都有公共的组件,copy来,copy去很容易出错,而且不容易维护,所以就想到用用cocoapods 建自己的私有库,Carthage用法虽然相对简单,但是它是把公共组件都放在framework里不容易单步调试,所以我还是选择用Cocoapods 来建立私有仓库 参考使用Cocoapods创建私有podspec

  • 2018-12-05 15:11:18

    为什么 Objective-C非常难

    作为一个Objective-C的coder,我总能听到一部 分人在这门语言上抱怨有很多问题。他们总在想快速学习这门语言来写一个App出来,但他们也总是联想到Objective-C看上去实在太难了或者在想这 些语法符号都是神马玩意?不错,他们问得非常好,所以本人也解释一下为什么很多程序员相比较学习Ruby或者Java很容易,但在决定开发iOS或者OS X应用时会那么犹豫。

  • 2018-12-05 15:22:23

    十分钟让你明白Objective-C的语法(和Java、C++的对比)

    很多想开发iOS,或者正在开发iOS的程序员以前都做过Java或者C++,当第一次看到Objective-C的代码时都会头疼,Objective-C的代码在语法上和Java, C++有着很大的区别,有的同学会感觉像是看天书一样。不过,语言都是相通的,有很多共性。下面列出Objective-C语言的语法和Java,C++的对比,这样你就会很容易Objective-C的语法是怎么回事了。

  • 2018-12-05 15:33:33

    一篇文章看懂有关iOS开发语言的一切!

    OS开发语言有哪些?OS开发语言主要包括什么?iOS开发语言具体怎么学习?今天重点介绍一下: iOS开发语言主要包括:C语言基础、Obiective-C编程、Swift、UIKit框架详解这几大块,在这里项目阶段就不详细的介绍了。 C语言基础 C语言是开发语言的基础,是最常用的一门程序设计语言,最常用于编写计算机程序。