/alibabacloud-polardbx-sql

name: alibabacloud-polardbx-sql
description: |
  Design partition schemes, select partition keys, create GSI, and write SQL for PolarDB-X 2.0 Enterprise Edition AUTO mode databases, handling PolarDB-X vs MySQL differences (partitioned tables, GSI, CCI, Sequence, table groups, TTL, pagination, etc.).
  Use when designing partition schemes, selecting partition keys, converting single tables to partitioned tables, creating GSI/CCI indexes, writing or migrating SQL for PolarDB-X, or diagnosing slow queries on PolarDB-X.
  Triggers: "PolarDB-X SQL", "PolarDB-X create table", "partitioned table", "partition design", "partition scheme", "partition key", "GSI", "CCI", "Sequence", "MySQL migrate to PolarDB-X", "PolarDB-X compatibility", "single table to partitioned table", "convert to partitioned table", "large table", "table sharding", "distributed table", "AUTO mode", "pagination query", "Keyset pagination", "Range partition", "auto add partition", "PolarDB-X slow query", "full-shard scan"
metadata:
  version: 0.3.1

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:

| 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