Mysql 表分区分类

Wesley13
• 阅读 625

针对Mysql数据库，表分区类型简析。

【1】表分区类型

（1）Range分区：按范围分区。按列值的范围区间进行分区存储；比如：id小于10存储在一个分区；id大于10小于20存储在另外一个分区；

（2）List分区：按离散值集合分区。与range分区类似，不过它是按离散值进行分区。

（3）Hash分区：按hash算法结果分区。对用户定义的表达式所返回的hash值来进行分区。

可以写partitions num(分区数目)，或直接使用分区语句，比如partition p0 values in … ..

根据分区算法的不同，Hash分区分为一般（取模法）Hash分区和线性（线性Hash规则，详见下线性hash分区内容）Hash分区。

（4）Key分区：Key分区类似于Hash分区，不同点如下：

[1] Key分区允许多列，而Hash分区只允许一列。

[2] 如果在有主键或者唯一键的情况下，Key中分区列可不指定，默认为主键或者唯一键；如果没有，则必须显性指定列。

[3] Key分区对象必须为列，而不能是基于列的表达式。

[4] Key分区和Hash分区的算法不一样。PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于列的MD5值。

（5）子分区：在一级分区的基础上，再进行分区后才存储。

【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---------------------

分区存储信息结果：

Mysql 表分区分类

如上表，理论与实际结合分析：

把id < 5的4个实体（wangchao、suntao、liuyan、xuhui）存储在分区p0；

把5 < id < 10的3个实体（wangqi、baihua、xiaoping）存储在分区p1；

把10 < id < 15的实体会存储在分区p2；

【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', 1, 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---------------------

分区存储信息结果：

Mysql 表分区分类

如上表，理论与实际结合分析：

把班级sgrade归属于离散值集合为（1、3）的3个实体（wangchao、suntao、baihua）存储在分区p0;

把班级sgrade归属于离散值集合为（2、4、6）的3个实体（liuyan、xuhui、xiaoping）存储在分区p1;

把班级sgrade归属于离散值集合为（10）的1个实体（wangqi）存储在分区p2;

另外，关于list分区表，尤其注意：当分区字段的值不归属于任何一个离散值集合时，数据会插入失败！

比如：插入源数据SQL语句：

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);

插入失败，失败信息如下：

