什么是临时表
临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时自动drop临时表。
因此通常情况下,只要创建全局临时表的连接断开,全局临时表即被除去 。
临时表的特性
如何创建临时表
drop table #Tmp --删除临时表#Tmp create table #Tmp --创建临时表#Tmp ( ID int IDENTITY (1,1) not null, --创建列ID,并且每次新增一条记录就会加1 WokNo varchar(50), primary key (ID) --定义ID为临时表#Tmp的主键 ); Select * from #Tmp --查询临时表的数据 truncate table #Tmp --清空临时表的所有数据和约束 #创建临时表 #create TEMPORARY table deparment_aa as select * from department; show tables; select * from deparment_aa;
内存表是什么
临时表与内存表的区别
内存表,就是放在内存中的表,数据放在内存中,表结构定义放在磁盘中,所使用内存的大小可通过My.cnf中的max_heap_table_size指定,如max_heap_table_size=1024M。临时表也是存放在内存中,临时表最大所需内存需要通过tmp_table_size=1024M设定。 当数据超过临时表的最大值设定时,自动转为磁盘表,此时因需要进行IO操作,性能会大大下降,而内存表不会,内存表满后,会提示数据满错误。 临时表和内存表都可以人工创建,但临时表更多的作用是系统自己创建后,组织数据以提升性能,如子查询,临时表在多个连接之间不能共享。
临时表的使用场景是:
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服务器选择即可。
临时表相关配置
tmp_table_size:指定系统创建的内存临时表最大大小;
表的设计原则
使用临时表一般都意味着性能比较低,特别是使用磁盘临时表,性能更慢,因此我们在实际应用中应该尽量避免临时表的使用。
如果实在无法避免,也应该尽量避免使用磁盘临时表。
常见的方法有:
1)创建索引:在ORDER BY或者GROUP BY的列上创建索引,这样可以避免使用临时表;
2)分拆很长的列,可以避免使用磁盘临时表:一般情况下,TEXT、BLOB,大于512字节的字符串,基本上都是为了显示信息,而
如何判断使用了临时表
使用explain查看执行计划,Extra列看到Using temporary就意味着使用了临时表。
查询临时表
select * from #临时表名;
select * from ##临时表名;
删除临时表
drop table #临时表名;
drop table ##临时表名;