分片表 | SphereEx-DBPlusSuite

分片表 #

语法	描述	类型
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. 创建分片规则 #

copyCREATE 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

示例

创建自动分片规则

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

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

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

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

copyCREATE 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. 修改分片规则 #

copyALTER 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”时不做分库策略

示例

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

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

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

copyALTER 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. 删除分片规则 #

copyDROP SHARDING TABLE RULE t_order_item;

示例

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

copyDROP SHARDING TABLE RULE t_order_item;

4. 查看分片规则 #

copySHOW SHARDING TABLE (RULE ruleName | RULES) [FROM databaseName]

示例

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

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

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

copymysql> 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. 查看分片表的节点分布 #

copySHOW SHARDING TABLE NODES;

示例

copymysql> 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. 单独删除分片算法 #

copyDROP SHARDING ALGORITHM algorithmName [, algorithmName] ...

示例

删除名称 t_order_hash_mod 的分片算法

copyDROP SHARDING ALGORITHM t_order_hash_mod;

7. 查看分片算法详情 #

copySHOW SHARDING ALGORITHMS;

示例

copymysql> 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. 查看未被使用的分片算法 #

copySHOW UNUSED SHARDING ALGORITHMS;

示例

copymysql> 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. 查看使用指定分片算法的表 #

copySHOW SHARDING TABLE RULES USED ALGORITHM shardingAlgorithmName [FROM databaseName]

示例

copymysql> 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. 删分布式主键生成算法 #

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

示例

copyDROP SHARDING KEY GENERATOR snowflake_key_generator;

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

copySHOW SHARDING KEY GENERATORS;

示例

copy  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. 查看未被使用的分布式主键生成算法 #

copySHOW UNUSED SHARDING KEY GENERATORS;

示例

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

输出说明

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

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

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

示例

copymysql> 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. 创建缺省的分库分表策略 #

copyCREATE 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。

示例

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

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

copyALTER 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。

示例

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

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

copyDROP DEFAULT SHARDING shardingScope STRATEGY

shardingScope:
    DATABASE | TABLE

示例

copyDROP DEFAULT SHARDING DATABASE STRATEGY;

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

copySHOW DEFAULT SHARDING STRATEGY;

示例

copymysql> 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. 查看自动化分片策略的详情 #

copySHOW AUTO_RESHARDING (STRATEGY tableName | STRATEGIES)

示例

查看全部自动重分片策略

copySHOW 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              |
+-----------------------+----------+---------------------------------------------------------------------+------------------------------------+-------------------------------------------+

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

copySHOW 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. 创建缺省的自动化分片策略 #

copyCREATE DEFAULT AUTO_RESHARDING STRATEGY

示例

copyCREATE 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. 修改缺省的自动化分片策略 #

copyALTER DEFAULT AUTO_RESHARDING STRATEGY

示例

copyALTER 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. 删除缺省的自动化分片策略 #

copyDROP DEFAULT AUTO_RESHARDING STRATEGY

示例

copyDROP DEFAULT AUTO_RESHARDING STRATEGY;

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

copySHOW DEFAULT AUTO_RESHARDING STRATEGY

示例

copymysql> 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. 创建全局索引策略 #

copycreateShardingGlobalIndexStrategy
    : 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
创建全局索引策略前，需要先具备对应的分片规则
一个分片规则（主表），只能创建一个全局索引策略

示例

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

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

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

copyCREATE 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. 修改全局索引策略 #

copyalterShardingGlobalIndexStrategy
    : 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

示例

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

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

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

copyALTER 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. 删除全局索引策略 #

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

示例

copyDROP SHARDING GLOBAL INDEX STRATEGY t_order, t_order_item;

26. 查看全局索引策略 #

copySHOW SHARDING GLOBAL INDEX (STRATEGY primaryTabelName | STRATEGIES);

说明

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

示例

查看全部全局索引策略

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

查看指定全局索引策略

copymysql> 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	表分片算法参数