Mysql数据库，分区类型简析。
【1】分区类型
Range分区：按范围分区。对列值连续性的行，按范围区间进行分区存储；比如：id小于10存储在一个区；id大于10小于20存储在另外一个区；
List分区：与range分区类似，不过它存放的是一个离散值的集合。
Hash分区：对用户定义的表达式所返回的值来进行分区。可以写partitions (分区数目)，或直接使用分区语句，比如partition p0 values in…..。
Key分区：与hash分区类似，只不过分区支持一列或多列，并且MySQL服务器自身提供hash函数。
子分区：在一级分区的基础上，再进行分区后才存储。
【2】Range分区
Range分区创建表SQL语句：
-- ----------------------------BEGIN RANGE--------------------- -- 按Range范围分区 -- [1]删除旧表 DROP TABLE `t_partition_by_range`; -- [2]创建新表 CREATE TABLE `t_partition_by_range` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `sName` VARCHAR(10) NOT NULL, `sAge` INT(2) UNSIGNED ZEROFILL NOT NULL, `sAddr` VARCHAR(20) DEFAULT NULL, `sGrade` INT(2) DEFAULT NULL, `sStuId` INT(8) DEFAULT NULL, `sSex` INT(1) UNSIGNED DEFAULT NULL ) ENGINE = INNODB PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN(5), PARTITION p1 VALUES LESS THAN(10), PARTITION p2 VALUES LESS THAN(15) ); -- [3]添加源数据 INSERT INTO t_partition_by_range (sName, sAge, sAddr, sGrade, sStuId, sSex) VALUES (‘wangchao‘, 8, ‘heyang‘, 1, 1801111, 0), (‘suntao‘, 9, ‘weinan‘, 3, 1803110, 1), (‘liuyan‘, 16, ‘hancheng‘, 2, 20190211, 0), (‘xuhui‘, 22, ‘hancheng‘, 4, 201904107, 1), (‘wangqi‘, 18, ‘xian‘, 10, 201910104, 1), (‘baihua‘, 16, ‘nanjing‘, 8, 201908105, 1), (‘xiaoping‘, 15, ‘shenzhen‘, 6, 20190603, 1); -- [4]查询分区信息 SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_ORDINAL_POSITION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ‘t_partition_by_range‘; -- ----------------------------END RANGE---------------------
分区信息结果：
总结：
【3】List分区
List分区创建表SQL语句：
-- ----------------------------BEGIN LIST--------------------- -- 按List范围分区 -- [1]删除旧表 DROP TABLE `t_partition_by_list`; -- [2]创建新表 CREATE TABLE `t_partition_by_list` ( `id` INT AUTO_INCREMENT , `sName` VARCHAR(10) NOT NULL, `sAge` INT(2) UNSIGNED ZEROFILL NOT NULL, `sAddr` VARCHAR(20) DEFAULT NULL, `sGrade` INT(2) NOT NULL, `sStuId` INT(8) DEFAULT NULL, `sSex` INT(1) UNSIGNED DEFAULT NULL, PRIMARY KEY (`id`, `sGrade`) ) ENGINE = INNODB PARTITION BY LIST(sGrade) ( PARTITION p0 VALUES IN(1, 3), PARTITION p1 VALUES IN(2, 4, 6), PARTITION p3 VALUES IN(10) ); -- [3]添加源数据 INSERT INTO t_partition_by_list (sName, sAge, sAddr, sGrade, sStuId, sSex) VALUES (‘wangchao‘, 8, ‘heyang‘, 1, 1801111, 0), (‘suntao‘, 9, ‘weinan‘, 3, 1803110, 1), (‘liuyan‘, 16, ‘hancheng‘, 2, 20190211, 0), (‘xuhui‘, 22, ‘hancheng‘, 4, 201904107, 1), (‘wangqi‘, 18, ‘xian‘, 10, 201910104, 1), (‘baihua‘, 16, ‘nanjing‘, 8, 201908105, 1), (‘xiaoping‘, 15, ‘shenzhen‘, 6, 20190603, 1); -- [4]查询分区信息 SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_ORDINAL_POSITION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ‘t_partition_by_list‘; -- ----------------------------END LIST---------------------
分区信息结果：
总结：
【4】Hash分区
Range分区创建表SQL语句：
-- ----------------------------BEGIN HASH--------------------- -- 按Hash值分区 -- [1]删除旧表 DROP TABLE `t_partition_by_hash`; -- [2]创建新表 CREATE TABLE `t_partition_by_hash` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `sName` VARCHAR(10) NOT NULL, `sAge` INT(2) UNSIGNED ZEROFILL NOT NULL, `sAddr` VARCHAR(20) DEFAULT NULL, `sGrade` INT(2) NOT NULL, `sStuId` INT(8) DEFAULT NULL, `sSex` INT(1) UNSIGNED DEFAULT NULL ) ENGINE = INNODB PARTITION BY HASH(id) PARTITIONS 4; -- [3]添加源数据 INSERT INTO t_partition_by_hash (sName, sAge, sAddr, sGrade, sStuId, sSex) VALUES (‘wangchao‘, 8, ‘heyang‘, 3, 1803111, 0), (‘suntao‘, 9, ‘weinan‘, 6, 1806110, 1), (‘liuyan‘, 16, ‘hancheng‘, 8, 20190811, 0), (‘xuhui‘, 22, ‘hancheng‘, 12, 201912107, 1), (‘wangqi‘, 18, ‘xian‘, 11, 201911104, 1), (‘baihua‘, 16, ‘nanjing‘, 10, 201910105, 1), (‘xiaoping‘, 15, ‘shenzhen‘, 9, 20190103, 1); -- [4]查询分区信息 SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_ORDINAL_POSITION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ‘t_partition_by_hash‘; -- ----------------------------END HASH---------------------
分区信息结果：
总结：
【5】Key分区
Key分区创建表SQL语句：
-- ----------------------------BEGIN KEY----------------------- -- [1]删除旧表 DROP TABLE `t_partition_by_key`; -- [2]创建新表 CREATE TABLE `t_partition_by_key` ( `id` INT AUTO_INCREMENT, `sName` VARCHAR(10) NOT NULL, `sAge` INT(2) UNSIGNED ZEROFILL NOT NULL, `sAddr` VARCHAR(20) DEFAULT NULL, `sGrade` INT(2) NOT NULL, `sStuId` INT(8) DEFAULT NULL, `sSex` INT(1) UNSIGNED DEFAULT NULL, PRIMARY KEY (`id`, `sGrade`) ) ENGINE = INNODB PARTITION BY KEY(sGrade) PARTITIONS 6; -- [3]添加源数据 INSERT INTO t_partition_by_key (sName, sAge, sAddr, sGrade, sStuId, sSex) VALUES (‘wangchao‘, 8, ‘heyang‘, 3, 1803111, 0), (‘suntao‘, 9, ‘weinan‘, 3, 1803110, 1), (‘liuyan‘, 16, ‘hancheng‘, 8, 20190811, 0), (‘xuhui‘, 22, ‘hancheng‘, 8, 201908107, 1), (‘wangqi‘, 18, ‘xian‘, 10, 201910104, 1), (‘baihua‘, 16, ‘nanjing‘, 10, 201910105, 1), (‘xiaoping‘, 15, ‘shenzhen‘, 9, 20190903, 1); -- [4]查询分区信息 SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_ORDINAL_POSITION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ‘t_partition_by_key‘; -- ----------------------------END KEY---------------------
分区信息结果：
总结：
【6】子分区
子分区创建表SQL语句：
-- ----------------------------BEGIN SUB PARTITION--------------------- -- 按SUB PARTITION分区 -- [1]删除旧表 DROP TABLE `t_partition_by_subpart`; -- [2]创建新表 CREATE TABLE `t_partition_by_subpart` ( `id` INT AUTO_INCREMENT, `sName` VARCHAR(10) NOT NULL, `sAge` INT(2) UNSIGNED ZEROFILL NOT NULL, `sAddr` VARCHAR(20) DEFAULT NULL, `sGrade` INT(2) NOT NULL, `sStuId` INT(8) DEFAULT NULL, `sSex` INT(1) UNSIGNED DEFAULT NULL, PRIMARY KEY (`id`, `sGrade`) ) ENGINE = INNODB PARTITION BY RANGE(id) SUBPARTITION BY HASH(sGrade) SUBPARTITIONS 2 ( PARTITION p0 VALUES LESS THAN(5), PARTITION p1 VALUES LESS THAN(10), PARTITION p2 VALUES LESS THAN(15) ); -- [3]添加源数据 INSERT INTO t_partition_by_subpart (sName, sAge, sAddr, sGrade, sStuId, sSex) VALUES (‘wangchao‘, 8, ‘heyang‘, 3, 1803111, 0), (‘suntao‘, 9, ‘weinan‘, 3, 1803110, 1), (‘liuyan‘, 16, ‘hancheng‘, 8, 20190811, 0), (‘xuhui‘, 22, ‘hancheng‘, 8, 201908107, 1), (‘wangqi‘, 18, ‘xian‘, 10, 201910104, 1), (‘baihua‘, 16, ‘nanjing‘, 10, 201910105, 1), (‘xiaoping‘, 15, ‘shenzhen‘, 9, 20190903, 1); -- [4]查询分区信息 SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_ORDINAL_POSITION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ‘t_partition_by_subpart‘; -- ----------------------------END SUB PARTITION---------------------
分区信息结果：
总结：
Good Good Study, Day Day Up.
顺序 选择 循环 总结
原文：https://www.cnblogs.com/Braveliu/p/11425796.html