查询分区拓扑

2024-07-18 03:07| 来源: 网络整理| 查看: 265

使用PARTITIONS视图查询

PolarDB-X兼容MySQL的INFORMATION_SCHEMA.PARTITIONS的视图查询，支持通过PARTITIONS视图查询各个一级分区及其二级分区的相关元信息，例如：

select * from information_schema.partitions where table_schema='autodb2' and table_name='test_tbl_part_name2' order by partition_name, subpartition_name; +---------------+--------------+---------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+--------------------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+-------------------+-----------+-----------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | SUBPARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME | +---------------+--------------+---------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+--------------------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+-------------------+-----------+-----------------+ | def | autodb2 | test_tbl_part_name2 | p1 | p1sp1 | 1 | 1 | KEY | KEY | bid | id | 1 | -4611686018427387899 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL | | def | autodb2 | test_tbl_part_name2 | p1 | p1sp2 | 1 | 2 | KEY | KEY | bid | id | 1 | 3 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL | | def | autodb2 | test_tbl_part_name2 | p1 | p1sp3 | 1 | 3 | KEY | KEY | bid | id | 1 | 4611686018427387905 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL | | def | autodb2 | test_tbl_part_name2 | p1 | p1sp4 | 1 | 4 | KEY | KEY | bid | id | 1 | 9223372036854775807 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL | | def | autodb2 | test_tbl_part_name2 | p2 | p2sp1 | 2 | 1 | KEY | KEY | bid | id | 9223372036854775807 | -4611686018427387899 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL | | def | autodb2 | test_tbl_part_name2 | p2 | p2sp2 | 2 | 2 | KEY | KEY | bid | id | 9223372036854775807 | 3 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL | | def | autodb2 | test_tbl_part_name2 | p2 | p2sp3 | 2 | 3 | KEY | KEY | bid | id | 9223372036854775807 | 4611686018427387905 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL | | def | autodb2 | test_tbl_part_name2 | p2 | p2sp4 | 2 | 4 | KEY | KEY | bid | id | 9223372036854775807 | 9223372036854775807 | 0 | 0 | 16384 | NULL | 16384 | NULL | NULL | NULL | NULL | NULL | | default | NULL | +---------------+--------------+---------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+--------------------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+-------------------+-----------+-----------------+ 8 rows in set (0.14 sec) 使用SHOW TOPOLOGY查询

若需要简单查询分区表的整体拓扑以及各个分区的物理位置（物理库表所在的DN），也可以采用SHOW TOPOLOGY FROM #table_name命令快速查看（如下所示）：

若需要查询分区表各分区的数据占比及其读写情况，可以通过INFORMATION_SCHEMA.TABLE_DETAIL视图进行，例如：

select table_name, partition_name, subpartition_name, percent, rows_read, rows_inserted, rows_updated, rows_deleted from information_schema.table_detail where table_schema='autodb2' and table_name='test_tbl_part_name2' order by partition_name, subpartition_name; +---------------------+----------------+-------------------+--------------------------------------+-----------+---------------+--------------+--------------+ | table_name | partition_name | subpartition_name | percent | rows_read | rows_inserted | rows_updated | rows_deleted | +---------------------+----------------+-------------------+--------------------------------------+-----------+---------------+--------------+--------------+ | test_tbl_part_name2 | p1 | p1sp1 | 0.00%├-------------------------┤ | 0 | 0 | 0 | 0 | | test_tbl_part_name2 | p1 | p1sp2 | 0.00%├-------------------------┤ | 0 | 0 | 0 | 0 | | test_tbl_part_name2 | p1 | p1sp3 | 0.00%├-------------------------┤ | 0 | 0 | 0 | 0 | | test_tbl_part_name2 | p1 | p1sp4 | 0.00%├-------------------------┤ | 0 | 0 | 0 | 0 | | test_tbl_part_name2 | p2 | p2sp1 | 0.00%├-------------------------┤ | 0 | 0 | 0 | 0 | | test_tbl_part_name2 | p2 | p2sp2 | 0.00%├-------------------------┤ | 0 | 0 | 0 | 0 | | test_tbl_part_name2 | p2 | p2sp3 | 0.00%├-------------------------┤ | 0 | 0 | 0 | 0 | | test_tbl_part_name2 | p2 | p2sp4 | 0.00%├-------------------------┤ | 0 | 0 | 0 | 0 | +---------------------+----------------+-------------------+--------------------------------------+-----------+---------------+--------------+--------------+ 8 rows in set (0.10 sec)

通过INFORMATION_SCHEMA.TABLE_DETAIL视图还能够查询PolarDB-X所特有的表的元数据信息，例如分区表所属的表组、分区表所属于的全局索引表等信息。

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章