znlgis 博客

GIS开发与技术分享

第十章：分表分库与多租户

目录

10.1 分表分库概述

10.1.1 为什么需要分表分库

当数据量达到一定规模时，单表单库会遇到性能瓶颈：

// 问题场景示例
// 订单表数据量：5000万条
// 查询性能：即使有索引，查询也需要几秒钟
var orders = db.Queryable<Order>()
    .Where(o => o.CreateTime >= DateTime.Now.AddDays(-30))
    .ToList(); // 慢查询

// 插入性能：高并发插入时锁竞争严重
for (int i = 0; i < 10000; i++)
{
    db.Insertable(new Order()).ExecuteCommand(); // 性能下降
}

分表分库的优势：

  • 提高查询性能：单表数据量减少
  • 提升并发能力：减少锁竞争
  • 便于维护：按业务逻辑分离数据
  • 支持数据归档：历史数据独立存储

10.1.2 分表分库策略

水平分表：按照一定规则将同一个表的数据分散到多个表中

order_2023_01, order_2023_02, order_2023_03...
order_user_1, order_user_2, order_user_3...

垂直分表：将表中某些字段拆分到另一个表

user (基础信息) + user_profile (扩展信息)
product (商品信息) + product_detail (详细描述)

垂直分库：按业务模块拆分到不同数据库

user_db (用户中心)
order_db (订单中心)
product_db (商品中心)

10.2 水平分表

10.2.1 SplitTable配置

SqlSugar使用SplitTable特性实现分表：

[SplitTable(SplitType.Month)]  // 按月分表
[SugarTable("order_{year}{month}{day}")]
public class Order
{
    [SugarColumn(IsPrimaryKey = true)]
    public long Id { get; set; }
    
    public string OrderNo { get; set; }
    
    [SplitField]  // 分表字段
    public DateTime CreateTime { get; set; }
    
    public decimal Amount { get; set; }
    public int UserId { get; set; }
}

// 使用分表
public class OrderService
{
    private readonly SqlSugarClient _db;
    
    public OrderService(SqlSugarClient db)
    {
        _db = db;
    }
    
    // 插入时自动路由到对应的表
    public void AddOrder(Order order)
    {
        _db.Insertable(order).SplitTable().ExecuteCommand();
    }
    
    // 查询时自动路由
    public List<Order> GetOrdersByTime(DateTime start, DateTime end)
    {
        return _db.Queryable<Order>()
            .SplitTable(tabs => tabs
                .Where(start, end)) // 指定时间范围
            .Where(o => o.CreateTime >= start && o.CreateTime <= end)
            .ToList();
    }
}

10.2.2 按时间分表

按时间分表是最常见的场景：

// 按月分表
[SplitTable(SplitType.Month)]
[SugarTable("log_{year}{month}")]
public class SystemLog
{
    [SugarColumn(IsPrimaryKey = true)]
    public long Id { get; set; }
    
    [SplitField]
    public DateTime CreateTime { get; set; }
    
    public string Message { get; set; }
    public string Level { get; set; }
}

// 按天分表
[SplitTable(SplitType.Day)]
[SugarTable("order_{year}{month}{day}")]
public class Order
{
    [SugarColumn(IsPrimaryKey = true)]
    public long Id { get; set; }
    
    [SplitField]
    public DateTime CreateTime { get; set; }
    
    public string OrderNo { get; set; }
}

// 按年分表
[SplitTable(SplitType.Year)]
[SugarTable("archive_{year}")]
public class ArchiveData
{
    [SugarColumn(IsPrimaryKey = true)]
    public long Id { get; set; }
    
    [SplitField]
    public DateTime CreateTime { get; set; }
    
    public string Content { get; set; }
}

// 实际使用
public class LogService
{
    private readonly SqlSugarClient _db;
    
