mycat 读写分离+分库分表+全局表

一、mycat读写分离 Mycat的读写分离是建立在Mysq的主从复制的基础上的

#修改配置文件 schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                             /> -->
        <dataNode name="dn1" dataHost="host1" database="test1" />
        <dataHost name="host1" 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.171.135:3306" user="root"
                                   password="123">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.171.146:3306" user="root" password="123" />
                </writeHost>
        </dataHost>
</mycat:schema>

设置有两种，如下： (1) 设置balance="1"与writeType="0"

**Balance参数设置： ** 修改的balance属性，通过此属性配置读写分离的类型 负载均衡类型，目前的取值有4 种： （1） balance="0", 不开启读写分离机制， 所有读操作都发送到当前可用的 writeHost 上。 （2） balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从 模式(M1->S1， M2->S2，并且 M1 与 M2 互为主备)，正常情况下， M2,S1,S2 都参与 select 语句的负载均衡。 （3） balance="2"，所有读操 作都随机的在 writeHost、 readhost 上分发。 （4） balance="3"，所有读请求随机的分发到 readhost 执行， writerHost 不负担读压力

.

WriteType参数设置：

1. writeType=“0”, 所有写操作都发送到可用的writeHost上。
2. writeType=“1”，所有写操作都随机的发送到readHost。
3. writeType=“2”，所有写操作都随机的在writeHost、readhost分上发。

“readHost是从属于writeHost的，即意味着它从那个writeHost获取同步数据，因此，当它所属的writeHost宕机 了，则它也不会再参与到读写分离中来，即“不工作了”，这是因为此时，它的数据已经“不可靠”了。基于这个考 虑，目前mycat 1.3和1.4版本中，若想支持MySQL一主一从的标准配置，并且在主节点宕机的情况下，从节点 还能读取数据，则需要在Mycat里配置为两个writeHost并设置banlance=1。”

(2) 设置switchType="2" 与slaveThreshold="100"

**switchType 目前有三种选择： ** -1：表示不自动切换 1 ：默认值，自动切换 2 ：基于MySQL主从同步的状态决定是否切换

“Mycat心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性： switchType="2" 与 slaveThreshold="100"，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过 检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running" 三个字段来 确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。“ 二、垂直拆分--分库 一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类， 分布到不同 的 数据库上面，这样也就将数据或者说压力分担到不同的库上面， 如何划分表 分库的原则： 有紧密关联关系的 表应该在一个库里，相互没有关联关系的表可以分到不同的库里。

#修改 schema 配置文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
         <table name="customer" dataNode="dn2"></table>
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                             /> -->
        <dataNode name="dn1" dataHost="host1" database="test1" />
        <dataNode name="dn2" dataHost="host2" database="test1" />
        <dataHost name="host1" 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.171.135:3306" user="root"
                                   password="123">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.171.146:3306" user="root" password="123" />
                </writeHost>
        </dataHost>
        <dataHost name="host2" 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="hostM2" url="192.168.171.131:3306" user="root" password="123">  
                </writeHost>
         </dataHost>
</mycat:schema>

#登录到数据库，创建表
[root@mycatconf]#mysql -umycat -p123456 -h192.168.171.134 -P8066
mysql>CREATE TABLE customer( id INT AUTO_INCREMENT, NAME VARCHAR(200), PRIMARY KEY(id) ); 
mysql>CREATE TABLE orders( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id) ); 
mysql>CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) ); 
mysql>CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) ); 
#以上四个表如何分库？客户表分在一个数据库，另外三张都需要关联查询，分在另外一个数据库。 
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| customer        |
| dict_order_type |
| orders          |
| orders_detail   |
| tab1            |
+-----------------+
5 rows in set (0.01 sec)
mysql>show tables;
+----+--------------+
|  customer          |
+----+---------------+

