【转载】MySQL 之临时表和内存表

原创
2013/09/25 23:33
阅读数 2.9K

      今天(2013-10-17)又看了一篇很久以前的博客文章,题目是《 How to eliminate temporary tables in MySQL 》,里面的一些观点和结论摘录如下:
  • to make statement-based replication reliable is eliminate temporary tables.
  • true temporary tables are created with CREATE TEMPORARY TABLE, and internal temporary tables are created internally by MySQL for sorting or processing subqueries.
  • replace temporary tables with real tables in the systems by not naming tables randomly.

=========== 我是分割线 ============

      今天(2013-10-11)看了另外一边博客文章,感觉之前记录的内容似乎有些地方说的不太对。这里的内容来自 MySQL 官网翻译。

MySQL 如何使用内部临时表?

在一些情况下,服务器会在处理 query 的时候创建内部临时表。这种表有两种存在形式:

  • 位于内存中,使用 MEMORY 存储引擎(内存临时表);
  • 位于磁盘上,使用 MyISAM 存储引擎(磁盘临时表)。
      服务器可能在最初创建的是内存临时表,之后当其变大到一定程度时再转变为磁盘临时表。对于服务器何时创建内部临时表或者临时表使用哪种存储引擎,用户没有直接控制的能力。

【服务器可能会创建临时表的场景】
1)ORDER BY 子句和 GROUP BY 子句不同,
例如:ORDERY BY price GROUP BY name;

2)在 JOIN 查询中,ORDER BY 或者 GROUP BY 使用了不是第一个表的列
例如:SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name

3)ORDER BY 中使用了 DISTINCT 关键字
ORDERY BY DISTINCT(price)

4)SELECT 语句中指定了 SQL_SMALL_RESULT 关键字
SQL_SMALL_RESULT 的意思就是告诉 MySQL,结果会很小,请直接使用内存临时表,不需要使用索引排序
SQL_SMALL_RESULT 必须和 GROUP BY、DISTINCT 或 DISTINCTROW 一起使用
一般情况下,我们没有必要使用这个选项,让 MySQL 服务器选择即可。

5)由 FROM 语句中的子查询产生的派生表
6)由于子查询或者  semi-join   materialization 所创建的表

【直接使用磁盘临时表的场景】
  • 表包含 TEXT 或者 BLOB 列;
  • GROUP BY 或者 DISTINCT 子句中包含长度大于 512 字节的列;
  • 使用 UNION 或者 UNION ALL 时,SELECT 子句中包含大于 512 字节的列;
【临时表相关配置】
  • tmp_table_size: 指定系统创建的内存临时表最大大小;
  • max_heap_table_size: 指定用户创建的内存表的最大大小;
注意:最终的系统创建的内存临时表大小是取上述两个配置值的最小值。

      服务器可能在最初创建的是内存临时表,之后当其变大到一定程度时再转变为磁盘临时表。这种表与通过 CREATE TABLE 显式 创建的 MEMORY 内存表是不同的:对于后者,系统变量  max_heap_table_size 决定的是 MEMORY 内存表允许的最大大小,而不会出现向磁盘表转变的动作。

      当服务器创建了内部临时表后(无论是在内存中还是在磁盘上),状态变量  Created_tmp_tables 都会增加。如果服务器创建了临时表在磁盘上(无论是初始创建在磁盘还是后来转化到磁盘), 状态变量  Created_tmp_disk_tables 的值都会增加。


=========== 我是分割线 ============

mysql 复制和临时表 temporary table

当你创建临时表的时候,你可以使用 temporary 关键字。如:
create temporary table tmp_table(name varchar(10) not null,passwd char(6) not null)
      临时表只在当前连接可见,当这个连接关闭的时候,会自动 drop 。这就意味着你可以在两个不同的连接里使用相同的临时表名,并且相互不会冲突,或者使用已经存在的表,但不是临时表的表名。(当这个临时表存在的时候,存在的表被隐藏了,如果临时表被 drop,存在的表就可见了)。创建临时表你必须有 create temporary table 权限。 

