MYSQL 表分区

mysql 分区表

以下实测在5.6.34-log版本上，采用range分区，分区列类型为datetime

1：如果表上有主键或唯一索引列，range方式分区必须加入

2：受mysql分区策略限制，分区的值必须是整数(int)，分区算法里可以用函数，但函数支持范围为返回数值类型的

3：分区的名字不能是纯数字，亲测开头为字母后边跟上数字的可以，如果分区p9 value(9)已经创建，则不能创建range为8的分区，也就是说分区值是严格递增的

4：查看分区表上查询数据时，查看执行计划使用explain PARTITIONS ....

5：增加分区语句ALTER TABLE test ADD PARTITION  (PARTITION p719528 VALUES LESS THAN (719529))

6：删除分区ALTER TABLE test DROP PARTITION p736739，会同时删除磁盘上的文件

7：创建表

CREATE TABLE `test` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`val` VARCHAR(50) DEFAULT NULL,

`create_time` DATETIME NOT NULL,

PRIMARY KEY (`id`,`create_time`),

KEY `create_time` (`create_time`)

) ENGINE=INNODB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

PARTITION BY RANGE ( TO_DAYS(create_time))

(PARTITION p0 VALUES LESS THAN (0) ENGINE = INNODB,

PARTITION p1 VALUES LESS THAN (1) ENGINE = INNODB,

PARTITION p719528 VALUES LESS THAN (719528) ENGINE = INNODB,

PARTITION p736732 VALUES LESS THAN (736732) ENGINE = INNODB,

PARTITION p736733 VALUES LESS THAN (736733) ENGINE = INNODB)

如果插入数据时，分区列值为NULL，则会插入到p0分区

8：查看表分区情况 SELECT t.partition_name,t.`PARTITION_DESCRIPTION` FROM information_schema.`PARTITIONS` t WHERE table_schema='test' ORDER BY t.`PARTITION_DESCRIPTION`

9：动态创建分区，每天增加一个分区，要用tigger调度

CREATE PROCEDURE `newPartitionJob`(parValue INT)

BEGIN

DECLARE partName VARCHAR(50);

DECLARE newPartValue INT;

IF parValue >=0 THEN

SET @newPartValue=parValue;

SET partName=CONCAT('p',parValue);

ELSE

/*基于最大的分区值 +1*/

SET @newPartValue=(SELECT t.`PARTITION_DESCRIPTION`+1 FROM information_schema.`PARTITIONS` t WHERE table_schema='test' ORDER BY t.`PARTITION_DESCRIPTION` DESC LIMIT 1);

SELECT @newPartValue;

/*分区名称*/

SET partName=CONCAT('p',@newPartValue);

SELECT partName;

END IF;

/*动态SQL*/

SET @sql = CONCAT('ALTER TABLE test ADD PARTITION  (PARTITION ',partName,' VALUES LESS THAN (',@newPartValue,'))');

SELECT @sql;

/*执行SQL*/

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END