    // 记录日志（自动创建表）
    public void Log(string message, string level)
    {
        var log = new SystemLog
        {
            CreateTime = DateTime.Now,
            Message = message,
            Level = level
        };
        
        _db.Insertable(log)
            .SplitTable()
            .ExecuteCommand();
    }
    
    // 查询指定月份的日志
    public List<SystemLog> GetLogsByMonth(int year, int month)
    {
        var start = new DateTime(year, month, 1);
        var end = start.AddMonths(1).AddDays(-1);
        
        return _db.Queryable<SystemLog>()
            .SplitTable(tabs => tabs.Where(start, end))
            .Where(l => l.CreateTime >= start && l.CreateTime <= end)
            .ToList();
    }
    
    // 查询最近7天的日志
    public List<SystemLog> GetRecentLogs()
    {
        var start = DateTime.Now.AddDays(-7);
        var end = DateTime.Now;
        
        return _db.Queryable<SystemLog>()
            .SplitTable(tabs => tabs.Where(start, end))
            .Where(l => l.CreateTime >= start && l.CreateTime <= end)
            .OrderBy(l => l.CreateTime, OrderByType.Desc)
            .ToList();
    }
}

10.2.3 按范围分表

按照数据范围或业务规则分表：

// 自定义分表规则
public class OrderByUserIdSplit
{
    // 根据用户ID分表（每10000个用户一张表）
    [SugarTable("order_user_{table}")]
    public class Order
    {
        [SugarColumn(IsPrimaryKey = true)]
        public long Id { get; set; }
        
        public int UserId { get; set; }
        public string OrderNo { get; set; }
        public DateTime CreateTime { get; set; }
    }
    
    // 实现分表逻辑
    public static string GetTableName(int userId)
    {
        int tableIndex = userId / 10000;
        return $"order_user_{tableIndex}";
    }
}

// 使用自定义分表
public class OrderRepository
{
    private readonly SqlSugarClient _db;
    
    // 插入订单
    public void AddOrder(Order order)
    {
        var tableName = OrderByUserIdSplit.GetTableName(order.UserId);
        
        _db.Insertable(order)
            .AS(tableName) // 指定表名
            .ExecuteCommand();
    }
    
    // 查询用户订单
    public List<Order> GetUserOrders(int userId)
    {
        var tableName = OrderByUserIdSplit.GetTableName(userId);
        
        return _db.Queryable<Order>()
            .AS(tableName)
            .Where(o => o.UserId == userId)
            .ToList();
    }
}

// 范围分表：按订单金额
public class OrderByAmountSplit
{
    // 小额订单表：0-100元
    // 中额订单表：100-1000元
    // 大额订单表：1000元以上
    
    public static string GetTableName(decimal amount)
    {
        if (amount < 100)
            return "order_small";
        else if (amount < 1000)
            return "order_medium";
        else
            return "order_large";
    }
}

10.3 自动路由

10.3.1 分表路由规则

SqlSugar支持灵活的路由规则：

// 配置全局分表策略
db.CurrentConnectionConfig.MoreSettings = new ConnMoreSettings
{
    // 自动创建分表
    IsAutoCreateTable = true,
    
    // 分表策略
    SplitTableFunc = (entityInfo) =>
    {
        // 自定义分表逻辑
        if (entityInfo.Type == typeof(Order))
        {
            // 动态返回表名列表
            var tables = new List<string>();
            for (int i = 1; i <= 12; i++)
            {
                tables.Add($"order_2024{i:00}");
            }
            return tables;
        }
        return null;
    }
};

// 高级路由配置
public class AdvancedSplitTableService
{
    private readonly SqlSugarClient _db;
    