查询：INSERT INTO t_partition_by_list (sName, sAge, sAddr, sGrade, sStuId, sSex) VALUES ('wangchao', 8, 'heyang', 1, 1801111, 0), ('su...错误代码： 1526

Table has no partition for value 8

【4】Hash分区

（1）一般Hash分区

Hash分区创建表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---------------------

分区存储信息结果：

Mysql 表分区分类

如上表，理论与实际结合分析：我们知道，一般情况下（非一般情况比如线性hash），hash分区使用的哈希函数为求模数。

那我们先利用Mysql的求模数函数分析一下，执行SQL如下：

SELECT id, MOD(id, 4) AS p_index FROM t_partition_by_hash ORDER BY id;

结果如下图：

Mysql 表分区分类

现在再结合上图分区信息的结果，我们得知：

把id哈希值为0的1个实体（xuhui）存储在分区p0;

把id哈希值为1的2个实体（wangchao、wangqi）存储在分区p1;

把id哈希值为2的2个实体（suntao、baihua）存储在分区p2;

把id哈希值为3的2个实体（liuyan、xiaoping）存储在分区p3;

好的，分析至此，我们查询一下数据存储的实际情况：

SQL语句如下：

SELECT * FROM t_partition_by_hash;

执行结果如下图：

Mysql 表分区分类

如上图，查询时，数据库引擎会按照分区顺序p0~p3依次读取到各个分区的数据内容。

（2）线性Hash分区

线性Hash分区创建表SQL语句：

-- ----------------------------BEGIN LINEAR HASH---------------------
-- 按Linear Hash值分区
-- [1]删除旧表
DROP TABLE `t_partition_by_linear_hash`;
-- [2]创建新表
CREATE TABLE `t_partition_by_linear_hash`
(
  `id` INT NOT NULL,
  `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 LINEAR HASH(sGrade) PARTITIONS 6;
-- [3]添加源数据
INSERT INTO t_partition_by_linear_hash
(id, sName, sAge, sAddr, sGrade, sStuId, sSex) 
VALUES 
(1, 'wangchao', 8, 'heyang', 3, 1803111, 0),
(2, 'suntao', 9, 'weinan', 6, 1806110, 1),
(3, 'liuyan', 16, 'hancheng', 8, 20190811, 0),
(4, 'xuhui', 22, 'hancheng', 12, 201912107, 1),
(5, 'wangqi', 18, 'xian', 11, 201911104, 1),
(6, 'baihua', 16, 'nanjing', 10, 201910105, 1),
(7, '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_linear_hash';
-- ----------------------------END LINEAR HASH---------------------

分区存储信息结果：

Mysql 表分区分类

学习一下线性Hash分区的规则：

假设分区个数num = 6, 那么N表示数据最终存储的分区：

[1] 第一步，确定V值

V值计算公式：V = POWER(2, CEILING(LOG(2, num)))

LOG()是计算num以2为底的对数，CEILING()是向上取整，POWER()是取2的次方值。

假设num的值是2的倍数那么这个表达式计算出来的结果不变。

V = POWER(2, CEILING(LOG(2, 6)))

V = POWER(2, 3)

V = 8

[2] 第二步，计算N值

N值计算公式：N = values & (V - 1)

&位与运算，将两个值都转换成二进制进行求与运算。

N = values & (V - 1)

N = 3 & (8 - 1)

N = 3 & 7

N = 3

[3] 第三步（当N >= num场景）

因为分区个数为num，那么，当N >= num时，显然分区仍未知，需要再求N值：

N值计算公式：N = N & (CEIL(V / 2) - 1)

N = 6 & (CEIL(8 / 2) - 1)

N = 6 & 3

N = 2

由于2不大于或等于6，所以可以确定存储在分区p2。

依据以上规则，下面首先，我们计算一级分区存储索引号，SQL语句如下：

SELECT id,
sGrade & (POWER(2, CEILING(LOG(2, 6))) - 1) AS p_index
FROM t_partition_by_linear_hash ORDER BY id;

结果如下图：

Mysql 表分区分类

很明显，id为2的实体，一级N值为6，需要进行第三步再确认，经规则示例分析，再确认结果应该为2。

好的，至此为止，我们已经分析完成，验证源数据存储实际情况，结果如下图：

Mysql 表分区分类

经验证，可知：

sGrade为8（id为3）的实体被存储在分区p0；

sGrade为9（id为7）的实体被存储在分区p1；

sGrade为6、10（id为2、6）的实体被存储在分区p2；

sGrade为3、11（id为1、5）的实体被存储在分区p3;

sGrade为12（id为4）的实体被存储在分区p4;

综上所述，实际与理论分析完全一致。

【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---------------------

分区存储信息结果：

Mysql 表分区分类

总结：从分区存储信息结果可知，p2分区存储了3个实体；p3分区存储了2个实体；p5分区存储了2个实体。的确已全部存储。

鉴于目前只明确Key分区的规则是基于列的MD5值，但具体为什么7个实体分别放在了p2、p3、p5分区，细节分析未果，待再研究。

附加目前分析进展，MD5值如下图：

查询SQL语句：

SELECT id, sGrade, MD5(sGrade) FROM t_partition_by_key ORDER BY id;

结果集：

Mysql 表分区分类

【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---------------------

分区存储信息结果：

Mysql 表分区分类

如上表，理论与实际结合分析：

把id < 5的4个实体（wangchao、suntao、liuyan、xuhui）存储在分区p0；

把5 < id < 10的3个实体（wangqi、baihua、xiaoping）存储在分区p1；

把10 < id < 15的实体会存储在分区p2；

按子分区再分析：

子分区为hash分区，即以sGrade值取模值作为分区索引号，查询SQL如下：

SELECT id, sGrade, MOD(sGrade, 2) FROM t_partition_by_subpart ORDER BY id;

 分区索引号结果如下图：

Mysql 表分区分类

id为1、2、3、4的实体，按主range分区，应该归属于p0分区；再按子hash分区：

因为实体1、2（wangchao、suntao）的sGrade值均为3，取模后值为1，即存储在p0的第二个子分区；

因为实体3、4（liuyan、xuhui）的sGrade值均为8，取模后置为0，即存储在p0的第一个子分区；

id为5、6、7的实体，按主range分区，应该归属于p1分区；再按子hash分区：

因为实体5、6（wangqi、baihua）的sGrade值均为10，取模后值为0，即存储在p1的第一个子分区；

因为实体7（xiaoping）的sGrade值为9，取模后值为1，即存储在p1的第二个子分区。

好的，分析至此，我们查询一下数据存储的实际情况：

SQL语句如下：

SELECT * FROM t_partition_by_subpart;

实际情况：

Mysql 表分区分类

如上图，说明：

因为查询时数据库引擎会按照分区顺序p0~p3依次读取到各个分区（包括子分区）存储的数据内容，可见与我们的理论分析结果相符。

Good Good Study, Day Day Up.

顺序 选择 循环 总结

mysql表分区数据库分区mysql创建表partition信息存储分类数据mysqlhashmysql数据库运维开发运维开发
点赞
收藏
评论区
推荐文章
blmius blmius
•
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
3502
0
0
Jacquelyn38 Jacquelyn38
•
2年前
2020年前端实用代码段，为你的工作保驾护航
有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
1986
0
0
helloworld_34035044 helloworld_34035044
•
1年前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为
780
0
0
Wesley13 Wesley13
•
2年前
mysql之数据分区
一：概述 通过把表分成多几区间，每个区间存储符合特定表达式的数据(即在我们创建分区表时指定每个分区存储的条件例如：PARTITIONp0VALUESLESSTHAN(100)即p0区间存储小于100的数据)。二：分区类型 　　即根据每个区间存储值的表达式不同，可分为如下几个类型,一般都是对数字类型或时间类型的数据进行分区。2.1 R
640
0
0
Easter79 Easter79
•
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中，有一些需要使用全局唯一ID的场景，这种时候为了防止ID冲突可以使用36位的UUID，但是UUID有一些缺点，首先他相对比较长，另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID，并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求，最初Twitter把存储系统从MySQL迁移
870
0
0
Wesley13 Wesley13
•
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败，请联系管理员！');中国在东8区所以加8方法二：selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
921
0
0
Stella981 Stella981
•
2年前
Consistent hashing一致性算法原理
最近在整理redis分布式集群，首先就整理一下分布式算法原理。常见的分区规则有哈希分区和顺序分区两种，Redis采用的是哈希分区规则。节点取余分区使用特定的数据，如Redis的键或用户ID为key，节点数量为N，则：hash(key)%N，计算出哈希值，然后决定映射到哪个节点上，如节点数为4时，哈希值的结果可能为0、1、2，3.现假
430
0
0
Stella981 Stella981
•
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段，id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
852
0
0
Wesley13 Wesley13
•
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
1115
0
0
京东云开发者 京东云开发者
•
1年前
为什么mysql不推荐使用雪花ID作为主键
作者：毛辰飞背景在mysql中设计表的时候，mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一)，而是推荐连续自增的主键id，官方的推荐是auto_increment，那么为什么不建议采用uuid，使用uuid究
714
0
0
Wesley13
Wesley13
Lv1
最大的安心是自律温柔和爱自己。
文章
1.2w
粉丝
19
获赞
1.7k
热门文章
JavPlayer：AI破坏马赛克，大量马赛克破坏版影片流出
8个在线接收手机短信验证码的免费网络服务整理
IDM 激活破解工具
2020年最新可用的磁力搜索引擎&种子搜索网站，持续整理分享
java.net.ConnectException：Connection timed out：connect 出现连接超时的问题？