【转载】MySQL Temporary Table 相关问题的探究

2022-05-26 07:52:22


【问题的引入】 
      让我们先来观察几条非常简单的 MySQL 语句：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> create temporary table tmp(id int, data char(20));
Query OK, 0 rows affected (0.01 sec)
 
mysql> create table tmp(id int, data char(20));
Query OK, 0 rows affected (0.01 sec)
 
mysql> drop table tmp;
Query OK, 0 rows affected (0.00 sec)
 
mysql> drop table tmp;
Query OK, 0 rows affected (0.00 sec)
 
mysql> drop table tmp;
ERROR 1051 (42S02): Unknown table 'test.tmp'
mysql>
      这是丁奇提出的引导性的问题，几条语句看似简单，不过接下来我们提出的一连串问题与进行的研究可都是围绕它们来的！ 
看到以上语句，你很容易会产生类似于以下的疑问： 
  1. 上述语句在一个 session 中先后创建了两个名为 'tmp' 的 table ，只不过一个是 temporary table ，一个是 normal table 。问题来了：temporary table 为何可以与同名的 normal table 共存？
  2. 上述语句成功执行了两条 DROP TABLE 语句，那么每一条语句操作的对象是哪个 table 呢？亦即同名的 temporary table 与 normal table 之间的优先级关系是如何的？
很好，有了问题就知道了前进的方向！接下来我们就从这两个问题入手，由浅入深，开始我们的探索之旅吧！ 

【单机模式下的同名问题与优先级问题的探究】 
      我们不妨从现象入手，先来验证第二个问题的结果究竟如何，即哪个表拥有较高的优先级？ 
为此我们设计如下的语句：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
mysql> create temporary table tmp(id1 int, data1 char(20));
Query OK, 0 rows affected (0.00 sec)
 
mysql> describe tmp;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id1   | int(11)  | YES  |     | NULL    |       |
| data1 | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> insert into tmp values(1, "Some");
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from tmp;
+------+-------+
| id1  | data1 |
+------+-------+
|    1 | Some  |
+------+-------+
1 row in set (0.00 sec)
 
mysql> create table tmp(id2 int, data2 char(20));
Query OK, 0 rows affected (0.00 sec)
 
mysql> describe tmp;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id1   | int(11)  | YES  |     | NULL    |       |
| data1 | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> insert into tmp values(2, "Some");
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from tmp;
+------+-------+
| id1  | data1 |
+------+-------+
|    1 | Some  |
|    2 | Some  |
+------+-------+
2 rows in set (0.00 sec)
      以上语句做的工作很简单：先创建一个名为 'tmp' 的 temporary table ，并 insert 一个值；之后创建一个名为 'tmp' 的 normal table ，也 insert 一个值。最终 select 时发现，两次 insert 操作均作用于 temporary table 。 
      至此我们可以得到初步的印象是，同名的 temporary table 与 normal table 共存时， temporary table 具有较高的优先级。但是别忘了还存在另一种情况：先创建的表总有着较高的优先级。这个猜想是很容易来验证它的对错的，我们只需将刚才的创建表的顺序调换一下即可。这里就不再重复代码，直接给出结果：即使 temporary table 在 normal table 之后创建，诸如 select，insert，update 等操作仍然优先作用于 temporary table 之上。于是我们可以进一步猜测 drop 表的时候，先 drop 的也是 temporary table 。马上来验证一下： 
/* 紧接着之前的代码 */ 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> drop table tmp;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from tmp;
Empty set (0.01 sec)
 
mysql> describe tmp;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id2   | int(11)  | YES  |     | NULL    |       |
| data2 | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> drop table tmp;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show tables;
Empty set (0.00 sec)
 
mysql> describe tmp;
ERROR 1146 (42S02): Table 'test.tmp' doesn't exist
      没问题吧！到这里我们已经从现象得出了 初步的结论 ：在同一个 session 下同名的 temporary table 与 normal table 共存时，temporary table 总是优先被操作的。那么我们可以更进一步提问：为什么 temporary table 的优先级会高于normal table 呢？ 
      而且别忘了在本段开始时我们还提出了一个问题：为什么在同一 session 下同名的 temporary table 与 normal table 可以共存？众所周知两个同名的 temporary table 或 normal table 都是不被允许的。我们可以先做出猜想：temporary table 与normal table 是存储在不同的位置的。这个猜想对吗？要回答这些问题，我们必须到 MySQL 的源码中一探究竟，找寻答案了！ 