    // 多条件路由
    public List<Order> GetOrders(OrderQueryDto query)
    {
        var queryable = _db.Queryable<Order>();
        
        // 根据查询条件确定需要查询的表
        if (query.StartTime.HasValue && query.EndTime.HasValue)
        {
            // 按时间范围路由
            queryable = queryable.SplitTable(tabs => 
                tabs.Where(query.StartTime.Value, query.EndTime.Value));
        }
        else if (query.UserId.HasValue)
        {
            // 按用户ID路由
            var tableName = GetTableNameByUserId(query.UserId.Value);
            queryable = queryable.AS(tableName);
        }
        else
        {
            // 查询所有分表
            queryable = queryable.SplitTable();
        }
        
        return queryable
            .WhereIF(query.UserId.HasValue, o => o.UserId == query.UserId)
            .WhereIF(query.StartTime.HasValue, o => o.CreateTime >= query.StartTime)
            .WhereIF(query.EndTime.HasValue, o => o.CreateTime <= query.EndTime)
            .ToList();
    }
    
    private string GetTableNameByUserId(int userId)
    {
        return $"order_user_{userId / 10000}";
    }
}

10.3.2 动态表名

在运行时动态指定表名：

// 动态表名查询
public class DynamicTableService
{
    private readonly SqlSugarClient _db;
    
    // 动态查询指定表
    public List<T> QueryTable<T>(string tableName) where T : class, new()
    {
        return _db.Queryable<T>()
            .AS(tableName)
            .ToList();
    }
    
    // 动态插入到指定表
    public int InsertToTable<T>(T entity, string tableName) where T : class, new()
    {
        return _db.Insertable(entity)
            .AS(tableName)
            .ExecuteCommand();
    }
    
    // 批量查询多个分表
    public List<Order> QueryMultipleTables(List<string> tableNames)
    {
        var allOrders = new List<Order>();
        
        foreach (var tableName in tableNames)
        {
            var orders = _db.Queryable<Order>()
                .AS(tableName)
                .ToList();
            allOrders.AddRange(orders);
        }
        
        return allOrders;
    }
    
    // 使用Union查询多个分表
    public List<Order> QueryWithUnion(DateTime start, DateTime end)
    {
        // 获取需要查询的表名
        var tableNames = GetTableNames(start, end);
        
        if (tableNames.Count == 0)
            return new List<Order>();
        
        // 构建Union查询
        var queryable = _db.Queryable<Order>().AS(tableNames[0]);
        
        for (int i = 1; i < tableNames.Count; i++)
        {
            queryable = queryable.Union(
                _db.Queryable<Order>().AS(tableNames[i])
            );
        }
        
        return queryable
            .Where(o => o.CreateTime >= start && o.CreateTime <= end)
            .ToList();
    }
    
    private List<string> GetTableNames(DateTime start, DateTime end)
    {
        var tables = new List<string>();
        var current = new DateTime(start.Year, start.Month, 1);
        var endMonth = new DateTime(end.Year, end.Month, 1);
        
        while (current <= endMonth)
        {
            tables.Add($"order_{current:yyyyMM}");
            current = current.AddMonths(1);
        }
        
        return tables;
    }
}

10.4 垂直分库

10.4.1 多库配置

配置多个数据库连接：

// 配置多个数据库
public class MultiDatabaseConfig
{
    public static SqlSugarScope CreateMultiDatabase()
    {
        var configs = new List<ConnectionConfig>
        {
            // 用户数据库
            new ConnectionConfig
            {
                ConfigId = "UserDb",
                ConnectionString = "server=.;database=UserDB;uid=sa;pwd=123",
                DbType = DbType.SqlServer,
                IsAutoCloseConnection = true
            },
            // 订单数据库
            new ConnectionConfig
            {
                ConfigId = "OrderDb",
                ConnectionString = "server=.;database=OrderDB;uid=sa;pwd=123",
                DbType = DbType.SqlServer,
                IsAutoCloseConnection = true
            },
            // 商品数据库
            new ConnectionConfig
            {
                ConfigId = "ProductDb",
                ConnectionString = "server=.;database=ProductDB;uid=sa;pwd=123",
                DbType = DbType.SqlServer,
                IsAutoCloseConnection = true
            }
        };
        
        return new SqlSugarScope(configs);
    }
}

