如何知道oceanbase哈希分区时，属性和partition_id的映射关系？

OceanBase
#1

【产品名称】oceanbase-ce

【产品版本】v3.1.1

【问题描述】

SELECT t.tenantid, a.tenantname, t.tablename, d.databasename, tg.tablegroupname , t.partnum , t2.partitionid, t2.ZONE, t2.svrip , round(t2.datasize/1024/1024/1024) datasize_gb

, a.primary_zone, IF(t.locality = ‘’ OR t.locality IS NULL, a.locality, t.locality) AS locality

FROM oceanbase.__all_tenant AS a

JOIN oceanbase.__all_virtual_database AS d ON ( a.tenant_id = d.tenant_id )

JOIN oceanbase.__all_virtual_table AS t ON (t.tenant_id = d.tenant_id AND t.database_id = d.database_id)

JOIN oceanbase.__all_virtual_meta_table t2 ON (t.tenant_id = t2.tenant_id AND (t.table_id=t2.table_id OR t.tablegroup_id=t2.table_id) AND t2.ROLE IN (1) )

LEFT JOIN oceanbase.__all_virtual_tablegroup AS tg ON (t.tenant_id = tg.tenant_id and t.tablegroup_id = tg.tablegroup_id)

WHERE a.tenantid IN (1001) AND t.tabletype IN (3)

AND d.databasename = ‘database’

and tablename in (‘table’)

ORDER BY t.tenantid, tg.tablegroupname, d.databasename, t.tablename, t2.partition_id;

数据库中某个表按照partition by hash(key)进行分区，通过上述语句可以查询到该表partitionid对应的内容存储在哪个物理节点，现在我想知道某个key对应的partitionid是什么（由此推断出某个key的内容存储在哪个物理节点上），我该怎么做？

#2

最简单的方式：你如果想知道某个特定的key分布的位置，可以用explain来做等值查询，会得到数据存储在的partition的name，再通过table id，partition id在__all_virtual_meta_table中查询存储的节点ip

create table t1 (c1 int, c2 varchar(1024), c3 int, primary key(c1, c2)) partition by key (c2) partitions 100;

 explain select * from t1 where c1=value1and c2=value2

| ==================================

|ID|OPERATOR |NAME|EST. ROWS|COST|

----------------------------------

|0 |TABLE GET|t1 |1    |53 |

==================================


Outputs & filters:

-------------------------------------

 0 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil),

   access([t1.c2], [t1.c1], [t1.c3]), partitions(p0)



1 个赞
#3 
MySQL [test]> create table t1 (c1 int, c2 varchar(1024), c3 int, primary key(c1, c2)) partition by key (c2) partitions 100;
MySQL [test]> select __all_part.tenant_id, part_id, part_name, part_idx from oceanbase.__all_part, oceanbase.__all_table_v2 where table_name = 't1' limit 10;
+-----------+---------+-----------+----------+
| tenant_id | part_id | part_name | part_idx |
+-----------+---------+-----------+----------+
|         1 |       0 | p0        |        0 |
|         1 |       1 | p1        |        1 |
|         1 |       2 | p2        |        2 |
|         1 |       3 | p3        |        3 |
|         1 |       4 | p4        |        4 |
|         1 |       5 | p5        |        5 |
|         1 |       6 | p6        |        6 |
|         1 |       7 | p7        |        7 |
|         1 |       8 | p8        |        8 |
|         1 |       9 | p9        |        9 |
+-----------+---------+-----------+----------+
10 rows in set (0.02 sec)



MySQL [test]> select __all_part.tenant_id, __all_table_v2.table_id, part_id, part_name, part_idx, __all_part.tenant_id << 40 | part_id as 'external part id' from oceanbase.__all_part, oceanbase.__all_table_v2 where table_name = 't1' limit 10;
+-----------+---------------+---------+-----------+----------+------------------+
| tenant_id | table_id      | part_id | part_name | part_idx | external part id |
+-----------+---------------+---------+-----------+----------+------------------+
|         1 | 1099511677777 |       0 | p0        |        0 |    1099511627776 |
|         1 | 1099511677777 |       1 | p1        |        1 |    1099511627777 |
|         1 | 1099511677777 |       2 | p2        |        2 |    1099511627778 |
|         1 | 1099511677777 |       3 | p3        |        3 |    1099511627779 |
|         1 | 1099511677777 |       4 | p4        |        4 |    1099511627780 |
|         1 | 1099511677777 |       5 | p5        |        5 |    1099511627781 |
|         1 | 1099511677777 |       6 | p6        |        6 |    1099511627782 |
|         1 | 1099511677777 |       7 | p7        |        7 |    1099511627783 |
|         1 | 1099511677777 |       8 | p8        |        8 |    1099511627784 |
|         1 | 1099511677777 |       9 | p9        |        9 |    1099511627785 |
+-----------+---------------+---------+-----------+----------+------------------+