## 背景 MySQL 在標準 SQL 外，會擴展一些好用的語法，本文關注的 REPLACE 和 INSERT IGNORE 就屬于這類。這 2 個語法都是對 INSERT 的擴展，語義是向表中插入數據，不同之處在于遇到 PK 或者 UK 沖突時的處理： 1. INSERT：報 duplicate key 的錯誤，數據不插入； 2. REPLACE：刪除掉老沖突記錄，插入新的記錄； 3. INSERT IGNORE：不插入數據，但是不報錯。 可以看到，REPLACE 的語義是用新數據取代老數據，INSERT IGNORE 的語義是保留老的數據。 本文將向大家介紹 TokuDB 引擎對這個2個語法的優化。 ## 優化分析 我們先看下優化前，一次 REPLACE 和 INSERT IGNORE 都需要做哪些操作。 對于 REPLACE： 1\. 先嘗試做 insert，因為 TokuDB 插入是異步的，為了保證唯一性約束，會先做一次 search，看是否有已經有記錄； 2\. 如果老記錄不存在，就直接插入；如果存在，就向 server 層報 dup key 錯誤； 3\. server 層拿到 dup key 錯誤后，再向引擎發一次 search 操作，把老記錄撈出來； 4\. server 層有了老記錄和要插入的數據，調引擎層的 update 接口做更新（其實這里應該做 delete + insert，server 層做了一個優先，只需要調一次引擎接口；如果有外鍵或者有delete觸發器的話，還是會做 delete + insert 的，可以參考?`write_record()`?函數）。 對于 INSERT IGNORE： 1\. 先嘗試做 insert，因為 TokuDB 插入是異步的，為了保證唯一性約束，會先做一次 search，看是否有已經有記錄； 2\. 如果老記錄不存在，就直接插入；如果存在，就向 server 層報 dup key 錯誤； 3\. server 層拿到 dup key 錯誤后，發現設置了 ignore，就正常返回。 TokuDB 優化后是怎樣的呢，REPLACE 和 INSERT IGNORE?只需要做一次插入即可，TokuDB 對寫是做了優化的，只需要將 msg 放在 FT 的 root 節點即可，后臺線程會異步將其應用到 leaf 節點（參考[TokuDB索引結構–Fractal Tree](http://mysql.taobao.org/monthly/2016/04/09/)），所以性能提升是比較明顯的。 做優化的調用棧如下： ~~~ #0 do_ignore_flag_optimization() #1 ha_tokudb::set_main_dict_put_flags() #2 ha_tokudb::insert_row_to_main_dictionary() #3 ha_tokudb::write_row() #4 handler::ha_write_row() #5 write_record() #6 mysql_insert() #7 mysql_execute_command() ~~~ 主要代碼邏輯在?`ha_tokudb::set_main_dict_put_flags()`?和?`do_ignore_flag_optimization()`?這2個函數中。 `do_ignore_flag_optimization()`?判斷能否做這個優化： ~~~ static inline bool do_ignore_flag_optimization( THD* thd, TABLE* table, bool opt_eligible) { bool do_opt = false; if (opt_eligible && (is_replace_into(thd) || is_insert_ignore(thd)) && tokudb::sysvars::pk_insert_mode(thd) == 1 && !table->triggers && !(mysql_bin_log.is_open() && thd->variables.binlog_format != BINLOG_FORMAT_STMT)) { do_opt = true; } return do_opt; } ~~~ `ha_tokudb::set_main_dict_put_flags()`?根據?`do_ignore_flag_optimization()`?返回的結果和當前語句設置 put_flag。 ~~~ else if (using_ignore_flag_opt && is_replace_into(thd) && !in_hot_index) { *put_flags = old_prelock_flags; } else if (opt_eligible && using_ignore_flag_opt && is_insert_ignore(thd) && !in_hot_index) { *put_flags = DB_NOOVERWRITE_NO_ERROR | old_prelock_flags; } else { *put_flags = DB_NOOVERWRITE | old_prelock_flags; } ~~~ `db_put()`?中會根據前面設置的 put_flag，決定是調用?`toku_ft_insert_unique()`，還是`toku_ft_maybe_insert()`，前者會先調用?`toku_ft_lookup()`?做唯一性檢查，然后再做插入；后者則直接插入。在最終調用?`toku_ft_root_put_msg()`，將 msg 放在root節點時，會根據之前的flag 生成不同 type 的msg，如 INSERT IGNORE 的 type 就設置為 FT_INSERT_NO_OVERWRITE，表示msg類型是插入，但是如果有老記錄時不覆蓋，后臺 apply 線程在應用時，就會根據 msg 的類型采取相應的處理。 ## 性能測試對比 為了能夠方便的開啟和關閉這個優化，筆者在代碼里加了一個開關。測試是用 sysbench，開32個線程，一個事務里就一條語句（REPLACE 或者 INSERT IGNORE），表結構就是sysbench默認的，但是去掉了二級索引，另外 binlog 是關閉的，結果如下： 1. REPLACE | 模式 | TPS | CPU% | | --- | --- | --- | | 關閉優化 | 3438.21 | 900 | | 開啟優化 | 6590.31 | 240 | 2. INSERT IGNORE | 模式 | TPS | CPU% | | --- | --- | --- | | 關閉優化 | 6165.36 | 1000 | | 開啟優化 | 6702.45 | 240 | 可以看到對于 REPLACE 的優化效果非常明顯，用更低的 CPU 消耗獲得了更高的 TPS；對于 INSERT IGNORE，CPU 消耗大大降低，TPS 有一定提升。 INSERT IGNORE 優化效果沒有 REPLACE 這么明顯，是因為 INSERT IGNORE 本身的邏輯要比 REPLACE 簡單，在優化前如果沖突記錄存在的話，是直接返回的。 ## 使用限制 需要注意的是，這個優化并不是通用的，具體的限制如下： 1. 只能有一個PK，不能其它任務用二級索引 PK所在的FT做插入時，是直接把 msg 放到 root 節點的，根本就沒有取可能存在的老記錄，所以二級索引的更新是沒法做的。 2. 要求 binlog 用 statement 格式，或者關閉 binlog 如果 binlog 是 row 格式的話，會導致備庫應用報錯，所有的操作都記為 Write_rows event，如果有記錄沖突的話，備庫執行時直接報 dup key 錯誤。 3. 表上不能有 triger 這個主要是因為優化后語義被改變了，replace 在沖突時沒有 delete 操作，insert ignore 引擎層永遠是返回成功的。