1.了解
分区是把一个表分成若干个部分，就是分区，分而化之，表明上还是一张表，内容存放在不同的文件了；
数据存放在不同的分区的依据就是分区键；
3.优势：存储量大 查询快 查询大 方便删除
存储量：和单个磁盘或者文件系统分区相比，可以存储更多数据;
优化查询：在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果
方便删除：对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据
查询吞吐量：跨多个磁盘来分散数据查询，以获得更大的查询吞吐量
4.准备
建表填500w数据
CREATE TABLE `tbl_user_no_part` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 修改mysql默认的结束符号，默认是分号；但是在函数和存储过程中会使用到分号导致解析不正确
delimiter $$
-- 随机生成一个指定长度的字符串
create function rand_string(n int) returns varchar(255)
begin
# 定义三个变量
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
set i = i + 1;
end while;
return return_str;
end $$
-- 创建插入的存储过程
create procedure insert_user(in start int(10), in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into tbl_user_no_part values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());
until i = max_num
end repeat;
commit;
end $$
-- 将命令结束符修改回来
delimiter ;
-- 调用存储过程，插入500万数据，需要等待一会时间，等待执行完成
call insert_user(100001,5000000);
select count(*) from tbl_user_no_part;
5.查看版本可行性
-- 查看mysql版本
select version();
-- 查看分区插件是否激活 partition active
show plugins;
对于低版本的MySQL，如果InnoDB引擎要想分区成功，需要在my.conf中设置innodb_file_per_table=1 设置成独立表空间
独立表空间：每张表都有对应的.ibd文件
innodb_file_per_table=1
6.range分区：分区键是范围 范围分配
6.1定义
-- 语法
create table <table> (
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分区字段) (
partition <分区名称> values less than (Value),
partition <分区名称> values less than (Value),
...
partition <分区名称> values less than maxvalue
);
range：表示按范围分区
分区字段：表示要按照哪个字段进行分区，可以是一个字段名，也可以是对某个字段进行
表达式运算如year(create_time)，使用range最终的值必须是数字
分区名称: 要保证不同，也可以采用 p0、p1、p2 这样的分区名称，
less than : 表示小于
Value : 表示要小于某个具体的值，如 less than (10) 那么分区字段的值小于10的
都会被分到这个分区
maxvalue: 表示一个最大的值
注意：range 对应的分区键值必须是数字值，可以使用range columns(分区字段) 对非int型做分区，
如字符串，对于日期类型的可以使用year()、to_days()、to_seconds()等函数
create table emp_date(
id int not null,
separated date not null default '9999-12-31'
)
partition by range columns(separated) (
partiontion p0 values less than ('1990-01-01'),
partiontion p0 values less than ('2001-01-01'),
partiontion p0 values less than ('2018-01-01')
);
分区可以在创建表的时候进行分区，也可以在创建表之后进行分区
alter table <table> partition by RANGE(id) (
PARTITION p0 VALUES LESS THAN (1000000),
PARTITION p1 VALUES LESS THAN (2000000),
PARTITION p2 VALUES LESS THAN (3000000),
PARTITION p3 VALUES LESS THAN (4000000),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
6.2案例
-- 创建分区表
CREATE TABLE `tbl_user_part` (
`id` int(11) NOT NULL ,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP
PRIMARY KEY (`id`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (age) (
PARTITION p0 VALUES LESS THAN (20),
PARTITION p1 VALUES LESS THAN (40),
PARTITION p2 VALUES LESS THAN (60),
PARTITION p3 VALUES LESS THAN (80),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
6.3.错误
A PRIMARY KEY must include all columns in the table’s partitioning function。
意思是说分区的字段必须是要包含在主键当中。 可以使用PRIMARY KEY (id,xxx)来将多个字段作为主键。
在做分区表时，选择分区的依据字段时要谨慎，需要仔细斟酌这个字段拿来做为分区依据是否合适，
这个字段加入到主键中做为复合主键是否适合。
6.4.对比
7.list :使用in 固定分配
-- 语法
create table <table> (
// 字段
) ENGINE=数据库引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by LIST (分区字段或者基于该字段的返回的整数值的表达式) (
partition <分区名称> values IN (Value1，Value2, Value3),
...
partition <分区名称> values IN (Value4, Value5),
);
8.columns
columns分区分为 range columns 和 list columns 两种
支持整数(tinyint到bigint,不支持decimal 和float)、日期(date、datetime)、字符串(char、varchar、binary、varbinary)三类
columns分区支持一个或者多个字段作为分区键，不支持表达式作为分区键，这点区别于range 和 list 分区。
多列排序，先根据a字段排序再根据b字段排序，根据排序结果来分区存放数据
create talbe rc3 (
a int,
b int
)
partition by range columns(a, b) (
partition p01 values less than (0, 10),
partition p02 values less than (10, 10),
partition p03 values less than (10, 20),
partition p04 values less than (10, 35),
partition p05 values less than (10, maxvalue),
partition p06 values less than (maxvalue, maxvalue),
);
insert into rc3(a, b) values(1, 10);
select (1, 10) < (10, 10) from dual;
-- 根据结果存放到p02分区上了 1小于10 10等于10 在02规定的范围内
select
partition_name,
partition_expression,
partition_description,
table_rows
from information_schema.partitions
where table_schema = schema() and table_name = 'rc3'; 9.hash：散列函数
9.1定义
分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪个分区
mysql支持两种hash分区：常规hash分区和线性hash分区
- 常规hash分区使用的是取模算法，对应一个表达式expr是可以计算出它被保存到哪个分区中，N = MOD(expr, num)
- 线性hash分区使用的是一个线性的2的幂运算法则。
对指定的字段(整型字段)进行哈希，将记录平均的分配到分区中，使得所有分区的数据比较平均。 hash分区只需要指定要分区的字段和要分成几个分区,
expr是一个字段值或者基于某列值云散返回的一个整数，
expr可以是mysql中有效的任何函数或者其它表达式，只要它们返回一个即非常熟也非随机数的整数。num 表示分区数量
-- HASH
create table <table> (
// 字段
) ENGINE=数据库引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY HASH(expr)
PARTITIONS <num>;
9.2常规hash问题：新增的话 会导致重新计算
常规hash分区方式看上去挺不错的，通过取模的方式来数据尽可能平均分布在每个分区，让每个分区管理的数据都减少，提高查询效率，可是当我们要增加分区时或者合并分区，问题就来了，假设原来是5个常规hash分区，现在需要增加一个常规分区，原来的取模算法是MOD(expr, 5), 根据余数0~4分布在5个分区中，现在新增一个分区后，取模算法变成MOD(expr, 6),根据余数0~6分区在6个分区中，原来5个分区的数据大部分都需要通过重新计算进行重新分区。
9.3 线性hash分区
常规hash分区在管理上带来了的代价太大，不适合需要灵活变动分区的需求。为了降低分区管理上的代价，mysql提供了线性hash分区，分区函数是一个线性的2的幂的运算法则。同样线性hash分区的记录被存在那个分区也是能被计算出来的。线性hash分区的优点是在分区维护(增加、删除、合并、拆分分区)时，mysql能够处理的更加迅速，缺点是：对比常规hash分区，线性hash各个分区之间数据的分布不太均衡。
-- LINEAR HASH
create table <table> (
// 字段
) ENGINE=数据库引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY LINEAR HASH(expr)
PARTITIONS <num>;
10.key
照key进行分区非常类似于按照hash进行分区 分区的字段可以是非int类型,如字符串、日期等类型
partition by key(expr) partitions num;
-- 不指定默认首选主键作为分区键，在没有主键的情况下会选择非空唯一键作为分区键
partition by key() partitions num;
-- linear key
partition by linear key(expr)
create table <table> (
// 字段
) ENGINE=数据库引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY HASH(分区字段名)
PARTITIONS <count>;11.复合分区
是分区表中对每个分区的再次分割，支持对range和list进行父分区，复合分区即可以使用hash分区也可以使用key分区进行子分区。
复合分区适用于保存非常大量的数据记录。
-- 根据年进行分区
-- 再根据天数分区
-- 3个range分区(p0,p1,p2)又被进一步分成2个子分区，实际上整个分区被分成了 3 x 2 = 6个分区
create table ts (
id int,
purchased date
)
partition by range(year(purchased))
subpartition by hash(to_days(purchased)) subpartitions 2
(
partition p0 values less than (1990),
partition p0 values less than (2000),
partition p0 values less than maxvalue
);
CREATE TABLE IF NOT EXISTS `sub_part` (
`news_id` int(11) NOT NULL COMMENT '新闻ID',
`content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',
`u_id` int(11) NOT NULL DEFAULT 0s COMMENT '来源IP',
`create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(create_time))
SUBPARTITION BY HASH(TO_DAYS(create_time))
(
PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, SUBPARTITION s1, SUBPARTITION s2),
PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s3, SUBPARTITION s4, SUBPARTITION good),
PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION tank0, SUBPARTITION tank1, SUBPARTITION tank3)
);
12.管理分区
mysql提供了添加、删除、重定义、合并、拆分分区的命令，这些操作都可以通过alter table 命令来实现
-- 删除list或者range分区(同时删除分区对应的数据)
alter table <table> drop partition <分区名称>;
-- 新增分区
-- range添加新分区
alter table <table> add partition(partition p4 values less than MAXVALUE);
-- list添加新分区
alter table <table> add partition(partition p4 values in (25,26,28));
-- hash重新分区
alter table <table> add partition partitions 4;
-- key重新分区
alter table <table> add partition partitions 4;
-- 子分区添加新分区，虽然我没有指定子分区，但是系统会给子分区命名的
alter table <table> add partition(partition p3 values less than MAXVALUE);
-- range重新分区
ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
-- list重新分区
ALTER TABLE <table> REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));
433
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
