FreeSql分表功能实践
FreeSql分表功能实践
-
FreeSql更新了自动分表功能，主要的优点在于“不再需要”手动进行分表。
-
目前支持按照时间自动分表，插入时根据指定的时间字段，自动插入数据到对应表中。
-
查询时，采用多表UNION ALL来实现组合查询，支持根据查询条件对表进行优化，比如时间范围在2022-03-01到2022-05-16，那么只会在202203、202204、202205这三张表查询。
-
目前主要应用在审计追踪模块，随着系统的运行，审计追踪的数据会快速增长，因此会有分表的需求。
一、分表实现
-
修改实体层的Table属性
-- 原属性 [Table(Name = "gxp_audit_trail")] -- 修改后属性 [Table(Name = "gxp_audit_trail_{yyyyMM}", AsTable = "CreatedTime=2022-1-1(1 month)")]如上所示可以在TableAttribute中规定表名的格式(年月)、指定的分表字段(创建时间)、分表的间隔(按月)，具体可以参考FreeSql的Wiki：https://github.com/dotnetcore/FreeSql/discussions/1066#
AsTable会初始化一个起始时间，如果早于这个时间，FreeSql会抛出异常，数据无法插入
-
仓储层的增删改查方法并不需要修改，分表会自动执行。需要注意的是，查询的方法最好都加一个时间参数对分表字段进行过滤，这样查询的时候可以根据时间来缩小表的范围，优化查询效率
二、问题总结
1、自动分表功能对子表查询的处理存在问题
也就是Exists查询，对于主表会根据筛选条件查询符合条件的表，对于子表会重复查询，生成错误sql。示例代码：
// beginTime = 2022-05-09
var source = Orm.Select<GxpAuditTrailEntity>()
.Where(o => Orm.Select<GxpAuditTrailItemEntity>().Any(s => s.EntityId == dto.EntityId && s.EntityName == dto.EntityName
&& s.AuditId == o.Id && s.CreatedTime > beginTime))
.Where(e => e.OrganCode == organCode && e.CreatedTime > beginTime);
对于主表能够成功识别，只查询202205表，但是子表识别失败，会UNION ALL同一个子表多次查询。
-- 生成的sql
SELECT count(1) as1
FROM `gxp_audit_trail_202205` a
WHERE (exists(SELECT * from (SELECT 1
FROM `gxp_audit_trail_item_202205` s
WHERE (s.`EntityId` = 0 AND s.`EntityName` IS NULL AND s.`AuditId` = a.`Id` AND s.`CreatedTime` > '2022-05-09 15:05:16.959')
limit 0,1) ftb
UNION ALL
SELECT * from (SELECT 1
FROM `gxp_audit_trail_item_202205` s
WHERE (s.`EntityId` = 0 AND s.`EntityName` IS NULL AND s.`AuditId` = a.`Id` AND s.`CreatedTime` > '2022-05-09 15:05:16.959')
limit 0,1) ftb
UNION ALL
SELECT * from (SELECT 1
FROM `gxp_audit_trail_item_202205` s
WHERE (s.`EntityId` = 0 AND s.`EntityName` IS NULL AND s.`AuditId` = a.`Id` AND s.`CreatedTime` > '2022-05-09 15:05:16.959')
limit 0,1) ftb
UNION ALL
SELECT * from (SELECT 1
FROM `gxp_audit_trail_item_202205` s
WHERE (s.`EntityId` = 0 AND s.`EntityName` IS NULL AND s.`AuditId` = a.`Id` AND s.`CreatedTime` > '2022-05-09 15:05:16.959')
limit 0,1) ftb
UNION ALL
SELECT * from (SELECT 1
FROM `gxp_audit_trail_item_202205` s
WHERE (s.`EntityId` = 0 AND s.`EntityName` IS NULL AND s.`AuditId` = a.`Id` AND s.`CreatedTime` > '2022-05-09 15:05:16.959')
limit 0,1) ftb)) AND (a.`OrganCode` = '#000964' AND a.`CreatedTime` > '2022-05-09 15:05:16.959')
解决的方案是，对于子表采用AsTable手动分表，代码实现如下
var beginTime = DateTime.Now.PeriodBegin(dto.Period);
var sourceList = new List<GxpAuditTrailEntity>();
var source = Orm.Select<GxpAuditTrailEntity>()
.Where(e => e.OrganCode == organCode && e.CreatedTime > beginTime);
#region 处理子表的分表
var tableNames = FreeSqlAsTableHelper.GetMonthTableName<GxpAuditTrailItemEntity>(beginTime,DateTime.Now);
var sourceItem = Orm.Select<GxpAuditTrailItemEntity>();
foreach (var item in tableNames)
{
sourceItem = sourceItem.AsTable((_, old) => old.Replace($"{{{FreeSqlAsTableHelper.MonthFormat}}}", item));
}
#endregion
source = source.Where(x => sourceItem.Any(s => s.EntityId == dto.EntityId && s.EntityName == dto.EntityName
&& s.AuditId == x.Id && x.CreatedTime > beginTime));
public class FreeSqlAsTableHelper
{
/// <summary>
/// 按月分表的表名格式
/// </summary>
public const string MonthFormat = "yyyyMM";
/// <summary>
/// 根据时间范围，以按月分表的方式获取所有可能的表名
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public static List<string> GetMonthTableName<T>(DateTime beginTime, DateTime endTime) where T : IEntity
{
var tableAttribute = typeof(T).GetAttribute<TableAttribute>()?.AsTable;
Regex regex = new Regex(@"\d+-\d+-\d+");
DateTime.TryParse(regex.Match(tableAttribute)?.Value, out DateTime minTime);
var tempTime = beginTime < minTime ? minTime : beginTime;
var tableNames = new List<string>();
while (tempTime < endTime)
{
tableNames.Add(tempTime.ToString(MonthFormat));
tempTime = tempTime.AddMonths(1);
}
return tableNames;
}
}
2、表达式中的时间条件不能使用Date模式
解决方案是，抽取出变量再传入参数，示例如下：
//无法识别
var source1 = Orm.Select<GxpAuditTrailItemEntity>()
.Where(p => p.CreatedTime.Between(dto.CreatedTime.Date, dto.CreatedTime.Date.AddDays(1)));
//正常识别
var startTime = dto.CreatedTime.Date;
var endTime = dto.CreatedTime.Date.AddDays(1);
var source = Orm.Select<GxpAuditTrailItemEntity>()
.Where(p => p.CreatedTime.Between(startTime, endTime));
三、数据迁移
由于分表采用日期结合的表名，历史数据无法和新的功能兼容，所以需要对历史数据进行迁移，我的方案是采用不分表的方式将数据全部捞取出来，再用分表的方式插入，实现代码如下：
/// <summary>
/// 迁移审计追踪数据
/// </summary>
public async Task<bool> MigrateAsync()
{
var index = 1;
var size = 10000;
long total;
do
{
var oldAuditTrailGroups = await Orm.Select<Data.Entity.AuditTrail.Migration.GxpAuditTrailItemEntity, Data.Entity.AuditTrail.Migration.GxpAuditTrailEntity>()
.InnerJoin(e => e.t1.AuditId == e.t2.Id).Page(index, size).Count(out total).ToListAsync(e => new { e.t1, e.t2 });
var oldAuditTrails = oldAuditTrailGroups.Select(e => e.t2).Distinct(new AuidtCompare()).ToList();
var auditTrails = mapper.Map<List<GxpAuditTrailEntity>>(oldAuditTrails);
var auditIndex = 0;
var auditIndixDic = new Dictionary<long, int>();
foreach (var audit in auditTrails)
{
auditIndixDic.Add(audit.Id, auditIndex++);
audit.Id = default;
}
await gxpAuditTrailRepository.InsertAsync(auditTrails);
var auditTrailItems = new List<GxpAuditTrailItemEntity>();
foreach (var oldItem in oldAuditTrailGroups)
{
var item = mapper.Map<GxpAuditTrailItemEntity>(oldItem.t1);
item.CreatedTime = oldItem.t2.CreatedTime.Value;
item.AuditId = auditTrails[auditIndixDic[item.AuditId]].Id;
item.Id = default;
auditTrailItems.Add(item);
}
await gxpAuditTrailItemRepository.InsertAsync(auditTrailItems);
}
while (index++ * size < total);
return true;
}
class AuidtCompare : IEqualityComparer<Data.Entity.AuditTrail.Migration.GxpAuditTrailEntity>
{
public bool Equals([AllowNull] Data.Entity.AuditTrail.Migration.GxpAuditTrailEntity x, [AllowNull] Data.Entity.AuditTrail.Migration.GxpAuditTrailEntity y)
{
return x.Id == y.Id;
}
public int GetHashCode([DisallowNull] Data.Entity.AuditTrail.Migration.GxpAuditTrailEntity obj)
{
return obj.Id.GetHashCode();
}
}
遇到难题1
迁移的过程中有一个比较难处理的问题，这里存在父表和子表，他们之间的引用关系是子表保存了父表主键作为外键引用。由于主键是自增的，迁移后父表的主键会改变，父子表的引用关系失效。解决逻辑如下：
- 先将父表和子表join查询，一起查询出来
- 根据主键过滤掉重复数据，拿到父表数据。直接Distinct会去重失败，因此自己实现了一个比较器
- 将原始父表数据存储到List中，再将原始主键和对应的List索引存到字典中Dictionary<long, int>中
- 将原始父表数据的主键Id置为空，然后插入数据库，此时FreeSql会自动更新List数据的主键Id为新的Id
- 遍历子表数据，根据其外键AuditId从字典Dictionary<long, int>获取到List索引，再根据索引获取到新的主键Id，赋值给AuditId
- 子表的主键Id置为空，创建时间置为父表数据的创建时间，插入数据库，完成数据迁移
遇到的难题2
因为需要迁移的数据量比较大，为了一次性操作入库的数据太大，采用了分页获取数据再分页插入的方式。如上代码所示一次性只取10000条，分批次插入。可能存在一个问题，同属于一条父数据的多条在两个批次中查出来，导致同一条父数据插入两次，这个问题暂时没有好的解决方案，但是影响相对较小，先搁置了。
浙公网安备 33010602011771号