ShardingSphere

简介

分片相关概念：https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/

运维

数据库选择与查询

show databases;
use db_name;
SELECT DATABASE();
show tables;
SHOW TABLES LIKE 'table_name';

存储单元维护

https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/distsql/syntax/rdl/storage-unit-definition/alter-storage-unit/

# 存储单元查询（底层数据库）
show storage units;
# 注册存储单元
REGISTER STORAGE UNIT ds_0 (
    HOST="127.0.0.1",
    PORT=3306,
    DB="db_0",
    USER="root",
    PASSWORD="root"
);
# 取消注册存储单元
UNREGISTER STORAGE UNIT ds_0;
# 修改存储单元
ALTER STORAGE UNIT ds_2 (
    URL="jdbc:mysql://127.0.0.1:3306/db_2?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"=10,"idleTimeout"="30000")
);

分片规则维护

# 查询分片规则
SHOW SHARDING TABLE RULES;
SHOW SHARDING TABLE RULE t_order;
# 当前逻辑库的分片算法
SHOW SHARDING ALGORITHMS;
SHOW DEFAULT SHARDING STRATEGY;
# 默认分片规则
CREATE DEFAULT SHARDING DATABASE STRATEGY IF NOT EXISTS (
    TYPE="standard", SHARDING_COLUMN=user_id, SHARDING_ALGORITHM(TYPE(NAME=inline, PROPERTIES("algorithm-expression"="ds_${user_id % 8}")))
);
# 添加分片规则
CREATE SHARDING TABLE RULE IF NOT EXISTS t_order (
    DATANODES("ds_${0..7}.t_order")
);
# 修改分片规则
ALTER SHARDING TABLE RULE t_order (
    STORAGE_UNITS(ds_0,ds_1,ds_2,ds_3),
	SHARDING_COLUMN=user_id, TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="16")),
	DATABASE_STRATEGY(TYPE="standard", SHARDING_COLUMN=user_id, SHARDING_ALGORITHM(TYPE(NAME="inline", PROPERTIES("algorithm-expression"="ds_${user_id % 8}")))),
	KEY_GENERATE_STRATEGY(COLUMN=order_id, TYPE(NAME="snowflake"))
);
# 删除分片规则
DROP SHARDING TABLE RULE IF EXISTS t_order;