文档章节

mysql优化: 内存表和临时表

zyt_1978
 zyt_1978
发布于 2016/09/25 22:55
字数 922
阅读 17
收藏 0

【推荐】2019 Java 开发者跳槽指南.pdf(吐血整理) >>>

由于直接使用临时表来创建中间表,其速度不如人意,因而就有了把临时表建成内存表的想法。但内存表和临时表的区别且并不熟悉,需要查找资料了。
一开始以为临时表是创建后存在,当连接断开时临时表就会被删除,即临时表是存在于磁盘上的。而实际操作中发现临时表创建后去目录下查看发现并没有发现对应的临时表文件(未断开链接).因而猜测临时表的数据和结构都是存放在内存中,而不是在磁盘中.
    这样一想内存表不是也是存在在内存中吗,那么他和临时表有什么区别?他们的速度是什么样子?

    查找了官方手册有以下的一些解释:
The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility.

Each MEMORY table is associated with one disk file. The filename begins with the table name and has an extension of .frm to indicate that it stores the table definition. 

由此可以看出来内存表会把表结构存放在磁盘上,把数据放在内存中
并做了以下实验:
临时表
mysql> create temporary table tmp1(id int not null);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tmp1;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                               |
+-------+----------------------------------------------------------------------------------------------+
| tmp1   | CREATE TEMPORARY TABLE `tmp1` ( `id` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8    |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

内存表
mysql> create table tmp2(id int not null) TYPE=HEAP;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tmp2;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table                                                                       |
+-------+------------------------------------------------------------------------------------+
| tmp2   | CREATE TABLE `tmp2` (
   `id` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看出来临时表和内存表的ENGINE 不同,临时表默认的是MyISAM,而内存表是MEMORY .去数据库目录查看,发现tmp2.frm而没有tmp1表的任何文件。看来实际情况是符合官方解释的。


那么速度方面呢(即MyISAM和MEMORY之间的区别)?
实验开始:
实现手段:对基于2张千万级别的表做一些OLAP切分操作,中间表的建立使用2种不同的方式。最后把中间表的数据按照要求取出,插入到结果表中
实验目的;测试临时内存表和临时表的速度
1.中间表的建立使用Create temporary table type = heap 即 把中间表建立成临时内存表 
2.中间表直接使用Create temporary table建立 

实验结果:
临时内存表: 1小时
1 2008-09-25 11:03:48
1 2008-09-25 12:03:39
临时表:1小时17分钟
2 2008-09-25 12:25:28
2 2008-09-25 13:42:37

由此发现MEMORY比MyISAM快大概20%。


接着查找官方手册:
As indicated by the name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in MEMORY tables are lost. The tables themselves continue to exist because their definitions are stored in .frm files on disk, but they are empty when the server restarts.


可以看出来MEMORY确实是very fast,and very useful for creating temporary tables .把临时表和内存表放在一起使用确实会快不少:create table tmp2(id int not null) engine memory;

内存表的建立还有一些限制条件:
MEMORY tables cannot contain        BLOB or TEXT columns. HEAP不支持BLOB/TEXT列。    
The server needs sufficient memory to maintain all   MEMORY tables that are in use at the same time. 在同一时间需要足够的内存.
To free memory used by a MEMORY table when   you no longer require its contents, you should execute DELETE or TRUNCATE TABLE, or remove the table altogether using DROP        TABLE.为了释放内存,你应该执行DELETE FROM heap_table或DROP TABLE heap_table。

本文转载自:http://www.cnblogs.com/tuyile006/archive/2009/02/06/1385121.html

zyt_1978
粉丝 9
博文 166
码字总数 21058
作品 0
程序员
私信 提问
很好的一篇讲解sql优化的文章

【问题现象】 线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。 SQL语句如下: SELECT DISTINCT g.*, cp.name AS cp_name, c.nam...

fzxu_05
2014/06/10
271
0
优化临时表使用,SQL语句性能提升100倍

【问题现象】 线上mysql数据库爆出一个慢查询,DBA观察发现,查询时服务器IO飙升,IO占用率达到100%, 执行时间长达7s左右。 SQL语句如下: SELECT DISTINCT g.*, cp.name AS cp_name, c.nam...

markGao
2014/06/10
107
0
mysql的tmp_table_size和max_heap_table_size

先说下tmptablesize吧: 它规定了内部内存临时表的最大值,每个线程都要分配。(实际起限制作用的是tmptablesize和maxheaptablesize的最小值。)如果内存临时表超出了限制,MySQL就会自动地把...

stone_ryan
2018/06/26
0
0
MYSQL调优总结1

如果一台服务器出现长时间负载过高 /周期性负载过大,或偶尔卡住如何来处理? 答:大的思路-------- 是周期性的变化还是偶尔问题? 是服务器整体性能的问题, 还是某单条语句的问题? 具体到单条语...

泡海椒
2016/04/05
23
0
MYSQL调优总结1

如果一台服务器出现长时间负载过高 /周期性负载过大,或偶尔卡住如何来处理? 答:大的思路-------- 是周期性的变化还是偶尔问题? 是服务器整体性能的问题, 还是某单条语句的问题? 具体到单条语...

泡海椒
2016/04/05
14
0

没有更多内容

加载失败,请刷新页面

加载更多

密码加密与微服务鉴权JWT详细使用教程

[TOC] 1.1、了解微服务状态 微服务集群中的每个服务,对外提供的都是Rest风格的接口,而Rest风格的一个最重要的规范就是:服务的无状态性。 什么是无状态? 1.服务端不保存任何客户端请求者信...

庭前云落
12分钟前
5
0
Xamarin.FormsShell基础教程(9)Shell相关类体系

Xamarin.FormsShell基础教程(9)Shell相关类体系 在Shell中,最为主要的类是Shell类。Shell类实现了大多数应用程序所需的基本UI功能的页面。除此以外,常用的类还有ShellContent、Tab、Fly...

大学霸
14分钟前
3
0
聊聊MVC、MVP、MVVM吧

先来介绍下本文结构: 三种框架的具体概念 在安卓开发上的写法、优缺点 1.概念 1.1 MVC MVC模式(Model–view–controller)是软件工程中的一种软件架构模式,把软件系统分为三个基本部分:模...

二营长的意大利炮手
31分钟前
3
0
如果解释Python,什么是.pyc文件?

我已经了解Python是一种解释型语言......但是,当我查看我的Python源代码时,我看到.pyc文件,Windows将其识别为“编译的Python文件”。 这些来自哪里? #1楼 Python代码经历了两个阶段。 第...

javail
36分钟前
3
0
.NET Core初览

初览的应用场景为游戏服务器开发。所以测试在侧重点上更强于IO和密集型计算 TODO

梦想游戏人
39分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部