mysql参数配置学习----临时表内存表的设置

参照自：https://blog.csdn.net/caomiao2006/article/details/52205391
MySQL内存表的特性与使用介绍:https://www.cnblogs.com/AloneSword/p/3206486.html
MySQL内存表:https://blog.csdn.net/JesseYoung/article/details/37931505
mysql优化： 内存表和临时表:https://www.cnblogs.com/tuyile006/archive/2009/02/06/1385121.html
mysql如何使用临时表，内存表来加快速度: https://zhuanlan.zhihu.com/p/19609168
mysql临时表与内存表： https://blog.csdn.net/pan_junbiao/article/details/86409064
mysql之临时表和内存表：https://yq.aliyun.com/articles/42014 一些主从同步的问题，很有启发。
mysql临时表：https://www.cnblogs.com/cchust/p/10891520.html 这篇文章对临时表及其分类用法

意义:防止意外创建一个超大的内存表导致所有的内存资源.被消耗殆尽。

只需要看下面几个命令的设置大概如下：

 #设置内存表最大限制，其实 是为内存临时表 准备的
max_heap_table_size =1024M 
# 设置内存临时表的最大限制
tmp_table_size =1024M
#指定当数据超过临时表的最大值设定时，自动转为基于磁盘表的时候所用的引擎，innodb或者myisam
internal_tmp_disk_storage_engine = INNODB
#指定当内存表数据超过临时表的最大值设定时，转存到磁盘时的路径。
tmpdir=D:\Mysql\mysql-8.0.18-winx64\data\temp

1，mysql临时表和内存表的区别

1.1 内存表

内存表，就是放在内存中的表，数据放在内存中，表结构定义放在磁盘中，所使用内存的大小通过max_heap_table_size指定，如max_heap_table_size=1024M。默认内存表默认的大小是64MB，依次我也觉得貌似mysql还可以做中间件。集群总是会讲究一个周期，周期性同步，周期性持久化，这其实应该和电子电路有共通的地方。

所谓内存表，是指整个数据库表都常驻在内存中的表，相对于普通表而言，内存表存储数据在内存中，而普通表存储在硬盘中。

MySQL内存表可以提升一些临时业务的查询效率，比如做Session的共享，一些类似缓存的数据等， 重要，重要

在MySQL的主从服务器上，内存表可以被复制
创建内存表

create table test
	(
	    id int unsigned not null auto_increment primary key,
	    state char(10),
	    type char(20),
	    date char(30)
	)ENGINE=MEMORY DEFAULT CHARSET=utf8;

1，内存表的表定义是存放在磁盘上的，扩展名为.frm， 所以重启不会丢失。

2，内存表的数据是存放在内存中的，所以重启会丢失数据。

3，内存表使用一个固定的记录长度格式。

4，内存表不支持BLOB或TEXT列，比如varchar与text字段就不会被支持。

5，内存表支持AUTO_INCREMENT列和对可包含NULL值的列的索引（网上大多说不支持，这是错误的）。内存表支持大于(>) 小于( <)操作，网上也说不支持。

6，mysql重启后，主键、自增、索引仍然存在，只是数据丢失。这也是对网上的一些错误文字纠正。

7，内存表表在所有客户端之间共享（就像其它任何非TEMPORARY表）。

8，MEMORY存储引擎执行HASH和BTREE索引。你可以通过添加一个如下所示的USING子句为给定的索引指定一个或另一个：

CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
	 
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;

9，在数据库复制时，如果主机当掉，则会在binLog中自动加入delete from [内存表]，将slave的数据也删除掉，以保证两边的数据一致性。

10，内存表不支持事务。

11，内存表是表锁，当修改频繁时，性能可能会下降。

12，内存表的数据在max_heap_table_size这里设定，若单张表行数超过了这个长度，则报数据满错误 ;

内存表的使用
内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库，但是使用上受到一些限制。

1，heap 对所有用户的连接是可见的，这使得它非常适合做缓存。

2，仅适合使用的场合。heap不允许使用xxxTEXT和xxxBLOB数据类型。