// 使用多数据库
public class MultiDbService
{
    private readonly SqlSugarScope _db;
    
    public MultiDbService()
    {
        _db = MultiDatabaseConfig.CreateMultiDatabase();
    }
    
    // 从用户库查询
    public User GetUser(int userId)
    {
        return _db.GetConnection("UserDb")
            .Queryable<User>()
            .First(u => u.Id == userId);
    }
    
    // 从订单库查询
    public List<Order> GetOrders(int userId)
    {
        return _db.GetConnection("OrderDb")
            .Queryable<Order>()
            .Where(o => o.UserId == userId)
            .ToList();
    }
    
    // 从商品库查询
    public Product GetProduct(int productId)
    {
        return _db.GetConnection("ProductDb")
            .Queryable<Product>()
            .First(p => p.Id == productId);
    }
}

10.4.2 跨库查询

实现跨库关联查询：

public class CrossDatabaseQuery
{
    private readonly SqlSugarScope _db;
    
    // 跨库查询：查询订单及用户信息
    public List<OrderWithUserDto> GetOrdersWithUser()
    {
        // 方式1：分别查询后内存Join
        var orders = _db.GetConnection("OrderDb")
            .Queryable<Order>()
            .ToList();
        
        var userIds = orders.Select(o => o.UserId).Distinct().ToList();
        
        var users = _db.GetConnection("UserDb")
            .Queryable<User>()
            .Where(u => userIds.Contains(u.Id))
            .ToList();
        
        // 内存Join
        var result = from o in orders
                     join u in users on o.UserId equals u.Id
                     select new OrderWithUserDto
                     {
                         OrderNo = o.OrderNo,
                         Amount = o.Amount,
                         UserName = u.Name,
                         UserEmail = u.Email
                     };
        
        return result.ToList();
    }
    
    // 跨库事务
    public bool CreateOrderWithUserUpdate(Order order, User user)
    {
        try
        {
            // 使用分布式事务
            _db.BeginTran();
            
            // 更新用户信息
            _db.GetConnection("UserDb")
                .Updateable(user)
                .ExecuteCommand();
            
            // 创建订单
            _db.GetConnection("OrderDb")
                .Insertable(order)
                .ExecuteCommand();
            
            _db.CommitTran();
            return true;
        }
        catch
        {
            _db.RollbackTran();
            return false;
        }
    }
}

10.5 多租户架构

10.5.1 独立数据库模式

每个租户使用独立的数据库：

public class TenantConfig
{
    public int TenantId { get; set; }
    public string TenantName { get; set; }
    public string ConnectionString { get; set; }
}

public class TenantDatabaseProvider
{
    private readonly SqlSugarScope _scope;
    private readonly List<TenantConfig> _tenants;
    
    public TenantDatabaseProvider()
    {
        // 加载租户配置
        _tenants = LoadTenantConfigs();
        
        // 创建多租户数据库连接
        var configs = _tenants.Select(t => new ConnectionConfig
        {
            ConfigId = t.TenantId.ToString(),
            ConnectionString = t.ConnectionString,
            DbType = DbType.SqlServer,
            IsAutoCloseConnection = true
        }).ToList();
        
        _scope = new SqlSugarScope(configs);
    }
    
    public SqlSugarClient GetTenantDb(int tenantId)
    {
        return _scope.GetConnection(tenantId.ToString());
    }
    
    private List<TenantConfig> LoadTenantConfigs()
    {
        // 从配置文件或数据库加载租户配置
        return new List<TenantConfig>
        {
            new TenantConfig 
            { 
                TenantId = 1, 
                TenantName = "租户A",
                ConnectionString = "server=.;database=TenantA_DB;uid=sa;pwd=123"
            },
            new TenantConfig 
            { 
                TenantId = 2, 
                TenantName = "租户B",
                ConnectionString = "server=.;database=TenantB_DB;uid=sa;pwd=123"
            }
        };
    }
}

