How to optimize full data extraction-PolarDB(PolarDB)-阿里云帮助中心

When to use this approach

Two scenarios require extracting full data from PolarDB-X using application code:

Batch export: Exporting all data to a downstream system. PolarDB-X supports multiple export tools for this use case.
. For more information, see Data Import and Export
In-database processing: Processing data inside PolarDB-X or returning large result sets to users in bulk — situations where external export tools cannot be used.

For each scenario, choose between extracting all data at once or paginating results.

Extract full data

SQL statements for full data extraction typically omit shard keys, which causes PolarDB-X to run a full table scan across all physical shards. The amount of data read scales linearly with extraction time.

A more efficient approach: use hints to route queries directly to physical shards and pull data from each shard. This reduces unnecessary network traffic and connection overhead. For more information about hint syntax, see How to use hints.

The following Java example uses show topology to discover all physical shards for a logical table, then queries each shard directly using the /*+TDDL:NODE()*/ 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);
                }
            }
        }
    }
}

The code retrieves the partition ID and physical table name for each shard using show topology, then serially queries each shard using its partition ID as the node hint.

Perform paged queries

Paged queries return data in fixed-size pages, which reduces page load time and avoids returning excessive records to users. The challenge in PolarDB-X, as in all relational databases, is choosing a pagination strategy that stays efficient as data volume grows.

Why LIMIT offset degrades at scale

The standard MySQL pagination pattern uses LIMIT offset, pageSize:

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

The id column is included in ORDER BY because gmt_create can contain duplicate values — without a tiebreaker, sort order is non-deterministic.

This pattern degrades as data volume grows:

Data volume	Query time
Hundreds of thousands of records	Tens of milliseconds per query
Millions of records	Seconds per query
Larger volumes	Unacceptable

Each query scans offset + pageSize records — not just pageSize. As users navigate to later pages, the offset grows and approaches a full table scan. Adding data nodes to a distributed database does not help, because the bottleneck is sort-and-offset, not shard throughput. Local secondary indexes (LSIs) on user_id and gmt_create do not resolve this either, because only user_id appears in the WHERE clause.

Solution 1: Cursor pagination by ID

When to use: id is a primary key or unique key with no duplicate values.

Replace the offset with a cursor — the maximum id value from the previous page:

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

First page: omit the WHERE id > lastMaxId condition.
Subsequent pages: pass the maximum id returned on the previous page as lastMaxId.

The database locates lastMaxId in the index and returns the next pageSize records without scanning earlier rows.

Note If id contains duplicate values, records with those duplicate values may be skipped.

Solution 2: Row Constructor Expression

When to use: The sort order uses multiple columns (such as gmt_create, id) and cursor values are never null.

Use a Row Constructor Expression to create a multi-column cursor. This feature is available in both MySQL and PolarDB-X.

A row constructor comparison such as (c2, c3) > (1, 1) is equivalent to:

c2 > 1 OR ((c2 = 1) AND (c3 > 1))

Applied to the t_order example:

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

First page: omit the (gmt_create, id) > (...) condition.
Subsequent pages: pass the maximum gmt_create and id values from the previous page.

Including user_id in ORDER BY lets the optimizer sort using the composite index on (user_id, gmt_create), which improves query performance.

Note If a Row Constructor Expression contains a null value, the expression evaluates to null rather than performing a comparison. In PolarDB-X, row constructor expressions are used for column pruning only when they contain only shard keys. For all other cases — including null handling — rewrite the condition as an explicit OR expression.

Best practice for PolarDB-X

The Row Constructor Expression syntax is concise but cannot handle null values in cursor columns. Use the explicit OR form instead, handling both the null and non-null cases separately:

-- When 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

-- When 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

This pattern avoids the null-evaluation issue with row constructors and ensures correct column pruning in PolarDB-X.