mysql-分区分库分表总结

分区的几种方式

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

https://blog.csdn.net/liangz/article/details/79352870

https://blog.csdn.net/qq_28289405/article/details/80576614