// 使用
public class TenantService
{
    private readonly TenantDatabaseProvider _provider;
    
    public List<Order> GetTenantOrders(int tenantId, int userId)
    {
        var db = _provider.GetTenantDb(tenantId);
        return db.Queryable<Order>()
            .Where(o => o.UserId == userId)
            .ToList();
    }
}

10.5.2 共享数据库模式

多个租户共享同一个数据库，通过TenantId区分：

// 实体类包含TenantId
public class Order
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    
    public int TenantId { get; set; } // 租户ID
    public string OrderNo { get; set; }
    public int UserId { get; set; }
    public decimal Amount { get; set; }
}

// 租户过滤器
public class TenantFilter
{
    private readonly SqlSugarClient _db;
    private int _currentTenantId;
    
    public TenantFilter(SqlSugarClient db)
    {
        _db = db;
        ConfigureGlobalFilter();
    }
    
    public void SetTenant(int tenantId)
    {
        _currentTenantId = tenantId;
    }
    
    private void ConfigureGlobalFilter()
    {
        // 配置全局查询过滤器
        _db.QueryFilter.Add(new TableFilterItem<Order>(
            it => it.TenantId == _currentTenantId));
        
        _db.QueryFilter.Add(new TableFilterItem<User>(
            it => it.TenantId == _currentTenantId));
        
        _db.QueryFilter.Add(new TableFilterItem<Product>(
            it => it.TenantId == _currentTenantId));
    }
}

// 使用租户过滤器
public class OrderService
{
    private readonly SqlSugarClient _db;
    private readonly TenantFilter _tenantFilter;
    
    public OrderService(SqlSugarClient db, TenantFilter tenantFilter)
    {
        _db = db;
        _tenantFilter = tenantFilter;
    }
    
    public List<Order> GetOrders(int tenantId)
    {
        // 设置当前租户
        _tenantFilter.SetTenant(tenantId);
        
        // 查询时自动过滤当前租户的数据
        return _db.Queryable<Order>().ToList();
    }
    
    public void AddOrder(int tenantId, Order order)
    {
        // 自动设置租户ID
        order.TenantId = tenantId;
        _db.Insertable(order).ExecuteCommand();
    }
}

10.5.3 租户隔离

实现完整的租户隔离机制：

public class TenantContext
{
    private static AsyncLocal<int> _tenantId = new AsyncLocal<int>();
    
    public static int CurrentTenantId
    {
        get => _tenantId.Value;
        set => _tenantId.Value = value;
    }
}

// AOP拦截器自动设置租户
public class TenantInterceptor
{
    public static void Configure(SqlSugarClient db)
    {
        // 插入时自动设置TenantId
        db.Aop.DataExecuting = (oldValue, entityInfo) =>
        {
            if (entityInfo.PropertyName == "TenantId")
            {
                entityInfo.SetValue(TenantContext.CurrentTenantId);
            }
        };
        
        // 查询时自动过滤
        db.QueryFilter.AddTableFilter<ITenant>(it => 
            it.TenantId == TenantContext.CurrentTenantId);
    }
}

// 租户接口
public interface ITenant
{
    int TenantId { get; set; }
}

// 实体实现租户接口
public class Order : ITenant
{
    public int Id { get; set; }
    public int TenantId { get; set; }
    public string OrderNo { get; set; }
}

// ASP.NET Core中间件
public class TenantMiddleware
{
    private readonly RequestDelegate _next;
    
    public TenantMiddleware(RequestDelegate next)
    {
        _next = next;
    }
    
    public async Task InvokeAsync(HttpContext context)
    {
        // 从请求头或Token中获取租户ID
        var tenantId = GetTenantIdFromRequest(context);
        TenantContext.CurrentTenantId = tenantId;
        
        await _next(context);
    }
    
