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

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 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. 在建立資料表時，定義一個類型為 BIGINT UNSIGNED NOT NULL 的 FTS_DOC_ID 資料行，並使用名為 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 工具程式。