利用Mycat进行分表分片

最新推荐文章于 2024-07-28 10:31:02 发布

wqr111

最新推荐文章于 2024-07-28 10:31:02 发布

阅读量2.2k

点赞数 2

分类专栏： Mycat 文章标签： mysql mycat

本文链接：https://blog.csdn.net/wqr111/article/details/118103276

版权

Mycat 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

本文介绍了电信计费系统中遇到的单表数据瓶颈问题，通过MySQL数据库的水平拆分策略进行优化。选择了客户id和省份作为分表字段，利用Mycat中间件实现数据的分布式存储。详细阐述了如何创建和配置Mycat的schema.xml和rule.xml，包括客户表、通话记录表和字典表的分片规则。同时，讨论了全局表的概念，用于解决跨分片查询的问题，并展示了如何在Mycat中配置全局表。

摘要由CSDN通过智能技术生成

分表

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

需求

在电信行业有电信计费系统(BOSS系统)，假设其中存储如下信息：

客户手机账户（手机号）信息
手机通话记录信息
字典表（如存储常用的码表信息，例如通话类型，01：呼出，02：呼入等）

简单ER图如下：

MySQL 单表存储数据条数是有瓶颈的，单表达到 1000 万条数据就达到了瓶颈，会影响查询效率，

需要进行水平拆分（分表）进行优化。BOSS系统预测5年内客户手机账户表5000万条以上。解决方案是将手机号表进行水平拆分。

在spt1 spt2 执行表结构sql语句如下：

/* 创建数据库 */
CREATE DATABASE boss;

USE boss;

