MySQL :: MySQL 8.4 參考手冊 :: 19.2.1.1 基於語句與基於列複製的優點和缺點

版本 8.4

8.0 日文

MySQL 8.4 參考手冊 / ... / 基於語句與基於列複製的優點和缺點

19.2.1.1 基於語句與基於列複製的優點和缺點

每個二進位記錄格式都有其優點和缺點。對於大多數使用者而言，混合複製格式應提供資料完整性和效能的最佳組合。但是，如果您希望在執行某些工作時利用特定於基於語句或基於列複製格式的功能，則可以使用本節中的資訊，其中總結了它們的相對優點和缺點，以判斷哪種最適合您的需求。

基於語句複製的優點

經過驗證的技術。
減少寫入日誌檔的資料量。當更新或刪除影響許多列時，這會導致日誌檔所需的儲存空間大幅減少。這也表示備份和還原可以更快完成。
日誌檔包含所有進行變更的語句，因此可用於稽核資料庫。

基於語句複製的缺點

不適合 SBR 的語句。 並非所有修改資料的語句（例如 INSERT、DELETE、UPDATE 和 REPLACE 語句）都可以使用基於語句的複製進行複製。當使用基於語句的複製時，任何非決定性的行為都難以複製。這類資料修改語言 (DML) 語句的範例如下：
- 依賴於非決定性的可載入函數或儲存程式的語句，因為此類函數或儲存程式傳回的值取決於除提供給它的參數之外的其他因素。（但是，基於列的複製僅複製函數或儲存程式傳回的值，因此它對資料表列和資料的影響在來源和複本上是相同的。）有關更多資訊，請參閱第 19.5.1.16 節「調用功能的複製」。
- 使用沒有 ORDER BY 的 LIMIT 子句的 DELETE 和 UPDATE 語句是非決定性的。請參閱第 19.5.1.18 節「複製和 LIMIT」。
- 使用 NOWAIT 或 SKIP LOCKED 選項的鎖定讀取語句 (SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE)。請參閱使用 NOWAIT 和 SKIP LOCKED 進行鎖定讀取並行。
- 決定性的可載入函數必須在複本上應用。
- 使用下列任何函數的語句無法使用基於語句的複製正確複製：
  - LOAD_FILE()
  - UUID()、UUID_SHORT()
  - USER()
  - FOUND_ROWS()
  - SYSDATE()（除非來源和複本都使用 --sysdate-is-now 選項啟動）
  - GET_LOCK()
  - IS_FREE_LOCK()
  - IS_USED_LOCK()
  - RAND()
  - RELEASE_LOCK()
  - SOURCE_POS_WAIT()
  - SLEEP()
  - VERSION()
  但是，所有其他函數都使用基於語句的複製正確複製，包括 NOW() 等等。
  有關更多資訊，請參閱第 19.5.1.14 節「複製和系統函數」。
無法使用基於語句的複製正確複製的語句會記錄警告，如下所示：
```
[Warning] Statement is not safe to log in statement format.
```
在這種情況下，也會向用戶端發出類似的警告。用戶端可以使用 SHOW WARNINGS 顯示它。
INSERT ... SELECT 需要比使用基於列的複製更多的列級鎖定。
需要進行資料表掃描的 UPDATE 語句（因為 WHERE 子句中未使用索引）必須比使用基於列的複製鎖定更多的列。
對於 InnoDB：使用 AUTO_INCREMENT 的 INSERT 語句會封鎖其他不衝突的 INSERT 語句。
對於複雜的語句，必須在更新或插入列之前在複本上評估和執行語句。使用基於列的複製時，複本只需要修改受影響的列，而不需要執行完整的語句。
如果在複本上的評估中發生錯誤，尤其是在執行複雜語句時，基於語句的複製可能會隨著時間的推移而緩慢地增加受影響列的誤差幅度。請參閱第 19.5.1.29 節「複製期間的複本錯誤」。
儲存函數的執行使用與呼叫語句相同的 NOW() 值。但是，儲存程序的情況並非如此。
決定性的可載入函數必須在複本上應用。
來源和複本上的資料表定義必須（幾乎）相同。有關更多資訊，請參閱第 19.5.1.9 節「來源和複本上具有不同資料表定義的複製」。
從 MySQL 授權資料表讀取資料（透過聯結清單或子查詢），但不修改它們的 DML 作業會作為 MySQL 授權資料表上的非鎖定讀取執行，因此不適用於基於語句的複製。有關更多資訊，請參閱授權資料表並行。

基於列的複製的優點

可以複製所有變更。這是最安全的複製形式。

注意

更新 mysql 系統架構中資訊的語句，例如 GRANT、REVOKE 以及對觸發器、儲存常式（包括儲存程序）和檢視的操作，都是使用基於語句的複製複製到複本的。
對於 CREATE TABLE ... SELECT 等語句，會從資料表定義產生 CREATE 語句，並使用基於語句的格式複製，而列插入則使用基於列的格式複製。
來源上需要的列鎖定較少，因此對於下列類型的語句，可實現更高的並行性：
- INSERT ... SELECT
- 帶有 AUTO_INCREMENT 的 INSERT 語句
- 具有未使用鍵或未變更大多數檢查列的 WHERE 子句的 UPDATE 或 DELETE 語句。
對於任何 INSERT、UPDATE 或 DELETE 語句，複本上需要的列鎖定較少。

基於列的複製的缺點

RBR 可能會產生更多必須記錄的資料。若要複製 DML 語句（例如 UPDATE 或 DELETE 語句），基於語句的複製只會將語句寫入二進位日誌。相比之下，基於列的複製會將每個變更的列寫入二進位日誌。如果語句變更許多列，基於列的複製可能會將明顯更多的資料寫入二進位日誌；即使對於回滾的語句也是如此。這也表示建立和還原備份可能需要更多時間。此外，會將二進位日誌鎖定更長的時間以寫入資料，這可能會導致並行問題。使用 binlog_row_image=minimal 可以大幅減少此缺點。
與基於語句的複製相比，產生大型 BLOB 值的決定性可載入函數使用基於列的複製需要更長的時間才能複製。這是因為會記錄 BLOB 資料行值，而不是產生資料的語句。
您無法在複本上看到從來源接收和執行的語句。但是，您可以使用 mysqlbinlog 搭配 --base64-output=DECODE-ROWS 和 --verbose 選項來查看變更的資料。
或者，使用 binlog_rows_query_log_events 變數，如果啟用此變數，則在使用 -vv 選項時，會將具有語句的 Rows_query 事件新增至 mysqlbinlog 輸出。
對於使用 MyISAM 儲存引擎的資料表，在將基於列的事件應用於二進位日誌時，與將其作為語句應用相比，複本上需要對 INSERT 語句進行更強的鎖定。這表示使用基於列的複製時，不支援對 MyISAM 資料表進行並行插入。