Logo
分片表

分片表 #

语法描述类型
CREATE SHARDING TABLE RULE创建分片规则RDL
ALTER SHARDING TABLE RULE修改分片规则RDL
DROP SHARDING TABLE RULE删除分片规则RDL
SHOW SHARDING TABLE RULE查看分片规则RQL
SHOW SHARDING TABLE NODES查看分片表的节点分布RQL
COUNT SHARDING RULE统计分片规则的数量RQL
DROP SHARDING ALGORITHM单独删除分片算法RDL
SHOW SHARDING ALGORITHMS查看分片算法详情RQL
SHOW UNUSED SHARDING ALGORITHMS查看未被使用的分片算法RQL
SHOW SHARDING TABLE RULES USED ALGORITHM查看使用指定分片算法的表RQL
DROP SHARDING KEY GENERATOR删除分布式主键生成算法RDL
SHOW SHARDING KEY GENERATORS查看分布式主键生成算法RQL
SHOW UNUSED SHARDING KEY GENERATORS查看未被使用的分布式主键生成算法RQL
SHOW SHARDING TABLE RULES USED KEY GENERATOR查看使用指定算法的表RQL
CREATE DEFAULT SHARDING shardingScope STRATEGY创建缺省的分库分表策略RDL
ALTER DEFAULT SHARDING shardingScope STRATEGY修改缺省的分库分表策略RDL
DROP DEFAULT SHARDING shardingScope STRATEGY删除缺省的分库分表策略RDL
SHOW DEFAULT SHARDING STRATEGY查看缺省的分库分表策略的详情RQL
SHOW AUTO_RESHARDING STRATEGY查看自动化分片策略的详情RQL
CREATE DEFAULT AUTO_RESHARDING STRATEGY创建缺省的自动化分片策略RDL
ALTER DEFAULT AUTO_RESHARDING STRATEGY修改缺省的自动化分片策略RDL
DROP DEFAULT AUTO_RESHARDING STRATEGY删除缺省的自动化分片策略RDL
SHOW DEFAULT AUTO_RESHARDING STRATEGY查看缺省的自动化分片策略的详情RQL
CREATE GLOBAL INDEX STRATEGY创建全局索引策略RDL
ALTER GLOBAL INDEX STRATEGY修改全局索引策略RDL
DROP GLOBAL INDEX STRATEGY删除全局索引策略RDL
SHOW GLOBAL INDEX STRATEGY查看全局索引策略RQL

1. 创建分片规则 #

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

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

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

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)

auditDefinition:
    AUDIT_STRATEGY(algorithmDefinition, [, algorithmDefinition]*, ALLOW_HINT_DISABLE=true)

autoReshardingStrategy:
    AUTO_RESHARDING_STRATEGY(
        MATCHING_ALGORITHM(algorithmDefinition),
        ACTION_ALGORITHM(algorithmDefinition)
    )

