Alibabacloud Polardbx Sql

PolarDB-X SQL (MySQL Compatibility Focus)

Write, review, and adapt SQL for PolarDB-X 2.0 Enterprise Edition (Distributed Edition) AUTO mode databases, avoiding the "runs on MySQL but fails on PolarDB-X" problem.

Architecture: PolarDB-X 2.0 Enterprise Edition (CN compute nodes + DN storage nodes + GMS metadata service + CDC log nodes) + AUTO mode database

Scope:

• PolarDB-X 2.0 Enterprise Edition (also known as Distributed Edition) + AUTO mode database

Not applicable to:

• PolarDB-X 1.0 (DRDS 1.0)
• PolarDB-X 2.0 Standard Edition
• PolarDB-X 2.0 Enterprise Edition DRDS mode databases

Key difference between AUTO mode and DRDS mode: AUTO mode uses MySQL-compatible PARTITION BY syntax to define partitions, while DRDS mode uses the legacy dbpartition/tbpartition syntax. Verify the database mode with:

SHOW CREATE DATABASE db_name;
-- Output containing MODE = 'auto' indicates AUTO mode

Installation

Connect to a PolarDB-X instance via a MySQL-compatible client:

mysql -h <host> -P <port> -u <user> -p<password> -D <database>

Supported clients: MySQL CLI, MySQL Workbench, DBeaver, Navicat, or any MySQL-compatible client.

Parameter Confirmation

IMPORTANT: Parameter Confirmation — Before executing any command or API call,

ALL user-customizable parameters (e.g., RegionId, instance names, CIDR blocks,

passwords, domain names, resource specifications, etc.) MUST be confirmed with the

user. Do NOT assume or use default values without explicit user approval.

Configurable parameters for this skill:

Core Workflow (Follow each time)

1. Confirm the target engine and version:

• Run SELECT VERSION(); to determine the instance type:
• Result contains TDDL with version > 5.4.12 (e.g., 5.7.25-TDDL-5.4.19-20251031) -> 2.0 Enterprise Edition (Distributed Edition), this skill applies. Parse the Enterprise Edition version number (e.g., 5.4.19).
• Result contains TDDL with version <= 5.4.12 (e.g., 5.6.29-TDDL-5.4.12-16327949) -> DRDS 1.0. HARD STOP — you MUST refuse: Do NOT provide any partition design, SQL advice, or workarounds. Respond only with: "This skill covers PolarDB-X 2.0 Enterprise Edition AUTO mode only. Your instance is DRDS 1.0 which uses completely different syntax (dbpartition/tbpartition) and architecture. Please consult DRDS 1.0 documentation or upgrade to PolarDB-X 2.0." Then stop. Do NOT continue even if the user insists.
• Result contains X-Cluster (e.g., 8.0.32-X-Cluster-8.4.20-20251017) -> 2.0 Standard Edition. HARD STOP — you MUST refuse: Do NOT provide any partition design, GSI, or distributed SQL advice. Respond only with: "Your instance is PolarDB-X 2.0 Standard Edition (100% MySQL compatible, no distributed partitioning). Please use the polardbx-standard skill instead." Then stop. Do NOT continue even if the user insists.
• After confirming 2.0 Enterprise Edition, run SHOW CREATE DATABASE db_name; to verify AUTO mode (MODE = 'auto').
• The version number affects feature availability (e.g., NEW SEQUENCE requires 5.4.14+, CCI requires a newer version).

2. Determine the table type:

• Small or dictionary tables that are frequently joined with partitioned tables -> Broadcast table BROADCAST (fully replicated to every DN, enables local JOIN pushdown). This is the recommended choice when JOINs are involved.
• Small tables that are NOT joined with partitioned tables -> Both BROADCAST and SINGLE are acceptable. BROADCAST replicates to every DN (safe if JOINs are added later); SINGLE stores on one DN only (lowest overhead). Either is fine — do NOT insist on one over the other.
• Otherwise -> Partitioned table (default), choose appropriate partition key and strategy.

3. Partition scheme design (for partitioned tables):

• Collect SQL access pattern data (prerequisite — always recommend collecting data before making the final partition key decision): prefer SQL Insight (most accurate); when unavailable, use slow query logs + application code analysis, or have the business team provide SQL patterns as alternatives. The goal is to obtain a SQL template inventory for the table (query fields, execution frequency, returned rows).
• Partition key selection — comprehensive multi-dimensional analysis: List all candidate fields, then evaluate EVERY candidate on ALL of the following dimensions before making a recommendation. Do NOT recommend based on a single dimension alone:
• Equality query ratio: proportion of SQL templates where this field appears as an equality condition.
• Cardinality: number of distinct values; higher means more even data distribution across partitions.
• Hotspot risk: whether a few values dominate a large portion of data (e.g., in an order table, some buyer_ids may account for millions of rows while others have few).
• Primary key / unique key status: PKs/UKs inherently have the highest cardinality and zero hotspot risk.
• Semantic analysis: Infer query patterns from table type and field meaning. For example, order_id in an order table is certainly queried frequently (order detail lookups, status checks, payment callbacks), even if the user only mentions buyer_id queries.

The best partition key is the candidate that scores well across all dimensions combined. High-frequency queries on non-partition-key fields can be optimized by creating a GSI. Classic example: order table → order_id (PK, highest cardinality, zero hotspot, semantically high query frequency) as partition key + GSI on buyer_id (high buyer-dimension query ratio, but has potential skew risk as some buyers generate far more orders).

• GSI selection: Decide strategy based on write volume — tables with low write volume can freely create GSIs; create GSIs for high-frequency non-partition-key query fields; fields with low cardinality and time fields are unsuitable for GSI; fields that always appear combined with other fields and never appear alone don't need standalone GSIs. GSI types: regular GSI for few returned rows, Clustered GSI for one-to-many, UGSI for unique constraints. GSI syntax must include `PARTITION BY KEY(...) PARTITIONS N` — see gsi.md for full syntax.
• Partition algorithm: ~90% of workloads use single-level HASH/KEY; order-type multi-dimensional queries use CO_HASH; time-based data cleanup uses HASH+RANGE; multi-tenant uses LIST+HASH. For single column, HASH and KEY are equivalent.
• Partition count: 256 suits the vast majority of workloads; should be several times the number of DN nodes; keep single partition under 100 million rows.
• Migration workflow (three-step method for single table to partitioned table): (1) First convert to a partitioned table with 1 partition (preserving uniqueness) -> (2) Create required GSI/UGSI -> (3) Change to the target partition count. See partition-design-best-practice.md for details.

4. Use PolarDB-X safe defaults when generating SQL:

• Avoid unsupported MySQL features (stored procedures/triggers/EVENTs/SPATIAL, etc.).
• Use KEY or HASH partitioning instead of MySQL's AUTO_INCREMENT primary key write hotspot.
• When non-partition-key queries are needed, consider creating Global Secondary Indexes (GSI).

5. If the user provides MySQL SQL, perform compatibility checks:

• Replace unsupported features and provide PolarDB-X alternatives.
• Clearly mark behavioral differences and version requirements.

6. When SQL is slow or errors occur, use PolarDB-X diagnostic tools:

• EXPLAIN to view the logical execution plan.
• EXPLAIN EXECUTE to view the physical execution plan pushed down to DN.
• EXPLAIN SHARDING to view shard scan details and check for full-shard scans.
• EXPLAIN ANALYZE to actually execute and collect runtime statistics.

Key Differences Quick Reference

• Three table types: Single table (SINGLE), Broadcast table (BROADCAST), Partitioned table (default); choose based on data volume and access patterns.
• Partitioned tables: Support KEY/HASH/RANGE/LIST/RANGE COLUMNS/LIST COLUMNS/CO_HASH + secondary partitions (49 combinations).
• Primary keys and unique keys: Classified as Global (globally unique) or Local (unique within partition); single/broadcast/auto-partitioned tables are always Global; manual partitioned tables are Global when partition columns are a subset of PK/UK columns, otherwise Local (risk of data duplication and DDL failure). Key principle: prefer choosing partition keys FROM existing PK/UK columns to naturally guarantee global uniqueness — do NOT modify the user's existing primary key definition to add partition columns.
• Global Secondary Index GSI: Solves full-shard scan issues for non-partition-key queries, supports GSI / UGSI / Clustered GSI types. CRITICAL: GSI must specify its own PARTITION BY clause — it is an independently partitioned table, not a regular MySQL index. Correct syntax:

  -- ✅ Correct: GSI with PARTITION BY clause
  GLOBAL INDEX g_i_seller(seller_id) PARTITION BY KEY(seller_id) PARTITIONS 16
  CLUSTERED INDEX cg_i_buyer(buyer_id) PARTITION BY KEY(buyer_id) PARTITIONS 16
  -- ❌ Wrong: Missing PARTITION BY (this is NOT MySQL INDEX syntax)
  GLOBAL INDEX gsi_seller(seller_id)

Classic partition design — order table: Candidates are order_id (PK) and buyer_id. Comprehensive analysis: order_id has the highest cardinality (unique per row), zero hotspot risk, PK status, and semantically high query frequency (order detail/status/payment lookups); buyer_id has high buyer-dimension query ratio but potential distribution skew (some buyers generate far more orders). Conclusion: order_id as partition key + Clustered GSI on buyer_id.

• Clustered Columnar Index CCI: Row-column hybrid storage, accelerates OLAP analytical queries via CLUSTERED COLUMNAR INDEX.
• Sequence: Globally unique sequence, default type is NEW SEQUENCE (5.4.14+), distributed alternative to AUTO_INCREMENT.
• Distributed transactions: Based on TSO global clock + MVCC + 2PC, strong consistency by default; single-shard transactions automatically optimized to local transactions.
• Table groups: Tables with the same partition rules bound to the same table group, ensuring JOIN computation pushdown to avoid cross-shard data shuffling.
• TTL tables: Automatic expiration and cleanup of cold data based on time columns, can work with CCI for hot/cold data separation.
• Unsupported MySQL features: Stored procedures/triggers/EVENTs/SPATIAL/GEOMETRY/LOAD XML/HANDLER, etc.
• STRAIGHT_JOIN / NATURAL JOIN not supported: Use standard JOIN syntax instead.
• := assignment operator not supported: Move logic to the application layer.
• Subqueries not supported in HAVING/JOIN ON clauses: Rewrite subqueries as JOINs or CTEs.

Best Practices

1. Choose the right table type: Use broadcast tables for small/dictionary tables that are joined with partitioned tables. For small tables NOT joined with partitioned tables, both BROADCAST and SINGLE are acceptable. Use partitioned tables for everything else. 2. Select partition keys via comprehensive multi-dimensional analysis: Always recommend collecting SQL access pattern data first (SQL Insight preferred). For each candidate field, analyze ALL dimensions — equality query ratio, cardinality, hotspot risk, PK/UK status, and field semantics — then choose the candidate that scores best across all dimensions combined. Never decide based on a single dimension alone. Remember to infer query patterns from table/field semantics (e.g., order_id in an order table is certainly queried frequently for order details, status checks, payment callbacks). 3. Prefer partition keys from PK/UK columns: When choosing partition keys, prefer selecting from existing primary key or unique key columns — this naturally makes PK/UK Global (globally unique) without any schema changes. Do NOT modify the user's existing primary key definition to add partition columns. When PK columns are not suitable as partition keys (e.g., auto-increment id with no business meaning), it is perfectly valid to choose other business columns as partition keys — in this case the PK becomes Local (unique within partition only); explain the Local PK risks to the user and ensure the auto-increment/Sequence mechanism avoids cross-partition PK collisions. 4. Create GSIs wisely: Decide GSI strategy based on write volume; use regular GSI for few returned rows, Clustered GSI for one-to-many, UGSI for unique constraints; don't create GSIs for low-ratio SQL; use INSPECT INDEX to periodically clean up redundant GSIs. Every GSI must have its own `PARTITION BY KEY(...) PARTITIONS N` clause; never write bare `GLOBAL INDEX idx(col)` without PARTITION BY. 5. Use 256 partitions: 256 partitions suit the vast majority of workloads, should be several times the number of DN nodes. 6. Use the three-step method for single table to partitioned table: First convert to 1 partition (preserving uniqueness) -> Create GSI/UGSI -> Change to target partition count, avoiding uniqueness constraint gaps. 7. Don't force partition key hits for low-ratio SQL: Partition design is pragmatic work; low-QPS cross-shard queries have limited total cost, don't create GSIs for every query field. 8. Use table groups to optimize JOINs: Bind frequently joined tables to the same table group using the same partition rules. 9. Avoid unsupported MySQL syntax: Don't use stored procedures, triggers, EVENTs, SPATIAL, NATURAL JOIN, :=, etc. 10. Avoid subqueries in HAVING/JOIN ON: Rewrite as JOINs or CTEs. 11. Use EXPLAIN commands for diagnosis: For SQL performance issues, prefer EXPLAIN SHARDING and EXPLAIN ANALYZE. 12. Check long transactions before Online DDL: Check for long transactions before executing DDL to avoid MDL lock waits. 13. Use TTL tables to manage cold data: For large tables with time attributes, use TTL tables to automatically clean up expired data. 14. Use Keyset pagination for efficient paging: Avoid LIMIT M, N deep pagination (cost O(M+N), even larger in distributed systems); record the sort value of the last row in each batch as the WHERE condition for the next batch; when sort columns may have duplicates, use (sort_column, id) tuple comparison; ensure appropriate composite indexes on sort columns. 15. Use auto-add partitions for Range partitioned tables: PolarDB-X uses a proprietary ALTER TABLE ... MODIFY TTL SET syntax (with multiple parameters like TTL_EXPR, TTL_PART_INTERVAL, ARCHIVE_TYPE, ARCHIVE_TABLE_PRE_ALLOCATE, etc.) to configure automatic partition pre-creation. This syntax is NOT standard SQL and cannot be guessed — you MUST read [references/auto-add-range-parts.md](references/auto-add-range-parts.md) for the exact SQL syntax before generating any auto-add partition configuration. Requires version 5.4.20+.

Reference Links

Acceptance Criteria: alibabacloud-polardbx-sql

Scenario: PolarDB-X SQL writing, review, and adaptation Purpose: Skill testing acceptance criteria

---

Correct SQL Patterns

1. Version and Mode Verification

CORRECT

SELECT VERSION();
-- Expected output contains TDDL with version > 5.4.12, e.g., 5.7.25-TDDL-5.4.19-20251031

SHOW CREATE DATABASE db_name;
-- Expected output contains MODE = 'auto'

