通过Mycat实现数据库分库分表

Mycat数据库中间件的安装与配置
通过Mycat实现数据库读写分离

分库

分库： 将不同的数据库放到不同的服务器上面

• 如何分库
  • 分库不能随意分，首先我们要知道不同服务器上的数据库时没办法实现多表联合查询的，但多表联合查询又是数据库中经常要用到的操作
  • 将不会涉及到联合查询，或者一些特殊类型的表分开
    • 特殊类型：例如当用户登录后我们通常将登录信息存放到redis中，如果需要用登录信息去查其他表我们可以先从redis中将数据查出来，在利用该数据去和其他表联合查询

实例：
#客户表 rows:20万
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
);

#订单表 rows:600万
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id)
);
我们将客户表和订单表分开存储，分别放到Windows和linux下

配置mycat

• 修改schema.xml

<mycat:schema xmlns:mycat="http://io.mycat/">
           <!--逻辑库    name名称,   checkSQLschema         sqlMaxLimit 末尾是否要加 limit xxx-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        		<!-- 将customer表放入dn2数据节点中，在这里对应我的linux-->
                 <table name="customer" dataNode="dn2" ></table>
        </schema>
            <!--逻辑库    name名称,   dataHost 引用的哪个dataHost      database:对应mysql的database-->
        <dataNode name="dn1" dataHost="host1" database="supermarket" />
        <dataNode name="dn2" dataHost="host2" database="supermarket" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
                          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.137.1:3306" user="root"
                                   password="123456">
                </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>
                <writeHost host="hostm2" url="192.168.137.136:3306" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
</mycat:schema>

• 重启mycat（在bin目录下）

  •   ./mycat start
    

使用mycat

• 通过mycat访问数据库

  • mysql -umycat -p123456 -h192.168.137.136 -P8066
    

• 在mycat下创建表，之前在配置文件中已经配置了那个表创建到那台服务器上面

   <table name="customer" dataNode="dn2" ></table>
  

  • customer会自动创建到linux下，其他的自动创建到Windows下
    
    

• 分开创建已完成，那么插入数据有没有问题呢？
  
  
  

• 接下来在试试查询
  

  好啦，到这里已经说明我们的分库操作完美的完成啦

分表

• 更改配置文件schema.xml

  <mycat:schema xmlns:mycat="http://io.mycat/">
             <!--逻辑库    name名称,   checkSQLschema         sqlMaxLimit 末尾是否要加 limit xxx-->
          <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                  <table name="customer" dataNode="dn2" ></table>
                  <!-- 将orders表分别存到两个库中，并制定 -->
                  <table name="orders" dataNode="dn1,dn2"  rule="mod_rule" ></table>
          </schema>
              <!--逻辑库    name名称,   dataHost 引用的哪个dataHost      database:对应mysql的database-->
          <dataNode name="dn1" dataHost="host1" database="supermarket" />
          <dataNode name="dn2" dataHost="host2" database="supermarket" />
          <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
                            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.137.1:3306" user="root"
                                     password="123456">
                  </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>
                  <writeHost host="hostm2" url="192.168.137.136:3306" user="root"
                                     password="123456">
                  </writeHost>
          </dataHost>
  </mycat:schema>
  

  • 改动
    • 我们将orders表分别存到两台服务器中，分表规则为mod_rule

    <table name="orders" dataNode="dn1,dn2"  rule="mod_rule" ></table>
    

• 更改配置文件rule.xml

  <tableRule name="mod_rule">
       <rule>
       	  <!-- 根据id来进行分表 -->
            <columns>customer_id</columns>
            <!-- 分表规则，采用取模规则-->
            <algorithm>mod-long</algorithm>
        </rule>
  </tableRule> 
   <!-- 这里是取模规则，class对应的是算法的源码-->
  <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
       <!-- how many data nodes -->
      <property name="count">2</property>
  </function>
  

• 在linux下创建orders表（Windows下之前我们已经创建好了）
  
• 重启mycat

  •   ./mycat restart 
    

• 将原先的orders表清空，并插入数据

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

• 分表结果
  
  
  • 查询一下
    • 这里的结果不是按照id排序的，是先查一个表，然好后再查第二个表，最后拼起来的

接下来我们加入一个订单详情表

显然订单详情表和订单表是要联合查询的，订单表我们搞定了，那么这个订单详情表要怎么弄呢？

• 订单表分库了，那么订单详情页必然要分库，怎么分呢？
  • 首先在两个数据都创建orders_detail表
    #订单详细表
    CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
    );
  • 我们把订单详情表和订单表建立关联关系，即主外键关系

    <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
    

  • 修改schema.xml

    <?xml version="1.0"?>
               <!--逻辑库    name名称,   checkSQLschema         sqlMaxLimit 末尾是否要加 limit xxx-->
            <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>
            </schema>
                <!--逻辑库    name名称,   dataHost 引用的哪个dataHost      database:对应mysql的database-->
            <dataNode name="dn1" dataHost="host1" database="supermarket" />
            <dataNode name="dn2" dataHost="host2" database="supermarket" />
            <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
                              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.137.1:3306" user="root"
                                       password="123456">
                    </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>
                    <writeHost host="hostm2" url="192.168.137.136:3306" user="root"
                                       password="123456">
                    </writeHost>
            </dataHost>
    </mycat:schema>
    

• 重启mycat

  •    ./mycat restart
    

• 插入数据验证

  •   INSERT INTO orders_detail(id,detail,order_id) VALUES(1,'detail1',1);
      INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
      INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
      INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
      INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
      INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);
    

• 查看数据的存储位置
  
  
  • 显然订单详情表也根据主外键关系进行了分表
• 查询一下数据

  SELECT * FROM orders o INNER JOIN orders_detail od ON od.order_id=o.id;
  

  

分表操作到这里也就顺利的完成啦