背景

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),所以性能提升是比较明显的。

做优化的调用栈如下:

  1. #0 do_ignore_flag_optimization()
  2. #1 ha_tokudb::set_main_dict_put_flags()
  3. #2 ha_tokudb::insert_row_to_main_dictionary()
  4. #3 ha_tokudb::write_row()
  5. #4 handler::ha_write_row()
  6. #5 write_record()
  7. #6 mysql_insert()
  8. #7 mysql_execute_command()

主要代码逻辑在 ha_tokudb::set_main_dict_put_flags()do_ignore_flag_optimization() 这2个函数中。

do_ignore_flag_optimization() 判断能否做这个优化:

  1. static inline bool do_ignore_flag_optimization(
  2. THD* thd,
  3. TABLE* table,
  4. bool opt_eligible) {
  5. bool do_opt = false;
  6. if (opt_eligible &&
  7. (is_replace_into(thd) || is_insert_ignore(thd)) &&
  8. tokudb::sysvars::pk_insert_mode(thd) == 1 &&
  9. !table->triggers &&
  10. !(mysql_bin_log.is_open() &&
  11. thd->variables.binlog_format != BINLOG_FORMAT_STMT)) {
  12. do_opt = true;
  13. }
  14. return do_opt;
  15. }

ha_tokudb::set_main_dict_put_flags() 根据 do_ignore_flag_optimization() 返回的结果和当前语句设置 put_flag。

  1. else if (using_ignore_flag_opt && is_replace_into(thd)
  2. && !in_hot_index)
  3. {
  4. *put_flags = old_prelock_flags;
  5. }
  6. else if (opt_eligible && using_ignore_flag_opt && is_insert_ignore(thd)
  7. && !in_hot_index)
  8. {
  9. *put_flags = DB_NOOVERWRITE_NO_ERROR | old_prelock_flags;
  10. }
  11. else
  12. {
  13. *put_flags = DB_NOOVERWRITE | old_prelock_flags;
  14. }

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

    模式TPSCPU%
    关闭优化3438.21900
    开启优化6590.31240
  2. INSERT IGNORE

    模式TPSCPU%
    关闭优化6165.361000
    开启优化6702.45240

可以看到对于 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 引擎层永远是返回成功的。