双写 | SphereEx-DBPlusSuite

数据库双写 #

语法	描述	类型
SHOW DUAL_WRITE RULES	查看双写规则	RQL
CREATE DUAL_WRITE RULE	创建双写规则	RDL
ALTER DUAL_WRITE RULE	修改双写规则	RDL
DROP DUAL_WRITE RULE	删除双写规则	RDL
SHOW DUAL_WRITE KEY_GENERATE_STRATEGIES	查看双写主键生成策略	RQL
CREATE DUAL_WRITE KEY_GENERATE_STRATEGY	创建双写主键生成策略	RDL
ALTER DUAL_WRITE KEY_GENERATE_STRATEGY	修改双写主键生成策略	RDL
DROP DUAL_WRITE KEY_GENERATE_STRATEGY	删除双写主键生成策略	RDL

1. 查看双写规则 #

SHOW DUAL_WRITE (RULES | RULE ruleName)

输出说明

列	说明
name	规则名称
primary_storage_unit	主存储节点
write_storage_units	写节点
read_storage_units	读节点
load_balancer_type	负载均衡算法类型
load_balancer_props	负载均衡算法参数
interceptor_type	读流量拦截器类型
interceptor_props	读流量拦截器参数

示例

查看所有双写规则

mysql> SHOW DUAL_WRITE RULES;
+---------------+----------------------+---------------------+--------------------+--------------------+-------------------------+------------------------+-----------------------------------+
| name          | primary_storage_unit | write_storage_units | read_storage_units | load_balancer_type | load_balancer_props     | interceptor_type       | interceptor_props                 |
+---------------+----------------------+---------------------+--------------------+--------------------+-------------------------+------------------------+-----------------------------------+
| dual_write_ds | ds_0                 | ["ds_0","ds_1"]     | ["ds_0","ds_1"]    | WEIGHT             | {"ds_0":"9","ds_1":"1"} | SphereEx:SQL_INTERCEPT | {"sqls":"SELECT * FROM t_order;"} |
+---------------+----------------------+---------------------+--------------------+--------------------+-------------------------+------------------------+-----------------------------------+
1 row in set (0.05 sec)

查看指定双写规则

mysql> SHOW DUAL_WRITE RULE dual_write_ds;
+---------------+----------------------+---------------------+--------------------+--------------------+-------------------------+------------------------+-----------------------------------+
| name          | primary_storage_unit | write_storage_units | read_storage_units | load_balancer_type | load_balancer_props     | interceptor_type       | interceptor_props                 |
+---------------+----------------------+---------------------+--------------------+--------------------+-------------------------+------------------------+-----------------------------------+
| dual_write_ds | ds_0                 | ["ds_0","ds_1"]     | ["ds_0","ds_1"]    | WEIGHT             | {"ds_0":"9","ds_1":"1"} | SphereEx:SQL_INTERCEPT | {"sqls":"SELECT * FROM t_order;"} |
+---------------+----------------------+---------------------+--------------------+--------------------+-------------------------+------------------------+-----------------------------------+
1 row in set (0.02 sec)

2. 创建双写规则 #

createDualWriteRule
    : CREATE DUAL_WRITE RULE ifNotExists? dualwriteRuleDefinition (, dualwriteRuleDefinition)*
    ;

dualwriteRuleDefinition
    : ruleName ( dataSourceDefinition (, loadBalanceAlgorithm)? (, interceptAlgorithm)? )
    ;

dataSourceDefinition
    : primaryStorageUnit, writeStorageUnits, readStorageUnits
    ;

primaryStorageUnit
    : PRIMARY_STORAGE_UNIT = storageUnitName
    ;

writeStorageUnits
    : WRITE_STORAGE_UNITS ( storageUnitNames )
    ;

readStorageUnits
    : READ_STORAGE_UNITS ( storageUnitNames )
    ;

storageUnitNames
    : storageUnitName (, storageUnitName)?
    ;

loadBalanceAlgorithm
    : LOAD_BALANCE_ALGORITHM ( algorithmDefinition )
    | algorithmDefinition
    ;

interceptAlgorithm
    : INTERCEPT_ALGORITHM ( algorithmDefinition )
    ;

ifNotExists
    : IF NOT EXISTS
    ;

参数说明

PRIMARY_STORAGE_UNIT 指定主数据源，必填项
WRITE_STORAGE_UNITS 指定写数据源，必填项
READ_STORAGE_UNITS 指定读数据源，必填项
LOAD_BALANCE_ALGORITHM 可选项（读数据源数量大于 1 时必填），指定负载均衡算法
INTERCEPT_ALGORITHM 可选项，指定拦截器算法，默认为空

示例

CREATE DUAL_WRITE RULE dual_write_ds (
    PRIMARY_STORAGE_UNIT=ds_0,
    WRITE_STORAGE_UNITS(ds_0, ds_1),
    READ_STORAGE_UNITS(ds_0,ds_1),
    LOAD_BALANCE_ALGORITHM(TYPE(NAME="WEIGHT", properties("ds_0"="9", "ds_1"="1"))),
    INTERCEPT_ALGORITHM(TYPE(NAME="SphereEx:SQL_INTERCEPT", properties("sqls"="SELECT * FROM t_order;")))
);

3. 修改双写规则 #

