SqlServer自动化表分区解决方案(三)之项目测试

wynnyo 2020年06月05日 1,527次浏览

SqlServer自动化表分区项目测试

项目示例

  • 在 Services 文件夹添加 LogService.cs 文件;

    public readonly DbContext _dbContext;
    public LogService()
    {
    	_dbContext = new DbContext();
    }
    
  • 添加 Insert 方法;

    /// <summary>
    /// 插入单条数据
    /// </summary>
    /// <param name="logs"></param>
    public void Insert(LogEntity log)
    {
        _dbContext.Db.MappingTables.Add("LogEntity", Consts.TableName);
        _dbContext.Db.Insertable(log).ExecuteCommand();
    }
    
    /// <summary>
    /// 插入多条数据
    /// </summary>
    /// <param name="logs"></param>
    public void Insert(ICollection<LogEntity> logs)
    {
        _dbContext.Db.MappingTables.Add("LogEntity", Consts.TableName);
        _dbContext.Db.Insertable(logs.ToList()).ExecuteCommand();
    }
    
  • 在 DbService.cs 文件新建统计方法

    /// <summary>
    /// 统计文件和文件组信息
    /// </summary>
    /// <returns></returns>
    public DataTable GetGroupFileInfo()
    {
        var sql = @"SELECT df.[name], df.physical_name, f.[name][filegroup]
                  FROM sys.database_files df
                  JOIN sys.filegroups f ON df.data_space_id = f.data_space_id";
        return _dbContext.Db.Ado.GetDataTable(sql);
    }
    
    /// <summary>
    /// 统计分区数据信息
    /// </summary>
    /// <returns></returns>
    public DataTable GetPartitionInfo()
    {
        var sql = $@"SELECT PARTITION = $PARTITION.{Consts.PartitionFunctionName} (createtime),
                           ROWS      = COUNT(*),
                           MinVal    = MIN(createtime),
                           MaxVal    = MAX(createtime)
                    FROM [dbo].[Log]
                    GROUP BY $PARTITION.{Consts.PartitionFunctionName} (createtime)
                    ORDER BY PARTITION";
        return _dbContext.Db.Ado.GetDataTable(sql);
    }
    
  • 把 DbService 和 LogService 加入到 IOC 中, 打开 Startup.cs 的 ConfigureServices 方法, 在末尾加入下面2句话.

    services.AddSingleton<DbService>();
    services.AddSingleton<LogService>();
    
  • 到这里 Service 层基本完成了, 接下来我们创建 Controller 层. 新建文件夹 Dtos, 用来存放返回前台的数据类型. 这里新建 GroupFileDto.cs 和 PartitionDto.cs.

    GroupFileDto

    /// <summary>
    /// 文件和文件组信息
    /// </summary>
    public class GroupFileDto
    {
        /// <summary>
        /// 文件 name
        /// </summary>
        public string Name { get; set; }
    
        /// <summary>
        /// 文件硬盘位置
        /// </summary>
        public string PhysicalName { get; set; }
    
        /// <summary>
        /// 文件组 name
        /// </summary>
        public string FileGroup { get; set; }
    }
    

    PartitionDto

    /// <summary>
    /// 统计分区信息
    /// </summary>
    public class PartitionDto
    {
        /// <summary>
        /// 分区
        /// </summary>
        public string Partition { get; set; }
    
        /// <summary>
        /// 分区中数据数量
        /// </summary>
        public string Rows { get; set; }
    
        /// <summary>
        /// 分区中时间最小值
        /// </summary>
        public string MinVal { get; set; }
    
        /// <summary>
        /// 分区中时间最大值
        /// </summary>
        public string MaxVal { get; set; }
    }
    
  • 在 Controllers 文件夹里新建 DbController.cs 和 LogController.cs, 这里要建基于 Api 的 Controller, 分别在构造方法里注入对应的 Service;

    private readonly DbService _dbService;
    
    public DbController(DbService dbService)
    {
        _dbService = dbService;
    }
    
    private readonly LogService _service;
    
    public LogController(LogService service)
    {
        _service = service;
    }
    
  • 在 DbController 新建方法 Init, InitPartitioningTables, PartitioningTablesTask

    [HttpPost]
    [Route("init")]
    public void Init()
    {
        try
        {
            _dbService.Init();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }
    
    [HttpPost]
    [Route("initPartitioningTables")]
    public void InitPartitioningTables()
    {
        try
        {
            _dbService.InitPartitioningTables();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }
    
    [HttpPost]
    [Route("partitioningTablesTask")]
    public void PartitioningTablesTask(int addDay = 0)
    {
        try
        {
            _dbService.PartitioningTablesTask(addDay);
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }
    
    [HttpGet]
    [Route("getGroupFileInfo")]
    public ICollection<GroupFileDto> GetGroupFileInfo()
    {
        try
        {
            var dt = _dbService.GetGroupFileInfo();
    
            return dt.AsEnumerable().Select(e => new GroupFileDto()
            {
                Name = e["name"]?.ToString(),
                FileGroup = e["filegroup"]?.ToString(),
                PhysicalName = e["physical_name"]?.ToString()
            }).ToList();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }
    
    [HttpGet]
    [Route("getPartitionInfo")]
    public ICollection<PartitionDto> GetPartitionInfo()
    {
        try
        {
            var dt = _dbService.GetPartitionsInfo();
    
            return dt.AsEnumerable().Select(e => new PartitionDto
            {
                Partition = e["PARTITION"]?.ToString(),
                Rows = e["ROWS"]?.ToString(),
                MinVal = e["MinVal"]?.ToString(),
                MaxVal = e["MaxVal"]?.ToString(),
            }).ToList();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }
    
  • 在 LogController 新建方法 Insert 和 RangeInsert 方法;

    [HttpPost]
    [Route("insert")]
    public void Insert(LogEntity log)
    {
        try
        {
            _service.Insert(log);
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }
    
    [HttpPost]
    [Route("rangeInsert")]
    public void RangeInsert(int rows)
    {
        var ran = new Random();
    
        var list = new List<LogEntity>();
    
        try
        {
            for (int i = 0; i < rows; i++)
            {
                // 随机生成 分区范围内的 时间
                var time = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day,
                        ran.Next(0, 24), ran.Next(0, 60), ran.Next(0, 60))
                    .AddDays(0 - ran.Next(0, Consts.ReserveDay));
    
                list.Add(new LogEntity()
                {
                    Id = 0,
                    Title = "Log",
                    CreateTime = time
                });
            }
    
            _service.Insert(list);
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }
    
  • 到这里所有的代码输入完毕, 下面来看下文件结构:

    Snipaste_2020-06-05_17-32-45

项目调试

  • f5 启动调试, 这里我对 launchsettings.json 进行了下更改, 把 launchBrowser 设为 false, launchUrl 设为 swagger.

  • 打开 swagger 界面.

    Snipaste_2020-06-05_17-38-19

  • 执行 init api, 数据库会生成 Log 表, 在 C 盘会生成 MyData\XmlBak 文件夹.

  • 执行 initPartitioningTables api, 在 C:\MyData 会生成 34 个 .ndf 文件, 在数据库 MyData-存储-分区方案和分区函数里生成 P_Data_Day 和 F_Data_Day.

  • 执行 getGroupFileInfo api, 可以看到返回 文件和文件组的信息.

    [
      {
        "name": "MyData",
        "physicalName": "C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\MyData.mdf",
        "fileGroup": "PRIMARY"
      },
      {
        "name": "Log_Data_00010101",
        "physicalName": "C:\\MyData\\Log_Data_00010101.ndf",
        "fileGroup": "Log00010101"
      },
      {
        "name": "Log_Data_20200506",
        "physicalName": "C:\\MyData\\Log_Data_20200506.ndf",
        "fileGroup": "Log20200506"
      },
      // 中间省略 .....
      {
        "name": "Log_Data_20200606",
        "physicalName": "C:\\MyData\\Log_Data_20200606.ndf",
        "fileGroup": "Log20200606"
      },
      {
        "name": "Log_Data_20200607",
        "physicalName": "C:\\MyData\\Log_Data_20200607.ndf",
        "fileGroup": "Log20200607"
      }
    ]
    
  • 执行 rangeInsert api, 我这里 生成了 100000 条数据.

  • 执行 getPartitionInfo api, 可以看到有 31 分区有数据.

    [
      {
        "partition": "2",
        "rows": "3213",
        "minVal": "2020/5/6 0:00:06",
        "maxVal": "2020/5/6 23:58:56"
      },
      {
        "partition": "3",
        "rows": "3181",
        "minVal": "2020/5/7 0:00:07",
        "maxVal": "2020/5/7 23:59:51"
      },
     // 中间忽略 .....
      {
        "partition": "31",
        "rows": "3262",
        "minVal": "2020/6/4 0:00:05",
        "maxVal": "2020/6/4 23:59:43"
      },
      {
        "partition": "32",
        "rows": "3253",
        "minVal": "2020/6/5 0:00:08",
        "maxVal": "2020/6/5 23:59:48"
      }
    ]
    
  • 执行 partitioningTablesTask api, 输入 1, 表示执行的是明天的 任务.

    • 我是 6月5日执行的 6月6日的任务.

    • 在 C:\MyData\XmlBak 中生成了 31 天前的 Log_Data_20200506.xml 文件;

    • 这时可以看下 .ndf 文件是从 5月7日 到 6月8日, 正常.

    • 查询 getGroupFileInfo api, 发现 文件组也是 从 5月7日 到 6月8日, 正常.

    • 查询 getPartitionInfo api. 和前面的对比下, 发现 json 的 第一组数据刚好是 以前查询的 第二组数据, 刚好说明第一组被删除掉了.

      [
        {
          "partition": "2",
          "rows": "3181",
          "minVal": "2020/5/7 0:00:07",
          "maxVal": "2020/5/7 23:59:51"
        },
       // 中间省略 ......
        {
          "partition": "31",
          "rows": "3253",
          "minVal": "2020/6/5 0:00:08",
          "maxVal": "2020/6/5 23:59:48"
        }
      ]
      
    • 最后查看下 xml 文件, 直接拖拽到浏览器里, 发现也没问题.