下面几点是临时表的限制:
  • 临时表只能用在 memory、myisam、merge 或者 innodb 存储引擎中;
  • 临时表不支持 mysql cluster(簇);
  • 在同一个 query 语句中,你只能查找一次临时表。例如:下面的就不可用;
    mysql> SELECT * FROM temp_table, temp_table AS t2;
    ERROR 1137: Can't reopen table: 'temp_table'
  • 如果在一个存储函数里,你用不同的别名查找一个临时表多次,或者在这个存储函数里用不同的语句查找,这个错误都会发生;
  • show tables 语句不会列举临时表;
  • 你不能用 rename 来重命名一个临时表。但是,你可以 alter table 代替:
    mysql>ALTER TABLE orig_name RENAME new_name;
在数据库复制中使用临时表也有问题,详情参看 Section 16.4.1, “Replication Features and Issues”。

复制和临时表

为了避免出现临时表复制问题,当临时表打开时,不要直接停止 slave 服务。而是用下面的步骤代替:
  1. 使用 stop slave sql_thread 语句;
  2. 使用 show status 查看 Slave_open_temp_tables 的值;
  3. 如果这个值不是 0 ,使用 start slave sql_thread 重启从库 SQL 线程,一会儿后再重复执行这个步骤;
  4. 当这个值是 0 时,使用 mysqladmin shutdown 命令停止 slave 。
       默认,所有的临时表都是被复制的,无论是否匹配 --replicate-do-db、--replicate-do-table 或者 --replicate-wild-do-table,复制临时表都会发生。但是,--replicate-ignore-table 和 --replicate-wild-ignore-table 两个选项是用来忽略临时表的。
      如果你不想复制某些临时表,请使用 --replicate-wild-ignore-table 选项。如 --replicate-wild-ignore-table=foo%.bar% 意思是告诉 slave 线程不要复制匹配以 foo 开头和以 bar 开头的表。

下面是转自网上某人的配置信息:

master端配置:
[mysqld]   
  #Master start   
  #日志输出地址 主要同步使用   
  log-bin=/var/log/mysql/updatelog
  #同步数据库   
  binlog-do-db=cnb  
  #主机id 不能和从机id重复   
  server-id=1    
  #Master end
slave端配置:
[mysqld]    
  #Slave start        
  #从机id,区别于主机id    
  server-id=2   
  #主机ip,供从机连接主机用    
  master-host=192.168.0.24  
  #主机端口    
  master-port=3307   
  #刚才为从机复制主机数据新建的账号    
  master-user=slave     
  #刚才为从机复制主机数据新建的密码    
  master-password=123456   
  #重试间隔时间10秒    
  master-connect-retry=10     
  #需要同步的数据库    
  replicate-do-db=cnb   
  #启用从库日志,这样可以进行链式复制    
  log-slave-updates    
  #从库是否只读,0表示可读写,1表示只读    
  read-only=1   
   
  #只复制某个表    
  #replicate-do-table=tablename                     
  #只复制某些表(可用匹配符)    
  #replicate-wild-do-table=tablename%    
  #只复制某个库    
  #replicate-do-db=dbname   
  #不复制某个表    
  #replicate-ignore-table=tablename   
  #不复制某些表    
  #replicate-wild-ignore-table=tablename%    
  #不复制某个库    
  #replicate-ignore-db=dbname   
  #Slave end


=========== 我是分割线 ============

基于mysql主从复制测试对临时表和内存表的支持


临时表测试:

1. 在主服务器上
a. 创建临时表 tmp1
create temporary table tmp1(id int not null);
b. 插入数据
mysql> insert into tmp1(id) values(26);
c. 查看数据
mysql> select * from tmp1;             
+----+
| id |
+----+
| 23 | 
+----+
1 row in set (0.00 sec)

在从服务器上查看:
1) 从服务器连接主服务器的状态是否正常;
show slave status\G
2)查看是否同步了临时表:
mysql> select * from tmp1;
ERROR 1146 (42S02): Table 'cacti.tmp1' doesn't exist

2. 在主服务器上删除 tmp1:
drop table tmp1;
从服务器没有报错!!!


内存表测试:

