分库分表

背景：在当今的大数据下，成年累月的数据堆积，可能一张表或者整个库的数据都是巨大的数据量。久而久之，肯定会有磁盘或者内存不足的情况，当然你可以说增加磁盘或者内存。但是增加了，肯定又会不足的，不可能无限量的扩展下去，且会有性能问题。所以但数据库进行数据存储，肯定有以下问题：

• IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。
• 请求数据太多，带宽不够，网络IO瓶颈
• CPU瓶颈，排序、分组、链接查询、聚合统计等等SQL耗费大量的CPU资源，请求数太多，CPU出现瓶颈。
  好，那怎么解决呢？ 分库分表
  分库分表的中心思想就是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提示数据性能的目的。

拆分策略

分库：将同一个数据库拆分多个数据库。
分表：将同一张表，分成多张表。
这两个分为垂直拆分和水平拆分。

垂直拆分库表

如上图左侧为垂直分库：按照业务的不同，将不同表拆分到不同的库中，比如把用户相关的拆分一个库，订单相关的拆分一个库，商品相关的拆分一个库。具有：

• 每个库的表结构都不一样
• 每个库的数据也不一样
• 所有库的并集是全量数据

上图右侧为垂直分表，就是以字段为依据，根据字段属性将不同字段拆分到不同表中，如把商品的主要信息放一张表，商品的附加信息放一张表，这两张表可以在一个库也可以在不同的库。具有：

• 每个表的结构都不一样
• 每个表的数据页不一样，一般通过一列关联
• 所有表的并集是全量数据

水平拆分

如上图左侧为水平分库：以字段为依据，按照一定的策略，将一个库的数据拆分到多个库中，比如可以按照年份，把去年的数据放一个库，今年的数据放一个库。具有：

• 每个库的表结构都一样
• 每个库的数据都不一样
• 所有库的并集是全量数据

如上图右侧为水平分表：以字段为依据，按照一定的策略将一个表的数据拆分到多个表中。比如可以按照id为1-1000w的数据放一个表，1000w到2000w的数据放一张表。具有：

• 每个表的表结构都一样
• 每个表的数据都不一样
• 所有表的并集是全量数据

那么问题来了。现在有多个库和多个表，我们的应用程序，该如何知道是访问哪个库哪个表呢？别急，往下看。

当前下，一般有两个成熟的中间件可以解决：

• shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，且只支持java语言，性能较高。我会php、go等等后端语言，java没弄过后端，所以门槛较高，后续再整理吧，下次一定。
• MyCat：不用调整代码即可实现分库分表，支持多种语言，性能不及前者。哈哈，来吧。

MyCat

MyCat
阿里的开源产品，基于java编写的mysql数据库中间件。可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在。也就是说之前连接的是mysql，现在连接mycat吧。

• 系统概念介绍

  

  由上图可知，mycat的架构分为逻辑结构（只是逻辑区分，不存具体数据）和物理结构（存放具体的数据）：

  • schema：逻辑库，逻辑上的数据库，不存储具体的数据，具体的数据存储都是在物理结构中。一个逻辑库有多个逻辑表组成。
  • table：逻辑表，关联了多个dataNode。
  • dataNode：分片节点，也叫数据节点，如逻辑表tableA，分散存在多个分片节点当中。至于什么时候存储于第一个分片，或者第二个分片，则有分片规则确定。
  • 节点主机，属于物理结构，真实存储具体数据的地方，每个分片关联一个数据库。
  • rule：分片规则，所有的规则在conf/rule.xml中定义，也可以自行定义。

得知，mycat当中是不存数据的，只是涉及到逻辑分片以及各种处理，具体的数据依然在底层的数据库存储。

