RAL 语法 #

RAL (Resource & Rule Administration Language) 为 SphereEx-DBPlusEngine 的管理语言，负责强制路由、事务类型切换、弹性伸缩、分片执行计划查询等增量功能的操作。

强制路由 #

语句	说明	示例
set readwrite_splitting hint source = [auto / write]	针对当前连接，设置读写分离的路由策略（自动路由或强制到写库）	set readwrite_splitting hint source = write
set sharding hint database_value = yy	针对当前连接，设置 hint 仅对数据库分片有效，并添加分片值，yy：数据库分片值	set sharding hint database_value = 100
add sharding hint database_value xx = yy	针对当前连接，为表 xx 添加分片值 yy，xx：逻辑表名称，yy：数据库分片值	add sharding hint database_value t_order= 100
add sharding hint table_value xx = yy	针对当前连接，为表 xx 添加分片值 yy，xx：逻辑表名称，yy：表分片值	add sharding hint table_value t_order = 100
clear hint	针对当前连接，清除 hint 所有设置	clear hint
clear [sharding hint / readwrite_splitting hint]	针对当前连接，清除 sharding 或 readwrite_splitting 的 hint 设置	clear readwrite_splitting hint
show [sharding / readwrite_splitting] hint status	针对当前连接，查询 sharding 或 readwrite_splitting 的 hint 设置	show readwrite_splitting hint status

数据迁移 #

语句	说明	示例
SHOW MIGRATION RULE	查询数据迁移规则	SHOW MIGRATION RULE
ALTER MIGRATION RULE	修改数据迁移规则	ALTER MIGRATION RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’)))))
MIGRATE TABLE ds.schema.table INTO table	从源端迁移到目标端	MIGRATE TABLE ds_0.public.t_order INTO t_order
SHOW MIGRATION LIST	查询运行列表	SHOW MIGRATION LIST
SHOW MIGRATION STATUS jobId	查询作业状态	SHOW MIGRATION STATUS 1234
STOP MIGRATION jobId	停止作业	STOP MIGRATION 12345
START MIGRATION jobId	开启停止的作业	START MIGRATION 1234
CHECK MIGRATION jobId	数据一致性校验	CHECK MIGRATION 1234
SHOW MIGRATION CHECK ALGORITHMS	查询可用的一致性校验算法	SHOW MIGRATION CHECK ALGORITHMS
CHECK MIGRATION jobId BY TYPE(NAME=algorithmTypeName)	数据一致性校验，使用指定的校验算法	CHECK MIGRATION 1234 BY TYPE(NAME="DATA_MATCH”)
SHOW MIGRATION CHECK STATUS jobId	查询数据一致性校验进度	SHOW MIGRATION CHECK STATUS 1234
STOP MIGRATION CHECK jobId	停止数据一致性校验	STOP MIGRATION CHECK 1234
START MIGRATION CHECK jobId	开启数据一致性校验	START MIGRATION CHECK 1234
ROLLBACK MIGRATION jobId	撤销作业。注意：该语句会清理目标端表，请谨慎操作	ROLLBACK MIGRATION 1234
COMMIT MIGRATION jobId	提交作业	COMMIT MIGRATION 1234

弹性伸缩 #

语句	说明	示例
SHOW RESHARDING RULE	查询重分片规则	SHOW RESHARDING RULE
ALTER RESHARDING RULE	修改重分片规则	ALTER RESHARDING RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’)))))
SHOW RESHARDING LIST	查询运行列表	SHOW RESHARDING LIST
SHOW RESHARDING STATUS jobId	查询任务状态	SHOW RESHARDING STATUS 1234
STOP RESHARDING jobId	停止运行任务	STOP RESHARDING 12345
START RESHARDING jobId	开始运行任务	START RESHARDING 1234
CHECK RESHARDING jobId	数据一致性校验	CHECK RESHARDING 1234
SHOW RESHARDING CHECK ALGORITHMS	查询可用的一致性校验算法	SHOW RESHARDING CHECK ALGORITHMS
CHECK RESHARDING jobId BY TYPE(NAME=algorithmTypeName)	数据一致性校验，使用指定的校验算法	CHECK RESHARDING 1234 BY TYPE(NAME=DEFAULT)
SHOW RESHARDING CHECK STATUS jobId	查询数据一致性校验进度	SHOW RESHARDING CHECK STATUS 1234
STOP RESHARDING CHECK jobId	停止数据一致性校验	STOP RESHARDING CHECK 1234
START RESHARDING CHECK jobId	开启数据一致性校验	START RESHARDING CHECK 1234
STOP RESHARDING SOURCE WRITING jobId	旧的 DBPlusEngine 数据源停写	STOP RESHARDING SOURCE WRITING 1234
FORCE? APPLY RESHARDING jobId	切换至新的 DBPlusEngine 元数据	APPLY RESHARDING 1234
RESTORE RESHARDING SOURCE WRITING jobId	旧的 DBPlusEngine 数据源恢复写	RESTORE RESHARDING SOURCE WRITING 1234
FORCE? COMMIT RESHARDING jobId	提交作业	COMMIT RESHARDING 1234
ROLLBACK RESHARDING jobId	撤销作业	ROLLBACK RESHARDING 1234

