采用MyCAT的多租户方案 - 按SQL注解路由

2019独角兽企业重金招聘Python工程师标准>>>

1. 方案示意图

2. 创建物理库

以root账号登录mariaDB, 创建三个schema: db_10001、db_10013、db_10020， 分别用来存放商户10001、10013、10020的数据:

C:\Users\HuQingmiao>mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.16-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database db_10001;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database db_10013;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database db_10020;
Query OK, 1 row affected (0.00 sec)

在以上三个schema上分别执行建表语句（也可以在配置mycat 逻辑库后通过mycat执行）:

DROP TABLE if EXISTS tenant_user;
CREATE TABLE tenant_user
(
    id      BIGINT UNSIGNED   NOT NULL,
    -- tenant_id     INT(10)         NOT NULL     , //注意，不需要此列
    name     	    VARCHAR(255)    NOT NULL     ,
    mbl_nbr       VARCHAR(15)     NOT NULL     ,
    idcard_no	    VARCHAR(15),
    gender        CHAR (1),
  PRIMARY KEY(id)
) ;

DROP TABLE  if EXISTS dict ;
CREATE TABLE dict
(
   id         BIGINT UNSIGNED AUTO_INCREMENT  NOT NULL,
   kind             VARCHAR(30) not null comment '类别',
   kind_desc        VARCHAR(45) comment '类别描述',
   code             VARCHAR(30) not null comment '编码',
   name             VARCHAR(250) not null comment '名称',
   order_by         SMALLINT not null comment '排序号',
   status           SMALLINT not null comment '0-作废   1-有效',
   PRIMARY KEY (id)
);

注意， 对于需要水平切分的表，不能设置自增主键，否则不同结点会存在相同主键值； 另外，对于global类型的表，也最好不用要自增主键，并发时会造成冲键冲突。

3. 配置逻辑库

按上图，打开${MYCAT_HOME}/conf/schema.xml，创建三个逻辑库、配置相应的datanode：

<schema name="logic_10001" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_10001">
	<table name="dict" primaryKey="id"  type="global" dataNode="dn_10001,dn_10013,dn_10020" />
</schema>
<schema name="logic_10013" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_10013">
	<table name="dict" primaryKey="id"  type="global" dataNode=""dn_10001,dn_10013,dn_10020" />
</schema>
<schema name="logic_10020" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_10020">
	<table name="dict" primaryKey="id"  type="global" dataNode=""dn_10001,dn_10013,dn_10020" />
</schema>

<dataNode name="dn_10001" dataHost="localhost1" database="db_10001" />
<dataNode name="dn_10013" dataHost="localhost1" database="db_10013" />
<dataNode name="dn_10020" dataHost="localhost1" database="db_10020" />

打开${MYCAT_HOME}/conf/server.xml，给用户root授权三个逻辑库的权限：

 <user name="root">
	<property name="password">123456</property>
	<property name="schemas">TESTDB,logic_10001,logic_10013,logic_10020</property>
 </user>

4. 读写测试

重启、并登录mycat：

C:\Users\HuQingmiao>mycat restart
wrapper  | Stopping the Mycat-server service...
wrapper  | Mycat-server stopped.
wrapper  | Starting the Mycat-server service...
wrapper  | Mycat-server started.

C:\Users\HuQingmiao>mysql -uroot -p123456 -hlocalhost -P8066 -DTESTDB
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

写入数据， 注意 要在原生sql前加上注解以告诉mycat操作哪个逻辑库，即：

       /*!mycat:schema = 逻辑库 */  原生sql

执行下列脚本：

/*!mycat:schema = logic_10001 */
insert into tenant_user (id, name, mbl_nbr, idcard_no )
values (1,  '牟森林', '13223232532', null);

/*!mycat:schema = logic_10013 */
insert into tenant_user (id, name, mbl_nbr, idcard_no )
values (2,  '牟森林', '13223232532', null);

/*!mycat:schema = logic_10020 */
insert into tenant_user (id, name, mbl_nbr, idcard_no )
values (3,  '胡三', '13520235368', null);


/*!mycat:schema = logic_10001 */
insert into dict (id, kind, kind_desc, code, name, order_by,status )
values (1,'tenant_entp_status', '企业信息状态', '1', '待认证', 1, 1);

/*!mycat:schema = logic_10001 */
insert into dict (id,kind, kind_desc, code, name, order_by,status )
values (3,'tenant_entp_status', '企业信息状态', '5', '已认证',  5,  1);

commit;

查看数据:

MySQL [TESTDB]> /*!mycat:schema = logic_10001 */
    ->  SELECT * FROM tenant_user;
+----+--------+-------------+-----------+--------+
| id | name   | mbl_nbr     | idcard_no | gender |
+----+--------+-------------+-----------+--------+
|  1 | 牟森林 | 13223232532 | NULL      | NULL   |
+----+--------+-------------+-----------+--------+
1 row in set (0.00 sec)

MySQL [TESTDB]> /*!mycat:schema =logic_10001 */
    -> SELECT * FROM dict;
+----+--------------------+--------------+------+----------+----------+--------+
| id | kind               | kind_desc    | code | name     | order_by | status |
+----+--------------------+--------------+------+----------+----------+--------+
|  1 | tenant_entp_status | 企业信息状态 | 1    | 待认证   |        1 |      1 |
|  3 | tenant_entp_status | 企业信息状态 | 5    | 已认证   |        5 |      1 |
+----+--------------------+--------------+------+----------+----------+--------+
3 rows in set (0.00 sec)
	
MySQL [TESTDB]> /*!mycat:schema =logic_10013 */
    ->  SELECT * FROM tenant_user;
