天天看点
返回

PolarDB-X 1.0-SQL 手册-SHOW-规则和拓扑查询语句

规则和拓扑类语句如下：

SHOW RULE [FROM tablename]

使用说明：

  • show rule

     ：查看数据库下每一个逻辑表的拆分情况；

  • show rule from tablename

     ：查看数据库下指定逻辑表的拆分情况。

重要列详解：

  • BROADCAST

     ：是否为广播表（0：否，1：是）；

  • DB_PARTITION_KEY

     ：分库的拆分键，没有分库的话，值为空；

  • DB_PARTITION_POLICY

     ：分库的拆分策略，取值包括哈希或YYYYMM、YYYYDD、YYYYWEEK 等日期策略；

  • DB_PARTITION_COUNT

     ：分库数；

  • TB_PARTITION_KEY

     ：分表的拆分键，没有分表的话，值为空；

  • TB_PARTITION_POLICY

     ：分表的拆分策略，取值包括哈希或MM、DD、MMDD、WEEK等日期策略；

  • TB_PARTITION_COUNT

     ：分表数。

  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]

查看数据库下逻辑表的拆分规则，比 SHOW RULE 指令展示的信息更加详细。

  • BROADCAST

  • JOIN_GROUP

     ：保留字段，暂时无意义。

  • ALLOW_FULL_TABLE_SCAN

     ：分库分表在没有指定分表键值的情况下是否允许查询数据，如果配置为 true，此时需要扫描每一个物理表来查找出符合条件的数据，简称为全表扫描；

  • DB_NAME_PATTERN

     ：

    DB_NAME_PATTERN

     中

    {}

     之间的0为占位符，执行SQL时会被

    DB_RULES_STR

     计算出的值替代，并保持位数。比如，

    DB_NAME_PATTERN

     的值为

    SEQ_{0000}_RDS

     ，

    DB_RULES_STR

    [1,2,3,4]

     ，则会产生4个

    DB_NAME

     ，分别为

    SEQ_0001_RDS

     、

    SEQ_0002_RDS

    SEQ_0003_RDS

    SEQ_0004_RDS

     ；

  • DB_RULES_STR

     ：具体的分库规则；

  • TB_NAME_PATTERN

    TB_NAME_PATTERN

    {}

    TB_RULES_STR

    TB_NAME_PATTERN

    table_{00}

    TB_RULES_STR

    [1,2,3,4,5,6,7,8]

     ，则会产生8张表，分别为

    table_01

    table_02

    table_03

    table_04

    table_05

    table_06

    table_07

    table_08

  • TB_RULES_STR

     ：分表规则；

  • PARTITION_KEYS

     ：分库和分表键集合，对于既分库又分表的情形，分库键在前，分表键在后；

  • DEFAULT_DB_INDEX

     ：单库单表存放的分库。

  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

查看指定逻辑表的拓扑分布，展示该逻辑表保存在哪些分库中，每个分库下包含哪些分表。

  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

查看分库分表键集合，分库键和分表键之间用逗号分割。如果最终结果有两个值，说明是既分库又分表的情形，第一个是分库键，第二个是分表键。如果结果只有一个值，说明是分库不分表的情形，该值是分库键。

  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

查看广播表列表。

  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

查看底层存储信息，包含数据库名、数据库分组名、连接信息、用户名、底层存储类型、读写权重、连接池信息等。

  • SCHEMA

     ：数据库名；

  • GROUP

     ：数据库分组名。分组的目标是管理多组数据完全相同的数据库，比如通过RDS MySQL进行数据复制后的主备数据库。主要用来解决读写分离、主备切换的问题；

  • URL

     ：底层数据库（如RDS MySQL）的连接信息；

  • TYPE

     ：底层存储类型，如RDS MySQL;

  • READ_WEIGHT

     ：读权重。在主实例的读压力比较大的时候，可以通过PolarDB-X读写分离功能将读流量进行分流，减轻RDS主实例的压力。PolarDB-X会自动识别读写流量，引导写流量进入RDS主实例，读流量则按配置的权重流向所有RDS实例；

  • WRITE_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

查看物理库的读写次数（历史累计数据）、读写权重（历史累计数据）。

  • MASTER_COUNT

     ：RDS主实例处理的读写查询次数（历史累计数据）；

  • SLAVE_COUNT

     ：RDS备实例处理的只读查询次数（历史累计数据）；

  • MASTER_PERCENT

     ：RDS主实例处理的读写查询占比（注意该列显示的是累计的实际数据占比，并不是用户配置的百分比）；

  • SLAVE_PERCENT

     ：RDS备实例处理的读写查询占比（注意该列显示的是累计的实际数据占比，并不是用户配置的百分比）。

注意：

  • 事务中的只读查询会被发送到RDS主实例；
  • 由于

    MASTER_PERCENT

    SLAVE_PERCENT

     这两列代表的是历史累计数据，更改读写权重的配比后，这几个数值并不能立即反应最新的读写权重配比，需累计一段比较长的时间才行。

  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)
存储 sql 关系型数据库 mysql java 数据库连接 数据库 rds java数据存储 java存储过程数据 java存储数据 java代码存储数据 java数据类型存储
上一篇: PolarDB-X 1.0-SQL 手册-SHOW-慢SQL相关
下一篇: 货拉拉技术副总监陈永庭：基于公共云的技术稳定性保障实践

继续阅读