淺談create table as 和 insert into select 複制表遇到的問題ll -h

1、 建立一個表，表結構與原表是相同的。建表語句：

CREATE TABLE <code>huayu_test1</code> (

<code>waybill_id</code> bigint(20) NOT NULL AUTO_INCREMENT COMMENT '?????',

<code>waybill_no</code> varchar(20) NOT NULL COMMENT '???',

<code>post_date</code> datetime NOT NULL COMMENT '????????',

<code>order_id</code> varchar(50) DEFAULT NULL COMMENT '?????????',

<code>batch_no</code> varchar(50) DEFAULT NULL COMMENT '???',

<code>biz_product_id</code> varchar(20) NOT NULL COMMENT '??????',

<code>biz_product_no</code> varchar(20) NOT NULL COMMENT '??????',

<code>biz_product_name</code> varchar(50) NOT NULL COMMENT '??????',

<code>sender_type</code> varchar(20) DEFAULT NULL COMMENT '0 ?? 1????',

<code>sender_id</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>sender_no</code> varchar(50) DEFAULT NULL COMMENT '??????(??????)',

<code>sender</code> varchar(200) DEFAULT NULL COMMENT '??????',

<code>sender_linker</code> varchar(200) DEFAULT NULL,

<code>sender_fixtel</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>sender_mobile</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>sender_addr</code> varchar(200) DEFAULT NULL COMMENT '??????',

<code>sender_country_no</code> varchar(20) DEFAULT NULL COMMENT '??????',

<code>sender_country_name</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>sender_province_no</code> varchar(20) DEFAULT NULL COMMENT '??????',

<code>sender_province_name</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>sender_city_no</code> varchar(20) DEFAULT NULL COMMENT '??????',

<code>sender_city_name</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>sender_county_no</code> varchar(20) DEFAULT NULL COMMENT '??????',

<code>sender_county_name</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>sender_notes</code> varchar(1000) DEFAULT NULL COMMENT '????',

<code>receiver_no</code> varchar(50) DEFAULT NULL COMMENT '??????????????',

<code>receiver</code> varchar(200) DEFAULT NULL COMMENT '??????',

<code>receiver_linker</code> varchar(200) DEFAULT NULL,

<code>receiver_fixtel</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>receiver_mobile</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>receiver_addr</code> varchar(200) DEFAULT NULL COMMENT '??????',

<code>receiver_country_no</code> varchar(20) DEFAULT NULL COMMENT '??????',

<code>receiver_country_name</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>receiver_province_no</code> varchar(20) DEFAULT NULL COMMENT '??????',

<code>receiver_province_name</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>receiver_city_no</code> varchar(20) DEFAULT NULL COMMENT '??????',

<code>receiver_city_name</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>receiver_county_no</code> varchar(20) DEFAULT NULL COMMENT '??????',

<code>receiver_county_name</code> varchar(50) DEFAULT NULL COMMENT '??????',

<code>insurance_flag</code> char(1) DEFAULT NULL COMMENT '????????????1:?? 2:?? 3:??',

<code>insurance_amount</code> decimal(12,2) DEFAULT NULL COMMENT '??????',

<code>pickup_type</code> varchar(20) DEFAULT NULL COMMENT '?????0 ???????1 ??????',

<code>payment_mode</code> varchar(20) DEFAULT NULL COMMENT '????(???) 1:??? 2:??? 3:??? 4:??????? 5:?? 6:?/??? 7:???',

<code>real_weight</code> decimal(8,0) DEFAULT NULL COMMENT '????',

<code>fee_weight</code> decimal(8,0) DEFAULT NULL,

<code>volume_weight</code> decimal(8,0) DEFAULT NULL,

<code>length</code> decimal(8,0) DEFAULT NULL,

<code>width</code> decimal(8,0) DEFAULT NULL,

<code>height</code> decimal(8,0) DEFAULT NULL,

<code>quantity</code> int(11) DEFAULT NULL,

<code>packaging</code> varchar(20) DEFAULT NULL,

<code>package_material</code> varchar(20) DEFAULT NULL,

<code>goods_desc</code> varchar(200) DEFAULT NULL,

<code>contents_quantity</code> int(11) DEFAULT NULL,

<code>cod_flag</code> char(1) DEFAULT NULL,

<code>cod_amount</code> decimal(12,2) DEFAULT NULL,

<code>receipt_flag</code> char(1) DEFAULT NULL,

<code>receipt_waybill_no</code> varchar(20) DEFAULT NULL,

<code>receipt_fee_amount</code> decimal(12,2) DEFAULT NULL,

<code>insurance_premium_amount</code> decimal(12,2) DEFAULT NULL,

<code>valuable_flag</code> char(1) DEFAULT NULL,

<code>cargo_total_price</code> decimal(12,2) DEFAULT NULL,

<code>cargo_total_purchasing_price</code> decimal(12,2) DEFAULT NULL,

<code>allow_fee_flag</code> char(1) DEFAULT NULL,

<code>is_feed_flag</code> char(1) DEFAULT NULL,

<code>manual_fee_type</code> char(1) DEFAULT NULL,

<code>fee_date</code> datetime DEFAULT NULL,

<code>discount_rate</code> decimal(6,2) DEFAULT NULL,

<code>settlement_mode</code> varchar(20) DEFAULT NULL,

<code>payment_state</code> char(1) DEFAULT NULL,

<code>payment_date</code> datetime DEFAULT NULL,

<code>payment_id</code> varchar(50) DEFAULT NULL,

<code>manage_org_code</code> varchar(20) DEFAULT NULL,

<code>postage_suite_code</code> varchar(20) DEFAULT NULL,

<code>fee_area_suite_code</code> varchar(20) DEFAULT NULL,

<code>fee_area_code</code> varchar(20) DEFAULT NULL,

<code>fee_area_name</code> varchar(20) DEFAULT NULL,

<code>is_advance_flag</code> char(1) DEFAULT NULL,

<code>deliver_type</code> char(1) DEFAULT NULL,

<code>deliver_sign</code> varchar(50) DEFAULT NULL,

<code>deliver_date</code> char(1) DEFAULT NULL,

<code>deliver_notes</code> varchar(1000) DEFAULT NULL,

<code>deliver_pre_date</code> date DEFAULT NULL,

<code>battery_flag</code> char(1) DEFAULT NULL,

<code>is_jinguan</code> varchar(20) DEFAULT NULL,

<code>workbench</code> varchar(20) DEFAULT NULL,

<code>electronic_preferential_no</code> varchar(50) DEFAULT NULL,

<code>electronic_preferential_amount</code> decimal(12,2) DEFAULT NULL,

<code>pickup_attribute</code> char(1) DEFAULT NULL,

<code>adjust_type</code> varchar(20) DEFAULT NULL,

<code>postage_revoke</code> decimal(12,2) DEFAULT NULL,

<code>print_flag</code> char(1) DEFAULT NULL,

<code>print_date</code> datetime DEFAULT NULL,

<code>print_times</code> int(11) DEFAULT NULL,

<code>declare_source</code> varchar(20) DEFAULT NULL,

<code>declare_type</code> varchar(20) DEFAULT NULL,

<code>declare_curr_code</code> varchar(20) DEFAULT NULL,

<code>create_user_name</code> varchar(50) DEFAULT NULL,

<code>modify_user_name</code> varchar(50) DEFAULT NULL,

<code>volume</code> decimal(8,0) DEFAULT NULL COMMENT '??',

<code>contents_type_no</code> varchar(20) DEFAULT NULL COMMENT '??????',

<code>contents_type_name</code> varchar(200) DEFAULT NULL COMMENT '??????',

<code>contents_weight</code> decimal(8,0) DEFAULT NULL COMMENT '??????',

<code>transfer_type</code> varchar(20) DEFAULT NULL COMMENT '????',

<code>postage_total</code> decimal(12,2) NOT NULL COMMENT '???=????+????',

<code>postage_standard</code> decimal(12,2) DEFAULT NULL COMMENT '????',

<code>postage_paid</code> decimal(12,2) DEFAULT NULL COMMENT '????',

<code>postage_other</code> decimal(12,2) DEFAULT NULL COMMENT '????',

<code>is_deleted</code> char(1) DEFAULT '0' COMMENT '?????n0??n1??',

<code>create_user_id</code> bigint(20) DEFAULT NULL COMMENT '???id',

<code>gmt_created</code> datetime DEFAULT NULL COMMENT '????',

<code>modify_user_id</code> bigint(20) DEFAULT NULL COMMENT '???id',

<code>gmt_modified</code> datetime DEFAULT NULL COMMENT '????',

<code>reserved1</code> bigint(20) DEFAULT NULL COMMENT '????1',

<code>reserved2</code> bigint(20) DEFAULT NULL COMMENT '????2',

<code>reserved3</code> bigint(20) DEFAULT NULL COMMENT '????3',

<code>reserved4</code> varchar(200) DEFAULT NULL COMMENT '????4',

<code>reserved5</code> varchar(200) DEFAULT NULL COMMENT '????5',

<code>reserved6</code> varchar(200) DEFAULT NULL COMMENT '????6',

<code>reserved7</code> varchar(200) DEFAULT NULL COMMENT '????7',

<code>reserved8</code> varchar(200) DEFAULT NULL COMMENT '????8',

<code>reserved9</code> datetime DEFAULT NULL COMMENT '????9',

<code>reserved10</code> text COMMENT '????10',

<code>logistics_order_no</code> bigint(50) DEFAULT NULL,

<code>inner_channel</code> varchar(20) DEFAULT NULL,

<code>base_product_id</code> bigint(20) DEFAULT NULL,

<code>base_product_no</code> varchar(20) DEFAULT NULL,

<code>base_product_name</code> varchar(20) DEFAULT NULL,

<code>is_special_marketing</code> char(1) DEFAULT NULL,

<code>product_type</code> varchar(20) DEFAULT NULL,

<code>biz_product_type</code> varchar(20) DEFAULT NULL,

<code>product_reach_area</code> char(1) DEFAULT NULL,

<code>contents_attribute</code> char(1) DEFAULT NULL,

<code>contents_cargo_no</code> varchar(1000) DEFAULT NULL,

<code>cmd_code</code> varchar(20) DEFAULT NULL,

<code>manual_charge_reason</code> varchar(200) DEFAULT NULL,

<code>time_limit</code> char(1) DEFAULT NULL,

<code>io_type</code> varchar(20) DEFAULT NULL,

<code>ecommerce_no</code> varchar(20) DEFAULT NULL,

<code>waybill_type</code> varchar(20) DEFAULT NULL,

<code>pre_waybill_no</code> varchar(50) DEFAULT NULL,

<code>post_batch_id</code> varchar(50) DEFAULT NULL,

<code>biz_occur_date</code> datetime DEFAULT NULL,

<code>post_org_id</code> bigint(20) DEFAULT NULL,

<code>post_org_no</code> varchar(50) DEFAULT NULL,

<code>org_drds_code</code> varchar(50) DEFAULT NULL,

<code>post_org_simple_name</code> varchar(50) DEFAULT NULL,

<code>post_org_product_name</code> varchar(20) DEFAULT NULL,

<code>post_person_id</code> bigint(20) DEFAULT NULL,

<code>post_person_no</code> varchar(50) DEFAULT NULL,

<code>post_person_name</code> varchar(50) DEFAULT NULL,

<code>post_person_mobile</code> varchar(50) DEFAULT NULL,

<code>sender_warehouse_id</code> bigint(20) DEFAULT NULL,

<code>sender_warehouse_name</code> varchar(200) DEFAULT NULL,

<code>sender_safety_code</code> varchar(50) DEFAULT NULL,

<code>sender_im_type</code> varchar(20) DEFAULT NULL,

<code>sender_im_id</code> varchar(50) DEFAULT NULL,

<code>sender_id_type</code> varchar(20) DEFAULT NULL,

<code>sender_id_no</code> varchar(50) DEFAULT NULL,

<code>sender_id_encrypted_code</code> varchar(50) DEFAULT NULL,

<code>sender_agent_id_type</code> varchar(20) DEFAULT NULL,

<code>sender_agent_id_no</code> varchar(50) DEFAULT NULL,

<code>sender_id_encrypted_code_agent</code> varchar(50) DEFAULT NULL,

<code>sender_addr_additional</code> varchar(200) DEFAULT NULL,

<code>sender_district_no</code> varchar(20) DEFAULT NULL,

<code>sender_postcode</code> varchar(20) DEFAULT NULL,

<code>sender_gis</code> varchar(20) DEFAULT NULL,

<code>registered_customer_no</code> varchar(50) DEFAULT NULL,

<code>receiver_type</code> char(1) DEFAULT NULL,

<code>receiver_id</code> bigint(20) DEFAULT NULL,

<code>receiver_warehouse_id</code> bigint(20) DEFAULT NULL,

<code>receiver_warehouse_name</code> varchar(200) DEFAULT NULL,

<code>receiver_safety_code</code> varchar(50) DEFAULT NULL,

<code>receiver_im_type</code> varchar(20) DEFAULT NULL,

<code>receiver_im_id</code> varchar(50) DEFAULT NULL,

<code>receiver_addr_additional</code> varchar(200) DEFAULT NULL,

<code>receiver_district_no</code> varchar(20) DEFAULT NULL,

<code>receiver_postcode</code> varchar(20) DEFAULT NULL,

<code>receiver_gis</code> varchar(20) DEFAULT NULL,

<code>receiver_notes</code> varchar(1000) DEFAULT NULL,

<code>customer_manager_id</code> bigint(20) DEFAULT NULL,

<code>customer_manager_no</code> varchar(50) DEFAULT NULL,

<code>customer_manager_name</code> varchar(50) DEFAULT NULL,

<code>salesman_id</code> bigint(20) DEFAULT NULL,

<code>salesman_no</code> varchar(50) DEFAULT NULL,

<code>salesman_name</code> varchar(50) DEFAULT NULL,

<code>order_weight</code> decimal(8,0) DEFAULT NULL,

<code>post_org_name</code> varchar(200) DEFAULT NULL,

PRIMARY KEY (<code>waybill_id</code>),

KEY <code>auto_shard_key_post_date</code> (<code>post_date</code>),

KEY <code>auto_shard_key_waybill_no</code> (<code>waybill_no</code>)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='???????';

2、 原表資料量

mysql&gt; 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&gt; 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&gt; select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_test1';

9.174G

4、 執行指令create table as select 複制表

mysql&gt; create table huayu_test2 as select * from qps_waybill_base_02;

Query OK, 15861881 rows affected (14 min 44.98 sec)

資料量

mysql&gt; select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_test2';

9.818G

5、資料行數

mysql&gt; select count(*) from huayu_test2;

count(*)

15861881

1 row in set (4.12 sec)

mysql&gt; select count(*) from huayu_test1;

1 row in set (7.00 sec)

mysql&gt;

.ibd檔案大小對比

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&gt; select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_test1_2';

9.472G

mysql&gt; select concat(round(sum(data_length/1024/1024/1024),3),'G') from information_schema.tables where table_name='huayu_test1_1';

9.364G

從這裡看，資料量的大小還是差不多的。

7、比較一下：

mysql&gt; show table status like 'qps_waybill_base_02'G

1. row **

Avg_row_length: 1324

Max_data_length: 0

Index_length: 1240465408

Auto_increment: 568964219

Create_options:

Block_format: Original

mysql&gt; show table status like 'huayu_test1'G

Avg_row_length: 639

Index_length: 1268776960

1 row in set (0.01 sec)

mysql&gt; show table status like 'huayu_test1_1'G

Avg_row_length: 656

Index_length: 286179328

Auto_increment: 15925006

mysql&gt; show table status like 'huayu_test1_2'G

Avg_row_length: 681

Index_length: 1307574272

8、在源表執行了optimize table 之後，資料變成了10G左右，跟複制後的表的資料相近了

10.041G

9、初步結論

在delete 很大空間之後，這個空間不會被立即删除，而是等待新的插入的資料進行填充，後續的insert 資料會占用舊的位置，使用OPTIMIZE TABLE來重新利用未使用的空間，并整理資料檔案的碎片，這個是針對myisam表和archive表的。