mysql 分区经验_MySQL 分区表注意事项，经验总结以及详细操作入门

一、创建分区表 employees

SQL 脚本如下所示。

CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT,

store_id INT

)

PARTITION BY RANGE (YEAR(separated)) (

PARTITION p0 VALUES LESS THAN (2011),

PARTITION p1 VALUES LESS THAN (2015),

PARTITION p2 VALUES LESS THAN (2020),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

分区表注意事项

MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中(否则判断主键或唯一时，需要扫描所有分区)。

分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL。

最大分区数目不能超过1024。

不支持外键。

不支持全文索引(FULL TEXT)。

分区表带来的好处在于更新、删除以及锁处理的时间会减少，但是如果查询并非针对表分区字段进行，那么查询的时间由于查询缓存失效反而会增加，这点需要取舍。

如果想修改有规则的表分区，注意只能新增，不要随意删除，这里删除表分区会造成该表分区内部数据也一起被删除掉，千万注意。另外如果设定了MAXVALUE那么是不能新增的，虽然删除MAXVALUE那条表分区后可以新增，但是依然注意删除的MAXVALUE分区是否有数据，如果有则不能随意删除，最好的办法依然是重建一张新表，表在创建时候重新制定规则后把旧表导入新表，这样能保证不会丢失数据。

将旧的表数据导入到新表(分区表)后，看到新表的数据都分布到不同的区了。

二、在分区表上创建索引

ALTER TABLE employees ADD PRIMARY KEY (id);

A PRIMARY KEY must include all columns in the table's partitioning function。要求主机必须包含分区列。

这样的话判断主键是否唯一就可以在单个分区内部完成，否则就需要跨所有的分区。

增加分区列-separated，创建主键 PRIMARY KEY。

ALTER TABLE employees ADD PRIMARY KEY (id, separated);

查看索引状态

SHOW INDEX FROM employees;

分区表尽量不要建主键，因为建主键的同时会建一个唯一性的全局索引，在drop分区表时如果不指定update global indexes则会使索引失效，导致数据无法入库。

如果非要建主键，要2种方法：

1. 应用上drop 分区表时显示指定update global indexes；

2. 将主键上的索引建成本地索引；

上述2种方法虽然可以实现，但效果都不好，因为当数据量超大时维护索引也是很大的开销。

创建非分区列的索引

CREATE INDEX idx_name ON employees(fname, lname);

三、删除、新增分区

ALTER TABLE employees DROP PARTITION p3;

删除之前创建的分区 p3

下面是 p3 分区对应的条件：

PARTITION p3 VALUES LESS THAN MAXVALUE

然后插入一条记录，超出了现有分区的条件，相当于找不到分区了。

则会出现错误提示：Table has no partition for value 2020：因为分区的范围没有包含所有可能的记录的值。

下面恢复之前的 p3 分区。

新增分区

ALTER TABLE `employees` add PARTITION(PARTITION p3 VALUES LESS THAN MAXVALUE)

完成新增分区之后，再次执行上面的INSERT 语句，然后查询该分区，发现记录已经成功插入。

再次新增分区 p4，发现有错误提示。

ALTER TABLE `employees` add PARTITION(PARTITION p4 VALUES LESS THAN (2021));

错误提示：MAXVALUE can only be used in last partition definition：MAXVALUE 只能是最后一个分区定义，无法新增分区了。只能删除MAXVALUE分区或者就只能重新分区了。

注意：删除MAXVALUE 分区需要非常谨慎，会删除该分区里面的数据记录。具体解决办法，可以参考前面的建议。

最好的办法依然是重建一张新表，表在创建时候重新制定规则后把旧表导入新表，这样能保证不会丢失数据。