• 安装

  安装也简单：直接进入安装版本 选择吧。

  本次演示计划，还是由当前服务器搭建mycat中间件，另外起三个mysql的docker容器作为分片服务器。

  前期准备：

  1：安装jdk
      - sudo yum update
      - sudo yum install java-1.8.0-openjdk-devel
      -java -version
  2：安装mycat  
      - http://dl.mycat.org.cn/ 下载1.6.7.6版本后并解压到/usr/local中，既有mycat目录结构
       tar -zxvf Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz -C /usr/local/
      - mycat相关目录介绍
          - bin：存放可执行文件，用户启动/停止mycat
          - conf：存放mycat的配置文件
          - lib：存放mycat的项目依赖包
          - logs：存放mycat的日志文件
  3：mycat相关命令，进入bin目录
      - ./mycat start 启动
      - ./mycat stop 停止
      -./mycat restart 重启
  4：准备三个mysql的docker容器
      - docker run -d -p 33061:3306 -e MYSQL_ROOT_PASSWORD=123456 --name mysql01 mysql:5.7
      - docker run -d -p 33062:3306 -e MYSQL_ROOT_PASSWORD=123456 --name mysql02 mysql:5.7
      - docker run -d -p 33063:3306 -e MYSQL_ROOT_PASSWORD=123456 --name mysql03 mysql:5.7
  

配置详解

schema.xml

schema.xml作为mycat中最重要的配置文件之一，涵盖了mycat的逻辑库、逻辑表、分片规则、分片节点以及数据源的配置。
主要包含了三组标签：

• schema标签，配置了逻辑库、逻辑表的相关信息
• dataNode标签，配置了数据节点相关信息
• datahost标签，配置了节点主机基于数据源的相关信息。

schema标签

<schema name="db01" checkSQLschema="true" sqlMaxLimit="100">
        <table name="tb_order" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>

<schema name="db02" checkSQLschema="true" sqlMaxLimit="100">
        <table name="tb_user" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
                <table name="tb_class" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>

schema标签用于定义MyCat实例中的逻辑库，一个MyCat实例中，可以有多个逻辑库，可以通过schema标签来划分不同的逻辑库。

MyCat中的逻辑库的概念，等同于mysql的database概念，需要操作某个逻辑库下的表时，也需要切换逻辑库，使用use db01，主要大小写。

- name：指定定义的逻辑库库名
- checkSQLschema：在sql语句操作指定了数据库名称，执行时是否自动去除，true自动去除，false，不自动去除。比如我们配置了true，在查询tb_order的时候，可以不先use，直接通过select * from db01.tb_order查询即可。如果是false，则需要先use db01，再查询。
- sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录。因为一旦用了MyCat就说明数据量很大，不加limit就会给你一个默认值。
- table：配置逻辑表的，所有需要拆分的表都需要再table标签中定义。
    - name：定义逻辑表名，在该逻辑库下唯一。
    - dataNode：定义逻辑表所属的dataNode，该属性需要和dataNode标签中的name对应，多个dataNode使用逗号分割。
    - rule：分片规则的名字，分片规则名字是在rule.xml中定义的
    - primaryKey：逻辑表对应真实表的主键。
    - type：逻辑表的类型，目前只有全局表和普通表。默认为普通表，全局表配置为global。

dataNode标签

<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />

dataNode标签定义了mycat中的数据节点，也就是通常说的数据分片。一个dataNode标签就是一个独立的数据分片。database就是关联的具体数据库

dataHost标签

<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://127.0.0.1:33061?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"user="root" password="123456" />
    </dataHost>

该标签在mycat逻辑库中作为底层标签存在，直接定义了具体的数据库实例、读写分离、心跳语句等等。

- name：唯一表示，供上层标签使用
- maxCon/minCon:最大连接数、最小连接数
- balance：负载均衡策略，取值为0,1,2,3,
- writeType：写操作并发方式，0：写操作转发到第一个writeHost，第一个挂了切换到第二个；1：写操作随机分发到配置的writeHost。
- dbDriver：数据库驱动，支持native，jdbc。

rule.xml

rule.xml中定义了所有拆分表的规则，在使用过程中可以灵活的使用分片算法，或者对同一分片算法使用不同的参数，它让分片过程可配置化。主要包含两类标签：tableRule、Function。

- columns：根据哪个字段进行分片，如id
- algorithm：指定到了下面的Function的name。

server.xml

配置了包含mycat的系统配置信息，主要有两个重要的标签：system、User。

• system标签

  对应的系统配置项以及含义。

• user标签

  配置了当前用户、密码是什么，该用户可以访问哪些数据库以及表等等其他权限相关。

入门级别实战配置

