深入理解MyCAT分库分表机制：架构师的秘密武器

周同学的技术栈

已于 2024-08-01 23:11:43 修改

阅读量589

点赞数 5

分类专栏：数据库技术之路文章标签： oracle 数据库

于 2024-08-01 23:10:39 首次发布

本文链接：https://blog.csdn.net/zgt_certificate/article/details/140859594

版权

数据库技术之路专栏收录该内容

130 篇文章 3 订阅

订阅专栏

在这里插入图片描述

一、MyCAT分库和分表的概念

1. 分库（Database Sharding）

分库是将一个大数据库拆分成多个小数据库，以减小单个数据库的压力并提高系统的扩展性。每个子数据库可以分布在不同的服务器上，从而分散负载并提高性能。

示例：

假设我们有一个用户信息数据库users_db，其中包含了大量的用户数据。分库的思想是将这个数据库拆分为多个子库，例如users_db_1、users_db_2等。每个子库存储特定用户的信息（如按用户ID或地区划分）。

2. 分表（Table Sharding）

分表是将一个大表拆分成多个小表，以提高查询性能和写入性能。分表可以在同一个数据库实例中实现，也可以在多个数据库实例中实现。

示例：

假设我们有一个订单表orders，包含了所有订单信息。通过分表，我们可以将这张大表拆分成多个小表，如orders_2024、orders_2023等，每个小表分别存储不同年份的订单数据。

二、MyCAT中的分库、分表配置

MyCAT通过配置文件schema.xml来定义数据库分库和分表的规则。以下是详细的配置步骤及实例。

1. 配置分库

分库在MyCAT中主要通过定义dataNode和dataHost来实现。

示例：

假设我们有一个user表，计划根据用户ID将其分库到两个数据库实例中：user_db_1和user_db_2。

schema.xml 配置示例

<schema name="mycatDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1,dn2">
    <!-- 定义user表的分片规则 -->
    <table name="user" primaryKey="id" dataNode="dn1,dn2" rule="userShard" />
</schema>

<dataNode name="dn1" dataHost="localhost1" database="user_db_1" />
<dataNode name="dn2" dataHost="localhost2" database="user_db_2" />

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>show status like 'threads_connected'</heartbeat>
    <writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="password" />
</dataHost>

<dataHost name="localhost2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>show status like 'threads_connected'</heartbeat>
    <writeHost host="hostM2" url="192.168.1.101:3306" user="root" password="password" />
</dataHost>

在上述配置中：

dataNode：定义了两个数据节点dn1和dn2，分别指向不同的数据库实例user_db_1和user_db_2。
dataHost：定义了不同的数据主机localhost1和localhost2，它们对应不同的数据库服务器。

2. 配置分表

分表在MyCAT中通过定义table元素中的分片规则（rule）来实现。

示例：

假设我们有一个订单表orders，根据订单ID进行分表，将其分为两个子表：orders_0和orders_1。

schema.xml 配置示例

<schema name="mycatDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1,dn2">
    <!-- 定义orders表的分片规则 -->
    <table name="orders" primaryKey="order_id" dataNode="dn1,dn2" rule="orderShard" />
</schema>

<dataNode name="dn1" dataHost="localhost1" database="orders_db" />
<dataNode name="dn2" dataHost="localhost1" database="orders_db" />

<function name="orderShard" class="io.mycat.route.function.PartitionByMod">
    <!-- 分片数量 -->
    <property name="count">2</property>
</function>

在上述配置中：

table元素中的rule定义了orderShard，这是一种分片函数，用于根据订单ID对表进行分片。
PartitionByMod函数表示按取模分片，将数据分为两片。

3. 自定义分片规则

除了默认的分片规则，MyCAT允许用户自定义分片规则以满足具体业务需求。

示例：

假设我们要根据用户注册时间对用户表进行分片。

schema.xml 自定义规则示例

<schema name="mycatDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1,dn2,dn3">
    <!-- 自定义分片规则 -->
    <table name="user" primaryKey="id" dataNode="dn1,dn2,dn3" rule="timeShard" />
