一、MySQL如何使用内部临时表?
在某些情况下,服务器会在处理query的时候组建内部临时表,这种表有两种存在形式:
1)、位于内存中,使用的是MEMORY存储引擎(内存临时表)
2)、位于磁盘上,使用MyISAM存储引擎(硬盘临时表)
服务器可能在最初创建的是内存临时表,之后当其变大到一定程度时再转变为磁盘临时表。对于
服务器什么时候创建内部临时表或者临时表使用哪种存储引擎,用户不能直接控制。
二、那么服务器创建临时表的场景有哪些呢?
1、order by子句和group by子句不同
例如:order by price group by
name
2、在多表联合查询中,order by或者group
by使用了不是第一个表的列
例如:select * from T1,T2 order by
T1.price group by T2.name
3、order by中使用了distinct关键字
例如:order by distinct(price)
4、from语句中的子查询产生的派生表
5、select
语句中指定了SQL_SMALL_RESULT关键字
SQL_SAMLL_RESULT的意思是告诉MySQL,结果会很小,请直接使用内存临时表
SQL_SAMLL_RESULT必须和GROUP
BY、DISTINCT或者DISTINCTROW一起使用
一般情况下,我们都不会使用SQL_SAMLL_RESULT这个选项,让服务器自己选择即可。
三、临时表的相关配置参数
1)、tmp_table_size:指定系统创建的内存临时表最大大小;
2)、max_heap_table_size:指定用户创建的内存表最大大小;
注意:最终系统创建的内存临时表大小是取上述两个配置值中的最小值。
服务器最初创建的是内存临时表,后面当其变大到一定程度时再转变为磁盘临时表。
由服务器创建的内部临时表和通过create
table显示创建的memory内存表是不同的。
对于使用create table
显示创建的memory内存表允许的大小是由max_heap_table_size
决定的。
当服务器创建了内部临时表(无论是在内存中的还是在磁盘上的),状态变量created_tmp_tables
值都会增加。如果服务器创建了临时表在磁盘上(无论是初始创建在磁盘还是后来转化到磁盘),
状态变量created_tmp_disk_tables的值都会增加。
四、MySQL复制和临时表temporary table
1)、用户创建临时表需要有create temporary
table权限,使用temporary关键字。如:
create temporary table
tmp_table(name varchar(10) not null,sex char(1) not null);
2)、create temporary
table这种方式创建的临时表只在当前连接可见,当这个连接关闭的时候,会自动drop掉。
这意味着你可以在两个不同的连接里使用相同的临时表名,并且相互不会冲突。
你也可以创建一个和当前存在的非临时表的表名字一样的临时表,当临时表存在的时候,存在的非临时表会被隐藏,
如果临时表被drop了,那么存在的非临时表就可见了。
五、临时表的限制
1)、临时表只能用在memory、myisam、merge或者innodb存储引擎中;
2)、临时表不支持mysql cluster;
3)、在同一个query中,你只能查找一次临时表,例如:
select * from temp_table,temp_table
AS t2;
会产生报错:can't reopen
table:'temp_table'
4)、show tables不能显示出临时表;
5)、不能使用rename重命名临时表,可以使用alter
table来替代:
alter table T1 RENAME T2