数据库迁移策略

简介

数据库迁移是将数据库结构变更安全地应用到不同环境的过程。EF Core Migrations 是 .NET 生态最主流的迁移工具。掌握迁移的创建、执行、回滚和生产环境策略，是持续交付的关键环节。

特点

• 1.版本管理 — 每次变更生成迁移文件
• 2.自动生成 — 对比模型自动生成 SQL
• 3.可回滚 — 支持回退到任意版本
• 4.多环境 — 开发/测试/生产统一管理

EF Core Migrations

基本命令

# 安装工具
dotnet tool install --global dotnet-ef

# 创建迁移
dotnet ef migrations add InitialCreate
dotnet ef migrations add AddUserEmail

# 应用迁移
dotnet ef database update
dotnet ef database update AddUserEmail         # 更新到指定版本

# 回滚
dotnet ef database update AddUserEmail         # 回滚到指定版本
dotnet ef database update 0                    # 回滚所有

# 移除最后一个迁移（未应用时）
dotnet ef migrations remove

# 生成 SQL 脚本（不执行）
dotnet ef migrations script
dotnet ef migrations script AddUserEmail       # 从指定版本开始
dotnet ef migrations script --idempotent       # 幂等脚本（生产推荐）

# 查看迁移状态
dotnet ef migrations list
dotnet ef database info

代码中的迁移

/// <summary>
/// 应用启动时自动迁移
/// </summary>
var app = builder.Build();

// 自动应用所有未执行的迁移
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    db.Database.Migrate();
}

app.Run();

迁移文件示例

/// <summary>
/// 自动生成的迁移文件
/// </summary>
public partial class AddUserEmail : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 添加列
        migrationBuilder.AddColumn<string>(
            name: "Email",
            table: "Users",
            type: "nvarchar(100)",
            maxLength: 100,
            nullable: false,
            defaultValue: "");

        // 创建索引
        migrationBuilder.CreateIndex(
            name: "IX_Users_Email",
            table: "Users",
            column: "Email",
            unique: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // 回滚操作（逆序）
        migrationBuilder.DropIndex(
            name: "IX_Users_Email",
            table: "Users");

        migrationBuilder.DropColumn(
            name: "Email",
            table: "Users");
    }
}

自定义迁移

手写 SQL 迁移

/// <summary>
/// 包含手写 SQL 的迁移
/// </summary>
public partial class SeedRoles : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 自动生成的部分...

        // 手写 SQL — 插入种子数据
        migrationBuilder.Sql(@"
            INSERT INTO Roles (Name, Description) VALUES
            ('Admin', '系统管理员'),
            ('User', '普通用户'),
            ('Manager', '管理者')
        ");

        // 手写 SQL — 数据转换
        migrationBuilder.Sql(@"
            UPDATE Users
            SET Email = LOWER(Email)
            WHERE Email IS NOT NULL
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DELETE FROM Roles WHERE Name IN ('Admin', 'User', 'Manager')");
    }
}

数据迁移（非结构变更）

/// <summary>
/// 纯数据迁移 — 不涉及表结构变更
/// </summary>
public partial class MigrateUserNames : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 将用户名统一转为首字母大写
        migrationBuilder.Sql(@"
            UPDATE Users
            SET UserName = UPPER(SUBSTRING(UserName, 1, 1)) + LOWER(SUBSTRING(UserName, 2, LEN(UserName)))
            WHERE UserName IS NOT NULL
        ");

        // 根据旧字段填充新字段
        migrationBuilder.Sql(@"
            UPDATE Orders
            SET CustomerName = (
                SELECT UserName FROM Users WHERE Users.Id = Orders.UserId
            )
            WHERE CustomerName IS NULL
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // 数据迁移通常不可回滚
        // 可以记录日志或标记
    }
}

生产环境迁移策略

安全迁移流程

# 1. 开发环境创建迁移
dotnet ef migrations add AddProductCategory

# 2. 审查生成的迁移文件
# 检查 Up() 和 Down() 方法
# 确认 SQL 不会锁表或丢失数据

# 3. 生成幂等 SQL 脚本
dotnet ef migrations script --idempotent --output migrate.sql

# 4. 在测试环境验证
sqlcmd -S test-db -d MyApp -i migrate.sql

# 5. 生产环境执行（维护窗口）
sqlcmd -S prod-db -d MyApp -i migrate.sql

危险变更处理

/// <summary>
/// 安全的列变更 — 分步迁移
/// </summary>

// 迁移 1：添加新列（不删除旧列）
public partial class Step1_AddNewColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "PhoneNumber",
            table: "Users",
            type: "nvarchar(20)",
            nullable: true);  // 允许 NULL
    }
}

