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

这篇文章主要介绍了Mysql临时表及分区表区别详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下

临时表与内存表

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

临时表,可以使用各种引擎类型 。如果是使用innodb引擎或者myisam引擎的临时表,写 数据的时候是写到磁盘上的。当然,临时表也可以使用memory引擎。

临时表特性

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

  • 建表语法是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个数据库实例上。

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

分区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命令,来显示实例的临时文件目录。

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

这个进程的进程号是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不同,磁盘文件名 也不同,因此可以并存。

分区表的引擎层行为

?

1

2

3

4

5

6

7

8

9

10

11

12

13

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);

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

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

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

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

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

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

对于myisam引擎:

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

因为在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层看,一个分区表就是一个表。

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

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

小结:

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

分区表应用场景

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

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

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持钦钦技术栈。

原文链接:https://www.cnblogs.com/jimmyhe/p/11241929.html

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/11497/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-08-13 5:20:29
下一篇 2022-08-13 5:20:45

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!