存储单元 | SphereEx-DBPlusSuite

存储单元 #

语法	描述	类型
REGISTER STORAGE UNIT storageUnitDefinition [, storageUnitDefinition] …	注册存储单元	RDL
ALTER STORAGE UNIT storageUnitDefinition [, storageUnitDefinition] …	调整存储单元	RDL
UNREGISTER STORAGE UNIT storageUnitName [, storageUnitName] … [ignore single tables]	注销存储单元	RDL
SHOW STORAGE UNITS [ [FROM databaseName] [WHERE USAGE_COUNT = usageCount] ]	查询存储单元	RQL
SHOW UNUSED STORAGE UNITS	查询未使用的存储单元	RQL
SHOW LOADING METADATA LIST	查询元数据加载进度	RQL

1. 注册存储单元 #

REGISTER STORAGE UNIT storageUnitDefinition [, storageUnitDefinition] ...

storageUnitDefinition:
    simpleSource | urlSource

simpleSource:
    storageUnitName(HOST=hostname,PORT=port,DB=dbName,USER=user [,PASSWORD=password] [,PROPERTIES(property [,property]) ...])

urlSource:
    storageUnitName(URL=url,USER=user [,PASSWORD=password] [,PROPERTIES(property [,property]) ...])

property:
    key=value

参数说明

名称	数据类型	说明
storageUnitName	IDENTIFIER	存储单元名称
hostname	STRING	数据源地址
port	INT	数据源端口
dbName	STRING	物理库名称
url	STRING	URL 地址
user	STRING	用户名
password	STRING	密码

添加存储单元前请确认已经创建分布式数据库，并执行 use 命令成功选择一个数据库；
确认将要添加或修改的存储单元是可以正常连接的，否则将不能操作成功；
不允许重复的 storageUnitName ；
PROPERTIES 用于自定义连接池参数，key 和 value 均为 STRING 类型；
ALTER STORAGE UNIT 修改存储单元时不允许改变该存储单元关联的真实数据源；
ALTER STORAGE UNIT 修改存储单元时会发生连接池的切换，此操作可能对进行中的业务造成影响，请谨慎使用；
UNREGISTER STORAGE UNIT 只会删除逻辑存储单元，不会删除真实的数据源；
被规则引用的存储单元将无法被删除；
若存储单元只被 single table rule 引用，且用户确认可以忽略该限制，则可以添加可选参数 ignore single tables 进行强制删除。
1.5.0版本之后注册存储单元的逻辑发生变化，注册存储单元不会自动加载单表。如需加载单表请参考单表加载语法

示例

REGISTER STORAGE UNIT ds_0 (
    HOST="127.0.0.1",
    PORT=3306,
    DB="db0",
    USER="root",
    PASSWORD="root"
),ds_1 (
    HOST="127.0.0.1",
    PORT=3306,
    DB="db1",
    USER="root"
),ds_2 (
    HOST="127.0.0.1",
    PORT=3306,
    DB="db2",
    USER="root",
    PROPERTIES("maximumPoolSize"="10")
),ds_3 (
    URL="jdbc:mysql://127.0.0.1:3306/db3?serverTimezone=UTC&useSSL=false",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

2. 修改存储单元 #

ALTER STORAGE UNIT storageUnitDefinition [, storageUnitDefinition] ...
storageUnitDefinition:
    simpleSource | urlSource

simpleSource:
    storageUnitName(HOST=hostname,PORT=port,DB=dbName,USER=user [,PASSWORD=password] [,PROPERTIES(property [,property]) ...])

urlSource:
    storageUnitName(URL=url,USER=user [,PASSWORD=password] [,PROPERTIES(property [,property]) ...])

property:
    key=value

参数说明

名称	数据类型	说明
storageUnitName	IDENTIFIER	存储单元名称
hostname	STRING	数据源地址
port	INT	数据源端口
dbName	STRING	物理库名称
url	STRING	URL 地址
user	STRING	用户名
password	STRING	密码

示例

ALTER STORAGE UNIT ds_0 (
    HOST="127.0.0.1",
    PORT=3309,
    DB="db0",
    USER="root",
    PASSWORD="root"
),ds_1 (
    URL="jdbc:mysql://127.0.0.1:3309/db1?serverTimezone=UTC&useSSL=false",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

3. 注销存储单元 #

UNREGISTER STORAGE UNIT storageUnitName [, storageUnitName] ... [ignore single tables]

示例

UNREGISTER STORAGE UNIT ds_0, ds_1;
UNREGISTER STORAGE UNIT ds_2, ds_3 ignore single tables;

4. 查询存储单元 #

SHOW STORAGE UNITS [ [FROM databaseName] | [WHERE USAGE_COUNT = usageCount] ]

示例

查看当前逻辑库下的所有存储单元：

mysql> SHOW STORAGE UNITS;
+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name | type  | host      | port | db   | connection_timeout_milliseconds | idle_timeout_milliseconds | max_lifetime_milliseconds | max_pool_size | min_pool_size | read_only | other_attributes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_0 | MySQL | 127.0.0.1 | 3306 | db_0 | 30000                           | 60000                     | 1800000                   | 50             | 1             | false     | {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"poolName":"HikariPool-1","registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |
| ds_1 | MySQL | 127.0.0.1 | 3306 | db_1 | 30000                           | 60000                     | 1800000                   | 50             | 1             | false     | {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"poolName":"HikariPool-2","registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |
+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.84 sec)

查看指定逻辑库中的存储单元

mysql> SHOW STORAGE UNITS FROM sharding_db;
+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name | type  | host      | port | db   | connection_timeout_milliseconds | idle_timeout_milliseconds | max_lifetime_milliseconds | max_pool_size | min_pool_size | read_only | other_attributes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_0 | MySQL | 127.0.0.1 | 3306 | db_0 | 30000                           | 60000                     | 1800000                   | 50             | 1             | false     | {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"poolName":"HikariPool-1","registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |
| ds_1 | MySQL | 127.0.0.1 | 3306 | db_1 | 30000                           | 60000                     | 1800000                   | 50             | 1             | false     | {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"poolName":"HikariPool-2","registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.84 sec)

输出说明

列	说明
name	数据源名称
type	数据源类型
host	数据源地址
port	数据源端口
db	数据库名称
attribute	数据源参数

5. 查看当前逻辑库下的未被使用的存储单元 #

SHOW UNUSED STORAGE UNITS

示例

SHOW UNUSED STORAGE UNITS

6. 查询元数据加载进度 #

当用户注册存储单元且该数据源中表数量较多时，元数据加载可能需要较多时间，此时用户可在另一个会话中

SHOW LOADING METADATA LIST

示例

mysql> SHOW LOADING METADATA LIST;
+-------------------+--------------------------------------+-------+-----------+
| storage_unit_name | instance_id                          | total | completed |
+-------------------+--------------------------------------+-------+-----------+
| ds_0              | 9b05aa47-5e72-4603-8641-375e1f78e9bb | 129   | 118       |
+-------------------+--------------------------------------+-------+-----------+
1 rows in set (0.01 sec)

输出说明

列	说明
storage_unit_name	存储单元名称
instance_id	实例 id
total	表总数
completed	已加载总数

特别说明 #

由于 Proxy 不支持 Oracle 协议，使用 Oracle 数据库的用户可将 Proxy 前端协议（proxy-frontend-database-protocol-type）设置为 MySQL，并注册 Oracle 存储单元：

REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:oracle:thin:@//127.0.0.1:1521/helowin",
    USER="system",
    PASSWORD="oracle",
    PROPERTIES("maximumPoolSize"=10)
);