PolarDB-X memungkinkan Anda menanyakan metadata tabel terpartisi dan topologi partisinya menggunakan beberapa metode.
Gunakan Tampilan PARTITIONS
PolarDB-X kompatibel dengan tampilan INFORMATION_SCHEMA.PARTITIONS dari MySQL. Anda dapat menggunakan tampilan PARTITIONS untuk menanyakan metadata setiap partisi dan subpartisinya. Contoh:
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 baris dalam set (0,14 detik)
Jalankan pernyataan SHOW TOPOLOGY
Anda dapat menjalankan pernyataan SHOW TOPOLOGY FROM #table_name untuk menanyakan topologi keseluruhan tabel terpartisi serta lokasi fisik setiap partisi. Lokasi fisik mengacu pada node data tempat setiap tabel fisik berada. Contoh:
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 baris dalam set (0,07 detik)Gunakan tampilan TABLE_DETAIL
Anda dapat menggunakan tampilan INFORMATION_SCHEMA.TABLE_DETAIL untuk menanyakan persentase data di setiap partisi tabel terpartisi dan informasi baca-tulis partisi tersebut. Contoh:
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 baris dalam set (0,10 detik)
Anda juga dapat menggunakan tampilan INFORMATION_SCHEMA.TABLE_DETAIL untuk menanyakan metadata spesifik tabel PolarDB-X, seperti grup tabel tempat tabel terpartisi milik dan tabel indeks global yang dimiliki oleh tabel terpartisi tersebut.