All Products
Search
Document Center

Rule and topology query statements

Last Updated: Aug 07, 2020

Distributed Relational Database Service (DRDS) provides the following statements for you to query rules and topology:

SHOW RULE [FROM tablename]

Instructions

  • show rule: queries the sharding status of each logical table in a database.
  • show rule from tablename: queries the sharding status of a specified logical table in a database.

Important columns:

  • BROADCAST: indicates whether the table is a broadcast table. 0 indicates No and 1 indicates Yes.
  • DB_PARTITION_KEY: indicates the database shard key. If no database shards exist, the parameter value is NULL.
  • DB_PARTITION_POLICY: indicates the database sharding policy. Options are hash and date policies 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 NULL.
  • TB_PARTITION_POLICY: indicates the table sharding policy. Options are hash and date policies such as MM, DD, MMDD, and WEEK.
  • TB_PARTITION_COUNT: indicates the number of table shards.
  1. mysql> show rule;
  2. +------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
  3. | ID | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
  4. +------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
  5. | 0 | dept_manager | 0 | | NULL | 1 | | NULL | 1 |
  6. | 1 | emp | 0 | emp_no | hash | 8 | id | hash | 2 |
  7. | 2 | example | 0 | shard_key | hash | 8 | | NULL | 1 |
  8. +------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
  9. 3 rows in set (0.01 sec)

SHOW FULL RULE [FROM tablename]

You can execute this SQL statement to view the sharding rule for a logical table in a database. This statement queries more detailed information than the SHOW RULE statement.

Important columns:

  • BROADCAST: indicates whether the table is a broadcast table. 0 indicates No and 1 indicates Yes.
  • JOIN_GROUP: indicates a reserved field. It is meaningless.
  • ALLOW_FULL_TABLE_SCAN: indicates whether to allow data querying when no shard key is specified for sharding. If this parameter is set to true, each physical table is scanned to find data that meets the condition, which is a full table scan.
  • DB_NAME_PATTERN: The digit 0 inside a pair of braces {} in DB_NAME_PATTERN is a placeholder. When the SQL statement is executed, it is replaced by the value calculated by DB_RULES_STR. The number of digits 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 four database 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 TB_NAME_PATTERN is a placeholder. When the SQL statement is executed, it is replaced by the value calculated by TB_RULES_STR. The number of digits 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 eight 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 database and table shard keys. When 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 non-partition databases and non-partition tables are stored.
  1. mysql> show full rule;
  2. +------+--------------+-----------+------------+-----------------------+----------------------------------------------------+-----------------------------------------------------+-----------------+------------------------------------+----------------+--------------------------------------------------+
  3. | 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 |
  4. +------+--------------+-----------+------------+-----------------------+----------------------------------------------------+-----------------------------------------------------+-----------------+------------------------------------+----------------+--------------------------------------------------+
  5. | 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 |
  6. | 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 |
  7. | 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 |
  8. +------+--------------+-----------+------------+-----------------------+----------------------------------------------------+-----------------------------------------------------+-----------------+------------------------------------+----------------+--------------------------------------------------+
  9. 3 rows in set (0.01 sec)

SHOW TOPOLOGY FROM tablename

You can execute this SQL statement to view the topology of a specified logical table, that is, the table shards in database shards to which data in the logical table is distributed.

  1. mysql> show topology from emp;
  2. +------+--------------------------------------------------+------------+
  3. | ID | GROUP_NAME | TABLE_NAME |
  4. +------+--------------------------------------------------+------------+
  5. | 0 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS | emp_0 |
  6. | 1 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS | emp_1 |
  7. | 2 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0001_RDS | emp_0 |
  8. | 3 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0001_RDS | emp_1 |
  9. | 4 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0002_RDS | emp_0 |
  10. | 5 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0002_RDS | emp_1 |
  11. | 6 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0003_RDS | emp_0 |
  12. | 7 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0003_RDS | emp_1 |
  13. | 8 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0004_RDS | emp_0 |
  14. | 9 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0004_RDS | emp_1 |
  15. | 10 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0005_RDS | emp_0 |
  16. | 11 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0005_RDS | emp_1 |
  17. | 12 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0006_RDS | emp_0 |
  18. | 13 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0006_RDS | emp_1 |
  19. | 14 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0007_RDS | emp_0 |
  20. | 15 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0007_RDS | emp_1 |
  21. +------+--------------------------------------------------+------------+
  22. 16 rows in set (0.01 sec)

