我们一直在追求架构的艺术!!

问题剖析

经由几分钟的排查,数据库情形如下:

  • 数据库接纳Sqlserver 2008 R2,单表数据量21亿。

  • 无水平或者垂直切分,然则接纳了分区表。分区表计谋是按时间降序分的区,快要30个分区。正由于分区表的缘故原由,系统才保证了在性能不是太差的情形下坚持至今。
  • 此表除群集索引之外,无其他索引,无主键(主键实在是行使索引来快速查重的)。以是在频仍插入新数据的情形下,索引调整所花费的性能比较低。

至于群集索引和非群集索引等知识,请列位移步google或者百度。

至于营业,不是太庞大。经由相关职员咨询,约莫40%的请求为单条Insert,约莫60%的请求为按class_id 和in_time(倒序)分页获取数据。Select请求所有掷中群集索引,以是性能异常高。这也是群集索引之以是这样设计的目的。

解决问题

由于单表数据量已经跨越21亿,而且2017年以前的数据险些不影响营业,以是决议把2017年以前(不包罗2017年)的数据迁徙到新表,仅供以后特殊营业查询使用。经由查询约莫有9亿数据量。
数据迁徙事情包罗三个个步骤:

  1. 从源数据表查询出要迁徙的数据
  2. 把数据插入新表
  3. 把旧表的数据删除
传统做法

这里声名一点,就算是传统的做法也需要分页获取源数据,由于你的内存一次性装载不下9亿条数据。

  1. 从源数据表分页获取数据,详细分页条数,太少则查询原表太频仍,太多则查询太慢。
    SQL语句类似于
    SELECT * FROM (
    SELECT *,ROW_NUMBER() OVER(ORDER BY class_id,in_time) p FROM  tablexx WHERE in_time <'2017.1.1'  
    ) t WHERE t.p BETWEEN 1 AND 100
  2. 把查询出来的数据插入目的数据表,这里强调一点,一定不要用单条插入计谋,必须用批量插入。
  3. 把数据删除,实在这里删除照样有一个小难点,表没有标示列。这里不睁开,由于这不是菜菜要说的重点。

若是你的数据量不大,以上方式完全没有问题,然则在9亿这个数字前面,以上方式显得心有余而力不足。一个字:慢,太慢,异常慢。

可以大要算一下,如果每秒可以迁徙1000条数据,约莫需要的时间为(单元:分)

900000000/1000/60=15000(分钟)

约莫需要10天^ V ^

改善做法

以上的传统做法坏处在那里呢?

  1. 在9亿数据前查询必须掷中索引,就算是非群集索引菜菜也不推荐,首推群集索引。
  2. 若是你领会索引的原理,你应该明了,一直的插入新数据的时刻,索引在一直的更新,调整,以保持树的平衡等特征。尤其是群集索引影响甚大,由于还需要移动现实的数据。

提取以上两点配合的要素,那就是群集索引。响应的解决方案也就应运而生:

  1. 根据群集索分页引查询数据
  2. 批量插入数据迎合群集索引,即:根据群集索引的顺序批量插入。
  3. 根据群集索引顺序批量删除

由于做了表分区,若是有一种方式把2017年以前的分区直接在磁盘物理层面从当前表剥离,然后挂载到另外一个表,可算是神级操作。有谁能指导一下菜菜,感激涕零

扩展阅读

  1. 一个表的群集索引的顺序就是现实数据文件的顺序,映射到磁盘上,本质上位于同一个磁道上,以是操作的时刻磁盘的磁头不必跳跃着去操作。

  2. 存储在硬盘中的每个文件都可分为两部分:文件头和存储数据的数据区。文件头用来纪录文件名、文件属性、占用簇号等信息,文件头保留在一个簇并映射在FAT表(文件分配表)中。而真实的数据则是保留在数据区当中的。平时所做的删除,实在是修改文件头的前2个代码,这种修改映射在FAT表中,就为文件作了删除符号,并将文件所占簇号在FAT表中的挂号项清零,示意释放空间,这也就是平时删除文件后,硬盘空间增大的缘故原由。而真正的文件内容仍保留在数据区中,并未得以删除。要等到以后的数据写入,把此数据区笼罩掉,这样才算是彻底把原来的数据删除。若是不被厥后保留的数据笼罩,它就不会从磁盘上抹掉。

