什么是表分区，如何表分区
什么是表分区？表分区其实就是将⼀张⼤数据量表中的数据按照不同的分区策略分配到不同的系统分区、硬盘或是不同的服务器设备上，实现数据的均衡分配，这样做的好处是均衡⼤数据量数据到不同的存储介⼦中，这样每个分区均摊了⼀部分数据，然后可以定位到指定的分区中，对数据表进⾏需求操作，另外，也⽅便管理⽔表，⽐如要删除某个时间段的数据，就可以按照⽇期分区，然后直接删除该⽇期分区即可，并且效率相对于传统的DELETE数据效率⾼很多，这⾥以Mysql为例进⾏说明。
· 分区分表区别
· 表分区的原理
· 表分区的策略
· 表分区的实施
· 表分区的注意
⼀、分区分表区别
分区和分表针对的都是数据表，⽽分表是真正的⽣成数据表，是将⼀张⼤数据量的表分成多个⼩表实现数据均衡；分区并不是⽣成新的数据表，⽽是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介⼦中，实际上还是⼀张表。另外，分区和分表都可以做到将表的数据均衡到不同的地⽅，提⾼数据检索的效率，降低数据库的频繁IO压⼒值，分区的优点如下：
1、相对于单个⽂件系统或是硬盘，分区可以存储更多的数据；
transcribe2、数据管理⽐较⽅便，⽐如要清理或废弃某年的数据，就可以直接删除该⽇期的分区数据即可；
3、精准定位分区查询数据，不需要全表扫描查询，⼤⼤提⾼数据检索效率；
4、可跨多个分区磁盘查询，来提⾼查询的吞吐量；
5、在涉及聚合函数查询时，可以很容易进⾏数据的合并；
⼆、表分区的原理
表的分区的原理理解起来⽐较简单，其实就是把⼀张⼤数据量的表，根据分区策略进⾏分区，分区设置完成之后，由数据库⾃⾝的储存引擎来实现分发数据到指定的分区中去，正如上图所⽰，⼀张数据表被分成了n个分区，并且分区被放⼊到不同的介⼦disk中，每个disk中包含⾃少⼀个分区，这就实现
了数据的均衡以及通过跨分区介⼦检索提⾼了整体的数据操作IO吞吐率。
注：想通过表分区来提供查询性能，就是要提⾼磁盘IO性能，必然就需要实现IO的并发，所以表分区就需要放到不同的磁盘上才⾏。在单磁盘上进⾏表分区基本属于扯淡
三、表分区的策略
⽬前在MySql中⽀持四种表分区的⽅式，分别为HASH、RANGE、LIST及KEY，当然在其它的类型数据库中，分区的实现⽅式略有不同，但是分区的思想原理是相同，具体如下：
1、HASH
HASH分区主要⽤来确保数据在预先确定数⽬的分区中平均分布，⽽在RANGE和LIST分区中，必须明确指定⼀个给定的列值或列值集合应该保存在哪个分区中，⽽在HASH分区中，MySQL⾃动完成这些⼯作，你所要做的只是基于将要被哈希的列值指定⼀个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
⽐如：
CREATE TABLE t_product_item (
id int(7) not null,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(YEAR(createtime))
PARTITIONS 10
;
上⾯的例⼦，使⽤HASH函数对createtime⽇期进⾏HASH运算，并根据这个⽇期来分区数据，这⾥共分为10个分区。
NOTE：
可以通过在CREATE TABLE 语句上添加⼀个“PARTITION BY HASH (expr)”⼦句，其中“expr”是⼀个返回整数的表达式。它可以是字段类型为MySQL 整型的⼀列的名字，也可以是返回⾮负数的表达式。另外，可能需要在后⾯再添加⼀个“PARTITIONSnum”⼦句，其中num 是⼀个⾮负的整数，它表⽰表将要被分割成分区的数量。
2、RANGE
基于属于⼀个给定连续区间的列值，把多⾏分配给同⼀个分区，这些区间要连续且不能相互重叠，使⽤VALUES LESS THAN操作符来进⾏定义。
⽐如：
CREATE TABLE t_product_item (
id int(7) not null,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(producttype) (
PARTITIONP0 VALUES LESS THAN(2),
PARTITIONP1 VALUES LESS THAN(4),
PARTITIONp2 VALUES LESS THAN(6),
PARTITIONp3 VALUES LESS THAN MAXVALUE
openstack定位于);
上⾯的例⼦，使⽤了范围RANGE函数对产品类型进⾏分区，共分为4个分区，产品类别为0,1的对应在分区P0中，2,3类别在分区P1中，依次类推即可。那么类别编号⼤于6的怎么分区呢？我们可以使⽤MAXVALUE来将⼤于6的数据统⼀存放在分区P3中即可。
oracle题库3、LIST
类似于按RANGE分区，区别在于LIST分区是基于列值匹配⼀个离散值集合中的某个值来进⾏选择分区的。LIST分区通过使⽤“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或⼀个基于某个列值、并返回⼀个整数值的表达式，然后通过“VALUES IN (value_list)”的⽅式来定义每个分区，其中“value_list”是⼀个通过逗号分隔的整数列表。mysql是什么系统
⽐如：
DROP TABLE IF EXISTS t_product_item;
CREATE TABLE t_product_item (
id int(7) not null,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(producttype) (
PARTITIONP0 VALUES IN (0,1),
PARTITIONP1 VALUES IN (2,3),
PARTITIONP2 VALUES IN (4,5),
PARTITIONP3 VALUES IN (6,7,8,9,10,11,12)
)
excel指数函数公式输入以10为底上⾯的例⼦，使⽤了列表匹配LIST函数对产品类型进⾏分区，共分为4个分区，产品类别为0,1的对应在分区P0中，2,3类别在分区P1中，依次类推即可。那么类别编号⼤于12的怎么分区呢？这⾥不同于RANGE，LIST分区的数据必须匹配列表中的产品类别才能进⾏分区。
4、KEY
类似于按HASH分区，区别在于KEY分区只⽀持计算⼀列或多列，且MySQL 服务器提供其⾃⾝的哈希函数。必须有⼀列或多列包含整数值。
⽐如：
CREATE TABLE t_product_item (
id int(7) not null,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(producttype)
PARTITIONS 10;
NOTE：
此种分区算法⽬前使⽤的⽐较少，⼤家知道其存在和怎么使⽤即可。
四、表分区的实施
这⾥我以HASH分区算法为例，进⾏数据表分区的实现，具体如下：
1、建分区表
sql：
DROP TABLE IF EXISTS t_product_item;
CREATE TABLE t_product_item (
id int(7) not null,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(producttype) (
PARTITIONP0 VALUES LESS THAN(2),
PARTITIONP1 VALUES LESS THAN(4),
PARTITIONp2 VALUES LESS THAN(6),
PARTITIONp3 VALUES LESS THAN MAXVALUE
)
NOTE：
这⾥使⽤了range算法分区，⽬前分为4个分区，分区是按照产品的类别进⾏划分，具体说明请查看上⾯的range讲解说明。
2、插⼊数据
A、插⼊产品类型为0,1 共1条
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(0,'A','A-title',99.99,'null',0);
B、插⼊产品类型为2,3 共1条
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(1,'A','A-title',99.99,'null',3);
C、插⼊产品类型为4,5 共1条
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(2,'A','A-title',99.99,'null',5);
D、插⼊产品类型为6,7 共2条
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(3,'A','A-title',99.99,'null',6);
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(4,'A','A-title',99.99,'null',7);
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(5,'A','A-title',99.99,'null',8);
3、验证分区
⾸先，查看下各个分区信息及数据是否正确：
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
andtable_name='t_product_item';
执⾏结果：
从上图知道，我们根据RNAGE算法，通过产品类型producttype进⾏RNAGE运算，结果插⼊数据的结果都已经正确的插⼊到对应的分区位置。
其次，使⽤EXPLAIN分析查询sql：
explain select * from t_product_item where producttype=3;
执⾏结果：
execution中文翻译从上图知道，这次的查询数据是从partions=P0中直接查询的，⽽不是全表查询，所以如果数据量很⼤时会明显提⾼检索效率。
五、表分区的注意
1、引擎的统⼀
在对同⼀个表进⾏分区时，必须保证数据表的引擎相同，⽐如：不能对⼀个分区的表为InnoDB，⽽另⼀个分区的引擎为MySIAM。
2、分区关联性
在对数据表分区时，不能只对数据进⾏分区，需要连同其对应的索引等属性⼀同分区动作，某种程度上可以保持数据属性的完整。
3、分区的级别
对表进⾏分区之后，如果某个分区中的数据量依然很⼤或是增长迅速，那么你同样可以再进⾏⼦分区
操作，将该数据再分区到其它分区中。另外，如果在⼀个分区中使⽤了⼦分区，那么其它的⼦分区也必须定义。
4、LIST分区
LIST分区没有类似如“VALUESLESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中到。
5、Linear线性
分区策略KEY和HASH都⽀持使⽤线性LINEAR的算法，也就是分区的编号是通过2的幂（powers-of-two）算法得到，⽽不是通过模数算法。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论