引言
PostgreSQL作为功能最强大的开源关系数据库之一，在企业级应用中占据重要地位。SQLSugar作为.NET生态中优秀的ORM框架，提供了对PostgreSQL的全面支持。本文将深入探讨如何高效使用SQLSugar操作PostgreSQL数据库，涵盖性能优化、特性利用、高级功能等多个方面。
一、PostgreSQL连接配置优化
1.1 连接字符串配置最佳实践
public class PostgreSqlConnectionConfig
{
public static ConnectionConfig GetOptimalConfig()
{
return new ConnectionConfig
{
ConnectionString = BuildConnectionString(),
DbType = DbType.PostgreSQL,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true, // 自动关闭连接
MoreSettings = new ConnMoreSettings
{
PgSqlIsAutoToLower = false, // 是否自动转小写
PgSqlIsAutoToLowerCodeFirst = false, // CodeFirst时是否转小写
IsAutoRemoveDataCache = true, // 自动清理缓存
DbMinPoolSize = 5, // 最小连接池大小
DbMaxPoolSize = 100, // 最大连接池大小
DbIdleTime = 15, // 空闲连接存活时间(分钟)
DbConnectionLifeTime = 15, // 连接生命周期(分钟)
DbCommandTimeout = 30, // 命令超时时间(秒)
EnablePaginationOptimize = true // 启用分页优化
}
};
}
private static string BuildConnectionString()
{
var builder = new NpgsqlConnectionStringBuilder
{
Host = "localhost",
Port = 5432,
Database = "mydb",
Username = "postgres",
Password = "password",
// 连接池优化
Pooling = true,
MinPoolSize = 5,
MaxPoolSize = 100,
ConnectionIdleLifetime = 15, // 分钟
ConnectionPruningInterval = 2, // 分钟
Timeout = 30, // 秒
CommandTimeout = 30, // 秒
// 性能优化参数
TcpKeepAlive = true,
KeepAlive = 60,
NoResetOnClose = true,
// 编码设置
Encoding = "UTF8",
// SSL设置
SslMode = SslMode.Prefer,
TrustServerCertificate = true,
// 应用名（用于监控）
ApplicationName = "MyApplication",
// 其他优化
Enlist = false, // 默认不参与分布式事务
MaxAutoPrepare = 100, // 最大预处理语句数
UsePerfCounters = false // 关闭性能计数器减少开销
};
return builder.ToString();
}
}
1.2 多数据源和读写分离配置
public class PostgreSqlMultiSourceConfig
{
public static List<ConnectionConfig> GetClusterConfigs()
{
return new List<ConnectionConfig>
{
// 主库（写库）
new ConnectionConfig
{
ConfigId = "master",
ConnectionString = "Host=master-host;Database=mydb;Username=user;Password=pass",
DbType = DbType.PostgreSQL,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute
},
// 从库1（读库）
new ConnectionConfig
{
ConfigId = "slave1",
ConnectionString = "Host=slave1-host;Database=mydb;Username=user;Password=pass",
DbType = DbType.PostgreSQL,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute
},
// 从库2（读库）
new ConnectionConfig
{
ConfigId = "slave2",
ConnectionString = "Host=slave2-host;Database=mydb;Username=user;Password=pass",
DbType = DbType.PostgreSQL,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute
}
};
}
}
// 使用读写分离
public class PostgreSqlRepository
{
private readonly ISqlSugarClient _db;
public PostgreSqlRepository()
{
var configs = PostgreSqlMultiSourceConfig.GetClusterConfigs();
_db = new SqlSugarClient(configs, db =>
{
// 配置主从
db.CurrentConnectionConfig = configs[0]; // 主库
// 从库配置
db.SlaveConnectionConfigs = new List<SlaveConnectionConfig>
{
new SlaveConnectionConfig { HitRate = 10, ConnectionString = configs[1].ConnectionString },
new SlaveConnectionConfig { HitRate = 10, ConnectionString = configs[2].ConnectionString }
};
// 配置AOP
ConfigureAop(db);
});
}
// 强制使用主库
public List<User> GetUsersFromMaster()
{
return _db.Queryable<User>()
.With(SqlWith.NoLock) // 强制主库
.ToList();
}
// 使用从库（自动负载均衡）
public List<User> GetUsersFromSlave()
{
return _db.Queryable<User>()
.AS<User>() // 使用从库
.ToList();
}
}
二、PostgreSQL特有功能支持
2.1 JSON/JSONB类型支持
// 实体类定义
[SugarTable("products")]
public class Product
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
public string Name { get; set; }
[SugarColumn(ColumnDataType = "jsonb")]
public ProductMetadata Metadata { get; set; }
[SugarColumn(ColumnDataType = "jsonb")]
public List<Tag> Tags { get; set; }
[SugarColumn(ColumnDataType = "jsonb")]
public JObject DynamicData { get; set; }
}
public class ProductMetadata
{
public string Manufacturer { get; set; }
public string Model { get; set; }
public Dictionary<string, string> Specifications { get; set; }
}
public class Tag
{
public string Name { get; set; }
public string Color { get; set; }
}
// JSON查询示例
public class JsonQueryExamples
{
private readonly ISqlSugarClient _db;
public List<Product> GetProductsByJsonPath()
{
// 查询JSON字段中的特定属性
return _db.Queryable<Product>()
.Where(p => SqlFunc.JsonField(p.Metadata, "$.Manufacturer") == "Apple")
.ToList();
}
public List<Product> GetProductsByJsonContains()
{
// 查询JSON数组中包含特定元素
return _db.Queryable<Product>()
.Where(p => SqlFunc.JsonContains(p.Tags,
"[{\"Name\":\"Electronics\"}]"))
.ToList();
}
public List<Product> QueryJsonField()
{
// 查询并返回JSON字段的部分内容
return _db.Queryable<Product>()
.Select(p => new
{
p.Id,
p.Name,
Manufacturer = SqlFunc.JsonField(p.Metadata, "$.Manufacturer"),
Model = SqlFunc.JsonField(p.Metadata, "$.Model")
})
.ToList();
}
public void UpdateJsonField()
{
// 更新JSON字段
_db.Updateable<Product>()
.SetColumns(p => p.Metadata, new ProductMetadata
{
Manufacturer = "Updated Manufacturer",
Model = "Updated Model"
})
.Where(p => p.Id == 1)
.ExecuteCommand();
// 更新JSON字段的部分内容
_db.Updateable<Product>()
.SetColumns(p => p.Metadata.Manufacturer == "New Manufacturer")
.Where(p => p.Id == 1)
.ExecuteCommand();
}
public List<Product> QueryJsonbWithIndex()
{
// 使用GIN索引加速JSONB查询
return _db.Queryable<Product>()
.Where(p => SqlFunc.JsonField(p.Metadata, "$.Manufacturer") == "Apple")
.With(SqlWith.NoLock)
.ToList();
}
}
2.2 数组类型支持
[SugarTable("users")]
public class User
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
public string Name { get; set; }
[SugarColumn(ColumnDataType = "integer[]")]
public List<int> FavoriteColors { get; set; }
[SugarColumn(ColumnDataType = "text[]")]
public List<string> Tags { get; set; }
[SugarColumn(ColumnDataType = "numeric[]")]
public List<decimal> Scores { get; set; }
}
public class ArrayQueryExamples
{
private readonly ISqlSugarClient _db;
public List<User> GetUsersByArrayContains()
{
// 查询数组包含特定元素
return _db.Queryable<User>()
.Where(u => u.Tags.Contains("vip"))
.ToList();
}
public List<User> GetUsersByArrayOverlap()
{
// 查询数组有重叠元素
var searchTags = new List<string> { "vip", "premium" };
return _db.Queryable<User>()
.Where(u => u.Tags.Any(tag => searchTags.Contains(tag)))
.ToList();
}
public List<User> GetUsersByArrayLength()
{
// 查询数组长度
return _db.Queryable<User>()
.Where(u => u.Tags.Count > 5)
.ToList();
}
public void UpdateArray()
{
// 向数组添加元素
_db.Updateable<User>()
.SetColumns(u => u.Tags, u => u.Tags.Append("new_tag"))
.Where(u => u.Id == 1)
.ExecuteCommand();
// 从数组移除元素
_db.Updateable<User>()
.SetColumns(u => u.Tags, u => u.Tags.Where(t => t != "old_tag").ToList())
.Where(u => u.Id == 1)
.ExecuteCommand();
}
public List<User> UnnestArrays()
{
// 展开数组（类似行转列）
return _db.Queryable<User>()
.Select(u => new
{
u.Id,
u.Name,
Tag = SqlFunc.AggregateUnnest(u.Tags) // 展开数组
})
.MergeTable() // 合并结果
.Where(t => t.Tag == "vip")
.Select(t => new User { Id = t.Id, Name = t.Name })
.ToList();
}
}
2.3 全文搜索功能
[SugarTable("articles")]
public class Article
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
[SugarColumn(IsIgnore = true)] // 不在数据库中存储
public string SearchVector { get; set; }
// 创建GIN索引
[SugarIndex("idx_articles_search", nameof(SearchVector), IndexType.Gin)]
public class ArticleIndex { }
}
public class FullTextSearchExamples
{
private readonly ISqlSugarClient _db;
public void CreateFullTextIndex()
{
// 创建全文搜索索引
_db.Ado.ExecuteCommand(@"
CREATE INDEX idx_articles_fts ON articles
USING gin(to_tsvector('english', title || ' ' || content));
-- 或者使用单独的tsvector列
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''))) STORED;
");
}
public List<Article> FullTextSearch(string searchTerm)
{
// 全文搜索查询
return _db.Queryable<Article>()
.Where(a => SqlFunc.Contains(
$"to_tsvector('english', {a.Title} || ' ' || {a.Content})",
$"plainto_tsquery('english', '{searchTerm}')"
))
.ToList();
}
public List<Article> FullTextSearchWithRanking(string searchTerm)
{
// 带排名的全文搜索
return _db.Queryable<Article>()
.Select(a => new
{
a.Id,
a.Title,
Rank = SqlFunc.AggregateCustom(
$"ts_rank(to_tsvector('english', {a.Title} || ' ' || {a.Content}), " +
$"plainto_tsquery('english', '{searchTerm}'))"
)
})
.Where(a => SqlFunc.Contains(
$"to_tsvector('english', {a.Title} || ' ' || {a.Content})",
$"plainto_tsquery('english', '{searchTerm}')"
))
.OrderBy(a => a.Rank, OrderByType.Desc)
.ToList();
}
public List<Article> FullTextSearchPhrase(string phrase)
{
// 短语搜索
return _db.Queryable<Article>()
.Where(a => SqlFunc.Contains(
$"to_tsvector('english', {a.Title} || ' ' || {a.Content})",
$"phraseto_tsquery('english', '{phrase}')"
))
.ToList();
}
}
三、PostgreSQL高级特性
3.1 CTE（公共表表达式）
public class CteExamples
{
private readonly ISqlSugarClient _db;
public List<DepartmentStats> GetDepartmentStatistics()
{
// 使用CTE进行复杂查询
var query = _db.Queryable<Employee>()
.GroupBy(e => e.DepartmentId)
.Select(e => new
{
DepartmentId = e.DepartmentId,
EmployeeCount = SqlFunc.AggregateCount(e.Id),
TotalSalary = SqlFunc.AggregateSum(e.Salary)
})
.AS("dept_stats");
var result = _db.Queryable(query)
.InnerJoin<Department>((stats, dept) => stats.DepartmentId == dept.Id)
.Select((stats, dept) => new DepartmentStats
{
DepartmentName = dept.Name,
EmployeeCount = stats.EmployeeCount,
TotalSalary = stats.TotalSalary,
AverageSalary = stats.TotalSalary / stats.EmployeeCount
})
.ToList();
return result;
}
public class DepartmentStats
{
public string DepartmentName { get; set; }
public int EmployeeCount { get; set; }
public decimal TotalSalary { get; set; }
public decimal AverageSalary { get; set; }
}
public List<EmployeeHierarchy> GetEmployeeHierarchy(int managerId)
{
// 递归CTE查询组织架构
var cteQuery = _db.UnionAll(
// 初始查询
_db.Queryable<Employee>()
.Where(e => e.Id == managerId)
.Select(e => new EmployeeHierarchy
{
EmployeeId = e.Id,
EmployeeName = e.Name,
ManagerId = e.ManagerId,
Level = 0
}),
// 递归查询
(parent, child) => _db.Queryable<Employee>()
.InnerJoin(parent, (e, p) => e.ManagerId == p.EmployeeId)
.Select((e, p) => new EmployeeHierarchy
{
EmployeeId = e.Id,
EmployeeName = e.Name,
ManagerId = e.ManagerId,
Level = p.Level + 1
})
);
return _db.Queryable(cteQuery)
.OrderBy(eh => eh.Level)
.ThenBy(eh => eh.EmployeeName)
.ToList();
}
public class EmployeeHierarchy
{
public int EmployeeId { get; set; }
public string EmployeeName { get; set; }
public int? ManagerId { get; set; }
public int Level { get; set; }
}
}
3.2 窗口函数
public class WindowFunctionExamples
{
private readonly ISqlSugarClient _db;
public List<SalesRank> GetSalesRankByRegion()
{
return _db.Queryable<Sales>()
.Select(s => new SalesRank
{
SalesPerson = s.SalesPerson,
Region = s.Region,
Amount = s.Amount,
RankInRegion = SqlFunc.AggregateCustom(
$"RANK() OVER (PARTITION BY {s.Region} ORDER BY {s.Amount} DESC)"
),
CumulativeAmount = SqlFunc.AggregateCustom(
$"SUM({s.Amount}) OVER (PARTITION BY {s.Region} ORDER BY {s.SaleDate})"
),
RegionTotal = SqlFunc.AggregateCustom(
$"SUM({s.Amount}) OVER (PARTITION BY {s.Region})"
),
PercentageOfRegion = SqlFunc.AggregateCustom(
$"{s.Amount} * 100.0 / SUM({s.Amount}) OVER (PARTITION BY {s.Region})"
)
})
.ToList();
}
public class SalesRank
{
public string SalesPerson { get; set; }
public string Region { get; set; }
public decimal Amount { get; set; }
public int RankInRegion { get; set; }
public decimal CumulativeAmount { get; set; }
public decimal RegionTotal { get; set; }
public decimal PercentageOfRegion { get; set; }
}
public List<MovingAverage> GetMovingAverage()
{
return _db.Queryable<StockPrice>()
.OrderBy(sp => sp.TradeDate)
.Select(sp => new MovingAverage
{
TradeDate = sp.TradeDate,
ClosePrice = sp.ClosePrice,
MovingAvg7 = SqlFunc.AggregateCustom(
$"AVG({sp.ClosePrice}) OVER (ORDER BY {sp.TradeDate} ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)"
),
MovingAvg30 = SqlFunc.AggregateCustom(
$"AVG({sp.ClosePrice}) OVER (ORDER BY {sp.TradeDate} ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)"
)
})
.ToList();
}
}
3.3 分区表支持
[SugarTable("logs", "CreatedTime", "month")]
public class Log
{
[SugarColumn(IsPrimaryKey = true)]
public Guid Id { get; set; }
public DateTime CreatedTime { get; set; }
[SugarColumn(IsNullable = true)]
public string Level { get; set; }
[SugarColumn(IsNullable = true)]
public string Message { get; set; }
[SugarColumn(ColumnDataType = "jsonb", IsNullable = true)]
public JObject Details { get; set; }
}
public class PartitionTableExamples
{
private readonly ISqlSugarClient _db;
public void SetupPartitioning()
{
// 创建分区表
_db.CodeFirst.BackupTable().InitTables(typeof(Log));
// 手动创建分区
CreatePartition("202401");
CreatePartition("202402");
CreatePartition("202403");
}
private void CreatePartition(string partitionSuffix)
{
var partitionName = $"logs_{partitionSuffix}";
var startDate = DateTime.Parse($"{partitionSuffix.Substring(0, 4)}-{partitionSuffix.Substring(4, 2)}-01");
var endDate = startDate.AddMonths(1);
_db.Ado.ExecuteCommand($@"
CREATE TABLE {partitionName} PARTITION OF logs
FOR VALUES FROM ('{startDate:yyyy-MM-dd}') TO ('{endDate:yyyy-MM-dd}');
-- 为分区创建索引
CREATE INDEX idx_{partitionName}_created ON {partitionName}(CreatedTime);
CREATE INDEX idx_{partitionName}_level ON {partitionName}(Level);
");
}
public List<Log> QueryPartitionedData(DateTime startDate, DateTime endDate)
{
// 自动路由到正确的分区
return _db.Queryable<Log>()
.Where(l => l.CreatedTime >= startDate && l.CreatedTime < endDate)
.OrderByDesc(l => l.CreatedTime)
.ToList();
}
public void DetachOldPartition(string partitionSuffix)
{
// 分离旧分区
var partitionName = $"logs_{partitionSuffix}";
_db.Ado.ExecuteCommand($@"
ALTER TABLE logs DETACH PARTITION {partitionName};
-- 归档到单独的表
ALTER TABLE {partitionName} RENAME TO {partitionName}_archive;
");
}
}
SQLSugar与PostgreSQL的结合为.NET开发者提供了强大的数据访问能力。通过合理配置连接、充分利用PostgreSQL特有功能、优化查询和事务处理，可以构建出高性能、可扩展的数据访问层。本文介绍的最佳实践和优化技巧，可以帮助开发者在实际项目中充分发挥PostgreSQL的潜力，构建高效稳定的应用程序。
扫一扫
933
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
