mysql中的临时表利用方式.docx
《mysql中的临时表利用方式.docx》由会员分享，可在线阅读，更多相关《mysql中的临时表利用方式.docx（9页珍藏版）》请在冰豆网上搜索。
mysql中的临时表利用方式
MySQL中的临时表利用方式
当工作在超级大的表上时，你可能偶尔需要运行很多查询取得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后多这些表运行查询。
当工作在非常大的表上时，你可能偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后多这些表运行查询。
创建临时表很容易，给正常的CREATETABLE语句加上TEMPORARY关键字:
CREATETEMPORARYTABLEtmp_table（
nameVARCHAR（10）NOTNULL,
valueINTEGERNOTNULL
）
临时表将在你连接MySQL期间存在。
当你断开时，MySQL将自动删除表并释放所用的空间。
固然你可以在仍然连接的时候删除表并释放空间。
DROPTABLEtmp_table
若是在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在，临时表将有必要屏蔽（隐藏）非临时表tmp_table。
如果你声明临时表是一个HEAP表，MySQL也允许你指定在内存中创建它:
CREATETEMPORARYTABLEtmp_table（
nameVARCHAR（10）NOTNULL,
valueINTEGERNOTNULL
）TYPE=HEAP
因为HEAP表存储在内存中，你对它运行的查询可能比磁盘上的临时表快些。
但是，HEAP表与一般的表有些不同，且有自身的限制。
详见MySQL参考手册。
正如前面的建议，你应该测试临时表看看它们是不是真的比对大量数据库运行查询快。
如果数据很好地索引，临时表可能一点不快
长处一个链接各个query彼此隔离不影响 droptableifexists tmp_mytable
文章主要讲述的是Mysql临时表的具体利用方案，并提示测试Mysql临时表查看它们是不是真的比对大量数据库的运行查询要快。
若是相关的数据很好地索引，临时表可能一点不快。
标签：
Mysql
当工作在超级大的表上时，你可能偶尔需要运行很多查询取得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后多这些表运行查询。
创建临时表很容易，给正常的CREATETABLE语句加上TEMPORARY关键字：
1.CREATETEMPORARYTABLEtmp_table（nameVARCHAR（10）NOTNULL,valueINTEGERNOTNULL）
临时表将在你连接MySQL期间存在。
当你断开时，MySQL将自动删除表并释放所用的空间。
固然你可以在仍然连接的时候删除表并释放空间。
1.DROPTABLEtmp_table
如果在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在，Mysql临时表将有必要屏蔽（隐藏）非临时表tmp_table。
如果你声明临时表是一个HEAP表，MySQL也允许你指定在内存中创建它：
1.CREATETEMPORARYTABLEtmp_table（nameVARCHAR（10）NOTNULL,valueINTEGERNOTNULL）TYPE=HEAP
因为HEAP表存储在内存中，你对它运行的查询可能比磁盘上的临时表快些。
但是，HEAP表与一般的表有些不同，且有自身的限制。
详见MySQL参考手册。
正如前面的建议，你应该测试临时表看看它们是否真的比对大量数据库运行查询快。
如果数据很好地索引，Mysql临时表可能一点不快。
Mysql：
临时表、表变量
在mysql中没有表变量这一概念！
mysql有临时表：
createtemporarytableifnotexists{表概念} --关键字”temporary“指示mysql创建会话级别的临时表。
*临时表只对当前会话可见，连接断开时，自动删除！
*你没必要担忧所创建的临时表的名称会和其他会话成立的临时表、或非临时表冲突！
注意如果你的临时表和正常表名称相同，正常表会被隐藏——犹如全局变量和局部变量那样
*创建临时表不会引发通常的commit事务提交
使用临时表的诸多限制
*引擎类型只能是：
memory（heap）、myisam、merge、innodb
*不支持mysqlcluster
*同一个查询语句中只能引用一次！
如SELECT*FROMTP_TABLE,TP_TABLEASALIAS_NAME; 是错误的
*
*同一个用户存储函数中只能引用一次！
*showtables不会显示临时表
*不能使用rename重命名临时表。
只能使用ALTERTABLEOLD_TP_TABLE_NAMERENAMENEW_TP_TABLE_NAME;
*影响使用replication功能
mysql服务器自动使用的内部临时表
*在某些情况下，mysql服务器会自动创建内部临时表：
该临时表可以是只存在于内存的memory临时表，或是存储于硬盘的myisam临时表；而且初始创建的memory临时表由于表的增大可能会转变成myisam临时表——其转化临界点由max_heap_table_size和tmp_table_size系统变量的较小值决定的！
注意：
max_heap_table_size系统变量应用于所有的memory引擎的表，不论是用户临时表、正常表、或者内部临时表。
*内部临时表的创建条件：
由于直接利用临时表来创建中间表，其速度不如人意，因此就有了把临时表建成内存表的想法。
但内存表和临时表的区别且并非熟悉，需要查找资料了。
一开始以为临时表是创建后存在，当连接断开时临时表就会被删除，即临时表是存在于磁盘上的。
而实际操作中发现临时表创建后去目录下查看发现并无发现对应的临时表文件（未断开链接）.因而猜想临时表的数据和结构都是寄放在内存中，而不是在磁盘中.
这样一想内存表不是也是存在在内存中吗，那么他和临时表有什么区别？
他们的速度是什么样子？
查找了官方手册有以下的一些解释：
TheMEMORYstorageenginecreatestableswithcontentsthatarestoredinmemory.Formerly,thesewereknownasHEAPtables.MEMORYisthepreferredterm,althoughHEAPremainssupportedforbackwardcompatibility.
EachMEMORYtableisassociatedwithonediskfile.Thefilenamebeginswiththetablenameandhasanextensionof.frmtoindicatethatitstoresthetabledefinition.
由此可以看出来内存表会把表结构存放在磁盘上，把数据放在内存中。
并做了以下实验：
临时表
mysql>createtemporarytabletmp1（idintnotnull）;
QueryOK,0rowsaffectedsec）
mysql>showcreatetabletmp1;
+-------+----------------------------------------------------------------------------------------------+
|Table|CreateTable |
+-------+----------------------------------------------------------------------------------------------+
|tmp1 |CREATETEMPORARYTABLE`tmp1`（`id`int（11）NOTNULL）ENGINE=MyISAMDEFAULTCHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------+
1rowinsetsec）
内存表
mysql>createtabletmp2（idintnotnull）TYPE＝HEAP;
QueryOK,0rowsaffectedsec）
mysql>showcreatetabletmp2;
+-------+------------------------------------------------------------------------------------+
|Table|CreateTable |
+-------+------------------------------------------------------------------------------------+
|tmp2 |CREATETABLE`tmp2`（
`id`int（11）NOTNULL
）ENGINE=MEMORYDEFAULTCHARSET=utf8|
+-------+------------------------------------------------------------------------------------+
1rowinsetsec）
可以看出来临时表和内存表的ENGINE不同，临时表默许的是MyISAM,而内存表是MEMORY.去数据库目录查看，发现而没有tmp1表的任何文件。
看来实际情况是符合官方解释的。
那么速度方面呢（即MyISAM和MEMORY之间的区别）？
实验开始：
实现手腕：
对基于2张万万级别的表做一些OLAP切分操作，中间表的成立使用2种不同的方式。
最后把中间表的数据依照要求掏出，插入到结果表中
实验目的;测试临时内存表和临时表的速度
1.中间表的建立使用Createtemporarytabletype=heap即把中间表建立成临时内存表
2.中间表直接使用Createtemporarytable建立
实验结果：
临时内存表：
1小时
12021-09-2511:
03:
48
12008-09-2512:
03:
39
临时表：
1小时17分钟
22008-09-2512:
25:
28
22008-09-2513:
42:
37
由此发现MEMORY比MyISAM快可能20％。
接着查找官方手册：
Asindicatedbythename,MEMORYtablesarestoredinmemory.Theyusehashindexesbydefault,whichmakesthemveryfast,andveryusefulforcreatingtemporarytables.However,whentheservershutsdown,allrowsstoredinMEMORYtablesarelost.Thetablesthemselvescontinuetoexistbecausetheirdefinitionsarestoredin.frmfilesondisk,buttheyareemptywhentheserverrestarts.
可以看出来MEMORY确实是veryfast，andveryusefulforcreatingtemporarytables.把临时表和内存表放在一路使用确实会快很多：
createtabletmp2（idintnotnull）enginememory;
内存表的建立还有一些限制条件：
MEMORYtablescannotcontain BLOBorTEXTcolumns.HEAP不支持BLOB/TEXT列。
Theserverneedssufficientmemorytomaintainall MEMORYtablesthatareinuseatthesametime.在同一时间需要足够的内存.
TofreememoryusedbyaMEMORYtablewhen younolongerrequireitscontents,youshouldexecuteDELETEorTRUNCATETABLE,orremovethetablealtogetherusingDROP TABLE.为了释放内存，你应该执行DELETEFROMheap_table或DROPTABLEheap_table。
升级会员 