mysql insert as select from table_在pgsql环境下，Insert into table select…与create table as select…的比较...

最近时间，因为刚入职没人培训就发了一些文档，感觉坑也挺大...心里又有了跑的......

好了回顾正题，因为实在太闲，于是听了一些前辈在讨论备份数据的时候，他们普遍使用insert into newTable select * from oldTable的做法(在pgsql环境下)。

因为我也不太懂pgsql调优，但是问了咋不直接用create table as select...?前辈们都是nsert into newTable select * from oldTable 效率是最高的...但是我懵逼了?

在我使用mysql和oralce下经常用insert into select 与 create table as语句来进行表单数据的复制，但就当复制表的数据量很小的时候两个语句的效率区别不大，而当表的数量级别达百万级以上的时候，就会有很明显的效率区别了呀?是不是pgsql不一样，于是我就自己弄了一点临时数据测试看看...百度了也没有相关pgsql的调优文章.....

然后，在微信读书上看到了一本关于pgsql的书之后，pgsql复制表，其实就这两种

1. INSERT INTO  NEWTABLE FROM SELECT * FROM OLDTABLE 语句

###

insert into NEWTABLE select * from OLDTABLE;

insert into NEWTABLE select field1,field2,field3 from OLDTABLEwhere ...;

前提：NEWTABLE 必须存在

1

2

3

4

5

###

insertintoNEWTABLEselect *fromOLDTABLE;

insertintoNEWTABLEselectfield1,field2,field3fromOLDTABLEwhere...;

前提：NEWTABLE必须存在

2. SELECT INTO NEWTABLE FROM OLDTABLE 语句

###

select * into NEWTABLE from OLDTABLE;

select field1,field2,field3 into NEWTABLE from OLDTABLE;

##要求目标NEWTABLE不存在，在插入时会自动创建表NEWTABLE，

##并将OLDTABLE中指定字段数据复制到NEWTABLE中。

##如果NEWTABLE存在则会报错..

##其实就相当于create table as语句

1

2

3

4

5

6

7

###

select *intoNEWTABLEfromOLDTABLE;

selectfield1,field2,field3intoNEWTABLEfromOLDTABLE;

##要求目标NEWTABLE不存在，在插入时会自动创建表NEWTABLE，

##并将OLDTABLE中指定字段数据复制到NEWTABLE中。

##如果NEWTABLE存在则会报错..

##其实就相当于create table as语句

至于他们的效率问题，我这边没有安装pgsql环境，国庆后再测试一下，再对比一下两者的效率怎么样..这次是吃了没有文化的亏..pgsql复制表居然还与其他不一样了...继续学习..

继续上次说的，这次测试时自己虚拟机环境，2H2G，docker环境搭建的pgsql，没任何设置优化。处理结果如下：

[SQL]insert into employees_i select * from employees

时间: 0.636s

受影响的行: 393216

[SQL]create table employees_c as select * from employees

时间: 0.401s

受影响的行: 393216

[SQL]SELECT INTO employees_si FROM employees

时间: 0.188s

受影响的行: 393216

---------------------------

[SQL]insert into employees_i select * from employees

时间: 5.952s

受影响的行: 3145728

[SQL]create table employees_c as select * from employees

时间: 2.513s

受影响的行: 3145728

[SQL]SELECT INTO employees_si FROM employees

时间: 1.438s

受影响的行: 3145728

-------

[SQL]insert into employees_i select * from employees

时间: 18.942s

受影响的行: 12582912

[SQL]create table employees_c as select * from employees

时间: 9.979s

受影响的行: 12582912

[SQL]SELECT INTO employees_si FROM employees

时间: 6.236s

受影响的行: 12582912

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

[SQL]insertintoemployees_iselect *fromemployees

时间:0.636s

受影响的行:393216

[SQL]createtableemployees_casselect *fromemployees

时间:0.401s

受影响的行:393216

[SQL]SELECTINTOemployees_siFROMemployees

时间:0.188s

受影响的行:393216

---------------------------

[SQL]insertintoemployees_iselect *fromemployees

时间:5.952s

受影响的行:3145728

[SQL]createtableemployees_casselect *fromemployees

时间:2.513s

受影响的行:3145728

[SQL]SELECTINTOemployees_siFROMemployees

时间:1.438s

受影响的行:3145728

-------

[SQL]insertintoemployees_iselect *fromemployees

时间:18.942s

受影响的行:12582912

[SQL]createtableemployees_casselect *fromemployees

时间:9.979s

受影响的行:12582912

[SQL]SELECTINTOemployees_siFROMemployees

时间:6.236s

受影响的行:12582912

从结果上看，pgsql的表复制最快是 select into >create table as select >insert into table select *

所以，这又是吃了没文化的亏吧~