MySQL-中间件mycat（二）（2）-阿里云开发者社区

测试分库

登录mycat登录，创建四个表。

1. [root@master2 ~]# mysql -umycat -p123456 -P8066 -h 192.168.8.10
2. #省略登录信息
3. mysql> use TESTDB;
4. 
5. mysql> CREATE TABLE customer(
6.     -> id INT AUTO_INCREMENT,
7.     -> NAME VARCHAR(200),
8.     -> PRIMARY KEY(id)
9.     -> );
10. Query OK, 0 rows affected (0.02 sec)
11.  OK!
12. 
13. mysql> CREATE TABLE orders(
14.     -> id INT AUTO_INCREMENT,
15.     -> order_type INT,
16.     -> customer_id INT,
17.     -> amount DECIMAL(10,2),
18.     -> PRIMARY KEY(id)
19.     -> );
20. Query OK, 0 rows affected (0.01 sec)
21.  OK!
22. 
23. mysql> CREATE TABLE orders_detail(
24.     -> id INT AUTO_INCREMENT,
25.     -> detail VARCHAR(2000),
26.     -> order_id INT,
27.     -> PRIMARY KEY(id)
28.     -> );
29. Query OK, 0 rows affected (0.01 sec)
30.  OK!
31. 
32. mysql> CREATE TABLE dict_order_type(
33.     -> id INT AUTO_INCREMENT,
34.     -> order_type VARCHAR(200),
35.     -> PRIMARY KEY(id)
36.     -> );
37. Query OK, 0 rows affected (0.03 sec)
38.  OK!

分别登录master1、master2，查看表。

master1为：

1. mysql> show tables;
2. +------------------+
3. | Tables_in_mytest |
4. +------------------+
5. | dict_order_type  |
6. | orders           |
7. | orders_detail    |
8. | tb1              |
9. +------------------+
10. 4 rows in set (0.00 sec)

master2为：

1. mysql> show tables;
2. +------------------+
3. | Tables_in_mytest |
4. +------------------+
5. | CUSTOMER         |
6. +------------------+
7. 1 row in set (0.00 sec)
8. 
9. mysql> desc CUSTOMER;
10. +-------+--------------+------+-----+---------+----------------+
11. | Field | Type         | Null | Key | Default | Extra          |
12. +-------+--------------+------+-----+---------+----------------+
13. | ID    | int(11)      | NO   | PRI | NULL    | auto_increment |
14. | NAME  | varchar(200) | YES  |     | NULL    |                |
15. +-------+--------------+------+-----+---------+----------------+
16. 2 rows in set (0.00 sec)

总结分库

由上可见，分库成功，简单的来讲，由mycat修改配置文件，dn1指向master1，dn2指向master2。登录mycat创建表，customer表已经指定dn2了，而其他表则创建到dn1。

水平拆分-分表

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中。

实现分表

选择要拆分的表 MySQL 单表存储数据条数是有瓶颈的，单表达到 1000 万条数据就达到了瓶颈，会影响查询效率，需要进行水平拆分（分表）进行优化。例如：例子中的 orders、 orders_detail 都已经达到600 万行数据，需要进行分表优化。分表字段以 orders 表为例，可以根据不同自字段进行分表

编号	分表字段	效果
1	id（主键、或创建时间）	查询订单注重时效，历史订单被查询的次数少，如此分片会造成一个节点访问多，一个访问少，不平均。
2	customer_id（客户id）	根据客户 id 去分，两个节点访问平均，一个客户的所有订单都在同一个节点

修改配置文件

登录mycat服务器，修改schema.xml 配置文件，为 orders 表设置数据节点为 dn1、 dn2，并指定分片规则为 mod_rule（自定义的名字）

1. [root@mycat conf]# cat schema.xml 
2. <?xml version="1.0"?>
3. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
4. <mycat:schema xmlns:mycat="http://io.mycat/">
5. 
6.         <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
7.                 <table name="customer" dataNode="dn2"></table>
8.                 <table name="orders" dataNode="dn1,dn2" rule="mod_rule">     #添加
9.    </table>                                  #添加
10.         </schema>
11.         <dataNode name="dn1" dataHost="localhost1" database="mytest" />
12.         <dataNode name="dn2" dataHost="localhost2" database="mytest" />
13.         <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
14.                           writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
15.                 <heartbeat>select user()</heartbeat>
16.                 <!-- can have multi write hosts -->
17.                 <writeHost host="hostM1" url="jdbc:mysql://192.168.8.20:3306" user="root"
18.                                    password="123">
19.                 <readHost host="hostS1" url="jdbc:mysql://192.168.8.30:3306" user="root"
20.                                    password="123"></readHost>
21.                 <readHost host="hostS2" url="jdbc:mysql://192.168.8.40:3306" user="root"
22.                                    password="123"></readHost>
23.                 </writeHost>
24. 
25.         </dataHost>
26.         <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
27.                           writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
28.                 <heartbeat>select user()</heartbeat>
29.                 <!-- can have multi write hosts -->
30.                 <writeHost host="hostM2" url="jdbc:mysql://192.168.8.50:3306" user="root"
31.                                    password="123">
32.                 </writeHost>
33. 
34.         </dataHost>
35. </mycat:schema>

