This topic describes rule and topology query statements.

SHOW RULE [FROM tablename]

Description:
  • SHOW RULE: queries the sharding details of each logical table in a database.
  • SHOW RULE FROM tablename: queries the sharding details of a specified logical table in a database.
mysql> 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 | dept_manager |         0 |                  | NULL                | 1                  |                  | NULL                | 1                  |
|    1 | emp          |         0 | emp_no           | hash                | 8                  | id               | hash                | 2                  |
|    2 | example      |         0 | shard_key        | hash                | 8                  |                  | NULL                | 1                  |
+------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
3 rows in set (0.01 sec)
            
Important columns:
  • BROADCAST: indicates whether the table is a broadcast table. A value of 0 indicates that the table is not a broadcast table. A value of 1 indicates that the table is a broadcast table.
  • DB_PARTITION_KEY: indicates the database shard key. If no database shards exist, the parameter value is empty.
  • DB_PARTITION_POLICY: indicates the database sharding policy. The parameter values can be hash values and date values in the formats such as YYYYMM, YYYYDD, and YYYYWEEK.
  • DB_PARTITION_COUNT: indicates the number of database shards.
  • TB_PARTITION_KEY: indicates the table shard key. If no table shards exist, the parameter value is empty.
  • TB_PARTITION_POLICY: indicates the table sharding policy. The parameter values can be hash values or date values in the formats such as MM, DD, MMDD, and WEEK.
  • TB_PARTITION_COUNT: indicates the number of table shards.

SHOW FULL RULE [FROM tablename]

You can execute this SQL statement to view the sharding rules of the logical tables in a database. This statement queries more detailed information than the SHOW RULE statement.

mysql> 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 | dept_manager |         0 | NULL       |                     0 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS   | NULL                                                | dept_manager    | NULL                               | NULL           | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS |
|    1 | emp          |         0 | NULL       |                     1 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_{0000}_RDS | ((#emp_no,1,8#).longValue().abs() % 8)              | emp_{0}         | ((#id,1,2#).longValue().abs() % 2) | emp_no      id | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS |
|    2 | example      |         0 | NULL       |                     1 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_{0000}_RDS | ((#shard_key,1,8#).longValue().abs() % 8).intdiv(1) | example         | NULL                               | shard_key      | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS |
+------+--------------+-----------+------------+-----------------------+----------------------------------------------------+-----------------------------------------------------+-----------------+------------------------------------+----------------+--------------------------------------------------+
3 rows in set (0.01 sec)
            
Important columns:
  • BROADCAST: indicates whether the table is a broadcast table. A value of 0 indicates that the table is not a broadcast table. A value of 1 indicates that the table is a broadcast table.
  • JOIN_GROUP: indicates a reserved field.
  • ALLOW_FULL_TABLE_SCAN: indicates whether data querying is allowed if no table shard keys are specified for sharding. If this parameter is set to true, each physical table is scanned to locate the data that meets the condition. This is a full table scan.
  • DB_NAME_PATTERN: The digit 0 inside a pair of braces {} in the parameter value is a placeholder. When the SQL statement is executed, the placeholders are replaced by the value of DB_RULES_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: indicates the database sharding rule.
  • TB_NAME_PATTERN: The digit 0 inside a pair of braces {} in the parameter value is a placeholder. When the SQL statement is executed, the placeholders are replaced by the value of TB_RULES_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: indicates the table sharding rule.
  • PARTITION_KEYS: indicates a set of the database and table shard keys. If both database sharding and table sharding are performed, the database shard key is placed before the table shard key.
  • DEFAULT_DB_INDEX: indicates the database shard in which a single-database non-partitioned 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 partitioned and the table shards in each database shard.

mysql> 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      |
+------+--------------------------------------------------+------------+
16 rows in set (0.01 sec)
            

SHOW PARTITIONS FROM tablename

You can execute this SQL statement to view a set of database and table shard keys, which are separated by commas (,). If two values are returned, both 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.

mysql> 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 the broadcast tables.

mysql> 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, storage type, read and write weights, and connection pool information.

mysql> 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 | mysql | 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 | mysql | 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 | mysql | 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 | mysql | 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 | mysql | 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 | mysql | 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 | mysql | 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 | mysql | 0    | 24   | 72   | 15           | 5000     | 0            | 1             | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0007_iiab | 10          | 10           |
+------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
8 rows in set (0.01 sec)
            

Important columns:

  • SCHEMA: indicates the database name.
  • GROUP: indicates the database group name. 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 enables read/write splitting and primary/secondary switchovers.
  • URL: indicates the URL that is used to connect to an underlying ApsaraDB RDS for MySQL database.
  • TYPE: indicates the underlying storage type. Only ApsaraDB RDS for MySQL is supported.
  • READ_WEIGHT: indicates the read weight. If you want to reduce the number of read requests 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. This offloads the read requests from the primary ApsaraDB RDS for MySQL instance. DRDS automatically identifies the 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: indicates the write weight.

SHOW NODE

You can execute this SQL statement to view the data of a physical database, such as the accumulative number of read operations, the accumulative number of write operations, the accumulative read weights, and the accumulative write weights.

mysql> 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: indicates the accumulative number of read and write queries processed by the primary ApsaraDB RDS for MySQL instance.
  • SLAVE_COUNT: indicates the accumulative number of read-only queries processed by the secondary ApsaraDB RDS for MySQL instances.
  • MASTER_PERCENT: indicates the actual percentage of the accumulative read and write queries processed by the primary ApsaraDB RDS for MySQL instance. This is not the specified percentage.
  • SLAVE_PERCENT: indicates the actual percentage of the accumulative read and write queries processed by the secondary ApsaraDB RDS for MySQL instances. This is not the specified percentage.
Note
  • Read-only queries in transactions are sent to the primary ApsaraDB RDS for MySQL instance.
  • The MASTER_PERCENT and SLAVE_PERCENT columns indicate the accumulative historical data. If the ratio between the read weight and the write weight changes, these parameter values do not immediately reflect the latest ratio. The latest ratio appears after a long period of time.