数据库是postgresql，设置创建时间字段为分表字段，按月分表，测试发现写入速度不快，怎么提速？ 返回
老暮 发布于1个月前
数据库是postgresql，版本是17；SqlSugar版本是5.1.4.172，用户操作会写入到AuditLog，根据文本日志记录显示每次插入记录到AuditLog，都会触发sqlsuagr检查分表和分表索引是否存在。
这个操作能不能只进行一次，不要每次插入记录都去检查
1.执行插入的代码如下：
// auditlog是单条AuditLog数据
await db.Insertable<AuditLog>(auditlog).SplitTable().ExecuteCommandAsync();
2.SqlSugar配置代码如下：
public static class SugarServiceCollectionExtensions
{
public static void AddSqlsugarSetup(this IServiceCollection services, IConfiguration configuration)
{
if (services == null)
throw new ArgumentNullException(nameof(services));
services.AddScoped<ISqlSugarClient>(x =>
{
SqlSugarClient datacontext = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = configuration.GetConnectionString("Default"),
DbType = DbType.PostgreSQL,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute,
MoreSettings = new ConnMoreSettings() { DisableNvarchar = true },
ConfigureExternalServices = new ConfigureExternalServices
{
EntityService = (c, p) =>
{
if (new NullabilityInfoContext().Create(c).WriteState is NullabilityState.Nullable)
{
p.IsNullable = true;
}
EntityService(c, p);
}
},
},
db =>
{
db.Aop.OnLogExecuting = (sql, pars) =>
{
Log.Information("Execute SQL:" + sql);
if (pars != null)
{
Log.Information("Execute SQL Parameters: " + string.Join(", ", pars.Select(p => $"{p.ParameterName}={p.Value}")));
}
};
});
datacontext.DbMaintenance.CreateDatabase();
datacontext.CodeFirst.SplitTables().InitTables(typeof(AuditLog));
datacontext.CodeFirst.SplitTables().InitTables(typeof(AuditLogAction));
datacontext.CodeFirst.SplitTables().InitTables(typeof(EntityChange));
datacontext.CodeFirst.SplitTables().InitTables(typeof(EntityPropertyChange));
return datacontext;
});
}
/// <summary>
/// 实体配置
/// </summary>
/// <param name="property"></param>
/// <param name="column"></param>
/// <param name="dbSetting"></param>
private static void EntityService(PropertyInfo property, EntityColumnInfo column)
{
if (column.PropertyName == nameof(IHasExtraProperties.ExtraProperties))
{
column.IsJson = true;
column.IsNullable = true;
column.DataType = StaticConfig.CodeFirst_BigString;
}
}
}
3.分表的实体类结构如下
[DisableAuditing]
[SplitTable(SplitType.Month)]//按日分表 （自带分表支持 年、季、月、周、日）
[SugarTable("abpauditlogs_{year}{month}{day}")]
[SugarIndex("IX_AuditLog_ExecutionTime", nameof(AuditLog.ExecutionTime), OrderByType.Asc, false)]
public class AuditLog : AggregateRoot<Guid>, IMultiTenant
{
[SugarColumn(IsIgnore = true)]
[Navigate(NavigateType.OneToMany, nameof(EntityChange.AuditLogId))]
public virtual List<EntityChange> EntityChanges { get; set; }
[SugarColumn(IsIgnore = true)]
[Navigate(NavigateType.OneToMany, nameof(AuditLogAction.AuditLogId))]
public virtual List<AuditLogAction> Actions { get; set; }
[SugarColumn(Length = 96, IsNullable = true)]
public virtual string ApplicationName { get; set; }
public virtual Guid? UserId { get; protected set; }
[SugarColumn(Length = 256, IsNullable = true)]
public virtual string UserName { get; set; }
public virtual Guid? TenantId { get; set; }
[SugarColumn(Length = 64, IsNullable = true)]
public virtual string TenantName { get; set; }
public virtual Guid? ImpersonatorUserId { get; set; }
[SugarColumn(Length = 256, IsNullable = true)]
public virtual string ImpersonatorUserName { get; set; }
public virtual Guid? ImpersonatorTenantId { get; set; }
[SugarColumn(Length = 64, IsNullable = true)]
public virtual string ImpersonatorTenantName { get; set; }
[SplitField] //分表字段 在插入的时候会根据这个字段插入哪个表，在更新删除的时候用这个字段找出相关表
public virtual DateTime ExecutionTime { get; set; }
public virtual int ExecutionDuration { get; set; }
[SugarColumn(Length = 64, IsNullable = true)]
public virtual string ClientIpAddress { get; set; }
[SugarColumn(Length = 128, IsNullable = true)]
public virtual string ClientName { get; set; }
[SugarColumn(Length = 64, IsNullable = true)]
public virtual string ClientId { get; set; }
[SugarColumn(Length = 64, IsNullable = true)]
public virtual string CorrelationId { get; set; }
[SugarColumn(Length = 512, IsNullable = true)]
public virtual string BrowserInfo { get; set; }
[SugarColumn(Length = 16, IsNullable = true)]
public virtual string HttpMethod { get; set; }
[SugarColumn(Length = 256, IsNullable = true)]
public virtual string Url { get; set; }
[SugarColumn(ColumnDataType = StaticConfig.CodeFirst_BigString, IsNullable = true)]
public virtual string Exceptions { get; set; }
[SugarColumn(Length = 256, IsNullable = true)]
public virtual string Comments { get; set; }
public virtual int? HttpStatusCode { get; set; }
public AuditLog()
{
}
}