(我插几句：作为一个不折不扣的 MySQL 菜鸟，刚拿到 MySQ L源码时我就像拿到了天书，除了膜拜之外根本不知道从何入手。经过一段时间的摸爬滚打，我高兴的发现我终于窥得了其中的端倪，并深感“任务驱动+gdb”是上手的好方法。MySQL 完整源码可以从以下地址下载：http://dev.mysql.com/downloads/) 

      我们可以从创建一张表的流程入手，来探究这个过程(以下代码中，如果没有特别注明，其注释均为原码注释。)。 对于语句 
?
1
2
create temporary table tmp(id int, data char(20));
create table tmp(id int, data char(20));
定位到 ./sql/sql_parse.cc 中的 mysql_execute_command() 函数。 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
case SQLCOM_CREATE_TABLE:
  {
    ...
    if ((res= create_table_precheck(thd, select_tables, create_table)))
      goto end_with_restore_list;
      ...
      /* regular create */
      if (create_info.options & HA_LEX_CREATE_TABLE_LIKE)
        res= mysql_create_like_table(thd, create_table, select_tables,
                                     &create_info);
      else
      {
        res= mysql_create_table(thd, create_table->db,
                                create_table->table_name, &create_info,
                                &alter_info, 0, 0);
      }
      ...
  }
首先我们查看同文件中 create_table_precheck() 函数的实现： 
?
1
2
3
4
5
6
7
8
9
10
11
12
...
      /*
        For temporary tables we don't have to check if the created table exists
      */
      if (!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE) &&
          find_table_in_global_list(tables, create_table->db,
                                    create_table->table_name))
      {
    error= FALSE;
        goto err;
      }
...
      而 find_table_in_global_list() 函数实质上调用了 ./sql/sql_base.cc 文件中的 find_table_in_list() 函数。这个函数的功能就是去内存中的全局 table list 中遍历，确认是否已有同名的 normal table 存在。注意，对于 temporary table ，到这里为止是不做重名检查的。 
      继续跟踪到 ./sql/sql_talbe.cc 中的 mysql_create_table() 函数。开头的注释说的很清楚：
?
1
2
3
/*
  Database and name-locking aware wrapper for mysql_create_table_no_lock(),
*/
      这个函数实际上是对 mysql_create_table_no_lock() 的一个封装，并且处理了一些加锁机制。我们继续跟踪到同文件的 mysql_create_table_no_lock() 函数。
?
1
2
3
4
5
6
7
8
9
10
11
12
13
...
  /* Check if table exists */
  if (create_info->options & HA_LEX_CREATE_TMP_TABLE)
  {
    path_length= build_tmptable_filename(thd, path, sizeof(path));
    create_info->table_options|=HA_CREATE_DELAY_KEY_WRITE;
  }
  else
  {
    path_length= build_table_filename(path, sizeof(path) - 1, db, alias, reg_ext,
                                      internal_tmp_table ? FN_IS_TMP : 0);
  }
...
      这里我们看到了一个关键函数 build_tmptable_filename() ，它位于 ./sql/sql_table.cc 文件中，这个函数是为 temporary table 命名的。在该函数内部我们又看到如下一段关键代码：
?
1
2
3
4
5
...
  my_snprintf(p, bufflen - (p - buff), "/%s%lx_%lx_%x%s",
              tmp_file_prefix, current_pid,
              thd->thread_id, thd->tmp_table++, reg_ext);
...
      有了以上这段代码，temporary table 的命名规则就非常清楚了，其中 current_pid 为 16 进制形式，thd->thread_id 是 Client 的线程序号，thd->tmp_table 就是临时表序号了，而 reg_ext 就是形如 *.frm 这样的后缀。 
      现在我们回到函数 mysql_create_table_no_lock() ，紧接着刚才的代码：
?
1
2
3
4
5
6
7
/* Check if table already exists */
  if ((create_info->options & HA_LEX_CREATE_TMP_TABLE) &&
      find_temporary_table(thd, db, table_name))
  {
    // 如果找到重名的表，那么执行这里的错误处理代码(非原注释)
  }
...
      在上面这段代码中我们又看到了一个关键函数 find_temporary_table() ，这个函数内部是大有文章的，它会去tmp_table list 中去遍历并检查 temporary table 是否已经存在。如果一切没有问题，那么继续往下执行：
?
1
2
3
4
5
6
7
...
 
  if (rea_create_table(thd, path, db, table_name,
                       create_info, alter_info->create_list,
                       key_count, key_info_buffer, file))
 
...
      这里我们可以看到 rea_create_table() 函数的功能是创建 normal table 的实际数据文件。 
?
1
2
3
4
5
6
7
8
9
10
11
12
...
  if (create_info->options & HA_LEX_CREATE_TMP_TABLE)
  {
    /* Open table and put in temporary table list */
    if (!(open_temporary_table(thd, path, db, table_name, 1)))
    {
      (void) rm_temporary_table(create_info->db_type, path);
      goto unlock_and_end;
    }
    thd->thread_specific_used= TRUE;
  }
