PolarDB-X SQL (Focus on MySQL Compatibility)

Scope of Application

• 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 in DRDS mode database

PARTITION BY

dbpartition/tbpartition

SHOW CREATE DATABASE db_name;
-- MODE = 'auto' in the output indicates AUTO mode

Workflow (Follow Each Time You Use)

1. Confirm target engine and version:
   • Execute

     SELECT VERSION();

     to determine the instance type:
     • Result contains

       TDDL

       and version number > 5.4.12 (e.g.,

       5.7.25-TDDL-5.4.19-20251031

       ) -> 2.0 Enterprise Edition (Distributed Edition), this skill is applicable. Parse the enterprise edition version number (e.g., 5.4.19) from it.
     • Result contains

       TDDL

       and version number <= 5.4.12 (e.g.,

       5.6.29-TDDL-5.4.12-16327949

       ) -> DRDS 1.0, this skill is not applicable.
     • Result contains

       X-Cluster

       (e.g.,

       8.0.32-X-Cluster-8.4.20-20251017

       ) -> 2.0 Standard Edition, this skill is not applicable, please use the

       polardbx-standard

       skill.
   • After confirming it's 2.0 Enterprise Edition, execute

     SHOW CREATE DATABASE db_name;

     to confirm it's in AUTO mode (MODE = 'auto').
   • Version number affects feature availability (e.g., NEW SEQUENCE requires 5.4.14+, CCI requires a newer version).
2. Confirm table type requirements:
   • Small tables or dictionary tables -> Broadcast Table

     BROADCAST

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

     SINGLE

     (stored only on one DN).
   • Other cases -> Partitioned Table (default), select appropriate partition key and partition strategy.
3. Use PolarDB-X safe defaults when generating SQL:
   • Avoid unsupported MySQL features (stored procedures/triggers/EVENT/SPATIAL, etc.).
   • Use

     KEY

     or

     HASH

     partitioning instead of MySQL's AUTO_INCREMENT primary key write hotspots.
   • Consider creating a Global Secondary Index (GSI) when querying with non-partition keys.
   • Use Sequence instead of relying on AUTO_INCREMENT continuity when you need globally unique IDs.
4. If the user provides MySQL SQL, perform compatibility checks:
   • Replace unsupported features and provide PolarDB-X alternatives.
   • Clearly mark behavioral differences and version requirements.
5. 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 scanning status and determine if full shard scanning exists.

   • EXPLAIN ANALYZE

     to actually execute and collect runtime statistics.

Quick Reference for Core Differences

• Three table types: Single Table (

  SINGLE

  ), Broadcast Table (

  BROADCAST

  ), Partitioned Table (default); choose based on data volume and access pattern.
• Partitioned tables: Support KEY/HASH/RANGE/LIST/RANGE COLUMNS/LIST COLUMNS/CO_HASH + sub-partitioning (49 combinations).
• Global Secondary Index (GSI): Solves the full shard scanning problem caused by non-partition key queries, supports three types: GSI / UGSI / Clustered GSI.
• Columnar Index (CCI): Hybrid row-column storage, accelerates OLAP analysis queries via

  CLUSTERED COLUMNAR INDEX

  .
• Sequence: Globally unique sequence, default type is

  NEW SEQUENCE

  (5.4.14+), a distributed alternative to AUTO_INCREMENT.
• Distributed transactions: Based on TSO global clock + MVCC + 2PC, strongly consistent by default; single-shard transactions are automatically optimized to local transactions.
• Table groups: Tables with the same partitioning rules are bound to the same table group, ensuring JOIN computation is pushed down and avoiding cross-shard data movement.
• TTL tables: Automatically expire and clean up cold data based on time columns, can cooperate with CCI to achieve hot-cold data separation.
• Unsupported MySQL features: Stored procedures/triggers/EVENT/SPATIAL/GEOMETRY/LOAD XML/HANDLER, etc.
• Does not support STRAIGHT_JOIN / NATURAL JOIN: Use standard JOIN syntax instead.
• Does not support := assignment operator: Move the logic to the application layer.
• Subqueries are not supported in HAVING/JOIN ON clauses: Rewrite subqueries as JOIN or CTE.

Reference Files (Within This Skill)

• skills/polardbx-sql/references/create-table.md

  - Table creation syntax, table types (single/broadcast/partitioned tables), partitioning strategies, sub-partitioning, partition management operations.

• skills/polardbx-sql/references/gsi.md

  - Creation, querying, and restrictions of Global Secondary Index (GSI/UGSI/Clustered GSI).

• skills/polardbx-sql/references/cci.md

  - Creation, usage, and applicable scenarios of Columnar Index (CCI).

• skills/polardbx-sql/references/sequence.md

  - Sequence types (NEW/GROUP/SIMPLE/TIME), creation, and usage.

• skills/polardbx-sql/references/transactions.md

  - Distributed transaction model, isolation levels, and notes.

• skills/polardbx-sql/references/mysql-compatibility-notes.md

  - Compatibility differences and development restrictions between MySQL and PolarDB-X.

• skills/polardbx-sql/references/explain.md

  - Usage of EXPLAIN series commands and execution plan diagnosis.

• skills/polardbx-sql/references/ttl-table.md

  - TTL table definition, cold data archiving, and cleanup scheduling.

• skills/polardbx-sql/references/online-ddl.md

  - Online DDL judgment, lock-free execution strategy, long transaction check, DMS lock-free changes, etc.