    private int GetTenantIdFromRequest(HttpContext context)
    {
        // 从Header获取
        if (context.Request.Headers.TryGetValue("X-Tenant-Id", out var headerValue))
        {
            return int.Parse(headerValue);
        }
        
        // 从JWT Token获取
        var tenantClaim = context.User.FindFirst("TenantId");
        if (tenantClaim != null)
        {
            return int.Parse(tenantClaim.Value);
        }
        
        return 0;
    }
}

10.6 扩展性策略

// 数据迁移策略
public class DataMigrationService
{
    private readonly SqlSugarClient _sourceDb;
    private readonly SqlSugarClient _targetDb;
    
    // 数据迁移到新的分表
    public void MigrateToNewTable(DateTime start, DateTime end)
    {
        var sourceTableName = $"order_{start:yyyyMM}";
        var targetTableName = $"order_{end:yyyyMM}";
        
        // 创建新表
        _targetDb.DbMaintenance.CreateTable(targetTableName, 
            _sourceDb.DbMaintenance.GetTableInfoList(sourceTableName));
        
        // 批量迁移数据
        var pageSize = 10000;
        var pageIndex = 1;
        int totalCount = 0;
        
        do
        {
            var data = _sourceDb.Queryable<Order>()
                .AS(sourceTableName)
                .Where(o => o.CreateTime >= start && o.CreateTime < end)
                .ToPageList(pageIndex, pageSize, ref totalCount);
            
            if (data.Count > 0)
            {
                _targetDb.Insertable(data)
                    .AS(targetTableName)
                    .ExecuteCommand();
            }
            
            pageIndex++;
        } while ((pageIndex - 1) * pageSize < totalCount);
    }
    
    // 历史数据归档
    public void ArchiveHistoricalData(DateTime beforeDate)
    {
        var archiveDb = new SqlSugarClient(new ConnectionConfig
        {
            ConnectionString = "Archive Database Connection",
            DbType = DbType.SqlServer
        });
        
        // 查询需要归档的数据
        var data = _sourceDb.Queryable<Order>()
            .Where(o => o.CreateTime < beforeDate)
            .ToList();
        
        // 插入到归档库
        archiveDb.Insertable(data).ExecuteCommand();
        
        // 删除原数据
        _sourceDb.Deleteable<Order>()
            .Where(o => o.CreateTime < beforeDate)
            .ExecuteCommand();
    }
}

// 自动扩容策略
public class AutoScalingService
{
    // 监控表大小，自动分表
    public void MonitorAndSplit()
    {
        var tables = GetAllTables();
        
        foreach (var table in tables)
        {
            var rowCount = GetTableRowCount(table);
            var tableSize = GetTableSize(table);
            
            // 当记录数超过1000万或表大小超过10GB时分表
            if (rowCount > 10000000 || tableSize > 10 * 1024 * 1024 * 1024)
            {
                SplitTable(table);
            }
        }
    }
    
    private void SplitTable(string tableName)
    {
        // 实现分表逻辑
        Console.WriteLine($"表 {tableName} 需要分表");
    }
}

10.7 本章小结

本章深入讲解了SqlSugar的分表分库和多租户架构：

  1. 分表分库概念：理解了为什么需要分表分库，以及水平分表、垂直分表、垂直分库的区别
  2. 水平分表：掌握了使用SplitTable特性实现按时间、按范围的分表策略
  3. 自动路由：学会了配置分表路由规则和动态表名的使用
  4. 垂直分库：了解了如何配置多数据库连接和实现跨库查询
  5. 多租户架构：掌握了独立数据库和共享数据库两种多租户模式
  6. 租户隔离：实现了完整的租户数据隔离和安全机制
  7. 扩展性策略：学习了数据迁移、归档和自动扩容的实现方法

通过本章的学习，您应该能够设计和实现支持大规模数据和高并发的分布式数据库架构。在下一章中，我们将学习SqlSugar的AOP与日志监控功能，这对于系统的可维护性和问题排查至关重要。

↑