mysql 存储过程 rowcount_mysql 存储过程 row_count()的搜索结果-阿里云开发者社区

旧城等待， 2022-10-27 02:39 44阅读 0赞

浅谈create table as 和 insert into select 复制表遇到的问题

1、 新建一个表，表结构与原表是相同的。建表语句：CREATE TABLE huayu\_test1 (waybill\_id bigint(20) NOT NULL AUTO\_INCREMENT COMMENT '?????',waybill\_no varchar(20) NOT NULL COMMENT '???',post\_date datetime NOT NULL COMMENT '????????',order\_id varchar(50) DEFAULT NULL COMMENT '?????????',batch\_no varchar(50) DEFAULT NULL COMMENT '???',biz\_product\_id varchar(20) NOT NULL COMMENT '??????',biz\_product\_no varchar(20) NOT NULL COMMENT '??????',biz\_product\_name varchar(50) NOT NULL COMMENT '??????',sender\_type varchar(20) DEFAULT NULL COMMENT '0 ?? 1????',sender\_id varchar(50) DEFAULT NULL COMMENT '??????',sender\_no varchar(50) DEFAULT NULL COMMENT '??????(??????)',sender varchar(200) DEFAULT NULL COMMENT '??????',sender\_linker varchar(200) DEFAULT NULL,sender\_fixtel varchar(50) DEFAULT NULL COMMENT '??????',sender\_mobile varchar(50) DEFAULT NULL COMMENT '??????',sender\_addr varchar(200) DEFAULT NULL COMMENT '??????',sender\_country\_no varchar(20) DEFAULT NULL COMMENT '??????',sender\_country\_name varchar(50) DEFAULT NULL COMMENT '??????',sender\_province\_no varchar(20) DEFAULT NULL COMMENT '??????',sender\_province\_name varchar(50) DEFAULT NULL COMMENT '??????',sender\_city\_no varchar(20) DEFAULT NULL COMMENT '??????',sender\_city\_name varchar(50) DEFAULT NULL COMMENT '??????',sender\_county\_no varchar(20) DEFAULT NULL COMMENT '??????',sender\_county\_name varchar(50) DEFAULT NULL COMMENT '??????',sender\_notes varchar(1000) DEFAULT NULL COMMENT '????',receiver\_no varchar(50) DEFAULT NULL COMMENT '??????????????',receiver varchar(200) DEFAULT NULL COMMENT '??????',receiver\_linker varchar(200) DEFAULT NULL,receiver\_fixtel varchar(50) DEFAULT NULL COMMENT '??????',receiver\_mobile varchar(50) DEFAULT NULL COMMENT '??????',receiver\_addr varchar(200) DEFAULT NULL COMMENT '??????',receiver\_country\_no varchar(20) DEFAULT NULL COMMENT '??????',receiver\_country\_name varchar(50) DEFAULT NULL COMMENT '??????',receiver\_province\_no varchar(20) DEFAULT NULL COMMENT '??????',receiver\_province\_name varchar(50) DEFAULT NULL COMMENT '??????',receiver\_city\_no varchar(20) DEFAULT NULL COMMENT '??????',receiver\_city\_name varchar(50) DEFAULT NULL COMMENT '??????',receiver\_county\_no varchar(20) DEFAULT NULL COMMENT '??????',receiver\_county\_name varchar(50) DEFAULT NULL COMMENT '??????',insurance\_flag char(1) DEFAULT NULL COMMENT '????????????1:?? 2:?? 3:??',insurance\_amount decimal(12,2) DEFAULT NULL COMMENT '??????',pickup\_type varchar(20) DEFAULT NULL COMMENT '?????0 ???????1 ??????',payment\_mode varchar(20) DEFAULT NULL COMMENT '????(???) 1:??? 2:??? 3:??? 4:??????? 5:?? 6:?/??? 7:???',real\_weight decimal(8,0) DEFAULT NULL COMMENT '????',fee\_weight decimal(8,0) DEFAULT NULL,volume\_weight decimal(8,0) DEFAULT NULL,length decimal(8,0) DEFAULT NULL,width decimal(8,0) DEFAULT NULL,height decimal(8,0) DEFAULT NULL,quantity int(11) DEFAULT NULL,packaging varchar(20) DEFAULT NULL,package\_material varchar(20) DEFAULT NULL,goods\_desc varchar(200) DEFAULT NULL,contents\_quantity int(11) DEFAULT NULL,cod\_flag char(1) DEFAULT NULL,cod\_amount decimal(12,2) DEFAULT NULL,receipt\_flag char(1) DEFAULT NULL,receipt\_waybill\_no varchar(20) DEFAULT NULL,receipt\_fee\_amount decimal(12,2) DEFAULT NULL,insurance\_premium\_amount decimal(12,2) DEFAULT NULL,valuable\_flag char(1) DEFAULT NULL,cargo\_total\_price decimal(12,2) DEFAULT NULL,cargo\_total\_purchasing\_price decimal(12,2) DEFAULT NULL,allow\_fee\_flag char(1) DEFAULT NULL,is\_feed\_flag char(1) DEFAULT NULL,manual\_fee\_type char(1) DEFAULT NULL,fee\_date datetime DEFAULT NULL,discount\_rate decimal(6,2) DEFAULT NULL,settlement\_mode varchar(20) DEFAULT NULL,payment\_state char(1) DEFAULT NULL,payment\_date datetime DEFAULT NULL,payment\_id varchar(50) DEFAULT NULL,manage\_org\_code varchar(20) DEFAULT NULL,postage\_suite\_code varchar(20) DEFAULT NULL,fee\_area\_suite\_code varchar(20) DEFAULT NULL,fee\_area\_code varchar(20) DEFAULT NULL,fee\_area\_name varchar(20) DEFAULT NULL,is\_advance\_flag char(1) DEFAULT NULL,deliver\_type char(1) DEFAULT NULL,deliver\_sign varchar(50) DEFAULT NULL,deliver\_date char(1) DEFAULT NULL,deliver\_notes varchar(1000) DEFAULT NULL,deliver\_pre\_date date DEFAULT NULL,battery\_flag char(1) DEFAULT NULL,is\_jinguan varchar(20) DEFAULT NULL,workbench varchar(20) DEFAULT NULL,electronic\_preferential\_no varchar(50) DEFAULT NULL,electronic\_preferential\_amount decimal(12,2) DEFAULT NULL,pickup\_attribute char(1) DEFAULT NULL,adjust\_type varchar(20) DEFAULT NULL,postage\_revoke decimal(12,2) DEFAULT NULL,print\_flag char(1) DEFAULT NULL,print\_date datetime DEFAULT NULL,print\_times int(11) DEFAULT NULL,declare\_source varchar(20) DEFAULT NULL,declare\_type varchar(20) DEFAULT NULL,declare\_curr\_code varchar(20) DEFAULT NULL,create\_user\_name varchar(50) DEFAULT NULL,modify\_user\_name varchar(50) DEFAULT NULL,volume decimal(8,0) DEFAULT NULL COMMENT '??',contents\_type\_no varchar(20) DEFAULT NULL COMMENT '??????',contents\_type\_name varchar(200) DEFAULT NULL COMMENT '??????',contents\_weight decimal(8,0) DEFAULT NULL COMMENT '??????',transfer\_type varchar(20) DEFAULT NULL COMMENT '????',postage\_total decimal(12,2) NOT NULL COMMENT '???=????+????',postage\_standard decimal(12,2) DEFAULT NULL COMMENT '????',postage\_paid decimal(12,2) DEFAULT NULL COMMENT '????',postage\_other decimal(12,2) DEFAULT NULL COMMENT '????',is\_deleted char(1) DEFAULT '0' COMMENT '?????n0??n1??',create\_user\_id bigint(20) DEFAULT NULL COMMENT '???id',gmt\_created datetime DEFAULT NULL COMMENT '????',modify\_user\_id bigint(20) DEFAULT NULL COMMENT '???id',gmt\_modified datetime DEFAULT NULL COMMENT '????',reserved1 bigint(20) DEFAULT NULL COMMENT '????1',reserved2 bigint(20) DEFAULT NULL COMMENT '????2',reserved3 bigint(20) DEFAULT NULL COMMENT '????3',reserved4 varchar(200) DEFAULT NULL COMMENT '????4',reserved5 varchar(200) DEFAULT NULL COMMENT '????5',reserved6 varchar(200) DEFAULT NULL COMMENT '????6',reserved7 varchar(200) DEFAULT NULL COMMENT '????7',reserved8 varchar(200) DEFAULT NULL COMMENT '????8',reserved9 datetime DEFAULT NULL COMMENT '????9',reserved10 text COMMENT '????10',logistics\_order\_no bigint(50) DEFAULT NULL,inner\_channel varchar(20) DEFAULT NULL,base\_product\_id bigint(20) DEFAULT NULL,base\_product\_no varchar(20) DEFAULT NULL,base\_product\_name varchar(20) DEFAULT NULL,is\_special\_marketing char(1) DEFAULT NULL,product\_type varchar(20) DEFAULT NULL,biz\_product\_type varchar(20) DEFAULT NULL,product\_reach\_area char(1) DEFAULT NULL,contents\_attribute char(1) DEFAULT NULL,contents\_cargo\_no varchar(1000) DEFAULT NULL,cmd\_code varchar(20) DEFAULT NULL,manual\_charge\_reason varchar(200) DEFAULT NULL,time\_limit char(1) DEFAULT NULL,io\_type varchar(20) DEFAULT NULL,ecommerce\_no varchar(20) DEFAULT NULL,waybill\_type varchar(20) DEFAULT NULL,pre\_waybill\_no varchar(50) DEFAULT NULL,post\_batch\_id varchar(50) DEFAULT NULL,biz\_occur\_date datetime DEFAULT NULL,post\_org\_id bigint(20) DEFAULT NULL,post\_org\_no varchar(50) DEFAULT NULL,org\_drds\_code varchar(50) DEFAULT NULL,post\_org\_simple\_name varchar(50) DEFAULT NULL,post\_org\_product\_name varchar(20) DEFAULT NULL,post\_person\_id bigint(20) DEFAULT NULL,post\_person\_no varchar(50) DEFAULT NULL,post\_person\_name varchar(50) DEFAULT NULL,post\_person\_mobile varchar(50) DEFAULT NULL,sender\_warehouse\_id bigint(20) DEFAULT NULL,sender\_warehouse\_name varchar(200) DEFAULT NULL,sender\_safety\_code varchar(50) DEFAULT NULL,sender\_im\_type varchar(20) DEFAULT NULL,sender\_im\_id varchar(50) DEFAULT NULL,sender\_id\_type varchar(20) DEFAULT NULL,sender\_id\_no varchar(50) DEFAULT NULL,sender\_id\_encrypted\_code varchar(50) DEFAULT NULL,sender\_agent\_id\_type varchar(20) DEFAULT NULL,sender\_agent\_id\_no varchar(50) DEFAULT NULL,sender\_id\_encrypted\_code\_agent varchar(50) DEFAULT NULL,sender\_addr\_additional varchar(200) DEFAULT NULL,sender\_district\_no varchar(20) DEFAULT NULL,sender\_postcode varchar(20) DEFAULT NULL,sender\_gis varchar(20) DEFAULT NULL,registered\_customer\_no varchar(50) DEFAULT NULL,receiver\_type char(1) DEFAULT NULL,receiver\_id bigint(20) DEFAULT NULL,receiver\_warehouse\_id bigint(20) DEFAULT NULL,receiver\_warehouse\_name varchar(200) DEFAULT NULL,receiver\_safety\_code varchar(50) DEFAULT NULL,receiver\_im\_type varchar(20) DEFAULT NULL,receiver\_im\_id varchar(50) DEFAULT NULL,receiver\_addr\_additional varchar(200) DEFAULT NULL,receiver\_district\_no varchar(20) DEFAULT NULL,receiver\_postcode varchar(20) DEFAULT NULL,receiver\_gis varchar(20) DEFAULT NULL,receiver\_notes varchar(1000) DEFAULT NULL,customer\_manager\_id bigint(20) DEFAULT NULL,customer\_manager\_no varchar(50) DEFAULT NULL,customer\_manager\_name varchar(50) DEFAULT NULL,salesman\_id bigint(20) DEFAULT NULL,salesman\_no varchar(50) DEFAULT NULL,salesman\_name varchar(50) DEFAULT NULL,order\_weight decimal(8,0) DEFAULT NULL,post\_org\_name varchar(200) DEFAULT NULL, PRIMARY KEY (waybill\_id), KEY auto\_shard\_key\_post\_date (post\_date), KEY auto\_shard\_key\_waybill\_no (waybill\_no)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='???????';2、 原表数据量

mysql> select concat(round(sum(data\_length/1024/1024/1024),3),'G') from information\_schema.tables where table\_name='qps\_waybill\_base\_02';

concat(round(sum(data\_length/1024/1024/1024),3),'G')

18.636G

1 row in set (0.00 sec)

3、 执行命令insert into 复制表mysql> insert into huayu\_test1 select \* from qps\_waybill\_base\_02;Query OK, 15861881 rows affected (16 min 8.37 sec)Records: 15861881 Duplicates: 0 Warnings: 0

数据量：

mysql> select concat(round(sum(data\_length/1024/1024/1024),3),'G') from information\_schema.tables where table\_name='huayu\_test1';

concat(round(sum(data\_length/1024/1024/1024),3),'G')

9.174G

1 row in set (0.00 sec)

4、 执行命令create table as select 复制表mysql> create table huayu\_test2 as select \* from qps\_waybill\_base\_02;Query OK, 15861881 rows affected (14 min 44.98 sec)Records: 15861881 Duplicates: 0 Warnings: 0

数据量

mysql> select concat(round(sum(data\_length/1024/1024/1024),3),'G') from information\_schema.tables where table\_name='huayu\_test2';

concat(round(sum(data\_length/1024/1024/1024),3),'G')

9.818G

1 row in set (0.00 sec)

5、数据行数

mysql> select count(\*) from huayu\_test2;

count(\*)

15861881

1 row in set (4.12 sec)

mysql> select count(\*) from huayu\_test1;

count(\*)

15861881

1 row in set (7.00 sec)

mysql>

.ibd文件大小对比

ll -h

total 44G-rw-rw---- 1 mysql mysql 61 May 3 2017 db.opt-rw-rw---- 1 mysql mysql 51K Feb 2 09:33 huayu\_test1.frm-rw-rw---- 1 mysql mysql 12G Feb 2 09:49 huayu\_test1.ibd-rw-rw---- 1 mysql mysql 52K Feb 2 09:59 huayu\_test2.frm-rw-rw---- 1 mysql mysql 11G Feb 2 10:13 huayu\_test2.ibd

6、由于源表是通过inert into select 将实际的生产上的表拼接起来的，现在以新建的huayu\_test1为源表进行测试，这个表是通过inert into select 源表 新建的表，数据量如下分别为insert into select 、 create table as select 和 新的源表：

mysql> select concat(round(sum(data\_length/1024/1024/1024),3),'G') from information\_schema.tables where table\_name='huayu\_test1\_2';

concat(round(sum(data\_length/1024/1024/1024),3),'G')

9.472G

1 row in set (0.00 sec)

mysql> select concat(round(sum(data\_length/1024/1024/1024),3),'G') from information\_schema.tables where table\_name='huayu\_test1\_1';

concat(round(sum(data\_length/1024/1024/1024),3),'G')

9.364G

1 row in set (0.00 sec)

mysql> select concat(round(sum(data\_length/1024/1024/1024),3),'G') from information\_schema.tables where table\_name='huayu\_test1';

concat(round(sum(data\_length/1024/1024/1024),3),'G')

9.174G

1 row in set (0.00 sec)

mysql>

从这里看，数据量的大小还是差不多的。7、比较一下：mysql> show table status like 'qps\_waybill\_base\_02'G 1. row \*\*

Name: qps\_waybill\_base\_02

Engine: InnoDB

Version: 10

Row\_format: Compact

Rows: 15109587

Avg\_row\_length: 1324

Data\_length: 20009975808

Max\_data\_length: 0 Index\_length: 1240465408

Data\_free: 7340032

Auto\_increment: 568964219

Create\_time: 2018-01-24 09:42:57

Update\_time: NULL

Check\_time: NULL

Collation: utf8\_general\_ci

Checksum: NULL

Create\_options:

Comment: ???????

Block\_format: Original1 row in set (0.00 sec)

mysql> show table status like 'huayu\_test1'G 1. row \*\*

Name: huayu\_test1

Engine: InnoDB

Version: 10

Row\_format: Compact

Rows: 15412951

Avg\_row\_length: 639

Data\_length: 9850322944

Max\_data\_length: 0 Index\_length: 1268776960

Data\_free: 7340032

Auto\_increment: 568964219

Create\_time: 2018-02-02 09:33:40

Update\_time: NULL

Check\_time: NULL

Collation: utf8\_general\_ci

Checksum: NULL

Create\_options:

Comment: ???????

Block\_format: Original1 row in set (0.01 sec)

mysql> show table status like 'huayu\_test1\_1'G 1. row \*\*

Name: huayu\_test1\_1

Engine: InnoDB

Version: 10

Row\_format: Compact

Rows: 15310307

Avg\_row\_length: 656

Data\_length: 10054795264

Max\_data\_length: 0 Index\_length: 286179328

Data\_free: 4194304

Auto\_increment: 15925006

Create\_time: 2018-02-02 10:27:47

Update\_time: NULL

Check\_time: NULL

Collation: utf8\_general\_ci

Checksum: NULL

Create\_options:

Comment:

Block\_format: Original1 row in set (0.00 sec)

mysql> show table status like 'huayu\_test1\_2'G 1. row \*\*

Name: huayu\_test1\_2

Engine: InnoDB

Version: 10

Row\_format: Compact

Rows: 14912979

Avg\_row\_length: 681

Data\_length: 10170138624

Max\_data\_length: 0 Index\_length: 1307574272

Data\_free: 7340032

Auto\_increment: 568964219

Create\_time: 2018-02-02 10:42:09

Update\_time: NULL

Check\_time: NULL

Collation: utf8\_general\_ci

Checksum: NULL

Create\_options:

Comment: ???????

Block\_format: Original1 row in set (0.00 sec)8、在源表执行了optimize table 之后，数据变成了10G左右，跟复制后的表的数据相近了

mysql> select concat(round(sum(data\_length/1024/1024/1024),3),'G') from information\_schema.tables where table\_name='qps\_waybill\_base\_02';

concat(round(sum(data\_length/1024/1024/1024),3),'G')

10.041G

1 row in set (0.00 sec)9、初步结论

1、Create table as 比 insert into select 的速度更快，但是经实验创建表后的数据大小insert into select占用的数据量比较小。Create 是ddl语句，insert 是dml语句，insert的时候每条语句都会产生对应的redo和undo日志，所以相对create 语句是慢一些的。

官方文档说明：Create table as 语句不会复制原表的索引，如果想要复制索引的话，需要指定索引

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

还有可能会发生数据类型的转换。例如，auto\_increment列的属性不会保留，varchar列会转换成char列，

但是原表的数据量相比create 和 insert 复制后的表为什么会这么大，而重新复制一个相同的原表后，用新的原表测试后create 和insert 复制后的数据量就差不多了。怀疑是生产上的原表可能有一些多余的东西，或者表在合并的过程中有空隙只是占用表空间，而没有存储数据 。

2、Optimize table：InnoDB表上进行大量插入、更新或删除操作之后。ibd文件，因为它是通过启用innodb\_file\_per\_table选项创建的。重新组织了表和索引，操作系统可以回收磁盘空间。

在delete 很大空间之后，这个空间不会被立即删除，而是等待新的插入的数据进行填充，后续的insert 数据会占用旧的位置，使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片，这个是针对myisam表和archive表的。

对于InnoDB表，Optimize table映射到ALTER TABLE…FORCE，它重新构建表以更新索引统计数据和聚集索引中的空闲未使用空间。在InnoDB表上运行时，optimize table 的时候会报这样的信息：Table does not support optimize, doing recreate + analyze instead。 会自动重建一个表再使用analyze命令进行优化

Analyse table是对表的索引分布进行分析，优化表的索引的性能

文章

关系型数据库 · MySQL · 索引 · SQL · 存储

2018-02-09