Sharding

Definition

Sharding Table Rule

  1. SHOW SHARDING TABLE tableRule | RULES [FROM schemaName]
  3. SHOW SHARDING ALGORITHMS [FROM schemaName]
  5. tableRule:
  6.     RULE tableName
  • Support query all data fragmentation rules and specified table query
  • Support query all sharding algorithms

Sharding Binding Table Rule

  1. SHOW SHARDING BINDING TABLE RULES [FROM schemaName]

Sharding Broadcast Table Rule

  1. SHOW SHARDING BROADCAST TABLE RULES [FROM schemaName]

Description

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 parameter
table_strategy_typeTable sharding strategy type
table_sharding_columnTable sharding column
table_sharding_algorithm_typeDatabase sharding algorithm type
table_sharding_algorithm_propsDatabase sharding algorithm parameter
key_generate_columnDistributed primary key generation column
key_generator_typeDistributed primary key generation type
key_generator_propsDistributed primary key generation parameter

Sharding Algorithms

ColumnDescription
nameSharding algorithm name
typeSharding algorithm type
propsSharding algorithm parameters

Sharding Binding Table Rule

ColumnDescription
sharding_binding_tablessharding Binding Table list

Sharding Broadcast Table Rule

ColumnDescription
sharding_broadcast_tablessharding Broadcast Table list

Example

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 |
  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        | worker-id:123     |
  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        | worker-id:123     |
  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 |
  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        | worker-id:123     |
  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)

Sharding Binding Table Rule

  1. mysql> show sharding binding table rules from sharding_db;
  2. +----------------------+
  3. | sharding_binding_tables |
  4. +----------------------+
  5. | t_order,t_order_item |
  6. | t1,t2                |
  7. +----------------------+
  8. 2 rows in set (0.00 sec)

Sharding Broadcast Table Rule

  1. mysql> show sharding broadcast table rules;
  2. +------------------------+
  3. | sharding_broadcast_tables |
  4. +------------------------+
  5. | t_1                    |
  6. | t_2                    |
  7. +------------------------+
  8. 2 rows in set (0.00 sec)