Sharding

Syntax

Sharding Table Rule

  1. SHOW SHARDING TABLE tableRule | RULES [FROM databaseName]
  3. SHOW SHARDING ALGORITHMS [FROM databaseName]
  5. SHOW UNUSED SHARDING ALGORITHMS [FROM databaseName]
  7. SHOW SHARDING AUDITORS [FROM databaseName]
  9. SHOW SHARDING TABLE RULES USED ALGORITHM shardingAlgorithmName [FROM databaseName]
  11. SHOW SHARDING KEY GENERATORS [FROM databaseName]
  13. SHOW UNUSED SHARDING KEY GENERATORS [FROM databaseName]
  15. SHOW UNUSED SHARDING AUDITORS [FROM databaseName]
  17. SHOW SHARDING TABLE RULES USED KEY GENERATOR keyGeneratorName [FROM databaseName]
  19. SHOW SHARDING TABLE RULES USED AUDITOR auditorName [FROM databaseName]
  21. SHOW DEFAULT SHARDING STRATEGY 
  23. SHOW SHARDING TABLE NODES
  25. tableRule:
  26.     RULE tableName
  • Support query all data fragmentation rules and specified table query
  • Support query all sharding algorithms
  • Support query all sharding audit algorithms

Sharding Table Reference Rule

  1. SHOW SHARDING TABLE REFERENCE RULES [FROM databaseName]

Broadcast Table Rule

  1. SHOW BROADCAST TABLE RULES [FROM databaseName]

Sharding Table Rule

ColumnDescription
tableLogical table name
actual_data_nodesActual data node
actual_data_sourcesActual data source (Displayed when creating rules by RDL)
database_strategy_typeDatabase sharding strategy type
database_sharding_columnDatabase sharding column
database_sharding_algorithm_typeDatabase sharding algorithm type
database_sharding_algorithm_propsDatabase sharding algorithm properties
table_strategy_typeTable sharding strategy type
table_sharding_columnTable sharding column
table_sharding_algorithm_typeTable sharding algorithm type
table_sharding_algorithm_propsTable sharding algorithm properties
key_generate_columnSharding key generator column
key_generator_typeSharding key generator type
key_generator_propsSharding key generator properties
auditor_typesSharding auditor types
allow_hint_disableEnable or disable sharding audit hint

Sharding Algorithms

ColumnDescription
nameSharding algorithm name
typeSharding algorithm type
propsSharding algorithm properties

Unused Sharding Algorithms

ColumnDescription
nameSharding algorithm name
typeSharding algorithm type
propsSharding algorithm properties

Sharding auditors

ColumnDescription
nameSharding audit algorithm name
typeSharding audit algorithm type
propsSharding audit algorithm properties

Unused Sharding Auditors

ColumnDescription
nameSharding audit algorithm name
typeSharding audit algorithm type
propsSharding audit algorithm properties

Sharding key generators

ColumnDescription
nameSharding key generator name
typeSharding key generator type
propsSharding key generator properties

Unused Sharding Key Generators

ColumnDescription
nameSharding key generator name
typeSharding key generator type
propsSharding key generator properties

Default Sharding Strategy

ColumnDescription
nameStrategy name
typeSharding strategy type
sharding_columnSharding column
sharding_algorithm_nameSharding algorithm name
sharding_algorithm_typeSharding algorithm type
sharding_algorithm_propsSharding algorithm properties

Sharding Table Nodes

ColumnDescription
nameSharding rule name
nodesSharding nodes

Sharding Table Reference Rule

ColumnDescription
nameSharding table reference rule name
sharding_table_referenceSharding table reference

Broadcast Table Rule

ColumnDescription
broadcast_tableBroadcast table

Sharding Table Rule

SHOW SHARDING TABLE RULES

  1. mysql> SHOW SHARDING TABLE RULES;
  2. +--------------+---------------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+---------------------------------------------------+-------------------+------------------+-------------------+
  3. | 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 |auditor_types | allow_hint_disable |
  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        |                   |DML_SHARDING_CONDITIONS    |true      |
  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        |                   |                           |          |
  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   | 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 | auditor_types | allow_hint_disable |
  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        |                   | DML_SHARDING_CONDITIONS    |true      |
  6. +---------+----------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+----------------------------------------------+-------------------+------------------+-------------------+
  7. 1 row in set (0.01 sec)

SHOW SHARDING ALGORITHMS

  1. mysql> SHOW SHARDING ALGORITHMS;
  2. +-------------------------+--------+-----------------------------------------------------+
  3. | name                    | type   | props                                               |
  4. +-------------------------+--------+-----------------------------------------------------+
  5. | t_order_inline          | INLINE | algorithm-expression=t_order_${order_id % 2}        |
  6. | t_order_item_inline     | INLINE | algorithm-expression=t_order_item_${order_id % 2}   |
  7. +-------------------------+--------+-----------------------------------------------------+
  8. 2 row in set (0.01 sec)

