Sharding
Syntax
Sharding Table Rule
SHOW SHARDING TABLE tableRule | RULES [FROM schemaName]
SHOW SHARDING ALGORITHMS [FROM schemaName]
SHOW UNUSED SHARDING ALGORITHMS [FROM schemaName]
SHOW SHARDING KEY GENERATORS [FROM schemaName]
SHOW UNUSED SHARDING KEY GENERATORS [FROM schemaName]
SHOW DEFAULT SHARDING STRATEGY
SHOW SHARDING TABLE NODES;
tableRule:
RULE tableName
- Support query all data fragmentation rules and specified table query
- Support query all sharding algorithms
Sharding Binding Table Rule
SHOW SHARDING BINDING TABLE RULES [FROM schemaName]
Sharding Broadcast Table Rule
SHOW SHARDING BROADCAST TABLE RULES [FROM schemaName]
Sharding Scaling Rule
SHOW SHARDING SCALING RULES [FROM schemaName]
Return Value Description
Sharding Table Rule
| Column |
Description |
| table |
Logical table name |
| actual_data_nodes |
Actual data node |
| actual_data_sources |
Actual data source (Displayed when creating rules by RDL) |
| database_strategy_type |
Database sharding strategy type |
| database_sharding_column |
Database sharding column |
| database_sharding_algorithm_type |
Database sharding algorithm type |
| database_sharding_algorithm_props |
Database sharding algorithm properties |
| table_strategy_type |
Table sharding strategy type |
| table_sharding_column |
Table sharding column |
| table_sharding_algorithm_type |
Table sharding algorithm type |
| table_sharding_algorithm_props |
Table sharding algorithm properties |
| key_generate_column |
Sharding key generator column |
| key_generator_type |
Sharding key generator type |
| key_generator_props |
Sharding key generator properties |
Sharding Algorithms
| Column |
Description |
| name |
Sharding algorithm name |
| type |
Sharding algorithm type |
| props |
Sharding algorithm properties |
Unused Sharding Algorithms
| Column |
Description |
| name |
Sharding algorithm name |
| type |
Sharding algorithm type |
| props |
Sharding algorithm properties |
Sharding key generators
| Column |
Description |
| name |
Sharding key generator name |
| type |
Sharding key generator type |
| props |
Sharding key generator properties |
Unused Sharding Key Generators
| Column |
Description |
| name |
Sharding key generator name |
| type |
Sharding key generator type |
| props |
Sharding key generator properties |
Default Sharding Strategy
| Column |
Description |
| name |
Strategy name |
| type |
Sharding strategy type |
| sharding_column |
Sharding column |
| sharding_algorithm_name |
Sharding algorithm name |
| sharding_algorithm_type |
Sharding algorithm type |
| sharding_algorithm_props |
Sharding algorithm properties |
Sharding Table Nodes
| Column |
Description |
| name |
Sharding rule name |
| nodes |
Sharding nodes |
Sharding Binding Table Rule
| Column |
Description |
| sharding_binding_tables |
sharding Binding Table list |
Sharding Broadcast Table Rule
| Column |
Description |
| sharding_broadcast_tables |
sharding Broadcast Table list |
Sharding Scaling Rule
| Column |
Description |
| name |
name of sharding scaling rule |
| input |
data read configuration |
| output |
data write configuration |
| stream_channel |
algorithm of stream channel |
| completion_detector |
algorithm of completion detecting |
| data_consistency_checker |
algorithm of data consistency checking |
Example
Sharding Table Rule
SHOW SHARDING TABLE RULES
mysql> SHOW SHARDING TABLE RULES;
+--------------+---------------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+---------------------------------------------------+-------------------+------------------+-------------------+
| 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 |
+--------------+---------------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+---------------------------------------------------+-------------------+------------------+-------------------+
| 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 | |
| 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 | |
| t2 | | ds_0,ds_1 | | | | | mod | id | mod | sharding-count:10 | | | |
+--------------+---------------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+---------------------------------------------------+-------------------+------------------+-------------------+
3 rows in set (0.02 sec)
SHOW SHARDING TABLE RULE tableName
mysql> SHOW SHARDING TABLE RULE t_order;
+---------+----------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+----------------------------------------------+-------------------+------------------+-------------------+
| 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 |
+---------+----------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+----------------------------------------------+-------------------+------------------+-------------------+
| 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 | |
+---------+----------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+----------------------------------------------+-------------------+------------------+-------------------+
1 row in set (0.01 sec)
SHOW SHARDING ALGORITHMS
mysql> SHOW SHARDING ALGORITHMS;
+-------------------------+--------+-----------------------------------------------------+
| name | type | props |
+-------------------------+--------+-----------------------------------------------------+
| t_order_inline | INLINE | algorithm-expression=t_order_${order_id % 2} |
| t_order_item_inline | INLINE | algorithm-expression=t_order_item_${order_id % 2} |
+-------------------------+--------+-----------------------------------------------------+
2 row in set (0.01 sec)
SHOW UNUSED SHARDING ALGORITHMS
mysql> SHOW UNUSED SHARDING ALGORITHMS;
+---------------+--------+-----------------------------------------------------+
| name | type | props |
+---------------+--------+-----------------------------------------------------+
| t1_inline | INLINE | algorithm-expression=t_order_${order_id % 2} |
+---------------+--------+-----------------------------------------------------+
1 row in set (0.01 sec)
SHOW SHARDING KEY GENERATORS
mysql> SHOW SHARDING KEY GENERATORS;
+------------------------+-----------+-----------------+
| name | type | props |
+------------------------+-----------+-----------------+
| t_order_snowflake | snowflake | |
| t_order_item_snowflake | snowflake | |
| uuid_key_generator | uuid | |
+------------------------+-----------+-----------------+
3 row in set (0.01 sec)
SHOW UNUSED SHARDING KEY GENERATORS
mysql> SHOW UNUSED SHARDING KEY GENERATORS;
+------------------------+-----------+-----------------+
| name | type | props |
+------------------------+-----------+-----------------+
| uuid_key_generator | uuid | |
+------------------------+-----------+-----------------+
1 row in set (0.01 sec)
SHOW DEFAULT SHARDING STRATEGY
mysql> SHOW DEFAULT SHARDING STRATEGY ;
+----------+---------+--------------------+-------------------------+-------------------------+------------------------------------------+
| name | type | sharding_column | sharding_algorithm_name | sharding_algorithm_type | sharding_algorithm_props |
+----------+---------+--------------------+-------------------------+-------------------------+------------------------------------------+
| TABLE | NONE | | | | |
| DATABASE | STANDARD| order_id | database_inline | INLINE | {algorithm-expression=ds_${user_id % 2}} |
+----------+---------+--------------------+-------------------------+-------------------------+------------------------------------------+
2 rows in set (0.07 sec)
SHOW SHARDING TABLE NODES
mysql> SHOW SHARDING TABLE NODES;
+---------+----------------------------------------------------------------+
| name | nodes |
+---------+----------------------------------------------------------------+
| t_order | ds_0.t_order_0, ds_1.t_order_1, ds_0.t_order_2, ds_1.t_order_3 |
+---------+----------------------------------------------------------------+
1 row in set (0.02 sec)
Sharding Binding Table Rule
mysql> SHOW SHARDING BINDING TABLE RULES;
+----------------------+
| sharding_binding_tables |
+----------------------+
| t_order,t_order_item |
| t1,t2 |
+----------------------+
2 rows in set (0.00 sec)
Sharding Broadcast Table Rule
mysql> SHOW SHARDING BROADCAST TABLE RULES;
+------------------------+
| sharding_broadcast_tables |
+------------------------+
| t_1 |
| t_2 |
+------------------------+
2 rows in set (0.00 sec)
Sharding Scaling Rule
mysql> SHOW SHARDING SCALING RULES;
+------------------+----------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+--------------------------------------------------------+-------------------------------------------------------------------------+-----------------------------------------------------+
| name | input | output | stream_channel | completion_detector | data_consistency_checker |
+------------------+----------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+--------------------------------------------------------+-------------------------------------------------------------------------+-----------------------------------------------------+
| sharding_scaling | {"workerThread":40,"batchSize":1000,"rateLimiter":{"type":"QPS","props":{"qps":"50"}}} | {"workerThread":40,"batchSize":1000,"rateLimiter":{"type":"TPS","props":{"tps":"2000"}}} | {"type":"MEMORY","props":{"block-queue-size":"10000"}} | {"type":"IDLE","props":{"incremental-task-idle-minute-threshold":"30"}} | {"type":"DATA_MATCH","props":{"chunk-size":"1000"}} |
+------------------+----------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+--------------------------------------------------------+-------------------------------------------------------------------------+-----------------------------------------------------+
1 row in set (0.00 sec)