.NET 集成 SqlSugar、读写分离 、Redis
<h2>一、环境准备（NuGet 安装）</h2><div> </div>
<div>
<div dir="ltr">
<div>
<pre><code># SqlSugar 核心
Install-Package SqlSugarCore
# Redis 驱动 (StackExchange.Redis 官方推荐)
Install-Package StackExchange.Redis
# 缓存工具包
Install-Package Microsoft.Extensions.Caching.StackExchangeRedis
</code></pre>
</div>
<div> </div>
</div>
</div>
<div> </div>
<hr>
<div> </div>
<h2>二、appsettings.json 配置</h2>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>{
"ConnectionStrings": {
// 主库（写入、更新、删除）
"MasterDb": "Server=.;Database=TestDb;Uid=root;Pwd=123456;",
// 从库（只读，可配置多个）
"SlaveDb": "Server=.;Database=TestDb;Uid=root;Pwd=123456;",
// Redis
"Redis": "127.0.0.1:6379,password=,defaultDatabase=0"
}
}
</code></pre>
</div>
<div> </div>
</div>
</div>
<div> </div>
<hr>
<div> </div>
<h2>三、SqlSugar 读写分离配置</h2>
<div> </div>
<h3>1. 注册 SqlSugar（Program.cs）</h3>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>var builder = WebApplication.CreateBuilder(args);
// 1. 注册 SqlSugar + 读写分离
builder.Services.AddSingleton<ISqlSugarClient>(provider =>
{
var config = new ConnectionConfig()
{
// 主库
ConnectionString = builder.Configuration.GetConnectionString("MasterDb"),
DbType = DbType.MySql, // 根据你的数据库改：SqlServer/PostgreSQL
IsAutoCloseConnection = true,
// ===================== 读写分离配置 =====================
SlaveConnectionConfigs = new List<SlaveConnectionConfig>()
{
new SlaveConnectionConfig()
{
ConnectionString = builder.Configuration.GetConnectionString("SlaveDb"),
HitRate = 100 // 权重（多从库可分配比例）
}
}
};
return new SqlSugarClient(config);
});
// 2. 注册 Redis
builder.Services.AddStackExchangeRedisCache(options =>
{
options.Configuration = builder.Configuration.GetConnectionString("Redis");
});
builder.Services.AddControllers();
</code></pre>
</div>
<div> </div>
</div>
</div>
<div> </div>
<hr>
<div> </div>
<h2>四、读写分离使用规则（自动切换）</h2>
<div> </div>
<div>SqlSugar 完全自动切换主从库，你不用手动控制：</div>
<div> </div>
<h3>自动规则</h3>
<div> </div>
<ul>
<li>查询（Select） → 自动走 从库</li>
<li>增删改（Insert/Update/Delete） → 自动走 主库</li>
<li>事务内所有操作 → 强制走 主库</li>
</ul>
<div> </div>
<h3>基础使用示例（Service 层）</h3>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>public class UserService
{
private readonly ISqlSugarClient _db;
public UserService(ISqlSugarClient db)
{
_db = db;
}
// ========== 读：自动走从库 ==========
public async Task<User> GetUserAsync(int id)
{
return await _db.Queryable<User>().In(id).FirstAsync();
}
// ========== 写：自动走主库 ==========
public async Task<bool> AddUserAsync(User user)
{
return await _db.Insertable(user).ExecuteCommandAsync() > 0;
}
public async Task<bool> UpdateUserAsync(User user)
{
return await _db.Updateable(user).ExecuteCommandAsync() > 0;
}
}
</code></pre>
</div>
<div> </div>
</div>
</div>
<div> </div>
<hr>
<div> </div>
<h2>五、Redis 缓存基础使用</h2>
<div> </div>
<div>结合读写分离，实现 “先查缓存 → 缓存没有再查从库 → 存入缓存”</div>
<div> </div>
<h3>1. 封装 Redis 帮助类</h3>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>public class RedisCacheService
{
private readonly IDistributedCache _cache;
public RedisCacheService(IDistributedCache cache)
{
_cache = cache;
}
// 获取缓存
public async Task<T> GetAsync<T>(string key)
{
var value = await _cache.GetStringAsync(key);
return value == null ? default : JsonSerializer.Deserialize<T>(value);
}
// 设置缓存（带过期时间）
public async Task SetAsync<T>(string key, T value, TimeSpan? expiry = null)
{
expiry ??= TimeSpan.FromMinutes(10); // 默认10分钟
await _cache.SetStringAsync(key, JsonSerializer.Serialize(value), new DistributedCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = expiry
});
}
// 删除缓存
public async Task RemoveAsync(string key)
{
await _cache.RemoveAsync(key);
}
}
</code></pre>
</div>
<div> </div>
</div>
</div>
<h3>2. 注册 Redis 帮助类</h3>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>builder.Services.AddScoped<RedisCacheService>();
</code></pre>
</div>
<div> </div>
</div>
</div>
<div> </div>
<hr>
<div> </div>
<h2>六、最终：读写分离 + Redis 整合（最佳实践）</h2>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>public class UserService
{
private readonly ISqlSugarClient _db;
private readonly RedisCacheService _cache;
public UserService(ISqlSugarClient db, RedisCacheService cache)
{
_db = db;
_cache = cache;
}
/// <summary>
/// 缓存 + 从库查询
/// </summary>
public async Task<User> GetUserWithCacheAsync(int userId)
{
var cacheKey = $"user:{userId}";
// 1. 先查 Redis
var user = await _cache.GetAsync<User>(cacheKey);
if (user != null) return user;
// 2. Redis 没有 → 查 从库
user = await _db.Queryable<User>().In(userId).FirstAsync();
if (user == null) return null;
// 3. 存入 Redis（10分钟过期）
await _cache.SetAsync(cacheKey, user);
return user;
}
/// <summary>
/// 更新数据 → 主库 + 删除缓存
/// </summary>
public async Task<bool> UpdateUserAsync(User user)
{
// 走主库
var result = await _db.Updateable(user).ExecuteCommandAsync() > 0;
if (result)
{
// 更新成功 → 删除旧缓存
await _cache.RemoveAsync($"user:{user.Id}");
}
return result;
}
}
</code></pre>
</div>
<div> </div>
</div>
</div>
<div> </div>
<hr>
<div> </div>
<h2>七、强制指定主库 / 从库（特殊场景）</h2>
<div> </div>
<div>如果你需要手动控制：</div>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>// 强制走主库查询
await _db.Queryable<User>().With(SqlWith.Master).ToListAsync();
// 强制走从库
await _db.Queryable<User>().With(SqlWith.Slave).ToListAsync();</code></pre>
</div>
</div>
</div><br><br>
来源：https://www.cnblogs.com/chuansheng/p/19913845
頁:
[1]