MySQL 模式的 Hash 分区规则

在 MySQL 模式下，Hash 分区仅支持 int 类型的字段，我们可以简单地使用 mod(分区键/分区数) 的方法来计算记录所在的分区位置。

• hash 默认分区名为：p0、p1、p2、p3、......
• mod 的余数 ：0 -->p0、1 -->p1、...

使用 MySQL 租户的两张哈希分区表来验证下 hash 分区的规则。

1. 创建两张分区表。

   obclient [zhumh]> create table t1 (c1 int, c2 int) partition by hash(c1) partitions 5;
   Query OK, 0 rows affected (0.10 sec)
   

   obclient [zhumh]> create table t2 (c1 int, c2 int) partition by hash(c1 + 1) partitions 5;
   Query OK, 0 rows affected (0.05 sec)
   

   obclient [test]> select tv.table_name,p.part_id,p.part_name from oceanbase.__all_table_v2 tv,oceanbase.__all_part p where  tv.table_id=p.table_id;
   +------------+---------+-----------+
   | table_name | part_id | part_name |
   +------------+---------+-----------+
   | t1         |       0 | p0        |
   | t1         |       1 | p1        |
   | t1         |       2 | p2        |
   | t1         |       3 | p3        |
   | t1         |       4 | p4        |
   | t2         |       0 | p0        |
   | t2         |       1 | p1        |
   | t2         |       2 | p2        |
   | t2         |       3 | p3        |
   | t2         |       4 | p4        |
   +------------+---------+-----------+
   10 rows in set (0.00 sec)
   

2. 插入几行示例数据。

   insert into t1 values(1,1),(2,2),(3,3);
   insert into t2 values(1,1),(2,2),(3,3);
   

3. 验证。

   表 t1

   obclient [zhumh]> select * from t1 partition(p0);
   Empty set (0.01 sec)
   

   obclient [zhumh]> select * from t1 partition(p1);
   +------+------+
   | c1   | c2   |
   +------+------+
   |    1 |    1 |
   +------+------+
   1 row in set (0.00 sec)
   

   obclient [zhumh]> select * from t1 partition(p2);
   +------+------+
   | c1   | c2   |
   +------+------+
   |    2 |    2 |
   +------+------+
   1 row in set (0.01 sec)
   

   obclient [zhumh]> select * from t1 partition(p3);
   +------+------+
   | c1   | c2   |
   +------+------+
   |    3 |    3 |
   +------+------+
   1 row in set (0.00 sec)
   

   obclient [zhumh]> select * from t1 partition(p4);
   Empty set (0.01 sec)
   

   表 t2

   obclient [zhumh]> select * from t2 partition(p4);
   +------+------+
   | c1   | c2   |
   +------+------+
   |    3 |    3 |
   +------+------+
   1 row in set (0.00 sec)
   

   obclient [zhumh]> select * from t2 partition(p3);
   +------+------+
   | c1   | c2   |
   +------+------+
   |    2 |    2 |
   +------+------+
   1 row in set (0.01 sec)
   

   obclient [zhumh]> select * from t2 partition(p2);
   +------+------+
   | c1   | c2   |
   +------+------+
   |    1 |    1 |
   +------+------+
   1 row in set (0.01 sec)
   

   obclient [zhumh]> select * from t2 partition(p1);
   Empty set (0.00 sec)
   

   obclient [zhumh]> select * from t2 partition(p0);
   Empty set (0.00 sec)
   

   根据 t1 和 t2 的结果，可以得出。

   	c1=2	p0	p1	p2	p3
   mod(分区键/5)		0	1	2	3
   t1	c1=2 --> mod(2/5)=2			2
   t2	c1+1=3 --> mod(3/5)=3				2

4. 结论。

   mod(分区键/分区数)，余数对应于分区。

适用版本

OceanBase 数据库所有版本。