备案控制台
探索云世界
开发者社区 数据库 关系型数据库 文章 正文

MySQL · 特性分析 · 内部临时表

2016-06-21 3436
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB
推荐场景：
PolarDB for MySQL 多主集群体验
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介：

MySQL中的两种临时表

外部临时表

通过CREATE TEMPORARY TABLE 创建的临时表，这种临时表称为外部临时表。这种临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭。这种临时表的命名与非临时表可以同名（同名后非临时表将对当前会话不可见，直到临时表被删除）。

内部临时表

内部临时表是一种特殊轻量级的临时表，用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。这些操作可能包括在优化阶段或者执行阶段。这种内部表对用户来说是不可见的，但是通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。内部临时表在SQL语句的优化过程中扮演着非常重要的角色， MySQL中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写SQL语句的时候应该尽量的去避免使用临时表。

内部临时表有两种类型：一种是HEAP临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要IO操作。另一种是OnDisk临时表，顾名思义，这种临时表会将数据存储在磁盘上。OnDisk临时表用来处理中间结果比较大的操作。如果HEAP临时表存储的数据大于MAX_HEAP_TABLE_SIZE（详情请参考MySQL手册中系统变量部分），HEAP临时表将会被自动转换成OnDisk临时表。OnDisk临时表在5.7中可以通过INTERNAL_TMP_DISK_STORAGE_ENGINE系统变量选择使用MyISAM引擎或者InnoDB引擎。

本篇文章主要介绍哪些操作可能会利用到内部临时表。如果用户在书写SQL语句的时候能够尽量少的使用内部临时表进行查询优化，将有效的提高查询执行的效率。

首先我们定义一个表t1，

CREATE TABLE t1( a int, b int); INSERT INTO t1 VALUES(1,2),(3,4);


下面所有的操作都是基于表t1进行举例的。

  • 在SQL语句中使用SQL_BUFFER_RESULT hint

SQL_BUFFER_RESULT主要用来让MySQL尽早的释放表上的锁。因为如果数据量很大的话，需要较长时间将数据发送到客户端，通过将数据缓冲到临时表中可以有效的减少读锁对表的占用时间。
例如：


	mysql> explain format=json select SQL_BUFFER_RESULT * from t1;
	EXPLAIN
	{
	  "query_block": {
		"select_id": 1,
		"cost_info": {
		  "query_cost": "2.00"
		},
		"buffer_result": {
		  "using_temporary_table": true,
		  "table": {
			"table_name": "t1",
			"access_type": "ALL",
		...


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

文章标签：
云数据库 RDS MySQL 版
关系型数据库
数据格式
JSON
MySQL
存储
关键词：
云数据库 RDS MySQL 版分析
云数据库 RDS MySQL 版特性
云数据库 RDS MySQL 版临时表
云数据库 RDS MySQL 版特性分析
云数据库 RDS MySQL 版内部临时表
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
db匠
目录
相关文章
jianz123
|
15天前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
jianz123
12 0
洛神灬殇
|
26天前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
洛神灬殇
95 0
jianz123
|
15天前
|
SQL 缓存 关系型数据库
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置
jianz123
80 1
技术蜜糖罐
|
21天前
|
缓存 关系型数据库 MySQL
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
技术蜜糖罐
37 0
季风泯灭的季节
|
2月前
|
SQL 关系型数据库 MySQL
Mysql事务隔离级别和锁特性
Mysql事务隔离级别和锁特性
季风泯灭的季节
47 0
爱吃糖的范同学
|
23天前
|
SQL 关系型数据库 MySQL
【MySQL】慢SQL分析流程
【4月更文挑战第1天】【MySQL】慢SQL分析流程
爱吃糖的范同学
30 1
洛神灬殇
|
26天前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（数据恢复补充篇）（一）
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（数据恢复补充篇）
洛神灬殇
30 0
源码星辰
|
27天前
|
存储 缓存 关系型数据库
MySQL事务的四大特性是如何保证的
在MySQL数据库中还有一种二进制日志，其用来基于时间点的还原及主从复制。从表面上来看其和重做日志非常相似，都是记录了对于数据库操作的日志。但是，从本质上来看有着非常大的不同。
源码星辰
14 1
1941623231718325
|
1月前
|
存储 关系型数据库 MySQL
TiDB与MySQL、PostgreSQL等数据库的比较分析
【2月更文挑战第25天】本文将对TiDB、MySQL和PostgreSQL等数据库进行详细的比较分析，探讨它们各自的优势和劣势。TiDB作为一款分布式关系型数据库，在扩展性、并发性能等方面表现突出；MySQL以其易用性和成熟性受到广泛应用；PostgreSQL则在数据完整性、扩展性等方面具有优势。通过对比这些数据库的特点和适用场景，帮助企业更好地选择适合自己业务需求的数据库系统。
1941623231718325
95 4
人不走空
|
1月前
|
SQL 关系型数据库 MySQL
深入理解MySQL事务特性：保证数据完整性与一致性
深入理解MySQL事务特性：保证数据完整性与一致性
人不走空
91 1
数据库

关系型数据库

热门文章

最新文章

  • 1
    PolarDB闪电助攻，《香肠派对》百亿好友关系实现毫秒级查询
  • 2
    为PostgreSQL讨说法 - 浅析《UBER ENGINEERING SWITCHED FROM POSTGRES TO MYSQL》
  • 3
    德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
  • 4
    PostgreSQL 如何实现批量更新、删除、插入
  • 5
    PostgreSQL upsert功能(insert on conflict do)的用法
  • 6
    Linux 性能诊断 perf使用指南
  • 7
    PostgreSQL 9种索引的原理和应用场景
  • 8
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 9
    PostgreSQL 触发器用法详解 - 1
  • 10
    MongoDB中使用的SCRAM-SHA1认证机制
  • 1
    下次老板问你MySQL如何优化时，你可以这样说，老板默默给你加工资
    16
  • 2
    MySQL慢日志的介绍以及如何使用问题
    7
  • 3
    MySQL的事务（看看也许有帮助呢）
    10
  • 4
    MySQL学习手册（第一部分）
    31
  • 5
    MySQL字段的时间类型该如何选择？千万数据下性能提升10%~30%🚀
    23
  • 6
    【设计模式系列笔记】建造者模式
    98
  • 7
    【设计模式系列笔记】工厂模式
    103
  • 8
    【设计模式系列笔记】抽象工厂模式
    100
  • 9
    【设计模式系列笔记】单例模式
    113
  • 10
    PolarDB闪电助攻，《香肠派对》百亿好友关系实现毫秒级查询
    125386

    • 相关产品

  • 云数据库 RDS MySQL 版
    文档详情 产品详情

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）