MySQL的insert into select 引發鎖表

上周五HaC我要上線，有一個腳本需要執行，執行前需要備份一個表。

運維大佬：「這個表的備份為什麼要這麼久，，？？」

1秒過去了……2秒過去了……

期間運營反饋系統出現大量訂單超時情況。

大佬找到我，問：「你怎麼備份的？」

我：「insert into select * from 呀！」

大佬：「？？你是不是不想混了？」

又是被大佬嫌棄的一天，為了不捲鋪蓋走人，我決定去學習一下表備份的常見方法。

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 是DML語句（數據操作語言，SQL中處理數據等操作統稱為數據操縱語言），完成後需要提交才能生效，CREATE TABLE AS SELECT 是DDL語句（數據定義語言，用於定義和管理 SQL 資料庫中的所有對象的語言 ），執行完直接生效，不提供回滾，效率比較高。

其次，功能不同，INSERT INTO SELECT只是插入數據，必須先建表；CREATE TABLE AS SELECT 則建表和插入數據一塊完成。

當有大量數據的時候不推薦使用Insert into as，因為該語句的插入的效率很慢。

4.總結

以上對複製表來說，都不是很好的選擇，分享幾種平時常用的方法：

導出成excel，然後拼sql 成 insert into values(),(),()的形式。

定時任務，任務的邏輯是查詢100條記錄，然後多個線程分到幾個任務執行，比如是個線程，每個線程10條記錄，插入後，在查詢新的100條記錄處理。

mysqldumb方法，例如

mysqldump -h<span class="katex-html" aria-hidden="true" style="font-size: inherit;color: inherit;line-height: inherit;margin: 1px;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="strut" style="height:0.69444em;vertical-align:0em;" style="font-size: inherit;color: inherit;line-height: inherit;margin: 1px;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mord mathit" style="font-size: inherit;color: inherit;line-height: inherit;margin: 1px;overflow-wrap: inherit !important;word-break: inherit !important;">h<span class="mord mathit" style="font-size: inherit;color: inherit;line-height: inherit;margin: 1px;overflow-wrap: inherit !important;word-break: inherit !important;">o<span class="mord mathit" style="font-size: inherit;color: inherit;line-height: inherit;margin: 1px;overflow-wrap: inherit !important;word-break: inherit !important;">s<span class="mord mathit" style="font-size: inherit;color: inherit;line-height: inherit;margin: 1px;overflow-wrap: inherit !important;word-break: inherit !important;">t<span class="mord mathit" style="margin-right:0.05764em;" style="font-size: inherit;color: inherit;line-height: inherit;margin: 1px;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="margin-right:0.05764em;" style="font-size: inherit;color: inherit;line-height: inherit;margin: 1px;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="margin-right:0.13889em;" style="font-size: inherit;color: inherit;line-height: inherit;margin: 1px;overflow-wrap: inherit !important;word-break: inherit !important;">Pport -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
</span class="mord mathit" style="margin-right:0.13889em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit"></span class="strut" style="height:0.69444em;vertical-align:0em;"></span class="katex-html" aria-hidden="true">

導出 CSV 文件

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

第3、4兩種方法適合整個表導出。

5. 業務少的情況（深夜什麼的）下，可以使用 create table as select  。

知識又增加了。