加密洗数 #

语句	说明	示例
SHOW ENCRYPTING RULE	查询加密洗数规则	SHOW ENCRYPTING RULE
ALTER ENCRYPTING RULE	修改加密洗数规则	ALTER ENCRYPTING RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’)))))
SHOW ENCRYPTING LIST;	查询运行列表	SHOW ENCRYPTING LIST;
SHOW ENCRYPTING STATUS {jobId};	查询任务状态，jobId：任务 id	SHOW ENCRYPTING STATUS 1234;
ENCRYPT TABLE {tableName};	开始运行任务，tableName：表名	ENCRYPT TABLE t_user;
COMMIT ENCRYPTING {jobId};	提交洗数任务，jobId：任务 id。当前版本 commit 只做清理 job 的操作，后续版本中将支持 commit 和 roolback	COMMIT ENCRYPTING 1234;

解密洗数 #

语句	说明	示例
SHOW DECRYPTING RULE	查询解密洗数规则	SHOW DECRYPTING RULE
ALTER DECRYPTING RULE	修改解密洗数规则	ALTER DECRYPTING RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’)))))
SHOW DECRYPTING LIST;	查询运行列表	SHOW DECRYPTING LIST;
SHOW DECRYPTING STATUS {jobId};	查询任务状态，jobId：任务 id	SHOW DECRYPTING STATUS 1234;
DECRYPT TABLE {tableName};	开始运行任务，tableName：表名	DECRYPT TABLE t_user;
COMMIT DECRYPTING {jobId};	提交解密洗数任务，jobId：任务 id。当前版本 commit 只做清理 job 的操作，后续版本中将支持 commit 和 roolback	COMMIT ENCRYPTING 1234;

熔断 #

语句	说明	示例
ALTER READWRITE_SPLITTING RULE [ groupName ] (ENABLE / DISABLE) storageUnitName [FROM databaseName]	启用 / 禁用读库	ALTER READWRITE_SPLITTING RULE group_1 ENABLE read_ds_1
[ENABLE / DISABLE] COMPUTE NODE instanceId	启用 / 禁用 proxy 实例	DISABLE COMPUTE NODE instance_1
SHOW COMPUTE NODES	查询 proxy 实例信息	SHOW COMPUTE NODES
SHOW STATUS FROM READWRITE_SPLITTING (RULES / RULE groupName) [FROM databaseName]	查询读写分离数据源状态	SHOW STATUS FROM READWRITE_SPLITTING RULES

全局规则 #

