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

      今天(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 关键字。如: 

?


1

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 语句中,你只能查找一次临时表。例如:下面的就不可用;

    ?


    1

    2

    mysql> SELECT * FROM temp_table, temp_table AS t2;

    ERROR 1137: Can't reopen table: 'temp_table'

  • 如果在一个存储函数里,你用不同的别名查找一个临时表多次,或者在这个存储函数里用不同的语句查找,这个错误都会发生;
  • show tables 语句不会列举临时表;
  • 你不能用 rename 来重命名一个临时表。但是,你可以 alter table 代替:

    ?


    1

    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端配置:

?


1

2

3

4

5

6

7

8

9

[mysqld]  

  #Master start  

  #日志输出地址 主要同步使用  

  log-bin=/var/log/mysql/updatelog

  #同步数据库  

  binlog-do-db=cnb 

  #主机id 不能和从机id重复  

  server-id=1   

  #Master end

slave端配置: 

?


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

[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 

?


1

create temporary table tmp1(id int not null);

b. 插入数据 

?


1

mysql> insert into tmp1(id) values(26);

c. 查看数据 

?


1

2

3

4

5

6

7

mysql> select * from tmp1;            

+----+

| id |

+----+

| 23 |

+----+

1 row in set (0.00 sec)

在从服务器上查看: 
1) 从服务器连接主服务器的状态是否正常;

?


1

show slave status\G

2)查看是否同步了临时表: 

?


1

2

mysql> select * from tmp1;

ERROR 1146 (42S02): Table 'cacti.tmp1' doesn't exist

2. 在主服务器上删除 tmp1: 

?


1

drop table tmp1;

从服务器没有报错!!! 

内存表测试:

主服务器上操作: 
a. 创建内存表 tmp_test2 

?


1

2

3

4

CREATE TABLE tmp_test2 (

  Id int(11) AUTO_INCREMENT,

  name varchar(255)

) ENGINE=MEMORY ;

b. 插入数据 

?


1

2

insert into tmp_test2(id)

values(10);

c. 查看数据 

?


1

2

3

4

5

6

7

mysql>select * from tmp_test2;                          

+------+------+

| id   | name |

+------+------+

|   10 | NULL |

+------+------+

1 row in set (0.00 sec)

在从服务器上查看状态: 
1) 从服务器连接主服务器状态是否正常 

?


1

show slave status\G

2)查看数据 

?


1

2

3

4

5

6

7

mysql> select * from tmp_test2;

+------+------+

| id   | name |

+------+------+

|   10 | NULL |

+------+------+

1 row in set (0.00 sec)

2. 在主服务器上删除 tmp_test2 表: 

?


1

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; 来查看这个线程。 

?


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

# 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 已经断开,但临时表是还存在的。 

?


1

2

3

4

5

6

7

8

9

10

11

12

13

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 的事件。  

?


1

2

3

4

5

6

7

8

#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 目录下。 

?


1

2

-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目录下找到 

?


1

2

-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 里面执行,否则可能导致临时表操作失败。 

时间: 2016-05-11

【转载】MySQL 之临时表和内存表的相关文章

mysql 临时表和内存表创建 查询 删除以及注意事项

mysql教程 临时表和内存表创建 查询 删除以及注意事项 临时表和内存表的engine 不同,临时表默认的是myisam,而内存表是memory ,临时表只对当前会话可见,连接断开时,自动删除! 你不必担心所创建的临时表的名称会和其他会话建立的临时表.或非临时表冲突!注意如果你的临时表和正常表名称相同,正常表会被隐藏--如同全局变量和局部变量那样 创建临时表不会引发通常的commit事务提交 临时表 create temporary table tmp_table 创建一个临时表 create

mysql 临时表、内存表和视图