3，一旦服务器重启，所有heap表数据丢失，但是heap表结构仍然存在，因为heap表结构是存放在实际数据库路径下的，不会自动删除。重启之后，heap将被清空，这时候对heap的查询结果都是空的。

4，如果heap是复制的某数据表，则复制之后所有主键、索引、自增等格式将不复存在，需要重新添加主键和索引，如果需要的话。

对于重启造成的数据丢失，有以下的解决办法：
1，在任何查询之前，执行一次简单的查询，判断heap表是否存在数据，如果不存在，则把数据重新写入，或者DROP表重新复制某张表。这需要多做一次查询。不过可以写成include文件，在需要用该heap表的页面随时调用，比较方便。

2，对于需要该heap表的页面，在该页面第一次且仅在第一次查询该表时，对数据集结果进行判断，如果结果为空，则需要重新写入数据。这样可以节省一次查询。

3，更好的办法是在mysql每次重新启动时自动写入数据到heap，但是需要配置服务器，过程比较复杂，通用性受到限制。

1.2 临时表

首先要搞清楚真正的临时表和 内部临时表的区别。如下：
真正的临时表是使用CREATE temporary TABLE创建的，而内部临时表是由MySQL在内部创建的，用于排序或处理子查询。

临时表也是存放在内存中，临时表最大所需内存需要通过tmp_table_size=1024M设定。

当数据超过临时表的最大值设定时，自动转为基于磁盘的MyISAM表，存储在指定的tmpdir目录，此时因需要进行IO操作，性能会大大下降

内部临时表是由我们的query语句产生的，在恰当的时候存放在内存中。

临时表更多的作用是系统自己创建后，组织数据以提升性能，如子查询，临时表在多个连接之间不能共享。

临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

临时表存在的时候，同名的磁盘中的表被隐藏了，如果临时表被 drop，磁盘中的表就可见了

临时表只能用在 memory、myisam、merge 或者 innodb 存储引擎中；

临时表不支持 mysql cluster（簇）；

在同一个 query 语句中，你只能查找一次临时表。 如 SELECT * FROM temp_table, temp_table AS t2;会报错

如下，是创建临时表的过程，当我们退出数据库之后，这个临时表就不存在了。除非超出规定内存 ，否则它只存在于内存之中。使用的是InnoDB引擎。

mysql> create temporary table tmp1(id int not null);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tmp1;
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------+                                                                                                       | tmp1  | CREATE TEMPORARY TABLE `tmp1` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_zh_0900_as_cs |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

对比内存表，如下，退出再次创建，会显示数据表已经存在，使用的是memory存储引擎。

