垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。
(1)创建数据库，创建表
CREATE DATABASE `user_db` CHARACTER SET ‘utf8‘ COLLATE ‘utf8_general_ci‘;
USE user_db; DROP TABLE IF EXISTS `t_user` ; CREATE TABLE `t_user` ( `user_id` BIGINT (20) NOT NULL COMMENT ‘用户id‘, `fullname` VARCHAR (255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘用户姓名‘, `user_type` CHAR(1) DEFAULT NULL COMMENT ‘用户类型‘, PRIMARY KEY (`user_id`) USING BTREE ) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
(2)修改Sharding-JDBC规则
# sharding-jdbc分片规则配置 # 数据源，新增m0，对应user_db spring.shardingsphere.datasource.names = m0,m1,m2 # m0数据源 spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m0.driver-class-name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.m0.url = jdbc:mysql://localhost:3306/user_db?useUnicode=true spring.shardingsphere.datasource.m0.username = root spring.shardingsphere.datasource.m0.password = root ...... # t_user分表策略，固定分配至m0的t_user真实表 spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = m0.t_user ...... # 没有分表可以不配置表的分片策略(主键生成策略也可以不配，默认采用数据库的) #spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column = user_id #spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression = t_user
(3)持久层
@Mapper public interface UserDao { /** * 新增用户 * @return * @param fullname 用户姓名 */ @Insert("insert into t_user(fullname) values(#{fullname})") int insertUser(String fullname); /** * 根据id列表查询多个用户 * @return * @param userIds 用户id列表 */ @Select("<script>" + "select" + " * " + " from t_user u " + " where u.user_id in " + " <foreach collection=‘userIds‘ open=‘(‘ separator=‘,‘ close=‘)‘ item=‘id‘> " + " #{id} " + " </foreach>" + "</script>") List<Map> selectUserbyIds(@Param("userIds") List<Long> userIds); }
(4)测试
@Test public void testInsertUser() { for (int i = 1; i <= 10; i++) { userDao.insertUser("姓名" + i); } } @Test public void testSelectUserbyIds() { List<Long> userIds = new ArrayList<>(); userIds.add(1L); userIds.add(2L); List<Map> users = userDao.selectUserbyIds(userIds); System.out.println(users); }
公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。
(1)分别在user_db、order_db_1、order_db_2中创建t_dict表：
CREATE TABLE `t_dict` ( `dict_id` bigint(20) NOT NULL COMMENT ‘字典id‘, `type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘字典类型‘, `code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘字典编码‘, `value` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘字典值‘, PRIMARY KEY (`dict_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
(2)修改Sharding-JDBC规则
# 指定t_dict为公共表 spring.shardingsphere.sharding.broadcast‐tables = t_dict
(3)持久层
@Mapper public interface DictDao { /** * 新增字典 * @param type 字典类型 * @param code 字典编码 * @param value 字典值 * @return */ @Insert("insert into t_dict(type, code, value) values(#{dictId}, #{type}, #{code}, #{value})") int insertDict(String type, String code, String value); /** * 删除字典 * @param dictId 字典id * @return */ @Delete("delete from t_dict where dict_id = #{dictId}") int deleteDict(Long dictId); }
(4)测试
@Test public void testInsertDict() { dictDao.insertDict("user_type", "0", "管理员"); dictDao.insertDict("user_type", "1", "操作员"); } @Test public void testDeleteDict() { dictDao.deleteDict(1L); dictDao.deleteDict(2L); }