mysql分库分表实战

原创

mob64ca12e2f123 2024-08-06 09:44:51 ©著作权

文章标签 分库分表 User 数据库 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mob64ca12e2f123的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL 分库分表实战解析

随着互联网应用的迅猛发展，数据量的激增使得单一数据库的性能逐渐难以满足需求。此时，分库分表策略应运而生，成为了解决大数据量存储及高并发访问的有效手段。本文将围绕 MySQL 分库分表的实战应用进行详细阐述，并提供相关代码示例。

什么是分库分表

分库分表是对数据库的水平和垂直拆分。具体来说：

水平分表：将一张表的数据横向拆分成多张表，每张表存储相同的数据结构，但只存储部分数据。比如，用户表 users 可分为 users_1、users_2 等.
垂直分库：将一个大型数据库拆分成多个小型数据库，以降低某个数据库的压力。例如，将用户信息和订单信息分成两个不同的数据库。

为什么要进行分库分表

分库分表的主要好处包括：

提高性能：分散了读写压力，提高了数据库的性能。
扩展性：可以动态增加服务器，满足业务增长需求。
维护性：减小了单个库的复杂度，便于数据库的维护和管理。

实现分库分表

以下是简单的分库分表示例，假设我们有一个用户表 users ，我们将其向两张表 users_part1 和 users_part2 进行分割。

1. 创建用户表

首先，我们需要创建基本的用户表。可以使用如下 SQL 语句：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 分表策略

接下来，我们将表分为两部分。我们可以根据 id 来决定数据存储的位置。可以采用哈希法或者范围法来实现，这里我们采用范围法：

id 在 [1, 1000] 的存入 users_part1
id 在 [1001, 2000] 的存入 users_part2

3. 创建分表

CREATE TABLE users_part1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users_part2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4. 数据插入

插入数据时，根据 id 分配到对应的表中：

DELIMITER //

CREATE PROCEDURE insert_user(IN name VARCHAR(50), IN email_address VARCHAR(100))
BEGIN
    DECLARE new_id INT;
    
    -- 获取当前最大ID
    SELECT COALESCE(MAX(id), 0) INTO new_id FROM users_part1;

    IF new_id < 1000 THEN
        INSERT INTO users_part1 (username, email) VALUES (name, email_address);
    ELSE
        SELECT COALESCE(MAX(id), 0) INTO new_id FROM users_part2;
        INSERT INTO users_part2 (username, email) VALUES (name, email_address);
    END IF;
END; //

DELIMITER ;

查询数据

在分库分表的场景下，查询数据时也需要注意哪个表存储了数据。下面是一个简单的查询示例：

CREATE PROCEDURE get_user(IN identifier INT)
BEGIN
    IF identifier <= 1000 THEN
        SELECT * FROM users_part1 WHERE id = identifier;
    ELSE
        SELECT * FROM users_part2 WHERE id = identifier;
    END IF;
END;

序列图示例

为了便于理解分库分表过程，以下是一个简单的序列图，展示了用户插入和查询的过程：

sequenceDiagram
    participant User
    participant DBPart1 as Database PT1
    participant DBPart2 as Database PT2
    User->>DBPart1: Insert User (id <= 1000)
    DBPart1-->>User: Success
    User->>DBPart2: Insert User (id > 1000)
    DBPart2-->>User: Success
    User->>DBPart1: Query User (id <= 1000)
    DBPart1-->>User: User Data
    User->>DBPart2: Query User (id > 1000)
    DBPart2-->>User: User Data