【问题的引入】
让我们先来观察几条非常简单的 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>
|
看到以上语句，你很容易会产生类似于以下的疑问：
- 上述语句在一个 session 中先后创建了两个名为 'tmp' 的 table ，只不过一个是 temporary table ，一个是 normal table 。问题来了：temporary table 为何可以与同名的 normal table 共存？
- 上述语句成功执行了两条 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)
|
至此我们可以得到初步的印象是，同名的 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 或 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));
|
|
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);
}
...
}
|
|
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;
}
...
|
继续跟踪到 ./sql/sql_talbe.cc 中的 mysql_create_table() 函数。开头的注释说的很清楚：
|
1
2
3
|
/*
Database and name-locking aware wrapper for 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);
}
...
|
|
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);
...
|
现在我们回到函数 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))
{
// 如果找到重名的表，那么执行这里的错误处理代码(非原注释)
}
...
|
|
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))
...
|
|
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;
}
...
|
|
1
|
error= write_create_table_bin_log(thd, create_info, internal_tmp_table);
|
|
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.
*/
|
至此，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);
}
...
|
好了，到目前为止我们已经从本质上回答了文章开头提出的两个问题，这样看起来问题已经解决的比较圆满了。但是且慢，我们以上所做的探究全部基于同一台服务器下，如果是分布式的系统，即主从模式下，又会出现什么样的状况呢？下面一节我们继续探究。
【主从模式下 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);
...
|
|
1
2
3
|
...
int4store(key + key_length + 4, thd->variables.pseudo_thread_id);
...
|
|
1
2
3
|
...
thd->thread_id= thd->variables.pseudo_thread_id= thread_id++;
...
|
|
1
2
3
4
5
6
7
8
9
10
|
...
while
(!sql_slave_killed(thd,rli))
{
...
if
(exec_relay_log_event(thd,rli))
{
...
}
}
...
|
|
1
2
3
|
...
thd->variables.pseudo_thread_id= thread_id;
// for temp tables
...
|
【主从模式下 temporary table 可能造成的不同步问题】
现在我们来考虑另外一个问题，即主从模式下 temporary table 可能引起的主从间不同步问 题。
回忆 MySQL 创建 temporary table 过程。该过程除了将 temporary table 信息加入当前线程所 拥有的 temporary table队列之外，还做了一项工作，即在 /tmp 目录下创建了临时数据文件， 如：
|
1
2
|
#sql64d6_18_0.frm
#sql64d6_18_0.ibd (InnoDB下)
|
|
1
2
|
Error
'Table '
test.tmp
' doesn'
t exist
' on query. Default database: '
test
'.
Query: '
insert
into
tmp
values
(SomeValue)'
|
解决这个问题的基本思路就是在 Slave 重启时以某种方式恢复原先内存中的相关信息。其中一种 思路是，在 Slave 创建 temporary table 时，我们额外写一个文件来记录与维护数据文件与客户 端线程 id 、表名、数据库名的对应关系。另外一种思路是，在 Slave 创建 temporary table 时， 我们将相应的 binlog 记录下来，然后在启动的时候重做这些记录。具体的实现这里就不再详细 展开。
