目录

百万级数据迁移方案测评小记

前言

最近公司在使用 ABP 重构之前的老项目,数据库也由 SQL SERVER 切换到了 MySql。吐槽一下,之前的产品使用的是 Windows Server 2008 , SqlServer 2008R2, .Net Framework 4.5,现在 开始拥抱 .net core。回到正题。目前单表有 10w+100w+ 数据不等,等会都测试一下。数据库切换,以及数据库表结构变化,不可以避免的需要进行数据迁移。而迁移方案也并不是很多,下面是我尝试使用的两种 方案进行测试。

多线程批量写入

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
private static async Task BatchInsertTestUsers(List<TestUser> testUsers)
        {
            var prefix =
                "INSERT INTO users (Id,Name,Age) VALUES";
            using (IDbConnection conn = new MySqlConnection(DataMigrationConfig.MySqlConstr))
            {
                var sqlText = new StringBuilder();
                sqlText.Append(prefix);

                foreach (var testUser in testUsers)
                {
                    sqlText.AppendFormat(
                        $"({testUser.Id},'{testUser.Name}', {testUser.Age}),");
                }

                var insertSql = sqlText.ToString().Substring(0, sqlText.ToString().LastIndexOf(','));
                await conn.ExecuteAsync(insertSql);
            }
        }
  • BatchInsertTestUsers 将传入的集合,拼接成 SQL 并执行。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public static Task RunMultiTasks(List<TestUser> users)
        {
            var tasks = new List<Task>();
            var pageSize = 10000;
            var writeCount = (users.Count() / pageSize) + 2;

            for (var i = 1; i < writeCount; i++)
            {
                var skipCount = (i - 1) * pageSize;
                var batchInsertList = users.Skip(skipCount).Take(pageSize).ToList();

                var task = Task.Run(() => { BatchInsertTestUsers(batchInsertList); });
                tasks.Add(task);
            }

            var sw = new Stopwatch();
            sw.Start();
            Task.WaitAll(tasks.ToArray());
            sw.Stop();
            Console.WriteLine($"多线程批量插入用时:{sw.ElapsedMilliseconds} ms");

            return Task.FromResult(0);
        }
  • RunMultiTasks 将数据分批,一次性插入 1w 条。

MySqlBulkLoader 方案

了解到 MySqlBulkLoader 是因为 SqlServerSqlbulkcopyMySqlBulkLoader 并不支持集合的导入,需要先将数据导出为 .csv 格式,然后读取 .csv 数据导入。

1
2
3
4
5
public static async Task Export(string filePath, List<TestUser> items)
        {
            IExporter exporter = new CsvExporter();
            await exporter.Export(filePath, items);
        }
  • 这里数据导出使用国人开源的 dotnetcore/Magicodes.IE 我这个导出代码,应该就懂了吧!操作简洁!!!
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
public static void Load(string filePath, string tableName)
        {
            using MySqlConnection conn = new MySqlConnection(DataMigrationConfig.MySqlConstr);
            var bulk = new MySqlBulkLoader(conn)
            {
                FieldTerminator = ",",
                FieldQuotationCharacter = '"',
                EscapeCharacter = '"',
                LineTerminator = "\r\n",
                FileName = filePath,
                Local = true,
                NumberOfLinesToSkip = 1,
                TableName = tableName,
                CharacterSet = "utf8mb4",
            };

            bulk.Load();
        }
  • 这里因为数据库并不在自己本机上,所以设置了 Local = true 读取本地文件,进行导入。

测试说明

  • 这个测试是在我本地测试的,数据库是跑在内网部署的一台机器上的 Docker 容器内,用的是机械硬盘。如果您的使用的是 SSD 硬盘,效果会更佳。
  • 这里测试主要是插入简单的用户数据,定义如下:
1
2
3
4
5
6
public class TestUser
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }
  • 分别测试1w10w,100w条数据插入的性能,以及开启索引以及关闭索引的影响
  • 测试执行代码如下:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
class Program
    {
        static async Task Main(string[] args)
        {
            var testData = DataGen.Run(100 * 10000);
            await RunMultiTasks(testData);
            await RunMySqlLoaderTask(testData);
        }

        public static async Task RunMultiTasks(List<TestUser> users)
        {
            await DataMigrateTask.RunMultiTasks(users);
        }

        public static async Task RunMySqlLoaderTask(List<TestUser> users)
        {
            var fileName = "users";
            var filePath = Directory.GetCurrentDirectory() + "\\" + fileName + ".csv";
            await DataMigrateTask.Export(filePath, users);
            var sw = new Stopwatch();
            sw.Start();
            DataMigrateTask.Load(filePath, "users");
            sw.Stop();
            Console.WriteLine($"MySqlBulkLoader 用时:{sw.ElapsedMilliseconds} ms");
        }
    }

测试结果

说了那么多,这里才是最重点。

方案 1w 10w 100w
RunMultiTasks 367ms 3548ms 91263ms
RunMySqlLoaderTask 2031ms 1597ms 13105ms
RunMultiTasks(关闭索引) 233ms 3230ms 67040ms
RunMySqlLoaderTask (关闭索引) 1785ms 1367ms 12456ms

最后

以上的测试仅供参考,上面的简单测试一下,数据量大的时候 MySqlLoaderTask 优势是明显的,对于小于 1w 数据量的可以采用多线程批量插入效果更好。有兴趣的小伙伴的可以自己下载代码玩玩。如有更好的 方案,不吝赐教。

补充

后来尝试了一下 postgresql,测试完之后,我只想说,好像挺猛的。这里迁移直接用了 postgresql 提供的 copy 命令,进行迁移。测试结果如下:

方案 1w 10w 100w
Copy 738ms 1492ms 6065ms
  • 这个数量大起来之后,优势比 mysql 要快的多。

已经在内网中开心的将 sqlserver 迁移到 mysql,打开站点体验一下,额,慢的要死。经过一顿排查,发现是 Count(*) 类的语句能执行10几秒,一顿搜索没有找到比较简单的方案解决这个问题。

  • MySqlLoader 导入 null 数据使用 NULL,而不是mysql文档上说的 \N