参数说明

  • STORAGE_UNITS 需使用 RDL 管理的数据源资源；
  • shardingAlgorithmType 指定自动分片算法类型，请参考 分片算法；
  • autoReshardingStrategyType 指定自动化分片算法类型，请参考 分片算法；
  • autoReshardingStrategyType 自动化分片的 MATCHING_ALGORITHM 算法可多个，任意一个满足，则触发扩容动作
  • keyGenerateStrategyType 指定分布式主键生成策略，请参考 分布式主键；
  • auditorAlgorithmType 指定分片审计策略，请参考 分片审计；
  • 重复的 tableName 将无法被创建；
  • shardingAlgorithm 能够被不同的 Sharding Table Rule 复用，因此在执行 DROP SHARDING TABLE RULE 时，对应的 shardingAlgorithm 不会被移除；
  • 如需移除 shardingAlgorithm，请执行 DROP SHARDING ALGORITHM；
  • 创建 sharding rule 后自动生成的算法命名规则为 tableName _ strategyType _ algorithmType；
  • 创建 sharding rule 后自动生成的主键策略命名规则为 tableName _ `strategyType；
  • Sharding Table Rule 同时支持 Auto Table 和 Table 两种类型，两者在语法上有所差异；
  • 执行 CREATE SHARDING TABLE RULE 时，将会自动创建新的分片算法，算法命名规则为 tableName_scope_shardingAlgorithmType，如 t_order_database_inline。
  • DATABASE_STRATEGY 为“NONE”时不做分库策略
  • 当 default autoReshardingStrategy 时用户不配置autoResharding的含义是使用default；当无 default autoReshardingStrategy 用户不配置的含义是不配置 autoResharding

示例

创建自动分片规则

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

创建标准分片规则，不分库只分表

CREATE SHARDING TABLE RULE t_item (
DATANODES("ds_0.t_item_${0..1}"),
DATABASE_STRATEGY(TYPE="NONE"),
TABLE_STRATEGY(TYPE="standard",SHARDING_COLUMN=order_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="t_item_${order_id % 2}"))))
);

创建自动分片规则，并配置自动重分片策略

CREATE SHARDING TABLE RULE t_order_auto_interval (
    STORAGE_UNITS(ds_0,ds_1),
    SHARDING_COLUMN=creation_date,TYPE(NAME="AUTO_INTERVAL",PROPERTIES("datetime-lower"="2023-06-01 00:00:00", "datetime-upper"="2023-06-25 23:59:59", "sharding-seconds"="86400")),
    AUTO_RESHARDING_STRATEGY(
        MATCHING_ALGORITHM(TYPE(NAME="INTERVAL_UPPER_TIME_HIGH_WATERLINE",PROPERTIES("remaining-seconds-until-upper-time"=864000))),
        ACTION_ALGORITHM(TYPE(NAME="SCALE_SHARDING",PROPERTIES("operation-type"="ADD","amount"=30)))
    )
);

2. 修改分片规则 #

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

shardingTableRuleDefinition:
    shardingAutoTableRule | shardingTableRule

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

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

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)

auditDefinition:
    AUDIT_STRATEGY(algorithmDefinition, [, algorithmDefinition]*, ALLOW_HINT_DISABLE=true)

autoReshardingStrategy:
    AUTO_RESHARDING_STRATEGY(
        MATCHING_ALGORITHM(algorithmDefinition),
        ACTION_ALGORITHM(algorithmDefinition)
    )

参数说明

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

示例

修改分片规则，使用自动分片策略

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

修改分片规则，并配置自动重分片策略

ALTER SHARDING TABLE RULE t_order_auto_interval (
    STORAGE_UNITS(ds_0,ds_1),
    SHARDING_COLUMN=creation_date,TYPE(NAME="AUTO_INTERVAL",PROPERTIES("datetime-lower"="2023-06-01 00:00:00", "datetime-upper"="2023-06-25 23:59:59", "sharding-seconds"="86400")),
    AUTO_RESHARDING_STRATEGY(
        MATCHING_ALGORITHM(TYPE(NAME="INTERVAL_UPPER_TIME_HIGH_WATERLINE",PROPERTIES("remaining-seconds-until-upper-time"=864000))),
        ACTION_ALGORITHM(TYPE(NAME="SCALE_SHARDING",PROPERTIES("operation-type"="ADD","amount"=30)))
    )
);

3. 删除分片规则 #

DROP SHARDING TABLE RULE t_order_item;

示例

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

DROP SHARDING TABLE RULE t_order_item;

4. 查看分片规则 #

SHOW SHARDING TABLE (RULE ruleName | 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 | global_index_names | consistency_level | table_access_by_index_threshold | sync_delay_milliseconds_threshold |
+---------+-------------------+---------------------+------------------------+--------------------------+----------------------------------+-----------------------------------+---------------------+-----------------------+-------------------------------+--------------------------------+---------------------+--------------------+---------------------+---------------+--------------------+--------------------+-------------------+---------------------------------+-----------------------------------+
| t_order |                   | ds_0,ds_1           |                        |                          |                                  |                                   | STANDARD            | order_id              | hash_mod                      | {"sharding-count":"4"}         | another_id          | snowflake          |                     |               |                    |                    |                   |                                 |                                   |
+---------+-------------------+---------------------+------------------------+--------------------------+----------------------------------+-----------------------------------+---------------------+-----------------------+-------------------------------+--------------------------------+---------------------+--------------------+---------------------+---------------+--------------------+--------------------+-------------------+---------------------------------+-----------------------------------+
1 row 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 | global_index_names | consistency_level | table_access_by_index_threshold | sync_delay_milliseconds_threshold |
+---------+-------------------+---------------------+------------------------+--------------------------+----------------------------------+-----------------------------------+---------------------+-----------------------+-------------------------------+--------------------------------+---------------------+--------------------+---------------------+---------------+--------------------+--------------------+-------------------+---------------------------------+-----------------------------------+
| t_order |                   | ds_0,ds_1           |                        |                          |                                  |                                   | STANDARD            | order_id              | hash_mod                      | {"sharding-count":"4"}         | another_id          | snowflake          |                     |               |                    |                    |                   |                                 |                                   |
+---------+-------------------+---------------------+------------------------+--------------------------+----------------------------------+-----------------------------------+---------------------+-----------------------+-------------------------------+--------------------------------+---------------------+--------------------+---------------------+---------------+--------------------+--------------------+-------------------+---------------------------------+-----------------------------------+
1 row in set (0.01 sec)

输出说明

列说明
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是否禁用分片审计hint
global_index_names全局索引名称
consistency_level全局索引一致性
table_access_by_index_threshold最大回表查询行数
sync_delay_milliseconds_threshold索引表和主表同步延迟时间阈值

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.01 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_hash_mod | hash_mod | {"sharding-count":"4"} |
| t_unused         | hash_mod | {"sharding-count":"4"} |
+------------------+----------+------------------------+

输出说明

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

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

SHOW UNUSED SHARDING ALGORITHMS;

示例

mysql> SHOW UNUSED SHARDING ALGORITHMS;
+----------+----------+------------------------+
| name     | type     | props                  |
+----------+----------+------------------------+
| t_unused | hash_mod | {"sharding-count":"4"} |
+----------+----------+------------------------+
1 row in set (0.02 sec)

输出说明

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

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

SHOW SHARDING TABLE RULES USED ALGORITHM shardingAlgorithmName [FROM databaseName]

示例

mysql> SHOW SHARDING TABLE RULES USED ALGORITHM t_order_hash_mod;
+------------+---------+
| type       | name    |
+------------+---------+
| auto_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 (shardingStrategyDefinition)

shardingScope:
    DATABASE | TABLE

databaseStrategy:
    DATABASE_STRATEGY (shardingStrategyDefinition)

tableStrategy:
    TABLE_STRATEGY (shardingStrategyDefinition)

shardingStrategyDefinition:
    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 (shardingStrategyDefinition)

shardingScope:
    DATABASE | TABLE

databaseStrategy:
    DATABASE_STRATEGY (shardingStrategyDefinition)

tableStrategy:
    TABLE_STRATEGY (shardingStrategyDefinition)

shardingStrategyDefinition:
    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分片算法参数

18. 查看自动化分片策略的详情 #

SHOW AUTO_RESHARDING (STRATEGY tableName | STRATEGIES)

示例

查看全部自动重分片策略

SHOW AUTO_RESHARDING STRATEGIES;
+-----------------------+----------+---------------------------------------------------------------------+------------------------------------+-------------------------------------------+
| table                 | type     | algorithm_name                                                      | algorithm_type                     | algorithm_props                           |
+-----------------------+----------+---------------------------------------------------------------------+------------------------------------+-------------------------------------------+
| t_order_auto_interval | matching | t_order_auto_interval_matching_interval_upper_time_high_waterline_0 | INTERVAL_UPPER_TIME_HIGH_WATERLINE | remaining-seconds-until-upper-time=864000 |
| t_order_auto_interval | action   | t_order_auto_interval_action_scale_sharding                         | SCALE_SHARDING                     | amount=30,operation-type=ADD              |
+-----------------------+----------+---------------------------------------------------------------------+------------------------------------+-------------------------------------------+

查看指定的自动重分片策略

SHOW AUTO_RESHARDING STRATEGY t_order_auto_interval;
+-----------------------+----------+---------------------------------------------------------------------+------------------------------------+-------------------------------------------+
| table                 | type     | algorithm_name                                                      | algorithm_type                     | algorithm_props                           |
+-----------------------+----------+---------------------------------------------------------------------+------------------------------------+-------------------------------------------+
| t_order_auto_interval | matching | t_order_auto_interval_matching_interval_upper_time_high_waterline_0 | INTERVAL_UPPER_TIME_HIGH_WATERLINE | remaining-seconds-until-upper-time=864000 |
| t_order_auto_interval | action   | t_order_auto_interval_action_scale_sharding                         | SCALE_SHARDING                     | amount=30,operation-type=ADD              |
+-----------------------+----------+---------------------------------------------------------------------+------------------------------------+-------------------------------------------+

19. 创建缺省的自动化分片策略 #

CREATE DEFAULT AUTO_RESHARDING STRATEGY

示例

CREATE DEFAULT AUTO_RESHARDING STRATEGY(
  MATCHING_ALGORITHM(
  TYPE(NAME="INTERVAL_UPPER_TIME_HIGH_WATERLINE",PROPERTIES("remaining-seconds-until-upper-time"=86400))
  ),
  ACTION_ALGORITHM(TYPE(NAME="SCALE_SHARDING",PROPERTIES("operation-type"="ADD","amount"=2)))
);

20. 修改缺省的自动化分片策略 #

ALTER DEFAULT AUTO_RESHARDING STRATEGY

示例

ALTER DEFAULT AUTO_RESHARDING STRATEGY(
  MATCHING_ALGORITHM(
  TYPE(NAME="INTERVAL_UPPER_TIME_HIGH_WATERLINE",PROPERTIES("remaining-seconds-until-upper-time"=86400))
  ),
  ACTION_ALGORITHM(TYPE(NAME="SCALE_SHARDING",PROPERTIES("operation-type"="ADD","amount"=2)))
);

21. 删除缺省的自动化分片策略 #

DROP DEFAULT AUTO_RESHARDING STRATEGY

示例

DROP DEFAULT AUTO_RESHARDING STRATEGY;

22. 查看缺省的自动化分片策略的详情 #

SHOW DEFAULT AUTO_RESHARDING STRATEGY

示例

mysql> SHOW DEFAULT AUTO_RESHARDING STRATEGY;
+----------+---------------------------------------+------------------------------------+------------------------------------------------+
| type     | algorithm_name                        | algorithm_type                     | algorithm_props                                |
+----------+---------------------------------------+------------------------------------+------------------------------------------------+
| matching | default_matching_matching_algorithm_0 | INTERVAL_UPPER_TIME_HIGH_WATERLINE | {"remaining-seconds-until-upper-time":"86400"} |
| action   | default_action_action_algorithm       | SCALE_SHARDING                     | {"amount":"2","operation-type":"ADD"}          |
+----------+---------------------------------------+------------------------------------+------------------------------------------------+
2 rows in set (0.32 sec)

23. 创建全局索引策略 #

createShardingGlobalIndexStrategy
    : CREATE SHARDING? GLOBAL INDEX STRATEGY ifNotExists? globalIndexStrategyDefinition (, globalIndexStrategyDefinition)*
    ;

globalIndexStrategyDefinition
    : primaryTabelName ( globalIndexItemDefinition (, globalIndexItemDefinition)* (, consistencyLevelDefinition)? (, tableAccessByIndexThresholdDefinition)? (, syncDelayMillisecondsThresholdDefinition)? )
    ;

globalIndexItemDefinition
    : ( (globalIndexName ,) (coveringColumns ,)? (globalIndexAutoTableRule | globalIndexTableRule) )
    ;

consistencyLevelDefinition
    : CONSISTENCY_LEVEL = consistencyLevelValue
    ;

consistencyLevelValue
    : STRONG | WEAK
    ;

tableAccessByIndexThresholdDefinition
    : TABLE_ACCESS_BY_INDEX_THRESHOLD = intValue
    ;

syncDelayMillisecondsThresholdDefinition
    : SYNC_DELAY_MILLISECONDS_THRESHOLD = intValue
    ;

globalIndexName
    : GLOBAL_INDEX_NAME = indexName
    ;

coveringColumns
    : COVERING_COLUMNS = columnName (, columnName)*
    ;

globalIndexAutoTableRule
    : storageUnits , shardingColumn , algorithmDefinition
    ;

globalIndexTableRule
    : dataNodes (, databaseStrategy)? (, tableStrategy)?
    ;

参数说明

  • primaryTabelName 为主表名称
  • globalIndexName 为可选项，用于指定索引名称，不指定时根据表名自动生成
  • coveringColumns 为可选项，含义请参考 YAML 中 coveringColumns
  • consistencyLevelDefinition 为可选项，含义请参考 YAML 中 consistencyLevel
  • tableAccessByIndexThresholdDefinition 为可选项，含义请参考 YAML 中 tableAccessByIndexThreshold
  • syncDelayMillisecondsThresholdDefinition 为可选项，含义请参考 YAML 中 syncDelayMillisecondsThreshold
  • 创建全局索引策略前，需要先具备对应的分片规则
  • 一个分片规则（主表），只能创建一个全局索引策略

示例

创建全局索引策略（自动分片方式）

CREATE GLOBAL INDEX STRATEGY t_order (
(GLOBAL_INDEX_NAME=t_order_idx,
 COVERING_COLUMNS=user_id,
 STORAGE_UNITS(ds_0,ds_1),
 SHARDING_COLUMN=order_id,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"=4))
),
CONSISTENCY_LEVEL=STRONG,
TABLE_ACCESS_BY_INDEX_THRESHOLD=10000,
SYNC_DELAY_MILLISECONDS_THRESHOLD=1000
);

创建全局索引策略（标准分片方式）

CREATE GLOBAL INDEX STRATEGY t_order_item (
(GLOBAL_INDEX_NAME=t_order_item_idx_user_id,
 COVERING_COLUMNS=user_id,
 DATANODES("ds_${0..1}.t_order_item_user_id_idx_${0..1}"),
 DATABASE_STRATEGY(TYPE="standard",SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${user_id % 2}")))),
 TABLE_STRATEGY(TYPE="standard", SHARDING_COLUMN=user_id, SHARDING_ALGORITHM(TYPE(NAME="inline", PROPERTIES("algorithm-expression"="t_order_item_user_id_idx_${user_id % 2}"))))
),
(GLOBAL_INDEX_NAME=t_order_item_idx_item_id,
 COVERING_COLUMNS=item_id,
 DATANODES("ds_${0..1}.t_order_item_itme_id_idx_${0..1}"),
 DATABASE_STRATEGY(TYPE="standard",SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${user_id % 2}")))),
 TABLE_STRATEGY(TYPE="standard", SHARDING_COLUMN=item_id, SHARDING_ALGORITHM(TYPE(NAME="inline", PROPERTIES("algorithm-expression"="t_order_item_itme_id_idx_${item_id % 2}"))))
),
CONSISTENCY_LEVEL=STRONG,
TABLE_ACCESS_BY_INDEX_THRESHOLD=10000,
SYNC_DELAY_MILLISECONDS_THRESHOLD=1000
);

24. 修改全局索引策略 #

alterShardingGlobalIndexStrategy
    : ALTER SHARDING? GLOBAL INDEX STRATEGY globalIndexStrategyDefinition (, globalIndexStrategyDefinition)*
    ;

globalIndexStrategyDefinition
    : primaryTabelName ( globalIndexItemDefinition (, globalIndexItemDefinition)* (, consistencyLevelDefinition)? (, tableAccessByIndexThresholdDefinition)? (, syncDelayMillisecondsThresholdDefinition)? )
    ;

globalIndexItemDefinition
    : ( (globalIndexName ,) (coveringColumns ,)? (globalIndexAutoTableRule | globalIndexTableRule) )
    ;

consistencyLevelDefinition
    : CONSISTENCY_LEVEL = consistencyLevelValue
    ;

consistencyLevelValue
    : STRONG | WEAK
    ;

tableAccessByIndexThresholdDefinition
    : TABLE_ACCESS_BY_INDEX_THRESHOLD = intValue
    ;

syncDelayMillisecondsThresholdDefinition
    : SYNC_DELAY_MILLISECONDS_THRESHOLD = intValue
    ;

globalIndexName
    : GLOBAL_INDEX_NAME = indexName
    ;

coveringColumns
    : COVERING_COLUMNS = columnName (, columnName)*
    ;

globalIndexAutoTableRule
    : storageUnits , shardingColumn , algorithmDefinition
    ;

globalIndexTableRule
    : dataNodes (, databaseStrategy)? (, tableStrategy)?
    ;

参数说明

  • primaryTabelName 为主表名称
  • globalIndexName 为可选项，用于指定索引名称，不指定时根据表名自动生成
  • coveringColumns 为可选项，含义请参考 YAML 中 coveringColumns
  • consistencyLevelDefinition 为可选项，含义请参考 YAML 中 consistencyLevel
  • tableAccessByIndexThresholdDefinition 为可选项，含义请参考 YAML 中 tableAccessByIndexThreshold
  • syncDelayMillisecondsThresholdDefinition 为可选项，含义请参考 YAML 中 syncDelayMillisecondsThreshold

示例

修改全局索引策略（自动分片方式）

ALTER GLOBAL INDEX STRATEGY t_order (
(GLOBAL_INDEX_NAME=t_order_idx,
 COVERING_COLUMNS=user_id,
 STORAGE_UNITS(ds_0,ds_1),
 SHARDING_COLUMN=order_id,TYPE(NAME="hash_mod",PROPERTIES("sharding-count"=4))
),
CONSISTENCY_LEVEL=STRONG,
TABLE_ACCESS_BY_INDEX_THRESHOLD=10000,
SYNC_DELAY_MILLISECONDS_THRESHOLD=1000
);

修改全局索引策略（标准分片方式）

ALTER GLOBAL INDEX STRATEGY t_order_item (
(GLOBAL_INDEX_NAME=t_order_item_idx_user_id,
 COVERING_COLUMNS=user_id,
 DATANODES("ds_${0..1}.t_order_item_user_id_idx_${0..1}"),
 DATABASE_STRATEGY(TYPE="standard",SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${user_id % 2}")))),
 TABLE_STRATEGY(TYPE="standard", SHARDING_COLUMN=user_id, SHARDING_ALGORITHM(TYPE(NAME="inline", PROPERTIES("algorithm-expression"="t_order_item_user_id_idx_${user_id % 2}"))))
),
(GLOBAL_INDEX_NAME=t_order_item_idx_item_id,
 COVERING_COLUMNS=item_id,
 DATANODES("ds_${0..1}.t_order_item_itme_id_idx_${0..1}"),
 DATABASE_STRATEGY(TYPE="standard",SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${user_id % 2}")))),
 TABLE_STRATEGY(TYPE="standard", SHARDING_COLUMN=item_id, SHARDING_ALGORITHM(TYPE(NAME="inline", PROPERTIES("algorithm-expression"="t_order_item_itme_id_idx_${item_id % 2}"))))
),
CONSISTENCY_LEVEL=STRONG,
TABLE_ACCESS_BY_INDEX_THRESHOLD=10000,
SYNC_DELAY_MILLISECONDS_THRESHOLD=1000
);

25. 删除全局索引策略 #

DROP SHARDING? GLOBAL INDEX STRATEGY ifExists? primaryTabelName (, primaryTabelName)*

示例

DROP SHARDING GLOBAL INDEX STRATEGY t_order, t_order_item;

26. 查看全局索引策略 #

SHOW SHARDING GLOBAL INDEX (STRATEGY primaryTabelName | STRATEGIES);

说明

  • 全局索引策略中的 global_index_names，consistency_level，table_access_by_index_threshold，sync_delay_milliseconds_threshold 请通过 SHOW SHARDING TABLE RULE 语法查看

示例

查看全部全局索引策略

mysql> SHOW GLOBAL INDEX STRATEGIES;
+---------------+--------------------+------------------+---------------------------------------------+---------------------+------------------------+--------------------------+----------------------------------+----------------------------------------------+---------------------+-----------------------+-------------------------------+-------------------------------------------------------+
| primary_table | global_index_table | covering_columns | 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                        |
+---------------+--------------------+------------------+---------------------------------------------+---------------------+------------------------+--------------------------+----------------------------------+----------------------------------------------+---------------------+-----------------------+-------------------------------+-------------------------------------------------------+
| t_order_item  | t_order_item_idx_0 | user_id          | ds_${0..1}.t_order_item_user_id_idx_${0..1} |                     | STANDARD               | user_id                  | inline                           | {"algorithm-expression":"ds_${user_id % 2}"} | STANDARD            | user_id               | inline                        | {"algorithm-expression":"user_id_idx_${item_id % 2}"} |
| t_order_item  | t_order_item_idx_1 | item_id          | ds_${0..1}.t_order_item_itme_id_idx_${0..1} |                     | STANDARD               | user_id                  | inline                           | {"algorithm-expression":"ds_${user_id % 2}"} | STANDARD            | user_id               | inline                        | {"algorithm-expression":"user_id_idx_${item_id % 2}"} |
| t_order       | t_order_idx_0      | user_id          |                                             | ds_0,ds_1           |                        |                          |                                  |                                              | STANDARD            | order_id              | hash_mod                      | {"sharding-count":"4"}                                |
+---------------+--------------------+------------------+---------------------------------------------+---------------------+------------------------+--------------------------+----------------------------------+----------------------------------------------+---------------------+-----------------------+-------------------------------+-------------------------------------------------------+
3 rows in set (0.02 sec)

查看指定全局索引策略

mysql> SHOW GLOBAL INDEX STRATEGY t_order;
+---------------+--------------------+------------------+-------------------+---------------------+------------------------+--------------------------+----------------------------------+-----------------------------------+---------------------+-----------------------+-------------------------------+--------------------------------+
| primary_table | global_index_table | covering_columns | 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 |
+---------------+--------------------+------------------+-------------------+---------------------+------------------------+--------------------------+----------------------------------+-----------------------------------+---------------------+-----------------------+-------------------------------+--------------------------------+
| t_order       | t_order_idx_0      | user_id          |                   | ds_0,ds_1           |                        |                          |                                  |                                   | STANDARD            | order_id              | hash_mod                      | {"sharding-count":"4"}         |
+---------------+--------------------+------------------+-------------------+---------------------+------------------------+--------------------------+----------------------------------+-----------------------------------+---------------------+-----------------------+-------------------------------+--------------------------------+
1 row in set (0.03 sec)

输出说明

列说明
primary_table主表名称
global_index_table全局索引表逻辑名称
covering_columns覆盖列
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表分片算法参数