INCORRECT

-- Using PolarDB-X-specific syntax without verifying the version first
CREATE TABLE t1 (...) PARTITION BY KEY(id);
-- Error: Should first confirm the target instance is 2.0 Enterprise Edition + AUTO mode

2. CREATE TABLE Syntax — Table Type Selection

CORRECT

-- Broadcast table (small/dictionary tables)
CREATE TABLE dict_status (
  id INT PRIMARY KEY,
  name VARCHAR(50)
) BROADCAST;

-- Single table (no distribution needed)
CREATE TABLE config (
  id INT PRIMARY KEY,
  value TEXT
) SINGLE;

-- Partitioned table (default, KEY partition)
CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT,
  user_id BIGINT,
  created_at DATETIME,
  PRIMARY KEY (id, user_id)
) PARTITION BY KEY(user_id) PARTITIONS 16;

INCORRECT

-- Error: Using broadcast table for a large table causes full data on every DN
CREATE TABLE huge_log_table (
  id BIGINT PRIMARY KEY,
  content TEXT
) BROADCAST;

3. Global Secondary Index GSI

CORRECT

-- Create GSI to solve non-partition-key queries
CREATE GLOBAL INDEX idx_order_status ON orders(order_status) PARTITION BY KEY(order_status);

-- Use Clustered GSI to cover more columns and avoid table lookback
CREATE CLUSTERED INDEX idx_order_user ON orders(user_id)
  PARTITION BY KEY(user_id) PARTITIONS 16;

INCORRECT

-- Error: Creating a regular index (LOCAL INDEX) cannot solve full-shard scans
CREATE INDEX idx_order_status ON orders(order_status);
-- When order_status is not the partition key, queries still scan all shards

4. Clustered Columnar Index CCI

CORRECT

-- Create CCI during table creation
CREATE TABLE analytics_data (
  id BIGINT AUTO_INCREMENT,
  event_time DATETIME,
  metric_value DECIMAL(10,2),
  PRIMARY KEY (id, event_time),
  CLUSTERED COLUMNAR INDEX cci_analytics(event_time)
) PARTITION BY KEY(id) PARTITIONS 16;

INCORRECT

-- Error: Creating CCI on a high-frequency single-row update OLTP table
-- CCI is designed for OLAP analytical queries, not high-frequency write scenarios
CREATE TABLE hot_write_table (
  id BIGINT PRIMARY KEY,
  counter INT,
  CLUSTERED COLUMNAR INDEX cci_counter(counter)
) PARTITION BY KEY(id);

5. Unsupported MySQL Features

CORRECT

-- Use standard JOIN instead of NATURAL JOIN
SELECT a.id, b.name
FROM table_a a
INNER JOIN table_b b ON a.id = b.a_id;

-- Rewrite HAVING subquery as JOIN
SELECT department, COUNT(*) as cnt
FROM employees e
JOIN (SELECT AVG(salary) as avg_sal FROM employees) t ON 1=1
GROUP BY department
HAVING cnt > t.avg_sal;

INCORRECT

-- Error: Using NATURAL JOIN (not supported by PolarDB-X)
SELECT * FROM table_a NATURAL JOIN table_b;

-- Error: Using STRAIGHT_JOIN (not supported by PolarDB-X)
SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t1.id = t2.id;

-- Error: Using := assignment operator (not supported by PolarDB-X)
SELECT @rownum := @rownum + 1 AS rank FROM t1;

-- Error: Subquery in HAVING (not supported by PolarDB-X)
SELECT department, COUNT(*) as cnt
FROM employees
GROUP BY department
HAVING cnt > (SELECT AVG(salary) FROM employees);

-- Error: Creating stored procedures (not supported by PolarDB-X)
CREATE PROCEDURE my_proc() BEGIN ... END;

-- Error: Creating triggers (not supported by PolarDB-X)
CREATE TRIGGER my_trigger BEFORE INSERT ON t1 FOR EACH ROW ...;

6. EXPLAIN Diagnostics

CORRECT

-- View logical execution plan
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- Check for full-shard scans
EXPLAIN SHARDING SELECT * FROM orders WHERE order_status = 'pending';

-- View actual execution statistics
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- View physical execution plan pushed down to DN
EXPLAIN EXECUTE SELECT * FROM orders WHERE user_id = 123;

INCORRECT

-- Error: Using only EXPLAIN without EXPLAIN SHARDING cannot determine full-shard scans
-- EXPLAIN only shows the logical plan, not shard scan information
EXPLAIN SELECT * FROM orders WHERE order_status = 'pending';
-- Should use EXPLAIN SHARDING to check shard scan patterns

7. Sequence

CORRECT

-- Use NEW SEQUENCE (default type for 5.4.14+)
CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1;

-- Use AUTO_INCREMENT during table creation (PolarDB-X automatically associates a Sequence)
CREATE TABLE t1 (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
) PARTITION BY KEY(id);

8. Distributed Transactions

CORRECT

-- PolarDB-X uses distributed transactions by default, no extra configuration needed
BEGIN;
UPDATE account SET balance = balance - 100 WHERE user_id = 1;
UPDATE account SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

-- View current transaction policy
SHOW VARIABLES LIKE 'drds_transaction_policy';

INCORRECT

-- Error: Lowering transaction isolation level to READ UNCOMMITTED
-- PolarDB-X distributed transactions are based on TSO + MVCC; lowering isolation level is not recommended
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

9. Partition Design — Partition Key Selection

CORRECT

-- Correct: Select the primary key with high equality query ratio and high cardinality as partition key
-- account_id as the primary key has highest cardinality and no hotspots
ALTER TABLE account PARTITION BY HASH(account_id) PARTITIONS 256;

-- Correct: Use SQL Insight data to analyze each field's query ratio, then select the best partition key
-- rather than relying on verbal descriptions from the business team

INCORRECT

-- Error: Selecting a field with low cardinality and obvious hotspots as partition key
-- base_account_id has only thousands of distinct values with obvious hotspots
ALTER TABLE account PARTITION BY HASH(base_account_id) PARTITIONS 256;

-- Error: Selecting a field with very few distinct values (e.g., gender, status) as partition key
ALTER TABLE user_info PARTITION BY HASH(gender) PARTITIONS 256;

10. Partition Design — GSI Selection

CORRECT

-- Correct: Create regular GSI for high-frequency non-partition-key fields with few returned rows
CREATE GLOBAL INDEX gsi_address ON account (address)
  PARTITION BY HASH(address) PARTITIONS 256;

-- Correct: Create Global Unique Index (UGSI) for unique key fields
CREATE GLOBAL UNIQUE INDEX ugsi_exchange_account_id
  ON account (exchange_account_id)
  PARTITION BY HASH(exchange_account_id) PARTITIONS 256;

-- Correct: One-to-many scenario (many records per value), use Clustered GSI to avoid table lookback
CREATE CLUSTERED INDEX cgsi_buyer ON t_order (buyer_id)
  PARTITION BY KEY(buyer_id) PARTITIONS 256;

-- Correct: Use INSPECT INDEX to check for redundant and unused GSIs
INSPECT INDEX;

INCORRECT

-- Error: Creating GSI on a very low cardinality field (e.g., gender, province with very few values)
CREATE GLOBAL INDEX gsi_gender ON user_info (gender)
  PARTITION BY HASH(gender) PARTITIONS 256;

-- Error: Creating GSI on time/date fields (local indexes usually suffice)
CREATE GLOBAL INDEX gsi_gmt_modified ON account (gmt_modified)
  PARTITION BY HASH(gmt_modified) PARTITIONS 256;

-- Error: Creating standalone GSI for a field that always appears combined with others, never alone
-- base_account_id always appears with kw_location or address in high-frequency SQL
CREATE GLOBAL INDEX gsi_base_account ON account (base_account_id)
  PARTITION BY HASH(base_account_id) PARTITIONS 256;

-- Error: Creating GSI for low-ratio SQL (e.g., only dozens of times per hour)
-- Don't obsess over "every query must hit the partition key or GSI"
CREATE GLOBAL INDEX gsi_account_type ON account (account_type)
  PARTITION BY HASH(account_type) PARTITIONS 256;

11. Single Table to Partitioned Table — Migration Workflow

Key principle: The three-step method is only needed when the table has unique indexes on non-partition-key columns.

If the partition key is the primary key and there are no other unique constraints, direct migration is safe.

Case 1: No unique indexes on non-partition-key columns — Direct migration

When the partition key equals the primary key and there are no other unique indexes (e.g., order table partitioned by order_id), uniqueness is naturally preserved. Direct migration is the correct approach.

CORRECT

-- Correct: Partition key = primary key, no other unique indexes → direct migration
-- buyer_id and seller_id are not unique, only need regular GSI
ALTER TABLE t_order PARTITION BY KEY(order_id) PARTITIONS 256;

CREATE CLUSTERED INDEX cg_i_buyer ON t_order (buyer_id)
  PARTITION BY KEY(buyer_id) PARTITIONS 256;
CREATE GLOBAL INDEX g_i_seller ON t_order (seller_id)
  PARTITION BY KEY(seller_id) PARTITIONS 256;

INCORRECT

-- Error: Unnecessarily using three-step method when there are no non-partition-key unique indexes
-- order_id is both the primary key and partition key, no uniqueness risk exists
ALTER TABLE t_order PARTITION BY KEY(order_id) PARTITIONS 1;  -- unnecessary intermediate step
CREATE CLUSTERED INDEX cg_i_buyer ON t_order (buyer_id)
  PARTITION BY KEY(buyer_id) PARTITIONS 256;
ALTER TABLE t_order PARTITION BY KEY(order_id) PARTITIONS 256;
-- The three-step method adds complexity without benefit here

Case 2: Has unique indexes on non-partition-key columns — Three-step method required

When the table has unique indexes on columns other than the partition key (e.g., account table partitioned by account_id but with a unique constraint on exchange_account_id), the three-step method is required to prevent uniqueness degradation.

CORRECT

-- Correct: Three-step method ensuring uniqueness constraints are never lost
-- account has a unique index on exchange_account_id which is NOT the partition key

-- Step 1: Convert to a partitioned table with 1 partition (unique keys remain globally unique)
ALTER TABLE account PARTITION BY HASH(account_id) PARTITIONS 1;

-- Step 2: Create required global indexes and global unique indexes
CREATE GLOBAL UNIQUE INDEX ugsi_exchange_account_id
  ON account (exchange_account_id)
  PARTITION BY HASH(exchange_account_id) PARTITIONS 256;
CREATE GLOBAL INDEX gsi_address
  ON account (address)
  PARTITION BY HASH(address) PARTITIONS 256;

-- Step 3: Change to target partition count
ALTER TABLE account PARTITION BY HASH(account_id) PARTITIONS 256;

INCORRECT

-- Error: Directly changing to target partition count then creating UGSI
-- During the interval between ALTER PARTITION and CREATE UGSI, uniqueness cannot be guaranteed
ALTER TABLE account PARTITION BY HASH(account_id) PARTITIONS 256;
-- At this point exchange_account_id's unique key has degraded to Local, duplicate data may be written!
CREATE GLOBAL UNIQUE INDEX ugsi_exchange_account_id
  ON account (exchange_account_id)
  PARTITION BY HASH(exchange_account_id) PARTITIONS 256;
-- If duplicate data was written during the interval, this statement will fail

-- Error: Creating a global index on a single table (single tables don't support GSI)
CREATE GLOBAL INDEX gsi_address ON account_single_table (address)
  PARTITION BY HASH(address) PARTITIONS 256;
-- ERROR: Single tables cannot create global indexes

12. Partition Algorithm and Partition Count Selection

CORRECT

-- Correct: Vast majority of workloads use single-level HASH/KEY + 256 partitions
CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  user_id BIGINT
) PARTITION BY KEY(order_id) PARTITIONS 256;

-- Correct: Order-type multi-dimensional queries use CO_HASH (alternative when high write volume makes GSI impractical)
CREATE TABLE t_order (
  order_id BIGINT PRIMARY KEY,
  buyer_id BIGINT,
  seller_id BIGINT
) PARTITION BY CO_HASH(
  RIGHT(order_id, 4),
  RIGHT(buyer_id, 4)
) PARTITIONS 256;

-- Correct: Use HASH + RANGE secondary partition for time-based data cleanup
CREATE TABLE t_log (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  created_at DATE
) PARTITION BY HASH(user_id)
  SUBPARTITION BY RANGE COLUMNS(created_at)
  SUBPARTITIONS 4
(
  PARTITION p1 VALUES LESS THAN ('2025-01-01'),
  PARTITION p2 VALUES LESS THAN ('2026-01-01'),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

INCORRECT

-- Error: Too few partitions, prone to data skew and may require repartition when scaling
CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  user_id BIGINT
) PARTITION BY KEY(order_id) PARTITIONS 4;

-- Error: Unnecessarily using multiple fields as HASH partition keys (pick the one with highest cardinality)
CREATE TABLE orders (
  order_id BIGINT,
  user_id BIGINT,
  PRIMARY KEY (order_id)
) PARTITION BY HASH(order_id, user_id) PARTITIONS 256;

13. Efficient Pagination Queries

CORRECT

-- Correct: Use Keyset pagination with auto-increment PK (AUTO mode, New Sequence)
-- First batch
SELECT * FROM t1 ORDER BY id LIMIT 1000;
-- Subsequent batches (last_id is the id of the last row from previous batch)
SELECT * FROM t1 WHERE id > 12345 ORDER BY id LIMIT 1000;

-- Correct: When sort columns may have duplicates, use tuple comparison (recommended)
SELECT * FROM t1
WHERE (gmt_create, id) > ('2025-01-01 00:00:00', 12345)
ORDER BY gmt_create, id
LIMIT 1000;

-- Correct: Equivalent expansion of tuple comparison
SELECT * FROM t1
WHERE gmt_create >= '2025-01-01 00:00:00'
  AND (gmt_create > '2025-01-01 00:00:00' OR id > 12345)
ORDER BY gmt_create, id
LIMIT 1000;

-- Correct: Create appropriate composite index for pagination queries
ALTER TABLE t1 ADD INDEX idx_page (gmt_create, id);

-- Correct: Pagination with filter conditions, index includes filter column
ALTER TABLE t1 ADD INDEX idx_page_c1 (c1, gmt_create, id);
SELECT * FROM t1
WHERE c1 = 'value' AND (gmt_create, id) > (?, ?)
ORDER BY gmt_create, id
LIMIT 1000;

