文档

查询分区拓扑

更新时间:

PolarDB-X支持通过多种方式查询分区表的元数据字典及其分区拓扑信息。

使用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命令快速查看(如下所示):

SHOW TOPOLOGY FROM test_tbl_part_name2 ORDER BY PARTITION_NAME, SUBPARTITION_NAME;
+------+----------------------+--------------------------------+----------------+-------------------+----------------+---------------------------+
| ID   | GROUP_NAME           | TABLE_NAME                     | PARTITION_NAME | SUBPARTITION_NAME | PHY_DB_NAME    | DN_ID                     |
+------+----------------------+--------------------------------+----------------+-------------------+----------------+---------------------------+
|    4 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00000 | p1             | p1sp1             | autodb2_p00000 | polardbx-storage-0-master |
|    0 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00001 | p1             | p1sp2             | autodb2_p00001 | polardbx-storage-1-master |
|    5 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00002 | p1             | p1sp3             | autodb2_p00000 | polardbx-storage-0-master |
|    1 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00003 | p1             | p1sp4             | autodb2_p00001 | polardbx-storage-1-master |
|    6 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00004 | p2             | p2sp1             | autodb2_p00000 | polardbx-storage-0-master |
|    2 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00005 | p2             | p2sp2             | autodb2_p00001 | polardbx-storage-1-master |
|    7 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00006 | p2             | p2sp3             | autodb2_p00000 | polardbx-storage-0-master |
|    3 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00007 | p2             | p2sp4             | autodb2_p00001 | polardbx-storage-1-master |
+------+----------------------+--------------------------------+----------------+-------------------+----------------+---------------------------+
8 rows in set (0.07 sec)

使用TABLE_DETAILS视图查询

若需要查询分区表各分区的数据占比及其读写情况,可以通过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所特有的表的元数据信息,例如分区表所属的表组、分区表所属于的全局索引表等信息。

  • 本页导读 (0)