On Friday, HaC I'm going online , There is a script to execute , A table needs to be backed up before execution .
O & M boss ：“ Why does the backup of this table take so long ,,？？”
1 Seconds passed ......2 Seconds passed ......
During the operation feedback system, a large number of orders were overtime .
Big guy found me , ask ：“ How do you back up ？”
insert into select * from ah！”
bosses ：“？？ You don't want to get mixed up ？”
It's another day that I'm hated by the boss , In order not to pack up and leave , I decided to learn some common methods of table backup .
MySQL In general, we usually use These two methods ：
- INSERT INTO SELECT
- CREATE TABLE AS SELECT
notes ： This article is only for MySQL innodb engine , Transactions are repeatable RR, The database version is 5.5
1.INSERT INTO SELECT
insert into Table2(field1,field2,...) select value1,value2,... from Table1
（1） Request target table Table2 There must be , And the fields field,field2... There must also be
（2） Be careful Table2 Primary key constraint for , If Table2 It has a primary key and is not empty , be field1, field2... The primary key must be included in
When executing a statement ,MySQL It's line by line locking （ Scan one lock one ）, Until all eligible data is locked , Release the lock after execution . So when the business is going on , Don't use this method .
stay RR Under isolation level , Also add row lock and clearance lock
Give me a chestnut ：
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;
First not commit; This statement is against the table t The primary key index has been added with (-∞,1] This next-key lock
Open a new one Navicat window , Simulate a new transaction entering , At this time, execute the following sentence sql We need to wait
insert into t values(-1,-1,-1);
It really locks the watch ~ I can't write it in , I finally know why the order is over time .
Back the pot, back the pot .
If you really want to use
INSERT INTO SELECT This method , You can use the following method to optimize ：
- Conditional , Force Index , Don't scan the whole table , for example
INSERT INTO Table2 SELECT * FROM Table1 FORCE INDEX (create_time) WHERE update_time <= '2020-03-08 00:00:00';
- add limit 100,100 such , Limited quantity
2. CREATE TABLE AS SELECT
create table as select Will create a table that doesn't exist , It can also be used to copy a table .
1. create table t3 as select * from t where 1=2; -- Create a table structure with t As like as two peas , Copy only structures, not data ; 2.create table t3 as select * from t ; -- Create a table structure with t As like as two peas , Replication structures also replicate data ;( Indexes don't create ) 3.create table t3(`id`,`a`) as select `id`,`c` from t; -- Create a table structure with t As like as two peas , Replication structures also replicate data , But specify the column name of the new table ;
The last two formats , If you follow the appropriate query criteria , You can only copy the data that meets the criteria to a new table . such as ：
create table table1 as select * from table2 where columns1>=1;
Table replication for large tables and multi fields , Consider whether each field is required , If it's not necessary , Can I customize the selection field , This will greatly increase the copy time .
CREATE table table1 as SELECT id FROM table2; -- Just copy id This column
Be careful This table creation process locks the table throughout the process . Statement execution finished , To release Metadata lock .
MDL Its full name is metadata lock, Metadata lock .MDL Lock is mainly used to maintain data consistency of table metadata , There are activities on the list （ Explicit or implicit ） When , You cannot write metadata . So from MySQL5.5 Version started to be introduced MDL lock , To protect the metadata information of the table , To solve or guarantee DDL Operation and DML Consistency between operations .
Be careful ：
- The new table does not automatically create the same index as the original table .（ That is, the index of the replicated table will disappear ）
3 . difference
First , The biggest difference is that they belong to different types of statements ,
INSERT INTO SELECTyes DML sentence （ Data operation language ,SQL Processing data and other operations are collectively referred to as data manipulation language ）, After completion, it needs to be submitted to take effect ,
CREATE TABLE AS SELECTyes DDL sentence （ Data definition language , Used to define and manage SQL The language of all objects in the database ）, It takes effect immediately after the execution , No rollback is provided , High efficiency .
secondly , Different functions ,
INSERT INTO SELECTJust insert data , You have to build a watch first ;
CREATE TABLE AS SELECTThen create the table and insert data together .
It is not recommended to use when there is a large amount of data Insert into as, Because the insertion efficiency of this statement is very slow .
The above is true for replicated tables , It's not a good choice , Share some common methods ：
Export to excel, Then spell it sql become insert into values(),(),() In the form of .
Timing task , The logic of the task is to query 100 Bar record , Then multiple threads are divided into several tasks to execute , Like a thread , Every thread 10 Bar record , After inserting , Looking for new 100 Record processing .
mysqldumb Method , for example
mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
export CSV file
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
The first 3、4 Two methods are suitable for whole table export .
5. When there is little business （ Late at night or something ） Next , have access to
create table as select .
Knowledge has increased again .