Mycat分库分表（一）

随着业务变得越来越复杂，用户越来越多，集中式的架构性能会出现巨大的问题，比如系统会越来越慢，而且时不时会宕机，所以必须要解决高性能和可用性的问题。这个时候数据库的优化就显得尤为重要，在说优化方案前，先分析下数据库性能瓶颈的原因有哪些；

1.1数据库性能瓶颈的分析

比如说在高并发的情况下连接数不够了。或者数据量太大，查询效率变得越来越低。或者是因为存储的问题，数据库所在的机器性能下降了。这些问题，归根结底都是受到了硬件的限制，比如 CPU，内存，磁盘，网络等等。在集中式的架构里面，我们一般是增加硬件设施来解决这些问题的，比喻换CPU，升级内存，扩展磁盘，升级带宽等等。但我们本次要说的不是硬件的优化，作为一个程序员，如果只会增加硬件那也没有啥优越感；言归正传，下面来说下数据库的软优化方案；

1.2数据库优化方案对比

1.2.1、重启

可能有很多朋友觉得这有点搞笑，但重启真是释放资源的最好方法；对于很久都没关闭的数据库服务器，重启会使其释放资源，导致反应速度会很多；所以对于夜间服务器空隙时间长的公司，可以写一个脚本，让数据库在夜间空隙时进行自动重启；

1.2.1 SQL 与索引

当 SQL 语句写得非常复杂，比如关联的表非常多，条件非常多，查询所消耗的时间非常长，这样的一个 SQL 就叫慢 SQL，关于慢SQL我在去年的文章中有讲解，有兴趣可以自己去看下。因为 SQL 语句是我们自己编写的，可控性是最高的，所以第一步就是检查 SQL。在很多情况下我们优化的目标是为了用到索引。

1.2.2 表与存储引擎

如果 SQL 本身没有什么大问题，我们接着就要检查我们查询的目标，也就是表结构的设计有没有问题。比如你对于字段类型和长度的选择，或者表结构是不是需要拆分或者合并，不同的表应该选择什么存储引擎，是不是要分区等等。

1.2.3 架构

表结构如果也没有问题，那就要上升到数据库服务的层面，从架构层面进行优化。因为数据都是在磁盘上存储，如果加了索引还是很慢，干脆可以把数据在内存里面缓存起来，这个时候可以部署缓存服务器。查询数据先查缓存，没有再查数据库，例如（布隆过滤器）。这样既可以减少数据库的压力，又可以提升查询速度。如果一台数据库服务器承受不了访问压力，可以部署集群做负载均衡。当然这些数据库节点应该有自动同步的机制。有了主从同步之后，就可以主从复制实现读写分离，让写的服务都访问 master 服务器，读的请求都访问从服务器。有了读写分离之后，问题并没有完全解决：1、只有一个 master，写的压力没有得到分摊；2、所有的节点都存储相同的数据，在一个节点出现存储瓶颈的时候，磁盘不够用了其他的节点也一样会遇到这个问题。所以这个时候我们要用到分布式环境中一个非常重要的手段：分片，每个节点都只存储总体数据的一部分，那这个就是我们今天要说分库分表。分片以后，为了提升可用性，可以再对分片做冗余。

1.2.4 数据库配置

如果通过架构层面没有解决问题，或者机器虽然配置很高但是性能没有发挥到极致，还可以优化数据库的配置，比如连接数，缓冲区大小等等。

二、分库分表的类型和特点

拆分一共就两种，一种叫垂直拆分，一种叫水平拆分。

垂直切分：基于表或字段划分，表结构不同。我们有单库的分表，也有多库的分库。

水平切分：基于数据划分，表结构相同，数据不同，也有同库的水平切分和多库的切分。

2.1垂直切分

垂直分表有两种，一种是单库的，一种是多库的。字段太多了，就要拆表，表太多了，就要拆库。

2.1.1 单库垂直分表

单库分表，比如：用户信息表，拆分成基本信息表，联系方式表等等。

2.1.2 多库垂直分表

多库垂直分表就是把原来存储在一个库的不同的表，拆分到不同的数据库。比喻说当如果数据库中有一个表的增长速度非常快，当垂直切分并没有从根本上解决单库单表数据量过大的问题。在这个时候，我们还需要对我们的数据做一个水平的切分。这个时候，一个应用需要多个数据库。

2.2水平切分

水平切分就是按照数据的维度分布不同的表中，可以是单库的，也可以是多库的。

2.2.1 单库水平分表

这个拿银行的交易系统讲解最容易，银行的每天交易流水非常大，但是大部分客户只会查近一年或近一个月的流水单，对于历史非常长的流水访问量会少很多，这时就可以对流水表进行水平拆分了。

2.2.2 多库水平分表