...
      上面这段代码是对 temporary table 操作的，其中 open_temporary_table() 函数打开一个 temporary table 并将其加入 thd->temporary_table 队列。继续往下，在函数末尾看到一句代码：
?
1
error= write_create_table_bin_log(thd, create_info, internal_tmp_table);
      进入 write_create_table_bin_log() 函数，上来就是一段非常清晰的注释： 
?
1
2
3
4
5
6
7
/*
    Don't write statement if:
    - It is an internal temporary table,
    - Row-based logging is used and it we are creating a temporary table, or
    - The binary log is not open.
    Otherwise, the statement shall be binlogged.
   */
      已经说得很明白了，如果是 内部创建的 temporary table 或者 Row-based binlog 模式下 创建 temporary table 或者 binlog 功能未开启 ，那么不写 binlog ，其他情况下都会写。 
      至此，MySQL 一个典型的创建表的流程就走完了。总结上述代码，我们可以回答第一个问题，也就是同名 normal table 与 temporary table 共存问题。现在我们知道，normal table 与 temporary table 保存的位置是不同的，temporary table 保存在 thd->temporary_table 队列中，而 normal table 是保存在全局的队列中的，这样同名的 normal table 与temporary table 就可以共存。并且，temporary table 是相对于 session 的，因为 session 结束后相应的线程就被回收了，那么对应于该线程的 temporary table 也就被释放了。更进一步，从 temporary table 的命名规则我们可以看到，每个temporary table 都对应着独特的客户端线程 id ，那么显然各个 Client 之间同名的 temporary table 是允许共存的。而normal table 显然是在任何情况下都不允许同。 

      为了回答第二个问题，即优先级问题，我们只需要看一下 drop 一个表的过程即可，其他操作的原理也是类似的。这里我们就不再像刚才那么详细的一步步分析源码，直接给出关键代码(位于函数 mysql_rm_table_part2() 中，该函数位于 ./sql/sql_table.cc) 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
...
 error= drop_temporary_table(thd, table); // 这里删除临时表(非原注释)
...
      error= ha_delete_table(thd, table_type, path, db, table->table_name,
                             !dont_log_query); // 这里删除表的内容和索引(非原注释)
...
    /* Delete the table definition file */
    strmov(end,reg_ext);
        // 以下删除表的定义文件(非原注释)
    if (!(new_error=my_delete(path,MYF(MY_WME))))
        {
      some_tables_deleted=1;
          new_error= Table_triggers_list::drop_all_triggers(thd, db,
                                                            table->table_name);
        }
...
      从以上代码我们不难看出，drop 表的过程总是先走 temporary table ，再走 normal table 的。这也就解释了为何 temporary table 有着比 normal table 更高的优先权。 

      好了，到目前为止我们已经从本质上回答了文章开头提出的两个问题，这样看起来问题已经解决的比较圆满了。但是且慢，我们以上所做的探究全部基于同一台服务器下，如果是分布式的系统，即主从模式下，又会出现什么样的状况呢？下面一节我们继续探究。 

【主从模式下 temporary table 机制的探究】 
      首先我们要说明的是 MySQL 主从备份的实现机制。我们知道 MySQL 的众多日志类型中有一种为 binlog 日志类型， 凡是涉及到修改数据库的操作都会被记录到binlog日志中 。binlog 日志本身又分为两种记录方式：Statement-based 方式，Row-based 方式(Mixed 方式可以视为这两种方式的混合)。在主从模式下，某个特定的分布式服务器群中有两种服务器：Master(主服务器)与 Slave(从服务器)。Master 方将自己的数据修改痕迹以某种方式记录在本机的 binlog 文件中，当有 Slave 连接到 Master 时，Master 会启动 Binlog dump 线程来将本地的 binlog 内容发送给 Slave 方。此时 Slave 方会启动两个线程：Slave I/O 线程和 Slave SQL 线程。Slave I/O 线程读取从 Master 的 Binlog dump 线程发送过来的 binlog内容，并将其写入本机的 Relay log 中。Slave SQL 线程则从本地的 Relay log 读取并且执行需要更新的事件。更具体的实现与配置细节可以参考官方文档：http://dev.mysql.com/doc/refman/5.1/en/replication.html 

      注意到 Slave 方执行事件的线程只有一个，那就是 Slave SQL 线程。想一想按照我们目前的理 解，会出现怎样的问题？回忆刚才的 MySQL temporary table 命名规则，其中有一项是线程  id 。再回忆刚才我们说到，由于 temporary table是相对于 session 的，于是不同的 Client 可 以创建同名的 temporary table 。问题来了：将这个情景移到主从模式下，Master 方同时连 接了两个 Client ，每一个 Client 各自创建了一个名为 a 的 temporary table 。我们假设此时  Master 的binlog 模式被设置为 Statement-based ，那么这两个建表事件都会被写入 binlog 。 现在 Slave I/O 线程检测并读取了这两个事件，Slave SQL 线程要执行这两个事件了。按照 我们的想法，此时 Slave 是不能区分这两个 temporary table 的，因为线程 id 相同！ 

      但是经过实际验证，MySQL 能处理这个问题，而并没有像我们预想的那样会报错。那么 MySQL  内部是如何处理的呢？让我们再仔细读一下建表函数 mysql_create_table_no_lock() 中的检 查 temporary table 名字冲突的函数find_temporary_table() 的实现代码。 
