Mysql临时表及分区表区别详解

临时表与内存表

内存表，指的是使用Memory引擎的表，建表语法是create table … engine=memory。这种 表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看 上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表

临时表，可以使用各种引擎类型 。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写 数据的时候是写到磁盘上的。当然，临时表也可以使用Memory引擎。

临时表特性

  • 建表语法是create temporary table …。
  • 一个临时表只能被创建它的session访问，对其他线程不可见。所以，图中session A创建的 临时表t，对于session B就是不可见的。
  • 临时表可以与普通表同名。
  • session A内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。
  • show tables命令不显示临时表。

由于临时表只能被创建它的session访问，所以在这个session结束的时候，会自动删除临时表。 也正是由于这个特性，临时表就特别适合join优化这种场景。

create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

不同session的临时表是可以重名的，如果有多个session同时执行join优化，不需要担心表名重复导致建表失败的问题。不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。临时表的应用

分库分表系统的跨库查询

一般分库分表的场景，就是要把一个逻辑上的大表分散到不同的数据库实例上。比如。将一个大 表ht，按照字段f，拆分成1024个分表，然后分布到32个数据库实例上。

分区key的选择是以“减少跨库和跨表查询”为依据的。如果大部分的语句都会包 含f的等值条件，那么就要用f做分区键。这样，在proxy这一层解析完SQL语句以后，就能确定将这条语句路由到哪个分表做查询。 比如

select v from ht where f=N;

这时，我们就可以通过分表规则（比如，N%1024)来确认需要的数据被放在了哪个分表上。这种语句只需要访问一个分表，是分库分表方案最欢迎的语句形式了。

但是，如果这个表上还有另外一个索引k，并且查询语句是这样的：

select v from ht where k >= M order by t_modified desc limit 100;

这时候，由于查询条件里面没有用到分区字段f，只能到所有的分区中去查找满足条件的所有 行，然后统一做order by 的操作。这种情况下，有两种比较常用的思路：

在proxy层的进程代码中实现排序，对proxy端的压力比较大，尤其是很容易出现内存不够用和CPU瓶颈的问题。

把各个分库拿到的数据，汇总到一个MySQL实例的一个表中，然后在这个汇总实例上做逻辑操作。

在汇总库上创建一个临时表temp_ht，表里包含三个字段v、k、t_modifified；

在各个分库上执行

select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

把分库执行的结果插入到temp_ht表中；

执行

select v from temp_ht order by t_modified desc limit 100;

为什么临时表可以重名

create temporary table temp_t(id int primary key)engine=innodb;

执行这个语句的时候，MySQL要给这个InnoDB表创建一个frm文件保存表结构定义，还要有地方保存表数据。

这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程id}_{线程id}_序列 号”。你可以使用select @@tmpdir命令，来显示实例的临时文件目录。

这个进程的进程号是1234，session A的线程id是4，session B的线程id是5。所以session A和session B创建的临时表，在磁盘上的文件不会重名

MySQL维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个table_def_key。 对于临时表，table_def_key在“库名+表名”基础上，又加入了“server_id+thread_id”。

也就是说，session A和sessionB创建的两个临时表t1，它们的table_def_key不同，磁盘文件名 也不同，因此可以并存。

分区表的引擎层行为

ATE	TABLE	`t`	(
		`ftime`	datetime	NOT	NULL,
		`c`	int(11)	DEFAULT	NULL,
		KEY	(`ftime`)
)	ENGINE=InnoDB	DEFAULT	CHARSET=latin1
PARTITION	BY	RANGE	(YEAR(ftime))
Û ॔ګդᎱ
B
 (PARTITION	p_2017	VALUES	LESS	THAN	(2017)	ENGINE	=	InnoDB,
 	PARTITION	p_2018	VALUES	LESS	THAN	(2018)	ENGINE	=	InnoDB,
 	PARTITION	p_2019	VALUES	LESS	THAN	(2019)	ENGINE	=	InnoDB,
 PARTITION	p_others	VALUES	LESS	THAN	MAXVALUE	ENGINE	=	InnoDB);
 insert	into	t	values('2017-4-1',1),('2018-4-1',1);

初始化表的时候，只插入了两行数据，sessionA的select语句对ftime这两个记录之间的间隙加了锁，间隙和加锁状态如图：