// 迁移 2：数据迁移（填充新列）
public partial class Step2_MigrateData : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            UPDATE Users
            SET PhoneNumber = Phone
            WHERE Phone IS NOT NULL
        ");
    }
}

// 迁移 3：应用约束 + 删除旧列（确认无误后）
public partial class Step3_Finalize : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 添加 NOT NULL 约束
        migrationBuilder.AlterColumn<string>(
            name: "PhoneNumber",
            table: "Users",
            nullable: false);

        // 删除旧列
        migrationBuilder.DropColumn(
            name: "Phone",
            table: "Users");
    }
}

大表变更策略

避免锁表的变更

-- 大表添加列（MySQL）
-- 好的做法：使用 ALGORITHM=INPLACE
ALTER TABLE large_table ADD COLUMN new_col INT DEFAULT 0,
ALGORITHM=INPLACE, LOCK=NONE;

-- SQL Server — 在线索引重建
ALTER INDEX PK_large_table ON large_table REBUILD WITH (ONLINE = ON);

-- 分批更新
-- 差：一次性更新百万行
UPDATE large_table SET status = 'active' WHERE status IS NULL;

-- 好：分批更新
WHILE 1 = 1
BEGIN
    UPDATE TOP (10000) large_table
    SET status = 'active'
    WHERE status IS NULL;

    IF @@ROWCOUNT = 0 BREAK;
    WAITFOR DELAY '00:00:01';  -- 间隔1秒释放锁
END

迁移最佳实践

场景	建议
添加列	允许 NULL 或有默认值
删除列	分两步：先忽略，再删除
重命名列	先加新列，迁移数据，再删旧列
修改类型	分步迁移，先兼容后切换
大表变更	分批操作，低峰执行
种子数据	使用 migrationBuilder.Sql

数据迁移实战

数据迁移模板

/// <summary>
/// 数据迁移：分批处理大表数据
/// </summary>
public partial class MigrateUserData : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 步骤 1：创建目标列（允许 NULL）
        migrationBuilder.AddColumn<string>(
            name: "full_name",
            table: "users",
            type: "nvarchar(200)",
            nullable: true);

        // 步骤 2：分批迁移数据
        migrationBuilder.Sql(@"
            DECLARE @BatchSize INT = 5000;
            DECLARE @Processed INT = 0;
            DECLARE @Total INT;

            SELECT @Total = COUNT(*) FROM users WHERE full_name IS NULL AND (first_name IS NOT NULL OR last_name IS NOT NULL);

            WHILE @Processed < @Total
            BEGIN
                UPDATE TOP (@BatchSize) users
                SET full_name = COALESCE(first_name, '') + ' ' + COALESCE(last_name, '')
                WHERE full_name IS NULL
                  AND (first_name IS NOT NULL OR last_name IS NOT NULL);

                SET @Processed = @Processed + @BatchSize;
                WAITFOR DELAY '00:00:00.5';  -- 间隔 500ms，减少锁争用
            END
        ");

        // 步骤 3：添加默认值
        migrationBuilder.AlterColumn<string>(
            name: "full_name",
            table: "users",
            type: "nvarchar(200)",
            nullable: false,
            defaultValue: "");
    }
}

跨数据库数据迁移

# MySQL 数据导出导入
# 导出指定表
mysqldump -u root -p --single-transaction --quick \
  --tables source_db users orders > migration_data.sql

# 导出查询结果
mysql -u root -p -e "SELECT * FROM users WHERE updated_at > '2026-01-01'" \
  source_db > users_export.csv

# 导入到目标库
mysql -u root -p target_db < migration_data.sql

# CSV 导入
LOAD DATA INFILE '/tmp/users_export.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

# PostgreSQL 数据迁移
pg_dump -U admin -t users -t orders source_db | psql -U admin target_db

# SQL Server 数据导出
bcp "SELECT * FROM source_db.dbo.users" queryout users_export.csv -c -t, -S server -U admin

# SQL Server 批量导入
BULK INSERT users FROM '/tmp/users_export.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2);

数据校验与对比

-- 迁移后数据校验

-- 1. 行数对比
SELECT COUNT(*) FROM source_db.users;
SELECT COUNT(*) FROM target_db.users;

-- 2. 关键字段抽样对比
SELECT id, name, email, COUNT(*) AS cnt
FROM source_db.users
GROUP BY id, name, email
EXCEPT
SELECT id, name, email, COUNT(*) AS cnt
FROM target_db.users
GROUP BY id, name, email;

-- 3. 聚合值对比
SELECT
    COUNT(*) AS total,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    MIN(created_at) AS earliest,
    MAX(created_at) AS latest
FROM source_db.orders;

-- 与目标库对比以上结果

