Mysql数据库分区及注意事项

Mysql数据库分区及注意事项

最近项目中用到数据库分区，在此记录下使用过程中遇到的问题及注意事项。

1、创建表

创建表时需要指定该表需要分区，使用关键字：PARTITION

CREATE TABLE `msg_log`  (
  `id` bigint(32) NOT NULL COMMENT '主键',
  `request_time` datetime(0) NOT NULL COMMENT '请求时间',
  `response_time` datetime(0) NOT NULL COMMENT '响应时间',
  `time_used` int(11) NOT NULL COMMENT '耗时(ms)',
  `create_by` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `update_by` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  PRIMARY KEY (`id`, `request_time`) USING BTREE
) PARTITION BY RANGE (TO_DAYS(request_time)) (
    PARTITION p0 VALUES LESS THAN (0)
 ) ;

此处需要注意的是：如果想要按某个字段的进行分区，那么该字段必须包含在主键里。

2、创建分区

alter table msg_log add partition (partition p20220707 values less than (TO_DAYS("20220707")+1))

将每天的记录生成对应的分区，一天一个分区。

3、删除分区

alter table msg_log drop partition p20220707

根据分区名称删除。

4、注意事项

4.1 如果当前我们创建了四个分区：p20220706,p20220707,p20220708,p20220709，对应的每天一个数据分区，由于不小心删除了其中的某个分区，例如p20220707,如果后续想重新新增该分区，此时如果执行2中的alter语句，虽然不报错，但是分区创建失败。这种情况下，需要将后面的p20220708,p20220709两个分区删除，然后再重新创建。

4.2 一般动态创建分区有以下两种方式：第一种，在数据库上创建函数，通过应用程序定时进行调用创建；第二种，在数据库上创建存储过程，通过数据库自身的执行计划调用。具体情况根据实际业务等情形进行选择。

4.3 不管是函数调用，还是数据库存储过程调用，如果开启了事务，务必防止在执行过程中，遇到错误时，做到回滚或提交，防止锁表。

5、创建分区函数

该函数，根据传入的参数创建对应的分区，形如：p20220707。参数有：时间、schema、表名称、格式。

DELIMITER $$
CREATE PROCEDURE sp_create_partition (day_value datetime, tb_schema varchar(128),tb_name varchar(128),par_name_format varchar(20))
BEGIN
  DECLARE par_name varchar(32);
  DECLARE par_value varchar(32);
  DECLARE _err int(1);
  DECLARE par_exist int(1);
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET _err = 1;
  START TRANSACTION;
    SET par_name = CONCAT('p', DATE_FORMAT(day_value, par_name_format));
    SELECT
      COUNT(1) INTO par_exist
    FROM information_schema.PARTITIONS
    WHERE TABLE_SCHEMA = tb_schema AND TABLE_NAME = tb_name AND PARTITION_NAME = par_name;
    IF (par_exist = 0) THEN
      SET par_value = DATE_FORMAT(day_value, '%Y-%m-%d');
      SET @alter_sql = CONCAT('alter table ', tb_name, ' add PARTITION (PARTITION ', par_name, ' VALUES LESS THAN (TO_DAYS("', par_value, '")+1))');
      PREPARE stmt1 FROM @alter_sql;
      EXECUTE stmt1;
    END IF;
  COMMIT;
  END
$$

以上数据库函数，通过mybatis调用方式，形如：

<insert id="addPartition" >
		CALL sp_create_partition(DATE_ADD(NOW(), INTERVAL + (#{offsetDay}) DAY), #{schemaName}, #{tableName},'%Y%m%d')
	</insert>

public void addPartition(@Param("schemaName") String schemaName, @Param("tableName") String tableName,
			@Param("offsetDay") int offsetDay);

6、删除分区函数

DELIMITER $$
CREATE PROCEDURE sp_drop_partition (day_value datetime, tb_schema varchar(128), tb_name varchar(128),par_name_format varchar(20))
BEGIN
  DECLARE str_day varchar(64);
  DECLARE _err int(1);
  DECLARE done int DEFAULT 0;
  DECLARE par_name varchar(64);
  DECLARE cur_partition_name CURSOR FOR
  SELECT
    partition_name
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA = tb_schema AND table_name = tb_name
  ORDER BY partition_ordinal_position;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET _err = 1;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  SET str_day = DATE_FORMAT(day_value, par_name_format);
  OPEN cur_partition_name;
  REPEAT
    FETCH cur_partition_name INTO par_name;
    IF (str_day > SUBSTRING(par_name, 2)) THEN
      SET @alter_sql = CONCAT('alter table ', tb_name, ' drop PARTITION ', par_name);
      PREPARE stmt1 FROM @alter_sql;
      EXECUTE stmt1;
    END IF;
  UNTIL done END REPEAT;
  CLOSE cur_partition_name;
END
$$

该函数，删除特定的分区名称，实现删除超过特定天数的记录分区。

mybatis调用方式，形如：

	<delete id="dropPartition">
		CALL sp_drop_partition(#{holdDate}, #{schemaName}, #{tableName},'%Y%m%d')
	</delete>

public void dropPartition(@Param("schemaName") String schemaName, @Param("tableName") String tableName,	@Param("holdDate") Date holdDate);

7、查询分区

select * from information_schema.partitions where table_name = 'msg_log';

查询某个表的某个分区是否存在

 select COUNT(1) t FROM information_schema.PARTITIONS   WHERE TABLE_SCHEMA = 'test_schema' AND TABLE_NAME = 'msg_log' AND PARTITION_NAME = 'p20220707';