为毛要分表和分区,,,,
所有数据库的通病,文件越大,性能越低...那问题就来了.数据越多文件越大...无解?哎,所以说知道 为毛要分区了吧!
那分表又是毛线?
分表就是把一张表拆分成若干表,,,根据情况常见2种方式,一种是横向(水平分表),不断复制完全一样的表,一种是纵向(垂直分表) 按列拆分成若干个表.
那分区又是毛线?
说白了就是mysql帮咱们分表,储存到不同的位置(自定义)
别扯了,说重点吧!
好.
分表
水平分表
完全相同的数据结构来复制表,
比如
user1 user2 user3 都有相同的数据结构 . id, username 假定每个表只存10万条数据. 那么 10万01就会存到user2中, 17万8000 也应该在第二个表中, 公式是 该条数据 % 10万(上限) 就可以得到表了
执行查询也是先计算该数据存在哪个表中. 缺点自然能看的出来. 搜索三个表的数据时比较 麻烦.这时可以对某些字段单独建一个关联表.
垂直分表
还是以user为例 id username password 10亿条数据可能过大. 那怎么办呢.
建 user,userpass 两个表.字段分别是 id,username id,password 一一关联. 即可. 缺点.如果使用 where username and password时就无法使用了. 所以 这种分表方式必须根据条件来判断是否可取.
分区
分区有4种方式分别是 hash key list range
先说hash
[SQL] 纯文本查看 复制代码
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
drop table if exists test1;
CREATE TABLE test1 (
id INT NOT NULL primary key auto_increment, -- 自动递增
username varchar(5) not null -- 用户名
)
ENGINE=innodb
PARTITION BY HASH(id) -- 以id(必须是主键才可以)分区,共分4个区.
PARTITIONS 4 ( -- 分别是
partition p0 -- p0 数据位置和索引位置分别如下
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test',
partition p1
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test',
partition p2
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test',
partition p3
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test'
) ;
insert test1 (username) values
('test1'),-- 储存在p0分区
('test2'),-- 储存在p1分区
('test3'),-- p2
('test4'),-- p3
('test5');-- po分区
|
如果出现 Error : Got error -1 from storage engine 说明没权限
/data/wwwroot/test/test 注意这里多出一个test
total 768
drwxrwx--- 6 _mysql wheel 204 4 13 11:27 .
drwxrwxrwx 3 xxxxxxxx wheel 102 4 13 11:27 ..
-rw-rw---- 1 _mysql wheel 98304 4 13 11:27 test1#P#p0.ibd
-rw-rw---- 1 _mysql wheel 98304 4 13 11:27 test1#P#p1.ibd
-rw-rw---- 1 _mysql wheel 98304 4 13 11:27 test1#P#p2.ibd
-rw-rw---- 1 _mysql wheel 98304 4 13 11:27 test1#P#p3.ibd
如果换成myisam 则是这样
/data/wwwroot/test 注意这里是设置的test路径 里面的test内容 就是上面的内容 其它就是myisam的分区文件 内容了
test test1#P#p1.MYD test1#P#p2.MYI
test1#P#p0.MYD test1#P#p1.MYI test1#P#p3.MYD
test1#P#p0.MYI test1#P#p2.MYD test1#P#p3.MYI
接下来就是range
[SQL] 纯文本查看 复制代码
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
drop table if exists test1;
CREATE TABLE test1 (
id INT NOT NULL primary key auto_increment, -- 自动递增
username varchar(5) not null -- 用户名
)
ENGINE=innodb
PARTITION BY RANGE(id) -- 以id(必须是主键才可以)分区,共分4个区.
PARTITIONS 4 ( -- 分别是
partition p0 VALUES LESS THAN (3) -- 小于3的
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test',
partition p1 values less than (5) -- 小于5的
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test',
partition p2 values less than (10) -- 小于10的
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test' ,
partition p3 values less than MAXVALUE -- >10 其它的
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test'
) ;
insert test1 (username) values
('test1'),-- 1 储存在p0分区
('test2'),-- 2 p0
('test3'),-- 3 p1
('test4'),-- 4 p1
('test5'),-- 5 p2
('test6');-- 6 p2
|
再然后就是list了
[SQL] 纯文本查看 复制代码
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
|
drop table if exists test1;
CREATE TABLE test1 (
id INT NOT NULL primary key auto_increment, -- 自动递增
username varchar(5) not null -- 用户名
)
ENGINE=innodb
PARTITION BY list(id) -- 以id(必须是主键才可以)分区,共分4个区.
PARTITIONS 2 ( -- 分别是
partition p0 VALUES in(1,2,3,4,5) -- id mod 10
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test',
partition p1 values in(6,7,8,9,0) -- id mod 10
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test'
) ;
insert test1 (username) values
('test1'),-- 1 储存在p0分区
('test2'),-- 2 p0
('test3'),-- 3 p0
('test4'),-- 4 p0
('test5'),-- 5 p0
('test6');-- 6 p1
|
最后就是key了.
理解key跟hash差不多就可以了,只不过Hash分区允许使用用户自定义的表达式,而Key分区不允许使用用户自定义的表达式，需要使用MySQL服务器提供的HASH函数;同时Hash分区只支持整数分区，而Key分区支持使用BLOB或Text类型外其他类型的列作为分区键
[SQL] 纯文本查看 复制代码
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
drop table if exists test1;
CREATE TABLE test1 (
id INT NOT NULL primary key auto_increment, -- 自动递增
username varchar(5) not null -- 用户名
)
ENGINE=innodb
PARTITION BY key(id) -- 以key.
( -- 分别是
partition p0
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test',
partition p1
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test',
partition p2
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test' ,
partition p3
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test'
)
;
|
另外还支持子分区.
比如 range基础上再来一个hash子分区
[SQL] 纯文本查看 复制代码
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
drop table if exists test1;
CREATE TABLE test1 (
id INT NOT NULL primary key auto_increment, -- 自动递增
username varchar(5) not null -- 用户名
)
ENGINE=innodb
PARTITION BY RANGE(id) -- 以id(必须是主键才可以)分区,共分4个区.
SUBPARTITION by hash(id)
( -- 分别是
partition p0 VALUES LESS THAN (3) -- 小于3的
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test',
partition p1 values less than (5) -- 小于5的
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test',
partition p2 values less than (10) -- 小于10的
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test' ,
partition p3 values less than MAXVALUE -- >10 其它的
data directory='/data/wwwroot/test'
index directory='/data/wwwroot/test'
)
;
|
mysql 5.5后增加了个 COLUMNS 分区
mysql-5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用非整形数据进行分区。COLUMNS分区支持以下数据类型：
所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。
日期类型，如DATE和DATETIME。其余日期类型不支持。
字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。
COLUMNS可以使用多个列进行分区。
|