主服务器上操作:
a. 创建内存表 tmp_test2
CREATE TABLE tmp_test2 ( 
  Id int(11) AUTO_INCREMENT, 
  name varchar(255) 
) ENGINE=MEMORY ;
b. 插入数据
insert into tmp_test2(id)
values(10);
c. 查看数据
mysql>select * from tmp_test2;                           
+------+------+
| id   | name |
+------+------+
|   10 | NULL | 
+------+------+
1 row in set (0.00 sec)

在从服务器上查看状态:
1) 从服务器连接主服务器状态是否正常
show slave status\G
2)查看数据
mysql> select * from tmp_test2;
+------+------+
| id   | name |
+------+------+
|   10 | NULL | 
+------+------+
1 row in set (0.00 sec)
2. 在主服务器上删除 tmp_test2 表:
drop table tmp_test2;
之后会发现,从服务上 tmp_test2 表也删除了,从服务器连接主服务器状态正常。

注:以上实验不能说明临时表不会在主从之间被复制、内存表会在主从之间复制。因为临时表在主从之间是否会被复制有一些前提条件要约束的!


=========== 我是分割线 ============

临时表在MySQL的复制中的处理

背景:在应用过程中,同事直接关闭了作为 slave 的 MySQL server 导致了临时表问题。

      在 MySQL5.1 手册(6.7. 复制特性和已知问题)中提到,关闭 slave 的正确流程(个人认为在 4 步骤只启动slave_SQL 线程好一些):
  1. 执行 STOP SLAVE 语句。
  2. 使用 SHOW STATUS 检查 slave_open_temp_tables 变量的值。
  3. 如果值为 0,使用 mysqladmin shutdown 命令关闭从服务器。
  4. 如果值不为 0,用 START SLAVE 重启从服务器线程。
  5. 后面再重复该程序看下次的运气是否好一些。
slave_open_temp_tables 的值显示,当前 slave 创建了多少临时表。

提出几个问题:
  • 关掉slave的mysqld, 那临时表肯定是不存在了,这样再次start slave,slave_sql 线程执行bin-log时,肯定会出现找不临时表的错误,
  • 这就为什么手册中会提出以上操作流程了,这个问题容易理解。
  • 众所周知,MySQL临时表只是当前connection有效(没有全局临时表),当connection断开,此临时也就会被删除,也就不存在了。
  • MySQL 5.1的replication,slave的sql线程只有一个,那stop slave后,slave_sql_thread也就停止了,那在Slave上创建的临时表应该随之删除,
  • 但从上面步骤来看,说明Stop slave后, 临时表还是存在的,这是为什么呢?
  • 如果Slave不停止,那由slave创建的临时是如何正常删除的? 它们在slave上的存储形式又是怎么样的?
以下简单分析一下 2,3 问题。
分析:
1,临时表只对当前会话可见,连接断开时,自动删除!
2,查看临时表,在Master的binlog中的记录形式:

2.1 MySQL 对临时表的复制,如果在 mixed 的 binlog_format 情况下,会以 Statement 的形式记录到 binlog中,当然也可以用 Row 形式 ,因为临时表是基于 Session 的(也可以说是 Connection 的),所以在复制中,MySQL 会把线程 ID 添加到临时表操作的事件中 ,此时的临时表是属于某个正在运行的 Thread 。 通过 mysqlbinlog 来查看 binlog ,可以看到事件上绑定了 thread_id=297 就是这个临时表的 宿主线程,当然你也可以用 Show processlist; 来查看这个线程。
# at 106 
#120318 1:42:30 server id 1 end_log_pos 291 Query thread_id=297 exec_time=0 error_code=0 
use rep/*!*/; 
SET TIMESTAMP=1332006150/*!*/; 
SET @@session.pseudo_thread_id=297/*!*/; 
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; 
SET @@session.sql_mode=0/*!*/; 
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; 
/*!\C latin1 *//*!*/; 
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; 
SET @@session.lc_time_names=0/*!*/; 
SET @@session.collation_database=DEFAULT/*!*/; 
create temporary table cache2( id int unsigned not null, value char(10) not null default '', primary key(id) )engine=myisam 
/*!*/; 
show processlist; 
| 297 | root | localhost | rep | Query | 0 | NULL | show processlist
2.2 从 Master 的 binlog 可以看到,有一个 SET @@session.pseudo_thread_id=297,这个记录 salve 的Sql_thread 在执行此 binlog 时, 会创建一个 id 号为 297 的"伪线程", 这样在 slave 上创建的此临时表 cache2的宿主线程就此伪线程。

