在我粗略的研究中，我無法找到關於SELECT INTO OUTFILE提供的好處的明確答案INSERT INTO ... SELECT。在閱讀與InnoDB 表上有關鎖相關的文件時，它指出：INSERT INTO ... SELECT

在插入到 T 的每一行上設置一個不帶間隙鎖的獨占索引記錄。如果事務隔離級別為 READ COMMITTED 或啟用了 innodb_locks_unsafe_for_binlog，並且事務隔離級別不是 SERIALIZABLE，則 InnoDB 將 S 上的搜尋作為一致讀取（沒有鎖）。否則，InnoDB 在 S 中的行上設置共享的 next-key 鎖。

為了避免鎖定INSERT INTO ... SELECT似乎我必須確保隔離級別是READ COMMITTED避免在查詢期間鎖定源表。

但是，我還沒有找到任何關於 locks 和 usingSELECT INTO OUTFILE的權威答案，甚至沒有 MySQL文件參考 lock 資訊。

我的目標是避免在查詢執行時鎖定源表以避免連接堆疊。

您應該使用SELECT … LOCK IN SHARE MODE。為什麼 ？

SELECT … LOCK IN SHARE MODE 在讀取的任何行上設置共享模式鎖。其他會話可以讀取這些行，但在您的事務送出之前不能修改它們。如果其中任何行被另一個尚未送出的事務更改，您的查詢將等待該事務結束，然後使用最新值。

在你的情況下，你可以嘗試這個

START TRANSACTION;
SELECT ... LOCK IN SHARE MODE;
SELECT ... INTO OUTFILE;
ROLLBACK;

這將執行兩個SELECT查詢

• 首先SELECT鎖定您希望的表中的行
• 二SELECT要執行SELECT ... INTO OUTFILE

就個人而言，我認為您不必如此嚴厲。事務隔離應該足夠聰明，以實現這個原子SELECT並為INSERT. 我知道我說***should be***這就是為什麼你首先問你問題。

無論您是SELECT ... INTO OUTFILE作為一個命令執行還是以我建議的嚴厲方式執行，源表的行數據都將是完全可讀的。

試一試 ！！！

更新 2014-12-10 15:12 EST

你的評論

謝謝你的回答，它確實有幫助，但 OP 的主要觀點是確定使用 SELECT INTO OUTFILE 而不是 INSERT INTO … SELECT 是否有好處？

它們在操作上是不同的

• SELECT INTO OUTFILE創建一個文本文件
• INSERT INTO SELECT從結果中載入一張表SELECT

更新 2014-12-11 12:21 EST

在這種情況下，我唯一能想到的是數據的時間點以及您使用它的時間。對於這兩種類型的操作，都會有一些隱式共享鎖定。

使用SELECT INTO OUTFILE，您正在準備結果並將其保存在外部。在載入過程中使用將數據載入到表中LOAD DATA INFILE不會涉及任何共享鎖定。請記住，這SELECT INTO OUTFILE會產生磁碟 I/O，並且在此過程中仍然會施加一些記憶體。

使用INSERT INTO SELECT，共享鎖可能必須在 InnoDB 中存活更長時間，因為您正在鎖定行並使用這些相同的行插入到另一個表中。

因此，如果我正在尋找性能獎勵，我會給予優勢，INSERT INTO SELECT因為您正在執行相同數量的共享行鎖定，單個操作的磁碟 I/O 必須小於單獨SELECT INTO OUTFILE和後續的LOAD DATA INFILE. 當然，您必須將這兩種方法與您的數據集進行比較。一個數據集的性能獎勵可能是另一個數據集的性能成本。

更新 2014-12-17 00:00 EST

你的評論

我沒有收到通知你更新了你的答案，所以我假設它沒有收到賞金。從理論上講，您的解釋確實有道理，但是我正在尋找更權威的回應，希望單獨文件的成本（正如您正確提到的）可能值得在復雜性上進行權衡以提高性能。

唯一的權威回應來自 MySQL 文件。

首先，MySQL 文件LOAD DATA INFILE是怎麼說的？