也就是说，2017-4-1和2018-4-1这两个记录之间的间隙会被锁住，那么sessionB的两条插入语句都应该进入锁等待状态。但是从效果上看，第一个insert语句是可以执行成功的，因为对于引擎来说，p2018和p2019是不同的表，2017的下一个记录不是2018-4-1而是p2018中的supremum，所以在t1时刻索引如图：

由于分区表的规则，sessionA只操作了p2018，sessionB要插入2018-2-1是可以的但要写入2017-12-1要等待sessionA的间隙锁。

对于MYISAM引擎：

因为在sessionA中，sleep了100秒，由于myisam只支持表锁，所以这条update会锁住整个表t的读，但是结果是，B的第一条语句是可以执行的，第二条语句才进入锁等待状态。

这是myisam表锁只在引擎层实现的，sessionA加的表锁，是所在p2018上，因此只会堵住分区上执行的查询，落到其他分区的查询不受影响。这样看来，分区表还不错，为什么不用呢，我们使用分区表的一个原因就是单表过大，那么不使用分区表，就要使用手动分表的方式。

手动分表需要创建t_2017,t_2018,t_2019，也就是找到需要更新的所有分表，依次执行，这和分区表无实质的差别，两者一个由serverceng决定使用哪个分区，一个由应用层代码决定使用哪个分表，因此，从引擎层看无实际差别。其实主要区别是在server层：打开表行为。

分区策略

每当第一次访问一个分区表时，mysql需要把所有分区都访问一遍：如果分区很多，比如查过了1000个，mysql启动的时候，open_files_limit默认为1024，那么就会在访问表的时候，由于打开了所有文件，超过了上限而报错。

mysiam使用的分区策略成为通用分区策略，每次访问分区都是有server层控制。有比较严重的性能问题。

innodb引擎引入了本地分区策略，是在innodb内部自己管理打开分区的行为。

分区表的server层行为

从server层看，一个分区表就是一个表。

虽然B只操作2017分区，但是由于A持有整个表t的mdl锁，导致了B的alter语句被堵住。如果是使用普通分表，不会跟另外一个分表上的查询语句出现MDL冲突。

小结：

  • mysql在第一次打开分区表的时候，需要访问所有分区
  • 在server层，认为这是同一张表，因此所有分区公用MDL锁
  • 在引擎层，认为这是不同的表，因此在MDL锁之后，会根据分区表规则，只访问必要的分区。

分区表应用场景

分区表的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁，分区表可以很方便的清理历史数据。

alter table t drop partition 操作是删除分区文件，效果跟drop类似，与delete相比，优势是速度快，对系统影响小。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。

赞 (0)

