简介
分片相关概念: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;