Logo
分片表

分片表 #

语法描述类型
CREATE SHARDING TABLE RULE shardingTableRuleDefinition [, shardingTableRuleDefinition] …创建分片规则RDL
ALTER SHARDING TABLE RULE shardingTableRuleDefinition [, shardingTableRuleDefinition] …修改分片规则RDL
DROP SHARDING TABLE RULE tableName [, tableName] …删除分片规则RDL
SHOW SHARDING TABLE tableRuleRULES [FROM databaseName]查看分片规则
SHOW SHARDING TABLE NODES查看逻辑库中分片表所在的分片规则RQL
COUNT SHARDING RULE统计分片规则的数量，在后续版本中将废弃此语法RQL
DROP SHARDING ALGORITHM algorithmName [, algorithmName] …单独删除分片算法 无法删除使用中的算法RDL
SHOW SHARDING ALGORITHMS [FROM databaseName]查看分片算法详情RQL
SHOW UNUSED SHARDING ALGORITHMS [FROM databaseName]查看未被使用的分片算法RQL
SHOW SHARDING TABLE RULES USED ALGORITHM shardingAlgorithmName [FROM databaseName]查看使用指定分片算法的表RQL
DROP SHARDING KEY GENERATOR [IF EXISTS] keyGeneratorName [, keyGeneratorName] …删除分布式主键生成算法RDL
SHOW SHARDING KEY GENERATORS [FROM databaseName]查看分布式主键生成算法SHOW UNUSED SHARDING KEY GENERATORS [FROM databaseName]
SHOW SHARDING TABLE RULES USED KEY GENERATOR keyGeneratorName [FROM databaseName]查看使用指定算法的表RQL
CREATE DEFAULT SHARDING shardingScope STRATEGY (shardingStrategy)创建缺省的分库分表策略RDL
ALTER DEFAULT SHARDING shardingScope STRATEGY (shardingStrategy)修改缺省的分库分表策略RDL
DROP DEFAULT SHARDING shardingScope STRATEGY;删除缺省的分库分表策略RDL
SHOW DEFAULT SHARDING STRATEGY查看缺省的分库分表策略的详情RQL

1. 创建分片规则 #

CREATE SHARDING TABLE RULE shardingTableRuleDefinition [, shardingTableRuleDefinition] ...
shardingTableRuleDefinition:
    shardingAutoTableRule | shardingTableRule

shardingAutoTableRule:
    tableName(storageUnits, shardingColumn, algorithmDefinition [, keyGenerateDefinition] [, auditDeclaration])

shardingTableRule:
    tableName(dataNodes [, databaseStrategy] [, tableStrategy] [, keyGenerateDefinition] [, auditDeclaration])

storageUnits:
    STORAGE_UNITS(storageUnit [, storageUnit] ...)

dataNodes:
    DATANODES(dataNode [, dataNode] ...)

storageUnit:
    storageUnitName | inlineExpression

dataNode:
    dataNodeName | inlineExpression

shardingColumn:
    SHARDING_COLUMN=columnName

algorithmDefinition:
    TYPE(NAME=shardingAlgorithmType [, PROPERTIES([algorithmProperties])])

keyGenerateDefinition:
    KEY_GENERATE_STRATEGY(COLUMN=columnName, strategyDefinition)
    
auditDeclaration:
    auditDefinition | auditStrategy

auditDefinition:
    AUDIT_STRATEGY([(singleAuditDefinition),(singleAuditDefinition)], ALLOW_HINT_DISABLE=true)
    
singleAuditDefinition:
    NAME=auditor1, algorithmDefinition
    
auditStrategy:
    AUDIT_STRATEGY(AUDITORS=[auditor1,auditor2], ALLOW_HINT_DISABLE=true)

参数说明

  • STORAGE_UNITS 需使用 RDL 管理的数据源资源；
  • shardingAlgorithmType 指定自动分片算法类型，请参考 自动分片算法；
  • keyGenerateStrategyType 指定分布式主键生成策略，请参考 分布式主键；
  • auditorAlgorithmType 指定分片审计策略，请参考 分片审计；
  • 重复的 tableName 将无法被创建；
  • shardingAlgorithm 能够被不同的 Sharding Table Rule 复用，因此在执行 DROP SHARDING TABLE RULE 时，对应的 shardingAlgorithm 不会被移除；
  • 如需移除 shardingAlgorithm，请执行 DROP SHARDING ALGORITHM；
  • strategyType 指定分片策略，请参考 分片策略；
  • Sharding Table Rule 同时支持 Auto Table 和 Table 两种类型，两者在语法上有所差异，对应配置文件请参考 数据分片 ；
  • 执行 CREATE SHARDING TABLE RULE 时，将会自动创建新的分片算法，算法命名规则为 tableName_scope_shardingAlgorithmType，如 t_order_database_inline。

