mysql中如何在存储过中立创建临时表

一、mysql中如何在存储过中立创建临时表

MySQL需要创建隐式临时表来解决某些类型的查询。往往查询的排序阶段需要依赖临时表。例如，当您使用 GROUP BY，ORDER BY或DISTINCT时。这样的查询分两个阶段执行：首先是收集数据并将它们放入临时表中，然后是在临时表上执行排序。

对于某些 UNION语句，不能合并的 VIEW，子查询时用到派生表，多表 UPDATE以及其他一些情况，还需要使用临时表。如果临时表很小，可以到内存中创建，否则它将在磁盘上创建。MySQL在内存中创建了一个表，如果它变得太大，就会被转换为磁盘上存储。内存临时表的最大值由 tmp_table_size或 max_heap_table_size值定义，以较小者为准。MySQL 5.7中的默认大小为 16MB。如果运行查询的数据量较大，或者尚未查询优化，则可以增加该值。设置阈值时，请考虑可用的 RAM大小以及峰值期间的并发连接数。你无法无限期地增加变量，因为在某些时候你需要让 MySQL使用磁盘上的临时表。

注意：如果涉及的表具有 TEXT或 BLOB列，则即使大小小于配置的阈值，也会在磁盘上创建临时表。

二、sql server 临时表占用硬盘吗

我们仍然使用两个会话，一个会话 run，用于运行主 SQL；另一个会话 ps，用于进行 performance_schema的观察：

将 performance_schema中的统计量重置，

临时表的表大小限制取决于参数 tmp_table_size和 max_heap_table_size中较小者，我们实验中以设置 max_heap_table_size为例。

我们将会话级别的临时表大小设置为 2M（小于上次实验中临时表使用的空间），执行使用临时表的 SQL：

会发现内存分配略大于 2M，我们猜测临时表会比配置略多一点消耗，可以忽略。

可以看到语句使用了一次需要落磁盘的临时表。

那么这张临时表用了多少的磁盘呢？

我们开启 performance_schema中 waits相关的统计项：

再查看 performance_schema的统计值：

1.临时表空间被写入了 7.92MiB的数据。

2.这些数据是语句写入后，慢慢逐渐写入的。

来看看这些写入操作的特征，该方法我们在实验 03使用过：

可以看到写入的线程是 page_clean_thread，是一个刷脏操作，这样就能理解数据为什么是慢慢写入的。

也可以看到每个 IO操作的大小是 16K，也就是刷数据页的操作。

1. MySQL会基本遵守 max_heap_table_size的设定，在内存不够用时，直接将表转到磁盘上存储。

2.由于引擎不同（内存中表引擎为 heap，磁盘中表引擎则跟随 internal_tmp_disk_storage_engine的配置），本次实验写磁盘的数据量和实验 05中使用内存的数据量不同。

3.如果临时表要使用磁盘，表引擎配置为 InnoDB，那么即使临时表在一个时间很短的 SQL中使用，且使用后即释放，释放后也会刷脏页到磁盘中，消耗部分 IO。

三、linux怎么修改mysql数据库临时表空间大小

先来说说临时表的概念。临时表顾名思义，就是临时的，用完销毁掉的表。数据既可以保存在临时的文件系统上，也可以保存在固定的磁盘文件系统上。临时表有下面几种：

这种临时表从数据库实例启动后开始生效，在数据库实例销毁后失效。在MySQL里面这种临时表对应的是内存表，即memory引擎。

这种临时表在用户登录系统成功后生效，在用户退出时失效。在MySQL里的临时表指的就是以 create temporary table这样的关键词创建的表。

这种临时表在事务开始时生效，事务提交或者回滚后失效。在MySQL里面没有这种临时表，必须利用会话级别的临时表间接实现。

