水平分库时,差不多都会使用一些规则去定义自动生成表通过查询反馈出以下数据
TableName | Count |
TableName1 | 10 |
TableName2 | 10 |
TableName3 | 10 |
public class TableDataCount
{
public int Count { get; set; }
public string TableName { get; set; }
}
在分页之时,切不可使用union all扫描全部表,而是只扫描你条件相关的几张表比如,查询PageIndex=2,PageSize=9时,我们只需要TableName1 与TableName2参与,取TableName1的第10行,取TableName2的1-8行,以下是
private BlockBase WetlandPageRule(int pageIndex, int pageSize, List<TableDataCount> tdcList)
{
int startRow = pageSize * (pageIndex - 1);
int endRow = startRow + pageSize;
int blockStart = 0;
int blockStartNum = 0;
int blockEnd = 0;
int blockEndNum = 0;
int startAccumulation = 0;
//起始点
for (int i = 0; i < tdcList.Count; i++)
{
startAccumulation += tdcList[i].Count;
if (startAccumulation - startRow >= 0)
{
blockStartNum = tdcList[i].Count - (startAccumulation - startRow);
blockStart = i;
break;
}
}
int endAccumulation = 0;
//结束点
for (int i = 0; i < tdcList.Count; i++)
{
endAccumulation += tdcList[i].Count;
if (endAccumulation - endRow >= 0 || i== tdcList.Count - 1)
{
var tempEndNum = tdcList[i].Count - (endAccumulation - endRow);
blockEndNum = tempEndNum > tdcList[i].Count ? tdcList[i].Count : tempEndNum;
blockEnd = i;
break;
}
}
return new BlockBase()
{
BlockEndNum = blockEndNum,
BlockEnd = blockEnd,
BlockStartNum = blockStartNum,
BlockStart = blockStart,
};
}
注:需创建BlockBase.cs