MySQL Temporary Table相关问题的探究

标签: 数据库 binlog mysql Temporary Table | 发表时间:2011-07-26 17:01 | 作者:ChenRui comain
出处:http://rdc.taobao.com/blog/cs

问题的引入

让我们先来观察几条非常简单的MySQL语句:

mysql> create temporary table tmp(id int, data char(20));
Query OK, 0 rows affected (0.00 sec)

mysql> create table tmp(id int, data char(20));
Query OK, 0 rows affected (0.00 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 'tmp'

这是丁奇提出的引导性的问题,几条语句看似简单,不过接下来我们提出的一连串问题与进
行的研究可都是围绕它们来的!

看到以上语句,你很容易会产生类似于以下的疑问:

1. 上述语句在一个session中先后创建了两个名为’tmp’的table,只不过一个是temporary
table,一个是normal table。问题来了:temporary table为何可以与同名的normal table
共存?

2. 上述语句成功执行了两条DROP TABLE语句,那么每一条语句操作的对象是哪个table呢?
亦即同名的temporary table与normal table之间的优先级关系是如何的?

很好,有了问题就知道了前进的方向!接下来我们就从这两个问题入手,由浅入深,开始我
们的探索之旅吧!

单机模式下的同名问题与优先级问题的探究

我们不妨从现象入手,先来验证第二个问题的结果究竟如何,即哪个表拥有较高的优先级?
为此我们设计如下的语句:

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。
马上来验证一下:

/* 紧接着之前的代码 */
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与normaltable可以共存?众所周知两个同名的temporary table或
normal table都是不被允许的。我们可以先做出猜想:temporary table与normal table
是存储在不同的位置的。这个猜想对吗?要回答这些问题,我们必须到MySQL的源码中
一探究竟,找寻答案了!

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

我们可以从创建一张表的流程入手,来探究这个过程(以下代码中,如果没有特别注明,
其注释均为原码注释。)

对于语句

create temporary table tmp(id int, data char(20));
create table tmp(id int, data char(20));

定位到./sql/sql_parse.cc中的mysql_execute_command()函数。

  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()函数的实现:

...

      /*
        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()函数。
开头的注释说的很清楚:

/*
  Database and name-locking aware wrapper for mysql_create_table_no_lock(),
*/

这个函数实际上是对mysql_create_table_no_lock()的一个封装,并且处理了一些加锁
机制。我们继续跟踪到同文件的mysql_create_table_no_lock()函数。

...

      /* 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命名的。在该函数内部我们又看到如下一段关键代码:

...

  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(),紧接着刚才的代码:

  /* 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是否已经存在。如果一切
没有问题,那么继续往下执行:

...

  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的实际数据文件。

...

  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队列。继续往下,在函数末尾看到一
句代码:

  error= write_create_table_bin_log(thd, create_info, internal_tmp_table);

进入write_create_table_bin_log()函数,上来就是一段非常清晰的注释:

  /*
    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)

...

 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()的实现代码。

...

  key_length= create_table_def_key(thd, key, table_list, 1);

...

显然create_table_def_key()函数是区分每个temporary table的关键,我们继续看这个函数
内部的细节:

...

    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()函数中有这样一句代码:

...

  thd->thread_id= thd->variables.pseudo_thread_id= thread_id++;

...

在这里,thd->variable.pseudo_thread_id是被初始化为Slave当前线程id的。那么它是何时被
修改的呢?继续看代码:

...

  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()。在该函数中我们看到:

...

    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目录下创建了临时数据文件,
如:

#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端报错,产生类似以下信息:

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记录下来,然后在启动的时候重做这些记录。具体的实现这里就不再详细
展开。

玩的开心!

相关 [mysql temporary table] 推荐:

MySQL Temporary Table相关问题的探究

- comain - 淘宝核心系统团队博客
让我们先来观察几条非常简单的MySQL语句:. 这是丁奇提出的引导性的问题,几条语句看似简单,不过接下来我们提出的一连串问题与进. 看到以上语句,你很容易会产生类似于以下的疑问:. 上述语句在一个session中先后创建了两个名为’tmp’的table,只不过一个是temporary. table,一个是normal table.

MySQL 對 MyISAM、InnoDB 使用 Optimize Table

- - Tsung's Blog
系統用久了, 自然就會有不連續的碎片(fragmented)產生, 以前 Dos 使用 defrag, Windows 使用磁碟重組, 而 MySQL 則是使用 Optimize table.. 以往都是使用: 使用 PHP 對所有 MySQL Database 做 Optimize / Repair 的動作 - 這裡面的那隻程式來跑..

抓取 MySQL Table 的 auto_increment 最大值

- - Tsung's Blog
MySQL 使用 InnoDB 要做 SELECT COUNT(*) 會非常慢, 若是此 Table 符合下述狀況. 很少刪除 (或知道大概會刪多少資料). 有設定 auto_increment. 就可以考慮直接抓取 auto_increment 的值來當參考.. 抓取 MySQL Table auto_increment 的最大值.

mysql的table is read only 解决方法

- - CSDN博客系统运维推荐文章
今天,在centos6下把mysql没法启动的数据库文件夹复制到/usr/local/mysql/var下,结果在修改表时,提示”table isreadonly”的错误. 首先想到的是遇到了权限问题,所以就去/usr/local/mysql/var查看了下,果然所有者不是mysql,权限也不正确.

MySQL 快速複製 Table 的方法

- - Tsung's Blog
MySQL 要複製 整個 Table, 要怎麼做呢?. MySQL 快速複製 Table 的方法. 以下範例都是把 old_table 複製到 new_table.. 先講講結論, 最推薦的作法是下述兩行:. 以下來講講幾種作法 和 優缺點.. MyISAM 的作法若比較暴力點的話, 可以用下述方式做:.

mysql删除大表更快的drop table办法

- 胖岁 - haohtml's blog
曾经发文介绍过,DROP table XXX ,特别是碰到大表时,. 在DROP TABLE 过程中,所有操作都会被HANG住. 这是因为INNODB会维护一个全局独占锁(在table cache上面),直到DROP TABLE完成才释放. 在我们常用的ext3,ext4,ntfs文件系统,要删除一个大文件(几十G,甚至几百G)还是需要点时间的.

解决 MySQL 的 Table is marked as crashed and should be repaired 问题

- - vpsee.com
昨天一位 VPS 客户说他的 WordPress 博客没了,网站可以打开,但是文章都没了,怀疑被黑. 我们登陆客户 VPS 后没发现被黑迹象,然后进入 MySQL 数据库发现 Table ‘./wordpress/wp_posts’ is marked as crashed and should be repaired 错误,因为 wp_posts 表被损坏了,所以 WordPress 的文章都显示不出来:.

Table冻结表头

- - CSDN博客Web前端推荐文章
序号. 内容. 序号. 内容. 作者:zyuc_wangxw 发表于2013-8-20 17:32:14 原文链接. 阅读:36 评论:0 查看评论.

花瓶茶几:Flo Table

- 阳阳 - 爱…稀奇~{新鲜:科技:创意:有趣}
没看过花瓶茶几(Flo Table),你就不知道粗腿原来也能如此优雅:把茶几的一条腿变成了玻璃花瓶,木材的实成与透明玻璃的轻盈,就如此完美地结合在了一起~于是,尽管随手插点桃红柳绿在花瓶中吧,任何一点属于自然的色彩,都能将这个家点缀得充满生气~. 亲爱的,这些东西也会对你胃口:. Felt Stool Bookshelf Table:凳子、书架和茶几.

【分享】jQuery 表格Table插件汇总

- - HTML5研究小组
本文搜集了大量 jQuery 表格插件,帮助 Web 设计者更好地驾御 HTML 表格,你可以对表格进行横向和竖向排序,设置固定表头,对表格进行搜索,对大表格进行分页,对表格进行滚动,拖放操作等等. Flexigrid – Web 2.0 Javscript Grid for jQuery – 可变列宽,自动适应表头宽度,可通过 Ajax 连接 XML 数据源,类似 Ext Grid,但基于 jQuery 因此更轻量小巧.