MySQL内存表和临时表的见解

临时表与内存表的区分
临时表是指使用create temprary table创建的临时表.临时表可以使用任何存储引擎,临时表只在单个连接中可见,当连接断开时,临时表也会消失.
MySQL最初会将临时表创建在内存中,当数据变的太大后,就会转储到磁盘上.
内存表是指用memory引擎创建的表.表结构存在于磁盘,数据放在内存中.

临时表创建的条件:
1,查询中有排序(ORDER BY)和分组(GROUP BY)的操作
2,在排序中使用过滤重复列(DISTINCT)
3,查询中用SQL_SMALL_RESULT选项

遇到下列情况会将内存中的临时表写入磁盘:
1,表中存在BLOB和TEXT字段
2,分组或过滤的列超过512字节
3,查询中合并结果集的列超过512字节

如果起初在内存中创建的临时表变的太大,MySQL会自动将其转成磁盘上的临时表.
内存中的临时表由 tmp_table_size 和 max_heap_table_size 两个参数决定.这与创建MEMORY引擎的表不同.MEMORY引擎的表由max_heap_table_size参数决定表的大小,并且它不会转成到在磁盘上的格式.
当MySQL创建临时表时(包括内存上和磁盘上),都会增加Created_tmp_tables 状态值,如果MySQL在磁盘上创建临时表(包括从内存上转成磁盘的),都会增加 Created_tmp_disk_tables状态值.

****关于内存表和临时表的两个选项****
# 独立的内存表所允许的最大容量.
# 此选项为了防止意外创建一个超大的内存表导致用尽所有的内存资源.
# 设置范围16KB-4GB
max_heap_table_size = 64M

# 内部(内存中)临时表的最大大小
# 如果一个表增长到比此值更大,将会自动转换为基于磁盘的表.
# 此限制是针对单个表的,而不是总和.
tmp_table_size = 64M


参考