Sharding

Syntax

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. ALTER SHARDING ALGORITHM shardingAlgorithmDefinition [,  shardingAlgorithmDefinition] ...
  13. DROP SHARDING ALGORITHM algorithmName [, algorithmName] ...
  15. shardingTableRuleDefinition:
  16.     shardingAutoTableRule | shardingTableRule
  18. shardingAutoTableRule:
  19.     tableName(resources COMMA shardingColumn COMMA algorithmDefinition (COMMA keyGenerateDeclaration)?)
  21. shardingTableRule:
  22.     tableName(dataNodes (COMMA  databaseStrategy)? (COMMA tableStrategy)? (COMMA keyGenerateDeclaration)?)
  24. resources:
  25.     RESOURCES(resource [, resource] ...)
  27. dataNodes:
  28.     DATANODES(dataNode [, dataNode] ...)
  30. resource:
  31.     resourceName | inlineExpression
  33. dataNode:
  34.     resourceName | inlineExpression
  36. shardingColumn:
  37.     SHARDING_COLUMN=columnName
  39. algorithmDefinition:
  40.     TYPE(NAME=shardingAlgorithmType [, PROPERTIES([algorithmProperties])])
  42. keyGenerateDeclaration:
  43.     keyGenerateDefinition | keyGenerateConstruction
  45. keyGenerateDefinition:
  46.     GENERATED_KEY(COLUMN=columnName, strategyDefinition)
  48. shardingScope:
  49.     DATABASE | TABLE
  51. databaseStrategy:
  52.     DATABASE_STRATEGY(shardingStrategy)
  54. tableStrategy:
  55.     TABLE_STRATEGY(shardingStrategy)
  57. keyGenerateConstruction
  58.     GENERATED_KEY(COLUMN=columnName, GENERATED_KEY_ALGORITHM=keyGenerateAlgorithmName)
  60. shardingStrategy:
  61.     TYPE=strategyType, shardingColumn, shardingAlgorithm
  63. shardingColumn:
  64.     SHARDING_COLUMN=columnName
  66. shardingAlgorithm:
  67.     existingAlgorithm | autoCreativeAlgorithm
  69. existingAlgorithm:
  70.     SHARDING_ALGORITHM=shardingAlgorithmName
  72. autoCreativeAlgorithm:
  73.     SHARDING_ALGORITHM(algorithmDefinition)
  75. strategyDefinition:
  76.     TYPE(NAME=keyGenerateStrategyType [, PROPERTIES([algorithmProperties])])
  78. shardingAlgorithmDefinition:
  79.     shardingAlgorithmName(algorithmDefinition)
  81. algorithmProperties:
  82.     algorithmProperty [, algorithmProperty] ...
  84. algorithmProperty:
  85.     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 Table Rule supports both Auto Table and Table at the same time. The two types are different in syntax. For the corresponding configuration file, please refer to Sharding
  • When using the autoCreativeAlgorithm way to specify shardingStrategy, a new sharding algorithm will be created automatically. The algorithm naming rule is tableName_strategyType_shardingAlgorithmType, such as t_order_database_inline

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

Key Generator

  1. CREATE SHARDING KEY GENERATOR snowflake_key_generator (
  2. TYPE(NAME=SNOWFLAKE, PROPERTIES("worker-id"=123))
  3. );
  5. ALTER SHARDING KEY GENERATOR snowflake_key_generator (
  6. TYPE(NAME=SNOWFLAKE, PROPERTIES("worker-id"=456))
  7. );
  9. DROP SHARDING KEY GENERATOR snowflake_key_generator;

Auto Table

  1. CREATE SHARDING TABLE RULE t_order (
  2. RESOURCES(resource_0,resource_1),
  3. SHARDING_COLUMN=order_id,TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=4)),
  4. GENERATED_KEY(COLUMN=another_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
  5. );
  7. ALTER SHARDING TABLE RULE t_order (
  8. RESOURCES(resource_0,resource_1,resource_2,resource_3),
  9. SHARDING_COLUMN=order_id,TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=16)),
  10. GENERATED_KEY(COLUMN=another_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
  11. );
  13. DROP SHARDING TABLE RULE t_order;
  15. DROP SHARDING ALGORITHM t_order_hash_mod;

Table

  1. CREATE SHARDING ALGORITHM table_inline (
  2. TYPE(NAME=inline,PROPERTIES("algorithm-expression"="t_order_item_${order_id % 2}"))
  3. );
  5. CREATE SHARDING TABLE RULE t_order_item (
  6. DATANODES("resource_${0..1}.t_order_item_${0..1}"),
  7. DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME=inline,PROPERTIES("algorithm-expression"="resource_${user_id % 2}")))),
  8. TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=table_inline),
  9. GENERATED_KEY(COLUMN=another_id,GENERATED_KEY_ALGORITHM=snowflake_key_generator)
  10. );
  12. ALTER SHARDING ALGORITHM database_inline (
  13. TYPE(NAME=inline,PROPERTIES("algorithm-expression"="resource_${user_id % 4}"))
  14. ),table_inline (
  15. TYPE(NAME=inline,PROPERTIES("algorithm-expression"="t_order_item_${order_id % 4}"))
  16. );
  18. ALTER SHARDING TABLE RULE t_order_item (
  19. DATANODES("resource_${0..3}.t_order_item${0..3}"),
  20. DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline),
  21. TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=table_inline),
  22. GENERATED_KEY(COLUMN=another_id,GENERATED_KEY_ALGORITHM=snowflake_key_generator)
  23. );
  25. DROP SHARDING TABLE RULE t_order_item;
  27. DROP SHARDING ALGORITHM database_inline;
  29. CREATE DEFAULT SHARDING DATABASE STRATEGY (
  30. TYPE = standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=algorithmsName
  31. );

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;