MySQL 分表实现方案

MySQL 分表主要有三种实现方式：应用层代码分表、MySQL 分区表(Partition)和使用分库分表中间件。下面详细介绍每种实现方法。

一、应用层代码分表

1. 水平分表实现

-- 创建分表（按用户ID最后一位分10张表）
CREATE TABLE `user_0` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB;

-- 创建user_1到user_9类似结构的表...

应用代码中实现路由逻辑（以PHP为例）：

function getTableName($userId) {
    return 'user_' . ($userId % 10);
}

function insertUser($user) {
    $table = getTableName($user['user_id']);
    $sql = "INSERT INTO {$table} (user_id, name) VALUES (:user_id, :name)";
    // 执行SQL...
}

2. 垂直分表实现

-- 主表
CREATE TABLE `user_base` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 扩展表（存储不常用信息）
CREATE TABLE `user_ext` (
  `user_id` bigint(20) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;

二、MySQL 分区表(Partition)

MySQL 原生支持的分区功能，对应用透明：

1. RANGE 分区（按范围）

CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE(YEAR(sale_date)) (
    PARTITION p_2020 VALUES LESS THAN (2021),
    PARTITION p_2021 VALUES LESS THAN (2022),
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

2. HASH 分区（均匀分布）

CREATE TABLE user_hash (
    id INT NOT NULL,
    username VARCHAR(30),
    created DATE
) PARTITION BY HASH(id) 
PARTITIONS 10;

3. LIST 分区（离散值）

CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(30),
    store_id INT
) PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (1, 3, 5),
    PARTITION pSouth VALUES IN (2, 4, 6),
    PARTITION pWest VALUES IN (7, 8, 9)
);

三、使用分库分表中间件

1. ShardingSphere 配置示例

# 配置分片规则
shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..15}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_${order_id % 16}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ds_${user_id % 2}

2. MyCat 配置示例

<!-- schema.xml -->
<table name="user" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long" />

<!-- rule.xml -->
<tableRule name="mod-long">
    <rule>
        <columns>id</columns>
        <algorithm>mod-long</algorithm>
    </rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <property name="count">3</property>
</function>

分表后的查询处理

单表查询：直接路由到具体分表

SELECT * FROM user_3 WHERE user_id = 123;

多表查询：使用UNION ALL合并结果

SELECT * FROM user_0 WHERE name LIKE '张%'
UNION ALL
SELECT * FROM user_1 WHERE name LIKE '张%'
...
UNION ALL
SELECT * FROM user_9 WHERE name LIKE '张%'

使用中间件：中间件自动处理SQL路由和结果合并

注意事项

分表字段选择要谨慎，尽量选择查询频繁且分布均匀的字段
避免跨分片事务，尽量保证事务在单分片内完成
考虑全局唯一ID生成方案（雪花算法、UUID等）
分表后索引设计要针对分片后的查询模式优化
监控各分表数据量和性能，避免数据倾斜

分表能显著提升大表性能，但会增加系统复杂度，建议在单表数据量达到千万级别后再考虑分表。

MySQL 分区表(Partition)中的1. RANGE 分区（按范围）方法，是要列出未来的年份，来分表吗

MySQL RANGE 分区关于未来年份的处理策略

对于 MySQL 的 RANGE 分区（按范围）方法，是否需要预先列出未来年份来分表，这是一个很好的问题。以下是详细解答：

核心原则

不需要预先创建未来所有年份的分区，但需要合理规划分区策略以应对未来数据增长。

最佳实践建议

提前1-2年分区：保持1-2年的未来分区即可，无需创建多年后的分区
建立分区维护机制：
- 每年年底自动添加下一年分区
- 可以通过事件或外部脚本实现

监控分区使用情况：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'sales';

合并旧分区：对于历史数据可以考虑合并

ALTER TABLE sales REORGANIZE PARTITION p_2020,p_2021 INTO (
    PARTITION p_hist_2020_2021 VALUES LESS THAN (2022)
);

分区命名规范：使用有意义的名称如 p_2023、p_2023_q1 等

为什么不需要预先创建多年分区？

存储效率：空分区也会占用系统资源
管理复杂度：过多分区增加维护难度
灵活性：业务需求可能变化，过早分区可能不符合未来实际需求
性能考虑：分区数量过多可能影响查询性能

通过动态添加分区的方式，既能满足数据增长需求，又能保持分区表的灵活性。

创建有分区的表时为什么会有历史数据分区，刚创建的表不是没有数据吗

关于新建分区表中历史数据分区的解释

您提出了一个很好的观察点：为什么在刚创建的分区表中会包含历史数据分区，即使表还没有任何数据？ 这确实需要详细解释。

核心原因

历史数据分区在新建表时就存在，主要是出于以下几个设计考虑：

1. 数据迁移准备

即使新表刚创建时没有数据，设计时通常考虑未来可能需要导入历史数据
预先创建历史分区可以避免以后重组分区带来的性能开销
例如从旧表迁移数据到新分区表时，历史数据可以直接放入对应分区

2. 统一的分区管理策略

保持分区策略的一致性（过去、现在、未来）
避免因遗漏历史分区导致数据无法正确归类
使应用程序无需关心表是新建还是已存在

3. 分区维护便利性

