MySQL :: MySQL 8.4 參考手冊 :: 10.5.5 InnoDB 表格的大量資料載入

版本 8.4

9.0
8.0
5.7

8.0 日文

MySQL 8.4 參考手冊 / ... / InnoDB 表格的大量資料載入

10.5.5 InnoDB 表格的大量資料載入

這些效能提示補充了第 10.2.5.1 節「最佳化 INSERT 陳述式」中快速插入的一般指南。

當匯入資料到 InnoDB 時，請關閉自動提交模式，因為它會為每個插入執行磁碟記錄刷新。若要在匯入作業期間停用自動提交，請使用 SET autocommit 和 COMMIT 陳述式將其包圍
```
SET autocommit=0;
... SQL import statements ...
COMMIT;
```
mysqldump 選項 --opt 建立的傾印檔案可以快速匯入 InnoDB 表格，即使不使用 SET autocommit 和 COMMIT 陳述式將其包圍也一樣。
如果次要索引鍵上有 UNIQUE 限制，您可以在匯入期間暫時關閉唯一性檢查來加速表格匯入
```
SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;
```
對於大型表格，這會節省大量的磁碟 I/O，因為 InnoDB 可以使用其變更緩衝區來批量寫入次要索引記錄。請確保資料不包含重複的索引鍵。
如果表格中有 FOREIGN KEY 限制，您可以在匯入期間關閉外部索引鍵檢查來加速表格匯入
```
SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;
```
對於大型表格，這可以節省大量的磁碟 I/O。
如果您需要插入多個列，請使用多列 INSERT 語法來減少用戶端和伺服器之間的通訊負擔
```
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
```
此提示適用於任何表格的插入，而不僅僅是 InnoDB 表格。
當對具有自動遞增欄位的表格進行大量插入時，請將 innodb_autoinc_lock_mode 設定為 2（交錯）而非 1（連續）。詳情請參閱第 17.6.1.6 節，「InnoDB 中的 AUTO_INCREMENT 處理」。
執行大量插入時，以 PRIMARY KEY 順序插入資料列會比較快。InnoDB 表格使用叢集索引，這使得以 PRIMARY KEY 的順序使用資料相對較快。對於無法完全容納在緩衝池中的表格，以 PRIMARY KEY 順序執行大量插入尤其重要。
為了在將資料載入 InnoDB FULLTEXT 索引時獲得最佳效能，請遵循以下步驟
1. 在建立表格時定義一個 FTS_DOC_ID 欄位，類型為 BIGINT UNSIGNED NOT NULL，並具有名為 FTS_DOC_ID_INDEX 的唯一索引。例如
```
CREATE TABLE t1 (
    FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL DEFAULT '',
    text mediumtext NOT NULL,
PRIMARY KEY (`FTS_DOC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
```
2. 將資料載入表格。
3. 在資料載入後建立 FULLTEXT 索引。
注意

當在建立表格時新增 FTS_DOC_ID 欄位時，請確保在更新 FULLTEXT 索引欄位時更新 FTS_DOC_ID 欄位，因為 FTS_DOC_ID 必須隨著每個 INSERT 或 UPDATE 而單調遞增。如果您選擇不在建立表格時新增 FTS_DOC_ID，並讓 InnoDB 為您管理 DOC ID，則 InnoDB 會在下一次呼叫 CREATE FULLTEXT INDEX 時，將 FTS_DOC_ID 作為隱藏欄位新增。然而，這種方法需要重建表格，這可能會影響效能。
如果將資料載入新的 MySQL 執行個體，請考慮使用 ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG 語法停用重做日誌記錄。停用重做日誌記錄有助於加快資料載入速度，因為避免了重做日誌寫入。如需更多資訊，請參閱停用重做日誌記錄。

警告

此功能僅適用於將資料載入新的 MySQL 執行個體。請勿在生產系統上停用重做日誌記錄。允許在停用重做日誌記錄時關閉並重新啟動伺服器，但是，在停用重做日誌記錄時意外停止伺服器可能會導致資料遺失和執行個體損毀。
使用 MySQL Shell 匯入資料。MySQL Shell 的平行表格匯入工具 util.importTable() 可為大型資料檔案快速將資料匯入到 MySQL 關聯式表格。MySQL Shell 的傾印載入工具 util.loadDump() 也提供平行載入功能。請參閱MySQL Shell 工具。