×
新网 > 建站推广 > 正文

数据库表分区可提升插入效率吗?

  • 作者:zccc
  • 来源:网络
  • 2020-07-22 15:02:49

数据库表分区可提升插入效率;数据库表分区可提高表增删改查的效率。数据库表分区进行插入操作的原理:当写入一条记录时,分区层打开并锁住所有的底层表,然后确定哪个分区接受这条

数据库表分区可提升插入效率;数据库表分区可提高表增删改查的效率。数据库表分区进行插入操作的原理:当写入一条记录时,分区层打开并锁住所有的底层表,然后确定哪个分区接受这条记录,再将记录写入对应的底层表。

什么是分区?

分区是按照规则分解表,将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了。app读写的时候操作的还是大表名字,db自动去组织分区的数据。

分区又可以分为两种:

1、水平分区(Horizontal Partitioning)

这种形式分区是对表的行进行分区,通过这样的方式不同分组里面的物理列分割的数据集得以组合,从而进行个体分割(单分区)或集体分割(1个或多个分区)。所有在表中定义的列在每个数据集中都能找到,

所以表的特性依然得以保持。

举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。(注:这里具体使用的分区方式我们后面再说,可以先说一点,一定要通过某个属性列来分割,譬如这里使用的列就是年份)

2、垂直分区(Vertical Partitioning)

这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个分区都包含了其中的列所对应的行。

举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。

分区表的原理

分区表是由多个相关的底层表实现,这些底层表也是由句柄对象表示,所以我们也可以直接访问各个分区,存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎),分区表的索引只是在各个底层表上各自加上一个相同的索引,从存储引擎的角度来看,底层表和一个普通表没有任何不同,存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

在分区表上的操作按照下面的操作逻辑进行:

select查询:

当查询一个分区表的时候,分区层先打开并锁住所有的底层表,优化器判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据

insert操作:

当写入一条记录时,分区层打开并锁住所有的底层表,然后确定哪个分区接受这条记录,再将记录写入对应的底层表

delete操作:

当删除一条记录时,分区层先打开并锁住所有的底层表,然后确定数据对应的分区,最后对相应底层表进行删除操作

update操作:

当更新一条数据时,分区层先打开并锁住所有的底层表,mysql先确定需要更新的记录在哪个分区,然后取出数据并更新,再判断更新后的数据应该放在哪个分区,然后对底层表进行写入操作,并对原数据所在的底层表进行删除操作

虽然每个操作都会打开并锁住所有的底层表,但这并不是说分区表在处理过程中是锁住全表的,如果存储引擎能够自己实现行级锁,如:innodb,则会在分区层释放对应的表锁,这个加锁和解锁过程与普通Innodb上的查询类似。

在下面的场景中,分区可以起到非常大的作用:

A:表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他都是历史数据

B:分区表的数据更容易维护,如:想批量删除大量数据可以使用清除整个分区的方式。另外,还可以对一个独立分区进行优化、检查、修复等操作

C:分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备

D:可以使用分区表来避免某些特殊的瓶颈,如:innodb的单个索引的互斥访问,ext3文件系统的inode锁竞争等

E:如果需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效果非常好

F:优化查询,在where字句中包含分区列时,可以只使用必要的分区来提高查询效率,同时在涉及sum()和count()这类聚合函数的查询时,可以在每个分区上面并行处理,最终只需要汇总所有分区得到的结果。

mysql数据库的分区总是把null当作比任何非null更小的值,这和数据库中处理null值的order by操作是一样的,升序排序时null总是在最前面,因此对于不同的分区类型,mysql数据库对于null的处理也各不相同。

对于range分区,如果向分区列插入了null,则mysql数据库会将该值放入最左边的分区,注意,如果删除分区,分区下的所有内容都从磁盘中删掉了,null所在分区被删除,null值也就跟着被删除了。

在list分区下要使用null,则必须显式地定义在分区的散列值中,否则插入null时会报错。hash和key分区对于null的处理方式和range,list分区不一样,任何分区函数都会将null返回为0.

分区

分区就是将数据库或其构成元素划分为不同的独立部分

--是一种预先组织表存储的方法

mysql支持水平分区

将特定表行分配为行的子集

分区的分布是跨物理存储进行的

--根据用户在需要时设置的指定规则

--每个分区存储为其自己的单元

数据的划分

--根据分区功能将数据划分为子集

--分区类型和表达式是表定义的一部分

--表达式可以是整数或返回整数值的函数。

--此值根据定义确定将每条记录存储在哪个分区中

1.primary key和unique key必须包含在分区key的一部分,否则在创建primary key和unique index时会报”ERROR 1503 (HY000)“

2.范围分区添加分区只能在最大值后面追加分区

3.所有分区的engine必须一样

4.范围分区分区字段:integer、数值表达式、日期列,日期函数表达式(如year(),to_days(),to_seconds(),unix_timestamp())

分区管理

新增分区

ALTER TABLE sale_data
ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

删除分区

–当删除了一个分区,也同时删除了该分区中所有的数据。

ALTER TABLE sale_data DROP PARTITION p201010;

分区的合并

下面的SQL,将p201001 – p201009 合并为3个分区p2010Q1 – p2010Q3

ALTER TABLE sale_data
REORGANIZE PARTITION p201001,p201002,p201003,
p201004,p201005,p201006,
p201007,p201008,p201009 INTO
(
PARTITION p2010Q1 VALUES LESS THAN (201004),
PARTITION p2010Q2 VALUES LESS THAN (201007),
PARTITION p2010Q3 VALUES LESS THAN (201010)
);

相关推荐:《 PHP教程 》、《mysql教程》

  • 相关专题

免责声明:本文内容由互联网用户自发贡献自行上传,本网站不拥有所有权,也不承认相关法律责任。如果您发现本社区中有涉嫌抄袭的内容,请发送邮件至:operations@xinnet.com进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。

免费咨询获取折扣

Loading