.net程序员需要知道的数据库小知识

《.NET程序员需要知道的数据库小知识》

在.NET开发中，数据库作为数据持久化的核心组件，其性能、安全性和设计合理性直接影响应用的整体质量。本文将从基础概念、优化技巧、安全实践和高级特性四个维度，为.NET程序员梳理数据库开发中必须掌握的关键知识。

一、数据库基础概念

1.1 关系型数据库与非关系型数据库

关系型数据库（如SQL Server、MySQL）以表格形式存储数据，支持ACID事务，适合结构化数据场景。非关系型数据库（如MongoDB、Redis）则采用键值对、文档或图结构，适合高并发读写和半结构化数据。

在.NET生态中，Entity Framework Core（EF Core）默认支持SQL Server、MySQL等关系型数据库，而MongoDB.Driver等库可操作NoSQL数据库。

1.2 连接字符串配置

连接字符串是应用与数据库通信的桥梁，需包含服务器地址、数据库名、认证信息等。示例：

// SQL Server连接字符串
"Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"

// MySQL连接字符串（需安装MySql.Data包）
"Server=localhost;Database=testdb;Uid=root;Pwd=123456;"

在.NET中，可通过IConfiguration注入连接字符串：

// appsettings.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=...;"
  }
}

// Startup.cs中配置
services.AddDbContext(options =>
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

二、性能优化技巧

2.1 索引优化

索引是提升查询性能的关键，但需权衡读写开销。常见索引类型包括：

• 聚集索引：决定表中数据的物理存储顺序，每表仅一个
• 非聚集索引：独立于数据存储，可创建多个
• 复合索引：多列组合索引，需遵循最左前缀原则

EF Core中可通过Fluent API创建索引：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity()
        .HasIndex(o => new { o.CustomerId, o.OrderDate })
        .HasName("IX_CustomerId_OrderDate");
}

2.2 查询优化

避免SELECT *，仅查询必要字段。使用AsNoTracking()提升只读查询性能：

var products = dbContext.Products
    .AsNoTracking()
    .Where(p => p.Price > 100)
    .ToList();

对于复杂查询，考虑使用存储过程或原生SQL：

var sql = "SELECT * FROM Products WHERE CategoryId = @categoryId";
var products = dbContext.Products
    .FromSqlRaw(sql, new SqlParameter("@categoryId", 5))
    .ToList();

2.3 批量操作

批量插入时，使用SqlBulkCopy（SQL Server）或EF Core的ExecuteUpdate/ExecuteDelete（.NET 8+）：

// 使用SqlBulkCopy
using var connection = new SqlConnection(connectionString);
connection.Open();
using var bulkCopy = new SqlBulkCopy(connection);
bulkCopy.DestinationTableName = "Products";
await bulkCopy.WriteToServerAsync(dataTable);

三、安全实践

3.1 参数化查询防SQL注入

永远不要拼接SQL字符串，始终使用参数化查询：

// 错误方式（易受SQL注入攻击）
var query = $"SELECT * FROM Users WHERE Username = '{username}'";

// 正确方式
var user = dbContext.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Username = @p0", username)
    .FirstOrDefault();

3.2 敏感数据加密

对密码、身份证号等敏感字段，应在应用层加密后存储。使用ProtectedData类（Windows）或Azure Key Vault：

// 使用DPAPI加密
byte[] encrypted = ProtectedData.Protect(plainTextBytes, null, DataProtectionScope.CurrentUser);
byte[] decrypted = ProtectedData.Unprotect(encrypted, null, DataProtectionScope.CurrentUser);

3.3 最小权限原则

数据库账号应仅授予必要权限，避免使用sa或root账号。示例角色分配：

• 应用账号：仅允许SELECT、INSERT、UPDATE、DELETE
• 报表账号：仅允许SELECT
• 管理员账号：允许DDL操作

四、高级特性

4.1 事务管理

EF Core默认开启事务，也可手动控制：

using var transaction = dbContext.Database.BeginTransaction();
try
{
    dbContext.Orders.Add(new Order { ... });
    dbContext.SaveChanges();
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

分布式事务需使用TransactionScope（需安装System.Transactions.Distributed包）：

using var scope = new TransactionScope();
try
{
    // 操作多个数据库
    scope.Complete();
}
catch
{
    // 自动回滚
}

4.2 并发控制

EF Core支持乐观并发控制，通过[ConcurrencyCheck]或[Timestamp]属性实现：

public class Product
{
    public int Id { get; set; }
    [ConcurrencyCheck]
    public string Name { get; set; }
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

// 更新时自动验证
try
{
    dbContext.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
    // 处理并发冲突
}

4.3 数据库迁移

EF Core的迁移功能可自动生成数据库变更脚本：

# 添加迁移
dotnet ef migrations add AddProductTable

# 应用迁移
dotnet ef database update

# 生成SQL脚本
dotnet ef migrations script --output script.sql

五、常见问题解决

5.1 连接超时

解决方案：

• 检查网络连通性
• 增加Connection Timeout值（默认15秒）
• 优化慢查询

5.2 死锁处理

SQL Server死锁日志分析：

-- 启用跟踪标志1222记录死锁信息
DBCC TRACEON (1222, -1)

-- 在SQL Server错误日志中查找死锁信息

.NET中可通过重试机制缓解：

var policy = Policy
    .Handle(ex => ex.Number == 1205) // 死锁错误号
    .WaitAndRetry(3, retryAttempt => TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)));

policy.Execute(() => 
{
    // 数据库操作
});

5.3 内存泄漏

常见原因：

• 未释放DbContext（应使用依赖注入）
• 未关闭的SqlConnection
• 大量数据加载到内存

解决方案：

// 使用using确保释放
using (var context = new AppDbContext())
{
    var data = context.Products.Take(1000).ToList();
}

// 或依赖注入（推荐）
public class MyService
{
    private readonly AppDbContext _context;
    
    public MyService(AppDbContext context)
    {
        _context = context;
    }
}

关键词：.NET数据库开发、EF Core、SQL优化、索引设计、参数化查询、事务管理、并发控制、数据库迁移、死锁处理、内存泄漏

简介：本文系统梳理了.NET程序员在数据库开发中需要掌握的核心知识，涵盖关系型与非关系型数据库基础、连接字符串配置、索引与查询优化、批量操作技巧、SQL注入防护、敏感数据加密、事务管理、并发控制、数据库迁移及常见问题解决方案，通过代码示例和最佳实践帮助开发者提升数据库应用的质量和性能。