MYSQL 分区自动管理_mysql 自动分区

一、 MYSQL的分区表简介

1. 什么是表分区？

是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

例如：
CREATE TABLE `rangeyear_t1_kafka_consume_log` (
`id` int(11) NOT NULL COMMENT 'id',
`create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '消息接收时间',
PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(create_time))
(PARTITION p2012 VALUES LESS THAN (2012) ENGINE = InnoDB,
PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB);

cnsz22pl0015:cmdpsit > ll|grep rangeyear_t1_kafka_consume_log
-rw-rw---- 1 mysql mysql   8889 Jun 18 13:55 rangeyear_t1_kafka_consume_log.frm
-rw-rw---- 1 mysql mysql    128 Jun 18 13:55 rangeyear_t1_kafka_consume_log.par
-rw-rw---- 1 mysql mysql  98304 Jun 18 11:27 rangeyear_t1_kafka_consume_log#P#p2012.ibd
-rw-rw---- 1 mysql mysql  98304 Jun 18 11:27 rangeyear_t1_kafka_consume_log#P#p2013.ibd
-rw-rw---- 1 mysql mysql  98304 Jun 18 11:27 rangeyear_t1_kafka_consume_log#P#p2014.ibd

2. 表分区有什么好处？

 1）分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。 
 2）和单个磁盘或者文件系统相比，可以存储更多数据 
 3） 优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果,类似oracle库中的分区修剪技术。
 4）分区表更容易维护。例如：想批量删除大量数据可以truncate或者drop整个分区。
 5）可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问。

例如：
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT ,
email VARCHAR(30) NOT NULL DEFAULT 
)
PARTITION BY RANGE (uid) (
PARTITION p0 VALUES LESS THAN (3000000)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',

PARTITION p2 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);

3. 分区表的限制因素

 1）一个表最多只能有1024个分区 
 2）MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。MYSQL5.5之后支持整数、日期时间、字符串。 
 3）如果分区表有唯一索引（主键），所有分区表达式中的列必须包含在唯一索引（主键）中
 4）分区表中无法使用外键约束 
 5）MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区；即只有局部索引，没有全局索引。

4. 分区表的分类

1）range分区, 按照数据的区间范围分区（连续递增）
partition by range(exp)( //exp可以为列名或者表达式，比如to_date(created_date)
partition p0 values less than(num)
)
# range范围覆盖问题：当插入的记录中对应的分区键的值不在分区定义的范围中的时候，插入失败
#分区键的值如果是NULL，将被作为一个最小值来处理
2）LIST分区：按照List中的值分区（枚举）
partition by list(exp)( //exp为列名或者表达式
partition p0 values in (3,5)  //值为3和5的在p0分区
)
#不适合分区经常变动的需求
3）HASH分区 ：主要用来分散热点读，使用取模算法
partition by hash(store_id) partitions 4;
#数据尽可能的均匀分布。 缺点：不适合分区经常变动的需求
#mysql提供了线性hash分区，与普通hash分区相比起对分区变更处理更迅速，但数据分布不大均衡
partition by LINER hash(store_id) partitions 4;
4）Key分区,类似Hash分区，Hash分区允许使用用户自定义的表达式，但Key分区不允许使用用户自定义的表达式。Hash仅支持整数分区，而Key分区支持除了Blob和text的其他类型的列作为分区键。
partition by key(exp) partitions 4;//exp是零个或多个字段名的列表
5） Composite（复合模式）
6） Columns分区,MySQL5.5中引入的分区类型，解决了5.5版本之前range分区和list分区只支持整数分区的问题。 Columns分区可以细分为 range columns分区和 list columns分区，他们都支持整数，日期时间，字符串三大数据类型。（不支持text和blob类型作为分区键） columns分区还支持多列分区。
PARTITION BY RANGE  COLUMNS(create_time)
(PARTITION p2015 VALUES LESS THAN ('2015-01-01') ENGINE = InnoDB,
 PARTITION p20180211 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) 

二、 MYSQL分区管理procedure的架构

1. 分区管理的架构逻辑

根据对总部所有mysql库中的分区表的汇总和分析，主要的分区类型为range、range columns、hash；其中range分区占到62%，range columns分区占到23%；而这两种分区类型中99%的分区键是按照时间字段作为分区的依据。所以针对SF分区表的使用情况，编写了一套统一的分区管理工具。
由上一节分区表的基础知识介绍可得知range和range columns的区别，首先，两种分区的关键字不同；其次range分区的依据可以是一个表达式range(exp)，并且该表达式的值必须是一个整数，而range columns的分区依据不支持表达式，但可以是多个列RANGE  COLUMNS(create_time)，其中列的类型也不限于整数，可支持时间，字符串。
根据以上信息，mysql分区管理工具的架构逻辑如下图所示
 
上图中红色小字标识得为存储过程的名字，首先，整个管理工具是由proc_main_partition发起调用，其次，proc_main_partition会读取配置表main_partition_table_config中的表名、策略等信息，最后，根据配置信息调用指定的子存储过程执行添加和删除分区的操作。

2. 各存储过程的功能介绍

 proc_main_partition_config
 #该存储过程的执行权限会授权给用户，供用户把业务表加入的分区管理策略当中，实现自动维护
 proc_main_partition
 #该procedure会根据配置表中的信息调用下面一系列的存储过程
 proc_main_partition_range_nofunc
 #如为range类型的分区，且其分区键为递增的数字时，该procedure完成其增删分区的具体行为
 #如：PARTITION BY RANGE (store_id)
 proc_main_partition_range_day
 #如为range类型的分区，且其分区策略为按天分区，或者按周分区，该procedure完成其增删分区的具体行为
 proc_main_partition_range_period
 #对于有些数据需要按照上旬、中旬、下旬的策略进行分区，比如6月，6.1-6.11之前为一个分区，6.11-6.21为一个分区，，6.21-7.01为一个分区
 #如为range类型的分区，且其分区策略为按上旬、中旬、下旬分区，该procedure完成其增删分区的具体行为
 proc_main_partition_range_month
 #如为range类型的分区，且其分区策略为按月分区，该procedure完成其增删分区的具体行为
 proc_main_partition_range_year
 #如为range类型的分区，且其分区策略为按年分区，该procedure完成其增删分区的具体行为
 proc_main_part_rangecolumn_day
 #如为range columns类型的分区，且其分区策略为按天分区，或者按周分区，该procedure完成其增删分区的具体行为
 proc_main_part_rangecolumn_period
 #对于有些数据需要按照上旬、中旬、下旬的策略进行分区，比如6月，6.1-6.11之前为一个分区，6.11-6.21为一个分区，，6.21-7.01为一个分区
 #如为range columns类型的分区，且其分区策略为按上旬、中旬、下旬分区，该procedure完成其增删分区的具体行为
 proc_main_part_range