?
1
2
3
...
  key_length= create_table_def_key(thd, key, table_list, 1);
...
      显然 create_table_def_key() 函数是区分每个 temporary table 的关键，我们继续看这个函数 内部的细节： 
?
1
2
3
...
    int4store(key + key_length + 4, thd->variables.pseudo_thread_id);
...
      这里我们看到一个关键信息：thd->variables.pseudo_thread_id 。如果使用 gdb 调试，我们发 现在find_temporary_table() 函数中 thd->variables.pseudo_thread_id 的值等于 Relay-log 中 的线程 id ，也就是 Master 的binlog 中记录 Client 的线程 id 的值。然而注意到 Slave SQL 线程初 始化函数 handle_slave_sql() 中调用的 init_slave_thread() 函数中有这样一句代码：
?
1
2
3
...
  thd->thread_id= thd->variables.pseudo_thread_id= thread_id++;
...
      在这里，thd->variable.pseudo_thread_id 是被初始化为 Slave 当前线程 id 的。那么它是何时被 修改的呢？继续看代码：
?
1
2
3
4
5
6
7
8
9
10
...
  while (!sql_slave_killed(thd,rli))
  {
    ...
    if (exec_relay_log_event(thd,rli))
    {
      ...
    }
  }
...
      以上代码进入了执行 relay log 的循环。exec_relay_log_event() 中调用了函数  apply_event_and_update_pos() ，而这个函数中调用了 ev->apply_event() ，最终调用了  Query_log_event::do_apply_event() 。在该函数中我们看到：
?
1
2
3
...
    thd->variables.pseudo_thread_id= thread_id;  // for temp tables
...
就是在这里，thd->variables.pseudo_thread_id 已经被置为我们想要看到的值了。很神奇吧！ 

【主从模式下 temporary table 可能造成的不同步问题】 
      现在我们来考虑另外一个问题，即主从模式下 temporary table 可能引起的主从间不同步问 题。 
      回忆 MySQL 创建 temporary table 过程。该过程除了将 temporary table 信息加入当前线程所 拥有的 temporary table队列之外，还做了一项工作，即在 /tmp 目录下创建了临时数据文件， 如： 
?
1
2
#sql64d6_18_0.frm
#sql64d6_18_0.ibd (InnoDB下)
      考虑以下情形：Master 机上创建了一个 temporary table ，并且此时 binlog 模式为  Statement-based 。于是 Slave 上读到了这个事件，并且在 Slave上 也同步了这个操作，即同样 建立了一个 temporary table 。此时由于某种原因，Slave 突然意外重启。我们知道服务器 重启会导致所有 /tmp 文件夹下的数据文件被清空，那么在 Slave 上，原先的 temporary table  不复存在。但是此时 Master 上的原始的 temporary table 还是好好的！这样，如果我们在  Master 上做任何对该temporary table 上的修改操作都会引起 Slave 端报错，产生类似以下信息： 
?
1
2
Error 'Table 'test.tmp' doesn't exist' on query. Default database: 'test'.
Query: 'insert into tmp values(SomeValue)'
      我们知道在 Slave Server 关闭后直到重启前，/tmp 目录下的数据文件都是存在的。问题的本质 在于：Slave Server 关闭后，内存中的 temporary table 链表被回收，导致 /tmp 下的数据文件 没有对应的数据结构，那么我们也就无从知晓对应的创建该表的 Client 到底是哪一个。 

      解决这个问题的基本思路就是在 Slave 重启时以某种方式恢复原先内存中的相关信息。其中一种 思路是，在 Slave 创建 temporary table 时，我们额外写一个文件来记录与维护数据文件与客户 端线程 id 、表名、数据库名的对应关系。另外一种思路是，在 Slave 创建 temporary table 时， 我们将相应的 binlog 记录下来，然后在启动的时候重做这些记录。具体的实现这里就不再详细 展开。 


原文地址：http://ace105.blog.51cto.com/639741/730268

上一篇：Spring Boot（05）——SpringApplication介绍


下一篇：松下再度发力欧洲 推出紧凑型295W光伏组件