MySQL 分库分表实战（一）

在面对大规模数据和高并发访问时，单个数据库或单表可能会成为系统瓶颈，影响系统的性能和可扩展性。分库分表是解决海量数据存储和访问性能问题的常用技术手段，通过将数据拆分到多个库或表中，实现读写分离、负载均衡和水平扩展。

一、分库分表的核心概念

1. 什么是分库分表？

• 分库：将数据水平拆分到多个数据库实例中，每个库存储一部分数据。
• 分表：在同一个数据库中，将大表拆分为多个小表，每个小表存储部分数据。

2. 为什么需要分库分表？

• 性能瓶颈：单表数据量过大，导致查询缓慢。
• 存储瓶颈：单个数据库实例存储能力有限。
• 高并发需求：单个数据库无法承受大量并发请求。
• 数据库主从架构不足：主从复制仅解决了读性能问题，写入仍存在瓶颈。

二、分库分表策略

1. 分库策略

• 按业务模块分库：不同业务模块独立数据库。例如：订单库、用户库。
• 水平分库：相同业务数据按照一定规则（如 user_id 取模）分布在多个数据库中。

2. 分表策略

• 范围分表：按时间、ID 范围分表。例如：订单表按月份拆分。
• 哈希分表：通过哈希函数将数据均匀分布到多个表中。
• 按字段分表：根据业务关键字段（如 user_id）取模分表。

三、实战：订单系统分库分表设计

1. 需求分析

• 每天新增订单百万级，订单表增长速度快，单表逐渐达到亿级数据。
• 查询订单主要通过 user_id 和 order_id 进行。
• 目标：将订单表拆分，减少单表数据量，提升查询性能。

2. 数据库设计

订单主表（order_main）结构：

四、分库分表方案

1. 分表策略：按用户ID分表

• 将订单数据按照用户ID分散存储到多个表中，减少单表数据量。
• 例如：
  • 表 order_0 存储 user_id % 4 = 0 的数据
  • 表 order_1 存储 user_id % 4 = 1 的数据
  • 以此类推…

2. 创建分表 SQL

-- 创建4个分表
CREATE TABLE order_0 (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(10, 2),
    status VARCHAR(20),
    create_time TIMESTAMP
);

CREATE TABLE order_1 LIKE order_0;
CREATE TABLE order_2 LIKE order_0;
CREATE TABLE order_3 LIKE order_0;

3. 编写分表插入逻辑

-- 插入订单数据
DELIMITER //
CREATE PROCEDURE insert_order(
    IN p_order_id BIGINT,
    IN p_user_id BIGINT,
    IN p_amount DECIMAL(10,2),
    IN p_status VARCHAR(20)
)
BEGIN
    SET @table_name = CONCAT('order_', p_user_id % 4);
    SET @sql = CONCAT(
        'INSERT INTO ', @table_name, 
        ' (order_id, user_id, amount, status, create_time) ',
        'VALUES (?, ?, ?, ?, NOW())'
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING p_order_id, p_user_id, p_amount, p_status;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

五、查询分表数据

-- 查询订单（user_id = 101 的订单记录）
DELIMITER //
CREATE PROCEDURE select_order(IN p_user_id BIGINT)
BEGIN
    SET @table_name = CONCAT('order_', p_user_id % 4);
    SET @sql = CONCAT('SELECT * FROM ', @table_name, ' WHERE user_id = ?');
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING p_user_id;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

六、分库分表的路由逻辑实现

在应用层或中间件中，需要实现分库分表路由逻辑。以下是简单的 Java 示例：

public class OrderRouter {
    private static final int DB_COUNT = 2;
    private static final int TABLE_COUNT = 4;

    // 根据 user_id 选择数据库
    public String getDatabase(long userId) {
        int dbIndex = (int) (userId % DB_COUNT);
        return "db_order_" + dbIndex;
    }

    // 根据 user_id 选择表
    public String getTable(long userId) {
        int tableIndex = (int) (userId % TABLE_COUNT);
        return "order_" + tableIndex;
    }
}

七、批量删除旧订单

需求：删除 1 年前的历史订单数据，避免长事务阻塞。

-- 分批删除旧订单
DELIMITER //
CREATE PROCEDURE batch_delete_orders(IN days_ago INT)
BEGIN
    DECLARE rows_affected INT DEFAULT 1;

    REPEAT
        DELETE FROM order_0 WHERE create_time < NOW() - INTERVAL days_ago DAY LIMIT 1000;
        SET rows_affected = ROW_COUNT();
    UNTIL rows_affected = 0 END REPEAT;

    REPEAT
        DELETE FROM order_1 WHERE create_time < NOW() - INTERVAL days_ago DAY LIMIT 1000;
        SET rows_affected = ROW_COUNT();
    UNTIL rows_affected = 0 END REPEAT;
END //
DELIMITER ;

八、分库分表后的数据汇总与查询

问题：分库分表后无法直接执行跨表或跨库查询。
解决方法：

1. 中间件：使用 Sharding-JDBC、MyCat 等数据库中间件，自动路由和聚合查询。
2. 数据汇总表：定期将分表数据汇总到一个大表，便于查询分析。
3. 应用层合并：在应用层进行多表查询，将结果合并处理。

九、分库分表带来的问题与解决方案

十、总结

1. 分库分表是一种解决数据库扩展性问题的有效方案，但需要权衡复杂性和开发成本。
2. 分表策略需结合业务特性选择，常用的包括哈希分表、范围分表和按时间分表。
3. 通过存储过程或应用层实现分表路由，提高分库分表操作的灵活性和可维护性。
4. 定期清理历史数据，减少存储压力，避免表数据量过大导致性能下降。

分库分表是数据库架构设计中不可或缺的重要技术，合理设计能显著提升系统的稳定性和可扩展性。