-- Correct: Pagination queries must explicitly specify ORDER BY
SELECT * FROM t1 WHERE id > 12345 ORDER BY id LIMIT 1000;

INCORRECT

-- Error: Using LIMIT OFFSET for deep pagination, cost O(M+N), gets slower as you go deeper
SELECT * FROM t1 ORDER BY gmt_create LIMIT 1000000, 1000;
-- Must scan 1,001,000 records to return 1,000 rows

-- Error: When sort columns may have duplicates, using only > will lose data
SELECT * FROM t1
WHERE gmt_create > '2025-01-01 00:00:00'
ORDER BY gmt_create
LIMIT 1000;
-- If multiple records have gmt_create = '2025-01-01 00:00:00', some will be skipped

-- Error: When sort columns may have duplicates, using only >= will have duplicate data
SELECT * FROM t1
WHERE gmt_create >= '2025-01-01 00:00:00'
ORDER BY gmt_create
LIMIT 1000;
-- Records already returned in the previous batch will be returned again

-- Error: Pagination query without ORDER BY, return order is undefined
SELECT * FROM t1 WHERE id > 12345 LIMIT 1000;
-- In distributed databases, data return order from different shards is random, results are unreliable

-- Error: No index on pagination sort columns, each query requires full table scan and sort
SELECT * FROM t1
WHERE (gmt_create, id) > (?, ?)
ORDER BY gmt_create, id
LIMIT 1000;
-- Without a (gmt_create, id) composite index, performance is poor

14. Range Partition Auto-Add Partitions

CORRECT

-- Correct: Configure auto-add partitions for time-type Range partition table (add-only)
ALTER TABLE t_order
MODIFY TTL SET
  TTL_ENABLE = 'ON',
  TTL_CLEANUP = 'OFF',
  TTL_EXPR = `gmt_created`,
  TTL_PART_INTERVAL = INTERVAL(1, MONTH),
  ARCHIVE_TYPE = 'PARTITION',
  ARCHIVE_TABLE_PRE_ALLOCATE = 2;

-- Correct: Immediately trigger pre-creation after configuration, WITH TTL_CLEANUP='OFF' forces add-only
ALTER TABLE t_order CLEANUP EXPIRED DATA WITH TTL_CLEANUP = 'OFF';

-- Correct: Second-level Range subpartitions use ARCHIVE_TYPE = 'SUBPARTITION'
ALTER TABLE t_event
MODIFY TTL SET
  TTL_ENABLE = 'ON',
  TTL_CLEANUP = 'OFF',
  TTL_EXPR = `created_at`,
  TTL_PART_INTERVAL = INTERVAL(1, MONTH),
  ARCHIVE_TYPE = 'SUBPARTITION',
  ARCHIVE_TABLE_PRE_ALLOCATE = 2;

-- Correct: Range partition table primary key includes partition column
CREATE TABLE t_order (
  id BIGINT AUTO_INCREMENT,
  gmt_created DATETIME NOT NULL,
  PRIMARY KEY (id, gmt_created)
) PARTITION BY RANGE COLUMNS(`gmt_created`) (
  PARTITION p20250401 VALUES LESS THAN ('2025-04-01'),
  PARTITION p20250501 VALUES LESS THAN ('2025-05-01')
);

INCORRECT

-- Error: Not triggering pre-creation immediately after configuring auto-add, must wait for next day's scheduled task
ALTER TABLE t_order
MODIFY TTL SET
  TTL_ENABLE = 'ON',
  TTL_CLEANUP = 'OFF',
  TTL_EXPR = `gmt_created`,
  TTL_PART_INTERVAL = INTERVAL(1, MONTH),
  ARCHIVE_TYPE = 'PARTITION',
  ARCHIVE_TABLE_PRE_ALLOCATE = 2;
-- Missing: ALTER TABLE t_order CLEANUP EXPIRED DATA WITH TTL_CLEANUP = 'OFF';
-- Customer's table may only have initial partitions; without triggering, must wait until 02:00 next day

-- Error: Manual trigger without WITH TTL_CLEANUP='OFF', may accidentally drop old partitions
ALTER TABLE t_order CLEANUP EXPIRED DATA;
-- If the table's TTL_CLEANUP = 'ON', expired partitions will be dropped simultaneously
-- Should use: ALTER TABLE t_order CLEANUP EXPIRED DATA WITH TTL_CLEANUP = 'OFF';

-- Error: Using auto-add partitions with integer-type partition column (not supported)
ALTER TABLE t_int_range
MODIFY TTL SET
  TTL_ENABLE = 'ON',
  TTL_EXPR = `int_col`,
  ARCHIVE_TYPE = 'PARTITION';
-- Auto-add partitions only supports DATE/DATETIME/TIMESTAMP type partition columns

-- Error: Second-level Range subpartitions using ARCHIVE_TYPE = 'PARTITION'
ALTER TABLE t_event
MODIFY TTL SET
  TTL_ENABLE = 'ON',
  TTL_EXPR = `created_at`,
  ARCHIVE_TYPE = 'PARTITION';
-- Second-level subpartitions should use ARCHIVE_TYPE = 'SUBPARTITION'

PolarDB-X Range Partition Auto-Add Partitions

PolarDB-X leverages the TTL mechanism to provide automatic partition pre-creation for Range partitioned tables. Scheduled tasks automatically pre-create future Range partitions, preventing write failures due to insufficient partitions.

Only applicable to time-type partition columns (DATE / DATETIME / TIMESTAMP) Range partitioned tables. Auto-add partitions is not supported for integer-type partition columns (integer columns use the EXPIRE OVER strategy, which requires expired partitions to be cleaned up before new ones can be added — incompatible with add-only scenarios).

Core Parameters

TTL_EXPR (Partition Column)

Specify the partition column associated with auto-add partitions:

TTL_EXPR = `partition_column`

Only the partition column name needs to be declared; no need to specify EXPIRE AFTER or TIMEZONE.

TTL_PART_INTERVAL (Partition Interval)

Define the time interval between adjacent Range partitions:

TTL_PART_INTERVAL = INTERVAL(int_value, interval_unit)

• interval_unit supports only: DAY / MONTH / YEAR.
• If not specified, defaults to monthly: INTERVAL(1, MONTH).

ARCHIVE_TABLE_PRE_ALLOCATE (Pre-creation Count)

Number of Range partitions pre-created by the scheduled task:

ARCHIVE_TABLE_PRE_ALLOCATE = int_number

Default values vary by partition interval:

TTL_CLEANUP (Whether to Clean Up Expired Partitions)

TTL_CLEANUP = 'ON' | 'OFF'

• OFF (recommended): Only pre-create new partitions; do not clean up old partitions. Suitable for add-only scenarios.
• ON: Scheduled task automatically drops expired partitions.

TTL_JOB (Schedule)

No need to specify explicitly; use system defaults. Defaults to starting at 02:00 UTC+8 daily.

First-Level Range Partition — Monthly Partitions

Applicable to scenarios where the time column is the first-level Range partition key:

-- 1. Create table: first-level RANGE COLUMNS partition
CREATE TABLE t_order (
  id BIGINT AUTO_INCREMENT,
  user_id BIGINT,
  amount DECIMAL(10,2),
  gmt_created DATETIME NOT NULL,
  PRIMARY KEY (id, gmt_created)
)
PARTITION BY RANGE COLUMNS(`gmt_created`) (
  PARTITION p20250401 VALUES LESS THAN ('2025-04-01'),
  PARTITION p20250501 VALUES LESS THAN ('2025-05-01'),
  PARTITION p20250601 VALUES LESS THAN ('2025-06-01')
);

-- 2. Configure TTL auto-add partitions (monthly, pre-create 2 months)
ALTER TABLE t_order
MODIFY TTL SET
  TTL_ENABLE = 'ON',
  TTL_CLEANUP = 'OFF',
  TTL_EXPR = `gmt_created`,
  TTL_PART_INTERVAL = INTERVAL(1, MONTH),
  ARCHIVE_TYPE = 'PARTITION',
  ARCHIVE_TABLE_PRE_ALLOCATE = 2;

-- 3. Immediately trigger auto-add partitions (WITH TTL_CLEANUP='OFF' forces add-only)
ALTER TABLE t_order CLEANUP EXPIRED DATA WITH TTL_CLEANUP = 'OFF';

Notes:

• TTL_CLEANUP = 'OFF' ensures partitions are only added, never dropped.
• Step 3 must be executed: the customer's table may only have initial time-range partitions; manually triggering immediately pre-creates missing future partitions without waiting for the next day's scheduled task.
• WITH TTL_CLEANUP = 'OFF' forces add-only at the statement level, even if the table's TTL definition has TTL_CLEANUP = 'ON', preventing accidental deletion of old partitions.

First-Level Range Partition — Daily Partitions

-- 1. Create table
CREATE TABLE t_log (
  id BIGINT AUTO_INCREMENT,
  log_content TEXT,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE COLUMNS(`created_at`) (
  PARTITION p20250601 VALUES LESS THAN ('2025-06-01'),
  PARTITION p20250602 VALUES LESS THAN ('2025-06-02'),
  PARTITION p20250603 VALUES LESS THAN ('2025-06-03')
);

-- 2. Configure TTL auto-add partitions (daily, pre-create 7 days)
ALTER TABLE t_log
MODIFY TTL SET
  TTL_ENABLE = 'ON',
  TTL_CLEANUP = 'OFF',
  TTL_EXPR = `created_at`,
  TTL_PART_INTERVAL = INTERVAL(1, DAY),
  ARCHIVE_TYPE = 'PARTITION',
  ARCHIVE_TABLE_PRE_ALLOCATE = 7;

-- 3. Immediately trigger auto-add partitions
ALTER TABLE t_log CLEANUP EXPIRED DATA WITH TTL_CLEANUP = 'OFF';

Second-Level Range Subpartitions

Applicable to scenarios where the first level uses KEY/HASH for data distribution and the second level uses Range for time-based rolling management:

-- 1. Create table: KEY first-level + RANGE COLUMNS second-level
CREATE TABLE t_event (
  id BIGINT AUTO_INCREMENT,
  app_id BIGINT,
  payload TEXT,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)
)
PARTITION BY KEY(`id`) PARTITIONS 8
SUBPARTITION BY RANGE COLUMNS(`created_at`) (
  SUBPARTITION sp20250401 VALUES LESS THAN ('2025-04-01'),
  SUBPARTITION sp20250501 VALUES LESS THAN ('2025-05-01'),
  SUBPARTITION sp20250601 VALUES LESS THAN ('2025-06-01')
);

-- 2. Configure TTL auto-add subpartitions (monthly, pre-create 2 months)
ALTER TABLE t_event
MODIFY TTL SET
  TTL_ENABLE = 'ON',
  TTL_CLEANUP = 'OFF',
  TTL_EXPR = `created_at`,
  TTL_PART_INTERVAL = INTERVAL(1, MONTH),
  ARCHIVE_TYPE = 'SUBPARTITION',
  ARCHIVE_TABLE_PRE_ALLOCATE = 2;

-- 3. Immediately trigger auto-add partitions
ALTER TABLE t_event CLEANUP EXPIRED DATA WITH TTL_CLEANUP = 'OFF';

Notes:

• ARCHIVE_TYPE = 'SUBPARTITION' specifies that the automatically managed partitions are templated second-level Range subpartitions.
• First-level KEY partitions remain unchanged; second-level Range subpartitions roll automatically.

Managing Auto-Add Partition Configuration

-- View TTL configuration
SHOW CREATE TABLE t_order;

-- Adjust pre-creation count
ALTER TABLE t_order MODIFY TTL SET ARCHIVE_TABLE_PRE_ALLOCATE = 6;

-- Adjust partition interval
ALTER TABLE t_order MODIFY TTL SET TTL_PART_INTERVAL = INTERVAL(1, DAY);

-- Pause auto-partition task
ALTER TABLE t_order MODIFY TTL SET TTL_ENABLE = 'OFF';

-- Resume auto-partition task
ALTER TABLE t_order MODIFY TTL SET TTL_ENABLE = 'ON';

-- Enable automatic cleanup of expired partitions
ALTER TABLE t_order MODIFY TTL SET TTL_CLEANUP = 'ON';

-- Disable cleanup (add-only, no delete)
ALTER TABLE t_order MODIFY TTL SET TTL_CLEANUP = 'OFF';

-- Manually trigger auto-add partitions (WITH TTL_CLEANUP='OFF' forces add-only)
ALTER TABLE t_order CLEANUP EXPIRED DATA WITH TTL_CLEANUP = 'OFF';

-- Remove TTL definition (if an archive table exists, drop it first)
ALTER TABLE t_order REMOVE TTL;

Limitations

• Only supports time-type partition columns: DATE / DATETIME / TIMESTAMP. Integer-type partition columns are not supported (the EXPIRE OVER strategy requires old partitions to be cleaned up before adding new ones, conflicting with add-only scenarios).
• Only supports partitioned tables in AUTO mode databases.
• Partition-based archiving requires Enterprise Edition version 5.4.20+ (MySQL 5.7 requires polardb-2.5.0_5.4.20-20250328 or later).
• Tables with partition-based archiving that have JOIN relationships with other tables may experience JOIN computation pushdown failures due to partition definition misalignment.
• When TTL_CLEANUP = 'OFF', new partitions are still automatically added, but expired partitions are not dropped.
• After configuration, immediately execute ALTER TABLE xxx CLEANUP EXPIRED DATA WITH TTL_CLEANUP = 'OFF' to trigger pre-creation, instead of waiting for the next day's scheduled task. WITH TTL_CLEANUP = 'OFF' forces add-only at the statement level.
• Before removing a TTL definition, if an associated archive table exists, it must be dropped first.

PolarDB-X Clustered Columnar Index (CCI)

The Clustered Columnar Index (CCI) is a row-column hybrid storage capability provided by PolarDB-X Enterprise Edition. CCI is essentially a columnar clustered index based on object storage that stores all columns from the primary table by default in columnar format, designed to accelerate OLAP analytical queries.

Applicable Scenarios

• Wide table multi-column aggregation queries (SUM/COUNT/AVG, etc.).
• Complex reports and data analysis.
• Scenarios requiring both OLTP and OLAP on the same data (HTAP).
• Combined with TTL tables for hot/cold data separation.

Creation Syntax

Create during table creation

CREATE TABLE t_order (
  order_id BIGINT PRIMARY KEY,
  buyer_id BIGINT,
  seller_id BIGINT,
  amount DECIMAL(10,2),
  create_time DATETIME,
  CLUSTERED COLUMNAR INDEX cci_seller(seller_id)
    PARTITION BY KEY(seller_id) PARTITIONS 16
) PARTITION BY KEY(order_id) PARTITIONS 16;