需求：tb_order表数据大，需要对其分解分表，分为三个数据节点，每个节点位于不同服务器。(我依旧通过docker来配置)

前期准备：环境我们刚刚已经安装好了，需要在三个容器中，分别创建 create database db01;

配置分片配置 (schema.xml)

实际上就是配置如上图结构的逻辑库、逻辑表、数据节点、和节点主机。

在/usr/local/mycat/conf下的schema.xml，替换为：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
        <table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

    </schema>

    <dataNode name="dn1" dataHost="dhost1" database="db01" />
    <dataNode name="dn2" dataHost="dhost2" database="db01" />
    <dataNode name="dn3" dataHost="dhost3" database="db01" />

    <dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://127.0.0.1:33061?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root"  password="123456" />
    </dataHost>
    
    <dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://127.0.0.1:33062?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
    </dataHost>
    
    <dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://127.0.0.1:33063?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
    </dataHost>
    
</mycat:schema>

配置用户及用户权限信息

相同目录下的 server.xml

找到user的标签，替换：

<user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">DB01</property>
                <!-- 设置允许的 IP 地址范围 -->
                <property name="host">%</property>
</user>

<user name="user">
        <property name="password">123456</property>
        <property name="schemas">DB01</property>
        <property name="readOnly">true</property>
</user>

启动

配置完成后，直接在bin路径下启动，启动成功后，占用端口为8066。

[root@VM-4-9-centos bin]# ./mycat start
Starting Mycat-server...

去logs/wrapper.log,如果在最后看到 MyCAT Server startup successfully. see logs in logs/mycat.log则表明启动成功。
如果有问题，则去 logs/mycat.log看日志，具体去排查吧。

访问如下：

[root@VM-4-9-centos ~]# mysql -h 127.0.0.1 -P 8066 -uroot -p
Enter password: 
Server version: 5.6.29-mycat-1.6.7.6-release-20210913163520 MyCat Server (OpenCloudDB)  # 来来来看这里，这里是mycat了，不是mysql了
mysql> show databases;
+----------+
| DATABASE |
+----------+
| DB01     |  # 这个是schema中配置的一个DB01数据库
+----------+
1 row in set (0.01 sec)
mysql> use DB01;
mysql> show tables;
+----------------+
| Tables in DB01 |
+----------------+
| TB_ORDER       | # 这个也是在schema中配置的table表。
+----------------+
1 row in set (0.00 sec)

到目前为止，DB01和tb_order(且没有表结构)仅仅是在逻辑结构上存在，还并未和实际数据库进行关联。

具体操作

• 在mycat中创建tb_order表

    CREATE TABLE TB_ORDER(
    id BIGINT(20) PRIMARY KEY,
    title VARCHAR(20)
    );
  INSERT INTO TB_ORDER (id,title) VALUES(1,'zhangsan'),(2,'lisi'),(3,'wanfwu');

• 在docker的mysql01中也同步创建了且插入了三条数据

[root@VM-4-9-centos ~]# mysql -h 127.0.0.1 -P33061 -uroot -p
Enter password: 
Server version: 5.7.43 MySQL Community Server (GPL)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db01               |
+--------------------+
mysql> use db01;
Database changed
mysql> select * from TB_ORDER;
+----+----------+
| ID | TITLE    |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wanfwu   |
+----+----------+
3 rows in set (0.00 sec)
mysql> select * from TB_ORDER;
Empty set (0.02 sec)

• 在docker的mysql02和mysql03中，创建了表结构，但是并未插入数据，因为默认的rule是：auto-sharding-long，规则是根据id来的，0-500w是第一个节点。比如id为5000001，就在第二个节点中。

[root@VM-4-9-centos ~]# mysql -h 127.0.0.1 -P 33062 -uroot -p
Enter password: 
Server version: 5.7.43 MySQL Community Server (GPL)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db01               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use db01;
Database changed
mysql> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| TB_ORDER       |
+----------------+
1 row in set (0.00 sec)
mysql> select * from TB_ORDER;
Empty set (0.00 sec)

案例：垂直拆分

需求：电商系统中，用户和订单每天产生大量数据，单台服务器的数据存储和处理能力是有限的，需要对数据库表进行拆分，原有的数据库表以及可以归类的如下：