这种临时表在SQL语句执行之间产生，执行完毕后失效。在MySQL里面这种临时表不是很固定，跟随MySQL默认存储引擎来变化。比如默认存储引擎是MyISAM,临时表的引擎就是MyISAM,并且文件生成形式以及数据运作形式和MyISAM一样，只是数据保存在内存里；如果默认引擎是INNODB，那么临时表的引擎就是INNODB，此时它的所有信息都保存在共享表空间ibdata里面。

MySQL 5.7对于InnoDB存储引擎的临时表空间做了优化。在MySQL 5.7之前，INNODB引擎的临时表都保存在ibdata里面，而ibdata的贪婪式磁盘占用导致临时表的创建与删除对其他正常表产生非常大的性能影响。在MySQL5.7中，对于临时表做了下面两个重要方面的优化：

MySQL5.7把临时表的数据以及回滚信息（仅限于未压缩表）从共享表空间里面剥离出来，形成自己单独的表空间，参数为innodb_temp_data_file_path。

在MySQL5.7中把临时表的相关检索信息保存在系统信息表中：information_schema.innodb_temp_table_info.而MySQL 5.7之前的版本想要查看临时表的系统信息是没有太好的办法。

需要注意的一点就是，虽然INNODB临时表有自己的表空间，但是目前还不能自己定义临时表空间文件的保存路径，只能是继承innodb_data_home_dir。此时如果想要拿其他的磁盘，比如内存盘来充当临时表空间的保存地址，只能用老办法，做软链。举个小例子：

我现在用的OS是 Ubuntu12.X，想用tmpfs文件系统充当临时表空间，

root@ytt-master-VirtualBox:/usr/local/mysql/data#ln-s/run/shm//usr/local/mysql/data/tmp_space2

root@ytt-master-VirtualBox:/usr/local/mysql/data#ls-l| grep'shm'

lrwxrwxrwx1 root root 9 Nov 13 10:28tmp_space2->/run/shm/

innodb_temp_data_file_path=tmp_space2/ibtmp2:200M:autoextend

添加到my.cnf里的[mysqld]下面一行

mysql>select@@innodb_temp_data_file_path\G

***************************1. row***************************

@@innodb_temp_data_file_path:tmp_space2/ibtmp2:200M:autoextend

先写一个批量创建临时表的存储过程：

DROPPROCEDURE IF EXISTS `sp_create_temporary_table`$$

CREATEDEFINER=`root`@`localhost` PROCEDURE `sp_create_temporary_table`(

DECLARE i INT UNSIGNED DEFAULT 1;

SET@stmt= CONCAT('create temporarytable tmp',i,'( id int, tmp_desc varchar(60));');

mysql>call sp_create_temporary_table(10);

QueryOK, 0 rows affected(0.07 sec)

如果在以前，我们只知道创建了10张临时表，但是只能凭记忆或者手工记录下来临时表的名字等信息。

现在可以直接从数据字典里面检索相关数据。

mysql>select* frominformation_schema.innodb_temp_table_info;

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

|TABLE_ID| NAME| N_COLS| SPACE| PER_TABLE_TABLESPACE| IS_COMPRESSED|

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

| 56|#sql1705_2_9| 5| 36| FALSE| FALSE|

| 55|#sql1705_2_8| 5| 36| FALSE|FALSE|

| 54|#sql1705_2_7| 5| 36| FALSE| FALSE|

| 53|#sql1705_2_6| 5| 36| FALSE| FALSE|

| 52|#sql1705_2_5| 5| 36| FALSE|FALSE|

| 51|#sql1705_2_4| 5| 36| FALSE| FALSE|

| 50|#sql1705_2_3| 5| 36| FALSE| FALSE|

| 49|#sql1705_2_2| 5| 36| FALSE|FALSE|

| 48|#sql1705_2_1| 5| 36| FALSE| FALSE|

| 47|#sql1705_2_0| 5| 36| FALSE| FALSE|

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

功能性我就写到这里，大家性能方面如果有兴趣可以找时间去测试。