三、水平拆分--分表 相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中， 每个表中 包 含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就 是将表中的某些行切分 到一个数据库，而另外的某些行又切分到其他的数据库中. 实现分表 选择要拆分的表 MySQL 单表存储数据条数是有瓶颈的，单表达到 1000 万条数据就达到了瓶颈，会 影响查询效率， 需要进行水平拆分（分表） 进行优化。 例如：例子中的 orders、 orders_detail 都已经达到 600 万行数据，需要进行分表优化。 分表字段 以 orders 表为例，可以根据不同自字段进行分表

#修改配置文件 schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
         <table name="customer" dataNode="dn2"></table>
         <table name="orders" dataNode="dn1,dn2" rule="mod_rule"> </table>                  # 为 orders 表设置数据节点为 dn1、 dn2， 并指定分片规则为 mod_rule（自定义的名字）
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                                          /> -->
        <dataNode name="dn1" dataHost="host1" database="test1" />
        <dataNode name="dn2" dataHost="host2" database="test1" />
        <dataHost name="host1" 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.171.135:3306" user="root"
                                   password="123">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.171.145:3306" user="root" password="123" />
                </writeHost>
        </dataHost>
        <dataHost name="host2" 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="hostM2" url="192.168.171.131:3306" user="root"
                                 password="123">
                </writeHost>
         </dataHost>
</mycat:schema>

				

#修改配置文件 rule.xml
#在 rule 配置文件里新增分片规则 mod_rule，并指定规则适用字段为 customer_id， 
#还有选择分片算法 mod-long（对字段求模运算） ， customer_id 对两个节点求模，根据结果分 片 
#配置算法 mod-long 参数 count 为 2，两个节点 
 
<tableRule name="mod_rule"> 
<rule>
<columns>customer_id</columns> 
<algorithm>mod-long</algorithm>
</rule> 
</tableRule> 
…
<function name="mod-long" class="io.mycat.route.function.PartitionByMod"> 
<!-- how many data nodes --> 
<property name="count">2</property>           # 将此处原本3修改为2，应为我们只有两个节点
</function> 

**在数据节点 dn2 上建 orders 表 **

mysql> CREATE TABLE orders( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id) );

插入数据，访问 Mycat 实现分片

mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100); 
mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300); 
mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000); 
mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400); 
mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020); 
#mycat上访问
mysql> select * from orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  2 |        101 |         100 | 100300.00 |
|  4 |        101 |         101 | 103000.00 |
|  6 |        102 |         100 | 100020.00 |
|  1 |        101 |         100 | 100100.00 |
|  3 |        101 |         101 | 120000.00 |
|  5 |        102 |         101 | 100400.00 |
+----+------------+-------------+-----------+
6 rows in set (0.02 sec)
#master节点1上访问
mysql> select * from orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  2 |        101 |         100 | 100300.00 |
|  4 |        101 |         101 | 103000.00 |
|  6 |        102 |         100 | 100020.00 |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)
#master节点2上访问
mysql> select * from orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  1 |        101 |         100 | 100100.00 |
|  3 |        101 |         101 | 120000.00 |
|  5 |        102 |         101 | 100400.00 |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

orders和orders_detail内联

#修改配置文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
         <table name="customer" dataNode="dn2"></table>
         <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
                    <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />                # 需要将childtable字段卸载table中间
        </table>
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                                          /> -->
        <dataNode name="dn1" dataHost="host1" database="test1" />
        <dataNode name="dn2" dataHost="host2" database="test1" />
        <dataHost name="host1" 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.171.135:3306" user="root"
                                   password="123">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.171.145:3306" user="root" password="123" />
                </writeHost>
        </dataHost>
        <dataHost name="host2" 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="hostM2" url="192.168.171.131:3306" user="root"
                                 password="123">
                </writeHost>
         </dataHost>
</mycat:schema>

在dn2 创建 orders_detail 表

mysql> CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) );

重启 Mycat 访问 Mycat 向 orders_detail 表插入数据

mysql> insert into orders_detail(id,detail,order_id) values (1,'detail',1); 

mysql> insert into orders_detail(id,detail,order_id) values (2,'detail',2); 
 
