【mysql】关于临时表

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了【mysql】关于临时表，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3634字，纯文字阅读大概需要6分钟。

内容图文

mysql官方的介绍

In some cases, the server creates internal temporary tables while processing queries. Such a table can be held in memory and processed by the MEMORY storage engine, or stored on disk and processed by the MyISAM storage engine. The server may create a temporary table initially as an in-memory table, then convert it to an on-disk table if it becomes too large. Users have no direct control over when the server creates an internal temporary table or which storage engine the server uses to manage it

以下几种情况会创建临时表

• UNION查询
• 用到TEMPTABLE算法或者是UNION查询中的视图
• 在JOIN查询中，ORDER BY或者GROUP BY使用了不是第一个表的列 例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
• 表连接中，ORDER BY的列不是驱动表中的
• DISTINCT查询并且加上ORDER BY时
• SQL中用到SQL_SMALL_RESULT选项时
• FROM中的子查询
• 子查询或者semi-join时创建的表
• 多表更新sql
• GROUP_CONCAT() or COUNT(DISTINCT) evaluation

在以下几种情况下，会创建磁盘临时表

• 表包含TEXT或者BLOB列
• 在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列（或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节）
• 在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）
• 执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令，因为它们的执行结果用到了BLOB列类型

临时表相关配置

tmp_table_size：指定系统创建的内存临时表最大大小

max_heap_table_size: 指定用户创建的内存表的最大大小

The maximum size for in-memory temporary tables is the minimum of thetmp_table_size and max_heap_table_size values

优化建议 

一般要避免使用临时表

1、在ORDER BY或者GROUP BY的列上创建索引

2、TEXT、BLOB等大字段，拆分表

从5.7.5开始，新增一个系统选项 internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB，而在这以前，只能使用 MyISAM。

在5.6.3以后新增的系统选项 default_tmp_storage_engine 是控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型，在以前默认是MEMORY，不要把这二者混淆了

ysql> show variables like ‘default_tmp%‘; 
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_tmp_storage_engine | InnoDB |
+----------------------------+--------+
1 row in set (0.00 sec)

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

mysql> show create table tmp1 \G
*************************** 1. row ***************************
       Table: tmp1
Create Table: CREATE TEMPORARY TABLE `tmp1` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> set default_tmp_storage_engine = myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table tmp2 (id int(11) unsigned not null comment ‘primary key‘ );
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tmp2 \G
*************************** 1. row ***************************
       Table: tmp2
Create Table: CREATE TEMPORARY TABLE `tmp2` (
  `id` int(11) unsigned NOT NULL COMMENT ‘primary key‘
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> set default_tmp_storage_engine = memory;
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table tmp3 (id int(11) unsigned not null comment ‘primary key‘ ); 
Query OK, 0 rows affected (0.00 sec)
mysql> show create table tmp3 \G
*************************** 1. row ***************************
       Table: tmp3
Create Table: CREATE TEMPORARY TABLE `tmp3` (
  `id` int(11) unsigned NOT NULL COMMENT ‘primary key‘
) ENGINE=MEMORY DEFAULT CHARSET=utf8

参考： http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

【mysql】关于临时表

标签：

本文系统来源：http://www.cnblogs.com/chenpingzhao/p/4954854.html

内容总结

以上是互联网集市为您收集整理的【mysql】关于临时表全部内容，希望文章能够帮你解决【mysql】关于临时表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端