</schema>

<dataNode name="dn1" dataHost="localhost1" database="user_db_1" />
<dataNode name="dn2" dataHost="localhost1" database="user_db_2" />
<dataNode name="dn3" dataHost="localhost1" database="user_db_3" />

<function name="timeShard" class="io.mycat.route.function.PartitionByDate">
    <!-- 按注册时间进行分片 -->
    <property name="dateColumn">register_time</property>
    <property name="dateFormat">yyyy-MM-dd</property>
    <property name="partitionCount">3</property>
</function>

在这个例子中：

timeShard是一个自定义的分片函数，用于根据用户注册时间将用户数据分为三片。
PartitionByDate函数通过日期进行分片，可以灵活地将数据分布到不同的节点上。

三、完整实例

示例1：基于用户ID的分库分表

假设我们有一个用户表users和订单表orders，分别需要进行分库和分表。

环境准备

数据库服务器
- MySQL服务器1：192.168.1.100，数据库名：users_db_1，orders_db
- MySQL服务器2：192.168.1.101，数据库名：users_db_2

用户表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    register_time DATETIME
);

订单表

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    quantity INT,
    order_time DATETIME
);

schema.xml 配置

<schema name="mycatDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="user_dn1,user_dn2,order_dn1,order_dn2">
    <!-- 定义users表的分库规则 -->
    <table name="users" primaryKey="id" dataNode="user_dn1,user_dn2" rule="userShard" />

    <!-- 定义orders表的分表规则 -->
    <table name="orders" primaryKey="order_id" dataNode="order_dn1,order_dn2" rule="orderShard" />
</schema>

<dataNode name="user_dn1" dataHost="userHost1" database="users_db_1" />
<dataNode name="user_dn2" dataHost="userHost2" database="users_db_2" />

<dataNode name="order_dn1" dataHost="orderHost1" database="orders_db" />
<dataNode name="order_dn2" dataHost="orderHost1" database="orders_db" />

<dataHost name="userHost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>show status like 'threads_connected'</heartbeat>
    <writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="password" />
</dataHost>

<dataHost name="userHost2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat

>show status like 'threads_connected'</heartbeat>
    <writeHost host="hostM2" url="192.168.1.101:3306" user="root" password="password" />
</dataHost>

<dataHost name="orderHost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>show status like 'threads_connected'</heartbeat>
    <writeHost host="hostO1" url="192.168.1.100:3306" user="root" password="password" />
</dataHost>

<function name="userShard" class="io.mycat.route.function.PartitionByMod">
    <!-- 用户表按ID取模分库 -->
    <property name="count">2</property>
</function>

<function name="orderShard" class="io.mycat.route.function.PartitionByMod">
    <!-- 订单表按订单ID取模分表 -->
    <property name="count">2</property>
</function>

启动MyCAT并验证

启动MyCAT
```
./bin/mycat start
```

连接MyCAT并验证

mysql -h 127.0.0.1 -P 8066 -u root -p

执行以下SQL语句进行验证：

-- 插入数据
INSERT INTO users (name, email, register_time) VALUES ('Alice', 'alice@example.com', NOW());
INSERT INTO orders (user_id, product_id, quantity, order_time) VALUES (1, 101, 2, NOW());

-- 查询数据
SELECT * FROM users;
SELECT * FROM orders;

如果能够成功插入和查询数据，则说明分库分表配置成功。

示例2：基于城市的分库和基于月份的分表

假设我们有一个城市数据表cities和月度销售表sales。

环境准备

数据库服务器
- MySQL服务器1：192.168.1.200，数据库名：cities_db，sales_db
- MySQL服务器2：192.168.1.201，数据库名：sales_db

城市表

CREATE TABLE cities (
    city_id INT AUTO_INCREMENT PRIMARY KEY,
    city_name VARCHAR(255),
    population INT
);

销售表

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    city_id INT,
    amount DECIMAL(10, 2),
    sale_date DATE
);