+----+--------+-------------+-----------+--------+
| id | name   | mbl_nbr     | idcard_no | gender |
+----+--------+-------------+-----------+--------+
|  2 | 牟森林 | 13223232532 | NULL      | NULL   |
+----+--------+-------------+-----------+--------+
1 row in set (0.00 sec)

MySQL [TESTDB]> /*!mycat:schema =logic_10013 */
    -> SELECT * FROM dict;
+----+--------------------+--------------+------+----------+----------+--------+
| id | kind               | kind_desc    | code | name     | order_by | status |
+----+--------------------+--------------+------+----------+----------+--------+
|  1 | tenant_entp_status | 企业信息状态 | 1    | 待认证   |        1 |      1 |
|  3 | tenant_entp_status | 企业信息状态 | 5    | 已认证   |        5 |      1 |
+----+--------------------+--------------+------+----------+----------+--------+
3 rows in set (0.00 sec)
	
MySQL [TESTDB]> /*!mycat:schema = logic_10020 */
    ->  SELECT * FROM tenant_user;
+----+------+-------------+-----------+--------+
| id | name | mbl_nbr     | idcard_no | gender |
+----+------+-------------+-----------+--------+
|  3 | 胡三 | 13520235368 | NULL      | NULL   |
+----+------+-------------+-----------+--------+
1 row in set (0.00 sec)

MySQL [TESTDB]>  /*!mycat:schema = logic_10020 */
    -> SELECT * FROM dict;
+----+--------------------+--------------+------+----------+----------+--------+
| id | kind               | kind_desc    | code | name     | order_by | status |
+----+--------------------+--------------+------+----------+----------+--------+
|  1 | tenant_entp_status | 企业信息状态 | 1    | 待认证   |        1 |      1 |
|  3 | tenant_entp_status | 企业信息状态 | 5    | 已认证   |        5 |      1 |
+----+--------------------+--------------+------+----------+----------+--------+
3 rows in set (0.00 sec)

5. 验证切分效果

现在登录本地mariaDB各个物理库，检查数据分布：

C:\Users\HuQingmiao>mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 614
Server version: 10.1.16-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select * from db_10001.tenant_user;
+----+-----------+--------+-------------+-----------+--------+
| id | tenant_id | name   | mbl_nbr     | idcard_no | gender |
+----+-----------+--------+-------------+-----------+--------+
|  1 |     10001 | 牟森林 | 13223232532 | NULL      | NULL   |
+----+-----------+--------+-------------+-----------+--------+
1 row in set (0.00 sec)

MariaDB [(none)]> select * from db_10001.dict;
+----+--------------------+--------------+------+----------+----------+--------+
| id | kind               | kind_desc    | code | name     | order_by | status |
+----+--------------------+--------------+------+----------+----------+--------+
|  1 | tenant_entp_status | 企业信息状态 | 1    | 待认证   |        1 |      1 |
|  3 | tenant_entp_status | 企业信息状态 | 5    | 已认证   |        5 |      1 |
+----+--------------------+--------------+------+----------+----------+--------+
3 rows in set (0.00 sec)

MariaDB [(none)]> select * from db_10013.tenant_user;
+----+-----------+--------+-------------+-----------+--------+
| id | tenant_id | name   | mbl_nbr     | idcard_no | gender |
+----+-----------+--------+-------------+-----------+--------+
|  2 |     10013 | 牟森林 | 13223232532 | NULL      | NULL   |
+----+-----------+--------+-------------+-----------+--------+
1 row in set (0.00 sec)

MariaDB [(none)]> select * from db_10013.dict;
+----+--------------------+--------------+------+----------+----------+--------+
| id | kind               | kind_desc    | code | name     | order_by | status |
+----+--------------------+--------------+------+----------+----------+--------+
|  1 | tenant_entp_status | 企业信息状态 | 1    | 待认证   |        1 |      1 |
|  3 | tenant_entp_status | 企业信息状态 | 5    | 已认证   |        5 |      1 |
+----+--------------------+--------------+------+----------+----------+--------+
3 rows in set (0.00 sec)

6. 修改应用程序

接下来，要对应用程序做以下两处修改：

1） 引入类TenantContextHolder.java， 然后在受理请求的方法中， 在接收入参后、处理业务前， 加上一行代码以把租户id暂存到当前线程变量（ThreadLocal）中， 并且在处理完成、返回给调用者前， 把租户id从当前线程变量中移除，代码如下：

       public String realriskEventDecision（Long tenantId, Long appId, String event）{
              // 把租户id暂存到 ThreadLocal
	         TenantContextHolder.setTenant(tenant); 

              // 处理业务逻辑
              do some business..  
        
              // 把租户id从 ThreadLocal移除
              TenantContextHolder.remove(); 
              
              return ..
       }

2） 引入TenantSQLInterceptor类， 在mybatis配置文件中，增加以下内容：

     <plugins>
          <plugin interceptor="com.github.walker.mycat.demo.common.support.TenantSQLInterceptor" />
     </plugins>

TenantSQLInterceptor将拦截sql, 向其添加头部注解， 以标明该sql应该发往哪个逻辑库执行； 它会调用TenantContextHolder.getSchema()以获得逻辑库名。

7. 方案总结

本方案适合把已有系统升级为saas服务， 只需要修改少量的代码， 即通过aop在请求入口处记录tenant_id，再通过sql拦截器获取tenant_id对应的逻辑库，然后在sql 头部添加mycat特有的注解以指明在哪个逻辑库执行。

转载于:https://my.oschina.net/HuQingmiao/blog/1518073