另一种是多库的水平分表。比如客户表，我们拆分到多个库存储，表结构是完全一样的。

2.3分库分表带来的问题

前面说了很多分库分表的场景及好处，但世间万物都是有利就有弊；下面就来说下他的弊端。

2.3.1 跨库关联查询

比如在跨库关联时，由于要关联的表是在不同的数据库，那么我们肯定不能直接使用 join 的这种方式去做关联查询。但我们有几个解决方案，例如字段冗余、mycat等。

2.3.2 分布式事务

如果是在一个数据库里面，我们可以用本地事务来控制，但是在不同的数据库里面就不行了。这里必须要出现一个协调者的角色，让大家统一行动，而且要分成多个阶段，一般是先确定都能成功才成功，只要有一个人不能成功，就要全部失败。

三、 Mycat 概念与配置

3.1 Mycat 介绍与核心概念

Mycat的官网网址：http://www.mycat.org.cn/；mycat运行在应用和数据库之间，可以当做一个 MySQL 服务器使用（不论是在工具还是在代码或者命令行中都可以直接连接）。实现对 MySQL 数据库的分库分表，也可以通过 JDBC 支持其他的数据库。

Mycat 的关键特性：

- 可以当做一个 MySQL 数据库来使用
- 支持 MySQL 之外的数据库，通过 JDBC 实现
- 解决了我们提到的所有问题，多表 join、分布式事务、全局序列号、翻页排序
- 支持 ZK 配置，带监控 mycat-web（已经停止维护）
- 2.0 已经发布；文档许久没有更新

3.2 Mycat 配置详解

我们先从官网下载Mycat包，有各种版本，我为了方便下了win

Mycat 解压以后有 5 个目录：

主要的配置文件 server.xml、schema.xml、rule.xml 和具体的分片配置文件。

3.2.1 server.xml

包含系统配置信息。

system 标签：例如字符集、线程数、心跳、分布式事务开关等等。

user 标签：配置登录用户和权限。

<user name="root" defaultAccount="true">
   <property name="password">root</property>
   <property name="schemas">ghymycat,ljxmycat</property>
   
   <!-- 表级 DML 权限设置 -->
   <!--      
   <privileges check="false">
      <schema name="TESTDB" dml="0110" >
         <table name="tb01" dml="0000"></table>
         <table name="tb02" dml="1111"></table>
      </schema>
   </privileges>     
    -->
</user>

3.2.2 schema.xml

schema 在 MySQL 里面跟数据库是等价的。schema.xml 包括逻辑库、表、分片规则、分片节点和数据源，可以定义多个 schema。

这里面有三个主要的标签（table、dataNode、dataHost）：

<table/>

表名和库名最好都用小写

定义了逻辑表，以及逻辑表分布的节点和分片规则：

<schema name="ghymycat" checkSQLschema="false" sqlMaxLimit="100">
        <!--范围分片表-->
        <table name="customer" primaryKey="id" dataNode="103-ghymycat,104-ghymycat,105-ghymycat" rule="auto-sharding-long" />
        <!--ER分片表-->
        <table name="er_scope" dataNode="103-ghymycat,104-ghymycat,105-ghymycat" rule="mod-long-order" >
            <childTable name="er_detail" primaryKey="id" joinKey="er_id" parentKey="er_id"/>
        </table>
        <table name="mycat_sequence" dataNode="103-ghymycat" autoIncrement="true" primaryKey="id"></table>
    </schema>

    <schema name="ljxmycat" checkSQLschema="false" sqlMaxLimit="100">
        <!--取模分片表-->
        <table name="student" primaryKey="sid" dataNode="103-ljxmycat,104-ljxmycat,105-ljxmycat" rule="mod-long" />
        <!--非分片表-->
        <table name="noshard" primaryKey="id" autoIncrement="true" dataNode="103-ljxmycat" />
        <!--全局表-->
        <table name="dict" primaryKey="id" type="global" dataNode="103-ljxmycat,104-ljxmycat,105-ljxmycat" />
        <!--单库分片表-->
        <table name="fee" primaryKey="id" subTables="fee2025$1-3" dataNode="103-ljxmycat" rule="sharding-by-month" />
    </schema>

配置	作用
checkSQLschema	在查询 SQL 中去掉逻辑库名
sqlMaxLimit	自动加上 limit 控制数据的返回
primaryKey	指定该逻辑表对应真实表的主键。MyCat 会缓存主键（通过 primaryKey 属性配置）与具体 dataNode 的信息。 primaryKey 当分片规则（rule）使用非主键进行分片时，那么在使用主键进行查询时，MyCat 就会通过缓存先确定记录在哪个 dataNode 上，然后再在该 dataNode 上执行查询。如果没有缓存/缓存并没有命中的话，还是会发送语句给所有的 dataNode。
dataNode	数据分片的节点
autoIncrement	自增长（全局序列），true 代表主键使用自增长策略
type	全局表：global。其他：不配置