-- 有了历史分区后，可以方便地整体管理旧数据
ALTER TABLE sales DROP PARTITION p_hist;  -- 快速删除所有历史数据
ALTER TABLE sales REORGANIZE PARTITION p_hist...  -- 重组历史分区

4. 查询优化考虑

即使没有数据，优化器也能基于分区结构生成更好的执行计划
对于时间范围查询，明确的历史分区边界有助于分区裁剪(partition pruning)

实际示例说明

假设我们2023年创建销售表，但需要包含2023年之前的数据：

-- 2023年新建表时的合理分区设计
CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE(YEAR(sale_date)) (
    PARTITION p_hist VALUES LESS THAN (2023),  -- 历史数据分区(空)
    PARTITION p_2023 VALUES LESS THAN (2024),  -- 当前年度分区
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 可能的使用场景：
-- 1. 导入2021-2022年的历史数据 → 进入p_hist
-- 2. 2023年新数据 → 进入p_2023
-- 3. 2024年后新数据 → 进入p_future(之后可重组为p_2024)

为什么不是错误设计？

零开销原则：空分区只占用极少量元数据空间，不影响性能
防御性设计：预防"历史数据突然需要导入"的情况
未来证明：避免以后通过ALTER TABLE重组分区，可能锁表影响生产环境

替代方案对比

如果不预先创建历史分区，未来需要添加时会面临：

-- 需要重组现有分区(可能锁表且耗时)
ALTER TABLE sales REORGANIZE PARTITION p_2023 INTO (
    PARTITION p_hist VALUES LESS THAN (2023),
    PARTITION p_2023 VALUES LESS THAN (2024)
);

因此，即使当前没有历史数据，预先创建历史分区仍是推荐做法，这体现了良好的数据库设计前瞻性。

如何对已有数据的表，进行分区分表

对已有数据的表进行分区分表的完整方案

对已有数据的表进行分区分表需要谨慎操作，以下是详细步骤和注意事项：

一、分区/分表前的准备工作

备份数据

-- 创建临时备份表
CREATE TABLE original_table_backup LIKE original_table;
INSERT INTO original_table_backup SELECT * FROM original_table;

分析表结构和查询模式
- 确定合适的分区键（如时间字段、ID等）
- 检查现有索引和约束条件

评估数据量

SELECT COUNT(*) FROM original_table;
SELECT MIN(partition_column), MAX(partition_column) FROM original_table;

二、分区方案实施

方案A：直接修改原表（适合中小型表）

-- 示例：按时间范围分区
ALTER TABLE original_table 
PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p_2020 VALUES LESS THAN (2021),
    PARTITION p_2021 VALUES LESS THAN (2022),
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_current VALUES LESS THAN MAXVALUE
);

方案B：创建新分区表后迁移数据（推荐大型表）

创建分区表结构

CREATE TABLE partitioned_table (
  id INT NOT NULL,
  create_time DATETIME NOT NULL,
  -- 其他字段...
  PRIMARY KEY (id, create_time)  -- 分区键必须包含在主键中
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p_hist VALUES LESS THAN (TO_DAYS('2023-01-01')),
  PARTITION p_2023 VALUES LESS THAN (TO_DAYS('2024-01-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

分批次迁移数据

-- 使用存储过程分批迁移
DELIMITER //
CREATE PROCEDURE migrate_data()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE batch_size INT DEFAULT 10000;
  DECLARE max_id INT;
  
  SELECT MAX(id) INTO max_id FROM original_table;
  
  WHILE max_id > 0 DO
    INSERT INTO partitioned_table 
    SELECT * FROM original_table 
    WHERE id BETWEEN max_id - batch_size AND max_id;
    
    SET max_id = max_id - batch_size - 1;
    COMMIT;
  END WHILE;
END //
DELIMITER ;

CALL migrate_data();

三、分表方案实施

水平分表步骤

创建分表结构

-- 创建10个分表（示例按ID哈希）
CREATE TABLE original_table_0 LIKE original_table;
CREATE TABLE original_table_1 LIKE original_table;
-- ...创建到original_table_9

迁移数据到分表

INSERT INTO original_table_0 SELECT * FROM original_table WHERE MOD(id, 10) = 0;
INSERT INTO original_table_1 SELECT * FROM original_table WHERE MOD(id, 10) = 1;
-- ...其他分表

创建视图或使用中间件路由

CREATE VIEW original_table AS
SELECT * FROM original_table_0 UNION ALL
SELECT * FROM original_table_1 UNION ALL
-- ...其他分表

四、切换方案

应用无停机切换

-- 1. 停止写入
-- 2. 同步增量数据
-- 3. 重命名表
RENAME TABLE original_table TO original_table_old, 
             partitioned_table TO original_table;
-- 4. 恢复写入

使用双写过渡期
- 应用同时写入新旧表
- 验证新表数据一致性后逐步迁移读操作

五、注意事项

索引调整：分区键必须包含在所有唯一索引中
外键约束：分区表不支持外键，需要改为应用层控制
事务处理：跨分区事务性能较差

监控维护：

-- 检查分区使用情况
SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'partitioned_table';

性能测试：在生产环境实施前充分测试

六、推荐工具

pt-online-schema-change：Percona的在线表结构变更工具
gh-ost：GitHub的在线DDL工具
ShardingSphere：分库分表中间件

mysql分表如何实现