又是被大佬嫌棄的一天，為了不卷鋪蓋走人，我決定去學習一下表備份的常見方法。
MySQL一般我們在生産上備份資料通常會用到 這兩種方法：
- INSERT INTO SELECT
- CREATE TABLE AS SELECT
注：本文僅針對MySQL innodb引擎，事務是可重複讀RR，資料庫版本為5.5
1.INSERT INTO SELECT
insert into Table2(field1,field2,...) select value1,value2,... from Table1
複制
注意
（1）要求目标表Table2必須存在，并且字段field,field2…也必須存在
（2）注意Table2的主鍵限制，如果Table2有主鍵而且不為空，則 field1， field2…中必須包括主鍵
在執行語句的時候，MySQL是逐行加鎖的（掃描一個鎖一個），直至鎖住所有符合條件的資料，執行完畢才釋放鎖。是以當業務在進行的時候，切忌使用這種方法。
在RR隔離級别下，還會加行鎖和間隙鎖
舉個栗子吧：
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;
複制
先不commit；這個語句對表 t 主鍵索引加了 (-∞,1] 這個 next-key lock
新開一個Navicat視窗，模拟新事務進入，此時執行下面這句sql就需要等待
insert into t values(-1,-1,-1);
複制
鎖住了
真就鎖表了~無法寫進去了，我終于知道為什麼訂單逾時了。
背鍋背鍋。
如果實在要使用
INSERT INTO SELECT
這種方法，可以使用下面的方法進行優化：
- 加條件，強制走索引，不要全表掃描，例如
INSERT INTO Table2 SELECT
*
FROM
Table1 FORCE INDEX (create_time)
WHERE
update_time <= '2020-03-08 00:00:00';
複制
- 加上limit 100,100 這種，限制數量
2. CREATE TABLE AS SELECT
create table as select
會建立一個不存在的表，也可以用來複制一個表。
1. create table t3 as select * from t where 1=2;
-- 建立一個表結構與t一模一樣的表，隻複制結構不複制資料；
2.create table t3 as select * from t ;
-- 建立一個表結構與t一模一樣的表,複制結構同時也複制資料；(索引不會建立)
3.create table t3(`id`,`a`) as select `id`,`c` from t;
-- 建立一個表結構與t一模一樣的表,複制結構同時也複制資料,但是指定新表的列名；
複制
後面兩種格式，如果後面跟上合适的查詢條件，可以隻複制符合條件的資料到新的表中。比如：
create table table1 as select * from table2 where columns1>=1;
複制
針對大表多字段的表複制，考慮是否每一個字段都是必需的，如果不是必需的，可以自定義選擇字段嗎，這樣複制的時間會大大提升。
CREATE table table1 as SELECT id FROM table2; -- 隻複制id這一列
複制
注意此建表過程全程鎖表。語句執行完畢，才釋放中繼資料鎖。
MDL全稱為metadata lock，即中繼資料鎖。MDL鎖主要作用是維護表中繼資料的資料一緻性，在表上有活動事務（顯式或隐式）的時候，不可以對中繼資料進行寫入操作。是以從MySQL5.5版本開始引入了MDL鎖，來保護表的中繼資料資訊，用于解決或者保證DDL操作與DML操作之間的一緻性。
注意：
- 新表不會自動建立建立和原表相同的索引。（即複制表的索引會消失）
3 .差別
- 首先，最大的差別是二者屬于不同類型的語句，
INSERT INTO SELECT
CREATE TABLE AS SELECT
- 其次，功能不同，
INSERT INTO SELECT
CREATE TABLE AS SELECT
- 當有大量資料的時候不推薦使用Insert into as，因為該語句的插入的效率很慢。
4.總結
以上對複制表來說，都不是很好的選擇，分享幾種平時常用的方法：
導出成excel，然後拼sql 成 insert into values(),(),()的形式。
定時任務，任務的邏輯是查詢100條記錄，然後多個線程分到幾個任務執行，比如是個線程，每個線程10條記錄，插入後，在查詢新的100條記錄處理。
mysqldumb方法，例如
導出 CSV 檔案
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
複制
第3、4兩種方法适合整個表導出。
5. 業務少的情況（深夜什麼的）下，可以使用
create table as select
。
知識又增加了。
原創電子書曆時整整一年總結的 Java 面試 + Java 後端技術學習指南，這是本人這幾年及校招的總結，各種高頻面試題已經全部進行總結，按照章節複習即可，已經拿到了大廠offer。原創思維導圖掃碼或者微信搜 程式員的技術圈子 回複 面試 領取原創電子書和思維導圖。 複制