技术分享 | MySQL 慢查询记录原理和内容解析

4. 第四部分执行信息

# Query_time: 19.254508  Lock_time: 0.001043  Rows_sent: 0  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 44  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 0

my_b_printf(&log_file,
"# Schema: %s  Last_errno: %u  Killed: %u\n"
"# Query_time: %s  Lock_time: %s  Rows_sent: %llu"
"  Rows_examined: %llu  Rows_affected: %llu\n"
"# Bytes_sent: %lu",
(thd->db().str ? thd->db().str : ""),
                  thd->last_errno, (uint) thd->killed,
                  query_time_buff, lock_time_buff,
(ulonglong) thd->get_sent_row_count(),
(ulonglong) thd->get_examined_row_count(),
(thd->get_row_count_func() > 0)
? (ulonglong) thd->get_row_count_func() : 0,
(ulong) (thd->status_var.bytes_sent - thd->bytes_sent_old)
my_b_printf(&log_file,
"  Tmp_tables: %lu  Tmp_disk_tables: %lu  "
"Tmp_table_sizes: %llu",
                    thd->tmp_tables_used, thd->tmp_tables_disk_used,
                    thd->tmp_tables_size)
snprintf(buf, 20, "%llX", thd->innodb_trx_id);及thd->innodb_trx_id

• Query_time：语句执行的时间及实际消耗时间 。
• Lock_time：包含 MDL lock 和 InnoDB row lock 和 MyISAM 表锁消耗时间的总和及锁等待消耗时间。前面已经进行了描述（实际上也并不全是锁等待的时间只是锁等待包含在其中）。

我们来看看Query_time和Lock_time的源码来源，它们来自于Query_logger::slow_log_write函数如下：

    query_utime= (current_utime > thd->start_utime) ?
(current_utime - thd->start_utime) : 0;
    lock_utime=  (thd->utime_after_lock > thd->start_utime) ?
(thd->utime_after_lock - thd->start_utime) : 0;

下面是数据current_utime 的来源，

current_utime= thd->current_utime();
实际上就是：
while(gettimeofday(&t, NULL) != 0)
{}
  newtime= (ulonglong)t.tv_sec * 1000000+ t.tv_usec;
return newtime;
获取当前时间而已

对于thd->utime_after_lock的获取我已经在前文进行了描述，不再解释。

• Rows_sent：发送给 mysql 客户端的行数，下面是源码中的解释 Number of rows we actually sent to the client

• Rows_examined：InnoDB 引擎层扫描的行数,下面是源码中的解释。(备注栈帧1)

  Number of rows read and/or evaluated for a statement. Used for slow log reporting. An examined row is defined as a row that is read and/or evaluated according to a statement condition, including increate_sort_index(). Rows may be counted more than once, e.g., a statement including ORDER BY could possibly evaluate the row in filesort() before reading it for e.g. update.

• Rows_affected：涉及到修改的话（比如DML语句）这是受影响的行数。

  for DML statements: to the number of affected rows;

  for DDL statements: to 0.

• Bytes_sent：发送给客户端的实际数据的字节数，它来自于(ulong) (thd->status_var.bytes_sent – thd->bytes_sent_old)

• Tmp_tables：临时表的个数。

• Tmp_disk_tables：磁盘临时表的个数。

• Tmp_table_sizes：临时表的大小。

thd->tmp_tables_used
thd->tmp_tables_disk_used
thd->tmp_tables_size

这三个指标增加的位置对应在 free_tmp_table 函数中如下：

  thd->tmp_tables_used++;
if(entry->file)
{
      thd->tmp_tables_size += entry->file->stats.data_file_length;
if(entry->file->ht->db_type != DB_TYPE_HEAP)
          thd->tmp_tables_disk_used++;
}

• InnoDB_trx_id：事物 ID，也就是 trx->id，/*!< transaction id */