mysql> insert into orders_detail(id,detail,order_id) values (3,'detail',3); 
 
mysql> insert into orders_detail(id,detail,order_id) values (4,'detail',4); 
 
mysql> insert into orders_detail(id,detail,order_id) values (5,'detail',5); 
 
mysql> insert into orders_detail(id,detail,order_id) values (6,'detail',6); 
mysql> select * from orders_detail;
+----+--------+----------+
| id | detail | order_id |
+----+--------+----------+
|  2 | detail |        2 |
|  4 | detail |        4 |
|  6 | detail |        6 |
|  1 | detail |        1 |
|  3 | detail |        3 |
|  5 | detail |        5 |
+----+--------+----------+
6 rows in set (0.02 sec)

访问 Mycat 实现内连接

mysql> select o.*,od.detail from orders as o inner join orders_detail as od on o.id=od.order_id; 
+----+------------+-------------+-----------+--------+ 
| id | order_type | customer_id | amount    | detail | 
+----+------------+-------------+-----------+--------+ 
|  1 |        101 |         100 | 100100.00 | detail | 
|  2 |        101 |         100 | 100300.00 | detail | 
|  6 |        102 |         100 | 100020.00 | detail | 
|  3 |        101 |         101 | 120000.00 | detail | 
|  4 |        101 |         101 | 103000.00 | detail | 
|  5 |        102 |         101 | 100400.00 | detail | 
+----+------------+-------------+-----------+--------+ 

四、全局表

在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联， 就成了比较棘手的问题，考虑到字典表具有以下几个特性： ① 变动不频繁 ② 数据量总体变化不大 ③ 数据规模不 大，很少有超过数十万条记录

.

Mycat 定义了一种特殊的表，称之为“全局表”，全局表具有以下特性： ① 全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性 ② 全局表的查询操作，只从 一个节点获取 ③ 全局表可以跟任何一个表进行 JOIN 操作 将字典表或者符合字典表特性的一些表定义为全局 表，则从另外一个方面，很好的解决了数据 JOIN 的难题。 通过全局表+基于 E-R 关系的分片策略， Mycat 可 以满足 80%以上的企业应用开发

#修改 schema.xml 配置文件 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
         <table name="customer" dataNode="dn2"></table>
         <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
                    <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
        </table>
        <table name="dict_order_type" dataNode="dn1,dn2" type="global">  </table>                 # 将字典表添加，并放在dn1和dn2上
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                                          /> -->
        <dataNode name="dn1" dataHost="host1" database="test1" />
        <dataNode name="dn2" dataHost="host2" database="test1" />
        <dataHost name="host1" 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.171.135:3306" user="root"
                                   password="123">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.171.145:3306" user="root" password="123" />
                </writeHost>
        </dataHost>
        <dataHost name="host2" 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="hostM2" url="192.168.171.131:3306" user="root"
                                 password="123">  
                </writeHost>
         </dataHost> 
</mycat:schema>

**在dn2 创建 dict_order_type 表 ** mysql> CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) ); **重启 Mycat，访问 Mycat 向 dict_order_type 表插入数据 **

mysql> insert into dict_order_type (id,order_type) values (101,'type1'); 
mysql> insert into dict_order_type (id,order_type) values (102,'type2'); 
#全局表在每个节点查看都是完整的，不像之前分片分表一半在这一半在那的
mysql> select * from dict_order_type;
+-----+------------+
| id  | order_type |
+-----+------------+
| 101 | type1      |
| 102 | type2      |
+-----+------------+
2 rows in set (0.07 sec)

常用分片规则 1、 取模 此规则为对分片字段求摸运算。 也是水平分表最常用规则。 5.1 配置分表中， orders 表采用了此规 则。 2、 分片枚举 通过在配置文件中配置可能的枚举 id，自己配置分片，本规则适用于特定的场景，比如有些 业务 需要按照省份或区县来做保存，而全国省份区县固定的，这类业务使用本条规则。