|
在實(shí)際業(yè)務(wù)系統(tǒng)中，當(dāng)單個數(shù)據(jù)庫不能承載負(fù)載壓力的時(shí)候，一般我們采用數(shù)據(jù)庫讀寫分離的方式來分擔(dān)數(shù)據(jù)庫負(fù)載。主庫承擔(dān)寫以及事務(wù)操作，從庫承擔(dān)讀操作。 為了支持多種數(shù)據(jù)庫我們先定義一個數(shù)據(jù)類型字典。key為連接字符串，value為數(shù)據(jù)庫類型： /// <summary> /// 數(shù)據(jù)庫方言集合 /// </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 }; 這樣我們切換不同的數(shù)據(jù)庫只需要配置數(shù)據(jù)庫連接字符串即可。 以mssql為例，配置數(shù)據(jù)庫連接字符串 "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)可以實(shí)現(xiàn)隨機(jī)訪問。也可以采用其他算法來負(fù)載均衡。 /// <summary> /// 主數(shù)據(jù)庫連接串 /// </summary> private string MasterConnectionString { get; set; } /// <summary> /// 從數(shù)據(jù)庫連接串集合 /// </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}"]; /// <summary> /// 數(shù)據(jù)庫類型 /// </summary> public DatabaseDialectEnum DatabaseDialect { get; private set; } 獲取主庫連接 private IDbConnection GetMasterConnection() { return GetConnection(MasterConnectionString); } 獲取從庫連接，這里采用隨機(jī)算法，如果沒有配置從庫，這里會返回主庫連接。 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("沒有設(shè)置從庫，將建立主庫連接"); 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來監(jiān)控?cái)?shù)據(jù)庫連接性能，所以 返回的connection用ProfiledDbConnection進(jìn)行了包裝。
主從數(shù)據(jù)源類型如下： public enum DataSourceEnum { MASTER, SLAVE } 本ConnectionFactory為單例模式，存在多線程訪問的情況，所以數(shù)據(jù)源設(shè)置為ThreadLocal<DataSourceEnum>,線程內(nèi)共享。 private static ThreadLocal<DataSourceEnum> threadLocal = new ThreadLocal<DataSourceEnum>(); /// <summary> /// 當(dāng)前線程數(shù)據(jù)源 /// </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(); } } 使用： 根據(jù)文章開頭所描述的實(shí)際操作來進(jìn)行主從庫訪問。 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() { //設(shè)置dapper的tableName取值 SqlMapperExtensions.TableNameMapper = (type) => type.Name; } /// <summary> /// 當(dāng)前線程數(shù)據(jù)源 /// </summary> /// <param name="sourceEnum"></param> public DataSourceEnum DataSource { set { threadLocal.Value = value; } get { return threadLocal.Value; } } /// <summary> /// 主數(shù)據(jù)庫連接串 /// </summary> private string MasterConnectionString { get; set; } /// <summary> /// 從數(shù)據(jù)庫連接串集合 /// </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> /// 數(shù)據(jù)庫方言集合 /// </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> /// 數(shù)據(jù)庫方言 /// </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("沒有設(shè)置從庫，將從建立主庫連接"); return GetMasterConnection(); } } } public enum DataSourceEnum { MASTER, SLAVE } |
|
|
