一、创建分区表 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 分区需要非常谨慎,会删除该分区里面的数据记录。具体解决办法,可以参考前面的建议。
最好的办法依然是重建一张新表,表在创建时候重新制定规则后把旧表导入新表,这样能保证不会丢失数据。