Mysql分区——通过range分区

前言

随着MySQL越来越流行，Mysql里面的保存的数据也越来越大。在日常的工作中，我们经常遇到一张表里面保存了上亿甚至过十亿的记录。这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情，由于所有的数据都一个普通的表里。所以只能是启用一个或多个带where条件的delete语句去删除（一般where条件是时间）。 这对数据库的造成了很大压力。即使我们把这些删除了，但底层的数据文件并没有变小。面对这类问题，最有效的方法就是在使用分区表。最常见的分区方法就是按照时间进行分区。 分区一个最大的优点就是可以非常高效的进行历史数据的清理。

一、分区类型

目前MySQL支持范围分区（RANGE），列表分区（LIST），哈希分区（HASH）以及KEY分区四种。这片文章我们主要介绍range分区。

二、RANGE分区

基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。本例中使用to_days函数。

1.新建表用来创建分区

代码如下（示例）：

drop table if exists `unmaintained_insurance_logs_bak`;
CREATE TABLE `unmaintained_insurance_logs_bak` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `insurance_no` varchar(63) NOT NULL COMMENT '险种号',
  `insurance_name` varchar(255) NOT NULL COMMENT '险种名称',
  `insurance_type` int(10) NOT NULL DEFAULT '0' COMMENT '险种类型 1 主险 2 附加险',
  PRIMARY KEY (`id`,`gmt_create`) USING BTREE,
  KEY `insurance_no_idx` (`insurance_no`),
  KEY `insurance_name_idx` (`insurance_no`)
) ENGINE=InnoDB AUTO_INCREMENT=1812 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

-- 根据入库时间进行RANGE分区(接在建表语句后，注意建表语句不能加分号)
PARTITION BY RANGE(TO_DAYS(gmt_create))(
		PARTITION p20191001 VALUES LESS THAN (TO_DAYS('2019-11-01 00:00:00')),

	  	PARTITION p20191101 VALUES LESS THAN (TO_DAYS('2019-12-01 00:00:00')),

   		PARTITION p20191201 VALUES LESS THAN (TO_DAYS('2020-01-01 00:00:00')),

		PARTITION p20200101 VALUES LESS THAN (TO_DAYS('2020-02-01 00:00:00')),

		PARTITION p20200201 VALUES LESS THAN (TO_DAYS('2020-03-01 00:00:00')),

		PARTITION p20200301 VALUES LESS THAN (TO_DAYS('2020-04-01 00:00:00')),

		PARTITION pMaxValue VALUES LESS THAN MAXVALUE

);

2.备份旧表

代码如下（示例）：

drop table if exists `unmaintained_insurance_logs_temp`;
ALTER TABLE unmaintained_insurance_logs RENAME TO unmaintained_insurance_logs_temp;

3.将新表名称改为旧表

代码如下（示例）：

ALTER TABLE unmaintained_insurance_logs_bak RENAME TO unmaintained_insurance_logs;

4.创建函数自动生成新的分区

delimiter $$
-- 创建函数名称
drop procedure if exists partition_unmaintained_insurance_logs;
CREATE PROCEDURE partition_unmaintained_insurance_logs()
BEGIN
-- 创建新的分区
-- 每个月创建下个月的分区
SET @cMonth = REPLACE(DATE(DATE_ADD(SYSDATE(),INTERVAL 1 MONTH)),'-','');
select concat('cMonth is ', @cMonth);
loop_label: loop
-- 查找当前除了MAXVALUE的最大分区项，并赋值给 P_Name
SELECT REPLACE
	(partition_name, 'p', '') INTO @P_Name
FROM
	INFORMATION_SCHEMA.PARTITIONS
WHERE
	table_name = 'unmaintained_insurance_logs'
	and partition_name <> 'pMaxValue'
ORDER BY
	partition_ordinal_position DESC
	LIMIT 0,1;
	-- 创建新分区名称，应该是比之前最大分区值多一月
SET @new_part= DATE(DATE_ADD(@P_Name+0, INTERVAL 1 MONTH))+0;
select concat('new_part is ', @new_part);
-- 创建新分区的条件值，每个月创建下一个月的分区
SET @order_date= DATE(DATE_ADD(@new_part+0, INTERVAL 1 MONTH))+0;
select concat('order_date is ', @order_date);
-- 如果新分区规则小于下个月的时间则循环创建分区
	if @order_date < @cMonth then
-- 删除最大分区maxValue
SELECT COUNT(*) INTO @cnt FROM INFORMATION_SCHEMA.PARTITIONS
	WHERE TABLE_SCHEMA = 'clie' and table_name='unmaintained_insurance_logs' and partition_name = 'pMaxValue';
  if @cnt > 0 then
    SET @s1=CONCAT('ALTER TABLE unmaintained_insurance_logs DROP PARTITION pMaxValue');
	  SELECT @s1;
    PREPARE stmt1 FROM @s1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
  end if;
-- 创建新的分区语句
SET @s2=CONCAT('ALTER TABLE unmaintained_insurance_logs ADD PARTITION (PARTITION p',@new_part,' VALUES LESS THAN (TO_DAYS (''',DATE(@order_date),''')))');
  SELECT @s2;
  PREPARE stmt2 FROM @s2;
  EXECUTE stmt2;
  DEALLOCATE PREPARE stmt2;
-- 创建maxValue分区用于存放大于当前时间的异常数据
SET @s3=CONCAT('ALTER TABLE unmaintained_insurance_logs ADD PARTITION (PARTITION pMaxValue VALUES LESS THAN MAXVALUE)');
	SELECT @s3;
  PREPARE stmt3 FROM @s3;
  EXECUTE stmt3;
  DEALLOCATE PREPARE stmt3;
     iterate loop_label;
  end if;
  -- 如果新分区月份大于当前的月份，停止创建分区
  if @order_date > @cMonth then
     leave loop_label;
  end if;
  end loop loop_label;
	COMMIT ;
END $$
DELIMITER ;