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

Core Workflow (Follow each time)

⚠️ CRITICAL: If the user's question involves any of the following, switch to the dedicated skill immediately — do NOT handle these in this skill:

• TTL / data expiration / cold data archiving / auto-add partitions → switch to polardbx-ttl20 skill
• Online DDL / lock-free DDL / DDL safety / OMC / MDL lock → switch to polardbx-online-ddl skill
• Pagination / deep paging / large table traversal / LIMIT optimization → switch to polardbx-pagination skill
• CCI / columnar index / OLAP analytics / HTAP → switch to polardbx-cci skill

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: Complete PKs/UKs have high cardinality, but hot-key traffic and skew still depend on access patterns and the partition strategy.
     • 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, high cardinality, frequent ID lookups; check hot-key traffic) 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 (triggers/EVENTs/SPATIAL, etc.); check version and routine-specific limitations for supported stored procedures and SQL functions.
   • 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 the polardbx-plan-analysis skill for execution plan analysis and diagnostics (EXPLAIN / EXPLAIN EXECUTE / EXPLAIN SHARDING / EXPLAIN ANALYZE).
7. When the user needs TTL (data expiration, cold data archiving, or auto-add partitions):
   • Use the polardbx-ttl20 skill for all TTL-related questions: cold data archiving, data expiration, and auto-add Range partitions.
   • Do NOT handle TTL questions in this skill — TTL syntax is unique to PolarDB-X and must be handled by the dedicated polardbx-ttl20 skill.

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 high cardinality (unique per row), PK status, and semantically high query frequency (order detail/status/payment lookups), but its hot-key traffic must still be checked; 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. All CCI questions (creation, query optimization, CCI vs GSI) must be handled by the polardbx-cci skill.
• Sequence: Distributed alternative to AUTO_INCREMENT. AUTO mode on supported versions (5.4.14+) defaults to NEW; DRDS mode defaults to GROUP. Verify with SHOW SEQUENCES.
• 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. All TTL questions (archiving, expiration, auto-add partitions) must be handled by the polardbx-ttl20 skill. Do NOT generate TTL SQL in this skill.
• Unsupported MySQL features: Triggers/EVENTs/SPATIAL/GEOMETRY/LOAD XML/HANDLER, etc. Stored procedures require 5.4.15+ and SQL-defined functions require 5.4.16+; check their specific restrictions.
• STRAIGHT_JOIN / NATURAL JOIN not supported: Use standard JOIN syntax instead.
• := assignment operator not supported: Move logic to the application layer.
• Subqueries: Scalar subqueries in HAVING are supported; check the actual query form and version. For unsupported JOIN ON subqueries, rewrite 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 unsupported triggers, EVENTs, SPATIAL, NATURAL JOIN, :=, etc. Verify routine support separately instead of rejecting all stored procedures/functions.
10. Check subquery compatibility by form: HAVING scalar subqueries are supported; rewrite unsupported forms such as restricted JOIN ON subqueries 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: Use the polardbx-online-ddl skill for all DDL safety assessment, lock-free execution, and long transaction checks.
13. Use TTL tables to manage cold data: For large tables with time attributes, use TTL tables to automatically clean up expired data. Use the polardbx-ttl20 skill for all TTL questions (archiving, expiration, auto-add partitions).
14. Use Keyset pagination for efficient paging: Use the polardbx-pagination skill for all pagination optimization, Keyset pagination, large table traversal, and data export batch queries.
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. Use the polardbx-ttl20 skill for the full configuration guide.

Reference Links