Add to an existing table

-- Using CREATE INDEX
CREATE CLUSTERED COLUMNAR INDEX cci_buyer
  ON t_order(buyer_id)
  PARTITION BY KEY(buyer_id) PARTITIONS 16;

-- Using ALTER TABLE
ALTER TABLE t_order ADD CLUSTERED COLUMNAR INDEX cci_buyer(buyer_id)
  PARTITION BY KEY(buyer_id) PARTITIONS 16;

Query Usage

The PolarDB-X optimizer can automatically select CCI for analytical queries, or you can specify it manually:

-- Automatic selection (optimizer decides based on cost model)
SELECT seller_id, SUM(amount) FROM t_order
GROUP BY seller_id ORDER BY SUM(amount) DESC LIMIT 10;

-- Manually specify CCI
SELECT /*+TDDL:FORCE_INDEX(t_order, cci_seller)*/ seller_id, SUM(amount)
FROM t_order GROUP BY seller_id;

-- Using FORCE INDEX
SELECT seller_id, SUM(amount) FROM t_order FORCE INDEX(cci_seller)
GROUP BY seller_id;

View CCI Information

SHOW COLUMNAR INDEX;

Relationship with GSI

CCI is essentially the columnar version of Clustered GSI:

• Clustered GSI: Row-store format, suitable for point queries and small range scans.
• CCI: Columnar format, suitable for large range scans and aggregation analysis.

Both store all primary table columns by default, but differ in storage format and applicable query types.

Combined with TTL Tables

CCI can be combined with TTL tables for hot/cold data separation: hot data resides in the row-store partitioned table, while cold data is archived to the columnar CCI (based on object storage), reducing storage costs while retaining analytical query capabilities.

Limitations

• Only supported by PolarDB-X Enterprise Edition (Distributed Edition).
• CCI data is stored on object storage; writes have some latency (eventually consistent).
• Creating CCI is an online operation that does not block DML.

Aliyun CLI Installation & Configuration Guide

Complete guide for installing and configuring Aliyun CLI.

Aliyun CLI 3.3.1+: Supports installing and using all published Alibaba Cloud product plugins. Make sure to upgrade to 3.3.1 or later for full plugin ecosystem coverage.

Installation

macOS

Using Homebrew (Recommended)

brew install aliyun-cli
# Upgrade to latest
brew upgrade aliyun-cli

# Verify version (>= 3.3.1)
aliyun version

Using Binary

# Download
wget https://aliyuncli.alicdn.com/aliyun-cli-macosx-latest-amd64.tgz

# Extract
tar -xzf aliyun-cli-macosx-latest-amd64.tgz

# Move to PATH
sudo mv aliyun /usr/local/bin/

# Verify
aliyun version

Linux

Debian/Ubuntu

# Download
wget https://aliyuncli.alicdn.com/aliyun-cli-linux-latest-amd64.tgz

# Extract and install
tar -xzf aliyun-cli-linux-latest-amd64.tgz
sudo mv aliyun /usr/local/bin/

# Verify
aliyun version

CentOS/RHEL

# Download
wget https://aliyuncli.alicdn.com/aliyun-cli-linux-latest-amd64.tgz

# Extract and install
tar -xzf aliyun-cli-linux-latest-amd64.tgz
sudo mv aliyun /usr/local/bin/

# Verify
aliyun version

ARM64 Architecture

# Download ARM64 version
wget https://aliyuncli.alicdn.com/aliyun-cli-linux-latest-arm64.tgz

# Extract and install
tar -xzf aliyun-cli-linux-latest-arm64.tgz
sudo mv aliyun /usr/local/bin/

Windows

Using Binary 1. Download from: https://aliyuncli.alicdn.com/aliyun-cli-windows-latest-amd64.zip 2. Extract the ZIP file 3. Add the directory to your PATH environment variable 4. Open new Command Prompt or PowerShell 5. Verify: aliyun version

Using PowerShell

# Download
Invoke-WebRequest -Uri "https://aliyuncli.alicdn.com/aliyun-cli-windows-latest-amd64.zip" -OutFile "aliyun-cli.zip"

# Extract
Expand-Archive -Path aliyun-cli.zip -DestinationPath C:\aliyun-cli

# Add to PATH (requires admin privileges)
$env:Path += ";C:\aliyun-cli"
[Environment]::SetEnvironmentVariable("Path", $env:Path, [System.EnvironmentVariableTarget]::Machine)

# Verify
aliyun version

Configuration

Quick Start

aliyun configure set \
  --mode AK \
  --access-key-id <your-access-key-id> \
  --access-key-secret <your-access-key-secret> \
  --region cn-hangzhou

All aliyun configure commands support non-interactive flags, which is the recommended approach — it works in scripts, CI/CD pipelines, and agent-driven automation without hanging on stdin prompts.

Where to Get Access Keys

1. Log in to Aliyun Console: https://ram.console.aliyun.com/ 2. Navigate to: AccessKey Management 3. Create a new AccessKey pair 4. Save the secret immediately — it's only shown once

Configuration Modes

Aliyun CLI supports 6 authentication modes. All examples below use non-interactive flags.

1. AK Mode (Access Key)

Most common mode for personal accounts and scripts.

aliyun configure set \
  --mode AK \
  --access-key-id LTAI5tXXXXXXXX \
  --access-key-secret 8dXXXXXXXXXXXXXXXXXXXXXXXX \
  --region cn-hangzhou

Configuration is stored in ~/.aliyun/config.json:

{
  "current": "default",
  "profiles": [
    {
      "name": "default",
      "mode": "AK",
      "access_key_id": "LTAI5tXXXXXXXX",
      "access_key_secret": "8dXXXXXXXXXXXXXXXXXXXXXXXX",
      "region_id": "cn-hangzhou",
      "output_format": "json",
      "language": "en"
    }
  ]
}

2. StsToken Mode (Temporary Credentials)

For short-lived access (tokens expire in 1-12 hours).

aliyun configure set \
  --mode StsToken \
  --access-key-id LTAI5tXXXXXXXX \
  --access-key-secret 8dXXXXXXXXXXXXXXXXXXXXXXXX \
  --sts-token v1.0:XXXXXXXXXXXXXXXX \
  --region cn-hangzhou

Use cases: CI/CD pipelines, temporary access for external contractors, cross-account access.

3. RamRoleArn Mode (Assume RAM Role)

Assume a RAM role for elevated or cross-account access.

aliyun configure set \
  --mode RamRoleArn \
  --access-key-id LTAI5tXXXXXXXX \
  --access-key-secret 8dXXXXXXXXXXXXXXXXXXXXXXXX \
  --ram-role-arn acs:ram::123456789012:role/AdminRole \
  --role-session-name my-session \
  --region cn-hangzhou

Use cases: cross-account resource access, temporary elevated privileges, role-based access control.

4. EcsRamRole Mode (ECS Instance RAM Role)

Use the RAM role attached to an ECS instance — no credentials needed.

aliyun configure set \
  --mode EcsRamRole \
  --ram-role-name MyEcsRole \
  --region cn-hangzhou

Requirements: must be running on an ECS instance with a RAM role attached.

Use cases: scripts and automation running on ECS instances.

5. RsaKeyPair Mode (RSA Key Pair)

Use RSA key pair for authentication (generate key pair in Aliyun Console first).

aliyun configure set \
  --mode RsaKeyPair \
  --private-key /path/to/private-key.pem \
  --key-pair-name my-key-pair \
  --region cn-hangzhou

6. RamRoleArnWithEcs Mode (ECS + RAM Role)

Combine ECS instance role with RAM role assumption for cross-account access from ECS.

aliyun configure set \
  --mode RamRoleArnWithEcs \
  --ram-role-name MyEcsRole \
  --ram-role-arn acs:ram::123456789012:role/TargetRole \
  --role-session-name my-session \
  --region cn-hangzhou

Environment Variables

Highest priority - overrides config file

Access Key Mode

export ALIBABA_CLOUD_ACCESS_KEY_ID=your_access_key_id
export ALIBABA_CLOUD_ACCESS_KEY_SECRET=your_access_key_secret
export ALIBABA_CLOUD_REGION_ID=cn-hangzhou

STS Token Mode

export ALIBABA_CLOUD_ACCESS_KEY_ID=your_access_key_id
export ALIBABA_CLOUD_ACCESS_KEY_SECRET=your_access_key_secret
export ALIBABA_CLOUD_SECURITY_TOKEN=your_sts_token
export ALIBABA_CLOUD_REGION_ID=cn-hangzhou

ECS RAM Role Mode

export ALIBABA_CLOUD_ECS_METADATA=role_name

Use Case:

• CI/CD pipelines
• Docker containers
• Temporary credential override

Managing Multiple Profiles

Create Named Profiles

aliyun configure set --profile projectA \
  --mode AK \
  --access-key-id LTAI5tAAAAAAAA \
  --access-key-secret 8dAAAAAAAAAAAAAAAAAAAAAAAA \
  --region cn-hangzhou

aliyun configure set --profile projectB \
  --mode AK \
  --access-key-id LTAI5tBBBBBBBB \
  --access-key-secret 8dBBBBBBBBBBBBBBBBBBBBBBBB \
  --region cn-shanghai

Use Specific Profile

aliyun ecs describe-instances --profile projectA

export ALIBABA_CLOUD_PROFILE=projectA
aliyun ecs describe-instances   # Uses projectA

List and Switch Profiles

aliyun configure list                      # List all profiles
aliyun configure set --current projectA    # Switch default profile

Credential Priority

Credentials are loaded in this order (first found wins):

1. Command-line flag: --profile <name> 2. Environment variable: ALIBABA_CLOUD_PROFILE 3. Environment credentials: ALIBABA_CLOUD_ACCESS_KEY_ID, etc. 4. Configuration file: ~/.aliyun/config.json (current profile) 5. ECS Instance RAM Role: If running on ECS with attached role

Verification

Test Authentication

# Basic test - list regions
aliyun ecs describe-regions

# Expected output: JSON array of regions

If successful, you'll see:

{
  "Regions": {
    "Region": [
      {
        "RegionId": "cn-hangzhou",
        "RegionEndpoint": "ecs.cn-hangzhou.aliyuncs.com",
        "LocalName": "华东 1（杭州）"
      },
      ...
    ]
  },
  "RequestId": "..."
}

If failed, you'll see error messages:

• InvalidAccessKeyId.NotFound - Wrong Access Key ID
• SignatureDoesNotMatch - Wrong Access Key Secret
• InvalidSecurityToken.Expired - STS token expired (for StsToken mode)
• Forbidden.RAM - Insufficient permissions

Debug Configuration

# Show current configuration
aliyun configure get

# Test with debug logging
aliyun ecs describe-regions --log-level=debug

# Check credential provider
aliyun configure get mode

Security Best Practices

1. Use RAM Users (Not Root Account)

❌ Don't: Use Aliyun root account credentials ✅ Do: Create RAM users with specific permissions

# Create RAM user in console
# Attach only necessary policies
# Use RAM user's access keys

2. Principle of Least Privilege

Grant only the minimum permissions needed:

# Example: Read-only ECS access
# Attach policy: AliyunECSReadOnlyAccess

3. Rotate Access Keys Regularly

# Create new access key in RAM Console, then update configuration
aliyun configure set --access-key-id NEW_KEY --access-key-secret NEW_SECRET
# Delete old access key from console

4. Use STS Tokens for Temporary Access

aliyun configure set --mode StsToken \
  --access-key-id XXXX --access-key-secret XXXX \
  --sts-token XXXX --region cn-hangzhou

5. Use ECS RAM Roles When Possible

aliyun configure set --mode EcsRamRole --ram-role-name MyRole --region cn-hangzhou

6. Never Commit Credentials

# Add to .gitignore
echo "~/.aliyun/config.json" >> .gitignore

# Use environment variables in CI/CD instead

7. Secure Config File

# Restrict permissions
chmod 600 ~/.aliyun/config.json

Troubleshooting

Issue: Command Not Found

# Check installation
which aliyun

# Check PATH
echo $PATH

# Reinstall or add to PATH

Issue: Authentication Failed

# Verify configuration
aliyun configure get

# Test with debug
aliyun ecs describe-regions --log-level=debug

# Check credentials in console
# Verify access key is active

Issue: Permission Denied

# Error: Forbidden.RAM

# Check RAM user permissions
# Attach necessary policies in RAM console
# Example: AliyunECSFullAccess for ECS operations

Issue: STS Token Expired

# Error: InvalidSecurityToken.Expired

# Reconfigure with new token
aliyun configure set --mode StsToken \
  --access-key-id XXXX --access-key-secret XXXX \
  --sts-token NEW_TOKEN --region cn-hangzhou

Issue: Wrong Region

# Some resources may not exist in the specified region

# Check available regions
aliyun ecs describe-regions

# Update default region
aliyun configure set region cn-shanghai

Advanced Configuration

Custom Endpoint

# Use custom or private endpoint
export ALIBABA_CLOUD_ECS_ENDPOINT=ecs-vpc.cn-hangzhou.aliyuncs.com

Proxy Settings

# HTTP proxy
export HTTP_PROXY=http://proxy.example.com:8080
export HTTPS_PROXY=http://proxy.example.com:8080

# No proxy for specific domains
export NO_PROXY=localhost,127.0.0.1,.aliyuncs.com

Timeout Settings

# Connection timeout (default: 10s)
export ALIBABA_CLOUD_CONNECT_TIMEOUT=30

# Read timeout (default: 10s)
export ALIBABA_CLOUD_READ_TIMEOUT=30

Next Steps

After installation and configuration:

1. Install plugins for services you need (v3.3.1+ supports all published product plugins):

   aliyun plugin install --names ecs vpc rds

   # List all available plugins
   aliyun plugin list-remote

2. Explore commands:

   aliyun ecs --help
   aliyun fc --help

3. Read documentation:

• Command Syntax Guide
• Global Flags Reference
• Common Scenarios

References

• Official Documentation: https://help.aliyun.com/zh/cli/
• RAM Console: https://ram.console.aliyun.com/
• Access Key Management: https://ram.console.aliyun.com/manage/ak
• Plugin Repository: https://github.com/aliyun/aliyun-cli

PolarDB-X CREATE TABLE

