在實際業務系統中,當單個資料庫不能承載負載壓力的時候,一般我們採用資料庫讀寫分離的方式來分擔資料庫負載。主庫承擔寫以及事務操作,從庫承擔讀操作。 為了支持多種資料庫我們先定義一個數據類型字典。key為連接字元串,value為資料庫類型: /// <summary> /// 資料庫方言集合 /// < ...
在實際業務系統中,當單個資料庫不能承載負載壓力的時候,一般我們採用資料庫讀寫分離的方式來分擔資料庫負載。主庫承擔寫以及事務操作,從庫承擔讀操作。
為了支持多種資料庫我們先定義一個數據類型字典。key為連接字元串,value為資料庫類型:
/// <summary> /// 資料庫方言集合 /// </summary> private readonly Dictionary<string, DatabaseDialectEnum> DialectDictionary = new Dictionary<string, DatabaseDialectEnum> { ["sqlconnection"] = DatabaseDialectEnum.MSSQL, ["sqlceconnection"] = DatabaseDialectEnum.SQLCE, ["npgsqlconnection"] = DatabaseDialectEnum.POSTGRES, ["sqliteconnection"] = DatabaseDialectEnum.SQLLITE, ["mysqlconnection"] = DatabaseDialectEnum.MYSQL, ["fbconnection"] = DatabaseDialectEnum.FIREBASE };
這樣我們切換不同的資料庫只需要配置資料庫連接字元串即可。
以mssql為例,配置資料庫連接字元串
"ConnectionString": { "sqlconnection": "Data Source=.;Initial Catalog=Db;User ID=sa;Password=**;Enlist=false;Max Pool SIZE=500;Min Pool SIZE=50;MultipleActiveResultSets=True", "sqlconnection_slaver_1": "Data Source=.;Initial Catalog=Db;User ID=sa;Password=**;Enlist=false;Max Pool SIZE=500;Min Pool SIZE=50;MultipleActiveResultSets=True", "sqlconnection_slaver_2": "Data Source=.;Initial Catalog=Db;User ID=sa;Password=**;Enlist=false;Max Pool SIZE=500;Min Pool SIZE=50;MultipleActiveResultSets=True" }
Key: sqlconnection為主庫(master)連接字元串,Key: sqlconnection_slaver_1和sqlconnection_slaver_2為兩個從庫(slaver)連接字元串。多個從庫(slaver)可以實現隨機訪問。也可以採用其他演算法來負載均衡。
根據字元串連接配置我們得到 主庫 連接串,和從庫連接串集合。同時根據連接串的key 確定資料庫種類。代碼如下:
/// <summary> /// 主資料庫連接串 /// </summary> private string MasterConnectionString { get; set; } /// <summary> /// 從資料庫連接串集合 /// </summary> private List<string> SlaverConnectionStrings { get; set; } = new List<string>(); public ConnectionFactory(IConfiguration configuration, ILoggerFactory loggerFactory) { _logger = loggerFactory.CreateLogger<ConnectionFactory>(); var connectionKeys = configuration.GetSection("ConnectionString").GetChildren().Select(s => s.Key).ToArray(); foreach (var connKey in connectionKeys) { var connSplit = connKey.Split('_'); if (connSplit.Length == 1) { MasterConnectionString = configuration[$"ConnectionString:{connKey}"];
//根據連接字元串約定,確定資料庫類型 DatabaseDialect = DialectDictionary[connKey]; } else { SlaverConnectionStrings.Add(configuration[$"ConnectionString:{connKey}"]); } } }
/// <summary> /// 資料庫類型 /// </summary> public DatabaseDialectEnum DatabaseDialect { get; private set; }
獲取主庫連接
private IDbConnection GetMasterConnection() { return GetConnection(MasterConnectionString); }
獲取從庫連接,這裡採用隨機演算法,如果沒有配置從庫,這裡會返回主庫連接。
private IDbConnection GetSlaverConnection() { int sc = SlaverConnectionStrings.Count(); if (sc > 0) { Random random = new Random(); int index = random.Next(0, sc); return GetConnection(SlaverConnectionStrings[index]); } else { _logger.LogInformation("沒有設置從庫,將建立主庫連接"); return GetMasterConnection(); } }
private IDbConnection GetConnection(string connectionString) => DatabaseDialect switch { DatabaseDialectEnum.MSSQL =>new ProfiledDbConnection(new SqlConnection(connectionString),MiniProfiler.Current), DatabaseDialectEnum.MYSQL => new ProfiledDbConnection(new MySqlConnection(connectionString), MiniProfiler.Current), _ => throw new NotImplementedException() };
註:這裡採用MiniProfiler來監控資料庫連接性能,所以 返回的connection用ProfiledDbConnection進行了包裝。
主從數據源類型如下:
public enum DataSourceEnum { MASTER, SLAVE }
本ConnectionFactory為單例模式,存在多線程訪問的情況,所以數據源設置為ThreadLocal<DataSourceEnum>,線程內共用。
private static ThreadLocal<DataSourceEnum> threadLocal = new ThreadLocal<DataSourceEnum>(); /// <summary> /// 當前線程數據源 /// </summary> /// <param name="sourceEnum"></param> public DataSourceEnum DataSource { set { threadLocal.Value = value; } get { return threadLocal.Value; } }
下麵正式獲取IDbConnection
public IDbConnection GetDbConnection() { if (DataSource == DataSourceEnum.MASTER) { return GetMasterConnection(); } else { return GetSlaverConnection(); } }
使用:
根據文章開頭所描述的實際操作來進行主從庫訪問。
private IDbConnection GetDbConnection(DataSourceEnum dataSource) { ConnectionFactory.DataSource = dataSource; return ConnectionFactory.GetDbConnection(); }
using var connection = GetDbConnection(DataSourceEnum.MASTER); connection.Execute(sql, param, CurrentTransaction, null, commandType)
using var connection = GetDbConnection(DataSourceEnum.SLAVE); connection.Get<T>(id, CurrentTransaction, CommandTimeout)
奉上全部代碼
public class ConnectionFactory : IConnectionFactory { private readonly ILogger _logger; private static ThreadLocal<DataSourceEnum> threadLocal = new ThreadLocal<DataSourceEnum>(); static ConnectionFactory() { //設置dapper的tableName取值 SqlMapperExtensions.TableNameMapper = (type) => type.Name; } /// <summary> /// 當前線程數據源 /// </summary> /// <param name="sourceEnum"></param> public DataSourceEnum DataSource { set { threadLocal.Value = value; } get { return threadLocal.Value; } } /// <summary> /// 主資料庫連接串 /// </summary> private string MasterConnectionString { get; set; } /// <summary> /// 從資料庫連接串集合 /// </summary> private List<string> SlaverConnectionStrings { get; set; } = new List<string>(); public ConnectionFactory(IConfiguration configuration, ILoggerFactory loggerFactory) { _logger = loggerFactory.CreateLogger<ConnectionFactory>(); var connectionKeys = configuration.GetSection("ConnectionString").GetChildren().Select(s => s.Key).ToArray(); foreach (var connKey in connectionKeys) { var connSplit = connKey.Split('_'); if (connSplit.Length == 1) { MasterConnectionString = configuration[$"ConnectionString:{connKey}"]; DatabaseDialect = DialectDictionary[connKey]; } else { SlaverConnectionStrings.Add(configuration[$"ConnectionString:{connKey}"]); } } } /// <summary> /// 資料庫方言集合 /// </summary> private readonly Dictionary<string, DatabaseDialectEnum> DialectDictionary = new Dictionary<string, DatabaseDialectEnum> { ["sqlconnection"] = DatabaseDialectEnum.MSSQL, ["sqlceconnection"] = DatabaseDialectEnum.SQLCE, ["npgsqlconnection"] = DatabaseDialectEnum.POSTGRES, ["sqliteconnection"] = DatabaseDialectEnum.SQLLITE, ["mysqlconnection"] = DatabaseDialectEnum.MYSQL, ["fbconnection"] = DatabaseDialectEnum.FIREBASE }; /// <summary> /// 資料庫方言 /// </summary> public DatabaseDialectEnum DatabaseDialect { get; private set; } private IDbConnection GetConnection(string connectionString) => DatabaseDialect switch { DatabaseDialectEnum.MSSQL =>new ProfiledDbConnection(new SqlConnection(connectionString),MiniProfiler.Current), DatabaseDialectEnum.MYSQL => new ProfiledDbConnection(new MySqlConnection(connectionString), MiniProfiler.Current), _ => throw new NotImplementedException() }; public IDbConnection GetDbConnection() { if (DataSource == DataSourceEnum.MASTER) { return GetMasterConnection(); } else { return GetSlaverConnection(); } } private IDbConnection GetMasterConnection() { return GetConnection(MasterConnectionString); } private IDbConnection GetSlaverConnection() { int sc = SlaverConnectionStrings.Count(); if (sc > 0) { Random random = new Random(); int index = random.Next(0, sc); return GetConnection(SlaverConnectionStrings[index]); } else { _logger.LogInformation("沒有設置從庫,將從建立主庫連接"); return GetMasterConnection(); } } } public enum DataSourceEnum { MASTER, SLAVE }