Sharding

Definition

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 needs to use data source resources managed by RDL
  • shardingAlgorithmType specifies the type of automatic sharding algorithm, please refer to Auto Sharding Algorithm
  • keyGenerateStrategyType specifies the distributed primary key generation strategy, please refer to Key Generate Algorithm
  • Duplicate tableName will not be created
  • shardingAlgorithm can be reused by different Sharding Table Rule, so when executing DROP SHARDING TABLE RULE, the corresponding shardingAlgorithm will not be removed
  • To remove shardingAlgorithm, please execute DROP SHARDING ALGORITHM
  • strategyType specifies the sharding strategy，please refer toSharding Strategy

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 will overwrite the binding table configuration in the database with the new configuration

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
  • ALTER will overwrite the broadcast table configuration in the database with the new configuration

Example

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;