分区的一些概念简介
通俗地讲表分区是将一大表，根据条件分割成若干个小表，对外我们看到的还是一张表,也就是逻辑上分区，删除了一个分区就等于这个分区的数据都删除了，分区需要根据什么分区就出现了分区键概念
为什么要对表进行分区
1.和单个磁盘或者文件系统分区相比，可以存储更多数据
2.优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者
多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数的查询时，
可以容易的在每个分区上并行处理，最终只3.需要汇总所有分区得到的结果
4.对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区
来快速删除数据
5.跨多个磁盘来分散数据查询，以获得更大的查询吞吐量
分区和分表区别
分区和水平分表功能类似，将一个大表的数据分割到多张小表中去，由于查询不需要全表扫描了，
只需要扫描某些分区，所以分区能提高查询速度。
水平分表需要用户预先手动显式创建出多张分表(如tbl_user0, tbl_user1, tbl_user2)，在物理
上实实在在的创建多张表，通过客户端代理(Sharding-JDBC等)或者中间件代理(Mycat等)
来实现分表逻辑。
分区是MySQL的一个插件Plugin功能，将一张大表的数据在数据库底层分成多个分区文件(如
tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd)，和水平分表不同的是分区不需
要显式的创建“分表”,数据库会自动创建分区文件的，用户看到的只是一张普通的表，其实是
对应的是多个分区，这个是对用户是屏蔽的、透明的，在使用上和使用一张表完全一样，
不需要借助任何功能来实现。分区是一种逻辑上的水平分表，在物理层面还是一张表。
分区类型
range分区：基于一个给定的连续区间范围(区间要求连续并且不能重叠)，把数据分配到不同的分区
list分区：类似于range分区，区别在于list分区是居于枚举出的值列表分区，range是基于给定
的连续区间范围分区
hash分区：基于给定的分区个数，把数据分配到不同的分区
key分区：类似于hash分区
注意：无论哪种分区，要么你分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其它字段分区。
range分区
range分区：给定一个连续区间的范围值进行分区，某个字段的值满足这个范围就会被分配到该分区。适用于字段的值是连续的区间的字段，如 日期范围, 连续的数字
-- 语法
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')
);
list分区
设置若干个固定值进行分区，如果某个字段的值在这个设置的值列表中就会被分配到该分区。适用于字段的值区分度不高的，或者值是有限的，特别是像枚举这样特点的列。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),
);
假定有20个音像店，分布在4个有经销权的地区，如下表所示：
====================
地区 商店ID 号
------------------------------------
北区 3, 5, 6, 9, 17
东区 1, 2, 10, 11, 19, 20
西区 4, 12, 13, 14, 18
中心区 7, 8, 15, 16
====================
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id)
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
)；
HASH分区
Hash分区主要用来分散热点读，确保数据在预先确定个数的分区中可能的平均分布。对一个表执行Hash分区时，mysql会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪个分区。
mysql支持两种hash分区，
常规hash分区和线性hash分区(linear hash分区)，常规hash分区使用的是取模算法，
对应一个表达式expr是可以计算出它被保存到哪个分区中，N = MOD(expr, num)
线性hash分区使用的是一个线性的2的幂运算法则。
-- HASH
create table <table> (
// 字段
) ENGINE=数据库引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY HASH(expr)
PARTITIONS <num>;
-- LINEAR HASH
create table <table> (
// 字段
) ENGINE=数据库引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY LINEAR HASH(expr)
PARTITIONS <num>;
常规hash分区在管理上带来了的代价太大，需要重新计算之前的所有分区不适合需要灵活变动分区的需求。为了降低分区管理上的代价，mysql提供了线性hash分区，分区函数是一个线性的2的幂的运算法则。同样线性hash分区的记录被存在那个分区也是能被计算出来的。线性hash分区的优点是在分区维护(增加、删除、合并、拆分分区)时，mysql能够处理的更加迅速，缺点是：对比常规hash分区，
线性hash各个分区之间数据的分布不太均衡。
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4；
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LINEAR HASH(YEAR(hired))
PARTITIONS 4；
key分区
类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
子分区
子分区(subpartition)：是分区表中对每个分区的再次分割，又被称为复合分区，mysql目前支持对range和list进行子分区，子分区即可以使用hash分区也可以使用key分区。复合分区适用于保存非常大量的数据记录。
到底还是开源软件，MySQL对复合分区的支持远远没有Oracle丰富。
在MySQL 5.6版本中，只支持RANGE和LIST的子分区，且子分区的类型只能为HASH和KEY。oracle支持的复合分区种类就很全，这里不再赘述
-- 根据年进行分区
-- 再根据天数分区
-- 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)
);
管理分区
mysql不禁止在分区键值上使用null,分区键可能是一个字段或者一个用户定义的表达式，一般情况下，mysql的分区把null值当做零值或者一个最小值进行处理。range分区中，null值会被当做最小值来处理；list分区中null值必须出现在枚举列表中，否则不被接受；hash/key分区中,null值会被当做领值来处理。
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)
/查看数据是否已经分区保存，以及分区数据分布
SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'sale_data';
总结
分区优点
1，分区可以分在多个磁盘，存储更大一点
2，根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了
3，进行大数据搜索时可以进行并行处理。
4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量
对已有数据表进行分区
可以使用ALTER TABLE来进行更改表为分区表，这个操作会创建一个分区表，然后自动进行数据copy然后删除原表。
ALTER TABLE tbl_rtdata PARTITION BY RANGE (Month(fld_date))
(
PARTITION p_Apr VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION p_May VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION p_Dec VALUES LESS THAN MAXVALUE );
但是，这种方法对于服务器的资源消耗比较大，所以下面还有另一种方法。
1.创建一张新的分区表结构
CREATE TABLE `vehicle_coord_range` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`region_code` varchar(64) DEFAULT NULL COMMENT '城市(区域)CODE',
`plate_num` varchar(64) DEFAULT NULL COMMENT '车牌号',
`lon` varchar(64) DEFAULT NULL COMMENT '经度',
`lat` varchar(64) DEFAULT NULL COMMENT '纬度',
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
`createTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
`create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
`update_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`remarks` varchar(255) DEFAULT NULL COMMENT '备注信息',
`del_flag` varchar(64) DEFAULT '0' COMMENT '逻辑删除标记（0：显示；1：隐藏）',
`direction` varchar(64) DEFAULT NULL COMMENT '行驶方向',
PRIMARY KEY (`id`,`create_date`)
) ENGINE=InnoDB AUTO_INCREMENT=172203 DEFAULT CHARSET=utf8 COMMENT='订单(行程) 线路实时坐标' partition by range (TO_DAYS(create_date))( #range分区结构
PARTITION vehicle_coord_1 VALUES LESS THAN (TO_DAYS('2017-05-22')),
PARTITION vehicle_coord_2 VALUES LESS THAN (TO_DAYS('2017-05-23'))
);
2.将初始数据导入新的表
insert into vehicle_coord_range select * from 旧表;
其中，可能会报出 table has no partition for value XXX
是因为联合主键中作为分区的 create_date 键值有小于数据的键值，分区的建区规则是 less than，需要手动添时间范围大于条件限制的分区，而且新建的分区数值不能小于原有分区。
ALTER TABLE vehicle_coord_range ADD PARTITION(
PARTITION vehicle_coord_20171122 VALUES LESS THAN (TO_DAYS('2017-11-22'))
);
通过 count() 函数查看数据导入是否完全，如果不完全，需要重新导入
3.查看分区信息
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = schema()
AND TABLE_NAME='vehicle_coord_range';
如果要删除表分区
ALTER TABLE vehicle_coord_range DROP PARTITION vehicle_coord_20171209;
4.创建存储过程
本存储过程直接创建在mavicat中
BEGIN
-- 表名赋值
set @p_name = (select concat('vehicle_coord_',date_format(date_sub(curdate(),interval - 2 day),'%Y%m%d'))from dual);
-- 表的日期限制
set @p_date = (select date_sub(curdate(),interval - 2 day)from dual);
-- 添加分区sql语句
set @p_sql = concat('ALTER TABLE vehicle_coord_range ADD PARTITION(PARTITION ',@p_name,' VALUES LESS THAN (TO_DAYS(\'',@p_date,'\')))');
-- 预处理sql，其中stmt是一个变量
PREPARE stmt FROM @p_sql;
-- 执行SQL语句
EXECUTE stmt;
-- 释放掉预处理段
DEALLOCATE PREPARE stmt; -- 释放掉预处理段
END
5.设定时间来每天定时调用创建新的分区
查看是否开启事件
show variables like 'event_scheduler';
如果没有开启事件，需要先开启事件
set global event_scheduler = on;
调用存储过程
call set_vehicle_coord_partition
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