由上可见：把商品相关表放入dataNode1中，把订单相关表放入到dataNode2中，把用户以及用户地址相关的表放入到dataNode3中。

前期准备：沿用我们上面的环境，需要在三个容器中，分别创建新库 CREATE DATABASE shopping default character set utf8mb4 collate utf8mb4_unicode_ci;

配置分片配置 (schema.xml)

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="shopping" checkSQLschema="true" sqlMaxLimit="100">
        <table name="tb_goods_base" dataNode="dn1" primaryKey="id"/>
        <table name="tb_goods_brand" dataNode="dn1" primaryKey="id"/>
        <table name="tb_goods_cat" dataNode="dn1" primaryKey="id"/>
        <table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id"/>
        <table name="tb_goods_item" dataNode="dn1" primaryKey="id"/>
        
        <table name="tb_order_item" dataNode="dn2" primaryKey="id"/>    
        <table name="tb_order_master" dataNode="dn2" primaryKey="order_id"/>
        <table name="tb_order_order_pay_log" dataNode="dn2" primaryKey="out_trade_no"/>
        
        
        <table name="tb_user" dataNode="dn3" primaryKey="id"/>
        <table name="tb_user_address" dataNode="dn3" primaryKey="id"/>
        <table name="tb_areas_provinces" dataNode="dn3" primaryKey="id"/>
        <table name="tb_areas_city" dataNode="dn3" primaryKey="id"/>
        <table name="tb_areas_region" dataNode="dn3" primaryKey="id"/>
    </schema>

    <dataNode name="dn1" dataHost="dhost1" database="shopping" />
    <dataNode name="dn2" dataHost="dhost2" database="shopping" />
    <dataNode name="dn3" dataHost="dhost3" database="shopping" />

    <dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://127.0.0.1:33061?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
    </dataHost>
    
    <dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://127.0.0.1:33062?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
    </dataHost>
    
    <dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://127.0.0.1:33063?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
    </dataHost>
    
</mycat:schema>

配置用户及用户权限信息

相同目录下的 server.xml

找到user的标签，替换：

<user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">shopping</property>
                <!-- 设置允许的 IP 地址范围 -->
                <property name="host">%</property>
</user>

<user name="user">
        <property name="password">123456</property>
        <property name="schemas">shopping</property>
        <property name="readOnly">true</property>
</user>

此时，我们的逻辑库、逻辑表就创建完成了。

[root@VM-4-9-centos ~]# mysql -h 127.0.0.1 -P 8066 -uroot -p
Enter password: 
Server version: 5.6.29-mycat-1.6.7.6-release-20220524173810 MyCat Server (OpenCloudDB)
mysql> show databases;
+----------+
| DATABASE |
+----------+
| shopping |
+----------+
mysql> use shopping;
Database changed
mysql> show tables;
+------------------------+
| Tables in shopping     |
+------------------------+
| tb_areas_city          |
| tb_areas_provinces     |
| tb_areas_region        |
| tb_goods_base          |
| tb_goods_brand         |
| tb_goods_cat           |
| tb_goods_desc          |
| tb_goods_item          |
| tb_order_item          |
| tb_order_master        |
| tb_order_order_pay_log |
| tb_user                |
| tb_user_address        |
+------------------------+
13 rows in set (0.00 sec)

创建实体表以及插入数据

在mycat服务器中： 创建表文件shopping-table.sql
插入数据文件shopping-insert.sql

直接source进去即可。 此时，我们直接操作mycat中的表，各种增删改查都可以，无序关系底层具体分配到了哪个库。在mycat中，连表查询也是可以的：

• 同一dataNode下查询：

  mysql> SELECT ua.USER_ID,ua.CONTACT,p.PROVINCE,c.CITY,r.AREA,ua.ADDRESS FROM TB_USER_ADDRESS ua,TB_AREAS_PROVINCES p,TB_AREAS_CITY c,TB_AREAS_REGION r WHERE ua.PROVINCE_ID=p.PROVINCEID and ua.CITY_ID=c.CITYID and ua.TOWN_ID=r.AREAID;
  +-----------+-----------+-----------+-----------+-----------+--------------------+
  | USER_ID   | CONTACT   | PROVINCE  | CITY      | AREA      | ADDRESS            |
  +-----------+-----------+-----------+-----------+-----------+--------------------+
  | deng      | 叶问      | 北京市    | 市辖区    | 西城区    | 咏春武馆总部       |
  | java00001 | 李佳红    | 北京市    | 市辖区    | 崇文区    | 修正大厦           |
  +-----------+-----------+-----------+-----------+-----------+--------------------+
  6 rows in set (0.01 sec)
  

