分区的几种方式
1.Range:
CREATE TABLE `test_tb_gradess` (
`ID` int(10) DEFAULT NULL,
`USER_NAME` varchar(20) DEFAULT NULL,
`COURSE` varchar(20) DEFAULT NULL,
`SCORE` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE ( SCORE)
(PARTITION p0 VALUES LESS THAN (60) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (70) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (80) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (90) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
测试数据:
INSERT INTO `test_tb_gradess` VALUES (1, '张三', '数学', 34);
INSERT INTO `test_tb_gradess` VALUES (2, '张三', '语文', 58);
INSERT INTO `test_tb_gradess` VALUES (3, '张三', '英语', 58);
INSERT INTO `test_tb_gradess` VALUES (4, '李四', '数学', 45);
INSERT INTO `test_tb_gradess` VALUES (5, '李四', '语文', 87);
INSERT INTO `test_tb_gradess` VALUES (6, '李四', '英语', 45);
INSERT INTO `test_tb_gradess` VALUES (7, '王五', '数学', 76);
INSERT INTO `test_tb_gradess` VALUES (8, '王五', '语文', 34);
INSERT INTO `test_tb_gradess` VALUES (9, '王五', '英语', 89);
select * from test_tb_gradess
查询区数据
select * from test_tb_gradess partition(p0)
2.List:
CREATE TABLE `test_tb_gradess` (
`ID` int(10) DEFAULT NULL,
`USER_NAME` varchar(20) DEFAULT NULL,
`COURSE` varchar(20) DEFAULT NULL,
`SCORE` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(ID) (
PARTITION p0 VALUES IN (3,5,6,9,17),
PARTITION p1 VALUES IN (1,2,10,11,19,20),
PARTITION p2 VALUES IN (4,12,13,14,18),
PARTITION p3 VALUES IN (7,8,15,16)
);
那么类别编号超出怎么分区呢?这里不同于RANGE,LIST分区的数据必须匹配列表中的产品类别才能进行分区。
3.Hash:
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 HASH( YEAR(hired) )
PARTITIONS 4;
4.Key:
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
分区管理
新增分区
ALTER TABLE sale_data
ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));
删除分区
–当删除了一个分区,也同时删除了该分区中所有的数据。
ALTER TABLE sale_data DROP PARTITION p201010;
重建分区
RANGE 分区重建
ALTER TABLE sale_data REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
LIST 分区重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
HASH/KEY 分区重建
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
用 REORGANIZE 方式重建分区的数量变成2,在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。
分区的合并
下面的SQL,将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3
ALTER TABLE sale_data
REORGANIZE PARTITION p201001,p201002,p201003,
p201004,p201005,p201006,
p201007,p201008,p201009 INTO
(
PARTITION p2010Q1 VALUES LESS THAN (201004),
PARTITION p2010Q2 VALUES LESS THAN (201007),
PARTITION p2010Q3 VALUES LESS THAN (201010)
);
参考文献
官网-分区:https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
https://www.cnblogs.com/lijunji/p/11502992.html
https://www.2cto.com/database/201503/380348.html
https://blog.csdn.net/qq_37609701/article/details/80319898
https://blog.csdn.net/persistencegoing/article/details/94437806
(https://blog.csdn.net/fmyzc/article/details/78030262