如今，我们操作数据库一般用ORM框架

现在用.NET Core + EFCore + SqlServer 实现数据读写分离

介绍

为什么要读写分离？

降低数据库服务器的压力

如何实现读写分离？

1.一个主库多个从库

2.配置主库复制数据到从库

 

 

为什么一个主库多个从库？

一般查询多于增删改，这就是我们常说的二八原则，20%操作是增删改，80%操作是查询

 

是否有缺点？

有延迟

 

如何解决延迟问题？

比较及时性的数据还是通过主库查询

 

具体如何实现？

通过发布服务器，主库发布，而从库订阅，从而实现主从库

实现

SqlServer 实现

使用SqlServer 2019，新建一个主库，创建表，再通过本地发布创建发布，

然后通过本地订阅订阅主库，创建两个从库

.NET Core MVC项目实现

项目结构

首先，在appsettings.json配置数据库连接字符串

1.   {
2.   "Logging": {
3.   "LogLevel": {
4.   "Default": "Information",
5.   "Microsoft": "Warning",
6.   "Microsoft.Hosting.Lifetime": "Information"
7.   }
8.   },
9.   "AllowedHosts": "*",
10.   "ConnectionStrings": {
11.   "EFCoreTestToRead": "Server=GREAMBWANG-DC\\MSSQLSERVER2019;Database=EFCoreTestToRead01;Trusted_Connection=True;,Server=GREAMBWANG-DC\\MSSQLSERVER2019;Database=EFCoreTestToRead02;Trusted_Connection=True;",
12.   "EFCoreTestToWrite": "Server=GREAMBWANG-DC\\MSSQLSERVER2019;Database=EFCoreTest;Trusted_Connection=True;"
13.   }
14.   }

Models层实现

创建模型

1.   public class UserInfo
2.   {
3.   [Key]
4.   public int Id { get; set; }
5.   public string Name { get; set; }
6.   public int Age { get; set; }
7.    
8.   }

创建上下文

1.   public class EFCoreContext : DbContext
2.   {
3.   public EFCoreContext(string connectionString)
4.   {
5.   ConnectionString = connectionString;
6.    
7.   //创建数据库
8.   //Database.EnsureCreated();
9.   }
10.    
11.   private string ConnectionString { get; }
12.    
13.   public DbSet<UserInfo> UserInfo { get; set; }
14.    
15.   /// <summary>
16.   /// 配置连接数据库
17.   /// </summary>
18.   /// <param name="optionsBuilder"></param>
19.   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
20.   {
21.   //base.OnConfiguring(optionsBuilder);
22.   optionsBuilder.UseSqlServer(ConnectionString);
23.    
24.    
25.   }
26.    
27.   protected override void OnModelCreating(ModelBuilder modelBuilder)
28.   {
29.   //base.OnModelCreating(modelBuilder);
30.    
31.   //初始化数据
32.   modelBuilder.Entity<UserInfo>().HasData(new List<UserInfo>()
33.   {
34.   new UserInfo() { Id = 1, Name = "哈哈", Age = 17 },
35.   new UserInfo() { Id = 2, Name = "呵呵", Age = 18 },
36.   new UserInfo() { Id = 3, Name = "嘻嘻", Age = 19 }
37.   });
38.   }
39.   }

创建上下文工厂

读写枚举

1.   public enum WriteAndReadEnum
2.   {
3.   Write,
4.   Read
5.   }

接口

1.   public interface IDbContextFactory
2.   {
3.   EFCoreContext CreateContext(WriteAndReadEnum writeAndRead);
4.   }

实现

在实现数据查询上，可以使用不同的策略，一般有随机策略，权重策略，轮询策略

随机策略：随机选择一个从库进行查询

权重策略：根据权重比例选择从库查询

轮询策略：根据顺序选择从库查询

1.   public class DbContextFactory : IDbContextFactory
2.   {
3.   private IConfiguration Configuration { get; }
4.   private string[] ReadConnectionStrings;
5.   public DbContextFactory(IConfiguration configuration)
6.   {
7.   Configuration = configuration;
8.   ReadConnectionStrings = Configuration.GetConnectionString("EFCoreTestToRead").Split(",");
9.   }
10.    
11.   public EFCoreContext CreateContext(WriteAndReadEnum writeAndRead)
12.   {
13.   string connectionString = string.Empty;
14.   switch (writeAndRead)
15.   {
16.   case WriteAndReadEnum.Write:
17.   connectionString = Configuration.GetConnectionString("EFCoreTestToWrite");
18.   break;
19.   case WriteAndReadEnum.Read:
20.   connectionString = GetReadConnectionString();
21.   break;
22.   default:
23.   break;
24.   }
25.   return new EFCoreContext(connectionString);
26.   }
27.    
28.   private string GetReadConnectionString()
29.   {
30.   /*
31.   * 随机策略
32.   * 权重策略
33.   * 轮询策略
34.   */
35.    
36.   //随机策略
37.   string connectionString = ReadConnectionStrings[new Random().Next(0, ReadConnectionStrings.Length)];
38.    
39.   return connectionString;
40.   }
41.   }

Models层完成

 

在Web层中

在Startup的ConfigureServices方法添加依赖注入

services.AddScoped<IDbContextFactory, DbContextFactory>();

 

操作

1.   public class HomeController : Controller
2.   {
3.   private readonly ILogger<HomeController> _logger;
4.    
5.   public IDbContextFactory DbContextFactory { get; }
6.    
7.   public HomeController(ILogger<HomeController> logger,IDbContextFactory dbContextFactory)
8.   {
9.   _logger = logger;
10.   DbContextFactory = dbContextFactory;
11.   }
12.    
13.   public IActionResult Index()
14.   {
15.   //写入操作
16.   EFCoreContext writeContext = DbContextFactory.CreateContext(WriteAndReadEnum.Write);
17.   writeContext.UserInfo.Add(new UserInfo() { Name = "AA", Age = 20 });
18.   writeContext.SaveChanges();
19.    
20.   //查询操作
21.   EFCoreContext readContext = DbContextFactory.CreateContext(WriteAndReadEnum.Read);
22.   UserInfo userInfo = readContext.UserInfo.OrderByDescending(u => u.Id).FirstOrDefault();
23.    
24.   return View();
25.   }
26.   }