PolarDB-X Distributed Edition supports three table types: single tables, broadcast tables, and partitioned tables. Partitioned tables are the default type, where data is horizontally split across multiple storage nodes (DN) according to partition rules.

Three Table Types

Single Table (SINGLE)

Data is stored on a single DN, suitable for small tables that don't require distribution.

CREATE TABLE config_tbl (
  id BIGINT PRIMARY KEY,
  key_name VARCHAR(64),
  value TEXT
) SINGLE;

Broadcast Table (BROADCAST)

Data is fully replicated to every DN, suitable for small dictionary tables that need frequent JOINs.

CREATE TABLE region_dict (
  region_id INT PRIMARY KEY,
  region_name VARCHAR(64)
) BROADCAST;

Partitioned Table (Default)

Data is distributed across multiple DNs according to partition rules, suitable for large business tables.

First-Level Partition Types

KEY Partition

Similar to MySQL's KEY partition, routes based on column value hashing. The most commonly used partition type:

CREATE TABLE t_order (
  order_id BIGINT PRIMARY KEY,
  user_id BIGINT,
  amount DECIMAL(10,2)
) PARTITION BY KEY(order_id) PARTITIONS 16;

Vector partition key (multi-column routing):

CREATE TABLE t_item (
  order_id BIGINT,
  item_id BIGINT,
  product_name VARCHAR(128),
  PRIMARY KEY (order_id, item_id)
) PARTITION BY KEY(order_id, item_id) PARTITIONS 16;

HASH Partition

Supports partition functions that can compute on column values before hashing:

-- Hash by year
CREATE TABLE t_event (
  id BIGINT PRIMARY KEY,
  event_date DATE
) PARTITION BY HASH(YEAR(event_date)) PARTITIONS 8;

-- Hash by day (using TO_DAYS)
CREATE TABLE t_log (
  id BIGINT PRIMARY KEY,
  created_at DATETIME
) PARTITION BY HASH(TO_DAYS(created_at)) PARTITIONS 16;

Note: Vector partition keys do not support partition functions; for timezone-sensitive time columns, use UNIX_TIMESTAMP().

CO_HASH Partition

A PolarDB-X-specific joint hash partition where multiple columns participate in routing. An equality condition on any single column can achieve partition pruning:

CREATE TABLE t_order (
  order_id BIGINT,
  buyer_id BIGINT,
  seller_id BIGINT,
  PRIMARY KEY (order_id)
) PARTITION BY CO_HASH(
  RIGHT(order_id, 4),
  RIGHT(buyer_id, 4)
) PARTITIONS 16;

RANGE / RANGE COLUMNS Partition

Partitions by range, suitable for time series or continuous numeric data:

