AI 助理
备案控制台
开发者社区 数据库 关系型数据库 文章 正文

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

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

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 版内部临时表
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
db匠
目录
相关文章
小王老师呀
|
15天前
|
存储 关系型数据库 MySQL
MySQL 8.0特性-自增变量的持久化
【11月更文挑战第8天】在 MySQL 8.0 之前，自增变量（`AUTO_INCREMENT`）的行为在服务器重启后可能会发生变化，导致意外结果。MySQL 8.0 引入了自增变量的持久化特性，将其信息存储在数据字典中，确保重启后的一致性。这提高了开发和管理的稳定性，减少了主键冲突和数据不一致的风险。默认情况下，MySQL 8.0 启用了这一特性，但在升级时需注意行为变化。
小王老师呀
33 1
绿水长流*z
|
2月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现，而持久性则依赖于事务日志中的`Redo Log`。在MySQL中，`Redo Log`确保已提交事务的数据能持久保存，即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改，待事务提交时写入磁盘。此外，`Redo Log`采用简单的物理日志格式和高效的顺序IO，确保快速提交。通过不同的落盘策略，可在性能和安全性之间做出权衡。
绿水长流*z
1646 14
小王老师呀
|
2月前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则，包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限，后者又优先于表级别权限。在权限冲突时，更严格的权限将被优先执行，确保数据库的安全性与资源合理分配。
小王老师呀
44 4
kitwang
|
3月前
|
JSON 关系型数据库 MySQL
MySQL 8.0 新特性
MySQL 8.0 新特性
kitwang
149 10
MySQL 8.0 新特性
程序员小海绵
|
3月前
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别？从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
程序员小海绵
547 18
Oracle和MySQL有哪些区别？从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
sunrr
|
2月前
|
SQL 安全 关系型数据库
MySQL8.2有哪些新特性？
【10月更文挑战第3天】MySQL8.2有哪些新特性？
sunrr
40 2
糜终
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
糜终
122 4
baker_zhuang
|
2月前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库，实现自然语言查询功能，包括环境搭建和配置流程。
baker_zhuang
222 0
糜终
|
3月前
|
Oracle NoSQL 关系型数据库
主流数据库对比：MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比：MySQL、PostgreSQL、Oracle和Redis的优缺点分析
糜终
460 2
蓝易云
|
3月前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施，可以有效地维护MySQL主从复制环境中数据的一致性和完整性，确保数据库系统的稳定性和可靠性。
蓝易云
116 6
数据库

关系型数据库

热门文章

最新文章

  • 1
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 2
    鉴黄、视频、图片去重、图像搜索业务分析与实践
  • 3
    Linux cgroup资源隔离各个击破之 - cpu隔离1
  • 4
    让人敬佩的白发程序员——MySQL/MariaDB之父Monty阿里交流会
  • 5
    HTAP数据库(OLTP+OLAP) - 数据库典型架构 优缺点剖析(shard VS shared)
  • 6
    使用pg_resetxlog修复PostgreSQL控制文件的方法
  • 7
    Greenplum 添加或删除standby master节点
  • 8
    PostgreSQL 逻辑结构 和 权限体系 介绍
  • 9
    PostgreSQL 自定义自动类型转换(CAST)
  • 10
    PostgreSQL SQL HINT的使用
  • 1
    【赵渝强老师】PostgreSQL中的模式
    23
  • 2
    【赵渝强老师】PostgreSQL的表空间
    10
  • 3
    Sybase数据恢复—Sybase数据库常见问题之数据库无法启动的恢复案例
    15
  • 4
    【赵渝强老师】PostgreSQL的数据库
    22
  • 5
    Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
    13
  • 6
    【赵渝强老师】PostgreSQL的数据库集群
    21
  • 7
    【赵渝强老师】PostgreSQL的逻辑存储结构
    45
  • 8
    【赵渝强老师】MySQL的参数文件
    46
  • 9
    【赵渝强老师】MySQL的全量日志文件
    31
  • 10
    【赵渝强老师】MySQL的慢查询日志
    52

    • 相关产品

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

    • 相关课程

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

    • 相关电子书

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

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    无影云桌面