Mysql solution to improve the efficiency of copying large data tables

Preface

This article mainly introduces the relevant content about improving the efficiency of copying large data tables in MySQL, and shares it for your reference and learning. We often encounter large data tables at work;

Scenario: The data table needs to be altered, such as adding a field or removing a field. This can be done directly on a data table with tens of thousands of data, but it is not easy to do it on a data table with nearly 10 million data.

Possible situations:

1. Cause the database to crash or freeze

2. Causes other processes to slow down database read and write I/O

3. Another possibility is that the data format is inconsistent and the data cannot be written (for example, a varchar type needs to be changed to an int type, and an error will be reported when the data length is too large)

Solution:--

1. Recreate a data table create new_table create new_table select * from old_table This form is equivalent to copying a new data table ----(not recommended): Only the fields and data of the data table are copied, but the table structure primary key, index and default value will not be copied.

2. Divide into two steps

1). create new_table create new_table like old_table a new table with the same structure as old_table (including primary key, index and default value, etc.)

2). insert into new_table insert into new_table select * from old_table

----(If the amount of data is small, it is recommended to use this solution if the amount of data reaches millions or tens of millions. If the amount of data reaches millions or tens of millions, this solution is not applicable.)

Extension: If you only want to copy part of the data table, you can specify insert into new_table (field1, field2) select field1, field2 from old_table [limit n,m];

3.

1). Export the data table data through select from into outfile command

2). load data infile into

Without further ado, let's look at the picture and see how much the processing speed of Solution 2 and Solution 3 differs for a data volume of about 1 million.

>select * from money_info into outfile '/var/lib/mysql-files/money.txt'; 
>create table money_info_cyq11 like money_info;
>load data infile '/var/lib/mysql-files/money.txt' into table money_info_cyq11;

>create table money_info_cyq22 like money_info;
>insert into money_info_cyq22 select * from money_info; 

The speed is about 4 times faster, the 20 times speed mentioned on the Internet has not been experienced yet [covering face]

Note: There is still a problem

The outfile directory is required

>show variables like '%secure%';

Through this command, you can see the location of the directory corresponding to out_file of secure_file_priv, and specify this location to export;

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.