-- 4. 哈希校验（逐行校验大数据量）
-- MySQL
SELECT MD5(GROUP_CONCAT(id, name, email ORDER BY id)) AS checksum
FROM source_db.users;

-- PostgreSQL
SELECT md5(string_agg(id || name || email, '' ORDER BY id)) AS checksum
FROM users;

CI/CD 迁移流程

自动化迁移流水线

# .gitlab-ci.yml 示例
stages:
  - build
  - test
  - migrate-staging
  - migrate-production

variables:
  DOTNET_CLI_TELEMETRY_OPTOUT: "1"

migration:test:
  stage: test
  script:
    - dotnet ef migrations script --idempotent -o migrations.sql
    - dotnet ef database update
  tags:
    - dotnet

migration:staging:
  stage: migrate-staging
  script:
    - dotnet ef migrations script --idempotent -o migrations.sql
    - echo "Review the generated SQL before applying"
    # 可以在这里加 SQL 审核 step
  environment: staging
  when: manual  # 手动触发
  artifacts:
    paths:
      - migrations.sql

migration:production:
  stage: migrate-production
  script:
    - echo "Running migration on production..."
    - dotnet ef migrations script --idempotent -o migrations.sql
    - # DBA 审核 SQL 后执行
    - dotnet ef database update
  environment: production
  when: manual  # 必须手动触发
  only:
    - main

迁移变更流程

开发阶段：
    1. 修改实体模型
    2. 生成迁移文件（dotnet ef migrations add）
    3. 本地测试迁移和回滚
    4. 提交代码 + 迁移文件

代码审查：
    1. 检查 Up/Down 方法是否对称
    2. 检查数据迁移是否有幂等性
    3. 检查大表变更是否分批处理
    4. 检查回滚脚本是否安全

预发布：
    1. 生成幂等 SQL 脚本
    2. DBA 审核 SQL
    3. 在 staging 环境执行
    4. 验证数据和功能

生产发布：
    1. 全量备份
    2. 低峰期执行
    3. 实时监控
    4. 验证回滚方案可用

常见迁移场景

重命名表/列

// 重命名需要分步进行，保证前后端兼容

// 步骤 1：添加新列
public partial class Step1_AddNewColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "email_address",
            table: "users",
            type: "nvarchar(200)",
            nullable: true);
    }
}

// 步骤 2：迁移数据
public partial class Step2_CopyData : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("UPDATE users SET email_address = email WHERE email IS NOT NULL");
    }
}

// 步骤 3：切换应用代码使用新列，确认稳定后删除旧列
public partial class Step3_RemoveOldColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 添加 NOT NULL 约束
        migrationBuilder.AlterColumn<string>(
            name: "email_address",
            table: "users",
            nullable: false);

        // 删除旧列（确认应用已全部切换）
        migrationBuilder.DropColumn(
            name: "email",
            table: "users");
    }
}

拆分大表

-- 场景：将 orders 表中的地址信息拆分到独立表

-- 步骤 1：创建新表
CREATE TABLE order_addresses (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50),
    detail VARCHAR(500),
    receiver_name VARCHAR(100),
    receiver_phone VARCHAR(20),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_order_id (order_id)
) ENGINE=InnoDB;

-- 步骤 2：分批迁移数据
INSERT INTO order_addresses (order_id, province, city, district, detail, receiver_name, receiver_phone)
SELECT id, province, city, district, detail, receiver_name, receiver_phone
FROM orders
WHERE id BETWEEN 1 AND 100000;

-- 循环执行直到所有数据迁移完成
-- 步骤 3：验证数据一致性
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM order_addresses;

-- 步骤 4：确认无误后删除原表中的冗余列
ALTER TABLE orders DROP COLUMN province, DROP COLUMN city,
DROP COLUMN district, DROP COLUMN detail,
DROP COLUMN receiver_name, DROP COLUMN receiver_phone;

字符集变更

-- MySQL 字符集从 utf8 迁移到 utf8mb4
-- 步骤 1：修改数据库默认字符集
ALTER DATABASE myapp CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 步骤 2：逐表修改（大表用 pt-online-schema-change）
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用 Percona 工具在线修改（不锁表）
pt-online-schema-change \
  --alter "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" \
  D=myapp,t=users,h=localhost,u=admin,p=password \
  --chunk-size=1000 \
  --max-lag=5 \
  --execute

-- 步骤 3：验证字符集
SHOW CREATE TABLE users;
-- 应显示 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

-- 注意事项：
-- utf8mb4 比 utf8 多占用约 10-20% 存储空间
-- VARCHAR 列的索引长度限制更严格（767 -> 768 字节）
-- 修改大表字符集可能耗时很长

迁移回滚策略

回滚方案设计

// 每个迁移的 Down 方法必须可安全执行

