基于Mycat的多租户分库方案

方案一： 租户共享MyCat中的schema，schema中的表会跨越多个datanode，因此每个表应该指定primary key, sharding rule可以解析primary key中包含的租户code，从而进一步确定每个租户对应的datanode.这就要求每个表的主键生成必须要主键生成器来生成(key generator),主键生成器要满足以下要求：

1. 主键生成效率高
2. 生成的主键全局无冲突
3. 生成的主键要包含租户code信息，并可被反向解析出来

方案二： 每个租户独占MyCat中的一个schema，schema的表不会跨datanode，类似的拓扑如下：

MyCat核心配置：

1. server.xml

<user name="root"> <property name="password">password</property> <property name="schemas"> GLOBALDB,JG1DB,JG2DB,JG3DB,JG4DB,JG5DB </property> </user>

2. schema.xml

<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="GLOBALDB" checkSQLschema="false" sqlMaxLimit="100"> <!-- global table is auto cloned to all defined data nodes ,so can join with any table whose sharding node is in the same data node --> <table name="orgmapping" primaryKey="id" type="global" dataNode="gdn" /> </schema> <schema name="JG1DB" checkSQLschema="false" sqlMaxLimit="100"> <table name="user" primaryKey="id" autoIncrement="true" dataNode="jg1dn" /> <table name="user_order" primaryKey="id" autoIncrement="true" dataNode="jg1dn" /> </schema> <schema name="JG2DB" checkSQLschema="false" sqlMaxLimit="100"> <table name="user" primaryKey="id" autoIncrement="true" dataNode="jg2dn" /> <table name="user_order" primaryKey="id" autoIncrement="true" dataNode="jg2dn" /> </schema> <schema name="JG3DB" checkSQLschema="false" sqlMaxLimit="100"> <table name="user" primaryKey="id" autoIncrement="true" dataNode="jg3dn" /> <table name="user_order" primaryKey="id" autoIncrement="true" dataNode="jg3dn" /> </schema> <schema name="JG4DB" checkSQLschema="false" sqlMaxLimit="100"> <table name="user" primaryKey="id" autoIncrement="true" dataNode="jg4dn" /> <table name="user_order" primaryKey="id" autoIncrement="true" dataNode="jg4dn" /> </schema> <schema name="JG5DB" checkSQLschema="false" sqlMaxLimit="100"> <table name="user" primaryKey="id" autoIncrement="true" dataNode="jg5dn" /> <table name="user_order" primaryKey="id" autoIncrement="true" dataNode="jg5dn" /> </schema> <dataNode name="gdn" dataHost="globalhost" database="wymglobal" /> <dataNode name="jg1dn" dataHost="g1host" database="jg1" /> <dataNode name="jg2dn" dataHost="g1host" database="jg2" /> <dataNode name="jg3dn" dataHost="g2host" database="jg3" /> <dataNode name="jg4dn" dataHost="g2host" database="jg4" /> <dataNode name="jg5dn" dataHost="g2host" database="jg5" /> <dataHost name="globalhost" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="192.168.0.199:3306" user="root" password="password"> </writeHost> </dataHost> <dataHost name="g1host" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="192.168.1.13:3306" user="root" password="password"> </writeHost> </dataHost> <dataHost name="g2host" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="192.168.1.142:3306" user="root" password="password"> </writeHost> </dataHost> </mycat:schema>

验证方案： 利用Spring boot jdbc 写测试程序：

1. 在src/main/resources/application.yml中定义数据源

logging: level: org.springframework: INFO com.wym: DEBUG ################### DataSource Configuration ########################## spring: application: name: gs-relational-data-access datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:8066 username: root password: password initialize: false init-db: false 2. 在DatasourceConfig.java中定义Datasource Bean和JdbcTemplate Bean

package com.wym.mycatdemo;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.env.Environment; import org.springframework.jdbc.core.JdbcTemplate;

import com.alibaba.druid.pool.DruidDataSource;

