分区的二种方式
1,横向分区
什么是横向分区呢?就是横着来分区了,举例来说明一下,假如有100W条数据,分成十份,前10W条数据放到第一个分区,第二个10W条数据放到第二个分区,依此类推。也就是把表分成了十分,根用merge来分表,有点像哦。取出一条数据的时候,这条数据包含了表结构中的所有字段,也就是说横向分区,并没有改变表的结构。
2,纵向分区
什么是纵向分区呢?就是竖来分区了,举例来说明,在设计用户表的时候,开始的时候没有考虑好,而把个人的所有信息都放到了一张表里面去,这样这个表里面就会有比较大的字段,如个人简介,而这些简介呢,也许不会有好多人去看,所以等到有人要看的时候,在去查找,分表的时候,可以把这样的大字段,分开来。
感觉数据库的分区好像是切苹果,到底是横着切呢,还是竖着切,根据个人喜好了,mysql提供的分区属于第一种,横向分区,并且细分成很多种方式。下面将举例说明一下。
二,mysql的分区
我觉着吧,mysql的分区只有一种方式,只不过运用不同的算法,規则将数据分配到不同的区块中而已。
1,mysql5.1及以上支持分区功能
安装安装的时候,我们就可以查看一下
1
2
3
4
5
6
|
[
root
@
BlackGhost
mysql
-
5.1.50
]
# ./configure --help |grep -A 3 Partition
===
Partition
Support
===
Plugin
Name
:
partition
Description
:
MySQL
Partitioning
Support
Supports
build
:
static
Configurations
:
max
,
max
-
no
-
ndb
|
查看一下,如果发现有上面这个东西,说明他是支持分区的,默认是打开的。如果你已经安装过了mysql的话
1
2
3
4
5
6
7
|
mysql
&
gt
;
show
variables
like
"%part%"
;
+
--
--
--
--
--
--
--
--
--
-
+
--
--
--
-
+
|
Variable_name
|
Value
|
+
--
--
--
--
--
--
--
--
--
-
+
--
--
--
-
+
|
have_partitioning
|
YES
|
+
--
--
--
--
--
--
--
--
--
-
+
--
--
--
-
+
1
row
in
set
(
0.00
sec
)
|
查看一下变量,如果支持的话,会有上面的提示的。
2,range分区
按照RANGE分区的表是通过如下一种方式进行分区的,每个分区包含那些分区表达式的值位于一个给定的连续区间内的行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
|
//创建range分区表
mysql
&
gt
;
CREATE
TABLE
IF
NOT
EXISTS
`
user
`
(
-
&
gt
;
`
id
`
int
(
11
)
NOT
NULL
AUTO_INCREMENT
COMMENT
'用户ID'
,
-
&
gt
;
`
name
`
varchar
(
50
)
NOT
NULL
DEFAULT
''
COMMENT
'名称'
,
-
&
gt
;
`
sex
`
int
(
1
)
NOT
NULL
DEFAULT
'0'
COMMENT
'0为男,1为女'
,
-
&
gt
;
PRIMARY
KEY
(
`
id
`
)
-
&
gt
;
)
ENGINE
=
MyISAM
DEFAULT
CHARSET
=
utf8
AUTO_INCREMENT
=
1
-
&
gt
;
PARTITION
BY
RANGE
(
id
)
(
-
&
gt
;
PARTITION
p0
VALUES
LESS
THAN
(
3
)
,
-
&
gt
;
PARTITION
p1
VALUES
LESS
THAN
(
6
)
,
-
&
gt
;
PARTITION
p2
VALUES
LESS
THAN
(
9
)
,
-
&
gt
;
PARTITION
p3
VALUES
LESS
THAN
(
12
)
,
-
&
gt
;
PARTITION
p4
VALUES
LESS
THAN
MAXVALUE
-
&
gt
;
)
;
Query
OK
,
0
rows
affected
(
0.13
sec
)
//插入一些数据
mysql
&
gt
;
INSERT
INTO
`
test
`
.
`
user
`
(
`
name
`
,
`
sex
`
)
VALUES
(
'tank'
,
'0'
)
-
&
gt
;
,
(
'zhang'
,
1
)
,
(
'ying'
,
1
)
,
(
'张'
,
1
)
,
(
'映'
,
0
)
,
(
'test1'
,
1
)
,
(
'tank2'
,
1
)
-
&
gt
;
,
(
'tank1'
,
1
)
,
(
'test2'
,
1
)
,
(
'test3'
,
1
)
,
(
'test4'
,
1
)
,
(
'test5'
,
1
)
,
(
'tank3'
,
1
)
-
&
gt
;
,
(
'tank4'
,
1
)
,
(
'tank5'
,
1
)
,
(
'tank6'
,
1
)
,
(
'tank7'
,
1
)
,
(
'tank8'
,
1
)
,
(
'tank9'
,
1
)
-
&
gt
;
,
(
'tank10'
,
1
)
,
(
'tank11'
,
1
)
,
(
'tank12'
,
1
)
,
(
'tank13'
,
1
)
,
(
'tank21'
,
1
)
,
(
'tank42'
,
1
)
;
Query
OK
,
25
rows
affected
(
0.05
sec
)
Records
:
25
Duplicates
:
0
Warnings
:
0
//到存放数据库表文件的地方看一下,my.cnf里面有配置,datadir后面就是
[
root
@
BlackGhost
test
]
# ls |grep user |xargs du -sh
4.0K
user
#P#p0.MYD
4.0K
user
#P#p0.MYI
4.0K
user
#P#p1.MYD
4.0K
user
#P#p1.MYI
4.0K
user
#P#p2.MYD
4.0K
user
#P#p2.MYI
4.0K
user
#P#p3.MYD
4.0K
user
#P#p3.MYI
4.0K
user
#P#p4.MYD
4.0K
user
#P#p4.MYI
12K
user
.
frm
4.0K
user
.
par
//取出数据
mysql
&
gt
;
select
count
(
id
)
as
count
from
user
;
+
--
--
--
-
+
|
count
|
+
--
--
--
-
+
|
25
|
+
--
--
--
-
+
1
row
in
set
(
0.00
sec
)
//删除第四个分区
mysql
&
gt
;
alter
table
user
drop
partition
p4
;
Query
OK
,
0
rows
affected
(
0.11
sec
)
Records
:
0
Duplicates
:
0
Warnings
:
0
/**存放在分区里面的数据丢失了,第四个分区里面有14条数据,剩下的3个分区
只有11条数据,但是统计出来的文件大小都是4.0K,从这儿我们可以看出分区的
最小区块是4K
*/
mysql
&
gt
;
select
count
(
id
)
as
count
from
user
;
+
--
--
--
-
+
|
count
|
+
--
--
--
-
+
|
11
|
+
--
--
--
-
+
1
row
in
set
(
0.00
sec
)
//第四个区块已删除
[
root
@
BlackGhost
test
]
# ls |grep user |xargs du -sh
4.0K
user
#P#p0.MYD
4.0K
user
#P#p0.MYI
4.0K
user
#P#p1.MYD
4.0K
user
#P#p1.MYI
4.0K
user
#P#p2.MYD
4.0K
user
#P#p2.MYI
4.0K
user
#P#p3.MYD
4.0K
user
#P#p3.MYI
12K
user
.
frm
4.0K
user
.
par
/*可以对现有表进行分区,并且会按規则自动的将表中的数据分配相应的分区
中,这样就比较好了,可以省去很多事情,看下面的操作*/
mysql
&
gt
;
alter
table
aa
partition
by
RANGE
(
id
)
-
&
gt
;
(
PARTITION
p1
VALUES
less
than
(
1
)
,
-
&
gt
;
PARTITION
p2
VALUES
less
than
(
5
)
,
-
&
gt
;
PARTITION
p3
VALUES
less
than
MAXVALUE
)
;
Query
OK
,
15
rows
affected
(
0.21
sec
)
//对15数据进行分区
Records
:
15
Duplicates
:
0
Warnings
:
0
//总共有15条
mysql
&
gt
;
select
count
(
*
)
from
aa
;
+
--
--
--
--
--
+
|
count
(
*
)
|
+
--
--
--
--
--
+
|
15
|
+
--
--
--
--
--
+
1
row
in
set
(
0.00
sec
)
//删除一个分区
mysql
&
gt
;
alter
table
aa
drop
partition
p2
;
Query
OK
,
0
rows
affected
(
0.30
sec
)
Records
:
0
Duplicates
:
0
Warnings
:
0
//只有11条了,说明对现有的表分区成功了
mysql
&
gt
;
select
count
(
*
)
from
aa
;
+
--
--
--
--
--
+
|
count
(
*
)
|
+
--
--
--
--
--
+
|
11
|
+
--
--
--
--
--
+
1
row
in
set
(
0.00
sec
)
|
3,list分区
LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值,而RANGE分 区是从属于一个连续区间值的集合。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
//这种方式失败
mysql
&
gt
;
CREATE
TABLE
IF
NOT
EXISTS
`
list_part
`
(
-
&
gt
;
`
id
`
int
(
11
)
NOT
NULL
AUTO_INCREMENT
COMMENT
'用户ID'
,
-
&
gt
;
`
province_id
`
int
(
2
)
NOT
NULL
DEFAULT
0
COMMENT
'省'
,
-
&
gt
;
`
name
`
varchar
(
50
)
NOT
NULL
DEFAULT
''
COMMENT
'名称'
,
-
&
gt
;
`
sex
`
int
(
1
)
NOT
NULL
DEFAULT
'0'
COMMENT
'0为男,1为女'
,
-
&
gt
;
PRIMARY
KEY
(
`
id
`
)
-
&
gt
;
)
ENGINE
=
MyISAM
DEFAULT
CHARSET
=
utf8
AUTO_INCREMENT
=
1
-
&
gt
;
PARTITION
BY
LIST
(
province_id
)
(
-
&
gt
;
PARTITION
p0
VALUES
IN
(
1
,
2
,
3
,
4
,
5
,
6
,
7
,
8
)
,
-
&
gt
;
PARTITION
p1
VALUES
IN
(
9
,
10
,
11
,
12
,
16
,
21
)
,
-
&
gt
;
PARTITION
p2
VALUES
IN
(
13
,
14
,
15
,
19
)
,
-
&
gt
;
PARTITION
p3
VALUES
IN
(
17
,
18
,
20
,
22
,
23
,
24
)
-
&
gt
;
)
;
ERROR
1503
(
HY000
)
:
A
PRIMARY
KEY
must
include
all
columns
in
the
table
's partitioning function
//这种方式成功
mysql> CREATE TABLE IF NOT EXISTS `list_part` (
-> `id` int(11) NOT NULL COMMENT '用户
ID
',
-> `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省
',
-> `name` varchar(50) NOT NULL DEFAULT '
' COMMENT '名称
',
-> `sex` int(1) NOT NULL DEFAULT '
0
' COMMENT '
0为男,
1为女'
-
&
gt
;
)
ENGINE
=
MyISAM
DEFAULT
CHARSET
=
utf8
-
&
gt
;
PARTITION
BY
LIST
(
province_id
)
(
-
&
gt
;
PARTITION
p0
VALUES
IN
(
1
,
2
,
3
,
4
,
5
,
6
,
7
,
8
)
,
-
&
gt
;
PARTITION
p1
VALUES
IN
(
9
,
10
,
11
,
12
,
16
,
21
)
,
-
&
gt
;
PARTITION
p2
VALUES
IN
(
13
,
14
,
15
,
19
)
,
-
&
gt
;
PARTITION
p3
VALUES
IN
(
17
,
18
,
20
,
22
,
23
,
24
)
-
&
gt
;
)
;
Query
OK
,
0
rows
affected
(
0.33
sec
)
|
上面的这个创建list分区时,如果有主銉的话,分区时主键必须在其中,不然就会报错。如果我不用主键,分区就创建成功了,一般情况下,一个张表肯定会有一个主键,这算是一个分区的局限性吧。
如果对数据进行测试,请参考range分区的测试来操作
4,hash分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以 及指定被分区的表将要被分割成的分区数量。
1
2
3
4
5
6
7
8
9
|
mysql
&
gt
;
CREATE
TABLE
IF
NOT
EXISTS
`
hash_part
`
(
-
&
gt
;
`
id
`
int
(
11
)
NOT
NULL
AUTO_INCREMENT
COMMENT
'评论ID'
,
-
&
gt
;
`
comment
`
varchar
(
1000
)
NOT
NULL
DEFAULT
''
COMMENT
'评论'
,
-
&
gt
;
`
ip
`
varchar
(
25
)
NOT
NULL
DEFAULT
''
COMMENT
'来源IP'
,
-
&
gt
;
PRIMARY
KEY
(
`
id
`
)
-
&
gt
;
)
ENGINE
=
MyISAM
DEFAULT
CHARSET
=
utf8
AUTO_INCREMENT
=
1
-
&
gt
;
PARTITION
BY
HASH
(
id
)
-
&
gt
;
PARTITIONS
3
;
Query
OK
,
0
rows
affected
(
0.06
sec
)
|
测试请参考range分区的操作
5,key分区
按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用 户定义的表达式,而KEY分区的 哈希函数是由MySQL 服务器提供。
1
2
3
4
5
6
7
8
9
|
mysql
&
gt
;
CREATE
TABLE
IF
NOT
EXISTS
`
key_part
`
(
-
&
gt
;
`
news_id
`
int
(
11
)
NOT
NULL
COMMENT
'新闻ID'
,
-
&
gt
;
`
content
`
varchar
(
1000
)
NOT
NULL
DEFAULT
''
COMMENT
'新闻内容'
,
-
&
gt
;
`
u_id
`
varchar
(
25
)
NOT
NULL
DEFAULT
''
COMMENT
'来源IP'
,
-
&
gt
;
`
create_time
`
DATE
NOT
NULL
DEFAULT
'0000-00-00 00:00:00'
COMMENT
'时间'
-
&
gt
;
)
ENGINE
=
MyISAM
DEFAULT
CHARSET
=
utf8
-
&
gt
;
PARTITION
BY
LINEAR
HASH
(
YEAR
(
create_time
)
)
-
&
gt
;
PARTITIONS
3
;
Query
OK
,
0
rows
affected
(
0.07
sec
)
|
测试请参考range分区的操作
6,子分区
子分区是分区表中每个分区的再次分割,子分区既可以使用HASH希分区,也可以使用KEY分区。这 也被称为复合分区(composite partitioning)。
1,如果一个分区中创建了子分区,其他分区也要有子分区
2,如果创建了了分区,每个分区中的子分区数必有相同
3,同一分区内的子分区,名字不相同,不同分区内的子分区名子可以相同(5.1.50不适用)
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql
&
gt
;
CREATE
TABLE
IF
NOT
EXISTS
`
sub_part
`
(
-
&
gt
;
`
news_id
`
int
(
11
)
NOT
NULL
COMMENT
'新闻ID'
,
-
&
gt
;
`
content
`
varchar
(
1000
)
NOT
NULL
DEFAULT
''
COMMENT
'新闻内容'
,
-
&
gt
;
`
u_id
`
int
(
11
)
NOT
NULL
DEFAULT
0s
COMMENT
'来源IP'
,
-
&
gt
;
`
create_time
`
DATE
NOT
NULL
DEFAULT
'0000-00-00 00:00:00'
COMMENT
'时间'
-
&
gt
;
)
ENGINE
=
MyISAM
DEFAULT
CHARSET
=
utf8
-
&
gt
;
PARTITION
BY
RANGE
(
YEAR
(
create_time
)
)
-
&
gt
;
SUBPARTITION
BY
HASH
(
TO_DAYS
(
create_time
)
)
(
-
&
gt
;
PARTITION
p0
VALUES
LESS
THAN
(
1990
)
(
SUBPARTITION
s0
,
SUBPARTITION
s1
,
SUBPARTITION
s2
)
,
-
&
gt
;
PARTITION
p1
VALUES
LESS
THAN
(
2000
)
(
SUBPARTITION
s3
,
SUBPARTITION
s4
,
SUBPARTITION
good
)
,
-
&
gt
;
PARTITION
p2
VALUES
LESS
THAN
MAXVALUE
(
SUBPARTITION
tank0
,
SUBPARTITION
tank1
,
SUBPARTITION
tank3
)
-
&
gt
;
)
;
Query
OK
,
0
rows
affected
(
0.07
sec
)
|
官方网站说不同分区内的子分区可以有相同的名字,但是mysql5.1.50却不行会提示以下错误
ERROR 1517 (HY000): Duplicate partition name s1
三,分区管理
1,删除分区
1
|
mysql
&
gt
;
alter
table
user
drop
partition
p4
;
|
2,新增分区
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
//range添加新分区
mysql
&
gt
;
alter
table
user
add
partition
(
partition
p4
values
less
than
MAXVALUE
)
;
Query
OK
,
0
rows
affected
(
0.06
sec
)
Records
:
0
Duplicates
:
0
Warnings
:
0
//list添加新分区
mysql
&
gt
;
alter
table
list_part
add
partition
(
partition
p4
values
in
(
25
,
26
,
28
)
)
;
Query
OK
,
0
rows
affected
(
0.01
sec
)
Records
:
0
Duplicates
:
0
Warnings
:
0
//hash重新分区
mysql
&
gt
;
alter
table
hash_part
add
partition
partitions
4
;
Query
OK
,
0
rows
affected
(
0.12
sec
)
Records
:
0
Duplicates
:
0
Warnings
:
0
//key重新分区
mysql
&
gt
;
alter
table
key_part
add
partition
partitions
4
;
Query
OK
,
1
row
affected
(
0.06
sec
)
//有数据也会被重新分配
Records
:
1
Duplicates
:
0
Warnings
:
0
//子分区添加新分区,虽然我没有指定子分区,但是系统会给子分区命名的
mysql
&
gt
;
alter
table
sub1_part
add
partition
(
partition
p3
values
less
than
MAXVALUE
)
;
Query
OK
,
0
rows
affected
(
0.02
sec
)
Records
:
0
Duplicates
:
0
Warnings
:
0
mysql
&
gt
;
show
create
table
sub1_part
\
G
;
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
1.
row *
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
Table
:
sub1_part
Create
Table
:
CREATE
TABLE
`
sub1_part
`
(
`
news_id
`
int
(
11
)
NOT
NULL
COMMENT
'新闻ID'
,
`
content
`
varchar
(
1000
)
NOT
NULL
DEFAULT
''
COMMENT
'新闻内容'
,
`
u_id
`
varchar
(
25
)
NOT
NULL
DEFAULT
''
COMMENT
'来源IP'
,
`
create_time
`
date
NOT
NULL
DEFAULT
'0000-00-00'
COMMENT
'时间'
)
ENGINE
=
MyISAM
DEFAULT
CHARSET
=
utf8
!
50100
PARTITION
BY
RANGE
(
YEAR
(
create_time
)
)
SUBPARTITION
BY
HASH
(
TO_DAYS
(
create_time
)
)
(
PARTITION
p0
VALUES
LESS
THAN
(
1990
)
(
SUBPARTITION
s0
ENGINE
=
InnoDB
,
SUBPARTITION
s1
ENGINE
=
InnoDB
,
SUBPARTITION
s2
ENGINE
=
InnoDB
)
,
PARTITION
p1
VALUES
LESS
THAN
(
2000
)
(
SUBPARTITION
s3
ENGINE
=
InnoDB
,
SUBPARTITION
s4
ENGINE
=
InnoDB
,
SUBPARTITION
good
ENGINE
=
InnoDB
)
,
PARTITION
p2
VALUES
LESS
THAN
(
3000
)
(
SUBPARTITION
tank0
ENGINE
=
InnoDB
,
SUBPARTITION
tank1
ENGINE
=
InnoDB
,
SUBPARTITION
tank3
ENGINE
=
InnoDB
)
,
PARTITION
p3
VALUES
LESS
THAN
MAXVALUE
(
SUBPARTITION
p3sp0
ENGINE
=
InnoDB
,
//子分区的名子是自动生成的
SUBPARTITION
p3sp1
ENGINE
=
InnoDB
,
SUBPARTITION
p3sp2
ENGINE
=
InnoDB
)
)
1
row
in
set
(
0.00
sec
)
|
3,重新分区
1
2
3
4
5
6
7
8
9
10
11
12
13
|
//range重新分区
mysql
&
gt
;
ALTER
TABLE
user
REORGANIZE
PARTITION
p0
,
p1
,
p2
,
p3
,
p4
INTO
(
PARTITION
p0
VALUES
LESS
THAN
MAXVALUE
)
;
Query
OK
,
11
rows
affected
(
0.08
sec
)
Records
:
11
Duplicates
:
0
Warnings
:
0
//list重新分区
mysql
&
gt
;
ALTER
TABLE
list_part
REORGANIZE
PARTITION
p0
,
p1
,
p2
,
p3
,
p4
INTO
(
PARTITION
p0
VALUES
in
(
1
,
2
,
3
,
4
,
5
)
)
;
Query
OK
,
0
rows
affected
(
0.28
sec
)
Records
:
0
Duplicates
:
0
Warnings
:
0
//hash和key分区不能用REORGANIZE,官方网站说的很清楚
mysql
&
gt
;
ALTER
TABLE
key_part
REORGANIZE
PARTITION
COALESCE
PARTITION
9
;
ERROR
1064
(
42000
)
:
You
have
an
error
in
your
SQL
syntax
;
check
the
manual
that
corresponds
to
your
MySQL
server
version
for
the
right
syntax
to
use
near
'PARTITION 9'
at
line
1
|
四,分区优点
1,分区可以分在多个磁盘,存储更大一点
2,根据查找条件,也就是where后面的条件,查找只查找相应的分区不用全部查找了
3,进行大数据搜索时可以进行并行处理。
4,跨多个磁盘来分散数据查询,来获得更大的查询吞吐量