public partial class AddUserPhoneNumber : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "phone_number",
            table: "users",
            type: "nvarchar(20)",
            nullable: true);

        // 数据迁移（不可逆）
        migrationBuilder.Sql(@"
            UPDATE users SET phone_number = phone WHERE phone IS NOT NULL
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // 回滚：只删除列，不恢复旧数据
        // 旧数据应该已经备份
        migrationBuilder.DropColumn(
            name: "phone_number",
            table: "users");
    }
}

// 生产环境回滚清单：
// 1. 有全量备份（迁移前）
// 2. 回滚 SQL 已测试通过
// 3. 回滚时间在 RTO 范围内
// 4. 应用代码可以回退到旧版本
// 5. 回滚后数据一致性已验证

迁移监控与告警

-- 迁移执行前检查
-- 1. 检查当前迁移版本
SELECT * FROM __EFMigrationsHistory ORDER BY MigrationId DESC LIMIT 5;

-- 2. 检查表行数
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'your_db'
ORDER BY table_rows DESC;

-- 3. 检查活跃连接
SHOW PROCESSLIST;
-- 确保没有长时间运行的查询

-- 4. 检查磁盘空间
-- 确保有足够空间（至少是表大小的 2 倍）

-- 迁移后验证
-- 1. 检查表结构
DESCRIBE users;
SHOW CREATE TABLE users;

-- 2. 检查索引
SHOW INDEX FROM users;

-- 3. 检查外键
SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'your_db';

-- 4. 检查数据抽样
SELECT * FROM users ORDER BY id DESC LIMIT 10;

优点

• 1.版本控制 — 数据库变更可追踪
• 2.自动生成 — 对比模型生成 SQL
• 3.可回滚 — 支持安全回退
• 4.CI/CD 集成 — 自动化迁移执行

缺点

• 1.合并冲突 — 多人同时迁移可能冲突
• 2.大表风险 — 大表结构变更可能锁表
• 3.回滚限制 — 数据迁移不可逆
• 4.工具依赖 — 依赖 EF Core 工具

总结

数据库迁移是持续交付的重要环节。开发环境用 dotnet ef 自动迁移，生产环境生成 SQL 脚本审核后执行。危险变更分步进行：先加后删，先兼容后切换。核心原则：迁移向前兼容，回滚准备充分，生产变更低峰执行。

关键知识点

• 数据库主题一定要同时看数据模型、读写模式和执行代价。
• 很多性能问题不是 SQL 语法问题，而是索引、统计信息、事务和数据分布问题。
• 高可用、备份、迁移和治理与查询优化同样重要。
• 先把数据模型、访问模式和执行代价绑定起来理解。

项目落地视角

• 所有优化前后都保留执行计划、样本 SQL 和关键指标对比。
• 上线前准备回滚脚本、备份点和校验方案。
• 把连接池、锁等待、慢查询和容量增长纳入日常巡检。
• 保留执行计划、样本 SQL、索引定义和优化前后指标。

常见误区

• 脱离真实数据分布讨论索引或分片。
• 只看单条 SQL，不看整条业务链路的事务和锁。
• 把测试环境结论直接等同于生产环境结论。
• 脱离真实数据分布设计索引。

进阶路线

• 继续向执行计划、存储引擎、复制机制和数据治理层深入。
• 把主题与 ORM、缓存、消息队列和归档策略联动起来思考。
• 沉淀成数据库设计规范、SQL 审核规则和变更流程。
• 继续深入存储引擎、复制机制、归档与冷热分层治理。

适用场景

• 当你准备把《数据库迁移策略》真正落到项目里时，最适合先在一个独立模块或最小样例里验证关键路径。
• 适合数据建模、查询优化、事务控制、高可用和迁移治理。
• 当系统开始遇到慢查询、锁冲突、热点数据或容量增长时，这类主题价值最高。

落地建议

• 先分析真实查询模式、数据量级和写入特征，再决定索引或分片策略。
• 所有优化结论都结合执行计划、样本数据和监控指标验证。
• 高风险操作前准备备份、回滚脚本与校验 SQL。

排错清单

• 先确认瓶颈在 CPU、I/O、锁等待、网络还是 SQL 本身。
• 检查执行计划是否走错索引、是否发生排序或全表扫描。
• 排查长事务、隐式类型转换、统计信息过期和参数嗅探。

复盘问题

• 如果把《数据库迁移策略》放进你的当前项目，最先要验证的输入、输出和失败路径分别是什么？
• 《数据库迁移策略》最容易在什么规模、什么边界条件下暴露问题？你会用什么指标或日志去确认？
• 相比默认实现或替代方案，采用《数据库迁移策略》最大的收益和代价分别是什么？

延伸阅读

• 数据迁移实战
• 连接池优化
• 索引原理与设计
• 数据库设计原则