如何最佳化資料全量抽取 - PolarDB

本文介紹了在應用內通過代碼高效抽取資料的方法。

簡介

資料幫浦是指通過代碼或者資料匯出工具，從PolarDB-X中批量讀取資料的操作。主要包括以下情境：

通過資料匯出工具將資料全量抽取到下遊系統。PolarDB-X支援多種資料匯出工具。
在應用內處理資料或者批量的將查詢結果返回給使用者瀏覽時，不能依賴外部工具，必須在應用內通過程式碼完成資料全量抽取。

本文主要介紹在應用內通過代碼高效抽取資料的方法，根據是否一次性讀取全量資料，分為全量抽取和分頁查詢。

全量抽取情境

全量抽取使用的SQL通常不包含表的拆分鍵，以全表掃描的方式執行，隨著讀取資料量的增加，資料幫浦操作的執行時間軸性增長。為了避免佔用過多網路或串連資源，可以使用HINT直接下發查詢語句，從物理分區中拉取資料。以下樣本採用Java代碼編寫，完整使用方法參考如何使用HINT。

public static void extractData(Connection connection, String logicalTableName, Consumer<ResultSet> consumer)
    throws SQLException {

    final String topology = "show topology from {0}";
    final String query = "/*+TDDL:NODE({0})*/select * from {1}";

    try (final Statement statement = connection.createStatement()) {
        final Map<String, List<String>> partitionTableMap = new LinkedHashMap<>();
        // Get partition id and physical table name of given logical table
        try (final ResultSet rs = statement.executeQuery(MessageFormat.format(topology, logicalTableName))) {
            while (rs.next()) {
                partitionTableMap.computeIfAbsent(rs.getString(2), (k) -> new ArrayList<>()).add(rs.getString(3));
            }
        }
        // Serially extract data from each partition
        for (Map.Entry<String, List<String>> entry : partitionTableMap.entrySet()) {
            for (String tableName : entry.getValue()) {
                try (final ResultSet rs = statement
                    .executeQuery(MessageFormat.format(query, entry.getKey(), tableName))) {
                    // Consume data
                    consumer.accept(rs);
                }
            }
        }
    }
}

分頁查詢情境

向使用者展示列表資訊時，需要分頁來提高頁面的載入效率，避免返回過多冗餘資訊，用於處理分頁顯示需求的查詢，稱為分頁查詢。關係型資料庫沒有直接提供分段返回表中資料的能力，高效的實現分頁查詢，還需要結合資料庫本身的特點來設計查詢語句。

以MySQL為例，分頁查詢最直觀的實現方法，是使用limit offset，pageSize來實現，例如如下查詢：

select * from t_order where user_id = xxx order by gmt_create, id limit offset, pageSize

因為gmt_create可能重複，所以order by時應加上id，保證結果順序的確定性。

說明該方案在表規模較小的時候，能夠正常運行。當t_order表增長到十萬級，隨著頁數增加，執行速度明顯變慢，可能降到幾十毫秒的量級，如果資料量增長到百萬級，則耗時達到秒級，資料量繼續增長，耗時最終會變得不可接受。

問題分析

假設我們在user_id，gmt_create上建立了局部索引，由於只有user_id上的條件，每次需要掃描的總資料量為offset + pageSize ，隨著offset的增大逐漸接近全表掃描，導致耗時增加。並且在分散式資料庫中，全表排序的吞吐無法通過增加DN數量來提高。

改進方案1

每次擷取下一頁記錄時，指定從上次結束的位置繼續往後取，這樣不需要設定offset ，能夠避免出現全表掃描的情況。如下為一個按ID進行分頁查詢的例子：

select * from t_order where id > lastMaxId order by id limit pageSize

第一次查詢不指定條件，後續查詢則傳入前一次查詢的最大id，在執行時，資料庫首先在索引上定位到lastMaxId的位置，然後連續返回pageSize條記錄即可，非常高效。

說明當ID為主鍵或者唯一鍵時，改進方案1可以達到分頁查詢的效果，也有不錯的效能。但缺點也比較明顯，當ID上有重複值時，可能會漏掉部分記錄。

改進方案2

MySQL支援通過 Row Constructor Expression實現多列比較的語義（PolarDB-X同樣支援）。

(c2,c3) > (1,1) 
等價於 
c2 > 1 OR ((c2 = 1) AND (c3 > 1))

因此，可以用下面的方法實現分頁查詢：

select * from t_order 
where user_id = xxx and (gmt_create, id) > (lastMaxGmtCreate, lastMaxId)
order by user_id, gmt_create, id limit pageSize

第一次查詢不指定條件，後續查詢則傳入前一次查詢的最大gmt_create和id，通過Row Constructor Expression正確處理gmt_create存在重複的情況。

說明樣本中，為了提高查詢效能，在user_id和gmt_create上建立了聯合索引，並在order by中加入user_id提示最佳化器可以通過索引來消除排序。由於Row Constructor Expression包含null值會導致運算式求值結果為null，當存在null值時需要使用OR運算式。PolarDB-X目前只在Row Constructor Expression僅包含拆分鍵時才將其用於分區裁剪，其他情境同樣需要使用OR運算式。

結合上述分析，給出一個PolarDB-X上分頁查詢的最佳實務：

-- lastMaxGmtCreate is not null 
select * from t_order 
where user_id = xxx 
and (
      (gmt_create > lastMaxGmtCreate) 
      or ((gmt_create = lastMaxGmtCreate) and (id > lastMaxId))
    )
order by user_id, gmt_create, id limit pageSize

-- lastMaxGmtCreate is null
select * from t_order 
where user_id = xxx 
and (
      (gmt_create is not null)
      or (gmt_create is null and id > lastMaxId)
    )
order by user_id, gmt_create, id limit pageSize