示例

CREATE SHARDING TABLE RULE t_order (
STORAGE_UNITS(ds_0,ds_1),
SHARDING_COLUMN=order_id,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
KEY_GENERATE_STRATEGY(COLUMN=another_id,TYPE(NAME="snowflake"))
);

2. 修改分片规则 #

ALTER SHARDING TABLE RULE shardingTableRuleDefinition [, shardingTableRuleDefinition] ...

shardingTableRuleDefinition:
    shardingAutoTableRule | shardingTableRule

shardingAutoTableRule:
    tableName(storageUnits, shardingColumn, algorithmDefinition [, keyGenerateDefinition] [, auditDeclaration])

shardingTableRule:
    tableName(dataNodes [, databaseStrategy] [, tableStrategy] [, keyGenerateDefinition] [, auditDeclaration])

storageUnits:
    STORAGE_UNITS(storageUnit [, storageUnit] ...)

dataNodes:
    DATANODES(dataNode [, dataNode] ...)

storageUnit:
    storageUnitName | inlineExpression

dataNode:
    dataNodeName | inlineExpression

shardingColumn:
    SHARDING_COLUMN=columnName

algorithmDefinition:
    TYPE(NAME=shardingAlgorithmType [, PROPERTIES([algorithmProperties])])

keyGenerateDefinition:
    KEY_GENERATE_STRATEGY(COLUMN=columnName, strategyDefinition)
    
auditDeclaration:
    auditDefinition | auditStrategy

auditDefinition:
    AUDIT_STRATEGY([(singleAuditDefinition),(singleAuditDefinition)], ALLOW_HINT_DISABLE=true)
    
singleAuditDefinition:
    NAME=auditor1, algorithmDefinition
    
auditStrategy:
    AUDIT_STRATEGY(AUDITORS=[auditor1,auditor2], ALLOW_HINT_DISABLE=true)

参数说明

  • STORAGE_UNITS 需使用 RDL 管理的数据源资源；
  • shardingAlgorithmType 指定自动分片算法类型，请参考 自动分片算法；
  • keyGenerateStrategyType 指定分布式主键生成策略，请参考 分布式主键；
  • auditorAlgorithmType 指定分片审计策略，请参考 分片审计；
  • 重复的 tableName 将无法被创建；
  • shardingAlgorithm 能够被不同的 Sharding Table Rule 复用，因此在执行 DROP SHARDING TABLE RULE 时，对应的 shardingAlgorithm 不会被移除；
  • 如需移除 shardingAlgorithm，请执行 DROP SHARDING ALGORITHM；
  • strategyType 指定分片策略，请参考 分片策略；
  • Sharding Table Rule 同时支持 Auto Table 和 Table 两种类型，两者在语法上有所差异，对应配置文件请参考 数据分片 ；
  • ALTER SHARDING TABLE RULE 不会触发数据重分片。

示例

ALTER SHARDING TABLE RULE t_order (
STORAGE_UNITS(ds_0,ds_1,ds_2,ds_3),
SHARDING_COLUMN=order_id,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="16")),
KEY_GENERATE_STRATEGY(COLUMN=another_id,TYPE(NAME="snowflake"))
);

3. 删除分片规则 #

DROP SHARDING TABLE RULE t_order_item;

示例

删除规则名为 t_order_item 的分片规则

DROP SHARDING TABLE RULE t_order_item;

4. 查看分片规则 #

SHOW SHARDING TABLE tableRule | RULES [FROM databaseName]

示例

查看逻辑库中所有表的分片规则

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 | auditor_types | allow_hint_disable |+--------------+---------------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+---------------------------------------------------+-------------------+------------------+-------------------+
| 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      || 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)

查看逻辑库中制定表的分片规则

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 | auditor_types | allow_hint_disable |+---------+----------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+----------------------------------------------+-------------------+------------------+-------------------+
| 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      | 
+---------+----------------------------+-------------------+----------------------+------------------------+-------------------------------+----------------------------------------+-------------------+---------------------+----------------------------+----------------------------------------------+-------------------+------------------+-------------------+
1 row in set (0.01 sec)

输出说明

列说明
table逻辑表名
actual_data_nodes实际的数据节点
actual_data_sources实际的数据源（通过 RDL 创建的规则时显示）
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是否禁用分片审计hint

5. 查看逻辑库中分片表所在的分片规则 #

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)

输出说明：

列说明
name分片规则名称
nodes分片节点

6. 单独删除分片算法 #

DROP SHARDING ALGORITHM algorithmName [, algorithmName] ...

示例

删除名称 t_order_hash_mod 的分片算法

DROP SHARDING ALGORITHM t_order_hash_mod;

7. 查看分片算法详情 #

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)

输出说明

列说明
name分片算法名称
type分片算法类型
props分片算法参数

