分表就是把一张表拆分成若干表,,,根据情况常见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 一一关联. 即可. 缺点.如果使用 whereusername and password时就无法使用了. 所以这种分表方式必须根据条件来判断是否可取.
分区
分区有4种方式分别是 hash key list range
hash
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
/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
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
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类型外其他类型的列作为分区键
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'
)
;
_level0.str
另外还支持子分区.
比如 range基础上再来一个hash子分区
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'
)
;
mysql5.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可以使用多个列进行分区。
扫一扫
708
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