语句	说明	示例
SHOW AUTHORITY RULE	查询权限规则配置	SHOW AUTHORITY RULE
SHOW TRANSACTION RULE	查询事务规则配置	SHOW TRANSACTION RULE
SHOW SQL_PARSER RULE	查询解析引擎规则配置	SHOW SQL_PARSER RULE
SHOW TRAFFIC[ RULES / RULE ruleName]	查询指定的双路由规则或者所有双路由规则	SHOW TRAFFIC RULES;
CREATE TRAFFIC RULE sql_match_traffic (LABLES(xxx),TRAFFIC_ALGORITHM(TYPE(NAME=xxx,PROPERTIES(“key” = “value”))), LOAD_BALANCER(TYPE(NAME=xxx,PROPERTIES(“key"="value”))))	创建双路由规则，`TRAFFIC_ALGORITHM` 支持 `SQL_MATCH` 与 `SQL_HINT` 两种类型 `LOAD_BALANCER` 支持 `RANDOM` 与 `ROUND_ROBIN` 两种类型	CREATE TRAFFIC RULE sql_match_traffic (LABLES(OLTP),TRAFFIC_ALGORITHM(TYPE(NAME=SQL_MATCH,PROPERTIES(“sql” = “SELECT * FROM t_order WHERE order_id = ?; UPDATE t_order SET order_id = ?;"))),LOAD_BALANCER(TYPE(NAME=RANDOM)))
ALTER TRANSACTION RULE(DEFAULT=xx,TYPE(NAME=xxx, PROPERTIES(“key1"="value1”,“key2"="value2”…)))	更新事务规则配置，`DEFAULT`：默认事务类型，支持 LOCAL、XA、BASE；`NAME`：事务管理器名称，支持 Atomikos、Narayana 和 Bitronix	ALTER TRANSACTION RULE(DEFAULT=XA,TYPE(NAME=Narayana, PROPERTIES(“databaseName"="jbossts”,“host"="127.0.0.1”)))
ALTER SQL_PARSER RULE SQL_COMMENT_PARSE_ENABLE=xx, PARSE_TREE_CACHE(INITIAL_CAPACITY=xx, MAXIMUM_SIZE=xx, CONCURRENCY_LEVEL=xx), SQL_STATEMENT_CACHE(INITIAL_CAPACITY=xxx, MAXIMUM_SIZE=xxx, CONCURRENCY_LEVEL=xxx)	更新解析引擎规则配置，`SQL_COMMENT_PARSE_ENABLE`：是否解析 SQL 注释，`PARSE_TREE_CACHE`：语法树本地缓存配置，`SQL_STATEMENT_CACHE`：SQL 语句本地缓存配置项	ALTER SQL_PARSER RULE SQL_COMMENT_PARSE_ENABLE=false, PARSE_TREE_CACHE(INITIAL_CAPACITY=10, MAXIMUM_SIZE=11, CONCURRENCY_LEVEL=1), SQL_STATEMENT_CACHE(INITIAL_CAPACITY=11, MAXIMUM_SIZE=11, CONCURRENCY_LEVEL=100)
ALTER TRAFFIC RULE sql_match_traffic (LABLES(xxx),TRAFFIC_ALGORITHM(TYPE(NAME=xxx,PROPERTIES(“key” = “value”))),LOAD_BALANCER(TYPE(NAME=xxx,PROPERTIES(“key"="value”))))	修改双路由规则，`TRAFFIC_ALGORITHM` 支持 `SQL_MATCH` 与 `SQL_HINT` 两种类型 `LOAD_BALANCER` 支持 `RANDOM` 与 `ROUND_ROBIN` 两种类型	ALTER TRAFFIC RULE sql_match_traffic (LABLES(OLTP),TRAFFIC_ALGORITHM(TYPE(NAME=SQL_MATCH,PROPERTIES(“sql” = “SELECT * FROM t_order WHERE order_id = ?; UPDATE t_order SET order_id = ?;"))), LOAD_BALANCER(TYPE(NAME=RANDOM)))
DROP TRAFFIC RULE ruleName [, ruleName]	删除双路由规则	DROP TRAFFIC RULE sql_match_traffic

变量管理 #

在 DistSQL 中，属性配置名称均使用下划线分隔。

语句	说明	示例
SET DIST VARIABLE variable_name = xx	variable_name 为 proxy 的属性配置	SET DIST VARIABLE sql_show = true
SHOW DIST VARIABLES	查询 proxy 所有的属性配置	SHOW DIST VARIABLES
SHOW DIST VARIABLE WHERE name = variable_name	查询指定的 proxy 属性	SHOW DIST VARIABLE WHERE name = sql_show

特殊变量

特殊变量不在 Proxy 的属性配置中，但可以通过 DistSQL 动态管理：

语句	说明	示例
SHOW DIST VARIABLE WHERE name = transaction_type	查询事务类型	SHOW DIST VARIABLE WHERE name = transaction_type
SET DIST VARIABLE transaction_type = xx	修改当前连接的事务类型, 支持 LOCAL，XA，BASE	SET DIST VARIABLE transaction_type = XA
SHOW DIST VARIABLE WHERE name = agent_plugins_enabled	查询 agent 插件启用状态	SHOW DIST VARIABLE WHERE name = agent_plugins_enabled
SET DIST VARIABLE agent_plugins_enabled = xx	设置 agent 插件的启用状态，值为布尔型	SET DIST VARIABLE agent_plugins_enabled = true
SHOW DIST VARIABLE WHERE name = cached_connections	查询当前缓存的连接数量	SHOW DIST VARIABLE WHERE name = cached_connections
SHOW DIST VARIABLE WHERE name = general_query_log	查询全量日志开启状态	SHOW DIST VARIABLE WHERE name = general_query_log
SET DIST VARIABLE general_query_log = xx	设置全量日志开启状态	SET DIST VARIABLE general_query_log = true
SHOW DIST VARIABLE WHERE name = slow_query_log	查询慢日志开启状态	SHOW DIST VARIABLE WHERE name = slow_query_log
SET DIST VARIABLE slow_query_log = xx	设置慢日志开启状态	SET DIST VARIABLE slow_query_log = true
SHOW DIST VARIABLE WHERE name = long_query_time	查询慢查询阈值	SHOW DIST VARIABLE WHERE name = long_query_time
SET DIST VARIABLE long_query_time = xx	设置慢查询阈值，单位为毫秒	SET DIST VARIABLE long_query_time = 5000

其他 #

语句	说明	示例
SHOW COMPUTE NODE INFO	查询当前 proxy 的实例信息	SHOW COMPUTE NODE INFO
SHOW COMPUTE NODE MODE	查询当前 proxy 的 mode 配置	SHOW COMPUTE NODE MODE
LABEL COMPUTE NODE instanceId WITH labelName [ , labelName ]	为实例添加标签	LABEL COMPUTE NODE 10.7.7.136@3309 with olap,group_by
UNLABEL COMPUTE NODE instanceId	移除实例的所有标签	UNLABEL COMPUTE NODE 10.7.7.136@3309
UNLABEL COMPUTE NODE instanceId WITH labelName	为实例移除指定的标签	UNLABEL COMPUTE NODE 10.7.7.136@3309 WITH group_by
RELABEL COMPUTE NODE instanceId with labelName [ , labelName ]	为实例重新添加标签（覆盖原有的标签）	RELABEL COMPUTE NODE 10.7.7.136@3309 with label_9, label_0
REFRESH TABLE METADATA	刷新所有表的元数据	REFRESH TABLE METADATA
REFRESH TABLE METADATA tableName	刷新指定表的元数据	REFRESH TABLE METADATA t_order
REFRESH TABLE METADATA tableName FROM STORAGE UNIT storageUnitName	刷新指定数据源中表的元数据	REFRESH TABLE METADATA t_order FROM STORAGE UNIT ds_1
REFRESH TABLE METADATA FROM STORAGE UNIT storageUnitName SCHEMA schemaName	刷新指定 schema 中表的元数据，如果 schema 中不存在表，则会删除该 schema	REFRESH TABLE METADATA FROM STORAGE UNIT ds_1 SCHEMA db_schema
SHOW TABLE METADATA tableName [, tableName] …	查询表的元数据	SHOW TABLE METADATA t_order
EXPORT DATABASE CONFIGURATION [FROM databaseName] [TO FILE “filePath”]	将 database 中的数据源和规则配置导出为 YAML 格式	EXPORT DATABASE CONFIGURATION TO FILE “/xxx/config-sharding.yaml”
IMPORT DATABASE CONFIGURATION FROM FILE “filePath”	将 YAML 中的配置导入到 database 中，仅支持对空库进行导入操作	IMPORT DATABASE CONFIGURATION FROM FILE “/xxx/config-sharding.yaml”
SHOW RULES USED STORAGE UNIT storageUnitName [FROM databaseName]	查询 database 中使用指定数据源的规则	SHOW RULES USED STORAGE UNIT ds_0 FROM databaseName
select [,string] … [,] decrypt({tableName}, {encryptColumn}, {encryptValue}), [,string] …	查询解密结果，跟 select 用法一致，提供 decrypt() 函数，tableName 为需要解密的表，encryptColumn 为需要解密的列， encryptValue 为需要解密的真实值。使用时需要配置对应的加密规则。	SELECT ‘Lm04PRW+sp+mG/QuimAUew==’, decrypt(t_user, pwd_cipher, ‘Lm04PRW+sp+mG/QuimAUew==’) UNION SELECT ‘XXX’, decrypt(t_user, pwd_cipher, ‘XXX’);

注意事项 #

DBPlusEngine-Proxy 默认不支持 hint，如需支持，请在 conf/server.yaml 中，将 properties 的属性 proxy-hint-enabled 设置为 true。