<!--数据节点与物理数据库的对应关系-->

<dataNode name="103-ghymycat" dataHost="host103" database="ghymycat" />
    <dataNode name="104-ghymycat" dataHost="host104" database="ghymycat" />
    <dataNode name="105-ghymycat" dataHost="host105" database="ghymycat" />

    <dataNode name="103-ljxmycat" dataHost="host103" database="ljxmycat" />
    <dataNode name="104-ljxmycat" dataHost="host104" database="ljxmycat" />
    <dataNode name="105-ljxmycat" dataHost="host105" database="ljxmycat" />

配置物理主机的信息，readhost 是从属于 writehost 的。

<dataHost name="host103" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.2.103:3306" user="root"
                   password="root">
        </writeHost>
    </dataHost>

    <dataHost name="host104" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.2.104:3306" user="root"
                   password="root">
            <!-- <readHost host="hostS1"></readHost>  -->
        </writeHost>
    </dataHost>

    <dataHost name="host105" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.2.105:3306" user="root"
                   password="root">
        </writeHost>
    </dataHost>

balance：负载的配置，决定 select 语句的负载

writeType：读写分离的配置，决定 update、delete、insert 语句的负载

switchType：主从切换配置

3.2.3 rule.xml

定义了分片规则和算法分片规则：

<tableRule name="auto-sharding-long">
   <rule>
      <columns>id</columns>
      <algorithm>rang-long</algorithm>
   </rule>
</tableRule>

分片算法：

<function name="rang-long"
   class="io.mycat.route.function.AutoPartitionByLong">
   <property name="mapFile">autopartition-long.txt</property>
</function>

分片配置：

autopartition-long.txt

10001-20000=1

0-10000=0

20001-100000=2

3.3 Mycat 分片验证

先准备三个数据库，我是在建了三个虚拟机，分别在每台上装了mysql

