How to efficiently scan data

Distributed Relational Database Service (PolarDB-X 1.0) supports efficient data scanning and uses aggregate functions for statistical summary during full table scans. For large datasets, you can target individual shards directly using SQL hints to scan all table shards in parallel.

Scanning modes

PolarDB-X 1.0 applies different execution strategies depending on how a query is structured:

Scenario	How PolarDB-X 1.0 executes it	Supported aggregate functions
No database or table shards	Passes the SQL statement directly to the backend ApsaraDB RDS for MySQL instance	All aggregate functions
Non-full table scan (shard key equality in WHERE clause)	Routes to a single ApsaraDB RDS for MySQL instance	All aggregate functions
Full table scan (no shard key filter)	Fans out to all shards, then merges results	COUNT, MAX, MIN, SUM; also supports LIKE, ORDER BY, LIMIT, and GROUP BY
Parallel scan of all table shards	Each shard is queried directly using a hint; you manage the sessions	Depends on per-shard query

For full table scans, only COUNT, MAX, MIN, and SUM are supported because each shard computes a partial aggregate locally and PolarDB-X 1.0 merges the results. Aggregate functions that require cross-shard ordering or grouping of individual rows cannot be pushed down this way.

Scan table shards using a hint

Use the TDDL hint syntax to target individual shards directly when you need to read data from specific shards — for export, auditing, or analytics.

Step 1: Get the table topology

Run SHOW TOPOLOGY FROM <table_name> to see the current physical layout of a table.

mysql> SHOW TOPOLOGY FROM DRDS_USERS;
+------+-------------------+--------------+
| ID   | GROUP_NAME        | TABLE_NAME   |
+------+-------------------+--------------+
|    0 | DRDS_00_RDS       | DRDS_USERS   |
|    1 | DRDS_01_RDS       | DRDS_USERS   |
+------+-------------------+--------------+
2 rows in set (0.06 sec)

The output lists each physical shard by its GROUP_NAME and the corresponding physical table name (TABLE_NAME). Run this command before each scan to get the latest topology — the shard layout can change after table restructuring.

Non-sharding tables are stored in database shard 0 by default.

Step 2: Query each shard using its GROUP_NAME

Use the TDDL hint /! TDDL:node='<GROUP_NAME>'*/ to route a query to a specific shard. The GROUP_NAME in the hint must match the GROUP_NAME column from the SHOW TOPOLOGY output. Use the physical table shard name (from the TABLE_NAME column), not the logical table name, in the SQL statement.

Run the following query on shard 0 (DRDS_00_RDS):

/! TDDL:node='DRDS_00_RDS'*/ SELECT * FROM DRDS_USERS;

Run the following query on shard 1 (DRDS_01_RDS):

/! TDDL:node='DRDS_01_RDS'*/ SELECT * FROM DRDS_USERS;

Repeat for each shard listed in the topology output.

Scan table shards in parallel

PolarDB-X 1.0 allows you to run mysqldump to export data. However, if you want to scan data faster, you can open multiple sessions — one per table shard — to scan all shards simultaneously.

The maximum number of parallel sessions equals the total number of table shards. Each session targets exactly one table shard, so opening more sessions than shards provides no additional benefit.

Example: The following topology shows a table with 4 database shards and 3 table shards each — 12 table shards total.

mysql> SHOW TOPOLOGY FROM LJLTEST;
+------+----------------+------------+
| ID   | GROUP_NAME     | TABLE_NAME |
+------+----------------+------------+
|    0 | TDDL5_00_GROUP | ljltest_00 |
|    1 | TDDL5_00_GROUP | ljltest_01 |
|    2 | TDDL5_00_GROUP | ljltest_02 |
|    3 | TDDL5_01_GROUP | ljltest_03 |
|    4 | TDDL5_01_GROUP | ljltest_04 |
|    5 | TDDL5_01_GROUP | ljltest_05 |
|    6 | TDDL5_02_GROUP | ljltest_06 |
|    7 | TDDL5_02_GROUP | ljltest_07 |
|    8 | TDDL5_02_GROUP | ljltest_08 |
|    9 | TDDL5_03_GROUP | ljltest_09 |
|   10 | TDDL5_03_GROUP | ljltest_10 |
|   11 | TDDL5_03_GROUP | ljltest_11 |
+------+----------------+------------+
12 rows in set (0.06 sec)

Open up to 12 sessions in parallel, with each session targeting one table shard. For example, to query ljltest_00 on TDDL5_00_GROUP:

/! TDDL:node='TDDL5_00_GROUP'*/ SELECT * FROM ljltest_00;

Run a similar statement in separate sessions for each of the remaining 11 shards (ljltest_01 through ljltest_11), using the corresponding GROUP_NAME from the topology output.