MySql实现表分区操作
前言
最近做的一个项目,因为数据库中轨迹表已经将近上亿条数据,而且每天都在增加,所以需要做分区操作
Demo实现(按天创建表分区)
- 创建一个带分区的表,同时添加两个分区,RANGE分区,分区表达式TO_DAYS(
c_date
),分区字段c_date
CREATE TABLE `zz_hw_hkg`.`t_user_v2` (
`c_id` int NOT NULL AUTO_INCREMENT,
`c_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`c_phone` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`c_date` date NOT NULL,
PRIMARY KEY (`c_id`, `c_date`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic PARTITION BY RANGE (TO_DAYS(`c_date`))
(PARTITION `p20210629` values less than (738336),
PARTITION `p20210630` values less than (738337)
);
- 创建事件:每秒向t_user_v2表添加一条数据
CREATE DEFINER = `root`@`localhost` EVENT `zz_hw_hkg`.`每秒向t_user_v2表添加一条数据`
ON SCHEDULE
EVERY '1' SECOND STARTS '2021-06-29 16:40:00'
DO BEGIN
insert into t_user_v2(c_name,c_phone,c_date) values ('国立','110',now());
END;
- 创建事件:每天创建表分区
CREATE DEFINER = `root`@`localhost` EVENT `zz_hw_hkg`.`每天创建表分区`
ON SCHEDULE
EVERY '1' DAY STARTS '2021-06-29 14:58:00'
DO BEGIN
call Auto_Create_Partition();
END;
- 创建存储过程:Auto_Create_Partition(),实现表分区的添加,注释已经很详细了,有问题评论或私信聊
CREATE DEFINER=`root`@`localhost` PROCEDURE `Auto_Create_Partition`()
L_END:BEGIN
# 初始化变量
DECLARE MAX_PARTITION_DESCRIPTION VARCHAR(255) DEFAULT 0;
DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT 0;
DECLARE i INT DEFAULT 1;
DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT 0;
DECLARE next_p_name varchar(255) DEFAULT 0;
# 查询表分区 最近的一个月的分区(ORDER BY partition_description DESC LIMIT 1)
SELECT PARTITION_NAME INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'zz_hw_hkg' AND TABLE_NAME = 't_user_v2' ORDER BY partition_description DESC LIMIT 1 ;
IF ISEXIST_PARTITION <=> "" THEN
SELECT "Partition table not is exist" AS "*****ERROR*****";
LEAVE L_END;
END IF;
# 根据时间设置要创建的分区的名称
SELECT concat('p',DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 DAY),'%Y%m%d')) INTO next_p_name;
# 判断要创建的分区是否存在
IF ISEXIST_PARTITION = next_p_name THEN
SELECT concat("Partition(",next_p_name,") is exist") AS "*****INFO*****";
LEAVE L_END;
END IF;
# 最近一个分区的设置的最大值(右边界)
SELECT partition_description INTO MAX_PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'zz_hw_hkg' AND TABLE_NAME = 't_user_v2' ORDER BY partition_description DESC LIMIT 1;
IF MAX_PARTITION_DESCRIPTION <=> "" THEN
SELECT "Partition table is error" AS "*****ERROR*****";
LEAVE L_END;
END IF;
# 创建新的分区
# 1.设置新的分区的右边界值 应当是要设置分区的下一天的值,这样数据永远不会超出
SET P_DESCRIPTION = TO_DAYS(DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 2 DAY),'%Y%m%d'));
# 2.拼接新增分区的SQL语句
SET @S=CONCAT('ALTER TABLE ','t_user_v2',' ADD PARTITION (PARTITION ',next_p_name,' VALUES LESS THAN (',P_DESCRIPTION,'))');
SELECT @S;
# 3.使用预处理执行SQL
# ------------------------------------------------------------------------------------------
# 语法:语法
# PREPARE statement_name FROM sql_text /*定义*/
# EXECUTE statement_name [USING variable [,variable...]] /*执行预处理语句*/
# DEALLOCATE PREPARE statement_name /*删除定义*/
# ------------------------------------------------------------------------------------------
PREPARE stmt2 FROM @S;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
END L_END
- 查询已经创建的分区信息
SELECT
table_name,
partition_name,
partition_method,
partition_expression,
partition_description,
table_rows,
data_length
FROM
information_schema.PARTITIONS
WHERE
table_schema = SCHEMA ()
AND table_name = 't_user_v2';
- 结果展示
参考文档
https://blog.csdn.net/qq_35726305/article/details/81221721
结语
世界没有两片相同的树叶,你再不堪,也是独一无二,无须自轻自贱。