--在所有数据库节点上创建数据库ghymycat，创建3张表
-- 范围分片表
CREATE TABLE `scope` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ER分片表
CREATE TABLE `er_scope` (
  `er_id` int(11) NOT NULL ,
  `uid` int(11) DEFAULT NULL ,
  `nums` int(11) DEFAULT NULL,
  `state` int(2) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`er_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ER分片表
CREATE TABLE `er_detail` (
  `er_id` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `goods_id` int(11) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `is_pay` int(2) DEFAULT NULL,
  `is_ship` int(2) DEFAULT NULL,
  `status` int(2) DEFAULT NULL,
  PRIMARY KEY (`er_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建表，在三个ljxmycat库中创建dict及student表
CREATE TABLE `dict` (
  `id` int(11) DEFAULT NULL,
  `param_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `param_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `student` (
  `sid` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `qq` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 在第一个数据库ljxmycat节点（103）数据库创建非分片表
CREATE TABLE `noshard` (
  `id` bigint(30) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

truncate table noshard;


-- 库内分表
-- 在第一个数据库ljxmycat节点（103）数据库创建单库分片表
CREATE TABLE `fee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `fee20251`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) 
);
CREATE TABLE `fee20252`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) 
);
CREATE TABLE `fee20253`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) 
);

启动mycat

如上图红框所示就代表启动成功，mycat的默认端口是8066，我们用Navicat premium连接成功所会如下图所示，他会把所有表内容进行一个聚合；

3.3.1 范围分片

在mycat的ghymycat中执行分片测试数据，scpoe是按范围进行分配的，分配的规则前面也有配置

-- 范围分片scope表
INSERT INTO `scope` (`id`, `name`) VALUES (6666, '张三');
INSERT INTO `scope` (`id`, `name`) VALUES (7777, '李四');
INSERT INTO `scope` (`id`, `name`) VALUES (16666, '王五');
INSERT INTO `scope` (`id`, `name`) VALUES (17777, '孙六');
INSERT INTO `scope` (`id`, `name`) VALUES (26666, '王二麻子');
INSERT INTO `scope` (`id`, `name`) VALUES (27777, '赵七')

插入数据完成后我们查看，会发现是分散在三个数据库上的

3.3.2 取模分片表

ljxmycat库中的student表，我们从下图可知，我们配置的模数是3

-- 取模分片表(ljxmycat库中的student表)
-- 测试取模分片（在mycat连接中ljxmycat数据库中执行）
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (1, '张三', '166669999');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (4, '李四', '655556666');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (2, '王五', '466669999');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (5, '赵六', '265286999');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (3, '李大郎', '368828888');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (6, '孙子', '516895555');

插入完成后自己可以查下，会发现模余数0的在第一个接点，余数1的在第二个节点上，余数2的在第三个节点上

3.3.3 取模分片（ER 表）

在实际生产环境中我们有些表的数据是存在逻辑的主外键关系的，比如订单表 er_scope和er_detail，有主外键的配置如下图

执行插入数据

INSERT INTO `er_scope` (`er_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (1, 1000001, 1, 2, '2025-9-23 14:35:37', '2025-9-23 14:35:37');

INSERT INTO `er_scope` (`er_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (2, 1000002, 1, 2, '2025-9-24 14:35:37', '2025-9-24 14:35:37');

INSERT INTO `er_scope` (`er_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (3, 1000003, 3, 1, '2025-9-25 11:35:49', '2025-9-25 11:35:49');




INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (3, 20180001, 85114752, 19.99, 1, 1, 1);

INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (1, 20180002, 25411251, 1280.00, 1, 1, 0);

INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (1, 20180003, 62145412, 288.00, 1, 1, 2);

INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180004, 21456985, 399.00, 1, 1, 2);

INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180005, 21457452, 1680.00, 1, 1, 2);

INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180006, 65214789, 9999.00, 1, 1, 3);

插入完成后我们会发现模数分布规则和上一个例子一样，唯一区别的是，关联的外键表数据存放会和主表放在同一个库里面

3.3.4 全局表

ljxmycat数据库，dict 表：全局表

执行下面语句插入

INSERT INTO `dict` (`id`, `param_code`, `param_name`) VALUES (1, '111', '全局就是所有库都有一样的');

3.3.5 非分片表

ljxmycat数据库，noshard 表

INSERT INTO `noshard` (`id`, `name`) VALUES (1, '分片的数据');

3.3.6 库内分表

插入数据

INSERT INTO `fee` (`id`, `create_time`) VALUES (1, '2025-1-1 14:46:19');

INSERT INTO `fee` (`id`, `create_time`) VALUES (2, '2025-2-1 14:46:19');

INSERT INTO `fee` (`id`, `create_time`) VALUES (3, '2025-3-1 14:46:19');

然后自己在对应的物理库查看可以看到在一个库里分表了

3.4 Mycat 全局 ID

关于全局ID这块网上有篇文章写的挺全的，如果要配置可以参照文章中配置,网址：https://blog.51cto.com/mynode/1910570

posted @ 2020-11-15 20:39 童话述说我的结局阅读(1658) 评论(0) 编辑收藏举报

刷新页面返回顶部

童话述说我的结局

这短短的一生我们最终都会失去，不妨大胆一点，爱一个人，攀一座山，追一个梦

Mycat分库分表（一）

1.1数据库性能瓶颈的分析

1.2数据库优化方案对比

1.2.1、重启

1.2.3 架构

1.2.4 数据库配置

二、分库分表的类型和特点

2.1垂直切分

2.1.1 单库垂直分表

2.1.2 多库垂直分表

2.2水平切分

2.2.1 单库水平分表

2.2.2 多库水平分表

2.3分库分表带来的问题

2.3.1 跨库关联查询

2.3.2 分布式事务

三、 Mycat 概念与配置

3.1 Mycat 介绍与核心概念

3.2 Mycat 配置详解

3.2.1 server.xml

3.2.2 schema.xml

3.2.3 rule.xml

3.3 Mycat 分片验证

3.3.1 范围分片

3.3.2 取模分片表

3.3.3 取模分片（ER 表）

3.3.4 全局表

3.3.5 非分片表

3.3.6 库内分表

3.4 Mycat 全局 ID

公告

童话述说我的结局

这短短的一生我们最终都会失去，不妨大胆一点，爱一个人，攀一座山，追一个梦

Mycat分库分表（一）

1.1数据库性能瓶颈的分析

1.2数据库优化方案对比

1.2.1、重启

1.2.3 架构

1.2.4 数据库配置

二、 分库分表的类型和特点

2.1垂直切分

2.1.1 单库垂直分表

2.1.2 多库垂直分表

2.2水平切分

2.2.1 单库水平分表

2.2.2 多库水平分表

2.3分库分表带来的问题

2.3.1 跨库关联查询

2.3.2 分布式事务

三、 Mycat 概念与配置

3.1 Mycat 介绍与核心概念

3.2 Mycat 配置详解

3.2.1 server.xml

3.2.2 schema.xml

3.2.3 rule.xml

3.3 Mycat 分片验证

3.3.1 范围分片

3.3.2 取模分片表

3.3.3 取模分片（ER 表）

3.3.4 全局表

3.3.5 非分片表

3.3.6 库内分表

3.4 Mycat 全局 ID

公告

二、分库分表的类型和特点