@Configuration public class DatasourceConfig {

@Autowired private Environment env;

@Bean(name = "dataSource") public DataSource dataSource() { DruidDataSource dataSource = new DruidDataSource(); dataSource.setUrl(env.getProperty("spring.datasource.url")); dataSource.setUsername(env.getProperty("spring.datasource.username"));// 用户名 dataSource.setPassword(env.getProperty("spring.datasource.password"));// 密码 dataSource.setInitialSize(2); dataSource.setMaxActive(20); dataSource.setMinIdle(0); dataSource.setMaxWait(60000); dataSource.setValidationQuery("SELECT 1"); dataSource.setTestOnBorrow(false); dataSource.setTestWhileIdle(true); dataSource.setPoolPreparedStatements(false); return dataSource; }

@Bean(name = "businessJdbcTemplate") public JdbcTemplate primaryJdbcTemplate(@Qualifier("dataSource") DataSource dataSource) { return new JdbcTemplate(dataSource); }

} 3. 4个机构数据库中user表的建表语句如下： CREATE TABLE `user` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL COMMENT '用户名', `password` varchar(20) DEFAULT NULL COMMENT '密码', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. 定义User.java import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor;

@Data @NoArgsConstructor @AllArgsConstructor public class User { private Integer id; private String name; private String password; public User(String name, String password) { this.name = name; this.password = password; } } 5. 定义User的数据库访问对象UserDao.java import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional;

import com.wym.mycatdemo.model.User;

@Repository public class UserDao { public static final String TENANT_SQL_TEMPLATE = "{1}"; @Autowired @Qualifier("businessJdbcTemplate") private JdbcTemplate jdbcTemplate;

@Transactional(readOnly = true) public List<User> findAll(String tenantSchema) { return jdbcTemplate.query( MessageFormat.format(TENANT_SQL_TEMPLATE, tenantSchema, "select * from user"), new UserRowMapper()); }

@Transactional(readOnly = true) public User findUserById(String tenantSchema, int id) { return jdbcTemplate.queryForObject( MessageFormat.format(TENANT_SQL_TEMPLATE, tenantSchema, "select * from user where id=?") , new Object[] { id }, new UserRowMapper()); }

@Transactional public User create(String tenantSchema, final User user) { final String sql = MessageFormat.format(TENANT_SQL_TEMPLATE, tenantSchema, "insert into user(name,password) values(?,?)") ;

KeyHolder holder = new GeneratedKeyHolder();

jdbcTemplate.update(new PreparedStatementCreator() {

@Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setString(1, user.getName()); ps.setString(2, user.getPassword()); return ps; } }, holder);

int newUserId = holder.getKey().intValue(); user.setId(newUserId); return user; }

@Transactional public void delete(String tenantSchema, final Integer id) { final String sql = MessageFormat.format(TENANT_SQL_TEMPLATE, tenantSchema, "delete from user where id=?"); jdbcTemplate.update(sql, new Object[] { id }, new int[] { java.sql.Types.INTEGER }); }

@Transactional public void update(String tenantSchema, final User user) { jdbcTemplate.update( MessageFormat.format(TENANT_SQL_TEMPLATE, tenantSchema, "update user set name=?,password=? where id=?") , new Object[] { user.getName(), user.getPassword(), user.getId() }); }

class UserRowMapper implements RowMapper<User> {

@Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setId(rs.getInt("id")); user.setName(rs.getString("name")); user.setPassword(rs.getString("password")); return user; }

} } 6. 测试内编写 import java.util.List;

import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.wym.mycatdemo.dao.UserDao; import com.wym.mycatdemo.model.User;

@RunWith(SpringJUnit4ClassRunner.class) @SpringBootTest(classes = SpringBootJdbcDemoApplication.class) // 指定spring-boot的启动类

public class SpringBootJdbcDemoApplicationTests {

@Autowired private UserDao userDao;

@Test public void findAllUsers() { List<User> users = userDao.findAll("JG1DB"); System.out.println(users);

}

@Test public void findUserById() { User user = userDao.findUserById("JG2DB",1); System.out.println(user); }

@Test public void updateById() { User user = userDao.findUserById("JG3DB",2); System.out.println(user); User newUser = new User(2, "JackChen", "[email protected]"); userDao.update("JG3DB", newUser); User newUser2 = userDao.findUserById("JG3DB",newUser.getId()); System.out.println(newUser2); }

@Test public void createUser() { User user = new User("rose", "[email protected]"); User savedUser = userDao.create("JG4DB",user); user = userDao.findUserById("JG4DB",savedUser.getId()); System.out.println(user); } @Test public void findAllUsers1() { List<User> users = userDao.findAll("JG5DB"); System.out.println("----------------------------------"+users);

}

} 7. 由运行结果即可得知，数据访问被MyCat正确的路由到各个机构的数据库中。

当然这个只是演示例子，正式项目里一个比较好的思路是：

1. 前端登录验证成功后，租户编码存入cookie中或本地缓存里
2. 前端通过HTTP方式访问后端api时，都需要在header中附带租户编码信息
3. 后端的 Restful API 的 定义2个filter, 比较一个叫prefilter, 一个叫postfilter, prefilter中读取http request中的header里的租户编码信息，并把它写入一个public static 的ThreadLocal对象， postfilter负责ThreadLocal对象的清除
4. DAO层从ThreadLocal对象中抓取租户编码，并把租户编码附加到sql语句头部

踩坑记录：

1. 为每个机构都定义个一个表名叫order的表，但是mycat不认，查了半天，结果发现是由于order是SQL查询关键字造 成的

2.MyCat中的表ID定义成自增长型，而且id自增长配置为 <property name="sequnceHandlerType">2</property>， 文档上说明为本地时间戳算法： ID= 64位二进制 (42(毫秒)+5(机器ID)+5(业务编码)+12(重复累加) 换算成十进制为18位数的long类型，每毫秒可以并发12位二进制的累加 但是MyCAT老是报id冲突，但从日志看生成的id与数据库中已有的并不冲突，查了半天是因为ID的类型是int 32位，生成的id为64的，插入时被截断（可能是保留高位），因此产生冲突。

完整的代码，请访问： https://github.com/tangaiyun/multitenancybymycat