Mysql分区表踩坑

1.背景    

    最近公司看板要搞月切看板，没法只有把每个月的最后一份数据存储下来，由于看板使用的是存储引擎是mysql，所以并不想每次都推送所有月份数据到mysql，尤其是每个月数据量较大的时候，只好使用增量的方式进行数据推送。采用增量的方式一种是用delete删除最新月份数据，然后再推最新月份数据，但这样会产生大量binlog影响性能，所以考虑再三，还是使用删除分区的方式再进行新增数据的添加。

2.mysql分区表使用

    mysql的分区类型主要有四种：Range分区，List分区，Hash分区，Key分区。本次主要使用的是List分区，这里强调一下这四种分区方式对null值的处理方式的不同点。RANGE分区中，NULL值会被当作最小值来处理；LIST分区中，NULL值必须出现在枚举列表中，否则不被接受；HASH/KEY分区中，NULL值会被当作零值处理。

(1)创建mysql分区表，注意这里的分区字段只能是int类型。

create table test_mysql_list_partition(
month_id int comment '月份ID,202401',
index_name varchar(50) comment '指标名称',
index_value varchar(50) comment '指标值'
)
PARTITION BY LIST COLUMNS(month_id) (  
    PARTITION p202401 VALUES IN (202401)
);

(2)每次要对某个分区插入数据时，需要先创建分区，否则插入没有创建的分区会报错，下面的语句通过传参的形式添加分区，如果已经存在的分区再执行下面的语句也会报错。

ALTER TABLE test_mysql_list_partition  ADD PARTITION (PARTITION p${month_id} VALUES IN (${month_id}));

　

(3)删除分区可通过下面的语句进行操作，但是如果删除不存在的分区也会报错。

ALTER TABLE test_mysql_list_partition  
DROP PARTITION  p${month_id};

(4)综上第（2），（3）点为了报错数据流不报错，我们在删除分区前要先判断分区是否存在,尤其是一天数据要刷几次的时候，这种场景可以通过写存储过程来解决，每次需要刷数前执行函数DropPartitionIfExists('xxx'),然后再添加分区。

CREATE PROCEDURE DropPartitionIfExists(IN partitionValue VARCHAR(255))  
BEGIN  
  DECLARE partitionExists BOOLEAN;  
  -- 检查分区是否存在  
  SELECT COUNT(*) > 0 INTO partitionExists  
  FROM information_schema.partitions  
  WHERE table_name = 'test_mysql_list_partition'  
  AND table_schema = DATABASE()  
  AND partition_expression LIKE CONCAT('%', partitionValue, '%');  
  IF partitionExists THEN  
    -- 删除分区  
    SET @dropPartitionSQL = CONCAT('ALTER TABLE test_mysql_list_partition DROP PARTITION  ', partitionValue);  
    PREPARE stmt FROM @dropPartitionSQL;  
    EXECUTE stmt;  
    DEALLOCATE PREPARE stmt;  
  END IF;  
END ;

　　

    执行效果如下：

 

 

3.总结

    mysql分区的使用感觉坑比较多，还是慎用。