• 不同的dataNode查询

mysql> SELECT * FROM TB_ORDER_MASTER o ,TB_AREAS_PROVINCES p,TB_AREAS_CITY c,TB_AREAS_REGION r WHERE o.RECEIVER_PROVINCE=p.PROVINCEID and o.RECEIVER_CITY=c.CITYID and o.RECEIVER_REGION=r.AREAID;
ERROR 1064 (HY000): invalid route in sql, multi tables found but datanode has no intersection  sql:SELECT * FROM TB_ORDER_MASTER o ,TB_AREAS_PROVINCES p,TB_AREAS_CITY c,TB_AREAS_REGION r WHERE o.RECEIVER_PROVINCE=p.PROVINCEID and o.RECEIVER_CITY=c.CITYID and o.RECEIVER_REGION=r.AREAID

报错了，原因是mycat不确定路由到哪里去。
解决方案：设置全局表配置 全局表配置：也就是每个模块都放一份全局表。如省、市、区表，是属于数据字典表，每个分片中可能都会遇到，可将其设置为全局表，利于业务操作。
则新的架构如下：

设置全局表

    <table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
    <table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
    <table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>

设置完成后，需要把三个容器中的表结构全部清空，不然会有问题。需要先停止mycat，再重新启动。接着再执行上面两个source重新做数据。之后那三张表会存在所有的分片中。

mysql> SELECT order_id,payment,receiver,city,province FROM TB_ORDER_MASTER o ,TB_AREAS_PROVINCES p,TB_AREAS_CITY c,TB_AREAS_REGION r WHERE o.RECEIVER_PROVINCE=p.PROVINCEID and o.RECEIVER_CITY=c.CITYID and o.RECEIVER_REGION=r.AREAID;
+--------------------+---------+-----------+-----------+-----------+
| order_id           | payment | receiver  | city      | province  |
+--------------------+---------+-----------+-----------+-----------+
| 918835712441212928 |    0.01 | 李佳星    | 市辖区    | 北京市    |
| 919059760869863424 |    0.02 | 李嘉诚    | 市辖区    | 北京市    |
| 992190237968957440 |    0.17 | 叶问      | 市辖区    | 北京市    |

当更新全局表后，所有的分片都会自动更新。

案例：水平拆分

需求：有一张日志表(tb_log)，每天都产生大量的日志数据，单台服务器的数据存储及处理能力有限，急需拆分日志表，且日志表均匀的分步在各个节点(三个节点)。
则架构如下：

环境准备：一台服务器搭载mycat，同时起三个mysql容器作为节点。

配置分片配置(schema.xml)

在上述垂直分片例子中的schema.xml中添加：

    <schema name="monitor" checkSQLschema="true" sqlMaxLimit="100">
        <table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long"/>
    </schema>
    <dataNode name="dn4" dataHost="dhost1" database="monitor" />
    <dataNode name="dn5" dataHost="dhost2" database="monitor" />
    <dataNode name="dn6" dataHost="dhost3" database="monitor" />

配置用户及用户权限信息置(server.xml)

在上述垂直分片例子中的server.xml中修改：

<property name="schemas">shopping,monitor</property>

重启mycat即可。

容器创建monitor数据库

CREATE DATABASE monitor default character set utf8mb4 collate utf8mb4_unicode_ci;

mysql创建tb_log表并导入数据查看

