数据分片

定义

Sharding Table Rule

  1. CREATE SHARDING TABLE RULE shardingTableRuleDefinition [, shardingTableRuleDefinition] ...
  3. CREATE DEFAULT SHARDING shardingScope STRATEGY (shardingStrategy)
  5. ALTER SHARDING TABLE RULE shardingTableRuleDefinition [, shardingTableRuleDefinition] ...
  7. DROP SHARDING TABLE RULE tableName [, tableName] ...
  9. CREATE SHARDING ALGORITHM shardingAlgorithmDefinition [,  shardingAlgorithmDefinition] ...
  11. DROP SHARDING ALGORITHM algorithmName [, algorithmName] ...
  13. shardingTableRuleDefinition:
  14.     shardingAutoTableRule | shardingTableRule
  16. shardingAutoTableRule:
  17.     tableName(resources (COMMA shardingColumn)? (COMMA algorithmDefinition)? (COMMA keyGenerateStrategy)?)
  19. shardingTableRule:
  20.     tableName(dataNodes (COMMA  databaseStrategy)? (COMMA tableStrategy)? (COMMA keyGenerateStrategy)?)
  22. resources:
  23.     RESOURCES(resource [, resource] ...)
  25. dataNodes:
  26.     DATANODES(dataNode [, dataNode] ...)
  28. resource:
  29.     resourceName | inlineExpression
  31. dataNode:
  32.     resourceName | inlineExpression
  34. shardingColumn:
  35.     SHARDING_COLUMN=columnName
  37. algorithmDefinition:
  38.     TYPE(NAME=shardingAlgorithmType [, PROPERTIES([algorithmProperties])])
  40. keyGenerateStrategy:
  41.     GENERATED_KEY(COLUMN=columnName, strategyDefinition)
  43. shardingScope:
  44.     DATABASE | TABLE
  46. databaseStrategy:
  47.     DATABASE_STRATEGY(shardingStrategy)
  49. tableStrategy:
  50.     TABLE_STRATEGY(shardingStrategy)
  52. shardingStrategy:
  53.     TYPE=strategyType, shardingColumn, shardingAlgorithm
  55. shardingColumn:
  56.     SHARDING_COLUMN=columnName
  58. shardingAlgorithm:
  59.     SHARDING_ALGORITHM=shardingAlgorithmName
  61. strategyDefinition:
  62.     TYPE(NAME=keyGenerateStrategyType [, PROPERTIES([algorithmProperties])])
  64. shardingAlgorithmDefinition:
  65.     shardingAlgorithmName(algorithmDefinition)
  67. algorithmProperties:
  68.     algorithmProperty [, algorithmProperty] ...
  70. algorithmProperty:
  71.     key=value
  • RESOURCES 需使用 RDL 管理的数据源资源
  • shardingAlgorithmType 指定自动分片算法类型，请参考 自动分片算法
  • keyGenerateStrategyType 指定分布式主键生成策略，请参考 分布式主键
  • 重复的 tableName 将无法被创建
  • shardingAlgorithm 能够被不同的 Sharding Table Rule 复用，因此在执行 DROP SHARDING TABLE RULE 时，对应的 shardingAlgorithm 不会被移除
  • 如需移除 shardingAlgorithm，请执行 DROP SHARDING ALGORITHM
  • strategyType 指定分片策略，请参考分片策略

Sharding Binding Table Rule

  1. CREATE SHARDING BINDING TABLE RULES bindTableRulesDefinition [, bindTableRulesDefinition] ...
  3. ALTER SHARDING BINDING TABLE RULES bindTableRulesDefinition [, bindTableRulesDefinition] ...
  5. DROP SHARDING BINDING TABLE RULES bindTableRulesDefinition [, bindTableRulesDefinition] ...
  7. bindTableRulesDefinition:
  8.     (tableName [, tableName] ... )
  • ALTER 会使用新的配置直接覆盖数据库内的绑定表配置

Sharding Broadcast Table Rule

  1. CREATE SHARDING BROADCAST TABLE RULES (tableName [, tableName] ...)
  3. ALTER SHARDING BROADCAST TABLE RULES (tableName [, tableName] ...)
  5. DROP SHARDING BROADCAST TABLE RULES (tableName [, tableName] ...)
  • ALTER 会使用新的配置直接覆盖数据库内的广播表配置

示例

Sharding Table Rule

  1. CREATE SHARDING TABLE RULE t_order (
  2. RESOURCES(resource_0,resource_1),
  3. SHARDING_COLUMN=order_id,
  4. TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=4)),
  5. GENERATED_KEY(COLUMN=another_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
  6. ),t_order_item (
  7. DATANODES("resource_${0..1}.t_order${0..1}"),
  8. DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline),
  9. TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=database_inline),
  10. GENERATED_KEY(COLUMN=another_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
  11. );
  13. ALTER SHARDING TABLE RULE t_order (
  14. RESOURCES(resource_0,resource_1),
  15. SHARDING_COLUMN=order_id,
  16. TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=10)),
  17. GENERATED_KEY(COLUMN=another_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
  18. ),t_order_item (
  19. DATANODES("resource_0.t_order${0..1}"),
  20. DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline),
  21. TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=database_inline),
  22. GENERATED_KEY(COLUMN=another_id,TYPE(NAME=uuid,PROPERTIES("worker-id"=123)))
  23. );
  25. DROP SHARDING TABLE RULE t_order, t_order_item;
  27. CREATE DEFAULT SHARDING DATABASE STRATEGY (
  28. TYPE = standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=algorithmsName
  29. );
  31. CREATE SHARDING ALGORITHM algorithmName (
  32. TYPE(NAME=hash_mod,PROPERTIES("algorithm-expression"="t_order_${order_id % 2}"))
  33. );
  35. DROP SHARDING ALGORITHM t_order_hash_mod;

Sharding Binding Table Rule

  1. CREATE SHARDING BINDING TABLE RULES (t_order,t_order_item),(t_1,t_2);
  3. ALTER SHARDING BINDING TABLE RULES (t_order,t_order_item);
  5. DROP SHARDING BINDING TABLE RULES;
  7. DROP SHARDING BINDING TABLE RULES (t_order,t_order_item);

Sharding Broadcast Table Rule

  1. CREATE SHARDING BROADCAST TABLE RULES (t_b,t_a);
  3. ALTER SHARDING BROADCAST TABLE RULES (t_b,t_a,t_3);
  5. DROP SHARDING BROADCAST TABLE RULES;
  7. DROP SHARDING BROADCAST TABLE RULES t_b;