相关推荐

  • MySQL问答系列之什么情况下会用到临时表

    临时表介绍 什么是临时表:MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间.为什么会产生临时表:一般是由于复杂的SQL导致临时表被大量创建 临时表分为两种,一种是内存临时表,一种是磁盘临时表.内存临时表采用的是memory存储引擎,磁盘临时表采用的是myisam存储引擎(磁盘临时表也可以使用innodb存储引擎,通过internal_tmp_disk_storage_engine参数来控制使用哪种存储引擎,从mysql5.7.6之后

  • MySQL分表和分区的具体实现方法

    垂直分表 垂直分表就是一个包含有很多列的表拆分成多个表,比如表A包含20个字段,现在拆分成表A1和A2,两个表各十个字段(具体如何拆根据业务来选择). 优势:在高并发的情境下,可以减少表锁和行锁的次数. 劣势:在数据记录非常大的情况下,读写速度还是会遇到瓶颈. 水平分表 假如某个网站,它的数据库的某个表已经达到了上亿条记录,那么此时如果通过select去查询,在没有索引的情况下,他的查询会非常慢,那么就可以通过hash算法将这个表分成10个子表(此时每个表的 的数据量只有1000万条了). 同时

  • MySQL两种临时表的用法详解

    外部临时表 通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表.这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭.这种临时表的命名与非临时表可以同名(同名后非临时表将对当前会话不可见,直到临时表被删除). 内部临时表 内部临时表是一种特殊轻量级的临时表,用来进行性能优化.这种临时表会被MySQL自动创建并用来存储某些操作的中间结果.这些操作可能包括在优化阶段或者执行阶段.这种内部表对用户来说是不可见的,但是通过EXPLAIN或者SHOW S

  • MySQL分区表的基本入门教程

    前言 在最近的项目中,我们需要保存大量的数据,而且这些数据是有有效期的,为了提供查询效率以及快速删除过期数据,我们选择了MySQL的分区机制.把数据按照时间进行分区. 分区类型 Range分区:最为常用,基于属于一个给定连续区间的列值,把多行分配给分区.最常见的是基于时间字段. 基于分区的列最好是整型,如果日期型的可以使用函数转换为整型. List分区:LIST分区和RANGE分区类似,区别在于LIST是枚举值列表的集合,RANGE是连续的区间值的集合. Hash分区:基于给定的分区个数,将数据

  • MySQL分区表的最佳实践指南

    前言: 分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表.但是对于应用程序来讲,分区的表和没有分区的表是一样的.换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理.本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助. 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据.在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这

  • MySQL临时表的简单用法介绍

    当工作在非常大的表上时,你可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录选择到一个临时表可能更快些,然后在这些表运行查询. 创建临时表很容易,给正常的CREATE TABLE语句加上TEMPORARY关键字: CREATE TEMPORARY TABLE tmp_table ( name VARCHAR(10) NOT NULL, value INTEGER NOT NULL ) 临时表将在你连接MySQL期间存在.当

  • MySQL中Update、select联用操作单表、多表，及视图与临时表的区别

    一.MySQL中使用从表A中取出数据来更新表B的内容 例如:要update表data中的一些列属性,但是修改属性的内容来源是来自表chanpin.SQL语言中不要显示的出现select关键字 update data d,chanpin c set d.zhulei=c.zhulei,d.xiaolei=c.xiaolei,d.fenxiang=c.fenxiang,d.zhuanye=c.zhuanye,d.jiliang=c.jiliang,d.gs=c.zgs,d.xzgs=c.zgs,d.

  • MySQL分区表的正确使用方法

    MySQL分区表概述 我们经常遇到一张表里面保存了上亿甚至过十亿的记录,这些表里面保存了大量的历史记录. 对于这些历史数据的清理是一个非常头疼事情,由于所有的数据都一个普通的表里.所以只能是启用一个或多个带where条件的delete语句去删除(一般where条件是时间). 这对数据库的造成了很大压力.即使我们把这些删除了,但底层的数据文件并没有变小.面对这类问题,最有效的方法就是在使用分区表.最常见的分区方法就是按照时间进行分区. 分区一个最大的优点就是可以非常高效的进行历史数据的清理. 1.

  • MySQL最佳实践之分区表基本类型

    MySQL分区表概述 随着MySQL越来越流行,Mysql里面的保存的数据也越来越大.在日常的工作中,我们经常遇到一张表里面保存了上亿甚至过十亿的记录.这些表里面保存了大量的历史记录. 对于这些历史数据的清理是一个非常头疼事情,由于所有的数据都一个普通的表里.所以只能是启用一个或多个带where条件的delete语句去删除(一般where条件是时间). 这对数据库的造成了很大压力.即使我们把这些删除了,但底层的数据文件并没有变小.面对这类问题,最有效的方法就是在使用分区表.最常见的分区方法就是按

  • Mysql临时表及分区表区别详解

    临时表与内存表 内存表,指的是使用Memory引擎的表,建表语法是create table - engine=memory.这种 表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在.除了这两个特性看 上去比较"奇怪"外,从其他的特征上看,它就是一个正常的表 临时表,可以使用各种引擎类型 .如果是使用InnoDB引擎或者MyISAM引擎的临时表,写 数据的时候是写到磁盘上的.当然,临时表也可以使用Memory引擎. 临时表特性 建表语法是create temporary ta

  • MySQL临时表的使用方法详解

    MySQL临时表的使用方法详解

    目录 1. 写在前面的话 2. 临时表的使用 2.1 创建一个只存放亚洲国家信息的临时表 2.1.1 创建临时表 2.1.2 向临时表里写数据 2.2 在查询过程中直接创建临时表 2.3 查询临时表中的数据 2.4 删除临时表 3. 以上操作的全部代码 总结 1. 写在前面的话 在开发数据库时,特别是写存储过程,遇到比较复杂的需求,使用临时表可以简化很多逻辑.曾经在一家互联网金融公司供职,公司数据组团队做数据清洗,写SQL脚本时,一个查询语句可以套到数层查询,甚至十几层.看起来几百行上千行的脚本

  • mysql中#{}和${}的区别详解

    mysql中#{}和${}的区别详解

    #{}会将传入的数据当成一个字符串,会对自动传入的数据加一个双引号 order by #{userId}   这里假如userId = 111,那么解析成sql时会变成 order by "111"这里如果userId = idStr,那么解析成sql时会变成 order by "idStr" ${}会将传入的数据直接显示生成在sql中 order by #{userId}  这里假如userId = 111,那么解析成sql时会变成 order by 111这里如

  • MySQL中int(10)和int(11)的区别详解

    目录 一.背景 二.MySQL整数类型 总结: 一.背景 在创建数据库表的时候,我们经常会用到int(x)来定义一个字段的类型,一直误以为这里的x表示存储数字的长度. 其实大错特错,这里的 x 指的是 最大显示宽度(最大有效显示宽度是255),且显示宽度与存储大小或类型包含的值的范围无关. 二.MySQL整数类型 类型 字节 取值范围 显示宽度 tinyint 1 -128 ~ 127 4 smallint 2 -32768 ~ 32767 6 mediumint 3 -8388608 ~ 83

  • Oracle10个分区和Mysql分区区别详解

    Oracle10g分区常用的是:range(范围分区).list(列表分区).hash(哈希分区).range-hash(范围-哈希分区).range-list(列表-复合分区). Range分区:Range分区是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中. 如按照时间划分,2010年1月的数据放到a分区,2月的数据放到b分区,在创建的时候,需要指定基于的列,以及分区的范围值. 在按时间分区时,如果某些记录暂无法预测范围,可以创建m

  • MySQL中Decimal类型和Float Double的区别(详解)

    MySQL中存在float,double等非标准数据类型,也有decimal这种标准数据类型. 其区别在于,float,double等非标准类型,在DB中保存的是近似值,而Decimal则以字符串的形式保存数值. float,double类型是可以存浮点数(即小数类型),但是float有个坏处,当你给定的数据是整数的时候,那么它就以整数给你处理.这样我们在存取货币值的时候自然遇到问题,我的default值为:0.00而实际存储是0,同样我存取货币为12.00,实际存储是12. 幸好mysql提供

  • MySQL中索引与视图的用法与区别详解

    前言 本文主要给大家介绍了关于MySQL中索引与视图的使用与区别的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧. 索引 一.概述 所有的Mysql列类型都可以被索引. mysql支持BTREE索引.HASH索引.前缀索引.全文本索引(FULLTEXT)[只有MyISAM引擎支持,且仅限于char,varchar,text列].空间列索引[只有MyISAM引擎支持,且索引的字段必须非空],但不支持函数索引. MyISAM和InnoDB存储引擎的表默认创建BTREE索引,

  • MySQL的视图和索引用法与区别详解

    MySQL的视图 简单来说MySQL的视图就是对SELECT 命令的定义的一个快捷键,我们查询时会用到非常复杂的SELECT语句,而这个语句我们以后还会经常用到,我们可以经这个语句生产视图.视图是一个虚拟的表,它不存储数据,所用的数据都在真实的表中. 这样做的好处有: 1.防止有未经允许的租户访问到敏感数据 2.将多个物理表抽象成一个逻辑表 3.结果容易理解 4.获得数据更容易,很多人对SQL语句不太了解,我们可以通过创建视图的形式方便用户使用. 5.显示数据更容易. 6.维护程序更方便.调试视

  • MySQL 行锁和表锁的含义及区别详解

    一.前言 对于行锁和表锁的含义区别,在面试中应该是高频出现的,我们应该对MySQL中的锁有一个系统的认识,更详细的需要自行查阅资料,本篇为概括性的总结回答. MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认的引擎.MyISAM不支持行锁,而InnoDB支持行锁和表锁. 相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制. MySQL大致可归纳为以下3种锁: 表级锁:开销小,加锁快:不会出现死锁:锁定粒度大,发生锁冲突的概率

  • MySQL null与not null和null与空值''''的区别详解

    相信很多用了MySQL很久的人,对这两个字段属性的概念还不是很清楚,一般会有以下疑问: 我字段类型是not null,为什么我可以插入空值 为毛not null的效率比null高 判断字段不为空的时候,到底要 select * from table where column <> '' 还是要用 select * from table wherecolumn is not null 呢. 带着上面几个疑问,我们来深入研究一下null 和 not null 到底有什么不一样. 首先,我们要搞清楚

随机推荐