億萬級分庫分表後如何進行跨表分頁查詢

Ron.Liang發表於2023-03-29

前言

在常規的應用系統開發中，很少會涉及到需要對資料進行分庫或者分表的操作，多數情況下，我們習慣使用ORM帶來的便利，且使用連線查詢是一種高效率的開發方式，就算涉及到分表的場景，很多時候也都可以使用ORM自帶的分表規則來解決問題。

比如在電商場景中，使用者和訂單是屬於重點增量的資料，通常情況下，或者按使用者編號取模或者按訂單編號取模進行分表，按便利性來區分，可以使用按使用者編號分表解決後續跨表分頁查詢問題，這也是推薦的方式之一。

據說淘寶採用的是雙寫訂單，即客戶和商家各自一套冗餘資料庫，再指向訂單表，這樣做可以規避資源搶奪的問題。

分表後查詢的多種方法

全域性表查詢

顧名思義，全域性查詢就是將分表後的資料主鍵再集中儲存到一張表中，由於全域性表只儲存很簡單的編號資訊，查詢效率相對較高，但是在資料持續增長的情況下，壓力也越來越大。

禁止跳頁查詢

禁止跳頁查詢在移動網際網路中廣泛被應用，這種方法的原理是在查詢中摒棄捨棄傳統的Page，轉而使用一個timestamp時間戳來內碼表碼，下一頁的查詢總是在上一頁的最後一條記錄的時間戳之後，當客戶端拉取不到任何資料的時候，即可停止分頁。

這種方法帶的一個問題就是不允許進行跳轉分頁，並且會帶來冗餘查詢的問題，比如需要查詢多張表後才得到PageSize需要的資料量，只能按部就班的往下查詢，不能進行並行查詢。特別緻命的是，此方法還將帶來重複資料的問題。對資料精度要求不高的場景可以採用。

按日期的二次查詢法

按日期的二次查詢法號稱可以解決分頁帶來的效能和精度問題，具體原理為，先將分頁跳過的資料量平均分佈到所有表中，如 Page=10,PageSize=50，如果有5個分表，則SQL語句：page=page/5,LIMIT 2,10;分別對5張表進行查詢，得到5個結果集，此時，5個結果集裡面分別有10條資料，其中下標0和rn-1的結果分別是當前結果集中的最小和最大時間戳(maxTimestamp)，透過比較5張表的返回記錄得到一個最小的時間戳 minTimestamp，再將這個最小的時間戳帶入SQL條件進行二次查詢，SQL程式碼

SELECT * FROM TABLE_NAME WHERE Timestamp BETWEEN @minTimestamp AND @maxTimestamp ORDER BY Timestamp

透過上面的程式碼，可以從資料庫中得到一個完全的結果集，然後在記憶體中將5個結果集合並排序，取分頁資料即可。看起來無懈可擊，完美解決了上面兩種分頁查詢引起的問題。實際上我個人認為，這裡面還是有一些需要注意的地方，比如由於分表規則的問題導致第一次查詢的表比較多（可能幾千張表），又或者在二次查詢中，某個區間的資料比較大，最後就是在記憶體中合併結果集也會造成效能問題。
這種查詢方法還是解決了精度的問題，也部分解決了效能問題，特別是在取模分表的場景，資料隨機性比較大的情況下，還是非常有用的。

大資料整合法

當資料量達到一定程度的時候，可以考慮上ELK或者其它大資料套件，可以很好的解決分頁帶的影響。

NewSql法

如果有條件，可以遷移資料庫到NewSql型別的資料庫上，NewSql資料庫屬於分散式資料庫，既有關聯式資料庫的優點又可以無限擴表，通常還支援關聯式資料庫間的無障礙遷移，比如國產的TiDB資料庫等。

有序的二次查詢法

有序的二次查詢法是基於上面的按日期的二次查詢法發展而來，這種方法目前還處於測試階段，具體做法是將資料按天進行分表，這樣就可以確保資料塊是連續的，以查詢最近17天的分頁資料為例，先查詢出所有表的總行數，這裡使用 COUNT(*) ，Mysql 會最佳化為information_schema.TABLES.TABLE_ROWS 索引查詢提高查詢效率，不用擔心效能問題，下面列出詳細的測試步驟。

建立分頁實體

public class PageEntity
{
    /// <summary>
    /// 跳過的記錄數
    /// </summary>
    public long Skip { get; set; }
    /// <summary>
    /// 選取的記錄數
    /// </summary>
    public long Take { get; set; }
    /// <summary>
    /// 總行數
    /// </summary>
    public long Total { get; set; }
    /// <summary>
    /// 表名
    /// </summary>
    public string TableName { get; set; }
}

定義分頁演算法類

public class PageDataService
{
    ...
}

初始化表

在 PageDataService 類中使用記憶體表模擬資料庫表，主要模擬資料分頁的情況，所以每個表的資料量都很小，方便人肉計算和跳頁

private readonly static List<PageEntity> entitys = new List<PageEntity>()
{
    new PageEntity{ Total=12,TableName="230301" },
    new PageEntity{ Total=3,TableName="230302" },
    new PageEntity{ Total=4,TableName="230303" },
    new PageEntity{ Total=1,TableName="230304" },
    new PageEntity{ Total=1,TableName="230305" },
    new PageEntity{ Total=7,TableName="230306" },
    new PageEntity{ Total=2,TableName="230307" },
    new PageEntity{ Total=11,TableName="230308" },
    new PageEntity{ Total=41,TableName="230309" },
    new PageEntity{ Total=25,TableName="230310" },
    new PageEntity{ Total=33,TableName="230311" },
    new PageEntity{ Total=8,TableName="230312" },
    new PageEntity{ Total=3,TableName="230313" },
    new PageEntity{ Total=0,TableName="230314" },
    new PageEntity{ Total=17,TableName="230315" },
    new PageEntity{ Total=88,TableName="230316" },
    new PageEntity{ Total=2,TableName="230317" }
};