CREATE TABLE t_sales (
  id BIGINT PRIMARY KEY,
  sale_date DATE,
  amount DECIMAL(10,2)
) PARTITION BY RANGE COLUMNS(sale_date) (
  PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
  PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
  PARTITION p2025 VALUES LESS THAN ('2026-01-01'),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

LIST / LIST COLUMNS Partition

Partitions by discrete value lists, suitable for enumeration-type fields:

CREATE TABLE t_regional_order (
  id BIGINT PRIMARY KEY,
  region VARCHAR(20),
  amount DECIMAL(10,2)
) PARTITION BY LIST COLUMNS(region) (
  PARTITION p_east VALUES IN ('shanghai', 'hangzhou', 'nanjing'),
  PARTITION p_north VALUES IN ('beijing', 'tianjin'),
  PARTITION p_south VALUES IN ('guangzhou', 'shenzhen')
);

Secondary Partitions (SUBPARTITION)

PolarDB-X supports secondary partitions, where first-level and second-level partitions can be freely combined (49 combinations).

Templated Secondary Partitions

All first-level partitions use the same secondary partition definition:

CREATE TABLE t_order_detail (
  id BIGINT PRIMARY KEY,
  order_date DATE,
  user_id BIGINT,
  amount DECIMAL(10,2)
) PARTITION BY RANGE COLUMNS(order_date)
  SUBPARTITION BY KEY(user_id) SUBPARTITIONS 4
(
  PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
  PARTITION p2025 VALUES LESS THAN ('2026-01-01'),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

Non-Templated Secondary Partitions

Each first-level partition can have a different number of secondary partitions:

CREATE TABLE t_sales_detail (
  id BIGINT PRIMARY KEY,
  region VARCHAR(20),
  user_id BIGINT
) PARTITION BY LIST COLUMNS(region)
  SUBPARTITION BY KEY(user_id)
(
  PARTITION p_east VALUES IN ('shanghai', 'hangzhou') SUBPARTITIONS 8,
  PARTITION p_north VALUES IN ('beijing', 'tianjin') SUBPARTITIONS 4
);

Partition Management Operations

-- Add partition (applicable to RANGE/LIST)
ALTER TABLE t_sales ADD PARTITION (
  PARTITION p2026 VALUES LESS THAN ('2027-01-01')
);

-- Drop partition
ALTER TABLE t_sales DROP PARTITION p2023;

-- Split partition (split one partition into multiple)
ALTER TABLE t_order SPLIT PARTITION p0 INTO (
  PARTITION p0a,
  PARTITION p0b
);

-- Merge partitions
ALTER TABLE t_order MERGE PARTITIONS p0a, p0b TO p0;

-- Move partition to a specific DN
ALTER TABLE t_order MOVE PARTITIONS p0 TO 'dn-1';

Partition Key Selection Principles

• Choose columns from the most frequent query conditions as partition keys to avoid full-shard scans.
• Choose columns with even data distribution to avoid hotspot partitions.
• If there are multiple high-frequency query dimensions, use CO_HASH or create Global Secondary Indexes (GSI).
• Each table supports a maximum of 8192 partitions.

Limitations

• Partition keys do not support JSON type columns.
• Partition keys do not support GEOMETRY type columns.
• Single-column HASH partitions can use partition functions; vector partition keys cannot.
• Secondary partitioned tables do not support SPLIT/MERGE/ADD/DROP SUBPARTITION.

PolarDB-X EXPLAIN Execution Plan Diagnostics

PolarDB-X provides a rich set of EXPLAIN command variants for viewing and analyzing SQL execution plans. Unlike MySQL, PolarDB-X execution plans are divided into two layers: CN (Compute Node) logical plans and DN (Data Node) physical plans.

Full Syntax

EXPLAIN {option} <SQL statement>

Supported options: LOGICALVIEW | LOGIC | SIMPLE | DETAIL | EXECUTE | PHYSICAL | OPTIMIZER | SHARDING | COST | ANALYZE | BASELINE | JSON_PLAN

Common Options

EXPLAIN (Default)

View the CN layer logical execution plan:

EXPLAIN SELECT * FROM t_order WHERE buyer_id = 12345;

Key parameters in the output:

• HitCache: Whether PlanCache was hit (true/false).
• TemplateId: Globally unique identifier for the query plan.
• Source: Plan source (e.g., PLAN_CACHE).
• WorkloadType: Workload type (e.g., TP, AP).

EXPLAIN EXECUTE

View the physical execution plan pushed down to DN (similar to MySQL's EXPLAIN), for quickly diagnosing index usage:

EXPLAIN EXECUTE SELECT * FROM t_order WHERE buyer_id = 12345;

Aggregates execution plan information from all DNs by default, with differences noted in the Extra column:

• Same plan / Different plan indicates whether execution plans are consistent across DNs.
• Scan rows shows scan row count statistics.

EXPLAIN ANALYZE

Actually executes the SQL and collects runtime statistics (note: this will actually execute the query):

EXPLAIN ANALYZE SELECT * FROM t_order WHERE buyer_id = 12345;

Outputs additional rowCount, execution time, and other runtime information for comparing estimated vs. actual row counts.

EXPLAIN SHARDING

View the shard scan pattern of a query on DNs to determine if a full-shard scan is occurring:

EXPLAIN SHARDING SELECT * FROM t_order WHERE buyer_id = 12345;

If all shards are being scanned, the query condition does not hit the partition key — consider adding a GSI.

EXPLAIN COST

View cost estimation for each operator and WORKLOAD type identification:

EXPLAIN COST SELECT * FROM t_order WHERE buyer_id = 12345;

EXPLAIN PHYSICAL

View execution mode, Fragment dependencies, and parallelism:

EXPLAIN PHYSICAL SELECT * FROM t_order WHERE buyer_id = 12345;

DN-Level EXPLAIN Variants

Requires a newer version (polardb-2.5.0_5.4.20+).

EXPLAIN DIFF_EXECUTE

Shows only DN execution plans with differences, for quickly locating problematic DNs:

EXPLAIN DIFF_EXECUTE SELECT * FROM t_order WHERE buyer_id = 12345;

EXPLAIN ALL_EXECUTE

Shows detailed execution plans for all DNs:

EXPLAIN ALL_EXECUTE SELECT * FROM t_order WHERE buyer_id = 12345;

EXPLAIN TREE_EXECUTE

Displays DN execution plans in a tree structure:

EXPLAIN TREE_EXECUTE SELECT * FROM t_order WHERE buyer_id = 12345;

EXPLAIN JSON_EXECUTE

Outputs DN optimizer information in JSON format:

EXPLAIN JSON_EXECUTE SELECT * FROM t_order WHERE buyer_id = 12345;

EXPLAIN ANALYZE_EXECUTE

Actually executes the SQL and shows DN-level execution statistics:

EXPLAIN ANALYZE_EXECUTE SELECT * FROM t_order WHERE buyer_id = 12345;

HINT Assistance

-- View DN execution plans at the physical sub-table level
/*+TDDL:EXPLAIN_EXECUTE_PHYTB_LEVEL=2*/
EXPLAIN EXECUTE SELECT * FROM t_order WHERE buyer_id = 12345;

Diagnostic Recommendations

• If EXPLAIN shows a full-shard scan, check whether the query condition includes the partition key or GSI key.
• If EXPLAIN EXECUTE shows Different plan, data skew may be causing some DNs to choose different execution plans.
• If estimated row counts differ significantly from actual row counts, consider running ANALYZE TABLE to refresh statistics.
• If EXPLAIN SHARDING shows too many shards being scanned, consider optimizing the partition strategy or adding a GSI.

PolarDB-X Global Secondary Index (GSI)

A Global Secondary Index (GSI) is a special partitioned table in PolarDB-X that stores redundant copies of selected columns from the primary table, distributed across storage nodes according to a specified partition scheme. The core purpose of GSI is to solve full-shard scan issues caused by non-partition-key queries.

Three GSI Types

Global Secondary Index (GSI)

Provides a different partition scheme from the primary table. The index table contains only the index columns, primary key columns, and the primary table's partition key:

CREATE TABLE t_order (
  order_id BIGINT PRIMARY KEY,
  buyer_id BIGINT,
  seller_id BIGINT,
  order_snapshot TEXT,
  GLOBAL INDEX g_i_buyer(buyer_id) PARTITION BY KEY(buyer_id) PARTITIONS 16
) PARTITION BY KEY(order_id) PARTITIONS 16;

Global Unique Index (UGSI)

Adds a global uniqueness constraint on top of GSI, ensuring the index key is unique across the entire table:

CREATE TABLE t_user (
  user_id BIGINT PRIMARY KEY,
  phone VARCHAR(20),
  name VARCHAR(64),
  UNIQUE GLOBAL INDEX g_i_phone(phone) PARTITION BY KEY(phone) PARTITIONS 16
) PARTITION BY KEY(user_id) PARTITIONS 16;

Clustered Global Index (Clustered GSI)

Stores all columns from the primary table by default, avoiding table lookback queries at the cost of storage space equal to the primary table:

CREATE TABLE t_order (
  order_id BIGINT PRIMARY KEY,
  buyer_id BIGINT,
  seller_id BIGINT,
  order_info TEXT,
  create_time DATETIME,
  CLUSTERED INDEX cg_i_buyer(buyer_id) PARTITION BY KEY(buyer_id) PARTITIONS 16
) PARTITION BY KEY(order_id) PARTITIONS 16;

Creation Methods

Inline creation during table creation (recommended)

See the examples above for each type.

Add to an existing table

-- Add a regular GSI
ALTER TABLE t_order ADD GLOBAL INDEX g_i_seller(seller_id)
  PARTITION BY KEY(seller_id) PARTITIONS 16;

-- Add a Global Unique Index
ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX g_i_order_no(order_no)
  PARTITION BY KEY(order_no) PARTITIONS 16;

-- Add a Clustered Global Index
ALTER TABLE t_order ADD CLUSTERED INDEX cg_i_seller(seller_id)
  PARTITION BY KEY(seller_id) PARTITIONS 16;

Using CREATE INDEX syntax

CREATE GLOBAL INDEX g_i_seller ON t_order(seller_id)
  PARTITION BY KEY(seller_id) PARTITIONS 16;

Covering Columns (COVERING)

Use COVERING to specify additional redundant columns, reducing table lookback overhead:

CREATE TABLE t_order (
  order_id BIGINT PRIMARY KEY,
  buyer_id BIGINT,
  seller_id BIGINT,
  amount DECIMAL(10,2),
  status INT,
  GLOBAL INDEX g_i_buyer(buyer_id) COVERING(amount, status)
    PARTITION BY KEY(buyer_id) PARTITIONS 16
) PARTITION BY KEY(order_id) PARTITIONS 16;

The index table automatically includes the primary key columns and the primary table's partition key columns; no need to repeat them in COVERING.

Query Usage

The PolarDB-X optimizer can automatically select GSIs, or you can specify them manually:

-- Using FORCE INDEX
SELECT * FROM t_order FORCE INDEX(g_i_buyer)
WHERE buyer_id = 12345;

-- Using HINT
SELECT /*+TDDL:INDEX(t_order, g_i_buyer)*/ *
FROM t_order WHERE buyer_id = 12345;

Limitations

• Each table supports a maximum of 32 global indexes.
• GSI requires XA/TSO distributed transaction support.
• Direct DML (INSERT/UPDATE/DELETE) or DDL on GSI index tables is prohibited.
• TRUNCATE on tables with GSI is prohibited; use DELETE to clear data instead.
• Before dropping a column included in a GSI, the corresponding GSI must be dropped first.
• GSI write performance has additional overhead (index table data must be kept consistent).
• Creating and dropping GSIs are online operations that do not block DML.

Design Recommendations

• Prioritize creating GSIs for the most frequent non-partition-key query conditions.
• If queries need to return many columns, use Clustered GSI to avoid table lookback.
• If only a few additional columns are needed, COVERING is more storage-efficient than Clustered GSI.
• If global uniqueness constraints are needed (e.g., phone numbers, order numbers), use UGSI.

PolarDB-X and MySQL Compatibility Notes

PolarDB-X Distributed Edition (Enterprise Edition) is highly compatible with MySQL protocol and syntax, but due to distributed architecture differences, some features are unsupported or behave differently. Use this as a checklist when migrating MySQL SQL to PolarDB-X.

Detecting PolarDB-X Version

SELECT VERSION();

Distinguish instance types by the return value:

• Contains TDDL with version > 5.4.12 -> 2.0 Enterprise Edition, version number is the part after TDDL- (e.g., 5.4.19).
• Contains TDDL with version <= 5.4.12 -> DRDS 1.0, this skill does not apply.
• Contains X-Cluster -> 2.0 Standard Edition, handle with standard MySQL syntax.

Unsupported MySQL Features (Do not generate by default)

• Stored Procedures and Stored Functions
• Triggers
• Event Scheduler (Events)
• User-Defined Functions (UDF)
• SPATIAL / GEOMETRY data types, spatial functions, and spatial indexes
• LOAD XML
• HANDLER statement
• IMPORT TABLE
• INSERT DELAYED
• STRAIGHT_JOIN (use standard JOIN instead)
• NATURAL JOIN (use explicit JOIN ON instead)
• := assignment operator (move logic to application layer)
• XML functions
• GTID functions
• Full-text search functions (MySQL's FULLTEXT is not available)
• ALTER EVENT / ALTER INSTANCE / ALTER SERVER
• CREATE EVENT / DROP EVENT
• CREATE SERVER / DROP SERVER
• CREATE SPATIAL REFERENCE SYSTEM
• LOCK INSTANCE FOR BACKUP / UNLOCK INSTANCE
• Replication statements (CHANGE MASTER TO, START/STOP SLAVE, etc.)
• Group replication statements (START/STOP GROUP_REPLICATION)
• INSTALL/UNINSTALL COMPONENT/PLUGIN

Partially Supported or Behavioral Differences

Subquery Limitations

• Subqueries are not supported in `HAVING` clauses; rewrite as JOIN or CTE.
• Subqueries are not supported in `JOIN ON` clauses; extract subqueries as independent JOINs.
• Scalar subqueries with equality operators are supported normally.

DML Differences

• ON UPDATE CURRENT_TIMESTAMP behavior is not fully consistent with MySQL; it's recommended to explicitly set update times in the application layer.
• Variable reference operations (@c=1, @d=@c+1) are not supported.

SHOW Commands

• SHOW WARNINGS and SHOW ERRORS do not support LIMIT and COUNT combinations.
• HELP command is not supported.

Keyword Limitations

• MILLISECOND and MICROSECOND keywords are not supported.

Data Type Limitations

• JSON type cannot be used as a partition key.
• GEOMETRY / LINESTRING and other spatial types are not supported.

DDL Limitations

• Secondary partitioned tables do not support Merge/Split/Add/Drop Subpartition.
• Index partitioned tables do not support Merge/Split/Add/Drop.
• Foreign keys are supported.
• Generated Columns are supported.
• RENAME TABLE is supported.

Identifier Limitations

Resource Limitations

Character Sets and Collations

• Default character set: utf8mb4.
• It's recommended to explicitly specify collations to avoid relying on default behavior (PolarDB-X's default collation may differ from MySQL's).
• If case-insensitive comparison is needed, explicitly set utf8mb4_general_ci collation.

Compatible MySQL Features (Safe to Use)

• Standard DML: SELECT / INSERT / UPDATE / DELETE / REPLACE
• Transactions: BEGIN / COMMIT / ROLLBACK / SAVEPOINT
• DDL: CREATE/ALTER/DROP TABLE / CREATE/DROP INDEX / CREATE/ALTER/DROP VIEW
• Account management: CREATE/ALTER/DROP USER / GRANT / REVOKE
• Prepared statements: PREPARE / EXECUTE / DEALLOCATE PREPARE
• LOAD DATA (disabled by default, needs manual enablement)
• LOCK TABLES
• SET TRANSACTION
• Most SHOW commands

PolarDB-X Online DDL and Lock-Free DDL Operations

PolarDB-X Distributed Edition has extensively optimized DDL table-locking issues, including MDL lock preemption, MDL dual versioning, and lock-free column type changes. This document explains how to determine if a DDL locks the table, how to execute DDL in a lock-free manner, and long transaction checks before DDL execution.

EXPLAIN ONLINE_DDL — Determine If DDL Locks the Table

Before executing DDL, use EXPLAIN ONLINE_DDL to predict whether the DDL will lock the table, without actually executing the DDL.

Version requirement: Instance version >= 5.4.20-20241224.

Syntax

EXPLAIN ONLINE_DDL ALTER TABLE ...

Return Fields

DDL TYPE and ALGORITHM Reference

Examples

-- Add column: INSTANT, completes in seconds, no table lock
EXPLAIN ONLINE_DDL ALTER TABLE t1 ADD COLUMN d int;
-- Result: DDL TYPE = ONLINE_DDL, ALGORITHM = INSTANT

-- Modify column type: COPY, locks table
EXPLAIN ONLINE_DDL ALTER TABLE t1 MODIFY COLUMN c bigint;
-- Result: DDL TYPE = LOCK_TABLE, ALGORITHM = COPY

-- Add partition: META_ONLY, completes in seconds, no table lock
EXPLAIN ONLINE_DDL ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES LESS THAN (2000000));
-- Result: DDL TYPE = ONLINE_DDL, ALGORITHM = META_ONLY

Lock-Free DDL Execution Strategy

Handle based on EXPLAIN ONLINE_DDL results:

1. DDL TYPE = ONLINE_DDL: Execute the original SQL directly; it won't lock the table. 2. DDL TYPE = LOCK_TABLE: Specify ALGORITHM=OMC to enable lock-free column type change.

ALGORITHM=OMC Lock-Free Column Type Change

For ALTER TABLE operations that would lock the table (e.g., modifying column types), specify ALGORITHM=OMC in the SQL to avoid table locking:

-- Original SQL would lock the table
EXPLAIN ONLINE_DDL ALTER TABLE t1 MODIFY COLUMN b text;
-- Result: DDL TYPE = LOCK_TABLE, ALGORITHM = COPY

-- After specifying OMC, no table lock
EXPLAIN ONLINE_DDL ALTER TABLE t1 MODIFY COLUMN b text, ALGORITHM=OMC;
-- Result: DDL TYPE = ONLINE_DDL, ALGORITHM = OMC

-- After confirming lock-free, execute
ALTER TABLE t1 MODIFY COLUMN b text, ALGORITHM=OMC;

Note: OMC executes slower and consumes more resources; only use it when you need to avoid table locking. Prefer native Online DDL.

Check Long Transactions Before DDL

Even if the DDL itself doesn't lock the table, uncommitted long transactions or large queries on the target table may still cause issues.

PolarDB-X MDL Optimizations

PolarDB-X has two key optimizations for MDL locks:

1. Preemptive MDL lock: Guarantees the DDL can acquire the MDL lock within a deterministic time frame (default 15s), solving the problem of DDL being unable to execute for extended periods. 2. Dual-version MDL lock: Introduces a dual-version metadata mechanism where new transactions access new metadata, preventing new transactions from being blocked.

Side effect: By default, connections with long transactions or large queries exceeding 15 seconds will be killed. If you have important data sync tasks (DataWorks/DTS/mysqldump, etc.), avoid performing DDL during those task executions.

Check Long Transactions via POLARDBX_TRX View

Query all transactions with duration exceeding 15 seconds:

SELECT
    TRX_ID AS 'Transaction ID',
    PROCESS_ID AS 'Connection ID',
    SCHEMA AS 'Database',
    START_TIME AS 'Transaction Start Time',
    ROUND(DURATION_TIME / 1000 / 1000, 3) AS 'Duration (seconds)',
    ROUND(ACTIVE_TIME / 1000 / 1000, 3) AS 'Active Time (seconds)',
    ROUND(IDLE_TIME / 1000 / 1000, 3) AS 'Idle Time (seconds)',
    SQL AS 'Current SQL'
FROM
    INFORMATION_SCHEMA.POLARDBX_TRX
WHERE
    DURATION_TIME > 15 * 1000 * 1000;

Field descriptions:

• Duration: Total elapsed time from transaction start to now.
• Active Time: Total time the database actually spent processing the transaction.
• Idle Time: Total time the client was not interacting with the database during the transaction (typically client-side business logic processing time).

Check Transaction MDL via METADATA_LOCK View

After locating a long transaction, check which tables it holds MDL locks on:

SELECT
    LOWER(HEX(TRX_ID)) AS 'Transaction ID',
    CONN_ID AS 'Connection ID',
    SUBSTRING_INDEX(SUBSTRING_INDEX(`TABLE`, '#', 1), '.', 1) AS 'Database',
    SUBSTRING_INDEX(SUBSTRING_INDEX(`TABLE`, '#', 1), '.', -1) AS 'Table Name',
    SUBSTRING_INDEX(FRONTEND, '@', 1) AS 'Username',
    SUBSTRING_INDEX(FRONTEND, '@', -1) AS 'Client IP',
    TYPE AS 'MDL Type'
FROM
    INFORMATION_SCHEMA.METADATA_LOCK
WHERE
    `TABLE` NOT LIKE 'tablegroupid%'
    AND LOWER(HEX(TRX_ID)) = '<transaction_id>';

Long Transaction Decision Before DDL

Recommended DDL Execution Workflow

Important: DDL is a high-risk operation. Before actually executing any DDL statement, you must present the DDL statement, EXPLAIN ONLINE_DDL results, and long transaction check results to the user and obtain explicit confirmation before execution. Never execute DDL directly without user confirmation.

1. EXPLAIN ONLINE_DDL ALTER TABLE ...
   |
   |-- ONLINE_DDL -> Execute directly
   |-- LOCK_TABLE -> Rewrite with ALGORITHM=OMC and re-EXPLAIN to confirm
   |
2. Check long transactions on the target table (POLARDBX_TRX + METADATA_LOCK)
   |
3. Present all information to the user and obtain explicit confirmation
   |
4. Execute DDL after confirming it's safe

View DDL Execution Progress

For long-running DDL operations (such as OMC, INPLACE, OSC involving data backfill), monitor execution progress in real-time via the INFORMATION_SCHEMA.DDL_PROGRESS view:

SELECT * FROM INFORMATION_SCHEMA.DDL_PROGRESS;

When users execute a long-running DDL and ask about progress, use this view.

DMS Lock-Free Changes

PolarDB-X's lock-free column type change feature is integrated into DMS (Data Management Service)'s lock-free change module. When using DMS, the system automatically determines whether the DDL has table-locking risks and intelligently selects the optimal execution strategy.

• Entry: DMS Console > Database Development > Data Changes > Lock-Free Changes
• Once enabled, both regular data change orders and lock-free change orders prioritize lock-free execution
• Version requirement: Instance version >= 5.4.20-20241224

Legacy Version Compatibility

For instance versions below 5.4.20-20241224:

• Refer to the official Online DDL documentation to determine if DDL locks the table.
• For ALTER TABLE types, append LOCK=NONE to the statement for testing:
• Executes normally -> The operation is Online and doesn't lock the table.
• Returns an error -> The operation doesn't support Online execution and will lock the table.
• It's recommended to verify on a test instance or test table.

FAQ

Q: Why isn't OMC the default execution strategy for ALTER TABLE?

While OMC avoids table locking, it executes slower and consumes more resources. In most scenarios, prefer MySQL-native Online DDL; only choose OMC when you need to avoid table locking.

Q: How to speed up DDL execution?

PolarDB-X supports parallel DDL functionality. When hardware resources are idle, you can adjust DDL concurrency to accelerate execution and shorten the change window.

PolarDB-X Efficient Pagination Query Best Practices

Pagination is a common database operation. This document describes how to efficiently perform paging in PolarDB-X distributed databases, meeting the following goals:

• Traverse all data in a large table (billions of rows), returning a fixed batch size (e.g., 1000 rows)
• Traverse in data write-time order
• Constant paging performance that doesn't degrade as page numbers increase
• No data omissions

Why LIMIT M, N Is Not Suitable for Deep Pagination

Cost in Standalone Databases

The cost of LIMIT M, N is O(M+N). The database cannot directly locate the Mth row; it must scan from the first row, skip M rows, and return the next N rows.

-- Get 1000 rows after the 10000th row
SELECT * FROM t1 ORDER BY gmt_create LIMIT 10000, 1000;
-- Actually scans 10000 + 1000 = 11000 records

The deeper you page, the more data needs to be scanned, and the worse the performance.

Even Higher Cost in Distributed Databases

In distributed databases, LIMIT M, N requires each shard to return the first M+N rows to the coordinator node for merge sorting:

-- During distributed execution, each shard needs to execute:
SELECT * FROM t1 ORDER BY gmt_create LIMIT 0, 11000;
-- Results from all shards are aggregated at the CN node for sorting, then the final 1000 rows are selected

Total cost = O(M+N) x network transfer overhead. The amplification effect of network transfer is even more significant with many shards.

For scenarios with small data volumes, low concurrency, and modest performance requirements, using LIMIT M, N directly is fine. For performance-sensitive scenarios, use the methods described below.

Efficient Pagination: Keyset Pagination (Cursor Pagination)

Core idea: Record the sort value of the last row in each batch and use it as the starting condition in the WHERE clause for the next batch, avoiding scanning data that has already been paged through.

Scenario 1: Tables Using New Sequence (AUTO Mode Default)

In AUTO mode databases, auto-increment primary keys default to New Sequence, which is globally ordered — the ID value represents the chronological order of data writes.

-- First batch
SELECT * FROM t1 ORDER BY id LIMIT 1000;

-- Record the id value of the last row as last_id, then for each subsequent batch:
SELECT * FROM t1 WHERE id > last_id ORDER BY id LIMIT 1000;

Since id is an ordered index, the database can directly locate the scan starting position. The cost is only the 1000 rows in the result set, regardless of which page you're on.

You can check the table's auto-increment strategy with SHOW SEQUENCES and the database mode with SHOW CREATE DATABASE.

Scenario 2: Sort Columns May Have Duplicates (e.g., Time Columns, Group Sequence IDs)

For tables with Group Sequence (mode=drds), ID order doesn't represent write time; or when sorting by a time column where time values may be duplicated.

Incorrect approach (will lose data or have duplicates):

-- Wrong: when gmt_create has duplicates, > will lose data, >= will have duplicates
SELECT * FROM t1 WHERE gmt_create > ? ORDER BY gmt_create LIMIT 1000;

Correct approach: Use (sort_column, id) combination as the cursor, with tuple comparison or equivalent conditions:

-- Method 1: Tuple comparison (recommended, supported by PolarDB-X)
SELECT * FROM t1
WHERE (gmt_create, id) > (?, ?)
ORDER BY gmt_create, id
LIMIT 1000;

-- Method 2: Equivalent expansion (for databases that don't support tuple comparison)
SELECT * FROM t1
WHERE gmt_create >= ?
  AND (gmt_create > ? OR id > ?)
ORDER BY gmt_create, id
LIMIT 1000;

Both methods are equivalent. In PolarDB-X, tuple comparison (Method 1) is recommended.

Complete Traversal Workflow

1. First batch query: SELECT * FROM t1 ORDER BY gmt_create, id LIMIT 1000;
2. Record the last row's gmt_create and id
3. Each subsequent batch: SELECT * FROM t1 WHERE (gmt_create, id) > (last_gmt_create, last_id) ORDER BY gmt_create, id LIMIT 1000;
4. When the result set is empty, traversal is complete

Per-Shard Traversal (Advanced Scenario)

When queries don't include the partition key, pagination queries are cross-partition. Performance is usually fine at low concurrency. But in the following extreme scenarios, you can traverse shard by shard:

• Many shards (e.g., >= 256)
• Very high stability requirements with no tolerance for unpredictable factors
• No strict data ordering requirements

Steps

1. Get the table's topology information:

SHOW TOPOLOGY FROM t1;

2. Use HINT to specify a shard and paginate within a single shard:

/*+TDDL:NODE('partition_name')*/
SELECT * FROM t1 WHERE (gmt_create, id) > (?, ?) ORDER BY gmt_create, id LIMIT 1000;

3. Outer loop iterates through all shards.

Index Requirements

The sort columns for pagination queries must have appropriate indexes; otherwise, each query requires a full table scan and sort:

-- Example: Create a composite index for pagination queries
ALTER TABLE t1 ADD INDEX idx_page (gmt_create, id);

-- With filter conditions
ALTER TABLE t1 ADD INDEX idx_page_c1 (c1, gmt_create, id);

Java Application Considerations

JDBC Parameter Settings

Statement Settings

Java Code Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class PaginationExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://<host>:3306/<database>"
            + "?netTimeoutForStreamingResults=0&socketTimeout=600000";
        String user = "<user>";
        String password = "<password>";

        boolean first = true;
        Object lastGmtCreate = null;
        long lastId = -1;
        int totalRows = 0;

        while (true) {
            try (Connection conn = DriverManager.getConnection(url, user, password)) {
                PreparedStatement ps;
                if (first) {
                    ps = conn.prepareStatement(
                        "SELECT * FROM t1 ORDER BY gmt_create, id LIMIT 1000");
                    first = false;
                } else {
                    ps = conn.prepareStatement(
                        "SELECT * FROM t1 "
                        + "WHERE gmt_create >= ? AND (gmt_create > ? OR id > ?) "
                        + "ORDER BY gmt_create, id LIMIT 1000");
                    ps.setObject(1, lastGmtCreate);
                    ps.setObject(2, lastGmtCreate);
                    ps.setLong(3, lastId);
                }

                ResultSet rs = ps.executeQuery();
                lastGmtCreate = null;
                lastId = -1;

                while (rs.next()) {
                    totalRows++;
                    lastGmtCreate = rs.getObject("gmt_create");
                    lastId = rs.getLong("id");
                    // Process row data...
                }

                if (lastId == -1) {
                    // No more data, traversal complete
                    break;
                }
            }
        }
        System.out.println("Total rows: " + totalRows);
    }
}

Data Export Scenario

If the purpose of pagination is data export, it's recommended to use PolarDB-X's open-source Batch Tool, which has more extensive export optimizations built for PolarDB-X:

• Batch Tool Documentation

Method Comparison

FAQ

Q: Why not use LIMIT M, N + OFFSET?

The cost of LIMIT M, N is O(M+N), and deep pagination performance degrades severely with large datasets. Keyset pagination cost is always O(N), regardless of the page number.

Q: Can tuple comparison `(gmt_create, id) > (?, ?)` use indexes?

Yes. In PolarDB-X, if there's a (gmt_create, id) composite index, tuple comparison can leverage the index for range scans.

Q: Can I skip ORDER BY when sorting?

No. In both standalone and distributed databases, the return order is undefined without ORDER BY. In distributed databases, the order of data returned from different shards is random — you must explicitly specify ORDER BY.

Q: Why should autocommit be set to true?

Pagination traversal is a long-running process. If pagination queries run within a transaction, it creates a long transaction that consumes database resources and may cause issues. Keeping autocommit=true ensures each query is independent.

PolarDB-X Partition Design Best Practices

Partition scheme design is the most critical aspect of using PolarDB-X distributed database, directly impacting system performance, scalability, and cost. This document provides principles for selecting partition keys, partition counts, partition algorithms, as well as GSI creation strategies and safe migration workflows.

Partition Design Steps Overview

1. Collect SQL access pattern data (SQL Insight or alternatives)
2. Analyze SQL templates, calculate query ratio for each field
3. Select partition key
4. Determine whether GSIs are needed and which to create
5. Choose partition algorithm
6. Determine partition count
7. Design migration workflow (ensuring continuity of uniqueness constraints and query performance)

Step 1: Collect SQL Access Patterns

The core basis for partition design is the table's actual SQL access patterns — which fields are queried frequently and the read/write ratio. There are several ways to obtain this data:

Method 1: SQL Insight (Strongly Recommended)

SQL Insight is the most accurate and effortless method, strongly recommended to enable.

• Enable via: SQL Insight Documentation
• If cost is a concern, enable for a period (e.g., one week) to collect data, then disable
• Search for the target table name, sort SQL templates by execution count
• Recommend exporting to CSV for reference
• Key data: Execution count, returned rows, read/write ratio for each SQL template

Method 2: Slow Query Logs + Application Code Analysis

• Review slow query logs via the console to find high-frequency slow queries for the table
• Combine with application code (ORM mappings, DAO layer, SQL files) to catalog all SQL templates for the table
• Annotate each SQL's call frequency (high/medium/low) and read/write type from the code

Method 3: Business Team Provides SQL Patterns

Have the business development team list all query and write patterns for the table.

Note: This method has the lowest accuracy; actual business scenarios often differ significantly from descriptions. If this is the only option, pay special attention to:

- List all WHERE condition fields for every SQL

- Distinguish the approximate QPS level for each SQL (e.g., thousands per second vs. a few per hour)

- Clarify write operation (INSERT/UPDATE/DELETE) condition fields and frequency

Data Quality Impact on Partition Design

Regardless of method, the goal is to obtain a SQL template inventory for the table, including query fields, execution frequency, and returned rows for each template.

Step 2: Partition Key Selection

Basic Principles

Partition key selection requires comprehensive multi-dimensional analysis — evaluate every candidate field on ALL dimensions below, then choose the one that scores best overall. Do NOT recommend based on a single dimension alone.

1. Equality query ratio: The proportion of SQL templates where this field appears as an equality condition (WHERE col = ?) 2. Cardinality: The field should have sufficiently many distinct values for even data distribution across partitions 3. Hotspot risk: Assess whether a few values dominate a large portion of data. Even a high-cardinality field can have skew (e.g., buyer_id in order tables — some buyers generate far more orders than others) 4. Primary key / unique key status: PKs/UKs inherently have the highest cardinality (unique per row), never produce hotspots, and guarantee the most even distribution; selecting them as partition keys also naturally maintains global uniqueness 5. Semantic analysis: Infer likely query patterns from the table type and field meaning. For example, order_id in an order table will certainly be queried frequently (order detail lookups, payment callbacks, status checks), even if the user only mentions buyer_id queries

Analysis Method

From SQL Insight results, evaluate each candidate field:

Tips for Identifying Hotspots

• Fields whose names contain words like "base", "parent", "group", "type", "category" typically have few distinct values and are prone to hotspots
• Verify data distribution with: SELECT col, COUNT(*) FROM table GROUP BY col ORDER BY COUNT(*) DESC LIMIT 20;

Example Analysis

Using the account table as an example, SQL Insight results show:

Conclusion: Although base_account_id has the highest query ratio, its low cardinality and obvious hotspots make it unsuitable as a partition key. account_id as the primary key has the highest cardinality and no hotspots, making it the better partition key.

Example Analysis 2 — Order Table (nuanced case: both candidates have high cardinality)

The user states "most queries filter by buyer_id, primary key is order_id". Comprehensive multi-dimensional analysis:

Comprehensive conclusion: Both fields have high query ratios. However, order_id scores better on cardinality (unique per row vs. millions of distinct values), hotspot risk (zero vs. potential skew), and PK status. Recommendation: order_id as partition key + Clustered GSI on buyer_id to optimize buyer-dimension queries. Always recommend collecting actual SQL access pattern data (SQL Insight or alternatives) to verify the analysis before finalizing.

Note: This is a common pattern where the user mentions only one query dimension. Semantic analysis reveals that other dimensions (order_id lookups) are also frequent. Do not assume that unmentioned fields have low query frequency — always analyze from the table's business semantics.

Step 3: GSI Selection

Basic Principles

Write volume assessment (the core basis for GSI strategy):

SQL Insight is the most accurate way to assess write volume.

GSI Type Selection

Fields Unsuitable for GSI

• Fields with very low cardinality (e.g., gender, province — very few distinct values)
• Time/date fields (local indexes usually suffice)

Composite Query Optimization

If a field always appears in combination with other fields in high-frequency SQL and never appears alone, there's no need to create a standalone GSI for it.

Example Analysis (continued, account table)

• Write volume: thousands per hour, very low compared to queries -> can freely create GSIs
• kw_location: query ratio ~30%, few records per value -> Regular GSI
• address: query ratio ~50%, few records per value -> Regular GSI
• exchange_account_id: unique key -> Global Unique Index (UGSI)
• base_account_id: although query ratio ~75%, it always appears in combination with kw_location or address in high-frequency SQL, never alone -> Do not create GSI

GSI Maintenance

Use the index diagnostic feature (INSPECT INDEX) to periodically check for redundant and unused GSIs:

• Index Diagnostics Documentation

Step 4: Partition Algorithm Selection

Common Partition Algorithms

PolarDB-X supports 7x7+7=56 partition strategies, but most workloads can choose from the above.

Detailed documentation: Partition Strategy Overview

Difference Between HASH and KEY

Multi-field partition key best practices:

• Very few workloads need multiple fields as HASH partition keys; pick the one with the highest cardinality from candidates
• Multi-column KEY partition is most common in hotspot splitting scenarios: the partition key consists of one business column + primary key, e.g., PARTITION BY KEY(some_column, pk)
• Reference: Hotspot Splitting Documentation

Step 5: Partition Count Selection

Basic Principles

1. 256 is appropriate for the vast majority of workloads 2. Partition count should be several times the number of DN nodes; too few can lead to data skew, and scaling may require repartitioning 3. Single partition data volume under 100 million rows provides a better operational experience (DDL duration, etc.) 4. Don't set too many; 256 partitions have been proven to handle billions of rows without issues 5. No need for precise calculation; approximate is fine

Step 6: Migration Workflow Design

Single Table to Partitioned Table: How It Works

Converting a single table to a partitioned table is an Online DDL. Core steps:

1. Create a temporary Clustered Global Secondary Index on the original table (partition key is the target partition key) 2. Incremental data is dual-written to both the primary table and temporary GSI via distributed transactions (write RT increases somewhat during migration) 3. Full data is backfilled from the primary table to the temporary GSI (incremental dual-write and full backfill proceed simultaneously; data is consistent once backfill completes) 4. The primary table and temporary GSI are swapped (lock-free operation) 5. The old table is cleaned up after the swap

Key characteristics:

• No table locking, the process is Online
• Write performance decreases somewhat during migration
• Long transactions (>=15s) may be interrupted
• Read operations are virtually unaffected

Uniqueness Constraint Handling

When converting a single table to a partitioned table, unique indexes become local indexes:

• Includes partition key -> Remains globally unique after migration
• Does not include partition key -> Only unique within partition after migration (risk of data duplication)

Single tables cannot create global indexes, so you cannot pre-create UGSI on a single table to solve this.

Choosing a Migration Workflow

The migration workflow depends on whether the table has unique keys that do not include the partition key. Check this condition first:

Does the table have unique keys (other than PK) that do NOT include the partition key?
  ├── NO  → Use the Standard Two-Step Method (simpler, covers most cases)
  └── YES → Use the Three-Step Method (protects uniqueness constraints)

Common "NO" scenarios (use Two-Step): partition key is the primary key and there are no other unique keys; or all unique keys already include the partition key.

>

Common "YES" scenarios (use Three-Step): the table has a unique key on a non-partition-key field (e.g., partition key is account_id, but there's a unique key on exchange_account_id).

Standard Two-Step Method

Applicable when there are no unique keys at risk — i.e., the partition key is the primary key and there are no other unique keys, or all unique keys already include the partition key.

Step 1: Convert single table to a partitioned table with the target partition count

ALTER TABLE t_order PARTITION BY HASH(order_id) PARTITIONS 256;

Step 2: Create required global indexes

CREATE CLUSTERED INDEX cgsi_buyer_id
  ON t_order (buyer_id)
  PARTITION BY KEY(buyer_id) PARTITIONS 256;

CREATE GLOBAL INDEX gsi_seller_id
  ON t_order (seller_id) COVERING(amount, status)
  PARTITION BY KEY(seller_id) PARTITIONS 256;

Rollback Plan:

Three-Step Method (Protecting Uniqueness Constraints)

Required when the table has unique keys that do not include the partition key. The core idea: first convert to a partitioned table with 1 partition (preserving uniqueness), then create GSI/UGSI, finally change to the target partition count.

Why is this needed? When converting a single table to a multi-partition table, unique indexes become local indexes. If a unique key does not include the partition key, it can only guarantee uniqueness within each partition, not globally. If you directly convert to the target partition count, there is a window between the partition change and UGSI creation where duplicate data may be written, causing subsequent UGSI creation to fail.

Step 1: Convert single table to a partitioned table with 1 partition

ALTER TABLE account PARTITION BY HASH(account_id) PARTITIONS 1;

At this point:

• The table is now a partitioned table and can create global indexes
• With only 1 partition, all unique keys remain globally unique (same as when it was a single table)

Step 2: Create required global indexes and global unique indexes

-- Global Unique Index (ensuring exchange_account_id global uniqueness)
CREATE GLOBAL UNIQUE INDEX ugsi_exchange_account_id
  ON account (exchange_account_id)
  PARTITION BY HASH(exchange_account_id) PARTITIONS 256;

-- Regular Global Index (optimizing address queries)
CREATE GLOBAL INDEX gsi_address
  ON account (address)
  PARTITION BY HASH(address) PARTITIONS 256;

-- Regular Global Index (optimizing kw_location queries)
CREATE GLOBAL INDEX gsi_kw_location
  ON account (kw_location)
  PARTITION BY HASH(kw_location) PARTITIONS 256;

At this point, exchange_account_id's uniqueness is guaranteed by the Global Unique Index — uniqueness constraints are never lost throughout the process.

Step 3: Change to the target partition count

ALTER TABLE account PARTITION BY HASH(account_id) PARTITIONS 256;

Rollback Plan:

Complete Example Summary

Example 1: Order table (no unique keys at risk — Two-Step Method)

Original table: Single table t_order
Partition key: order_id (primary key, no other unique keys)
Partition algorithm: HASH
Partition count: 256
Global indexes:
  - Clustered GSI on buyer_id (highest-frequency query optimization)
  - GSI on seller_id + COVERING (lower-frequency query optimization)

Migration workflow (Two-Step):
  1. ALTER TABLE t_order PARTITION BY HASH(order_id) PARTITIONS 256;
  2. CREATE CLUSTERED INDEX cgsi_buyer_id ON t_order(buyer_id) ...;
     CREATE GLOBAL INDEX gsi_seller_id ON t_order(seller_id) COVERING(...) ...;

Example 2: Account table (has unique keys at risk — Three-Step Method)

Original table: Single table account
Partition key: account_id (primary key, highest cardinality, no hotspots)
Unique key: exchange_account_id (does NOT include partition key — at risk)
Partition algorithm: HASH
Partition count: 256
Global indexes:
  - UGSI on exchange_account_id (unique key protection)
  - GSI on address (high-frequency query optimization)
  - GSI on kw_location (high-frequency query optimization)

Migration workflow (Three-Step):
  1. ALTER TABLE account PARTITION BY HASH(account_id) PARTITIONS 1;
  2. CREATE GLOBAL UNIQUE INDEX ugsi_exchange_account_id ...;
     CREATE GLOBAL INDEX gsi_address ...;
     CREATE GLOBAL INDEX gsi_kw_location ...;
  3. ALTER TABLE account PARTITION BY HASH(account_id) PARTITIONS 256;

FAQ

Q: Do only large tables need partitioning?

No. Tables with high access volume also benefit from partitioning, as it allows more nodes to share the traffic.

Q: Do partitioned tables support foreign keys?

Partitioned tables support foreign keys, but it's an experimental feature not recommended for production environments. It's recommended to drop foreign key constraints beforehand.

Q: Does converting a single table to a partitioned table lock the table?

No. The migration process is Online.

Q: Should all queries hit the partition key or GSI?

No. Partition design is pragmatic work. Low-ratio SQL is not important regardless of how many shards it crosses. When a single-table SQL becomes a cross-shard SQL:

• Response time increase is usually limited (generally within 1x), because queries to each shard are parallelized
• Total cost = per-query cost x QPS; low QPS means limited total cost increase

Q: Does GSI significantly impact performance? Is it safe to use?

• For read-heavy scenarios, some additional write cost is acceptable
• Even for high write volumes, GSI is the only universal solution for multi-dimensional queries. When there's no other way to implement multi-dimensional queries, use GSI and configure appropriate machine resources

Q: What is the business impact during migration?

Primary Keys and Unique Keys

In PolarDB-X Distributed Edition AUTO mode, primary keys and unique keys are classified as Global (globally unique) or Local (unique within partition) based on their relationship with partition columns. Understanding this distinction is critical for correct table design and avoiding data consistency issues.

Primary Key Classification

Global Primary Key

Guarantees global uniqueness. Primary keys in the following scenarios are Global:

• Single tables and broadcast tables: Primary keys are always globally unique.
• Manual partitioned tables: When the primary key columns include all partition columns, it is a Global primary key.

-- Single table: Global primary key
CREATE TABLE single_tbl(
  id bigint NOT NULL AUTO_INCREMENT,
  name varchar(30),
  PRIMARY KEY(id)
) SINGLE;

-- Broadcast table: Global primary key
CREATE TABLE brd_tbl(
  id bigint NOT NULL AUTO_INCREMENT,
  name varchar(30),
  PRIMARY KEY(id)
) BROADCAST;

-- Manual partitioned table: PK (id, name, addr) includes all partition columns (id, addr) -> Global PK
CREATE TABLE key_tbl(
  id bigint,
  name varchar(10),
  addr varchar(30),
  PRIMARY KEY(id, name, addr)
) PARTITION BY KEY(id, addr);

Local Primary Key

Only guarantees uniqueness within a partition, cannot guarantee global uniqueness. Occurs in manual partitioned tables where the primary key columns do not include all partition columns.

-- Manual partitioned table: PK (order_id) does not include partition column (city) -> Local PK
CREATE TABLE list_tbl(
  order_id bigint,
  city varchar(50),
  name text,
  PRIMARY KEY(order_id)
) PARTITION BY LIST(city)
(
  PARTITION p1 VALUES IN ("Beijing"),
  PARTITION p2 VALUES IN ("Shanghai"),
  PARTITION p3 VALUES IN ("Guangzhou"),
  PARTITION p4 VALUES IN ("Shenzhen"),
  PARTITION p5 VALUES IN(DEFAULT)
);

Local primary key risks: Different partitions can have the same primary key value.

-- Insert into p1 partition (Beijing)
INSERT INTO list_tbl(order_id, city, name) VALUES (10001, "Beijing", "phone");
-- Query OK

-- Same order_id, same partition -> Primary key conflict (within-partition uniqueness works)
INSERT INTO list_tbl(order_id, city, name) VALUES (10001, "Beijing", "book");
-- ERROR: Duplicate entry '10001' for key 'PRIMARY'

-- Same order_id, different partition (Shenzhen) -> Succeeds, global primary key duplication!
INSERT INTO list_tbl(order_id, city, name) VALUES (10001, "Shenzhen", "camera");
-- Query OK

SELECT * FROM list_tbl;
-- order_id=10001 appears in two records, in Beijing and Shenzhen partitions respectively

Local primary key DDL issues: When executing partition strategy changes that cause duplicate primary key data to fall into the same partition, the DDL will fail.

ALTER TABLE list_tbl
PARTITION BY LIST (city)
(
  PARTITION p1 VALUES IN ("Beijing", "Shenzhen"),  -- Two order_id=10001 rows merged into same partition
  PARTITION p2 VALUES IN ("Shanghai"),
  PARTITION p3 VALUES IN ("Guangzhou"),
  PARTITION p5 VALUES IN(DEFAULT)
);
-- ERROR: Duplicated entry '10001' for key 'PRIMARY'

Unique Key Classification

Global Unique Key

Guarantees global uniqueness. Unique keys in the following scenarios are Global:

• Single tables and broadcast tables: Unique keys are always globally unique.
• Manual partitioned tables: When the unique key columns include all partition columns, it is a Global unique key.
• UNIQUE GLOBAL INDEX: Achieves global uniqueness via a Global Secondary Index.

-- Manual partitioned table: UK (inner_id, type_id) includes partition column (type_id) -> Global UK
CREATE TABLE hash_tbl(
  type_id int,
  inner_id int,
  UNIQUE KEY(inner_id, type_id)
) PARTITION BY HASH(type_id);

-- Manual partitioned table: Achieves Global UK via UNIQUE GLOBAL INDEX
CREATE TABLE key_tbl(
  type_id int,
  serial_id int,
  UNIQUE GLOBAL INDEX u_sid(serial_id) PARTITION BY HASH(serial_id)
) PARTITION BY HASH(type_id);

Local Unique Key

Only guarantees uniqueness within a partition, cannot guarantee global uniqueness. Occurs in manual partitioned tables where the unique key columns do not include all partition columns.

-- Manual partitioned table: UK (serial_id) does not include partition column (order_time) -> Local UK
CREATE TABLE range_tbl(
  id int primary key auto_increment,
  serial_id int,
  order_time datetime NOT NULL,
  UNIQUE KEY(serial_id)
) PARTITION BY RANGE(order_time)
(
  PARTITION p1 VALUES LESS THAN ('2022-12-31'),
  PARTITION p2 VALUES LESS THAN ('2023-12-31'),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

Local unique key risks: Similar to Local primary keys, different partitions can have the same unique key value.

INSERT INTO range_tbl(serial_id, order_time) VALUES (20001, '2022-01-01');
-- Query OK (p1 partition)

INSERT INTO range_tbl(serial_id, order_time) VALUES (20001, '2022-01-02');
-- ERROR: Duplicate entry '20001' for key 'serial_id' (within-partition uniqueness works)

INSERT INTO range_tbl(serial_id, order_time) VALUES (20001, '2024-01-01');
-- Query OK (p3 partition, different partition -> global unique key duplication!)

Local unique key DDL issues: When converting a manual partitioned table to a single table, redistribution will fail if duplicate unique key values exist.

ALTER TABLE range_tbl SINGLE;
-- ERROR: Duplicated entry for key 'PRIMARY'

Quick Reference for Classification Rules

Recommendations and Considerations

1. Prefer choosing partition keys from PK/UK columns: When designing partition schemes, prefer selecting partition keys from existing primary key or unique key columns — this naturally ensures Global classification without modifying the user's schema. Do NOT add partition columns into the user's existing primary key definition. 2. Local primary key scenarios: If the business genuinely requires a Local primary key (partition key is not part of PK), use AUTO_INCREMENT or Sequence for system-generated primary key values to avoid manually specifying primary key values from the business side. Explain the risks (cross-partition duplicate PKs, DDL failures on repartition) to the user. 3. Local unique key scenarios: The business side should take measures to ensure global uniqueness of unique key values. 4. Data synchronization note: If a table has duplicate primary key values due to Local primary keys, when syncing to downstream systems (e.g., AnalyticDB MySQL), set the downstream primary key to the full set of "PolarDB-X table's primary key columns + partition columns" to avoid conflicts. 5. Converting Local PK to globally unique: Use Sequence to generate unique values as primary key values; see sequence.md. 6. No special syntax needed for Global PK/UK: Use the same syntax as MySQL; just ensure the classification conditions above are met.

PolarDB-X Sequence

Sequences in PolarDB-X generate globally unique numeric sequences, primarily used for primary key or unique index columns. In distributed scenarios, Sequences replace MySQL's AUTO_INCREMENT to provide global uniqueness guarantees.

Sequence Types

NEW SEQUENCE (Default, Recommended)

Introduced in version 5.4.14, this is the current default Sequence type. Key characteristics:

• Sequential auto-increment: Values generated within a single connection are strictly consecutive and increasing. Across connections, values also tend to increase (unlike GROUP SEQUENCE's batch segment allocation, there are no large gaps).
• Globally unique: Guarantees global uniqueness in distributed environments.
• Persistence-based: Value allocation is based on underlying storage nodes. After instance restart, allocation continues from the last position without loss or duplication.
• High performance: Internal caching mechanism ensures allocation efficiency, approaching GROUP SEQUENCE performance levels.
• Customizable: Supports START WITH, INCREMENT BY, MAXVALUE, CYCLE parameters (customization features require 5.4.17+).

-- Default creation (equivalent to CREATE NEW SEQUENCE)
CREATE SEQUENCE order_seq;

-- With parameters
CREATE NEW SEQUENCE order_seq
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 9999999999
  NOCYCLE;

GROUP SEQUENCE (Legacy Default Type)

Fetches ID segments in batch from the database and caches them in memory for allocation. High performance. Each node independently caches an ID segment, so values allocated across nodes are not continuous (e.g., Node A allocates 1-10000, Node B allocates 10001-20000). Starting value is 100001.

CREATE GROUP SEQUENCE user_id_seq;

-- With starting value
CREATE GROUP SEQUENCE user_id_seq START WITH 200001;

For unit-based deployment using UNIT COUNT / INDEX:

CREATE GROUP SEQUENCE user_id_seq
  START WITH 100001
  UNIT COUNT 3 INDEX 0;

SIMPLE SEQUENCE

Supports custom step, maximum value, and cycling. Functionality has been superseded by NEW SEQUENCE; not recommended for new use cases:

CREATE SIMPLE SEQUENCE legacy_seq
  START WITH 1000
  INCREMENT BY 2
  MAXVALUE 99999999
  CYCLE;

TIME SEQUENCE

Generates unique IDs based on timestamps. The column type must be BIGINT:

CREATE TIME SEQUENCE ts_seq;

Explicit Sequence Usage

Get Values

-- Get a single value
SELECT order_seq.NEXTVAL FROM DUAL;

-- Get 10 values in batch
SELECT order_seq.NEXTVAL FROM DUAL WHERE COUNT = 10;

Use in INSERT

INSERT INTO t_order (order_id, buyer_id, amount)
VALUES (order_seq.NEXTVAL, 12345, 99.90);

View All Sequences

SHOW SEQUENCES;

Implicit Sequence

When a table column is defined with AUTO_INCREMENT, PolarDB-X automatically associates an implicit Sequence. Users don't need to create or manage it manually.

CREATE TABLE t_user (
  user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64)
) PARTITION BY KEY(user_id) PARTITIONS 16;

Note: The implicit Sequence type depends on the instance version. Version 5.4.14+ defaults to NEW SEQUENCE, where AUTO_INCREMENT is strictly consecutive within a single connection and tends to increase across connections. Versions before 5.4.14 default to GROUP SEQUENCE, where values allocated across nodes are not continuous.

Modify and Drop Sequences

-- Modify parameters
ALTER SEQUENCE order_seq START WITH 500000 INCREMENT BY 5;

-- Change type (START WITH must be specified)
ALTER SEQUENCE order_seq CHANGE TO SIMPLE START WITH 1000000;

-- Drop
DROP SEQUENCE order_seq;

Limitations

• Maximum 16384 Sequences per database.
• NEW SEQUENCE requires version 5.4.14+; customization features require 5.4.17+.
• GROUP SEQUENCE starts at 100001; cannot start from 1.
• TIME SEQUENCE columns must be BIGINT type.
• Unit-based GROUP SEQUENCE does not support type conversion.