Mycat水平拆分Mysql

环境参数：
Mycat-server-1.6.6.1
JDK 1.7
mysql 5.7
架构： 192.168.189.140为mycat节点  192.168.189.142为orderdb01和orderdb02节点 
数据库文件：http://106.52.166.110:8080/sichuan/bak_imooc.sql

在电商业务或者物联网使用记录业务中，订单表和使用记录表往往是一张大表，当这张表数据量极大并且索引的性能也达到瓶颈的时候一般解决方案会有两种

1. 使用ElasticSearch搜索引擎代替这张大表

2. 使用mycat分库分表中间件对大表进行水平拆分，下面是我对mycat水平拆分的总结
   在这里我以对order_master表进行分片为例进行分片演示（分片键是customer_id），首先mycat由以下几个配置文件控制分库分表
   一. schema.xml是用于配置逻辑库和逻辑表
   二. rule.xml负责设置其分片规则（分片规则有简单取模，哈希取模，枚举取模，字符串范围取模这四种，以下演示我使用的是最简单的简单取模）
   三. server.xml负责配置用户的访问权限
   其次在schema.xml文件中核心标签有<schema> <datahost><datanode>

3. 给192.168.189.140和192.168.189.142节点数据库创建im_mycat用户然后在schema.xml文件中配置：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="imooc_db" checkSQLschema="false" sqlMaxLimit="100">
                <table name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02" rule="order_master" autoIncrement="true"/>
                <table name="order_detail" primaryKey="order_detail_id" dataNode="ordb" />
                <table name="order_cart" primaryKey="cart_id" dataNode="ordb" />
                <table name="order_customer_addr" primaryKey="customer_addr_id" dataNode="ordb" />
                <table name="region_info" primaryKey="region_id" dataNode="ordb" />
                <table name="shipping_info" primaryKey="ship_id" dataNode="ordb" />
                <table name="warehouse_info" primaryKey="w_id" dataNode="ordb" />
                <table name="warehouse_product" primaryKey="wp_id" dataNode="ordb" />
        </schema>
        <dataNode name="ordb" dataHost="mysql189142" database="order_db" />
        <dataNode name="orderdb01" dataHost="mysql189142" database="orderdb01" />
        <dataNode name="orderdb02" dataHost="mysql189142" database="orderdb02" />

        <dataNode name="mycat" dataHost="mysql189140" database="mycat" />

        <dataHost name="mysql189142" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
                <heartbeat> select user() </heartbeat>
                <writeHost host="192.168.189.142" url="192.168.189.142:3306" user="im_mycat" password="123456"  />
        </dataHost>
        <dataHost name="mysql189140" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
                <heartbeat> select user() </heartbeat>
                <writeHost host="192.168.189.140" url="192.168.189.140:3306" user="im_mycat" password="123456"  />
        </dataHost>

</mycat:schema>

4. 在server.xml文件中配置：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
 <system>
  <property name="serverPort">8066</property>
  <property name="managerPort">9066</property>
  <property name="nonePasswordLogin">0</property>
  <property name="bindIp">0.0.0.0</property>
  <property name="frontWriteQueueSize">2048</property>

  <property name="charset">utf8</property>
  <property name="txIsolation">2</property>
  <property name="processors">8</property>
  <property name="idleTimeout">1800000</property>
  <property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
  <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
  <property name="sqlExecuteTimeout">300</property>  <!-- SQL 执行超时 单位:秒-->
  <property name="sequnceHandlerType">2</property>
  <property name="defaultMaxLimit">100</property>
  <property name="maxPacketSize">104857600</property>
 </system>

 <user name="root" defaultAccount="true">
  <property name="password">123456</property>
  <!--只配置逻辑库与schema的name值对应，对外-->
  <property name="schemas">imooc_db</property>
 </user>
</mycat:server>

5. 在rule.xml文件中配置（配置分片算法）

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
        - you may not use this file except in compliance with the License. - You 
        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
        - - Unless required by applicable law or agreed to in writing, software - 
        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
        License for the specific language governing permissions and - limitations 
        under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
   <tableRule name="order_master">
       <rule>
             <columns>customer_id</columns>
                 <algorithm>mod-long</algorithm>
           </rule>
   </tableRule>
   <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
       <property name="count">2</property>
   </function>

</mycat:rule>

6. 启动mycat进行测试：

   
   image.png
   image.png
   image.png