SHOW UNUSED SHARDING ALGORITHMS

  1. mysql> SHOW UNUSED SHARDING ALGORITHMS;
  2. +---------------+--------+-----------------------------------------------------+
  3. | name          | type   | props                                               |
  4. +---------------+--------+-----------------------------------------------------+
  5. | t1_inline     | INLINE | algorithm-expression=t_order_${order_id % 2}        |
  6. +---------------+--------+-----------------------------------------------------+
  7. 1 row in set (0.01 sec)

SHOW SHARDING AUDITORS

  1. mysql> SHOW SHARDING AUDITORS;
  2. +------------+-------------------------+-------+
  3. | name       | type                    | props |
  4. +------------+-------------------------+-------+
  5. | dml_audit  | DML_SHARDING_CONDITIONS |       |
  6. +------------+-------------------------+-------+
  7. 2 row in set (0.01 sec)

SHOW SHARDING TABLE RULES USED ALGORITHM shardingAlgorithmName

  1. mysql> SHOW SHARDING TABLE RULES USED ALGORITHM t_order_inline;
  2. +-------+---------+
  3. | type  | name    |
  4. +-------+---------+
  5. | table | t_order |
  6. +-------+---------+
  7. 1 row in set (0.01 sec)

SHOW SHARDING KEY GENERATORS

  1. mysql> SHOW SHARDING KEY GENERATORS;
  2. +------------------------+-----------+-----------------+
  3. | name                   | type      | props           |
  4. +------------------------+-----------+-----------------+
  5. | t_order_snowflake      | snowflake |                 |
  6. | t_order_item_snowflake | snowflake |                 |
  7. | uuid_key_generator     | uuid      |                 |
  8. +------------------------+-----------+-----------------+
  9. 3 row in set (0.01 sec)

SHOW UNUSED SHARDING KEY GENERATORS

  1. mysql> SHOW UNUSED SHARDING KEY GENERATORS;
  2. +------------------------+-----------+-----------------+
  3. | name                   | type      | props           |
  4. +------------------------+-----------+-----------------+
  5. | uuid_key_generator     | uuid      |                 |
  6. +------------------------+-----------+-----------------+
  7. 1 row in set (0.01 sec)

SHOW UNUSED SHARDING KEY AUDITORS

  1. mysql> SHOW UNUSED SHARDING KEY AUDITORS;
  2. +------------+-------------------------+-------+
  3. | name       | type                    | props |
  4. +------------+-------------------------+-------+
  5. | dml_audit  | DML_SHARDING_CONDITIONS |       |
  6. +------------+-------------------------+-------+
  7. 1 row in set (0.01 sec)

SHOW SHARDING TABLE RULES USED KEY GENERATOR keyGeneratorName

  1. mysql> SHOW SHARDING TABLE RULES USED KEY GENERATOR keyGeneratorName;
  2. +-------+---------+
  3. | type  | name    |
  4. +-------+---------+
  5. | table | t_order |
  6. +-------+---------+
  7. 1 row in set (0.01 sec)

SHOW SHARDING TABLE RULES USED AUDITOR auditorName

  1. mysql> SHOW SHARDING TABLE RULES USED AUDITOR sharding_key_required;
  2. +-------+---------+
  3. | type  | name    |
  4. +-------+---------+
  5. | table | t_order |
  6. +-------+---------+
  7. 1 row in set (0.01 sec)

SHOW DEFAULT SHARDING STRATEGY

  1. mysql> SHOW DEFAULT SHARDING STRATEGY ;
  3. +----------+---------+--------------------+-------------------------+-------------------------+------------------------------------------+
  4. | name     | type    | sharding_column    | sharding_algorithm_name | sharding_algorithm_type | sharding_algorithm_props                 |
  5. +----------+---------+--------------------+-------------------------+-------------------------+------------------------------------------+
  6. | TABLE    | NONE    |                    |                         |                         |                                          |
  7. | DATABASE | STANDARD| order_id           | database_inline         | INLINE                  | {algorithm-expression=ds_${user_id % 2}} |
  8. +----------+---------+--------------------+-------------------------+-------------------------+------------------------------------------+
  9. 2 rows in set (0.07 sec)

SHOW SHARDING TABLE NODES

  1. mysql> SHOW SHARDING TABLE NODES;
  2. +---------+----------------------------------------------------------------+
  3. | name    | nodes                                                          |
  4. +---------+----------------------------------------------------------------+
  5. | t_order | ds_0.t_order_0, ds_1.t_order_1, ds_0.t_order_2, ds_1.t_order_3 |
  6. +---------+----------------------------------------------------------------+
  7. 1 row in set (0.02 sec)

Sharding Table Reference Rule

  1. mysql> SHOW SHARDING TABLE REFERENCE RULES;
  2. +-------+--------------------------+
  3. | name  | sharding_table_reference |
  4. +-------+--------------------------+
  5. | ref_0 | t_a,t_b                  |
  6. | ref_1 | t_c,t_d                  |
  7. +-------+--------------------------+
  8. 2 rows in set (0.00 sec)

Broadcast Table Rule

  1. mysql> SHOW BROADCAST TABLE RULES;
  2. +-----------------------+
  3. | broadcast_table       |
  4. +-----------------------+
  5. | t_1                   |
  6. | t_2                   |
  7. +-----------------------+
  8. 2 rows in set (0.00 sec)