数据分片

定义

Sharding Table Rule

  1. SHOW SHARDING TABLE tableRule | RULES [FROM schemaName]
  2. SHOW SHARDING ALGORITHMS [FROM schemaName]
  3. tableRule:
  4. RULE tableName
  • 支持查询所有数据分片规则和指定表查询
  • 支持查询所有分片算法

Sharding Binding Table Rule

  1. SHOW SHARDING BINDING TABLE RULES [FROM schemaName]

Sharding Broadcast Table Rule

  1. SHOW SHARDING BROADCAST TABLE RULES [FROM schemaName]

说明

Sharding Table Rule

说明
table逻辑表名
actual_data_nodes实际的数据节点
actual_data_sources实际的数据源(通过 RDL 创建的规则时显示)
database_strategy_type数据库分片策略类型
database_sharding_column数据库分片键
database_sharding_algorithm_type数据库分片算法类型
database_sharding_algorithm_props数据库分片算法参数
table_strategy_type表分片策略类型
table_sharding_column表分片键
table_sharding_algorithm_type表分片算法类型
table_sharding_algorithm_props表分片算法参数
key_generate_column分布式主键生成列
key_generator_type分布式主键生成器类型
key_generator_props分布式主键生成器参数

Sharding Algorithms

说明
name分片算法名称
type分片算法类型
props分片算法参数

Sharding Binding Table Rule

说明
sharding_binding_tables绑定表名称

Sharding Broadcast Table Rule

说明
sharding_broadcast_tables广播表名称

示例

Sharding Table Rule

SHOW SHARDING TABLE RULES

  1. mysql> show sharding table rules;
  2. +--------------+---------------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+---------------------------------------------------+-------------------+------------------+-------------------+
  3. | table | actual_data_nodes | actual_data_sources | database_strategy_type | database_sharding_column | database_sharding_algorithm_type | database_sharding_algorithm_props | table_strategy_type | table_sharding_column | table_sharding_algorithm_type | table_sharding_algorithm_props | key_generate_column | key_generator_type | key_generator_props |
  4. +--------------+---------------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+---------------------------------------------------+-------------------+------------------+-------------------+
  5. | t_order | ds_${0..1}.t_order_${0..1} | | INLINE | user_id | INLINE | algorithm-expression:ds_${user_id % 2} | INLINE | order_id | INLINE | algorithm-expression:t_order_${order_id % 2} | order_id | SNOWFLAKE | worker-id:123 |
  6. | t_order_item | ds_${0..1}.t_order_item_${0..1} | | INLINE | user_id | INLINE | algorithm-expression:ds_${user_id % 2} | INLINE | order_id | INLINE | algorithm-expression:t_order_item_${order_id % 2} | order_item_id | SNOWFLAKE | worker-id:123 |
  7. | t2 | | ds_0,ds_1 | | | | | mod | id | mod | sharding-count:10 | | | |
  8. +--------------+---------------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+---------------------------------------------------+-------------------+------------------+-------------------+
  9. 3 rows in set (0.02 sec)

SHOW SHARDING TABLE RULE tableName

  1. mysql> show sharding table rule t_order;
  2. +---------+----------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+----------------------------------------------+-------------------+------------------+-------------------+
  3. | table | actual_data_nodes | actual_data_sources | database_strategy_type | database_sharding_column | database_sharding_algorithm_type | database_sharding_algorithm_props | table_strategy_type | table_sharding_column | table_sharding_algorithm_type | table_sharding_algorithm_props | key_generate_column | key_generator_type | key_generator_props |
  4. +---------+----------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+----------------------------------------------+-------------------+------------------+-------------------+
  5. | t_order | ds_${0..1}.t_order_${0..1} | | INLINE | user_id | INLINE | algorithm-expression:ds_${user_id % 2} | INLINE | order_id | INLINE | algorithm-expression:t_order_${order_id % 2} | order_id | SNOWFLAKE | worker-id:123 |
  6. +---------+----------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+----------------------------------------------+-------------------+------------------+-------------------+
  7. 1 row in set (0.01 sec)

SHOW SHARDING ALGORITHMS

  1. mysql> show sharding algorithms;
  2. +-------------------------+--------+-----------------------------------------------------+
  3. | name | type | props |
  4. +-------------------------+--------------------------------------------------------------+
  5. | t_order_inline | INLINE | algorithm-expression=t_order_${order_id % 2} |
  6. | t_order_item_inline | INLINE | algorithm-expression=t_order_item_${order_id % 2} |
  7. +-------------------------+--------+-----------------------------------------------------+
  8. 2 row in set (0.01 sec)

Sharding Binding Table Rule

  1. mysql> show sharding binding table rules from sharding_db;
  2. +----------------------+
  3. | sharding_binding_tables |
  4. +----------------------+
  5. | t_order,t_order_item |
  6. | t1,t2 |
  7. +----------------------+
  8. 2 rows in set (0.00 sec)

Sharding Broadcast Table Rule

  1. mysql> show sharding broadcast table rules;
  2. +------------------------+
  3. | sharding_broadcast_tables |
  4. +------------------------+
  5. | t_1 |
  6. | t_2 |
  7. +------------------------+
  8. 2 rows in set (0.00 sec)