(精华)2020年10月18日 数据库调优 分库分表底层详解(EFCore实现DbContext读写分离负载均衡)
【摘要】
首先下载如下包：
Microsoft.EntityFrameworkCoreMicrosoft.EntityFrameworkCore.DesignMicrosoft.EntityFrameworkCo...
首先下载如下包：
- Microsoft.EntityFrameworkCore
- Microsoft.EntityFrameworkCore.Design
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.Tools
User类
public class User
{
public long Id { get; set; }
public string Account { get; set; }
public string Password { get; set; }
public string Name { get; set; }
public string Phone { get; set; }
public string Email { get; set; }
}
public class DbConnectionOption
{
public string MasterConnection { get; set; }
public IList<string> SlaveConnections{ get; set; }
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
定义服务层
public interface IUserService
{
User Find(long id);
User Insert(User user);
int Delete(Expression<Func<User, bool>> predicate);
}
public class UserService : IUserService
{
private readonly MyTestContext _dbContext;
private readonly ILogger<UserService> _logger;
public UserService(MyTestContext dbContext, ILogger<UserService> logger)
{
_dbContext = dbContext;
_logger = logger;
}
public User Find(long id)
{
_dbContext.ToSlave();
var conn = _dbContext.Database.GetDbConnection().ConnectionString;
_logger.LogInformation($"Find[id={id}] Using '{conn}'");
var user = _dbContext.User.FirstOrDefault(u => u.Id == id);
return user;
}
public User Insert(User user)
{
_dbContext.ToMaster();
var conn = _dbContext.Database.GetDbConnection().ConnectionString;
_logger.LogInformation($"Insert[Account={user.Account}] Using '{conn}'");
_dbContext.User.Add(user);
_dbContext.SaveChanges();
return user;
}
public int Delete(Expression<Func<User,bool>> predicate)
{
_dbContext.ToMaster();
var conn = _dbContext.Database.GetDbConnection().ConnectionString;
_logger.LogInformation($"Delete Using '{conn}'");
return _dbContext.User.Where(predicate).Delete();
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
均衡
public class SlaveRoundRobin
{
//服务器列表
private readonly IList<string> _items;
//锁
private readonly object _syncLock = new object();
//当前访问的服务器索引，开始是-1，因为没有人访问
private int _currentIndex = -1;
public SlaveRoundRobin(IOptions<DbConnectionOption> dbConnection)
{
_items = dbConnection.Value.SlaveConnections;
if(_items.Count <= 0 )
{
throw new ArgumentException("no elements.", nameof(SlaveRoundRobin));
}
}
public string GetNext()
{
lock (this._syncLock)
{
_currentIndex++;
//超过数量，索引归0
if (_currentIndex >= _items.Count)
_currentIndex = 0;
return _items[_currentIndex];
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
DbContext
public class MyTestContext : DbContext
{
private readonly DbConnectionOption _dbConnection;
private readonly SlaveRoundRobin _slaveRoundRobin;
public MyTestContext(IOptions<DbConnectionOption> options, SlaveRoundRobin slaveRoundRobin)
{
_dbConnection = options.Value;
_slaveRoundRobin = slaveRoundRobin;
}
public virtual DbSet<User> User { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer(_dbConnection.MasterConnection);
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<User>(entity =>
{
entity.HasIndex(e => e.Account)
.HasName("IX_Account")
.IsUnique();
entity.Property(e => e.Account).HasMaxLength(50);
entity.Property(e => e.Email).HasMaxLength(50);
entity.Property(e => e.Name)
.IsRequired()
.HasMaxLength(50);
entity.Property(e => e.Password).HasMaxLength(50);
entity.Property(e => e.Phone)
.IsRequired()
.HasMaxLength(15);
});
}
public void ToMaster()
{
Database.GetDbConnection().ConnectionString = _dbConnection.MasterConnection;
}
public void ToSlave()
{
Database.GetDbConnection().ConnectionString = _slaveRoundRobin.GetNext();
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
使用
class Program
{
static void Main(string[] args)
{
var configuration = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.Build()
.GetSection("ConnectionStrings");
var serviceProvider = new ServiceCollection()
.AddOptions()
.AddLogging(builder => builder.AddConsole())
.Configure<DbConnectionOption>(configuration)
.AddSingleton<SlaveRoundRobin>()
.AddDbContext<MyTestContext>()
.AddSingleton<IUserService, UserService>()
.BuildServiceProvider();
var userService = serviceProvider.GetRequiredService<IUserService>();
userService.Delete(u => u.Account == "Admin");
var newUser = new User
{
Account = "Admin",
Password = "123",
Name = "管理员",
Phone = "18811223344",
Email = "admin@qq.com"
};
userService.Insert(newUser);
var stopwatch = new Stopwatch();
stopwatch.Start();
var user = userService.Find(newUser.Id);
while (user == null)
{
Thread.Sleep(100);
user = userService.Find(newUser.Id);
}
stopwatch.Stop();
Console.WriteLine($"Account:{user.Account};Name:{user.Name};Delay:{stopwatch.Elapsed}");
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
文章来源: codeboy.blog.csdn.net，作者：愚公搬代码，版权归原作者所有，如需转载，请联系作者。
原文链接：codeboy.blog.csdn.net/article/details/109149652
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）