2.3 当 stop slave 后,Slave_SQL 线程已经关闭,但此时在 Slave 的临时表是还存在的,可以通过在 Slave 上查看 Status 变量 Slave_open_temp_tables,其实是不为 0 的,也就说由 Master 复制来的临时表还存在,因为这些临时表是所属于 Master 上创建临时表的 Thread 的 Thread_ID 对应的 pseudo_thread,所以虽然Slave_SQL connection 已经断开,但临时表是还存在的。
mysql> show status like '%slave%'; 
+----------------------------+-------+ 
| Variable_name | Value | 
+----------------------------+-------+ 
| Com_show_slave_hosts | 0 | 
| Com_show_slave_status | 0 | 
| Com_slave_start | 0 | 
| Com_slave_stop | 0 | 
| Slave_open_temp_tables | 3 | 
| Slave_retried_transactions | 0 | 
| Slave_running | ON | 
+----------------------------+-------+ 
7 rows in set (0.00 sec)
对于问题 2, 为何 slave sql thread 停掉后,临时表还存在的原因。

3, Slave 中的临时是如何删除的呢?
      当在 Master 上的、创建此临时表的 Session 断开后,binlog 会记录一个 Drop 临时表的事件, 这样 Slave 对应的临时表也就被删除了,可以查看临时的状态变量可得。从下面可以看,在我测试环境中 Master 上 thread_id=297 的这个 connection,一共创建了 3 个临时表,当退出 mysql 后,Master 的 binlog 中会记录一个 Drop temporary table 的事件。 
#120318 1:45:53 server id 1 end_log_pos 734 Query thread_id=297 exec_time=0 error_code=0 
SET TIMESTAMP=1332006353/*!*/; 
/*!\C utf8 *//*!*/; 
SET @@session.character_set_client=33,@@session.collation_connection=8,@@session.collation_server=8/*!*/; 
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `cache3`,`cache2`,`cache` 
/*!*/; 
DELIMITER ; 
# End of log file
当 slave 的 slave_sql_thread 执行此事件,也就把刚才创建的临时表删除了。

4, Slave 创建的临时表放在哪里呢?
      MySQL 创建的临时表的文件,其实是放在 show variables like 'tmp_dir' 这个变量指定的目录下。 默认情况是下在 /tmp 目录下。
-rw-rw---- 1 mysql mysql 98304 Mar 23 05:39 #sql2625_18_0.ibd 
-rw-rw---- 1 mysql mysql 8586 Mar 23 05:39 #sql2625_18_0.frm
同时也会在slave上的/tmp目录下找到
-rw-rw---- 1 mysql mysql 8586 Mar 24 18:28 #sqld0b_7_2.frm
-rw-rw---- 1 mysql mysql 98304 Mar 24 18:28 #sqld0b_7_2.ibd
也可以根据mysqld打开的文件来查看。

5,关于临时表有两个问题:
5.1 在重新启动 Slave 的 mysqld 服务时,Stop Slave 后,一定要检查 Slave_open_temp_tables 这个状态值是否已经是 0,如果不是, 要重新 start slave, 再 stop slave,查看,直接是 0 后,才 stop mysql 。因为 mysql 重新启动后,在 Slave 上的所有临时表都没有了,这样重新进行复制时, 后面还有对临时表的操作的 binlog 事件,因为 Slave 上的临时表已不存在,此时肯定会出错了。
5.2 在用 binlog 进行 point_in_time 恢复数据库时,一定要注意,把所有的 binlog 放在同一个 session 里面执行,否则可能导致临时表操作失败。










展开阅读全文
打赏
0
2 收藏
分享
加载中
更多评论
打赏
0 评论
2 收藏
0
分享
在线直播报名
返回顶部
顶部