分库分表设计阶段的一个重要考虑的因素是分库分表的数量以及后期扩容问题。
建议根据业务5-10年的发展，一次性确定表的数量，避免后期增加分表数量；如果后期要改变表的数量，数据迁移难度较大（会涉及到记录级别的迁移）；
如果我们规划1024张表，初期可以1个实例，32个库，每个库32张表；
后期随着业务发展，可以将32个库切分到不同实例，甚至将每张表切到不同的实例，最多切1024个实例，对应1024个库，每个库一个表。
扩容的一个原则是只能表级迁移，避免记录级迁移。
一个普遍的规则是(假设分1024张表，初期16个库)：库id = 分片键%16，表id = int(分片键/16) % 64
下面以sharding-proxy为例，说明这种情况下的扩容过程。
为了便于展示，假设一共16张表，初期4个库，每个库4张表，后期扩容为8个库，每个库2张表。
扩容前
datasource配置：
ds_0: !!org.apache.shardingsphere.orchestration.core.configuration.YamlDataSourceConfigurationdataSourceClassName: com.zaxxer.hikari.HikariDataSourceproperties:jdbcUrl: jdbc:mysql://10.40.12.21:9999/db0?serverTimezone=UTC&useSSL=falseusername: lbadminpassword: lbadminconnectionTimeout: 30000idleTimeout: 60000maxLifetime: 0maxPoolSize: 50minPoolSize: 1maintenanceIntervalMilliseconds: 30000readOnly: false
ds_1: !!org.apache.shardingsphere.orchestration.core.configuration.YamlDataSourceConfigurationdataSourceClassName: com.zaxxer.hikari.HikariDataSourceproperties:jdbcUrl: jdbc:mysql://10.40.12.21:9999/db1?serverTimezone=UTC&useSSL=false……
ds_2: !!org.apache.shardingsphere.orchestration.core.configuration.YamlDataSourceConfigurationdataSourceClassName: com.zaxxer.hikari.HikariDataSourceproperties:jdbcUrl: jdbc:mysql://10.40.12.21:9999/db2?serverTimezone=UTC&useSSL=false……
ds_3: !!org.apache.shardingsphere.orchestration.core.configuration.YamlDataSourceConfigurationdataSourceClassName: com.zaxxer.hikari.HikariDataSourceproperties:jdbcUrl: jdbc:mysql://10.40.12.21:9999/db3?serverTimezone=UTC&useSSL=false……rule配置：
defaultDatabaseStrategy:inline:algorithmExpression: ds_${user_id % 4}shardingColumn: user_id
tables:t_order:actualDataNodes: ds_${0..3}.t_order_${0..3}keyGenerator:column: order_idtype: SNOWFLAKElogicTable: t_ordertableStrategy:inline:algorithmExpression: t_order_${user_id.intdiv(4) % 4}shardingColumn: user_id通过sharding-proxy建表初始化数据
drop table t_order;CREATE TABLE `t_order` (`order_id` bigint(20) DEFAULT NULL,`user_id` int(11) DEFAULT NULL,PRIMARY key(order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;insert into t_order(user_id) values(1);
insert into t_order(user_id) values(2);
insert into t_order(user_id) values(3);
insert into t_order(user_id) values(4);
insert into t_order(user_id) values(5);
insert into t_order(user_id) values(6);
insert into t_order(user_id) values(7);
insert into t_order(user_id) values(8);
insert into t_order(user_id) values(9);
insert into t_order(user_id) values(10);
insert into t_order(user_id) values(11);
insert into t_order(user_id) values(12);
insert into t_order(user_id) values(13);
insert into t_order(user_id) values(14);
insert into t_order(user_id) values(15);
insert into t_order(user_id) values(16);user_id数据的分布：
tab db user_id
t_order_0 db0 16
t_order_0 db1 1
t_order_0 db2 2
t_order_0 db3 3
t_order_1 db0 4
t_order_1 db1 5
t_order_1 db2 6
t_order_1 db3 7
t_order_2 db0 8
t_order_2 db1 9
t_order_2 db2 10
t_order_2 db3 11
t_order_3 db0 12
t_order_3 db1 13
t_order_3 db2 14
t_order_3 db3 15扩容后
模拟扩容，8个库，每个库2张表：
datasource配置：
增加：
ds_4: !!org.apache.shardingsphere.orchestration.core.configuration.YamlDataSourceConfigurationdataSourceClassName: com.zaxxer.hikari.HikariDataSourceproperties:jdbcUrl: jdbc:mysql://10.40.12.21:9999/db4?serverTimezone=UTC&useSSL=false……
ds_5: !!org.apache.shardingsphere.orchestration.core.configuration.YamlDataSourceConfigurationdataSourceClassName: com.zaxxer.hikari.HikariDataSourceproperties:jdbcUrl: jdbc:mysql://10.40.12.21:9999/db5?serverTimezone=UTC&useSSL=false……
ds_6: !!org.apache.shardingsphere.orchestration.core.configuration.YamlDataSourceConfigurationdataSourceClassName: com.zaxxer.hikari.HikariDataSourceproperties:jdbcUrl: jdbc:mysql://10.40.12.21:9999/db6?serverTimezone=UTC&useSSL=false……
ds_7: !!org.apache.shardingsphere.orchestration.core.configuration.YamlDataSourceConfigurationdataSourceClassName: com.zaxxer.hikari.HikariDataSourceproperties:jdbcUrl: jdbc:mysql://10.40.12.21:9999/db7?serverTimezone=UTC&useSSL=false……
rule配置：
defaultDatabaseStrategy:inline:algorithmExpression: ds_${user_id % 8}shardingColumn: user_id
tables:t_order:actualDataNodes: ds_${0..7}.t_order_${0..7}keyGenerator:column: order_idtype: SNOWFLAKElogicTable: t_ordertableStrategy:inline:algorithmExpression: t_order_${user_id.intdiv(8) % 2}shardingColumn: user_id
重建表并初始化数据：
user_id数据的分布：
tab db user_id
t_order_0 db0 16
t_order_0 db1 1
t_order_0 db2 2
t_order_0 db3 3
t_order_0 db4 4
t_order_0 db5 5
t_order_0 db6 6
t_order_0 db7 7
t_order_1 db0 8
t_order_1 db1 9
t_order_1 db2 10
t_order_1 db3 11
t_order_1 db4 12
t_order_1 db5 13
t_order_1 db6 14
t_order_1 db7 15数据迁移示意图
总之
扩容前：4个库，每个库4张表，库id = 分片键%4，表id = int(分片键/4) % 4
扩容后：8个库，每个库2张表，库id = 分片键%8，表id = int(分片键/8) % 2