/* 客户手机号表 */
CREATE TABLE customer (
  id bigint(20) NOT NULL COMMENT '主键',
  cid bigint(20) DEFAULT NULL COMMENT '客户id',
  name varchar(500) DEFAULT NULL COMMENT '客户名称',
  phone varchar(500) DEFAULT NULL COMMENT '电话号',
  provice varchar(500) DEFAULT NULL COMMENT '所属省份',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='电信客户信息（手机号）';


/* 手机号通话记录 */
CREATE TABLE calllog (
  id bigint(20) NOT NULL COMMENT 'id',
  phone_id bigint(20) DEFAULT NULL COMMENT '主键',
  type varchar(10) DEFAULT NULL COMMENT '通话类型',
  duration bigint(20) DEFAULT NULL COMMENT '通话时长（秒）',
  othernum varchar(20) DEFAULT NULL COMMENT '对方电话号',
  PRIMARY KEY (id),
  KEY FK_Reference_1 (phone_id),
  CONSTRAINT FK_Reference_1 FOREIGN KEY (phone_id) REFERENCES customer (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通话记录';


/* 字典表 */
CREATE TABLE dict (
  id bigint(20) NOT NULL COMMENT 'id',
  caption varchar(100) DEFAULT NULL COMMENT '代码类型名称',
  code varchar(10) DEFAULT NULL COMMENT '代码',
  name varchar(10) DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='字典表';

1.原则

以客户表（customer）为例可以采用不同字段进行分表：

序号	分表字段	说明
1	id（主键、或创建时间）	从业务上来看同一个客户的不同手机号分布在不同的数据节点上，可能造成查询效率降低
2	cid（客户 id）、provice(省份)	根据客户 id 去分，两个节点访问平均，一个客户所有的手机号都在同一个数据节点上。

2.安装数据库

此处同上述过程一样采用docker容器模拟不同的数据节点。此处为测试方便创建两个MySQL数据库容器。

docker run --name spt1 -p 3416:3306   -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7 &&\

docker run --name spt2 -p 3426:3306   -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7

执行过程如下：

[root@mycat ~]# docker run --name spt1 -p 3416:3306   -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7 && docker run --name spt2 -p 3426:3306   -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7
c9273c38f676aaf09321c6b117cf9445d5a15a632694480daf02db8cc9352bf6
5b19f22dbefbd00a65aaa6185a0c493518b4d71a5ff10b63f0bef6404efbb9bc
[root@mycat ~]# docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
5b19f22dbefb   mysql:5.7   "docker-entrypoint.s…"   4 seconds ago   Up 2 seconds   33060/tcp, 0.0.0.0:3426->3306/tcp, :::3426->3306/tcp   spt2
c9273c38f676   mysql:5.7   "docker-entrypoint.s…"   6 seconds ago   Up 4 seconds   33060/tcp, 0.0.0.0:3416->3306/tcp, :::3416->3306/tcp   spt1
[root@mycat ~]#

3.创建数据库、表

分别连接两个容器，并执行上述sql脚本用于创建数据库、数据库表。

查询两个容器的ip地址

[root@mycat ~]# docker inspect --format '{{ .NetworkSettings.IPAddress }}'  spt1 | awk '{print "spt1:",$1}' && docker inspect --format '{{ .NetworkSettings.IPAddress }}'  spt2 | awk '{print "spt2:",$1}'

spt1: 172.17.0.2
spt2: 172.17.0.3
[root@mycat ~]#

连接容器spt1，执行数据库脚本

mycat实现分表

根据需求此处需要将customer 进行水平拆分，并分布到两个数据节点spt1、spt2上。需要做如下修改

修改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="spt1">
        <!-- 
            rule="customer_rule"  使用的 分片规则，需要在rule.xml中配置
        -->
        <table name="customer " dataNode="spt1,spt2" rule="customer_rule" ></table>
    </schema>

    <dataNode name="spt1" dataHost="host1" database="boos"/>
    <dataNode name="spt2" dataHost="host2" database="boos"/>

    <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="172.17.0.2:3306" user="root" password="root">
        </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="172.17.0.3:3306" user="root"  password="root">
        </writeHost>
    </dataHost>

</mycat:schema>

修改rule.xml配置customer_rule规则

在 rule 配置文件里新增分片规则 customer_rule，并指定规则适用字段为cid，

<tableRule name="customer_rule">
    <rule>
        <!-- 分片字段-->
         <columns>cid</columns>
        <!-- 分片算法名 -->
         <algorithm>mod-long</algorithm>
    </rule>
 </tableRule>

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

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

    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">2</property>
    </function>

上面 columns 标识将要分片的表字段，algorithm 分片函数，

此种配置非常明确即根据 id 进行十进制求模预算，相比固定分片 hash，此种在批量插入时可能存在批量插入单

事务插入多数据分片，增大事务一致性难度（因此种方式实现最简单所以优先说明）。

启动Mycat，登录Mycat插入数据进行验证

insert into customer (id, cid, name, phone, provice) values('1','1','张飞','13800000001','燕人');
insert into customer (id, cid, name, phone, provice) values('2','2','赵云','13800000002','真定');
insert into customer (id, cid, name, phone, provice) values('3','3','诸葛亮','13800000003','沂南');
insert into customer (id, cid, name, phone, provice) values('4','4','关羽','13800000004','运城');
insert into customer (id, cid, name, phone, provice) values('5','5','刘玄德','13800000005','涿州');
insert into customer (id, cid, name, phone, provice) values('6','6','孙策','13800000006','东吴');

插入数据后在Mycat客户端、数据节点spt1、sp2上分别执行查询发现

数据节点spt1、spt2分别保存一部分数据
mycat客户端可以查询出所有数据

需要注意的点，mycat插入时根据字段分片，需要提供字段枚举

正确的插入sql

insert into customer (id, cid, name, phone, provice) values('5','5','刘玄德','13800000005','涿州');

错误的sql

insert into customer values('6','6','孙策','13800000006','东吴');

不提供列的枚举会报如下错误

MySQL [TESTDB]> insert into customer  values('6','6','孙策','13800000006','东吴');
ERROR 1064 (HY000): partition table, insert must provide ColumnList

Mycat 的分片 join

Join 绝对是关系型数据库中最常用一个特性，然而在分布式环境中,跨分片的 join 确是最复杂的，最难解决一

个问题。

Mycat性能建议

尽量避免使用 Left join 或 Right join,而用 Inner join

在使用 Left join 或 Right join 时，ON 会优先执行，where 条件在最后执行，所以在使用过程中，条件尽

可能的在 ON 语句中判断，减少 where 的执行

少用子查询，而用 join。

Mycat 目前版本支持跨分片的 join,主要实现的方式有四种。

全局表，ER 分片，catletT(人工智能)和 ShareJoin，ShareJoin 在开发版中支持，前面三种方式 1.3.0.1 支

持。

ER分片

MyCAT 借鉴了 NewSQL 领域的新秀 Foundation DB 的设计思路，Foundation DB 创新性的提出了 Table

Group 的概念，其将子表的存储位置依赖于主表，并且物理上紧邻存放，因此彻底解决了 JION 的效率和性能问

题，根据这一思路，提出了基于 E-R 关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分

片上。

customer(客户手机账户表) 采用按照客户id取模这个分片策略，分片在 spt1,spt2 上，calllog（呼叫记录表）依赖父表进行分片，两个

表的关联关系为 customer.id =calllog.phone_id。于是数据分片和存储的示意图如下：

这样一来，分片 spt1 上的的 customer 与 spt1 上的 calllog 就可以进行局部的 JOIN 联合，spt2(...sptn) 上也如此，再合并两个节点的数据即可完成整体的 JOIN，基于 E-R 映射的数据分片模式，基本上解决了 80%以上的企业应用所面临的问题。

实现

修改schema.xml文件，在上次配置基础上修改table标签，在customer 表下添加子标签

<childTable name="calllog" primaryKey="id" joinKey="phone_id" parentKey="id" />

完整的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="spt1">
        <!-- 
                         rule="customer_rule"  自定义的 分片规则，需要在rule.xml中配置
        -->
        <table name="customer" dataNode="spt1,spt2" rule="customer_rule" >
            <!-- 
                name="calllog"          子表（从表）名称
                primaryKey="id"         子表（从表）主键
                joinKey="id"            主表主键（calllog.phone_id）
                parentKey="phone_id"    从表中记录的外键（customer.id）
            -->
            <childTable name="calllog" primaryKey="id" joinKey="phone_id" parentKey="id" />
        </table>
    </schema>

    <dataNode name="spt1" dataHost="host1" database="boss"/>
    <dataNode name="spt2" dataHost="host2" database="boss"/>

    <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="172.17.0.2:3306" user="root" password="root">
        </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="172.17.0.3:3306" user="root"  password="root">
        </writeHost>
    </dataHost>

</mycat:schema>

重新启动mycat（加载配置），登录mycat插入数据

insert into calllog (id, phone_id, type, duration, othernum) values('1','1','01','77','15800000001');
insert into calllog (id, phone_id, type, duration, othernum) values('2','1','02','45','15800000002');
insert into calllog (id, phone_id, type, duration, othernum) values('3','2','01','38','15800000003');
insert into calllog (id, phone_id, type, duration, othernum) values('4','2','02','64','15800000004');
insert into calllog (id, phone_id, type, duration, othernum) values('5','3','01','57','15800000005');
insert into calllog (id, phone_id, type, duration, othernum) values('6','3','02','88','15800000006');
insert into calllog (id, phone_id, type, duration, othernum) values('7','4','01','88','15800000007');
insert into calllog (id, phone_id, type, duration, othernum) values('8','4','02','69','15800000008');
insert into calllog (id, phone_id, type, duration, othernum) values('9','5','01','23','15800000009');
insert into calllog (id, phone_id, type, duration, othernum) values('10','5','02','46','15800000010');
insert into calllog (id, phone_id, type, duration, othernum) values('11','6','01','45','15800000011');
insert into calllog (id, phone_id, type, duration, othernum) values('12','6','02','77','15800000012');

Copy to clipboardErrorCopied

分别在mycat、数据节点spt1、spt2使用join查询

SELECT 
  cus.id,
  cus.name '客户名称',
  log.type '通话类型',
  log.othernum '对方电话号' 
FROM
  customer cus 
  INNER JOIN calllog log
    ON cus.id = log.phone_id 
ORDER BY cus.id ;

Copy to clipboardErrorCopied

12.5 全局表

1.介绍

前几章节已经实现了表的的水平划分（分表）。上述表中还有一个dict(字典表)存储的是一些系统中常用的数据字典，如代表通话类型的代码（01：呼入，02：呼出），这张表如果单独在某一个数据节点（如：spt1）上，会导致另外数据节点（如：spt2）上的数据无法关联查询

一个真实的业务系统中，往往存在大量的类似字典表的表，这些表基本上很少变动，字典表具有以下几个特

性：

• 变动不频繁；

• 数据量总体变化不大；

• 数据规模不大，很少有超过数十万条记录。

对于这类的表，在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关

联，就成了比较棘手的问题，所以 Mycat 中通过数据冗余来解决这类表的 join，即所有的分片都有一份数据的拷

贝，所有将字典表或者符合字典表特性的一些表定义为全局表。

数据冗余是解决跨分片数据 join 的一种很好的思路，也是数据切分规划的另外一条重要规则。

2.实现

修改 schema.xml 配置文件，添加table节点并设置为global类型

<table name="dict" dataNode="spt1,spt2" type="global" ></table>

全量配置文件如下：

<?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="spt1">
        <!-- 
                         rule="customer_rule"  自定义的 分片规则，需要在rule.xml中配置
        -->
        <table name="customer" dataNode="spt1,spt2" rule="customer_rule" >
            <!-- 
                name="calllog"          子表（从表）名称
                primaryKey="id"         子表（从表）主键
                joinKey="phone_id"        从表中记录的外键（calllog.phone_id）
                parentKey="id"            主表主键（customer.id）
            -->
            <childTable name="calllog" primaryKey="id" joinKey="phone_id" parentKey="id" />
        </table>

        <!-- 
            设置 dict表为  global全局表，并在spt1、spt2两个数据节点冗余（内容重复）存在
        -->
        <table name="dict" dataNode="spt1,spt2" type="global" ></table>

    </schema>

    <dataNode name="spt1" dataHost="host1" database="boss"/>
    <dataNode name="spt2" dataHost="host2" database="boss"/>

    <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="172.17.0.2:3306" user="root" password="root">
        </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="172.17.0.3:3306" user="root"  password="root">
        </writeHost>
    </dataHost>

</mycat:schema>

3.验证全局表

登录mycat插入字典表的数据

insert into dict (id, caption, code, name) values('1','ptype','01','呼出');
insert into dict (id, caption, code, name) values('2','ptype','02','呼入');

分别在mycat客户端、数据节点spt1、spt2中查询关联dict表的sql

SELECT 
  cus.id,
  cus.name '客户名称',
  dict.name '通话类型', 
  log.othernum '对方电话号' 
FROM
  customer cus 
  INNER JOIN calllog log
    ON cus.id = log.phone_id 
  INNER JOIN dict ON dict.caption='ptype' AND dict.code = log.type
ORDER BY cus.id ;