Sharding

Syntax

Sharding Table Rule

  1. SHOW SHARDING TABLE tableRule | RULES [FROM schemaName]
  3. SHOW SHARDING ALGORITHMS [FROM schemaName]
  5. SHOW SHARDING TABLE NODES;
  7. tableRule:
  8.     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]

Return Value 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 Table Nodes

ColumnDescription
nameSharding rule name
nodesSharding nodes

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)

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 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)