分頁演算法

public static List<PageEntity> Pagination(int page, int pageSize)
{
    long preBlock = 0;
    int currentPage = page;
    long currentPageSize = pageSize;   
    List<PageEntity> results = new List<PageEntity>();

    foreach (var item in entitys)
    {
        var skip = ((currentPage - 1) * currentPageSize) + preBlock;
        var remainder = item.Total - skip;
        if (remainder > 0)
        {
            item.Skip = skip;
            item.Take = currentPageSize;
            if (remainder >= currentPageSize)
            {
                results.Add(item);
                break;
            }
            else
            {
                currentPageSize = currentPageSize - remainder;
                item.Take = remainder;
                currentPage = 1;
                preBlock = 0;
                results.Add(item);
            }
        }
        else
        {
            preBlock = Math.Abs(remainder);
            currentPage = 1;
        }
    }

    // 輸出測試結果
    if (results.Count > 0)
    {
        Console.ForegroundColor = ConsoleColor.Red;
        Console.WriteLine("本次查詢，Page:{0},PageSize:{1}", page, pageSize);
        Console.ForegroundColor = ConsoleColor.Gray;
        foreach (var item in results)
        {
            Console.WriteLine("表：{0},總行數：{1},OFFSET：{2}，LIMIT：{3}", item.TableName, item.Total, item.Skip, item.Take);
        }
        Console.WriteLine();
    }
    else
    {
        Console.ForegroundColor = ConsoleColor.Red;
        Console.WriteLine("分頁下無資料：{0},{1}", page, pageSize);
        Console.ForegroundColor = ConsoleColor.Gray;
    }

    return results;
}

在上面的分頁演算法中，定義了4個私有變數，分別是
preBlock：存跨表資料塊長度
currentPage：當前表分頁
currentPageSize：當前表分頁長度，也是當前表接 preBlock 所需要的查詢長度
results：查詢表結果，存需要進行二次查詢的表結構

接下來，就對最近 17 張表進行模擬輪詢計算，把資料塊連線起來，首先是計算 skip 的長度，這裡使用當前表分頁加跨表塊

var skip = ((currentPage - 1) * currentPageSize) + preBlock

得到真實的 skip，然後用當前表 Total - skip 得到下一表的接續長度

 var remainder = item.Total - skip;

再透過判斷接續長度 remainder 大於 0，如果小於0則設定 preBlock 和 currentPage 進入下一表結構，如果大於 0 則進一步判斷其是否可以覆蓋 currentPageSize，如果可以覆蓋則記錄當前表並跳出迴圈，否則 重置 currentPageSize 和其它條件後進入下一個表結構。

if (remainder > 0)
{
    item.Skip = skip;
    item.Take = currentPageSize;
    if (remainder >= currentPageSize)
    {
        results.Add(item);
        break;
    }
    else
    {
        currentPageSize = currentPageSize - remainder;
        item.Take = remainder;
        currentPage = 1;
        preBlock = 0;
        results.Add(item);
    }
}
else
{
    preBlock = Math.Abs(remainder);
    currentPage = 1;
}

測試分頁結果

構建一些測試資料進行分頁，看接續是否已經閉合

public class Program
{
    public static void Main(string[] args)
    {
        PageDataService.Pagination(1, 40);
        PageDataService.Pagination(2, 40);
        PageDataService.Pagination(3, 40);
        PageDataService.Pagination(4, 40);
        PageDataService.Pagination(5, 40);
        PageDataService.Pagination(6, 40);
        PageDataService.Pagination(7, 40);
        PageDataService.Pagination(8, 40);
        PageDataService.Pagination(9, 40);
        PageDataService.Pagination(113, 10);

        Console.ReadKey();
    }
}

輸出測試結果

透過輸出的測試結果，可以看到，資料塊是連續的，且已經得到了每次需要查詢的表結構資料，在實際應用中，只需要對這個結果執行並行查詢然後在記憶體中歸併排序就可以了。

並行查詢和排序

public static void Query()
{
    var entitys = PageDataService.Pagination(1, 40);
    List<UserEntity> datas = new List<UserEntity>();
    Parallel.ForEach(entitys, entity =>
    {
        var sql = $"SELECT * FROM TABLE_{entity.TableName} ORDER BY Timestamp LIMIT {entity.Skip},{entity.Take}";
        var results = Mysql.Query<UserEntity>(sql);
        datas.AddRange(results);
    });

    // 排序
    datas = datas.OrderByDescending(x => x.Timestamp).ToList();
}

到這裡，就完成了有序的二次查詢法的演算法過程。這種分頁演算法存在一定的侷限性，比如必須是連續的資料塊，按一定時間區間進行分表才可使用，大區間查詢時的分頁，第一次查詢會比較慢，比如查詢區間為3年內的按天分表分頁資料，將會導致第一次查詢開啟 3*365 個資料庫連線，當然，這取決於你第一次查詢採用的是並行查詢還是輪詢，還是有最佳化空間的。

結束語

本文共列出了多種分庫分表方式下的查詢問題，大部分 ORM 只解決了分表插入的問題，對於分頁查詢，實際上也是沒有很好的解決方案，原因在於分頁查詢和業務的分割有著緊密的聯絡，很多時候不能簡單的將業務問題認為是中介軟體的問題。有序的二次查詢法作為一次探索，期望能解決部分業務帶來的分頁問題。

相關文章