mysql 分表扩容

基础概念

MySQL 分表扩容是指在数据库表的数据量不断增长，达到单个表的性能瓶颈时，通过将数据分散到多个表中来提升数据库性能和扩展性的过程。这通常涉及到水平拆分（Sharding），即将一个大表拆分成多个小表，每个小表存储部分数据。

类型

基于范围的分表：根据数据的某个字段值的范围进行分表，例如按时间范围分表。
基于哈希的分表：根据数据的某个字段值进行哈希计算，然后根据哈希值将数据分配到不同的表中。
基于列表的分表：根据数据的某个字段值匹配预定义的列表，将数据分配到不同的表中。

应用场景

大数据量：当单个表的数据量达到几百万甚至上亿条时，性能会显著下降。
高并发：在高并发场景下，单个表的读写压力过大，需要通过分表来分散压力。
业务需求：某些业务场景下，需要根据特定的字段进行数据的分离和管理。

常见问题及解决方法

1. 数据一致性

问题：分表后，如何保证数据的一致性？

解决方法：

使用分布式事务管理器，如XA协议。
采用最终一致性模型，通过消息队列等方式确保数据最终一致。

2. 查询复杂性

问题：分表后，查询操作变得更加复杂。

解决方法：

使用中间件或框架（如MyCat、ShardingSphere）来简化查询操作。
设计合理的数据库结构，尽量减少跨表查询。

3. 数据迁移

问题：扩容时，如何进行数据迁移？

解决方法：

使用在线数据迁移工具，如pt-online-schema-change。
设计数据迁移脚本，确保迁移过程中数据的完整性和一致性。

示例代码

以下是一个简单的基于哈希的分表示例：

-- 创建分表
CREATE TABLE user_0 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

CREATE TABLE user_1 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

-- 插入数据
DELIMITER $$
CREATE PROCEDURE insert_user(IN user_id INT, IN user_name VARCHAR(255), IN user_email VARCHAR(255))
BEGIN
    DECLARE table_name VARCHAR(255);
    SET table_name = CONCAT('user_', user_id % 2);
    SET @sql = CONCAT('INSERT INTO ', table_name, ' (id, name, email) VALUES (', user_id, ', "', user_name, '", "', user_email, '")');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

CALL insert_user(1, 'Alice', 'alice@example.com');
CALL insert_user(2, 'Bob', 'bob@example.com');