LOAD DATA INFILE 語句以非常高的速度將文本文件中的行讀取到表中。LOAD DATA INFILE 是 SELECT … INTO OUTFILE 的補充。（請參閱第 13.2.9.1 節，“SELECT … INTO 語法”。）要將數據從表寫入文件，請使用 SELECT … INTO OUTFILE。要將文件讀回表中，請使用 LOAD DATA INFILE。

兩段後，它說

有關 INSERT 與 LOAD DATA INFILE 的效率以及加速 LOAD DATA INFILE 的更多資訊，請參閱第 8.2.2.1 節，“INSERT 語句的速度”。

當您查看插入語句的速度時，它會說：

為了優化插入速度，將許多小操作組合成一個大操作。理想情況下，您建立一個連接，一次發送許多新行的數據，並將所有索引更新和一致性檢查延遲到最後。

插入一行所需的時間由以下因素決定，其中數字表示近似比例：

連接：（3）

向伺服器發送查詢：(2)

解析查詢：（2）

插入行：（1×行大小）

插入索引：（1 × 索引數）

結束： (1)

這沒有考慮打開表的初始成本，每個並發執行的查詢都會執行一次。

假設 B-tree 索引，表的大小會減慢 log N 的索引插入速度。

您可以使用以下方法來加快插入速度：

如果您同時從同一個客戶端插入多行，請使用帶有多個 VALUES 列表的 INSERT 語句一次插入多行。這比使用單獨的單行 INSERT 語句要快得多（在某些情況下快很多倍）。如果要向非空表添加數據，則可以調整 bulk_insert_buffer_size 變數以使數據插入更快。請參見第 5.1.4 節，“伺服器系統變數”。

從文本文件載入表時，使用 LOAD DATA INFILE。這通常比使用 INSERT 語句快 20 倍。請參見第 13.2.6 節，“LOAD DATA INFILE 語法”。

利用列具有預設值的事實。僅當要插入的值與預設值不同時才顯式插入值。這減少了 MySQL 必須做的解析並提高了插入速度。

有關 InnoDB 表的特定提示，請參閱第 8.5.4 節，“InnoDB 表的批量數據載入”。

有關 MyISAM 表的特定提示，請參閱第 8.6.2 節，“MyISAM 表的批量數據載入”。

此時事情開始看起來有點模糊，因為您必鬚根據儲存引擎調整載入過程。MyISAM 在此語句中相當直接，因為大容量插入緩衝區僅用於 MyISAM，而 LOAD DATA INFILE 將利用大容量插入緩衝區。InnoDB 不會。

看看這個 InnoDB 的圖示（Percona CTO Vadim Tchachenko）

調整選項還有其他考慮因素，但LOAD DATA INFILE實際上會將所有內容放入 InnoDB 緩衝池，通過日誌緩衝區、雙寫緩衝區、插入緩衝區（如果目標表具有非唯一索引）、重做日誌（ib_logfile0、ib_logfile1）和表的物理文件。這就是 LOAD DATA INFILE 的好處必須被取消的地方。

我寫過這個

• Feb 06, 2012: LOAD DATA (400k rows) INFILE 大約需要 7 分鐘，無法殺死“記錄慢查詢”程序？
• Apr 20, 2012:為什麼 ‘LOAD DATA INFILE’ 比普通的 INSERT 語句快？
• Jul 13, 2012:從 infile 載入 Mysql 卡在硬碟上等待
• Jan 11, 2013: MySQL LOAD DATA INFILE 在使用 InnoDB 引擎進行幾次輸入後減慢了 80%

結語

正如我在之前對這個答案的更新中已經說過的那樣

因此，如果我正在尋找性能獎勵，我會給予優勢，INSERT INTO SELECT因為您正在執行相同數量的共享行鎖定，單個操作的磁碟 I/O 必須小於單獨SELECT INTO OUTFILE和後續的LOAD DATA INFILE. 當然，您必須將這兩種方法與您的數據集進行比較。一個數據集的性能獎勵可能是另一個數據集的性能成本。

基本上，您必須SELECT INTO OUTFILE/LOAD DATA INFILE針對INSERT INTO SELECT. 它可能是一個數據集的 6 個，一個數據集的六個，另一個數據集的陸上勝利。

MySQL Docs 和我過去的文章中所說的一切，我仍然對INSERT INTO SELECT. 您只需要測試這兩種方法。

引用自：https://dba.stackexchange.com/questions/84825