Skip to content

Sharding

shuxin edited this page Aug 1, 2024 · 10 revisions

分库分表

Chloe.ORM 分片已实现分库分表、分页、聚合、分组聚合等功能,并支持多个字段组合分片以及多字段路由,相关使用说明:https://github.com/shuxinqin/Chloe/issues/330 ,下面是一个简单的使用例子。

1. 创建字段路由规则,实现 IRoutingStrategy 接口:

你的分片算法要写在此类里。下面是一个以创建时间 CreateTime 字段为分片键,按年分库月分表的路由规则例子。

/// <summary>
/// CreateTime 字段路由规则
/// </summary>
public class CreateTimeRoutingStrategy : RoutingStrategy<DateTime>, IRoutingStrategy
{
    public CreateTimeRoutingStrategy(YourShardingRoute route) : base(route)
    {

    }

    /// <summary>
    /// 当查询如 query.Where(a => a.CreateTime == createTime) 时,从所有分表中筛选出满足条件的分表
    /// </summary>
    /// <param name="createTime"></param>
    /// <returns></returns>
    public override IEnumerable<RouteTable> ForEqual(DateTime createTime)
    {
        return this.Route.GetTables().Where(a => (a.DataSource as OrderRouteDataSource).Year == createTime.Year && (a as OrderRouteTable).Month == createTime.Month);
    }

    /// <summary>
    /// 当查询如 query.Where(a => a.CreateTime != createTime) 时,从所有分表中筛选出满足条件的分表
    /// </summary>
    /// <param name="createTime"></param>
    /// <returns></returns>
    public override IEnumerable<RouteTable> ForNotEqual(DateTime createTime)
    {
        return this.Route.GetTables();
    }

    /// <summary>
    /// 当查询如 query.Where(a => a.CreateTime > createTime) 时,从所有分表中筛选出满足条件的分表
    /// </summary>
    /// <param name="createTime"></param>
    /// <returns></returns>
    public override IEnumerable<RouteTable> ForGreaterThan(DateTime createTime)
    {
        return this.Route.GetTables().Where(a => (a.DataSource as OrderRouteDataSource).Year > createTime.Year || ((a.DataSource as OrderRouteDataSource).Year == createTime.Year && (a as OrderRouteTable).Month >= createTime.Month));
    }

    /// <summary>
    /// 当查询如 query.Where(a => a.CreateTime >= createTime) 时,从所有分表中筛选出满足条件的分表
    /// </summary>
    /// <param name="createTime"></param>
    /// <returns></returns>
    public override IEnumerable<RouteTable> ForGreaterThanOrEqual(DateTime createTime)
    {
        return this.Route.GetTables().Where(a => (a.DataSource as OrderRouteDataSource).Year > createTime.Year || ((a.DataSource as OrderRouteDataSource).Year == createTime.Year && (a as OrderRouteTable).Month >= createTime.Month));
    }

    /// <summary>
    /// 当查询如 query.Where(a => a.CreateTime &lt; createTime) 时,从所有分表中筛选出满足条件的分表
    /// </summary>
    /// <param name="createTime"></param>
    /// <returns></returns>
    public override IEnumerable<RouteTable> ForLessThan(DateTime createTime)
    {
        return this.Route.GetTables().Where(a => (a.DataSource as OrderRouteDataSource).Year < createTime.Year || ((a.DataSource as OrderRouteDataSource).Year == createTime.Year && (a as OrderRouteTable).Month <= createTime.Month));
    }

    /// <summary>
    /// 当查询如 query.Where(a => a.CreateTime &lt;= createTime) 时,从所有分表中筛选出满足条件的分表
    /// </summary>
    /// <param name="createTime"></param>
    /// <returns></returns>
    public override IEnumerable<RouteTable> ForLessThanOrEqual(DateTime createTime)
    {
        return this.Route.GetTables().Where(a => (a.DataSource as OrderRouteDataSource).Year < createTime.Year || ((a.DataSource as OrderRouteDataSource).Year == createTime.Year && (a as OrderRouteTable).Month <= createTime.Month));
    }
}

2. 创建分片路由,实现 IShardingRoute 接口:

/// <summary>
/// 分片路由。
/// </summary>
public class YourShardingRoute : IShardingRoute
{
    //CreateTime 的路由规则
    IRoutingStrategy _createTimeRoutingStrategy;

    public YourShardingRoute()
    {
        //在这里创建 CreateTime 的路由规则
        this._createTimeRoutingStrategy = new CreateTimeRoutingStrategy(this);
    }

    /// <summary>
    /// 获取所有的分片表。
    /// </summary>
    /// <returns></returns>
    public IEnumerable<RouteTable> GetTables()
    {
        //todo:返回所有分表,具体如何构建 RouteTable 对象,请参考 demo
        //注:框架不具有自动建库和创建分表功能,需要您提前将库和分表建好!!!
    }

    /// <summary>
    /// 根据实体属性获取相应的路由规则,如果传入的 member 没有路由规则,返回 null 即可。
    /// </summary>
    /// <param name="member">实体属性</param>
    /// <returns>如果传入的属性不是分片属性,直接返回 null 即可</returns>
    public IRoutingStrategy GetStrategy(MemberInfo member)
    {
        //todo:返回路由策略,如果传入的属性不是分片属性,直接返回 null 即可
        if(member.Name == "CreateTime")
        {
            //在这里使用 CreateTime 的路由规则
            return this._createTimeRoutingStrategy;
        }

        //其他字段,直接返回 null 即可
        return null;
    }

    /// <summary>
    /// 根据排序字段对路由表重排。
    /// </summary>
    /// <param name="tables"></param>
    /// <param name="orderings"></param>
    /// <returns></returns>
    public SortResult SortTables(List<RouteTable> tables, List<Ordering> orderings)
    {
        /*
          假设根据时间分表(CreateTime),查询时又根据 CreateTime 排序(如 query.OrderBy(a => a.CreateTime)),因为数据储存是按时间段有序存储的,所以针对这种情况下的查询可以优化一下避免无谓的查询,
          因此你可以实现 IShardingRoute.SortTables() 方法,对定位到的表进行一次重排,以提升查询效率(可以看看这篇文章,说得很好 https://www.cnblogs.com/xuejiaming/p/15237878.html) 。
          ps:如果不需要重排,IShardingRoute.SortTables() 实现里直接返回传入的参数即可。
         */
    }
}

具体实现参考 https://github.com/shuxinqin/Chloe/blob/master/src/ChloeDemo/Sharding/OrderShardingRoute.cs#L60

3. 将你实现的分片路由注册进框架:

ShardingConfigBuilder<YourShardingEntity> shardingConfigBuilder = new ShardingConfigBuilder<YourShardingEntity>();
shardingConfigBuilder.HasShardingKey(a => a.CreateTime);     //配置分片字段
shardingConfigBuilder.HasRoute(new YourShardingRoute()); //设置分片路由

4. 创建 DbContext:

DbContext dbContext = new MsSqlContext("...");  //MySqlContext, OracleContext...都行

//如果你的DbContext只是操作分片表,也可以创建一个虚的 DbContext
//DbContext dbContext = new DbContext();

//然后可以进行增删查改了
dbContext.Query<YourShardingEntity>().Where(...).ToList();
dbContext.Insert<YourShardingEntity>(new YourShardingEntity(){ ... });
dbContext.Update<YourShardingEntity>(new YourShardingEntity(){ ... });
dbContext.Delete<YourShardingEntity>(...);

使用案例请参考 github 上的 demo sharding