MySQL中Innodb关于Handler_commit每次DML增加2的原因是什么
本篇内容主要讲解“MySQL中Innodb关于Handler_commit每次DML增加2的原因是什么”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“MySQL中Innodb关于Handler_commit每次DML增加2的原因是什么”吧!
专注于为中小企业提供网站设计、网站建设服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业通海免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了上1000+企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。
请教一个问题。我每次insert一条语句,查询show global status like 'Handler_commit'; 发现每次增加值是2,难道不应该是1吗? 最简单的insert into table a values(1);
一、问题展示
语句如下:
mysql> flush status; Query OK, 0 rows affected (0.10 sec) mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.01 sec) mysql> insert into testm values(16,'gaopeng',34); Query OK, 1 row affected (0.15 sec) mysql> show status like '%commit%'; +----------------+-------+| Variable_name | Value | +----------------+-------+ | Com_commit | 0 || Com_xa_commit | 0 | | Handler_commit | 2 |+----------------+-------+3 rows in set (0.01 sec)
问为什么 Handler_commit是2而不是1。
二、原因分析
其实对于这个问题只要看看这个Handler_commit指标增加的方式就可以看出原因。实际上这个指标出现在ha_commit_low函数中如下:
for (; ha_info; ha_info= ha_info_next) { int err; handlerton *ht= ha_info->ht(); if ((err= ht->commit(ht, thd, all))) { my_error(ER_ERROR_DURING_COMMIT, MYF(0), err); error=1; } DBUG_ASSERT(!thd->status_var_aggregated); thd->status_var.ha_commit_count++; //此处增加 ha_info_next= ha_info->next(); if (restore_backup_ha_data) reattach_engine_ha_data_to_thd(thd, ht); ha_info->reset(); /* keep it conveniently zero-filled */ }
可以清楚的看到ha_commit_count实际就是调用ht->commit的次数,由于有多个Handler的存在,因此这里需要调用多次。对于开启binlog+innodb的这种结构来讲分别要做:
binlog的commit
Innodb的commit
后面会看到实际binlog的commit什么都没做,但是这是一种协议。
那么如果我们关闭binlog可以发现Handler_commit为1了如下:
mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> insert into testm values(15,'gaopeng',34); Query OK, 1 row affected (0.10 sec) mysql> show status like '%commit%'; +----------------+-------+| Variable_name | Value | +----------------+-------+ | Com_commit | 0 || Com_xa_commit | 0 | | Handler_commit | 1 |+----------------+-------+3 rows in set (0.01 sec)
三、binlog commit栈帧
#0 binlog_commit (hton=0x3485e30, thd=0x7fff2c014430, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:1833#1 0x0000000000f64104 in ha_commit_low (thd=0x7fff2c014430, all=false, run_after_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1923#2 0x000000000185772b in MYSQL_BIN_LOG::process_commit_stage_queue (this=0x2e01c80, thd=0x7fff2c014430, first=0x7fff2c014430) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8647#3 0x0000000001858f5d in MYSQL_BIN_LOG::ordered_commit (this=0x2e01c80, thd=0x7fff2c014430, all=false, skip_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9318#4 0x000000000185700c in MYSQL_BIN_LOG::commit (this=0x2e01c80, thd=0x7fff2c014430, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8440#5 0x0000000000f63df8 in ha_commit_trans (thd=0x7fff2c014430, all=false, ignore_global_read_lock=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1818
但是实际上binlog_commit什么都没做,因为在此之前他已经做完了需要做的事情比如flush、sync等
static int binlog_commit(handlerton *hton, THD *thd, bool all){ DBUG_ENTER("binlog_commit"); /* Nothing to do (any more) on commit. */ DBUG_RETURN(0); }
四、Innodb commit接口
#0 innobase_commit (hton=0x2e9edd0, thd=0x7fff2c014430, commit_trx=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4652#1 0x0000000000f64104 in ha_commit_low (thd=0x7fff2c014430, all=false, run_after_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1923#2 0x000000000185772b in MYSQL_BIN_LOG::process_commit_stage_queue (this=0x2e01c80, thd=0x7fff2c014430, first=0x7fff2c014430) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8647#3 0x0000000001858f5d in MYSQL_BIN_LOG::ordered_commit (this=0x2e01c80, thd=0x7fff2c014430, all=false, skip_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9318#4 0x000000000185700c in MYSQL_BIN_LOG::commit (this=0x2e01c80, thd=0x7fff2c014430, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8440#5 0x0000000000f63df8 in ha_commit_trans (thd=0x7fff2c014430, all=false, ignore_global_read_lock=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1818
实际上innodb comit才是需要真正做的,这里包含一些事情要做,比如事物状态的改变,资源的释放。
最后select也会增加Handler_commit,增加为1。
到此,相信大家对“MySQL中Innodb关于Handler_commit每次DML增加2的原因是什么”有了更深的了解,不妨来实际操作一番吧!这里是创新互联网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
新闻名称:MySQL中Innodb关于Handler_commit每次DML增加2的原因是什么
文章出自:http://myzitong.com/article/pecjod.html