100万级数据SQL Server迁移MySql方案测评小记

IT菜鸟

4 年前

前言 – SQL Server迁移MySql

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

多线程批量写入

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 并执行。

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 是因为 SqlServer 的 Sqlbulkcopy。MySqlBulkLoader 并不支持集合的导入，需要先将数据导出为 .csv 格式，然后读取 .csv 数据导入。

public static async Task Export(string filePath, List<TestUser> items)
        {
            IExporter exporter = new CsvExporter();
            await exporter.Export(filePath, items);
        }

这里数据导出使用国人开源的 dotnetcore/Magicodes.IE 我这个导出代码，应该就懂了吧！操作简洁！！！

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 读取本地文件，进行导入。

SQL Server迁移MySql方案测试说明

这个测试是在我本地测试的，数据库是跑在内网部署的一台机器上的 Docker 容器内，用的是机械硬盘。如果您的使用的是 SSD 硬盘，效果会更佳。
这里测试主要是插入简单的用户数据，定义如下：

public class TestUser
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

分别测试1w，10w,100w条数据插入的性能，以及开启索引以及关闭索引的影响
测试执行代码如下：

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");
        }
    }

SQL Server迁移MySql方案测试结果

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

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

最后

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

代码地址：DataMigrationTest

坑

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

本文转载自博客园，原文链接：https://www.cnblogs.com/hellotim/p/13207489.html