alterDualWriteRule
    : ALTER DUAL_WRITE RULE dualwriteRuleDefinition (, dualwriteRuleDefinition)*
    ;

dualwriteRuleDefinition
    : ruleName ( dataSourceDefinition (, loadBalanceAlgorithm)? (, interceptAlgorithm)? )
    ;

dataSourceDefinition
    : primaryStorageUnit, writeStorageUnits, readStorageUnits
    ;

primaryStorageUnit
    : PRIMARY_STORAGE_UNIT = storageUnitName
    ;

writeStorageUnits
    : WRITE_STORAGE_UNITS ( storageUnitNames )
    ;

readStorageUnits
    : READ_STORAGE_UNITS ( storageUnitNames )
    ;

storageUnitNames
    : storageUnitName (, storageUnitName)?
    ;

loadBalanceAlgorithm
    : LOAD_BALANCE_ALGORITHM ( algorithmDefinition )
    | algorithmDefinition
    ;

interceptAlgorithm
    : INTERCEPT_ALGORITHM ( algorithmDefinition )
    ;

参数说明

PRIMARY_STORAGE_UNIT 指定主数据源，必填项
WRITE_STORAGE_UNITS 指定写数据源，必填项
READ_STORAGE_UNITS 指定读数据源，必填项
LOAD_BALANCE_ALGORITHM 可选项（读数据源数量大于 1 时必填），指定负载均衡算法
INTERCEPT_ALGORITHM 可选项，指定拦截器算法

示例

ALTER DUAL_WRITE RULE dual_write_ds (
    PRIMARY_STORAGE_UNIT=ds_0,
    WRITE_STORAGE_UNITS(ds_0, ds_1),
    READ_STORAGE_UNITS(ds_0,ds_1),
    LOAD_BALANCE_ALGORITHM(TYPE(NAME="WEIGHT", properties("ds_0"="9", "ds_1"="1"))),
    INTERCEPT_ALGORITHM(TYPE(NAME="SphereEx:SQL_INTERCEPT", properties("sqls"="SELECT * FROM t_order;")))
);

4. 删除双写规则 #

dropDualWriteRule
    : DROP DUAL_WRITE RULE ifExists? ruleName (, ruleName)*
    ;

ifExists
    : IF EXISTS
    ;

示例

DROP DUAL_WRITE RULE dual_write_ds;

5. 查看双写主键生成策略 #

SHOW DUAL_WRITE (KEY_GENERATE_STRATEGIES | KEY_GENERATE_STRATEGY tableName)

输出说明

列	说明
table_name	表名称
column_name	列名称
key_generate_algorithm_type	主键生成算法类型
key_generate_algorithm_props	主键生成算法参数

示例

查看所有双写主键生成策略

mysql> SHOW DUAL_WRITE KEY_GENERATE_STRATEGIES;
+------------+-------------+-----------------------------+------------------------------+
| table_name | column_name | key_generate_algorithm_type | key_generate_algorithm_props |
+------------+-------------+-----------------------------+------------------------------+
| t_province | id          | snowflake                   |                              |
| t_city     | id          | snowflake                   |                              |
+------------+-------------+-----------------------------+------------------------------+
2 rows in set (0.00 sec)

查看指定双写主键生成策略

mysql> SHOW DUAL_WRITE KEY_GENERATE_STRATEGY t_province;
+------------+-------------+-----------------------------+------------------------------+
| table_name | column_name | key_generate_algorithm_type | key_generate_algorithm_props |
+------------+-------------+-----------------------------+------------------------------+
| t_province | id          | snowflake                   |                              |
+------------+-------------+-----------------------------+------------------------------+
1 row in set (0.01 sec)

2. 创建双写主键生成策略 #

createDualWriteKeyGenerateStrategy
    : CREATE DUAL_WRITE KEY_GENERATE_STRATEGY ifNotExists? keyGenerateDefinition (, keyGenerateDefinition)*
    ;

keyGenerateDefinition
    : tableName (COLUMN = columnName, algorithmDefinition)
    ;

ifNotExists
    : IF NOT EXISTS
    ;

示例

CREATE DUAL_WRITE KEY_GENERATE_STRATEGY 
t_province (COLUMN=id,TYPE(NAME="snowflake")),
t_city(COLUMN=id,TYPE(NAME="snowflake"));

3. 修改双写主键生成策略 #

alterDualWriteKeyGenerateStrategy
    : ALTER DUAL_WRITE KEY_GENERATE_STRATEGY keyGenerateDefinition (, keyGenerateDefinition)*
    ;

keyGenerateDefinition
    : tableName (COLUMN = columnName, algorithmDefinition)
    ;

示例

ALTER DUAL_WRITE KEY_GENERATE_STRATEGY 
t_province (COLUMN=id,TYPE(NAME="NANOID")),
t_city(COLUMN=id,TYPE(NAME="SNOWFLAKE", properties("max-vibration-offset"="4096")));

4. 删除双写主键生成策略 #

dropBroadcastKeyGenerateStrategy
    : DROP BROADCAST KEY_GENERATE_STRATEGY ifExists? tableName (, tableName)*
    ;

ifExists
    : IF EXISTS
    ;

示例

DROP DUAL_WRITE KEY_GENERATE_STRATEGY t_province, t_city;