MySQL中的两种临时表

内容导读

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

内容图文

如果SQL语句中包含了DERIVED_TABLE。

在5.7中，由于采用了新的优化方式，我们需要使用 set optimizer_switch=’derived_merge=off’来禁止derived table合并到外层的Query中。
例如：

	mysql> explain format=json select * from (select * from t1) as tt;
	EXPLAIN
	{
	  "query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "2.40"
		},
		"table": {
		  "table_name": "tt",
		  "access_type": "ALL",
		  ...
		  "materialized_from_subquery": {
			"using_temporary_table": true,
		...

如果我们查询系统表的话，系统表的数据将被存储到内部临时表中。

我们当前不能使用EXPLAIN来查看是否读取系统表数据需要利用到内部临时表，但是可以通过SHOW STATUS来查看是否利用到了内部临时表。
例如：

	mysql> select * from information_schema.character_sets;
	mysql> show status like ‘CREATE%‘;

如果DISTINCT语句没有被优化掉，即DISTINCT语句被优化转换为GROUP BY操作或者利用UNIQUE INDEX消除DISTINCT, 内部临时表将会被使用。

	mysql> explain format=json select distinct a from t1;
	EXPLAIN
	{
	{
	  "query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "1.60"
		},
		"duplicates_removal": {
		  "using_temporary_table": true,
		...

如果查询带有ORDER BY语句，并且不能被优化掉。下面几种情况会利用到内部临时表缓存中间数据，然后对中间数据进行排序。

1）如果连接表使用BNL（Batched Nestloop）/BKA(Batched Key Access)
例如：

1))BNL默认是打开的

mysql> explain format=json select * from t1, t1 as t2 order by t1.a;
EXPLAIN
{
  "query_block": {
  "select_id": 1,
  "cost_info": {
	"query_cost": "22.00"
  },
  "ordering_operation": {
	"using_temporary_table": true,
  ...

2))关掉BNL后，ORDER BY将直接使用filesort。

mysql> set optimizer_switch=‘block_nested_loop=off‘;
Query OK, 0 rows affected (0.00 sec)
mysql> explain format=json select * from t1, t1 as t2 order by t1.a;
EXPLAIN
{
   "query_block": {
	"select_id": 1,
	"cost_info": {
	  "query_cost": "25.00"
	},
	"ordering_operation": {
	  "using_filesort": true,
	...

2）ORDER BY的列不属于执行计划中第一个连接表的列。
例如：

mysql> explain format=json select * from t as t1, t as t2 order by t2.a;
EXPLAIN
{
   "query_block": {
	"select_id": 1,
	"cost_info": {
	  "query_cost": "25.00"
	},
	"ordering_operation": {
	  "using_temporary_table": true,
	...

3）如果ORDER BY的表达式是个复杂表达式。

那么什么样的ORDER BY表达式，MySQL认为是复杂表达式呢？

1))如果排序表达式是SP或者UDF。
例如：

drop function if exists func1;
delimiter |
create function func1(x int)
returns int deterministic
begin
declare z1, z2 int;
set z1 = x;
set z2 = z1+2;
return z2;
end|
delimiter ;
explain format=json select * from t1 order by func1(a);
{
	"query_block": {
	"select_id": 1,
	"cost_info": {
	  "query_cost": "2.20"
	},
	"ordering_operation": {
	  "using_temporary_table": true,
	...

2))ORDER BY的列包含聚集函数

为了简化执行计划，我们利用INDEX来优化GROUP BY语句。
例如：

  create index idx1 on t1(a);
  explain format=json SELECt a FROM t1 group by a order by sum(a);
  | {
	   "query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "1.20"
		},
		"ordering_operation": {
		  "using_temporary_table": true,
		  "using_filesort": true,
		  "grouping_operation": {
			"using_filesort": false,
		...
  drop index idx1 on t1;

3))ORDER BY的列中包含有SCALAR SUBQUERY,当然该SCALAR SUBQUERY没有被优化掉。
例如：

explain format=json select (select rand() from t1 limit 1) as a from t1 order by a;		
| {
	  "query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "1.20"
		},
		"ordering_operation": {
		  "using_temporary_table": true,
		  "using_filesort": true,
			...

4) 如果查询既带有ORDER BY同时也有GROUP BY语句，但是两个语句使用的列不相同。

注意: 如果是5.7，我们需要将sql_mode设置为非only_full_group_by模式，否则会报错。

同样为了简化执行计划，我们利用INDEX来优化GROUP BY语句。
例如：

set sql_mode=‘‘;
create index idx1 on t1(b);
explain format=json select t1.a from t1 group by t1.b order by 1;
| {
	 "query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "1.40"
		},
	"ordering_operation": {
		  "using_temporary_table": true,
		  "using_filesort": true,
		  "grouping_operation": {
			"using_filesort": false,
	...
drop index idx1 on t1;

如果查询带有GROUP BY语句，并且不能被优化掉。下面几种情况会利用到内部临时表缓存中间数据，然后对中间数据进行GROUP BY。

1）如果连接表使用BNL（Batched Nestloop）/BKA(Batched Key Access)。
例如：

	explain format=json select t2.a from t1, t1 as t2 group by t1.a;
	| {
		"query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "8.20"
		},
		"grouping_operation": {
		  "using_temporary_table": true,
		  "using_filesort": true,
		  "cost_info": {
			"sort_cost": "4.00"
		...

2) 如果GROUP BY的列不属于执行计划中的第一个连接表。
例如：

	explain format=json select t2.a from t1, t1 as t2 group by t2.a;
	| {
		"query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "8.20"
		},
		"grouping_operation": {
		  "using_temporary_table": true,
		  "using_filesort": true,
		  "nested_loop": [
		...

3) 如果GROUP BY语句使用的列与ORDER BY语句使用的列不同。
例如：

	set sql_mode=‘‘;
	explain format=json select t1.a from t1 group by t1.b order by t1.a;
	| {
	   "query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "1.40"
		},
		"ordering_operation": {
		  "using_filesort": true,
		  "grouping_operation": {
			"using_temporary_table": true,
			"using_filesort": false,
		...

4) 如果GROUP BY带有ROLLUP并且是基于多表外连接。
例如：

	explain format=json select sum(t1.a) from t1 left join t1 as t2 on true group by t1.a with rollup;
	| {
		"query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "7.20"
		},
		"grouping_operation": {
		  "using_temporary_table": true,
		  "using_filesort": true,
		  "cost_info": {
			"sort_cost": "4.00"
		  },
		...

5) 如果GROUP BY语句使用的列来自于SCALAR SUBQUERY，并且没有被优化掉。
例如：

	explain format=json select (select avg(a) from t1) as a from t1 group by a;
	| {
		"query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "3.40"
		},
		"grouping_operation": {
		  "using_temporary_table": true,
		  "using_filesort": true,
		  "cost_info": {
			"sort_cost": "2.00"
		  },
		...

IN表达式转换为semi-join进行优化
1) 如果semi-join执行方式为Materialization
例如：

set optimizer_switch=‘firstmatch=off,duplicateweedout=off‘;
explain format=json select * from t1 where a in (select b from t1);
| {
	"query_block": {
	"select_id": 1,
	"cost_info": {
	  "query_cost": "5.60"
	},
	"nested_loop": [
	  {
		 "rows_examined_per_scan": 1,
			  "materialized_from_subquery": {
				"using_temporary_table": true,
				"query_block": {
				  "table": {
					"table_name": "t1",
					"access_type": "ALL",

	...

2) 如果semi-join执行方式为Duplicate Weedout
例如：

	set optimizer_switch=‘firstmatch=off‘;
	explain format=json select * from t1 where a in (select b from t1);
	 | {
		"query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "4.80"
		},
		"duplicates_removal": {
		  "using_temporary_table": true,
		  "nested_loop": [
				{
		...

如果查询语句带有UNION，MySQL将利用内部临时表帮助UNION操作消除重复。
例如：

	explain format=json select * from t1 union select * from t1;
	| {
		"query_block": {
		"union_result": {
		  "using_temporary_table": true,
		  "table_name": "<union1,2>",
		...

如果查询语句使用多表更新。
这里Explain不能看到内部临时表被利用，所以需要查看status。
例如：

update t1, t1 as t2 set t1.a=3;
show status like ‘CREATE%‘;

如果聚集函数中包含如下函数，内部临时表也会被利用。

1) count(distinct *)
例如：
	explain format=json select count(distinct a) from t1;
2) group_concat
例如：
	explain format=json select group_concat(b) from t1;

总之，上面列出了10种情况，MySQL将利用内部临时表进行中间结果缓存，如果数据量比较大的话，内部临时表将会把数据存储在磁盘上，这样显然会对性能有所影响。为了尽可能的减少性能损失，我们需要尽量避免上述情况的出现。

MySQL中的两种临时表

标签：

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

内容总结

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

内容备注

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

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/467291.html

来源：【匿名】

【上一篇】mysql 修改添加删除表字段【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL中的两种临时表】教程文章相关的互联网学习教程文章

unsigned 既为非负数，用此类型可以增加数据长度!例如如果 tinyint最大是127，那 tinyint unsigned 最大就可以到 127 * 2unsigned 属性只针对整型，而binary属性只用于char 和varchar。类型说明tinyint非常小的整数smallint较小整数mediumint中等大小整数int标准整数bigint较大整数float单精度浮点数double双精度浮点数decimal一个串的浮点数每种数值类型的名称和取值范围如表2所示。类型说明取值范围tinyint[...

大量删除MySQL中的数据【代码】

出现的背景：公司做了一个redis相关的项目，其中mysql存储了很多统计数据。比如客户端上报的数据，redis实例的数据，应用的数据，机器的数据等。每天都在上报，采集，由于没有定期删除，数据大量累积。大概有一年左右的数据，一个表的数据已经达到亿级别的。这样算下来，一个表的数据至少是几十GB了。因此需要删除过期的数据，暂时保留近三个月的统计数据。解决方案：基本每个表都有个字段叫create_time或者collect_time的字段，只...

MSSQL将多行单列变一行一列并用指定分隔符分隔，模拟Mysql中的group_concat【代码】【图】

-- 将多行记录（只能一个列）用指定分隔符分隔IF(OBJECT_ID(‘sp_RowsChangeClosBySplit‘,N‘P‘) ISNOTNULL)DROPPROC sp_RowsChangeClosBySplit GOCREATEPROC sp_RowsChangeClosBySplit @tablenvarchar(50), -- 表名@fieldnvarchar(50), -- 要拼接的字段@splitvarchar(10)=‘,‘, -- 分隔符@wherenvarchar(500)=‘‘-- 条件ASBEGINDECLARE@sqlNVARCHAR(1000) SET@sql=‘select TOP 1 stuff((se...

Mysql中的find_in_set的使用方法介绍

使用举例：用户表(user)中的一个字段(purview)代表该用户的权限将用户的所有权限id存入此字段。比如有：1,2,3,4,5这样的五个权限查找哪些用户拥有权限id为1的权限复制代码代码如下:select * from user where find_in_set(‘1‘,purview); sql语句 sql解释：查出所有拥有1这个权限的所有用户的信息。这样设计数据表的目的是减少表的数量，但是会增加更新的难度！原文：http://www.jb51.net/article/34870.htm

mysql中的where和having子句的区别

mysql中的where和having子句的区别@import url(http://www.blogjava.net/CuteSoft_Client/CuteEditor/Load.ashx?type=style&file=SyntaxHighlighter.css);@import url(/css/cuteeditor.css);having的用法having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。SQL实例：一、显示每个地区的总人口数和总面积．SELECT region, SUM...

mysql 中的数据类型

unsigned 既为非负数，用此类型可以增加数据长度! 例如如果 tinyint最大是127，那 tinyint unsigned 最大就可以到 127 * 2 unsigned 属性只针对整型，而binary属性只用于char 和varchar。类型说明tinyint非常小的整数smallint较小整数mediumint中等大小整数int标准整数bigint较大整数float单精度浮点数double双精度浮点数decimal一个串的浮点数每种数值类型的名称和取值范围如表2所示。类型说明取值范围tin...

MySQL中的数据类型【代码】

mysql中的数据类型数值类型MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。MySQL支持的整数类型有TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。对于小数的表示，MYSQL分为两种方式：浮点数和定点数。浮点数包括float(单精...

MySQL中的semi-join【代码】【图】

http://www.linuxidc.com/Linux/2015-05/117523.htm1. 背景介绍什么是semi-join？所谓的semi-join是指semi-join子查询。当一张表在另一张表找到匹配的记录之后，半连接（semi-jion）返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。另外，右节点的表一条记录也不会返回。半连接通常使用IN 或 EXISTS 作为连接条件。该子查询具有如下结构：SELECT ... FROM outer_tabl...

( 17 )MySQL中的触发器【图】

MySQL中的触发器触发器需求:有两张表,一张定单表,一张商品表.每生成一个订单,商品库存就相应减少.触发器 ( trigger )事先为某张表绑定好一段代码,当某些内容发生改变的时候(增删该),系统会自动触发代码的执行.触发器包括 : 事件类型,触发时间,触发对象事件类型: 增删改,三种类型 insert ,delete和update.触发时间: 触发前后 before 和 after触发对象:表中的每一条记录(行)一张表中只能有一种触发时间的一种类型的触发器 : 一张表最...

MySQL中的日志【图】

前言MySQL中有以下日志文件，分别是：　　1：重做日志（redo log）　　2：回滚日志（undo log）　　3：二进制日志（binlog）　　4：错误日志（errorlog）　　5：慢查询日志（slow query log）　　6：一般查询日志（general log）　　7：中继日志（relay log）。其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。一、重做日志（redo log）作用：...

漫谈MySql中的事务【图】

最近一直在做订单类的项目，使用了事务。我们的数据库选用的是MySql，存储引擎选用innoDB，innoDB对事务有着良好的支持。这篇文章我们一起来扒一扒事务相关的知识。为什么要有事务？事务广泛的运用于订单系统、银行系统等多种场景。如果有以下一个场景：A用户和B用户是银行的储户。现在A要给B转账500元。那么需要做以下几件事： 1. 检查A的账户余额>500元； 2. A账户扣除500元； 3. B账户...

mysql中的一些操作

查询mysql中事务提交的情况：show variables like ‘%commit%‘; 可以查看当前autocommit值在mysql数据库中它的默认值是"on"代表自动事务. 自动事务的意义就是:执行任意一条sql语句都会自动提交事务. 测试:将autocommit的值设置为off 1.set autocommit=off 关闭自动事务。 2.必须手动commit才可以将事务提交。注意:mysql默认autocommit=on oracle默认的autocommit=off; 1.mysql中设置 1.查看事务...

mysql中的存储引擎

MySQL中常用的几种存储引擎：innoDB、bdb、myisam、memory以及这几个引擎的讲解；InnoDB存储引擎：（1） innodb存储引擎该mysql表提供了事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。（2）innodb支持自增长列（auto_increment）,自增长列的值不能为空，如果在使用的时候为空的话怎会进行自动存现有的值开始增值，如果有但是比现在的还大，则就保存这个值。（3）innodb存储引擎支持外键（foreign key） ,外键所在的...

MySQL中的CONCAT函数使用教程【代码】

使用MySQL CONCAT()函数将两个字符串连接起来，形成一个单一的字符串。试试下面的例子： mysql> SELECT CONCAT(‘FIRST ‘, ‘SECOND‘); +----------------------------+ | CONCAT(‘FIRST ‘, ‘SECOND‘) | +----------------------------+ | FIRST SECOND | +----------------------------+ 1 row in set (0.00 sec)了解CONCAT函数更详细，考虑EMPLOYEE_TBL表具有以下记录： mysql> SELECT * FROM employee_tbl; +-----...

mysql中的过程procedure【代码】

一、创建过程语法create procedure 过程名字(参数列表)begin　　-- SQL语句...end-- 创建存储过程delimiter $$ createprocedure my_act() beginselect*from goods; end $$ delimiter ;二、查看过程-- 查看所有过程 show procedure status [like ‘pattern‘];-- 查看过程创建语句 show createprocedure 过程名字;三、过程调用-- 过程调用 call 过程名字();四、过程删除-- 删除过程dropprocedure 过程名字;五、过程参数详解　　相比...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / MySQL中的两种临时表

MySQL中的两种临时表

内容导读

内容图文

内容总结

内容备注

内容手机端

【MySQL中的两种临时表】教程文章相关的互联网学习教程文章

mysql中的unsigned

大量删除MySQL中的数据【代码】

MSSQL将多行单列变一行一列并用指定分隔符分隔，模拟Mysql中的group_concat【代码】【图】

Mysql中的find_in_set的使用方法介绍

mysql中的where和having子句的区别

mysql 中的数据类型

MySQL中的数据类型【代码】

MySQL中的semi-join【代码】【图】

( 17 )MySQL中的触发器【图】

MySQL中的日志【图】

漫谈MySql中的事务【图】

mysql中的一些操作

mysql中的存储引擎

MySQL中的CONCAT函数使用教程【代码】

mysql中的过程procedure【代码】

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程