登录mycat服务器，修改配置文件 rule.xml，具体修改内容看下图红框中内容。

1. [root@mycat conf]# vim rule.xml 
2. #省略部分内容
3.  <tableRule name="mod_rule">                 #修改
4.                 <rule>
5.                         <columns>id</columns>
6.                         <algorithm>mod-long</algorithm>
7.                 </rule>
8.         </tableRule>
9. #省略部分内容
10.         <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
11.                 <!-- how many data nodes -->
12.                 <property name="count">2</property>       #修改
13.         </function>
14. #省略部分内容

#在 rule 配置文件里新增分片规则 mod_rule，并指定规则适用字段为id。

#还有选择分片算法 mod-long（对字段求模运算）， id 对两个节点求模，根据结果分片。

#配置算法 mod-long 参数 count 为 2，两个节点。

测试分表

1. 在数据节点 dn2 上建 orders 表

1. [root@master2 ~]# mysql -uroot -p123
2. #省略部分内容
3. mysql> use mytest;
4. 
5. mysql> CREATE TABLE orders(
6.     -> id INT AUTO_INCREMENT,
7.     -> order_type INT,
8.     -> customer_id INT,
9.     -> amount DECIMAL(10,2),
10.     -> PRIMARY KEY(id)
11.     -> );
12. Query OK, 0 rows affected (0.03 sec)

2. 重启 Mycat，让配置生效

[root@mycat conf]# mycat console

3. 访问 Mycat 实现分片

[root@master2 ~]# mysql -umycat -p123456 -P8066 -h192.168.8.10

4. 在 mycat 里向 orders 表插入下面六行数据， INSERT 字段不能省略

1. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(1,101,100,100100);
2. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
3. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
4. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
5. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
6. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);

5. 分别在mycat、master1、master2中查看orders表数据，分表成功

1. #mycat
2. mysql> select * from orders;
3. +------+------------+-------------+-----------+
4. | id   | order_type | customer_id | amount    |
5. +------+------------+-------------+-----------+
6. |    2 |        101 |         100 | 100300.00 |
7. |    4 |        101 |         101 | 103000.00 |
8. |    6 |        102 |         100 | 100020.00 |
9. |    1 |        101 |         100 | 100100.00 |
10. |    3 |        101 |         101 | 120000.00 |
11. |    5 |        102 |         101 | 100400.00 |
12. +------+------------+-------------+-----------+
13. 6 rows in set (0.01 sec)
14. #master1
15. mysql> select * from orders;
16. +----+------------+-------------+-----------+
17. | id | order_type | customer_id | amount    |
18. +----+------------+-------------+-----------+
19. |  2 |        101 |         100 | 100300.00 |
20. |  4 |        101 |         101 | 103000.00 |
21. |  6 |        102 |         100 | 100020.00 |
22. +----+------------+-------------+-----------+
23. 3 rows in set (0.00 sec)
24. #master2
25. mysql> select * from orders;
26. +----+------------+-------------+-----------+
27. | id | order_type | customer_id | amount    |
28. +----+------------+-------------+-----------+
29. |  1 |        101 |         100 | 100100.00 |
30. |  3 |        101 |         101 | 120000.00 |
31. |  5 |        102 |         101 | 100400.00 |
32. +----+------------+-------------+-----------+
33. 3 rows in set (0.00 sec)

连接查询

1. 登录mycat服务器，修改配置文件，添加下列内容，表示可以外键连接。

1. [root@mycat conf]# cat schema.xml 
2. <?xml version="1.0"?>
3. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
4. <mycat:schema xmlns:mycat="http://io.mycat/">
5. 
6.         <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
7.                 <table name="customer" dataNode="dn2"></table>
8.                 <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
9.                         <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />    #添加
10.     </table>
11. #省略部分内容

2. 登录master2上创建 orders_detail表。

1. [root@master2 ~]# mysql -uroot -p123
2. 
3. mysql> use mytest;
4. 
5. mysql> CREATE TABLE orders_detail(
6.     -> id INT AUTO_INCREMENT,
7.     -> detail VARCHAR(2000),
8.     -> order_id INT,
9.     -> PRIMARY KEY(id)
10.     -> );
11. Query OK, 0 rows affected (0.01 sec)

3. 重启 Mycat 访问，并登录Mycat 向 orders_detail 表插入数据

[root@mycat conf]# mycat console

重启后登录mycat