NetCore 代码(现实运行代码)

  1. 第一步:由于群集索引需要class_id ,以是宁肯花2-4秒时间把要操作的class_id查询出来(ORM为dapper),而且升序排列
            DateTime dtMax = DateTime.Parse("2017.1.1");
            var allClassId = DBProxy.GeSourcetLstClassId(dtMax)?.OrderBy(s=>s);
  2. 根据第一步class_id 列表顺序查询数据,每个class_id 分页获取,然后插入目的表,所有完成然后删除源表响应class_id的数据。(所有掷中群集索引)

            int pageIndex = 1; //页码
            int pageCount = 20000;//每页的数据条数
            DataTable tempData =null;
            int successCount = 0;
            foreach (var classId in allClassId)
            {
                tempData = null;
                pageIndex = 1;
                while (true)
                {
                    int startIndex = (pageIndex - 1) * pageCount+1;
                    int endIndex = pageIndex * pageCount;
    
                    tempData = DBProxy.GetSourceDataByClassIdTable(dtMax, classId, startIndex, endIndex);
                    if (tempData == null || tempData.Rows.Count==0)
                    {
                        //最后一页无数据了,删除源数据源数据然后跳出
                         DBProxy.DeleteSourceClassData(dtMax, classId);
                        break;
                    }
                    else
                    {
                        DBProxy.AddTargetData(tempData);
                    }
                    pageIndex++;
                }
                successCount++;
                Console.WriteLine($"班级:{classId} 完成,已经完成:{successCount}个");
            }

    DBProxy 完整代码:

    class DBProxy
    {
        //获取要迁徙的数据所有班级id
        public static IEnumerable<int> GeSourcetLstClassId(DateTime dtMax)
        {
            var connection = Config.GetConnection(Config.SourceDBStr);
            string Sql = @"SELECT class_id FROM  tablexx WHERE in_time <@dtMax GROUP BY class_id ";
            using (connection)
            {
                return connection.Query<int>(Sql, new { dtMax = dtMax }, commandType: System.Data.CommandType.Text);
    
            }
        }
    
        public static DataTable GetSourceDataByClassIdTable(DateTime dtMax, int classId, int startIndex, int endIndex)
        {
            var connection = Config.GetConnection(Config.SourceDBStr);
            string Sql = @" SELECT * FROM (
                        SELECT *,ROW_NUMBER() OVER(ORDER BY in_time desc) p FROM  tablexx WHERE in_time <@dtMax  AND class_id=@classId
                        ) t WHERE t.p BETWEEN @startIndex AND @endIndex ";
            using (connection)
            {
                DataTable table = new DataTable("MyTable");
                var reader = connection.ExecuteReader(Sql, new { dtMax = dtMax, classId = classId, startIndex = startIndex, endIndex = endIndex }, commandType: System.Data.CommandType.Text);
                table.Load(reader);
                reader.Dispose();
                return table;
            }
        }
         public static int DeleteSourceClassData(DateTime dtMax, int classId)
        {
            var connection = Config.GetConnection(Config.SourceDBStr);
            string Sql = @" delete from  tablexx WHERE in_time <@dtMax  AND class_id=@classId ";
            using (connection)
            {
                return connection.Execute(Sql, new { dtMax = dtMax, classId = classId }, commandType: System.Data.CommandType.Text);
    
            }
        }
        //SqlBulkCopy 批量添加数据
        public static int AddTargetData(DataTable data)
        {
            var connection = Config.GetConnection(Config.TargetDBStr);
            using (var sbc = new SqlBulkCopy(connection))
            {
                sbc.DestinationTableName = "tablexx_2017";               
                sbc.ColumnMappings.Add("class_id", "class_id");
                sbc.ColumnMappings.Add("in_time", "in_time");
                .
                .
                .
                using (connection)
                {
                    connection.Open();
                    sbc.WriteToServer(data);
                }               
            }
            return 1;
        }
    
    }

    运行讲述

    程序本机运行,开***毗邻远程DB服务器,运行1分钟,迁徙的数据数据量为 1915560,每秒约3万条数据

    1915560 / 60=31926 条/秒

    cpu情形(不高):

    磁盘行列情形(不高):

    在以下情形下速率还将提高

    1. 源数据库和目的数据库硬盘为ssd,而且分别为差别的服务器
    2. 迁徙程序和数据库在同一个局域网,保障数据传输时刻带宽不会成为瓶颈
    3. 合理的设置SqlBulkCopy参数
    4. 菜菜的场景大多数场景下每次批量插入的数据量达不到设置的值,由于有的class_id 对应的数据量就几十条,甚至几条而已,打开关闭数据库毗邻也是需要耗时的
    5. 单纯的批量添加或者批量删除操作

领取架构师进阶资料大礼包