8. 查看未被使用的分片算法 #

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)

输出说明

列说明
name分片算法名称
type分片算法类型
props分片算法参数

9. 查看使用指定分片算法的表 #

SHOW SHARDING TABLE RULES USED ALGORITHM shardingAlgorithmName [FROM databaseName]

示例

mysql> SHOW SHARDING TABLE RULES USED ALGORITHM t_order_inline;
+-------+---------+
| type  | name    |
+-------+---------+
| table | t_order |
1 row in set (0.01 sec)

输出说明

列说明
type分片算法类型
name分片算法名称（与表名相同）

10. 删分布式主键生成算法 #

DROP SHARDING KEY GENERATOR [IF EXISTS] keyGeneratorName [, keyGeneratorName] ...

示例

DROP SHARDING KEY GENERATOR snowflake_key_generator;

11. 查看分布式主键生成算法 #

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)

输出说明

列说明
name主键生成器名称
type主键生成器类型
props主键生成器参数

12. 查看未被使用的分布式主键生成算法 #

SHOW UNUSED SHARDING KEY GENERATORS;

参数说明

无

示例

mysql> SHOW UNUSED SHARDING KEY GENERATORS;
+------------------------+-----------+-----------------+
| name                   | type      | props           |
+------------------------+-----------+-----------------+
| dml_audit              | uuid      |                 |
+------------------------+-----------+-----------------+
1 row in set (0.01 sec)

输出说明

列说明
name主键生成器名称
type主键生成器类型
props主键生成器参数

13. 查看使用指定算法的表 #

SHOW SHARDING TABLE RULES USED KEY GENERATOR keyGeneratorName [FROM databaseName]

示例

mysql> SHOW SHARDING TABLE RULES USED KEY GENERATOR t_order_snowflake;
+-------+---------+
| type  | name    |
+-------+---------+
| table | t_order |
+-------+---------+
1 row in set (0.01 sec)

输出说明

列说明
type分片算法类型（分库算法或者分表算法）
name分片规则名称

14. 创建缺省的分库分表策略 #

CREATE DEFAULT SHARDING shardingScope STRATEGY (shardingStrategy)

shardingScope:
    DATABASE | TABLE

databaseStrategy:
    DATABASE_STRATEGY(shardingStrategy)

tableStrategy:
    TABLE_STRATEGY(shardingStrategy)

shardingStrategy:
    TYPE=strategyType, shardingColumn, shardingAlgorithm

shardingColumn:
    SHARDING_COLUMN=columnName

shardingAlgorithm:
    SHARDING_ALGORITHM(algorithmDefinition)

strategyDefinition:
    TYPE(NAME=keyGenerateStrategyType [, PROPERTIES([algorithmProperties])])

参数说明

  • strategyType 指定分片策略，请参考分片策略；
  • 执行 CREATE DEFAULT SHARDING STRATEGY 时，同样会自动创建新的分片算法，算法命名规则为 default_scope_shardingAlgorithmType，如 default_database_inline。

示例

CREATE DEFAULT SHARDING DATABASE STRATEGY (
TYPE="standard",SHARDING_COLUMN=order_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${order_id % 2}")))
);

15. 修改缺省的分库分表策略 #

ALTER DEFAULT SHARDING shardingScope STRATEGY (shardingStrategy)

shardingScope:
    DATABASE | TABLE

databaseStrategy:
    DATABASE_STRATEGY(shardingStrategy)

tableStrategy:
    TABLE_STRATEGY(shardingStrategy)

shardingStrategy:
    TYPE=strategyType, shardingColumn, shardingAlgorithm

shardingColumn:
    SHARDING_COLUMN=columnName

shardingAlgorithm:
    SHARDING_ALGORITHM(algorithmDefinition)

strategyDefinition:
    TYPE(NAME=keyGenerateStrategyType [, PROPERTIES([algorithmProperties])])

参数说明

  • strategyType 指定分片策略，请参考分片策略；
  • 执行 CREATE DEFAULT SHARDING STRATEGY时，同样会自动创建新的分片算法，算法命名规则为 default_scope_shardingAlgorithmType，如 default_database_inline。

示例

ALTER DEFAULT SHARDING DATABASE STRATEGY (
TYPE="standard",SHARDING_COLUMN=order_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${order_id % 2}")))
);

16. 删除缺省的分库分表策略 #

DROP DEFAULT SHARDING shardingScope STRATEGY

shardingScope:
    DATABASE | TABLE

示例

DROP DEFAULT SHARDING DATABASE STRATEGY;

17. 查看缺省的分库分表策略的详情 #

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)

输出说明

列说明
name策略名称
type分片策略类型
sharding_column分片键
sharding_algorithm_name分片算法名称
‘\sharding_algorithm_type分片算法类型
sharding_algorithm_props分片算法参数