二进制文件里面的exec_time是什么意思

原文：http://www.mysqlperformanceblog.com/2011/01/31/what-is-exec_time-in-binary-logs/

If you’ve used MySQL’s mysqlbinlog tool, you’ve probably seen something like the following in the output: “exec_time=0″ What is the exec_time? It seems to be the query’s execution time, but it is not.

In most cases, the exec_time will be similar to the query execution time on the server where the query originally ran. If you’re running replication, then on the replication master, the exec_time will look believable. But on replicas, exec_time can be completely different. Suppose you execute an UPDATE that takes one second to run on the master. This gets logged into the binary log and copied to replicas for replay. Now suppose that you have also set log_slave_updates on the replica. When the statement executes in the replication thread, it will be logged to the replica’s binary log so it can be replicated to other servers in turn.

If you use mysqlbinlog to examine the replica’s binary log, you might see a very different exec_time, such as exec_time=1000, even if the statement did not take 1000 seconds to execute. Why is that?

The reason is that the exec_time is the difference from the statement’s original start timestamp and the time at which it completed executing. On the originating server, that’s accurate to within the tolerance of a one-second timestamp. However, replicas might execute the statement much later. And in those cases, the end timestamp will be much later than the start timestamp on the originating server, thus causing the statement to appear to have taken a long time to execute.

当你是使用MySQL的二进制文件查看工具查看二进制文件的时候，可能会发现里面有一部分输出的内容是:"exec_time=0"，那exec_time是什么意思？这个看起来是语句的执行时间，但这可能并不一定。

在大多情况下，exec_time会和语句真实的执行时间比较接近，但当你在主从环境下执行这个的时候就可能会发生不一定的情况。

但你在从上面设置了log_slave_update=1以后，主上面过来的操作会同样写到从的二进制文件里，那你就会发现，可能在主上面的exec_time=0的情况到从的二进制文件里面就变成了exec_time=1000，这个主要是因为时间的计算是根据主二进制文件里面的语句时间戳进行的计算，那当这个语句在从上面执行以后，时间可能已经过了很久，因此在从上面的exec_time的值计算方式就是：主执行语句的起始时间到从执行完这个语句的结束时间，因此这个exec_time会变得和主差别较大，这个时候我认为是不用担心的，主要根据主上面的exec_time去判断语句的执行时长的就可了。

看了一下博客的评论，里面有一个观点，用这个数也可以大概判断一下语句的执行延迟时长。但这个计算方式已经有MySQL的状态显示出来了，现在看来，当初MySQL开发延迟的时候，会不会是考虑了这种计算方式呢？呵呵。