开发笔记 -- mysql创建动态分区
问题背景描述： 数据量过大时可以按某些查询条件创建分区,如果这些条件时动态变化的,那么可以根据这些条件创建动态分区。
HASH：分区主要用来确保数据在预先确定数目的分区中平均分布，而在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中，而在HASH分区中，MySQL自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
RANGE：基于属于一个给定连续区间的列值，把多行分配给同一个分区，这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。
DROP TABLES t_vehicle_capacity_car_record;
#创建分区(根据HASH进行分区)
CREATE TABLE `t_vehicle_capacity_car_record` (
`record_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'record_id#运力记录id' ,
`province_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'province_code#省编号' ,
`city_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'city_code#市编号' ,
`district_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'district_code#区编号' ,
`capacity_car_type_id` bigint(20) NULL DEFAULT NULL COMMENT 'capacity_car_type_id#车辆类型id' ,
`capacity_car_type_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'capacity_car_type_name#车辆类型名称' ,
`capacity_car_number` int(11) NULL DEFAULT NULL COMMENT 'capacity_car_number#车辆数量' ,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`province_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`city_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`district_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`record_id`, `create_time`)
)partition by hash(Month(create_time))partitions 7;
#创建分区(根据RANGE进行分区)
CREATE TABLE `t_vehicle_capacity_car_record` (
`record_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'record_id#运力记录id' ,
`province_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'province_code#省编号' ,
`city_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'city_code#市编号' ,
`district_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'district_code#区编号' ,
`capacity_car_type_id` bigint(20) NULL DEFAULT NULL COMMENT 'capacity_car_type_id#车辆类型id' ,
`capacity_car_type_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'capacity_car_type_name#车辆类型名称' ,
`capacity_car_number` int(11) NULL DEFAULT NULL COMMENT 'capacity_car_number#车辆数量' ,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`province_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`city_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`district_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`record_id`, `create_time`)
)PARTITION BY RANGE (to_days(create_time))
(
PARTITION P20180421 VALUES LESS THAN (to_days('20180425')),
PARTITION P20180425 VALUES LESS THAN (to_days('20180426')),
PARTITION P20180426 VALUES LESS THAN (to_days('20180427')),
PARTITION P20180427 VALUES LESS THAN (to_days('20180428'))
);
#创建分区(根据RANGE进行分区，然后再将RANGE分的数据进行HASH在分成3份)
CREATE TABLE `t_vehicle_capacity_car_record` (
`record_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'record_id#运力记录id' ,
`province_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'province_code#省编号' ,
`city_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'city_code#市编号' ,
`district_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'district_code#区编号' ,
`capacity_car_type_id` bigint(20) NULL DEFAULT NULL COMMENT 'capacity_car_type_id#车辆类型id' ,
`capacity_car_type_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'capacity_car_type_name#车辆类型名称' ,
`capacity_car_number` int(11) NULL DEFAULT NULL COMMENT 'capacity_car_number#车辆数量' ,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`province_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`city_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`district_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`record_id`, `create_time`)
)PARTITION BY RANGE (to_days(create_time))
subpartition by hash(to_days(create_time))
subpartitions 3(
PARTITION P20180421 VALUES LESS THAN (to_days('20180425')),
PARTITION P20180425 VALUES LESS THAN (to_days('20180426')),
PARTITION P20180426 VALUES LESS THAN (to_days('20180427')),
PARTITION P20180427 VALUES LESS THAN (to_days('20180428'))
);
#覆盖新增分区
ALTER TABLE `t_vehicle_capacity_car_record` PARTITION BY RANGE (Month(create_time))(
PARTITION P20180429 VALUES LESS THAN (TO_DAYS('20180429')),
PARTITION P20180430 VALUES LESS THAN MAXVALUE
);
#新增分区
ALTER TABLE `t_vehicle_capacity_car_record` add PARTITION (PARTITION P20180428 VALUES LESS THAN (TO_DAYS('20180428')));
#重新定义hash分区表:
ALTER TABLE `t_vehicle_capacity_car_record` partition by hash(Month(create_time))partitions 7;
#清空分区数据
TRUNCATE t_vehicle_capacity_car_record;
#hasp分区
#HASH分区用月份做条件分成4个
ALTER TABLE `t_vehicle_capacity_car_record` PARTITION BY HASH (Month(create_time))
partitions 4;
#HASH分区用天做条件分成100个
ALTER TABLE `t_vehicle_capacity_car_record` PARTITION BY HASH (Day(create_time))
partitions 100;
#删除分区(不会删除分区数据)
alter table t_vehicle_capacity_car_record REMOVE PARTITIONING;
#删除分区(会删除分区数据)
alter table t_vehicle_capacity_car_record DROP partition P20180425;
#查询分区
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME='t_vehicle_capacity_car_record';
#创建分区时间
SELECT DATE_FORMAT(DATE_SUB(curdate(),INTERVAL 0 DAY),'%Y%m%d')
#动态分区，每天凌晨1点执行分区。
DROP PROCEDURE IF EXISTS PAR_ADD_MSG;
#动态创建分区过程
CREATE PROCEDURE PAR_ADD_MSG()
BEGIN
DECLARE pName1 VARCHAR(20);
DECLARE pName2 VARCHAR(20);
DECLARE pSQL VARCHAR(200);
SET pName1 = DATE_FORMAT(DATE_SUB(curdate(),INTERVAL 0 DAY),'%Y%m%d');
SET pName2 = DATE_FORMAT(DATE_SUB(curdate(),INTERVAL -1 DAY),'%Y%m%d');
SET @pSQL = CONCAT('ALTER TABLE `t_vehicle_capacity_car_record` ADD PARTITION (PARTITION P',pName1,' VALUES LESS THAN (',to_days(pName2),'))');
PREPARE stmt FROM @pSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
DROP EVENT IF EXISTS EVENT_PAR_ADD_MSG;
#JOB动态执行过程
#每天凌晨1点执行分区
DELIMITER ;;
CREATE EVENT EVENT_PAR_ADD_MSG
ON SCHEDULE EVERY 1 DAY STARTS date_add(date(curdate()),INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL PAR_ADD_MSG();
END
;;
DELIMITER;
SELECT * FROM t_vehicle_capacity_car_record t WHERE TO_DAYS(t.create_time) = TO_DAYS('2018-04-25');
SELECT count(1) FROM t_vehicle_capacity_car_record t;