• 创建表

  CREATE TABLE TB_LOG (
  id bigint(20) NOT NULL COMMENT 'ID',
  model_name varchar(200), model_value varchar(200), return_value varchar(200), return_class varchar(200), operate_user varchar(20), 
  operate_time varchar(20), param_and_value varchar(500), operate_class varchar(200),
  operate_method varchar(200), cost_time bigint(20), source int(1), PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  

• 导入数据

INSERT INTO TB_LOG (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('1','user','insert','success','java.lang.String','10001','2022-01-06 18:12:28','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','10',1);

INSERT INTO TB_LOG (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('2','user','insert','success','java.lang.String','10001','2022-01-06 18:12:27','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','23',1);

INSERT INTO TB_LOG (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('3','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','34',1);

INSERT INTO TB_LOG (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('4','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','13',2);

INSERT INTO TB_LOG (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('5','user','insert','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','insert','29',3);

INSERT INTO TB_LOG (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('6','user','find','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','find','29',2);

查看验证

随便找一个容器查看，是根据id来进行mod运算平均分步

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db01               |
| monitor            |
| shopping           |
+--------------------+
7 rows in set (0.00 sec)
mysql> use monitor;
Database changed
mysql> select id from TB_LOG;
+----+
| id |
+----+
|  2 |
|  5 |
+----+
2 rows in set (0.00 sec)

mycat水平分表规则

其实我们上面的例子中，已经有用到过mod-long,auto-sharding-long规则，那还有哪些呢？我们统计下。

范围分片

rule="auto-sharding-long"
根据指定的字段及其配置的范围和数据节点的对应情况，来决定该数据属于哪一个分片。

如上图，我们有三个节点，主键id为int且自增的时候较为适用，如id为1-500w的在第一个节点等等。

那么我如果有很多个节点，或者需要自定义，该如何配置呢？可查看下图自定义配置：

取模分片

rule="mod-long"
根据指定的字段值与节点数量进行求模运算，根据运算结果来决定该数据属于哪一个分片。适用于id为int类型，且均匀的分步在不同分片中，如id为雪花算法生成。

自定义只需要修改一个，就是如下的count，就是分片节点数。

    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
            <property name="count">3</property>
    </function>

一致性hash

rule="sharding-by-murmur"
根据指定的字段值进行hash，然后根据hash值来决定当前数据落在哪个分片节点当中。字段类型不局限于int，可以是字符串，适用于字段值为uuid生成的。
所谓一致性hash，就是相同的hash因子计算值总是被划分到相同的分区表中。即使后面分片扩容后，但是只要hash因子相同还是会落在相同的分片中。

我们只要修改count，就是分片节点数即可。

    <function name="murmur"
                      class="io.mycat.route.function.PartitionByMurmurHash">
            <property name="seed">0</property><!-- 默认是0 -->
            <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
            <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
            <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
            <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
                    用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -->
    </function>

枚举分片

rule="sharding-by-intfile"
通过在配置文件中配置可能的枚举值，指定数据分步到不同数据节点中，这个规则适用于固定的可枚举的类型，比如省份、性别、状态等等拆分数据业务。

如上图，我们定义好的枚举规则就是0,1,2，则枚举值为0的落在了第一个节点，为1的落在了第二个节点，为2的落在了第三个节点。 sharding-by-intfile如下：

<tableRule name="sharding-by-intfile">
        <rule>
                <columns>sharding_id</columns>
                <algorithm>hash-int</algorithm>
        </rule>
</tableRule>

我们可以针对columns进行修改，比如状态id就改为 status_id 。

sharding-by-intfile的algorithm为hash-int如下：

    <function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
            <property name="mapFile">partition-hash-int.txt</property>
    </function>

partition-hash-int.txt为：
后面是分片索引值，从0开始。

1=0
2=1
3=2

有个问题，假设status_id未在枚举之列，比如为4的情况，则会报错，所以需要指定一个默认的值，就是枚举之外的默认值，可以把hash-int修改为

    <function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
        <property name="defalutNode">0</property>
    </function>

这就表示如果找不到配置的节点，默认落在第0个节点，不要这个要慎重，后续如果加枚举值了，这个数据可能会丢失。

应用指定

rule="sharding-by-substring"
运行节点由应用自主决定路由到那个分片，直接根据字符串子串(必须是数子)计算分片号。适用id前几位固定，比如01001-01999表示A集合，02001-02999表示B集合等等。
具体如下图解释：

固定分片hash算法

rule="sharding-by-long-hash"

该算法类似于十进制的求模运算，但是为二进制的操作，例如id的二进制低10位与1111111111进行&运算，根据结果来决定该条数据落在哪个库下面。

解释如下：

它具有：

• 如果是求模，连续的值分别分配到不同的分片，但固定分片hash算法会将连续的值分配到相同的分片，降低事务处理的难度。
• 可以均匀分配，也可以非均匀分配，比如第三个节点的数据量明显更大。
• 分片字段必须要是数字类型。 配置如下：

如上，
count中的2,1，2表示前面两个，就是dn4，和dn5,1表示后面一个，就是dn6。
length和count是一致的，表示分片长度，也就是第一个256表示前面两个运算长度都是256，理解为dn4是0-256，dn5是257-511，dn6是512-1024，因为默认最大1024.

举例：比如插入id为515，则计算为10 0000 0011 & 11 1111 1111 = 10 0000 0011 = 515,515在第三个分片(512-1024)中。

字符串hash解析

rule="sharding-by-stringhash"
上述的固定分片hash算法，字段必须要是数字类型，如果是字符串类型的话，可以采用字符串hash解析算法。
原理就是截取字符串中的指定位置的子字符串，进行hash算法，算出分片。

如上图：tomcat和herry两个字符串，配置指定子字符串为0-1两位，则是to和he，分别与1023进行&运算，然后得出tomcat落在第一个节点，herry落在第二个节点。

如上图，和固定分片hash算法类似，都是分片长度默认为1024.

按天(日期)分片

rule="sharding-by-date"
按天就是指定一个时间周期，比如十天，第一个十天放在第一个分片，第二个十天放在第二个分片，周而复始，适用于日志表等等。如下图：

• 开始时间(begin)和结束时间(end)，以及一个周期(partionday)。
• 也就是1号到10号分配到了第一个，11-20到第二个，21-30放到第三个分片中。
• 如果已经超过了呢？比如2022-02-01如何放置呢？上面说了，周而复始，放在第一个分片中。

• 我们要修改的就是columns字段，放入固定字段。
• dateFormat就是日期格式，sBeginDate为开始时间，sEndDate为结束时间，sPartionDay为周期。
• 我们有三个分片，那么就是配置一个月30天，周期是10则刚好就是三个分片。如果周期为5，还是三个分片则会报错。
• 比如配置一年的跨度，周期还是10，就需要37个分片。

按自然月分片

rule="sharding-by-month"
使用场景就是按照月份来分片，每个自然月为一个分片。这个作为操作记录、日志记录等等来说是最合适不过了。

如下图，就是按照开始和结束时间来，有三个分片轮着来，如果超过了end，则周而复始。

配置的规则和按天类似，少了一个sPartionDay，其余一致。比按天简单很多，推荐使用这个。

• 注意的是，从开始时间，一个月为一个分片，结束后的时间并非不能插入而是周而复始的插入。
• 我们有三个分片，则只能配置1月到3月的。如果想配置1月到12月则需要12个分片。

mycat管理

mycat默认开通了两个端口，可以在server.xml中进行修改。

• 8066 数据访问端口，即进行DML和DDL操作。
• 9066 数据库管理端口，即mycat服务管理控制功能，用于管理mycat的整个集群状态。

mysql -h127.0.0.1 -P9066 -uroot -p

命令	含义
show @@help	查看mycat管理工具帮助文档
show @@version	查看mycat的版本
reload @@config	重新加载mycat的配置文件
show @@datasource	查看mycat的数据源信息
show @@datanode	查看mycat现有的分片节点信息
show @@threadpool	查看mycat的线程池信息
show @@sql	查看执行的sql
show @@sql.sum	查看执行的sql统计

mysql> show @@version；
+---------------------------------------------+
| VERSION                                     |
+---------------------------------------------+
| 5.6.29-mycat-1.6.7.6-release-20220524173810 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> show @@datanode;
+------+-----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST         | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+-----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1  | dhost1/shopping |     0 | mysql |      0 |    5 | 1000 |       5 |          0 |        0 |       0 |            -1 |
| dn2  | dhost2/shopping |     0 | mysql |      0 |    5 | 1000 |       5 |          0 |        0 |       0 |            -1 |
| dn3  | dhost3/shopping |     0 | mysql |      0 |    5 | 1000 |       5 |          0 |        0 |       0 |            -1 |
| dn4  | dhost1/monitor  |     0 | mysql |      0 |    4 | 1000 |       8 |          0 |        0 |       0 |            -1 |
| dn5  | dhost2/monitor  |     0 | mysql |      0 |    4 | 1000 |       9 |          0 |        0 |       0 |            -1 |
| dn6  | dhost3/monitor  |     0 | mysql |      0 |    4 | 1000 |       8 |          0 |        0 |       0 |            -1 |
+------+-----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
6 rows in set (0.01 sec)
mysql> show @@datasource;
+----------+--------+-------+-----------+-------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST      | PORT  | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+-----------+-------+------+--------+------+------+---------+-----------+------------+
| dn1      | master | mysql | 127.0.0.1 | 33061 | W    |      0 |    9 | 1000 |      13 |         0 |          3 |
| dn3      | master | mysql | 127.0.0.1 | 33063 | W    |      0 |    9 | 1000 |      13 |         0 |          3 |
| dn2      | master | mysql | 127.0.0.1 | 33062 | W    |      0 |    9 | 1000 |      14 |         0 |          4 |
| dn5      | master | mysql | 127.0.0.1 | 33062 | W    |      0 |    9 | 1000 |      14 |         0 |          4 |
| dn4      | master | mysql | 127.0.0.1 | 33061 | W    |      0 |    9 | 1000 |      13 |         0 |          3 |
| dn6      | master | mysql | 127.0.0.1 | 33063 | W    |      0 |    9 | 1000 |      13 |         0 |          3 |
+----------+--------+-------+-----------+-------+------+--------+------+------+---------+-----------+------------+
6 rows in set (0.00 sec)

mycat监控-图形化界面工具mycat-eye

mycat-web(mycat-eye)是对mycat-server提供监控服务，功能不局限于对mycat-server使用，他通过jdbc连接对mycat、mysql监控，监控远程服务器的cpu、内存、网络、磁盘等等。
mycat-eye运行过程中需要依赖zookeeper，因此需要先

• 安装zookeeper。

1：下载：https://zookeeper.apache.org/releases.html
2：解压：tar -zxvf apache-zookeeper-3.8.2-bin.tar.gz -C /usr/local/
3：创建目录data如：/usr/local/apache-zookeeper-3.8.2-bin/data
4：进入conf目录，拷贝zoo_sample.cfg为zoo.cfg,并修改dataDir=/usr/local/apache-zookeeper-3.8.2-bin/data
5：启动 
    [root@VM-4-9-centos bin]# ./zkServer.sh start
    Starting zookeeper ... STARTED
    [root@VM-4-9-centos bin]# ./zkServer.sh status
    Mode: standalone # 为这个表示启动成功，不然查问题吧

• 安装mycat-web

1：下载：http://dl.mycat.org.cn/
2：tar -zxvf Mycat-web.tar.gz -C /usr/local/
3：如果mycat-web和zookeeper在同一个服务器上，就可以直接启动：
    [root@VM-4-9-centos mycat-web]# pwd
    /usr/local/mycat-web
    [root@VM-4-9-centos mycat-web]# sh start.sh
    nohup: ignoring input and appending output to ‘nohup.out’
    
4：如果不在，则修改/usr/local/mycat-web/mycat-web/WEB-INF/classe/mycat.properties下的
zookeeper=localhost:2181 实际地址。

以上如果全部安装没问题，防火墙端口也加入了的话，输出 http://124.223.47.250:8082/mycat/ 即可访问，如下：

我们在实际中，主要是SQL-监控，可以看mysql的性能、慢查询等等。

问题

在搭建中遇到一些问题记录：
1：编写schema的时候，由于是配置的jdbc地址为实际的服务器ip，但我由于是docker，导致一直链接不上，通过日志查看后改为127.0.0.1即可
2：启动后，mycat上创建表但是一直报错为 no search key word，查询无果后想着是不是版本问题，换成最近版本即可解决。
3: docker里面的mysql版本默认字符集不是utf8，导致数据导入一直有问题。

感谢

本文主要来源于mycat官网文档，以及itcast博主的介绍。