alibabacloud-polardbx-sql

What this skill does

# 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:

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

## Installation

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

```bash
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:

| Parameter Name | Required/Optional | Description | Default Value |
|---------------|-------------------|-------------|---------------|
| host | Required | PolarDB-X instance connection address | None |
| port | Required | PolarDB-X instance port | 3306 |
| user | Required | Database username | None |
| password | Required | Database password | None |
| database | Required | Target database name | None |

## 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](references/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](references/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 requiremen