SHOW PARTITIONS FROM tablename

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

  1. mysql> show partitions from emp;
  2. +-----------+
  3. | KEYS |
  4. +-----------+
  5. | emp_no,id |
  6. +-----------+
  7. 1 row in set (0.00 sec)

SHOW BROADCASTS

You can execute this SQL statement to view the broadcast tables.

  1. mysql> show broadcasts;
  2. +------+------------+
  3. | ID | TABLE_NAME |
  4. +------+------------+
  5. | 0 | brd2 |
  6. | 1 | brd_tbl |
  7. +------+------------+
  8. 2 rows in set (0.01 sec)

SHOW DATASOURCES

You can execute this SQL statement to view the information about the underlying storage, including the database name, database group name, connection URL, user name, underlying storage type, read/write weight, and connection pool information.

Important columns:

  • SCHEMA: indicates the database name.
  • GROUP: indicates the database group name. Grouping is intended to manage multiple databases that store the same data, such as the primary and secondary databases that replicate data by using an ApsaraDB RDS for MySQL instance. Grouping is used for read/write splitting and primary/secondary failover.
  • URL: indicates the connection URL of an underlying ApsaraDB RDS for MySQL database.
  • TYPE: indicates the underlying storage type, for example, the ApsaraDB RDS for MySQL.
  • READ_WEIGHT: indicates the read weight. When the primary ApsaraDB RDS for MySQL instance has many read requests, you can use the read/write splitting function of DRDS to distribute the read requests to secondary ApsaraDB RDS for MySQL instances, reducing the read pressure on the primary ApsaraDB RDS for MySQL instance. DRDS automatically identifies read and write traffic. It diverts the write traffic to the primary ApsaraDB RDS for MySQL instance and the read traffic to each ApsaraDB RDS for MySQL instance based on its weight settings.
  • WRITE_WEIGHT: indicates the write weight. See the description of READ_WEIGHT.
  1. mysql> show datasources;
  2. +------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
  3. | ID | SCHEMA | NAME | GROUP | URL | USER | TYPE | INIT | MIN | MAX | IDLE_TIMEOUT | MAX_WAIT | ACTIVE_COUNT | POOLING_COUNT | ATOM | READ_WEIGHT | WRITE_WEIGHT |
  4. +------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
  5. | 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 |
  6. | 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 |
  7. | 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 |
  8. | 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 |
  9. | 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 |
  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 |
  11. | 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 |
  12. | 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 |
  13. +------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
  14. 8 rows in set (0.01 sec)

SHOW NODE

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

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 read and write queries processed by the primary ApsaraDB RDS for MySQL instance, not the configured percentage.
  • SLAVE_PERCENT: indicates the actual percentage of read and write queries processed by the secondary ApsaraDB RDS for MySQL instances, not the configured percentage.

Note:

  • Read-only queries in transactions are sent to the primary ApsaraDB RDS for MySQL instance.
  • The MASTER_PERCENT and SLAVE_PERCENT fields indicate the accumulative historical data. After the read/write weight ratio is changed, these values do not immediately reflect the latest read/write weight ratio, which appears after a long period of time passed.
  1. mysql> show node;
  2. +------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
  3. | ID | NAME | MASTER_READ_COUNT | SLAVE_READ_COUNT | MASTER_READ_PERCENT | SLAVE_READ_PERCENT |
  4. +------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
  5. | 0 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS | 12 | 0 | 100% | 0% |
  6. | 1 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0001_RDS | 0 | 0 | 0% | 0% |
  7. | 2 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0002_RDS | 0 | 0 | 0% | 0% |
  8. | 3 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0003_RDS | 0 | 0 | 0% | 0% |
  9. | 4 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0004_RDS | 0 | 0 | 0% | 0% |
  10. | 5 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0005_RDS | 0 | 0 | 0% | 0% |
  11. | 6 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0006_RDS | 0 | 0 | 0% | 0% |
  12. | 7 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0007_RDS | 0 | 0 | 0% | 0% |
  13. +------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
  14. 8 rows in set (0.01 sec)