1. [root@master2 ~]# mysql -umycat -p123456 -P8066 -h192.168.8.10
2. #省略部分内容
3. mysql> use TESTDB;
4. 
5. mysql> insert into orders_detail(id,detail,order_id) values (1,'detail',1);
6. Query OK, 1 row affected (0.25 sec)
7.  OK!
8. 
9. mysql>  insert into orders_detail(id,detail,order_id) values (2,'detail',2);
10. Query OK, 1 row affected (0.02 sec)
11.  OK!
12. 
13. mysql> insert into orders_detail(id,detail,order_id) values (3,'detail',3);
14. Query OK, 1 row affected (0.01 sec)
15.  OK!
16. 
17. mysql>  insert into orders_detail(id,detail,order_id) values (4,'detail',4);
18. Query OK, 1 row affected (0.01 sec)
19.  OK!
20. 
21. mysql> insert into orders_detail(id,detail,order_id) values (5,'detail',5);
22. Query OK, 1 row affected (0.01 sec)
23.  OK!
24. 
25. mysql> insert into orders_detail(id,detail,order_id) values (6,'detail',6);
26. Query OK, 1 row affected (0.01 sec)
27.  OK!
28. 
29. mysql> select o.*,od.detail from orders as o inner join orders_detail as od on o.id=od.order_id;
30. +------+------------+-------------+-----------+--------+
31. | id   | order_type | customer_id | amount    | detail |
32. +------+------------+-------------+-----------+--------+
33. |    2 |        101 |         100 | 100300.00 | detail |
34. |    4 |        101 |         101 | 103000.00 | detail |
35. |    6 |        102 |         100 | 100020.00 | detail |
36. |    1 |        101 |         100 | 100100.00 | detail |
37. |    3 |        101 |         101 | 120000.00 | detail |
38. |    5 |        102 |         101 | 100400.00 | detail |
39. +------+------------+-------------+-----------+--------+
40. 6 rows in set (0.07 sec)

全局表

在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，就成了比较棘手的问题，考虑到字典表具有以下几个特性：

1、变动不频繁

2、数据量总体变化不大

3、数据规模不大，很少有超过数十万条记录鉴于此， Mycat 定义了一种特殊的表，称之为“全局表”，全局表具有以下特性：

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

修改配置文件

登录mycat修改 schema.xml 配置文件

1. [root@mycat conf]# cat schema.xml 
2. <?xml version="1.0"?>
3. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
4. <mycat:schema xmlns:mycat="http://io.mycat/">
5. 
6.         <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
7.                 <table name="customer" dataNode="dn2"></table>
8.                 <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
9.                         <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
10.     </table>
11.     <table name="dict_order_type" dataNode="dn1,dn2" type="global">
12.   </table>
13.         </schema>
14. #省略部分内容

测试全局表

1. 在master2创建 dict_order_type 表

1. [root@master2 ~]# mysql -uroot -p123
2. #省略部分内容
3. mysql> use mytest
4. 
5. mysql> CREATE TABLE dict_order_type(
6.     -> id INT AUTO_INCREMENT,
7.     -> order_type VARCHAR(200),
8.     -> PRIMARY KEY(id)
9.     -> );
10. Query OK, 0 rows affected (0.01 sec)

2. 重启 Mycat

[root@mycat conf]# mycat console

3. 访问 Mycat 向 dict_order_type 表插入数据

1. [root@master2 ~]# mysql -umycat -p123456 -P8066 -h192.168.8.10
2. #省略部分内容
3. mysql> use TESTDB
4. 
5. mysql> insert into dict_order_type (id,order_type) values (101,'type1');
6. Query OK, 1 row affected (0.22 sec)
7.  OK!
8. 
9. mysql> insert into dict_order_type (id,order_type) values (102,'type2');
10. Query OK, 1 row affected (0.03 sec)
11.  OK!

4. 分别登录mycat、master1、master2，查看表，最终都可以看到id为101，102的数据内容。

1. mysql> select * from dict_order_type;
2. +-----+------------+
3. | id  | order_type |
4. +-----+------------+
5. | 101 | type1      |
6. | 102 | type2      |
7. +-----+------------+
8. 2 rows in set (0.00 sec)

常用分片规则

1、取模此规则为对分片字段求摸运算。也是水平分表最常用规则。 5.1 配置分表中， orders 表采用了此规则。

2、分片枚举通过在配置文件中配置可能的枚举 id，自己配置分片，本规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存，而全国省份区县固定的，这类业务使用本条规则。

MySQL-中间件mycat（二）（2）

测试分库

总结分库

水平拆分-分表

实现分表

测试分表

连接查询

全局表

修改配置文件

测试全局表

常用分片规则

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL-中间件mycat（二）（2）

测试分库

总结分库

水平拆分-分表

实现分表

测试分表

全局表

修改配置文件

测试全局表

常用分片规则

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像