MySQL参数tmp_table_size和max_heap_table_size对比分析

前言

如果 SQL 在执行过程中读到的数据无法直接得到结果，那么就需要额外的内存来保存中间结果，得出最终结果，这个额外的内存就是内部临时表，哪些操作会产生临时表呢，下面这个截图从官网获得

与临时表相关的两个参数

tmp_table_size

• 内存中临时表的最大大小。这个变量不适用于用户创建的MEMORY引擎表；
• 实际限制是tmp_table_size和max_heap_table_size的最小值；
• 当内存中的临时表超过限制时，MysQL会自动将其转换为磁盘上的临时表。

max_heap_table_size

• 这个变量设置用户创建的MEMORY引擎表允许增长的最大大小；
• 该变量的值用于计算内存表的MAX_ROWS值;
• 设置这个变量对一个已有的MEMORY表没有影响;
• 这个变量与tmp_table_size一起使用，以限制内部内存表的大小。

总结（我的环境MySQL 5.7.31）

1、tmp_table_size 内存中临时表的最大大小，这个变量不适用于用户创建的MEMORY引擎表。
2、max_heap_table_size这个参数是用来限制MEMORY引擎表的，这个变量设置用户创建的MEMORY表允许增长的最大大小，该变量的值用于计算内存表的MAX_ROWS值。
3、当在内存或磁盘上创建内部临时表时，服务器会增加Created_tmp_tables值。当在磁盘上创建内部临时表时，服务器会增加Created_tmp_disk_tables值。如果在磁盘上创建了过多的内部临时表，请考虑增加tmp_table_size和max_heap_table_size设置。
4、内存临时表由MEMORY存储引擎管理，所以建议max_heap_table_size设置一样的，如果tmp表变得太大，就会在磁盘上创建默认的InnoDB表（5.7版本是这样）；
5、tmp_table_size&max_heap_table_size，内部临时表是存在内存中的，使用 MEMORY 存储引擎，如果大小超过了这两者较小的值，则会转化为磁盘临时表；
6、internal_tmp_disk_storage_engine：如果内部临时表转化为磁盘临时表，则这个参数指定了磁盘临时表的存储引擎，默认是 INNODB，还可以设置为 MYISAM；
7、innodb_temp_data_file_path：指定了临时表空间的位置和大小，默认值为 ibtmp1:12M:autoextend ，即 datadir/ibtmp1，初始大小12M可以无限扩展，建议限制一个最大值防止把磁盘撑满。

个人笔记