返回顶部

OutOfMemory.CN技术专栏-> MySQL-> mysql 根据日期字段做表分区

mysql 根据日期字段做表分区

更多

mysql 的表分区有几种类型, list, hash, range等, 今天我们介绍下日期分区, 假如与如下表:

新建mysql分区表


CREATE TABLE core(
  core_id INT NOT NULL AUTO_INCREMENT,
  stamp DATETIME,
  data VARCHAR(80),
  PRIMARY KEY(core_id,stamp)
)
PARTITION BY LIST(TO_DAYS(stamp))(
  PARTITION core_20100715 VALUES IN (TO_DAYS('2010-07-15')),
  PARTITION core_20100716 VALUES IN (TO_DAYS('2010-07-16'))
);

需要注意分区列必须包含在主键中, 否则mysql会报错。

测试分区表

我们向表中插入一条数据:


INSERT INTO core (stamp,data) VALUES ('20100715','a')

上面的sql语句应该会顺利执行, 我们插入的日期范围在分区字段指定的范围内。

那么如果我们插入一条数据日期范围不再分区字段范围内,会如何呢?


INSERT INTO core (stamp,data) VALUES ('20100717','x')

mysql会报错:

Error Code: 1526. Table has no partition for value 734335

我们可以通过mysql的系统表information_schema.PARTITIONS查看分区的信息:


SELECT *
FROM information_schema.PARTITIONS
WHERE TABLE_NAME='core';

sql执行的内容如下:

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
NULL	test	core	core_20100715	NULL	1	NULL	LIST	NULL	TO_DAYS(stamp)	NULL	734333	0	0	16384	NULL	0	4194304	NULL	NULL	NULL	NULL		default	NULL
NULL	test	core	core_20100716	NULL	2	NULL	LIST	NULL	TO_DAYS(stamp)	NULL	734334	0	0	16384	NULL	0	4194304	NULL	NULL	NULL	NULL		default	NULL

和我们的预期是一致的

可以通过下面的sql语句来添加新的分区, 如下sql给表新添一个分区


ALTER TABLE core
ADD PARTITION (PARTITION core_20100717 VALUES IN (TO_DAYS('2010-07-17')));

下面的sql语句可以删除一个分区:


ALTER TABLE core
DROP PARTITION core_20100717;

如果表创建的时候没有创建分区,我们可以通过alter table来给表添上分区设置,如下sql


ALTER TABLE core
PARTITION BY LIST(TO_DAYS(stamp))(
  PARTITION core_20100715 VALUES IN (TO_DAYS('2010-07-15')),
  PARTITION core_20100716 VALUES IN (TO_DAYS('2010-07-16'))
);

如果要给现有表删除分区,可以通过下面的sql来操作:


ALTER TABLE core
REMOVE PARTITIONING;

添加和删除分区并不会影响表中的数据, 但是添加分区时分区必须可以覆盖表中现有的数据

如果服务器上有多块磁盘,可以将不同的分区对应文件放到不同的磁盘上

如下sql语句将不同的分区放到了不同的目录上面了


ALTER TABLE core
PARTITION BY LIST(TO_DAYS(stamp))(
  PARTITION p20100715 VALUES IN (TO_DAYS('2010-07-15'))
  DATA DIRECTORY '/data/2010-07-15'
  INDEX DIRECTORY '/data/2010-07-15',
  PARTITION p20100716 VALUES IN (TO_DAYS('2010-07-16'))
  DATA DIRECTORY '/data/2010-07-16'
  INDEX DIRECTORY '/data/2010-07-16'
);

在执行上面的sql时有可能会遇到下面的错误

You may get this error: SQL Error (1210): Incorrect arguments to DATA DIRECTORY

出现这个错误可能是因为这个目录已经被其他mysql分区占用了, 分区路径不能是mysql当前的数据路径。 如果给分区指定路径,最好独立于mysql数据文件路径

mysql官方文档,分区资料很全面,可以作为参考

推荐阅读:
支持

1

反对

0

发表评论