暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 深入理解MyCAT分库分表机制：架构师的秘密武器

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

周同学带您玩AI 2024-08-01
160

一、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
，分别需要进行分库和分表。

环境准备

  1. 数据库服务器

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

  2. 用户表

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

  3. 订单表

    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并验证

  1. 启动MyCAT

    ./bin/mycat start

  2. 连接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
。

环境准备

  1. 数据库服务器

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

  2. 城市表

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

  3. 销售表

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

schema.xml 配置

<schema name="mycatDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="city_dn1,sales_dn1,sales_dn2">
    <!-- 定义cities表的分库规则 -->
    <table name="cities" primaryKey="city_id" dataNode="city_dn1" rule="cityShard" />

    <!-- 定义sales表的分表规则 -->
    <table name="sales" primaryKey="sale_id" dataNode="sales_dn1,sales_dn2" rule="monthShard" />
</schema>

<dataNode name="city_dn1" dataHost="cityHost1" database="cities_db" />

<dataNode name="sales_dn1" dataHost="salesHost1" database="sales_db" />
<dataNode name="sales_dn2" dataHost="salesHost2" database="sales_db" />

<dataHost name="cityHost1" 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="hostC1" url="192.168.1.200:3306" user="root" password="password" />
</dataHost>

<dataHost name="salesHost1" 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="hostS1" url="192.168.1.200:3306" user="root" password="password" />
</dataHost>

<dataHost name="salesHost2" 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="hostS2" url="192.168.1.201:3306" user="root" password="password" />
</dataHost>

<function name="cityShard" class="io.mycat.route.function.PartitionByMod">
    <!-- 城市表按城市ID取模分库 -->
    <property name="count">1</property>
</function>

<function name="monthShard" class="io.mycat.route.function.PartitionByMonth">
    <!-- 销售表按月份分表 -->
    <property name="dateColumn">sale_date</property>
    <property name="dateFormat">yyyy-MM</property>
    <property name="partitionCount">2</property>
</function>

启动MyCAT并验证

  1. 启动MyCAT

    ./bin/mycat start

  2. 连接MyCAT并验证

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

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

    -- 插入城市数据
    INSERT INTO cities (city_name, population) VALUES ('New York', 8419600);
    INSERT INTO cities (city_name, population) VALUES ('Los Angeles', 3980400);

    -- 插入销售数据
    INSERT INTO sales (city_id, amount, sale_date) VALUES (1, 1000.00, '2024-07-01');
    INSERT INTO sales (city_id, amount, sale_date) VALUES (2, 1500.00, '2024-08-01');

    -- 查询数据
    SELECT * FROM cities;
    SELECT * FROM sales;

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

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。

全网第一个AI+PPT提效小册希望能对大家有帮助订阅之后有专属学习交流群 以及AI考试资料分享


mycatmysqlschemamysql数据库heartbeat
文章转载自周同学带您玩AI，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论