mysql> create table tmp3(id int not null) engine = heap;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tmp3 ;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------+
| tmp3  | CREATE TABLE `tmp3` (
  `id` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_zh_0900_as_cs |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（1）临时表只在当前连接可见，当这个连接关闭的时候，会自动drop。比如打开mysql 就是一个连接会话。两个不同的连接可以使用相同名字的临时表，两个表之间不存在什么关系，如果临时表的名字和已经存在的磁盘表名字一样，那么临时表会暂时覆盖磁盘表。就是说，你select 查询，只会显示临时表里面的，不会显示磁盘表。

（2）临时表的存储引擎：memor，myisam，merge，innodb，临时表不支持mysql cluster簇。

（3）同一个查询语句，只能用一次临时表，就是说不能将表和自己做连接等。

（4）重命名表，不能用rename 可以用alter table代替。

（5）如果超出了临时表的容量，临时表会转换成磁盘表。

（6）show tables语句不会列出临时表，在information_schema中也不存在临时表信息，show create table可以查看临时表。

【服务器可能会创建临时表的场景】
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中临时表主要有两类，包括外部临时表和内部临时表。外部临时表是通过语句create temporary table…创建的临时表，临时表只在本会话有效，会话断开后，临时表数据会自动清理。内部临时表主要有两类，一类是information_schema中临时表，另一类是会话执行查询时，如果执行计划中包含有“Using temporary”时，会产生临时表。内部临时表与外部临时表的一个区别在于，我们看不到内部临时表的表结构定义文件frm。而外部临时表的表定义文件frm，一般是以#sql{进程id}_{线程id}_序列号组成，因此不同会话可以创建同名的临时表。

临时表
临时表与普通表的主要区别在于是否在实例，会话，或语句结束后，自动清理数据。比如，内部临时表，我们在一个查询中，如果要存储中间结果集，而查询结束后，临时表就会自动回收，不会影响用户表结构和数据。另外就是，不同会话的临时表可以重名，所有多个会话执行查询时，如果要使用临时表，不会有重名的担忧。5.7引入了临时表空间后，所有临时表都存储在临时表空间(非压缩)中，临时表空间的数据可以复用。临时表并非只支持Innodb引擎，还支持myisam引擎，memory引擎等。因此，临时表我们看不到实体(idb文件)，但其实不一定是内存表，也可能存储在临时表空间中。

临时表 VS 内存表
临时表既可以innodb引擎表，也可以是memory引擎表。这里所谓的内存表，是说memory引擎表，通过建表语句create table …engine=memory，数据全部在内存，表结构通过frm管理，同样的内部的memory引擎表，也是看不到frm文件中，甚至看不到information_schema在磁盘上的目录。在MySQL内部，information_schema里面的临时表就包含两类：innodb引擎的临时表和memory引擎的临时表。比如TABLES表属于memory临时表，而columns,processlist,属于innodb引擎临时表。内存表所有数据都在内存中，在内存中数据结构是一个数组(堆表)，所有数据操作都在内存中完成，对于小数据量场景，速度比较快(不涉及物理IO操作)。但内存毕竟是有限的资源，因此，如果数据量比较大，则不适合用内存表，而是选择用磁盘临时表(innodb引擎)，这种临时表采用B+树存储结构(innodb引擎)，innodb的bufferpool资源是共享的，临时表的数据可能会对bufferpool的热数据有一定的影响，另外，操作可能涉及到物理IO。memory引擎表实际上也是可以创建索引的，包括Btree索引和Hash索引，所以查询速度很快，主要缺陷是内存资源有限。

1.3 区别与联系

当数据超过临时表的最大值设定时，自动转为磁盘表，此时因需要进行IO操作，性能会大大下降，而内存表不会，内存表满后，会提示数据满错误。

临时表和内存表都可以人工创建，但临时表更多的作用是系统自己创建后，组织数据以提升性能，如子查询，临时表在多个连接之间不能共享。

mysql数据库使用memory存储引擎作为临时表存储中间结果集(intermediate result)，如果中间集结果大于memory表的容量设置，又或者中间结果集包含text和blog列类型字段，则mysql会把他们转换到myisam存储引擎表而放到磁盘上，会对查询产生性能影响。

临时表:表建在内存里，数据在内存里
内存表:表建在磁盘里，数据在内存里

临时表主要是为了放一些中间大结果集的一些子集，内存表可以放一些经常频繁使用的数据。

临时表一般都很少用，而且要用也一般是在程序中动态创建或者由MySQL内部根据SQL执行计划需要自己创建。
内存表则大多数是当Cache用，在早期一台机器没有装Memcache等第三方cache时，h内存表无疑是cache的最好的选择了。而如今随着memcache、NoSQL的流行，内存表就越来越少人使用了

在一些情况下，服务器会在处理 query 的时候创建内部临时表。这种表有两种存在形式：
位于内存中，使用 MEMORY 存储引擎（内存临时表）；
位于磁盘上，使用 MyISAM 存储引擎（磁盘临时表）。
服务器可能在最初创建的是内存临时表，之后当其变大到一定程度时再转变为磁盘临时表。对于服务器何时创建内部临时表或者临时表使用哪种存储引擎，用户没有直接控制的能力。

小插曲

// InnoDB转MyISAM
ALTER TABLE `tablename` ENGINE = MYISAM
// MyISAM转InnoDB
alter table tablename type=innodb;
ALTER TABLE `tablename` ENGINE = InnoDB