.NET 本地Db數(shù)據(jù)庫-技術(shù)方案選型
當(dāng)前位置：點晴教程→知識管理交流
→『 技術(shù)文檔交流 』
公司現(xiàn)有項目使用了LiteDB作為本地數(shù)據(jù)存儲，但每次開機有較高的概率讀取阻塞。 因為死鎖或者損壞導(dǎo)致的阻塞問題，目前只能設(shè)置超時。在db讀取超時后，部分情況可以刪除文件、重建db解決，也有無法刪除db文件的情況。 導(dǎo)致的技術(shù)債務(wù)造成了非常多的冗余維護(hù)工作量，需要基于常用的數(shù)據(jù)庫及使用方式，重新做個技術(shù)選型確認(rèn) LiteDB，是一類NoSql的文檔數(shù)據(jù)庫，引用Nuget包LiteDB對接開發(fā)，社區(qū)litedb-org/LiteDB: LiteDB - A .NET NoSQL Document Store in a single data file 在Windows本地數(shù)據(jù)存儲場景中主要有Sqlite、LiteDB、LocalDB幾個主要選項 Windows本地數(shù)據(jù)庫選型.NET Windows 本地數(shù)據(jù)庫中 SQLite、LiteDB、LocalDB 的對比，CodeX生成如下：
1. SQLite特點：
適合：
劣勢：
2. LiteDB特點：
適合：
劣勢：
3. LocalDB（SQL Server LocalDB）**特點： SQL Server Express 的輕量模式
適合：
劣勢：
數(shù)據(jù)庫選型建議1. 死鎖損壞問題按上面收集的情況，litedb存在頻繁的db死鎖損壞問題 SQLite 是否也會卡死？ SQLite 不會出現(xiàn) LiteDB 這種"卡死"問題。 原因如下： 1. SQLite 有內(nèi)置的 busy_timeout 機制，寫鎖沖突時會自動等待+重試，超時后返回錯誤，不會無限阻塞 2. WAL 模式下讀寫不互相阻塞，只有寫-寫沖突 3. 多個連接實例訪問同一文件是 SQLite 的正常用法，而 LiteDB 在這種模式下就容易死鎖 4. SQLite 的鎖機制經(jīng)過 20+ 年生產(chǎn)環(huán)境驗證 根據(jù)已知的社區(qū)反饋，liteDb在并發(fā)讀寫這塊有較多問題。LiteDB 的鎖機制在高并發(fā)場景下天然脆弱，而 SQLite 的 WAL 模式能更好地支持并發(fā)讀寫，且生態(tài)更成熟、調(diào)試工具更豐富。 2.社區(qū)成熟度考慮到社區(qū)成熟度的情況。LiteDb Github倉庫已知大量死鎖問題，Nuget引用量37.8M不算高；而Sqlite是windows客戶端本地標(biāo)準(zhǔn)成熟的方案了
3.性能對比拆成 5 個指標(biāo)看：
純讀寫吞吐（尤其批量寫）：通常 SQLite ≥ LocalDB > LiteDB（具體取決于索引、事務(wù)、同步模式、數(shù)據(jù)模型） 所以大部分情況選用Sqlite。如果是其它小場景的需求，對象存儲可以選文檔型數(shù)據(jù)庫LiteDB, 要兼容 SQL Server可以選LocalDB Sqlite使用方式選型.NET sqlte數(shù)據(jù)庫支持包：
轉(zhuǎn)換數(shù)據(jù)類有以下幾種方式：
所以.NET讀寫數(shù)據(jù)庫有幾下方案：
以下分別給出4種方案，完成.NET的數(shù)據(jù)庫讀寫以及表遷移 數(shù)據(jù)庫表遷移目標(biāo)（V1 -> V2）
EF Core + EFCore.SqliteEF Core，適合快速開發(fā)、團(tuán)隊熟悉 .NET 官方生態(tài)。但映射存在一定的性能開銷 1 using Microsoft.EntityFrameworkCore; 2 using Microsoft.EntityFrameworkCore.Migrations; 3 4 var db = new AppDbContext(); 5 db.Database.Migrate(); // 純代碼觸發(fā)遷移 6 7 // 寫 8 db.Users.Add(new User { Name = "Alice", Email = "alice@test.com", Age = 20 }); 9 db.SaveChanges(); 10 11 // 讀 12 foreach (var u in db.Users.AsNoTracking()) 13 { 14 Console.WriteLine($"{u.Id} {u.Name} {u.Email} Age={u.Age}"); 15 } 16 17 public class AppDbContext : DbContext 18 { 19 public DbSet<User> Users => Set<User>(); 20 21 protected override void OnConfiguring(DbContextOptionsBuilder options) 22 => options.UseSqlite("Data Source=efcore_sqlite_demo.db"); 23 } 24 25 public class User 26 { 27 public int Id { get; set; } 28 public string Name { get; set; } = ""; 29 public string Email { get; set; } = ""; 30 public int? Age { get; set; } 31 } 32 33 // ====== 遷移1：Init ====== 34 [DbContext(typeof(AppDbContext))] 35 [Migration("202602260001_Init")] 36 public class Init : Migration 37 { 38 protected override void Up(MigrationBuilder migrationBuilder) 39 { 40 migrationBuilder.CreateTable( 41 name: "Users", 42 columns: table => new 43 { 44 Id = table.Column<int>(nullable: false) 45 .Annotation("Sqlite:Autoincrement", true), 46 Name = table.Column<string>(nullable: false), 47 Email = table.Column<string>(nullable: false) 48 }, 49 constraints: table => table.PrimaryKey("PK_Users", x => x.Id)); 50 } 51 52 protected override void Down(MigrationBuilder migrationBuilder) 53 => migrationBuilder.DropTable(name: "Users"); 54 } 55 56 // ====== 遷移2：AddAgeAndBackfill ====== 57 [DbContext(typeof(AppDbContext))] 58 [Migration("202602260002_AddAgeAndBackfill")] 59 public class AddAgeAndBackfill : Migration 60 { 61 protected override void Up(MigrationBuilder migrationBuilder) 62 { 63 migrationBuilder.AddColumn<int>( 64 name: "Age", 65 table: "Users", 66 nullable: true); 67 68 migrationBuilder.Sql("UPDATE Users SET Age = 18 WHERE Age IS NULL;"); 69 } 70 71 protected override void Down(MigrationBuilder migrationBuilder) 72 => migrationBuilder.DropColumn(name: "Age", table: "Users"); 73 } EF Core + 補充手寫sql如果既想用 EF Core，又希望對數(shù)據(jù)庫變更“強可控”，則可以使用EF Core + Microsoft.Data.Sqlite 1 using Microsoft.Data.Sqlite; 2 using Microsoft.EntityFrameworkCore; 3 4 var connStr = "Data Source=efcore_manual_demo.db"; 5 await MigrationRunner.MigrateAsync(connStr); 6 7 using var db = new AppDbContext(connStr); 8 9 // 寫 10 db.Users.Add(new User { Name = "Alice", Email = "alice@test.com", Age = 20 }); 11 db.SaveChanges(); 12 13 // 讀 14 foreach (var u in db.Users.AsNoTracking()) 15 { 16 Console.WriteLine($"{u.Id} {u.Name} {u.Email} Age={u.Age}"); 17 } 18 19 public static class MigrationRunner 20 { 21 public static async Task MigrateAsync(string connStr) 22 { 23 await using var conn = new SqliteConnection(connStr); 24 await conn.OpenAsync(); 25 26 // 版本表 27 var createVersion = conn.CreateCommand(); 28 createVersion.CommandText = """ 29 CREATE TABLE IF NOT EXISTS __schema_migrations ( 30 version TEXT NOT NULL PRIMARY KEY, 31 applied_at TEXT NOT NULL 32 ); 33 """; 34 await createVersion.ExecuteNonQueryAsync(); 35 36 await ApplyIfNotExists(conn, "202602260001_Init", """ 37 CREATE TABLE IF NOT EXISTS Users ( 38 Id INTEGER PRIMARY KEY AUTOINCREMENT, 39 Name TEXT NOT NULL, 40 Email TEXT NOT NULL 41 ); 42 """); 43 44 await ApplyIfNotExists(conn, "202602260002_AddAgeAndBackfill", """ 45 ALTER TABLE Users ADD COLUMN Age INTEGER NULL; 46 UPDATE Users SET Age = 18 WHERE Age IS NULL; 47 """); 48 } 49 50 private static async Task ApplyIfNotExists(SqliteConnection conn, string version, string sql) 51 { 52 var check = conn.CreateCommand(); 53 check.CommandText = "SELECT COUNT(1) FROM __schema_migrations WHERE version = $v"; 54 check.Parameters.AddWithValue("$v", version); 55 var exists = Convert.ToInt32(await check.ExecuteScalarAsync()) > 0; 56 if (exists) return; 57 58 await using var tx = await conn.BeginTransactionAsync(); 59 try 60 { 61 var cmd = conn.CreateCommand(); 62 cmd.Transaction = tx; 63 cmd.CommandText = sql; 64 await cmd.ExecuteNonQueryAsync(); 65 66 var ins = conn.CreateCommand(); 67 ins.Transaction = tx; 68 ins.CommandText = """ 69 INSERT INTO __schema_migrations(version, applied_at) 70 VALUES($v, $t); 71 """; 72 ins.Parameters.AddWithValue("$v", version); 73 ins.Parameters.AddWithValue("$t", DateTime.UtcNow.ToString("O")); 74 await ins.ExecuteNonQueryAsync(); 75 76 await tx.CommitAsync(); 77 } 78 catch (SqliteException ex) when (ex.Message.Contains("duplicate column name")) 79 { 80 await tx.RollbackAsync(); 81 } 82 } 83 } 84 85 public class AppDbContext : DbContext 86 { 87 private readonly string _connStr; 88 public AppDbContext(string connStr) => _connStr = connStr; 89 public DbSet<User> Users => Set<User>(); 90 protected override void OnConfiguring(DbContextOptionsBuilder options) 91 => options.UseSqlite(_connStr); 92 } 93 94 public class User 95 { 96 public int Id { get; set; } 97 public string Name { get; set; } = ""; 98 public string Email { get; set; } = ""; 99 public int? Age { get; set; } 100 } Dapper + Microsoft.Data.Sqlite適合性能優(yōu)先、SQL 可控優(yōu)先、追求輕量。這類開銷低、速度快、透明 SQL；適合高頻讀寫和明確數(shù)據(jù)模型。但缺點很明顯，sql量太多了 1 using Dapper; 2 using Microsoft.Data.Sqlite; 3 4 var connStr = "Data Source=dapper_demo.db"; 5 using var conn = new SqliteConnection(connStr); 6 conn.Open(); 7 8 Migrate(conn); 9 10 // 寫 11 conn.Execute( 12 "INSERT INTO Users(Name, Email, Age) VALUES (@Name, @Email, @Age);", 13 new { Name = "Alice", Email = "alice@test.com", Age = 20 }); 14 15 // 讀 16 var users = conn.Query<User>("SELECT Id, Name, Email, Age FROM Users ORDER BY Id;").ToList(); 17 foreach (var u in users) 18 { 19 Console.WriteLine($"{u.Id} {u.Name} {u.Email} Age={u.Age}"); 20 } 21 22 static void Migrate(SqliteConnection conn) 23 { 24 conn.Execute(""" 25 CREATE TABLE IF NOT EXISTS __schema_migrations ( 26 version TEXT NOT NULL PRIMARY KEY, 27 applied_at TEXT NOT NULL 28 ); 29 """); 30 31 Apply(conn, "202602260001_Init", """ 32 CREATE TABLE IF NOT EXISTS Users ( 33 Id INTEGER PRIMARY KEY AUTOINCREMENT, 34 Name TEXT NOT NULL, 35 Email TEXT NOT NULL 36 ); 37 """); 38 39 Apply(conn, "202602260002_AddAgeAndBackfill", """ 40 ALTER TABLE Users ADD COLUMN Age INTEGER NULL; 41 UPDATE Users SET Age = 18 WHERE Age IS NULL; 42 """); 43 } 44 45 static void Apply(SqliteConnection conn, string version, string sql) 46 { 47 var exists = conn.ExecuteScalar<long>( 48 "SELECT COUNT(1) FROM __schema_migrations WHERE version=@v", new { v = version }) > 0; 49 if (exists) return; 50 51 using var tx = conn.BeginTransaction(); 52 try 53 { 54 conn.Execute(sql, transaction: tx); 55 conn.Execute(""" 56 INSERT INTO __schema_migrations(version, applied_at) 57 VALUES(@v, @t) 58 """, new { v = version, t = DateTime.UtcNow.ToString("O") }, tx); 59 60 tx.Commit(); 61 } 62 catch (SqliteException ex) when (ex.Message.Contains("duplicate column name")) 63 { 64 tx.Rollback(); 65 } 66 } 67 68 public class User 69 { 70 public long Id { get; set; } 71 public string Name { get; set; } = ""; 72 public string Email { get; set; } = ""; 73 public int? Age { get; set; } 74 } SqlSugar + Microsoft.Data.Sqlite上手快，功能集成度高（CodeFirst/DbFirst 等）。如果是數(shù)據(jù)庫表結(jié)構(gòu)經(jīng)常變動，建議使用這個方案，CodeFrist開發(fā)非常便捷 1 using SqlSugar; 2 3 var db = new SqlSugarClient(new ConnectionConfig 4 { 5 ConnectionString = "Data Source=sqlsugar_demo.db", 6 DbType = DbType.Sqlite, 7 IsAutoCloseConnection = true, 8 InitKeyType = InitKeyType.Attribute 9 }); 10 11 Migrate(db); 12 13 // 寫 14 db.Insertable(new User { Name = "Alice", Email = "alice@test.com", Age = 20 }).ExecuteCommand(); 15 16 // 讀 17 var list = db.Queryable<User>().OrderBy(x => x.Id).ToList(); 18 foreach (var u in list) 19 { 20 Console.WriteLine($"{u.Id} {u.Name} {u.Email} Age={u.Age}"); 21 } 22 23 static void Migrate(SqlSugarClient db) 24 { 25 db.Ado.ExecuteCommand(""" 26 CREATE TABLE IF NOT EXISTS __schema_migrations ( 27 version TEXT NOT NULL PRIMARY KEY, 28 applied_at TEXT NOT NULL 29 ); 30 """); 31 32 Apply(db, "202602260001_Init", """ 33 CREATE TABLE IF NOT EXISTS Users ( 34 Id INTEGER PRIMARY KEY AUTOINCREMENT, 35 Name TEXT NOT NULL, 36 Email TEXT NOT NULL 37 ); 38 """); 39 40 Apply(db, "202602260002_AddAgeAndBackfill", """ 41 ALTER TABLE Users ADD COLUMN Age INTEGER NULL; 42 UPDATE Users SET Age = 18 WHERE Age IS NULL; 43 """); 44 } 45 46 static void Apply(SqlSugarClient db, string version, string sql) 47 { 48 var exists = db.Ado.GetInt(""" 49 SELECT COUNT(1) FROM __schema_migrations WHERE version=@v 50 """, new { v = version }) > 0; 51 52 if (exists) return; 53 54 db.Ado.BeginTran(); 55 try 56 { 57 db.Ado.ExecuteCommand(sql); 58 db.Ado.ExecuteCommand(""" 59 INSERT INTO __schema_migrations(version, applied_at) 60 VALUES(@v, @t) 61 """, new { v = version, t = DateTime.UtcNow.ToString("O") }); 62 63 db.Ado.CommitTran(); 64 } 65 catch 66 { 67 db.Ado.RollbackTran(); 68 } 69 } 70 71 [SugarTable("Users")] 72 public class User 73 { 74 [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] 75 public int Id { get; set; } 76 public string Name { get; set; } = ""; 77 public string Email { get; set; } = ""; 78 public int? Age { get; set; } 79 } 如果使用SqlSugar的已封裝CodeFrist方案，遷移數(shù)據(jù)表會更簡單： 1 using SqlSugar; 2 3 var db = new SqlSugarClient(new ConnectionConfig 4 { 5 ConnectionString = "Data Source=app.db", 6 DbType = DbType.Sqlite, 7 IsAutoCloseConnection = true, 8 InitKeyType = InitKeyType.Attribute, 9 ConfigureExternalServices = new ConfigureExternalServices 10 { 11 EntityService = (prop, col) => 12 { 13 // 可選：統(tǒng)一處理字符串長度等 14 if (prop.PropertyType == typeof(string) && col.Length == 0) 15 col.Length = 200; 16 } 17 } 18 }); 19 20 // 1) CodeFirst 建表/補字段 21 db.CodeFirst.InitTables<User>(); 22 23 // 2) 如需“遷移數(shù)據(jù)”（例如給新字段Age回填），用.NET代碼執(zhí)行SQL 24 db.Ado.ExecuteCommand("UPDATE Users SET Age = 18 WHERE Age IS NULL;"); 25 26 // 3) 寫入 27 db.Insertable(new User 28 { 29 Name = "Alice", 30 Email = "alice@test.com", 31 Age = 20 32 }).ExecuteCommand(); 33 34 // 4) 讀取 35 var users = db.Queryable<User>().OrderBy(x => x.Id).ToList(); 36 foreach (var u in users) 37 { 38 Console.WriteLine($"{u.Id} {u.Name} {u.Email} Age={u.Age}"); 39 } 40 41 [SugarTable("Users")] 42 public class User 43 { 44 [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] 45 public int Id { get; set; } 46 47 [SugarColumn(Length = 100, IsNullable = false)] 48 public string Name { get; set; } = string.Empty; 49 50 [SugarColumn(Length = 200, IsNullable = false)] 51 public string Email { get; set; } = string.Empty; 52 53 // 新增字段：CodeFirst會嘗試補列 54 [SugarColumn(IsNullable = true)] 55 public int? Age { get; set; } 56 } 但要注意： Sugar的幾種操作方式， CodeFirst：db.CodeFirst.InitTables<User>()，啥叫CodeFirst?就是代碼優(yōu)先，由代碼驅(qū)動數(shù)據(jù)庫。不用自己先在數(shù)據(jù)庫里設(shè)計好表、字段、索引，然后用工具生成 C# 實體（這種叫DbFirst） ORM 的 CRUD/表達(dá)式 API：Insertable / Updateable / Queryable 原生 SQL 執(zhí)行：db.Ado.ExecuteCommand("UPDATE ...") 所以，個人建議使用SqlSugar方案，CodeFirst數(shù)據(jù)表字段補全真的非常適合表結(jié)構(gòu)變動，ORM鏈?zhǔn)讲僮魈峁┝吮憬莸淖x寫操作。 如果需要提升讀寫性能，也可以通過純sql語句來替換Insertable、Updateable、Queryable操作。當(dāng)然，這個差距很多時候尤其是1萬級以內(nèi)、小中型CRUD是不明顯的，只有在10萬級以上才有細(xì)微體現(xiàn)。同時一般瓶頸在并發(fā)IO/鎖，而不是 ORM 轉(zhuǎn)換 下面是10萬條數(shù)據(jù).NET Framework472，EFCore與SqlSugar 加載、讀取、寫入操作的Benchmark性能分析情況：
如果是.NET版本比如10性能會更高，所以一般情況下可以不用考慮讀寫性能 kybs00/SqliteBenchmarkTestDemo 轉(zhuǎn)自https://www.cnblogs.com/kybs0/p/19638136 該文章在 2026/3/2 16:34:12 編輯過 |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
