(精华)2020年10月18日 数据库调优 分库分表底层详解(EFCore实现DbContext读写分离负载均衡)

首先下载如下包:

  1. Microsoft.EntityFrameworkCore
  2. Microsoft.EntityFrameworkCore.Design
  3. Microsoft.EntityFrameworkCore.SqlServer
  4. 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; }
}

定义服务层

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();
    }
}

均衡

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];
        }
    }
}

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();
        }
    }

使用

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}");
        }
    }
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 猿与汪的秘密 设计师:白松林 返回首页