PolarDB-X SQL (MySQL Compatibility Focus)

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

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

PARTITION BY

dbpartition/tbpartition

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

Installation

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

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.

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, this skill does not apply.
     • Result contains

       X-Cluster

       (e.g.,

       8.0.32-X-Cluster-8.4.20-20251017

       ) -> 2.0 Standard Edition, this skill does not apply, use the

       polardbx-standard

       skill instead.
   • 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 -> Broadcast table

     BROADCAST

     (fully replicated to every DN).
   • Tables that don't need distribution -> Single table

     SINGLE

     (stored on one DN only).
   • Otherwise -> Partitioned table (default), choose appropriate partition key and strategy.
3. Partition scheme design (for partitioned tables):
   • Collect SQL access pattern data: 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: Prefer fields with high equality query ratio and high cardinality; primary keys/unique keys have a natural advantage (highest cardinality, no hotspots); exclude fields with hotspots (fields with few distinct values or extremely uneven distribution are unsuitable as partition keys).
   • 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 require primary/unique keys to include all partition columns for Global, otherwise Local (risk of data duplication and DDL failure).
• 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:
  sql

  -- ✅ 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)

• 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, single tables for non-distributed needs, partitioned tables for everything else.
2. Select partition keys based on real SQL patterns: Prefer SQL Insight data; when unavailable, use slow query logs or code analysis as alternatives; prioritize fields with high equality query ratio, high cardinality, and no hotspots; primary keys/unique keys are naturally strong partition key candidates.
3. Include partition columns in primary keys: Primary/unique keys of manual partitioned tables should include all partition columns to ensure global uniqueness.
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: Leverage the TTL mechanism to automatically pre-create future partitions for time-type Range partitioned tables, preventing write failures due to insufficient partitions; set

    TTL_CLEANUP = 'OFF'

    for add-only mode; immediately run

    CLEANUP EXPIRED DATA WITH TTL_CLEANUP = 'OFF'

    after configuration to trigger the first pre-creation; requires version 5.4.20+.

Reference Links