数据分片

定义

Sharding Table Rule

  1. SHOW SHARDING TABLE tableRule | RULES [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逻辑表名
actualDataNodes实际的数据节点
actualDataSources实际的数据源（通过 RDL 创建的规则时显示）
databaseStrategyType数据库分片策略类型
databaseShardingColumn数据库分片键
databaseShardingAlgorithmType数据库分片算法类型
databaseShardingAlgorithmProps数据库分片算法参数
tableStrategyType表分片策略类型
tableShardingColumn表分片键
tableShardingAlgorithmType表分片算法类型
tableShardingAlgorithmProps表分片算法参数
keyGenerateColumn分布式主键生成列
keyGeneratorType分布式主键生成器类型
keyGeneratorProps分布式主键生成器参数

Sharding Binding Table Rule

列说明
shardingBindingTables绑定表名称

Sharding Broadcast Table Rule

列说明
shardingBroadcastTables广播表名称

示例

Sharding Table Rule

SHOW SHARDING TABLE RULES

  1. mysql> show sharding table rules;
  2. +--------------+---------------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+---------------------------------------------------+-------------------+------------------+-------------------+
  3. | table        | actualDataNodes                 | actualDataSources | databaseStrategyType | databaseShardingColumn | databaseShardingAlgorithmType | databaseShardingAlgorithmProps         | tableStrategyType | tableShardingColumn | tableShardingAlgorithmType | tableShardingAlgorithmProps                       | keyGenerateColumn | keyGeneratorType | keyGeneratorProps |
  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   | actualDataNodes            | actualDataSources | databaseStrategyType | databaseShardingColumn | databaseShardingAlgorithmType | databaseShardingAlgorithmProps         | tableStrategyType | tableShardingColumn | tableShardingAlgorithmType | tableShardingAlgorithmProps                  | keyGenerateColumn | keyGeneratorType | keyGeneratorProps |
  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)

Sharding Binding Table Rule

  1. mysql> show sharding binding table rules from sharding_db;
  2. +----------------------+
  3. | shardingBindingTables |
  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. | shardingBroadcastTables |
  4. +------------------------+
  5. | t_1                    |
  6. | t_2                    |
  7. +------------------------+
  8. 2 rows in set (0.00 sec)