This topic describes query statements for rules and topology.
SHOW RULE [FROM tablename]
Description:
show rule: Views the sharding status of each logical table in the database.show rule from tablename: Views the sharding status of a specific logical table in the database.
SHOW RULE;
+----+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+----+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| 0 | k_1 | 0 | k | hash | 40 | k | hash | 2 |
| 1 | k_2 | 0 | k | hash | 40 | k | hash | 2 |
| 2 | sbtest1 | 0 | id | hash | 40 | id | hash | 2 |
| 3 | t1 | 0 | id | hash | 40 | id | hash | 4 |
+----+------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
4 rows in set (0.05 sec) Important columns:
- BROADCAST: Specifies whether the table is a broadcast table. A value of 0 indicates no, and 1 indicates yes.
- DB_PARTITION_KEY: The shard key for database sharding. If database sharding is not used, this value is empty.
- DB_PARTITION_POLICY: The sharding policy for databases. Valid values include hash and date-based policies such as YYYYMM, YYYYDD, and YYYYWEEK.
- DB_PARTITION_COUNT: The number of database shards.
- TB_PARTITION_KEY: The shard key for table sharding. If table sharding is not used, this value is empty.
- TB_PARTITION_POLICY: The sharding policy for tables. Valid values include hash and date-based policies such as MM, DD, MMDD, and WEEK.
- TB_PARTITION_COUNT: The number of table shards.
SHOW FULL RULE [FROM tablename]
Views the sharding rules for logical tables in a database. This command provides more detailed information than the SHOW RULE command.
SHOW FULL RULE;
+----+------------+-----------+------------+-----------------------+----------------------+--------------------------------------------------+-------------------+----------------------------------------+----------------+--------------------+
| ID | TABLE_NAME | BROADCAST | JOIN_GROUP | ALLOW_FULL_TABLE_SCAN | DB_NAME_PATTERN | DB_RULES_STR | TB_NAME_PATTERN | TB_RULES_STR | PARTITION_KEYS | DEFAULT_DB_INDEX |
+----+------------+-----------+------------+-----------------------+----------------------+--------------------------------------------------+-------------------+----------------------------------------+----------------+--------------------+
| 0 | k_1 | 0 | NULL | 1 | TEST1_{000000}_GROUP | ((#k,1,80#).longValue().abs() % 80).intdiv(2) | k_1_cewR_{00} | ((#k,1,80#).longValue().abs() % 80) | k | TEST1_SINGLE_GROUP |
| 1 | k_2 | 0 | NULL | 1 | TEST1_{000000}_GROUP | ((#k,1,80#).longValue().abs() % 80).intdiv(2) | k_2_1xsQ_{00} | ((#k,1,80#).longValue().abs() % 80) | k | TEST1_SINGLE_GROUP |
| 2 | sbtest1 | 0 | NULL | 1 | TEST1_{000000}_GROUP | ((#id,1,80#).longValue().abs() % 80).intdiv(2) | sbtest1_wO5k_{00} | ((#id,1,80#).longValue().abs() % 80) | id | TEST1_SINGLE_GROUP |
| 3 | t1 | 0 | NULL | 1 | TEST1_{000000}_GROUP | ((#id,1,160#).longValue().abs() % 160).intdiv(4) | t1_EMrC_{000} | ((#id,1,160#).longValue().abs() % 160) | id | TEST1_SINGLE_GROUP |
+----+------------+-----------+------------+-----------------------+----------------------+--------------------------------------------------+-------------------+----------------------------------------+----------------+--------------------+Important columns:
- BROADCAST: Specifies whether the table is a broadcast table. A value of 0 indicates no, and 1 indicates yes.
- JOIN_GROUP: A reserved field that is not currently used.
- ALLOW_FULL_TABLE_SCAN: Specifies whether to allow queries on sharded databases and tables without a shard key. If this is set to true, a full table scan is performed. A full table scan queries every physical table to find matching data.
- DB_NAME_PATTERN: The 0 in the curly braces {} is a placeholder. When an SQL statement is executed, the placeholder is replaced by the value calculated from DB_RULES_STR, and the number of digits is preserved. For example, if DB_NAME_PATTERN is SEQ_{0000}_RDS and DB_RULES_STR is [1,2,3,4], four database names are generated: SEQ_0001_RDS, SEQ_0002_RDS, SEQ_0003_RDS, and SEQ_0004_RDS.
- DB_RULES_STR: The specific database sharding rules.
- TB_NAME_PATTERN: The 0 in the curly braces {} is a placeholder. When an SQL statement is executed, the placeholder is replaced by the value calculated from TB_RULES_STR, and the number of digits is preserved. For example, if TB_NAME_PATTERN is table_{00} and TB_RULES_STR is [1,2,3,4,5,6,7,8], eight tables are generated: table_01, table_02, table_03, table_04, table_05, table_06, table_07, and table_08.
- TB_RULES_STR: The table sharding rules.
- PARTITION_KEYS: The collection of database and table shard keys. In scenarios with both database and table sharding, the database shard key is listed first, followed by the table shard key.
- DEFAULT_DB_INDEX: The database shard where single tables are stored.
SHOW TOPOLOGY FROM tablename
Views the topology of a specific logical table. This command shows the database shards where the logical table is stored and the table shards within each database shard.
SHOW TOPOLOGY FROM EMP;
+------+--------------------------------------------------+------------+
| ID | GROUP_NAME | TABLE_NAME |
+------+--------------------------------------------------+------------+
| 0 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS | emp_0 |
| 1 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS | emp_1 |
| 2 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0001_RDS | emp_0 |
| 3 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0001_RDS | emp_1 |
| 4 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0002_RDS | emp_0 |
| 5 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0002_RDS | emp_1 |
| 6 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0003_RDS | emp_0 |
| 7 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0003_RDS | emp_1 |
| 8 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0004_RDS | emp_0 |
| 9 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0004_RDS | emp_1 |
| 10 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0005_RDS | emp_0 |
| 11 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0005_RDS | emp_1 |
| 12 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0006_RDS | emp_0 |
| 13 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0006_RDS | emp_1 |
| 14 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0007_RDS | emp_0 |
| 15 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0007_RDS | emp_1 |
+------+--------------------------------------------------+------------+SHOW PARTITIONS FROM tablename
Views the collection of database and table shard keys. The keys are separated by a comma. If two values are returned, the table uses both database and table sharding. The first value is the database shard key, and the second is the table shard key. If only one value is returned, the table uses only database sharding, and the value is the database shard key.
SHOW PARTITIONS FROM EMP;
+-----------+
| KEYS |
+-----------+
| emp_no,id |
+-----------+
1 row in set (0.00 sec)
SHOW BROADCASTS
Views a list of broadcast tables.
SHOW BROADCASTS;
+------+------------+
| ID | TABLE_NAME |
+------+------------+
| 0 | brd2 |
| 1 | brd_tbl |
+------+------------+
2 rows in set (0.01 sec)
SHOW DATASOURCES
Views underlying storage information. This includes the database name, database group name, connection information, username, underlying storage class, read and write weights, and connection pool information.
SHOW DATASOURCES;
+------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
| ID | SCHEMA | NAME | GROUP | URL | USER | TYPE | INIT | MIN | MAX | IDLE_TIMEOUT | MAX_WAIT | ACTIVE_COUNT | POOLING_COUNT | ATOM | READ_WEIGHT | WRITE_WEIGHT |
+------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
| 0 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0000_iiab_1 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0000 | jnkinsea0 | xdb | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0000_iiab | 10 | 10 |
| 1 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0001_iiab_2 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0001_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0001 | jnkinsea0 | xdb | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0001_iiab | 10 | 10 |
| 2 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0002_iiab_3 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0002_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0002 | jnkinsea0 | xdb | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0002_iiab | 10 | 10 |
| 3 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0003_iiab_4 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0003_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0003 | jnkinsea0 | xdb | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0003_iiab | 10 | 10 |
| 4 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0004_iiab_5 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0004_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0004 | jnkinsea0 | xdb | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0004_iiab | 10 | 10 |
| 5 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0005_iiab_6 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0005_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0005 | jnkinsea0 | xdb | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0005_iiab | 10 | 10 |
| 6 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0006_iiab_7 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0006_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0006 | jnkinsea0 | xdb | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0006_iiab | 10 | 10 |
| 7 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0007_iiab_8 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0007_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0007 | jnkinsea0 | xdb | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0007_iiab | 10 | 10 |
+------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+Important columns:
- Schema: The database name.
- GROUP: The database group name. A group is used to manage multiple databases that contain identical data, such as primary and standby databases after data replication using RDS (MySQL). This is mainly used for read/write splitting and primary/standby switchovers.
- URL: The connection information for the underlying RDS (MySQL) instance.
- type: The underlying storage class.
- READ_WEIGHT: The read weight. If the read pressure on the primary instance is high, you can use the read/write splitting feature to distribute read traffic and reduce the load on the primary RDS instance. PolarDB-X automatically detects read and write traffic. It directs write traffic to the primary RDS instance and routes read traffic to all RDS instances based on the configured weights.
- WRITE_WEIGHT: The write weight.
SHOW NODE
Views the cumulative historical number of reads and writes, and the read and write weights for physical databases.
SHOW NODE;
+------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
| ID | NAME | MASTER_READ_COUNT | SLAVE_READ_COUNT | MASTER_READ_PERCENT | SLAVE_READ_PERCENT |
+------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
| 0 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS | 12 | 0 | 100% | 0% |
| 1 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0001_RDS | 0 | 0 | 0% | 0% |
| 2 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0002_RDS | 0 | 0 | 0% | 0% |
| 3 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0003_RDS | 0 | 0 | 0% | 0% |
| 4 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0004_RDS | 0 | 0 | 0% | 0% |
| 5 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0005_RDS | 0 | 0 | 0% | 0% |
| 6 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0006_RDS | 0 | 0 | 0% | 0% |
| 7 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0007_RDS | 0 | 0 | 0% | 0% |
+------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
8 rows in set (0.01 sec)
Important columns:
- MASTER_COUNT: The total number of read and write queries on the RDS primary instance.
- SLAVE_READ_COUNT: The cumulative number of read-only queries processed by the standby RDS instance.
- MASTER_PERCENT: The percentage of read and write queries processed by the RDS primary instance. This column displays the actual cumulative percentage, not the user-configured percentage.
- SLAVE_PERCENT: The percentage of read and write queries that are processed by the standby RDS instance. This value represents the actual cumulative percentage, not the user-configured percentage.
Note
- Read-only queries within a transaction are sent to the primary RDS instance.
- Because the
MASTER_PERCENTandSLAVE_PERCENTcolumns represent cumulative historical data, their values do not immediately reflect a new read/write weight ratio. A significant amount of time must pass for sufficient data to accumulate and accurately reflect the new ratio.