虚拟表,顾名思义,就是实际上并不存在(物理上不存在),但是逻辑上存在的表.这样说很抽象,还是看一些实际的例子吧.在MySQL中,存在三种虚拟表:临时表.内存表和视图. 一.mysql临时表1.什么是临时表临时表是建立在系统临时文件夹中的表,如果使用得当,完全可以像普通表一样进行各种操作. 临时表的数据和表结构都储存在内存之中,退出时,其所占的空间会自动被释放.2.创建临时表(1)定义字段CREATE TEMPORARY TABLE tmp_table (        name VARCHAR(

MYSQL使用心得(四) 临时表与内存表

mysql5.5性能优化-内存表 内存表分为2种,但共同点是,重起数据库以后,内存中的数据全部丢失,内存表的功能 有部分的限制,有些属性不能像正常表一样使用,所以请大家使用的时候谨慎参照官方文档.下面只是抛砖引玉. 1.临 时表:表建在内存里,数据在内存里 2.内存表:表建在磁盘里,数据在内存里 其中包括2个重要的参数 [mysqld] # 内存表容量 max_heap_table_size=1024M # 临时表容量 tmp_table_size=1024M 建立内存表的时候,在5.5里,需要

MySQL内存表的特性与使用介绍_Mysql

内存表,就是放在内存中的表,所使用内存的大小可通过My.cnf中的max_heap_table_size指定,如max_heap_table_size=1024M,内存表与临时表并不相同,临时表也是存放在内存中,临时表最大所需内存需要通过tmp_table_size = 128M设定.当数据超过临时表的最大值设定时,自动转为磁盘表,此时因需要进行IO操作,性能会大大下降,而内存表不会,内存表满后,会提示数据满错误. 临时表和内存表都可以人工创建,但临时表更多的作用是系统自己创建后,组织数据以提升

MySQL的内存表的基础学习教程_C 语言

内存表,就是放在内存中的表,所使用内存的大小可通过My.cnf中的max_heap_table_size指定,如max_heap_table_size=1024M,内存表与临时表并不相同,临时表也是存放在内存中,临时表最大所需内存需要通过tmp_table_size = 128M设定.当数据超过临时表的最大值设定时,自动转为磁盘表,此时因需要进行IO操作,性能会大大下降,而内存表不会,内存表满后,会提示数据满错误. 临时表和内存表都可以人工创建,但临时表更多的作用是系统自己创建后,组织数据以提升

MySQL内存表的特性与使用介绍

  国内私募机构九鼎控股打造APP,来就送 20元现金领取地址:http://jdb.jiudingcapital.com/phone.html内部邀请码:C8E245J (不写邀请码,没有现金送)国内私募机构九鼎控股打造,九鼎投资是在全国股份转让系统挂牌的公众公司,股票代码为430719,为"中国PE第一股",市值超1000亿元.        ----------------------------------------------------------------------

mysql创建内存表的方法_Mysql

如何创建内存表?创建内存表非常的简单,只需注明 ENGINE= MEMORY 即可: 复制代码 代码如下: CREATE TABLE  `tablename` ( `columnName` varchar(256) NOT NUL) ENGINE=MEMORY DEFAULT CHARSET=latin1 MAX_ROWS=100000000; 注意: 当内存表中的数据大于max_heap_table_size设定的容量大小时,mysql会转换超出的数据存储到磁盘上,因此这是性能就大打折扣了,所

MySQL应用技巧之内存使用线程独享

在这里我们将介绍的是MySQL内存使用上的线程独享,线程独享内存主要用于各客户端连接线程存储各种操作的独享数据,如线程栈信息,分组排序操作,数据读写缓冲,结果集暂存等等,而且大多数可以通过相关参数来控制内存的使用量. 对于任何一个数据库管理系统来说,内存的分配使用绝对可以算的上是其核心之一了,所以很多希望更为深入了解某数据库管理系统的人,都会希望一窥究竟,我也不例外. 从内存的使用方式MySQL 数据库的内存使用主要分为以下两类 线程独享内存 全局共享内存 今天这篇文章暂时先分析 MySQL 中

MySQL中临时表的基本创建与使用教程_Mysql

当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后在这些表运行查询. 创建临时表很容易,给正常的CREATE TABLE语句加上TEMPORARY关键字: CREATE TEMPORARY TABLE tmp_table ( name VARCHAR(10) NOT NULL, value INTEGER NOT NULL ) 临时表将在你连接MySQL期间存在.当