一, 分区概念
分区允许根据指定的规则,跨文件系统分配单个表的多个部分。表的不同部分在不同的位置被存储为单独的表。MySQL从5.1.3开始支持Partition。
分区和手动分表对比
手动分表 | 分区 |
多张数据表 | 一张数据表 |
重复数据的风险 | 没有数据重复的风险 |
写入多张表 | 写入一张表 |
没有统一的约束限制 | 强制的约束限制 |
MySQL支持RANGE,LIST,HASH,KEY分区类型,其中以RANGE最为常用:
- Range(范围)–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。
- Hash(哈希)–这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
- Key(键值)-上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。
- List(预定义列表)–这种模式允许系统通过预定义的列表的值来对数据进行分割。
- Composite(复合模式) –以上模式的组合使用
二,分区能做什么
- 逻辑数据分割
- 提高单一的写和读应用速度
- 提高分区范围读查询的速度
- 分割数据能够有多个不同的物理文件路径
- 高效的保存历史数据
- 一个表上的约束检查
- 不同的主从服务器分区策略,例如master按Hash分区,slave按range分区
三,分区的限制(截止5.1.44版)
• 只能对数据表的整型列进行分区,或者数据列可以通过分区函数转化成整型列
• 最大分区数目不能超过1024
• 如果含有唯一索引或者主键,则分区列必须包含在所有的唯一索引或者主键在内
• 不支持外键
• 不支持全文索引(fulltext)
- 按日期进行分区很非常适合,因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多
四,什么时候使用分区
• 海量数据表
• 历史表快速的查询,可以采用ARCHIVE+PARTITION的方式。
• 数据表索引大于服务器有效内存
• 对于大表,特别是索引远远大于服务器有效内存时,可以不用索引,此时分区效率会更有效。
一、为什么要分区?
需求:大数据。解决方案:分而治之,更细一点即为。将大表和大索引分为一个更小的操作单元在mysql中,分区允许将表、索引和索引编排表细分为更小的单元。分区后,每个分区有自己单独的名称。对于DBA来讲,这些分区可以统一管理,也可以分开管理。
但是对于应用程序来讲,分区的表和没有分区的表示一样的。换句话来讲,分区对于应用是透明的,只是 数据库对于数据的重新整理。
Mysql分区,这个是 mysql对于分区的官方解释。
1.分区的作用
a)提升性能应该说,几乎所有分区的目的都是用来提升性能的。不是谁哪天早上吃着鸡蛋灌饼,说咱们分个区玩玩吧!不是的。那么分区是如何来提升检索数据的性能的呢?没有分区之前,数据每次在查询和检索总是面向的是整个数据库和表。分区之后,mysql针对每个分区生成特定的数据文件与索引文件。每次只会检索特定的部分数据,因此可以更好的来执行和维护数据库。究其原因还是因为在分区之后表指派到不同的物理驱动器上,这样便可在同时访问多个分区时减少物理 I/O 争用。
b)易于管理
对于一些无用的冗余的历史数据来讲,可以根据分区之后直接删除相应的分区。操作起来更加简便,因为执行数以万计的数据远比直接删除文件来的困难的多、时间也更加的长。
c)容错
分区之后相比分区之前,一张表从之前一个文件分为现在的三个文件。分区之后即使一个文件出现破坏,也不会影响其他数据。
2..常用分区方法
RANGE分区
LIST分区
HASH分区
KEY分区
二、如何分区?
1.查看数据库是否支持分区
1
|
SHOW VARIABLES
LIKE
'%partition%'
;
|
对于mysql来讲,现阶段支持分区操作的版本有5.1和5.5.如图显示为YES则表明该数据库支持分区操作。
2.四种常见分区
Range分区
俗称:范围分区。根据表的字段的值,依据给定某段连续的区间来分区。
直接创建表时分区
1
2
3
4
5
6
7
8
9
10
11
12
13
|
create
table
teacher
(id
varchar
(20)
not
null
,
name
varchar
(20),
age
varchar
(20),
birthdate
date
not
null
,
salary
int
)
partition
by
range(
year
(birthdate))
(
partition p1
values
less than (1970),
partition p2
values
less than (1990),
partition p3
values
less than maxvalue
);
|
Ps:创建teacher表,并在创建teacher表同时根据birthdate字段将表划分为p1、p2、p3三个分区。
在创建表后分区
1
2
3
4
5
6
7
|
ALTER
TABLE
teacher
partition
by
range(
year
(birthdate))
(
partition p1
values
less than (1970),
partition p2
values
less than (1990),
partition p3
values
less than maxvalue
);
|
Ps:给已经创建了的表分区,分为p1、p2、p3.
LIST分区
俗名:列表分区。其实list分区和range分区应该说都是一样的,不同的是range分区在分区是的依据是一段连续的区间;而list分区针对的分区依据是一组分布的散列值。
1
2
3
4
5
6
7
8
9
10
11
12
|
create
table
student
(id
varchar
(20)
not
null
,
studentno
int
(20)
not
null
,
name
varchar
(20),
age
varchar
(20)
)
partition
by
list(studentno)
(
partition p1
values
in
(1,2,3,4),
partition p2
values
in
(5,6,7,8),
partition p3
values
in
(9,10,11)
);
|
Ps:如上创建表student,并将student表分为p1、p2、p3三个分区。需要注意的是一般情况下,针对表的分区字段为int等数值类型。
HASH分区
小名:哈希分区。哈希分区主要是依据表的某个字段以及指定分区的数量。
1
2
3
4
5
6
7
|
create
table
user
(
id
int
(20)
not
null
,
role
varchar
(20)
not
null
,
description
varchar
(50)
)
partition
by
hash(id)
partitions 10;
|
Ps:如上创建user表,并将user表平均分为十个分区。比较有限制的就是需要知道表的数据有多少才能更好平均分配分区。
key分区
类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
1
2
3
|
create
table
role( id
int
(20)
not
null
,
name
varchar
(20)
not
null
)
partition
by
linear
key
(id)
partitions 10;
|
3.分区表管理
对指定表添加分区
1
|
alter
table
user
add
partition(partition p4
values
less than MAXVALUE);
|
删除指定表指定分区
alter table student drop partition p1;
创建子分区
1
2
3
4
5
6
7
8
|
create
table
role_subp(id
int
(20)
not
null
,
name
int
(20)
not
null
)
partition
by
list(id)
subpartition
by
hash(
name
)
subpartitions 3
(
partition p1
values
in
(10),
partition p2
values
in
(20)
)
|
复合分区
1
2
3
|
alter
table
user
reorganize partition p1,p3
into
(partition p1
values
less than (1000));
|