通往架构师的进阶之路---MySql实现表分区操作-CSDN博客

本文链接：https://blog.csdn.net/a123456bj/article/details/118362314

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';