目录
通用核心
- 分区字段必须是唯一索引和主键索引的一部分,原因就是,mysql中的主键、唯一限制都是通过索引实现的,唯一索引和主键索引只有一个区别那就是后者不能为null,同时,加了主键和唯一之后,该字段其实就可以省略index了,因为前两者本身就是特殊的索引,已经包含了索引的功能。
- 分区字段可以配合分区函数,只有数字类型和日期类型有可用的分区函数,字符类型等等是没有的
range分区
语法: PARTITION BY RANGE (expr) (PARTITION p0 VALUES LESS THAN (number))
核心: 整数表达式 + 连续递增区间 + switch判断逻辑
场景: 分区表达式范围变化
分区表达式结果必须为整数
DROP TABLE if EXISTS `test_range`;
CREATE TABLE `test_range` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by RANGE(`name`)(
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30),
PARTITION p3 VALUES LESS THAN MAXVALUE)
// 失败:Field 'name' is of a not allowed type for this type of partitioning
DROP TABLE if EXISTS `test_range`;
CREATE TABLE `test_range` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by RANGE(MD5(`name`))(
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30),
PARTITION p3 VALUES LESS THAN MAXVALUE)
// 失败: This partition function is not allowed
DROP TABLE if EXISTS `test_range`;
CREATE TABLE `test_range` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by RANGE(year(`time`))(
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30),
PARTITION p3 VALUES LESS THAN MAXVALUE)
// 成功
必须连续递增区间
DROP TABLE if EXISTS `test_range`;
CREATE TABLE `test_range` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by RANGE(year(`time`))(
PARTITION p0 VALUES LESS THAN (30),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (50),
PARTITION p3 VALUES LESS THAN MAXVALUE)
// 失败:VALUES LESS THAN value must be strictly increasing for each partition
插入区间必须存在
DROP TABLE if EXISTS `test_range`;
CREATE TABLE `test_range` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by RANGE(year(`time`))(
PARTITION p0 VALUES LESS THAN (30),
PARTITION p1 VALUES LESS THAN (40),
PARTITION p2 VALUES LESS THAN (50))
// 成功
无法插入分区之外的值:
list分区
语法: PARTITION BY LIST(expr) (PARTITION p0 VALUES IN (number1,number2))
核心: 整数表达式 + 不重复离散值逗号分隔
场景: 分区表达式为穷尽离散值
分区表达式结果必须为整数
DROP TABLE if EXISTS `test_list`;
CREATE TABLE `test_list` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by list(`name`)(
PARTITION p0 VALUES in ("h"),
PARTITION p1 VALUES in ("w"),
PARTITION p2 VALUES in ("m"))
// 失败:1697 - VALUES value for partition 'p0' must have type INT
DROP TABLE if EXISTS `test_list`;
CREATE TABLE `test_list` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by list(`age`)(
PARTITION p0 VALUES in (10,11),
PARTITION p1 VALUES in (20,21),
PARTITION p2 VALUES in (30,31))
// 成功
分区枚举不可以重复
DROP TABLE if EXISTS `test_list`;
CREATE TABLE `test_list` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by list(`age`)(
PARTITION p0 VALUES in (10,11),
PARTITION p1 VALUES in (20,21,11),
PARTITION p2 VALUES in (30,31))
// 失败:> 1495 - Multiple definition of same constant in list partitioning
插入区间必须存在
同 range分区
hash分区
语法: PARTITION BY HASH(expr) PARTITIONS number;
核心: 整数表达式 + 正整数分区数量
场景: 分区表达式where查询
分区表达式结果必须为整数
DROP TABLE if EXISTS `test_hash`;
CREATE TABLE `test_hash` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by HASH(`name`)PARTITIONS 4;
分区数量必须为正整数
DROP TABLE if EXISTS `test_hash`;
CREATE TABLE `test_hash` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by HASH(`age`)PARTITIONS -1;
// You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 8
DROP TABLE if EXISTS `test_hash`;
CREATE TABLE `test_hash` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by HASH(`age`)PARTITIONS 0;
// 1504 - Number of partitions = 0 is not an allowed value
余数取摸决定分区
key分区
语法: PARTITION BY KSY(expr) PARTITIONS number;
核心: 任意表达式 + 正整数分区数量 + 服务器内置算法对任意数据类型求hash + 分区表达式非整数时极易分区不均衡
场景: 分区表达式where查询
分区表达式可以是任意类型
DROP TABLE if EXISTS `test_key`;
CREATE TABLE `test_key` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by KEY(`time`)PARTITIONS 4;
// 成功
DROP TABLE if EXISTS `test_key`;
CREATE TABLE `test_key` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by KEY(`name`)PARTITIONS 4;
// 成功
DROP TABLE if EXISTS `test_key`;
CREATE TABLE `test_key` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by KEY(`age`)PARTITIONS 4;
// 成功
分区表达式的类型决定了分区算法
- 分区表达式的类型为整数:此时key分区完全等于hash分区
- 分区表达式的类型为字符串:对字符串求hash值进行hash算法
- 分区表达式的类型为日期:对日期求hash值进行hash算法
- 分区表达式的类型为其他:求hash值进行hash算法
非整数分区表达式时极易分区不均衡
-
name
VARCHAR(99) NOT NULL, 为key分区字段
-
time
datetime NOT NULL, 为key分区字段
总述
- range和list基本类似,区别就是前者必须是递增离散值,后者是不重复枚举值,相同点是一旦分区未包含插入值会插入失败,插入查询性能也类似。
- hash和key基本类似,区别就是前者只支持整数表达式,后者可以接收任意表达式,但是其算法也都是把其他的数据类型转化成整数再求hash值,相同点是任意数据都可以插入成功
- key分区比较特殊,虽然可支持范围比较大,但是非整数表达式就行分区时极易造成分区不均衡,实际操作时key分区能支持的极限就是字符串了,同时尽量避免使用key分区。