Rule and topology query statements
This topic describes rule and topology query statements.
SHOW RULE [FROM tablename]
Description:
show rule: queries the sharding details of each logical table in the database.show rule from tablename: queries the sharding details of the specified 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 : indicates whether the table is a broadcast table. The value 0 indicates that the table is not a broadcast table. The value 1 indicates that the table is a broadcast table.
DB_PARTITION_KEY : the database shard key. If no database shards exist, the parameter value is empty.
DB_PARTITION_POLICY : the database sharding policy. The parameter value can be a hash value or a date value in formats such as YYYYMM, YYYYDD, and YYYYWEEK.
DB_PARTITION_COUNT : the number of database shards.
TB_PARTITION_KEY : the table shard key. If no table shards exist, the parameter value is empty.
TB_PARTITION_POLICY : the table sharding policy. The parameter value can be a hash value or a date value in formats such as MM, DD, MMDD, and WEEK.
TB_PARTITION_COUNT : the number of table shards.
SHOW FULL RULE [FROM tablename]
You can execute this SQL statement to view the sharding rules of logical tables in a database. This statement returns more information than the SHOW RULE statement.
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 : indicates whether the table is a broadcast table. The value 0 indicates that the table is not a broadcast table. The value 1 indicates that the table is a broadcast table.
JOIN_GROUP : a reserved field. This field is temporarily meaningless.
ALLOW_FULL_TABLE_SCAN : indicates whether data queries are allowed if no table shard keys are specified for sharding. If this parameter is set to true, each physical table is scanned to locate data that meets the condition. This is a full table scan.
DB_NAME_PATTERN : The digit 0 that is enclosed by braces ({}) in the parameter value is a placeholder. When the SQL statement is executed, the placeholders are replaced by the value of DBRULES_STR. The number of digits in the parameter value remains unchanged. For example, if the value of DB_NAME_PATTERN is SEQ{0000}_RDS and the value of DB_RULES_STR is [1,2,3,4], the following DB_NAME values are generated: SEQ_0001_RDS, SEQ_0002_RDS, SEQ_0003_RDS, and SEQ_0004_RDS.
DB_RULES_STR : the database sharding rule.
TB_NAME_PATTERN : The digit 0 that is enclosed by braces ({}) in the parameter value is a placeholder. When the SQL statement is executed, the placeholders are replaced by the value of TBRULES_STR. The number of digits in the parameter value remains unchanged. For example, if the value of TB_NAME_PATTERN is table{00} and the value of TB_RULES_STR is [1,2,3,4,5,6,7,8], the following 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 rule.
PARTITION_KEYS : a set of the database and table shard keys. If database sharding and table sharding are performed, the database shard key is placed before the table shard key.
DEFAULT_DB_INDEX : the database shard in which a non-sharded table is stored.
SHOW TOPOLOGY FROM tablename
You can execute this SQL statement to view the topology of a specified logical table. The information contains the database shards to which data in the logical table is assigned and the table shards in 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
You can execute this SQL statement to view a set of database and table shard keys. The database shard key and the table shard key are separated by commas (,). If two values are returned, database sharding and table sharding are performed. The first value is the database shard key and the second value is the table shard key. If only one value is returned, only database sharding is performed. This value is the database shard key.
SHOW PARTITIONS FROM EMP;
+-----------+
| KEYS |
+-----------+
| emp_no,id |
+-----------+
1 row in set (0.00 sec)
SHOW BROADCASTS
You can execute this SQL statement to view broadcast tables.
SHOW BROADCASTS;
+------+------------+
| ID | TABLE_NAME |
+------+------------+
| 0 | brd2 |
| 1 | brd_tbl |
+------+------------+
2 rows in set (0.01 sec)
SHOW DATASOURCES
You can execute this SQL statement to view the information about the underlying storage. The information includes the database name, database group name, connection URL, username, type of the underlying storage, 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 name of the database.
GROUP : the name of the database group. After the databases are grouped, you can manage multiple databases that store the same data in a group. For example, after you replicate the data of a database to an ApsaraDB RDS for MySQL instance, you can manage the primary database and the secondary database in a group. Database grouping can help resolve issues that are associated with read/write splitting and failovers.
URL : the URL that is used to connect to an underlying ApsaraDB RDS for MySQL database.
TYPE : the type of the underlying storage.
READ_WEIGHT : the read weight. If you want to reduce the number of read requests sent to the primary ApsaraDB RDS for MySQL instance, you can use the read/write splitting feature to distribute some read requests to the secondary ApsaraDB RDS for MySQL instances. PolarDB-X automatically identifies read and write requests. Then, it sends the write requests to the primary ApsaraDB RDS for MySQL instance and distributes the read requests to each ApsaraDB RDS for MySQL instance based on the specified read weights.
WRITE_WEIGHT : the write weight.
SHOW NODE
You can execute this SQL statement to view the data of a physical database, such as the total number of read operations, the total number of write operations, the total read weights, and the total write weights.
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 processed by the primary ApsaraDB RDS for MySQL instance.
SLAVE_COUNT : the total number of read-only queries processed by the secondary ApsaraDB RDS for MySQL instances.
MASTER_PERCENT : the percentage of the total number of read and write queries processed by the primary ApsaraDB RDS for MySQL instance. This percentage is not specified by the user.
SLAVE_PERCENT : the percentage of the total number of read and write queries processed by the secondary ApsaraDB RDS for MySQL instances. This percentage is not specified by the user.
Note
Read-only queries in transactions are sent to the primary ApsaraDB RDS for MySQL instance.
The
MASTER_PERCENTandSLAVE_PERCENTcolumns indicate the historical data. If the ratio between the read weight and the write weight changes, the values in the columns do not immediately reflect the latest ratio. The latest ratio appears after a long period of time elapses.