alibabacloud-polardbx-sql

PolarDB-X SQL（侧重 MySQL 兼容性）

为 PolarDB-X 2.0 企业版（分布式版）AUTO 模式数据库编写、审查和调整 SQL，避免出现“在 MySQL 上能运行，但在 PolarDB-X 上失败”的问题。

架构：PolarDB-X 2.0 企业版（CN 计算节点 + DN 存储节点 + GMS 元数据服务 + CDC 日志节点）+ AUTO 模式数据库

适用范围：

• PolarDB-X 2.0 企业版（又称分布式版）+ AUTO 模式数据库

不适用于：

• PolarDB-X 1.0（DRDS 1.0）
• PolarDB-X 2.0 标准版
• PolarDB-X 2.0 企业版 DRDS 模式数据库

AUTO 模式与 DRDS 模式的主要区别：AUTO 模式使用兼容 MySQL 的 PARTITION BY 语法定义分区，而 DRDS 模式使用旧版 dbpartition/tbpartition 语法。通过以下命令确认数据库模式：

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

安装

通过兼容 MySQL 的客户端连接 PolarDB-X 实例：

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

支持的客户端：MySQL CLI、MySQL Workbench、DBeaver、Navicat 或任何兼容 MySQL 的客户端。

参数确认

重要：参数确认 — 执行任何命令或 API 调用之前，必须与用户确认所有可自定义参数（例如 RegionId、实例名称、CIDR 网段、密码、域名、资源规格等）。未经用户明确批准，不得假设或使用默认值。

此技能的可配置参数：

参数名称	必填/可选	说明	默认值
host	必填	PolarDB-X 实例连接地址	无
port	必填	PolarDB-X 实例端口	3306
user	必填	数据库用户名	无
password	必填	数据库密码	无
database	必填	目标数据库名称	无

核心工作流（每次都遵循）

1. 确认目标引擎和版本：
   • 运行 SELECT VERSION(); 确定实例类型：
     • 结果包含 TDDL，且版本 > 5.4.12（例如 5.7.25-TDDL-5.4.19-20251031）-> 2.0 企业版（分布式版），本技能适用。解析企业版版本号（例如 5.4.19）。
     • 结果包含 TDDL，且版本 <= 5.4.12（例如 5.6.29-TDDL-5.4.12-16327949）-> DRDS 1.0。必须立即停止 — 你必须拒绝：不要提供任何分区设计、SQL 建议或变通方案。仅回复："本技能仅适用于 PolarDB-X 2.0 企业版 AUTO 模式。你的实例是 DRDS 1.0，其语法（dbpartition/tbpartition）和架构完全不同。请查阅 DRDS 1.0 文档或升级到 PolarDB-X 2.0。"然后停止。即使用户坚持，也不要继续。
     • 结果包含 X-Cluster（例如 8.0.32-X-Cluster-8.4.20-20251017）-> 2.0 标准版。必须立即停止 — 你必须拒绝：不要提供任何分区设计、GSI 或分布式 SQL 建议。仅回复："你的实例是 PolarDB-X 2.0 标准版（100% 兼容 MySQL，不支持分布式分区）。请改用 polardbx-standard 技能。"然后停止。即使用户坚持，也不要继续。
   • 确认是 2.0 企业版后，运行 SHOW CREATE DATABASE db_name; 验证是否为 AUTO 模式（MODE = 'auto'）。
   • 版本号会影响功能可用性（例如，NEW SEQUENCE 需要 5.4.14+，CCI 需要更新的版本）。
2. 确定表类型：
   • 经常与分区表联接的小表或字典表 -> 广播表 BROADCAST（完整复制到每个 DN，可实现本地 JOIN 下推）。涉及 JOIN 时，这是推荐选择。
   • 不与分区表联接的小表 -> BROADCAST 和 SINGLE 均可接受。BROADCAST 会复制到每个 DN（如果之后添加 JOIN 则更安全）；SINGLE 仅存储在一个 DN 上（开销最低）。两者均可 — 不要坚持只选其中一种。
   • 其他情况 -> 分区表（默认），选择合适的分区键和策略。
3. 分区方案设计（针对分区表）：
   • 收集 SQL 访问模式数据 （前提条件 — 始终建议在最终确定分区键前收集数据）：优先使用 SQL Insight（最准确）；无法使用时，可通过慢查询日志和应用代码分析，或让业务团队提供 SQL 模式作为替代方案。目标是获取该表的 SQL 模板清单（查询字段、执行频率、返回行数）。
   • 分区键选择 — 全面的多维度分析：列出所有候选字段，然后在提出建议前，按以下所有维度评估每个候选字段。不要仅凭单一维度提出建议：
     • 等值查询比例：该字段出现在等值条件中的 SQL 模板占比。
     • 基数：不同值的数量；基数越高，数据在各分区间分布越均匀。
     • 热点风险：少数值是否占据大量数据（例如，在订单表中，某些 buyer_ids 可能对应数百万行，而其他值对应的行数很少）。
     • 主键 / 唯一键状态：主键和唯一键具有最高基数，且热点风险为零。
     • 语义分析：根据表类型和字段含义推断查询模式。例如，订单表中的 order_id 肯定会被频繁查询（订单详情查询、状态检查、支付回调），即使用户只提到 buyer_id 查询。 最佳分区键是综合各维度后得分较高的候选字段。可以通过创建 GSI 优化对非分区键字段的高频查询。经典示例：订单表 → 以 order_id（主键，基数最高、热点风险为零，且从语义上看查询频率高）作为分区键，并在 buyer_id 上创建 GSI（buyer 维度查询占比高，但存在偏斜风险，因为某些 buyer 产生的订单远多于其他 buyer）。
   • GSI 选择：根据写入量决定策略 — 写入量较低的表可以自由创建 GSI；为高频查询的非分区键字段创建 GSI；基数低的字段和时间字段不适合作为 GSI；总是与其他字段组合出现、从不单独出现的字段不需要独立 GSI。GSI 类型：返回行数较少时使用常规 GSI，一对多场景使用 Clustered GSI，唯一约束使用 UGSI。GSI 语法必须包含 PARTITION BY KEY(...) PARTITIONS N — 完整语法请参阅 gsi.md。
   • 分区算法：约 90% 的工作负载使用单级 HASH/KEY；订单类多维查询使用 CO_HASH；基于时间的数据清理使用 HASH+RANGE；多租户场景使用 LIST+HASH。对于单列，HASH 和 KEY 等价。
   • 分区数量：256 适用于绝大多数工作负载；分区数量应为 DN 节点数的数倍；单个分区应少于 1 亿行。
   • 迁移工作流（单表转换为分区表的三步法）：(1) 先将其转换为只有 1 个分区的分区表（保留唯一性）-> (2) 创建所需的 GSI/UGSI -> (3) 更改为目标分区数。详情请参阅 partition-design-best-practice.md。
4. 生成 SQL 时采用 PolarDB-X 安全默认设置：
   • 避免使用不受支持的 MySQL 功能（存储过程/触发器/EVENT/SPATIAL 等）。
   • 使用 KEY 或 HASH 分区，避免 MySQL AUTO_INCREMENT 主键造成写入热点。
   • 需要查询非分区键时，可考虑创建全局二级索引（GSI）。
5. 如果用户提供 MySQL SQL，执行兼容性检查：
   • 替换不受支持的功能，并提供 PolarDB-X 替代方案。
   • 明确标注行为差异和版本要求。
6. SQL 运行缓慢或出现错误时，使用 PolarDB-X 诊断工具：
   • 使用 EXPLAIN 查看逻辑执行计划。
   • 使用 EXPLAIN EXECUTE 查看下推到 DN 的物理执行计划。
   • 使用 EXPLAIN SHARDING 查看分片扫描详情并检查是否发生全分片扫描。
   • 使用 EXPLAIN ANALYZE 实际执行并收集运行时统计信息。

关键差异速查

• 三种表类型：单表（SINGLE）、广播表（BROADCAST）、分区表（默认）；根据数据量和访问模式选择。
• 分区表：支持 KEY/HASH/RANGE/LIST/RANGE COLUMNS/LIST COLUMNS/CO_HASH，以及二级分区（49 种组合）。
• 主键和唯一键：分为全局键（全局唯一）和本地键（分区内唯一）；单表、广播表和自动分区表始终使用全局键；手动分区表在分区列属于主键/唯一键列的子集时使用全局键，否则使用本地键（存在数据重复和 DDL 失败的风险）。核心原则：优先从现有主键/唯一键列中选择分区键，以自然保证全局唯一性——不要修改用户现有的主键定义来添加分区列。
• 全局二级索引 GSI：解决非分区键查询导致的全分片扫描问题，支持 GSI / UGSI / Clustered GSI 类型。重要：GSI 必须指定自己的 PARTITION BY 子句——它是独立分区的表，而不是常规 MySQL 索引。正确语法：

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

  经典分区设计——订单表：候选列为 order_id（PK）和 buyer_id。综合分析：order_id 的基数最高（每行唯一）、热点风险为零、是主键，且语义上查询频率高（订单详情/状态/支付查询）；buyer_id 的买家维度查询占比较高，但可能存在数据分布倾斜（部分买家产生的订单远多于其他买家）。结论：使用 order_id 作为分区键，并在 buyer_id 上创建 Clustered GSI。
• 聚簇列存索引 CCI：采用行列混合存储，通过 CLUSTERED COLUMNAR INDEX 加速 OLAP 分析查询。
• 序列：全局唯一序列，默认类型为 NEW SEQUENCE（5.4.14+），是 AUTO_INCREMENT 的分布式替代方案。
• 分布式事务：基于 TSO 全局时钟、MVCC 和 2PC，默认提供强一致性；单分片事务会自动优化为本地事务。
• 表组：将分区规则相同的表绑定到同一表组，确保 JOIN 计算下推，避免跨分片数据洗牌。
• TTL 表：根据时间列自动过期并清理冷数据，可与 CCI 配合实现冷热数据分离。
• 不支持的 MySQL 功能：存储过程/触发器/EVENT/SPATIAL/GEOMETRY/LOAD XML/HANDLER 等。
• 不支持 STRAIGHT_JOIN / NATURAL JOIN：请改用标准 JOIN 语法。
• 不支持 := 赋值运算符：请将逻辑移至应用层。
• HAVING/JOIN ON 子句中不支持子查询：请将子查询改写为 JOIN 或 CTE。

最佳实践

1. 选择合适的表类型：对于与分区表进行联接的小型表或字典表，使用广播表。对于不与分区表联接的小型表，BROADCAST 和 SINGLE 均可接受。其他情况一律使用分区表。
2. 通过全面的多维分析选择分区键：务必先建议收集 SQL 访问模式数据（优先使用 SQL Insight）。对每个候选字段，分析所有维度——等值查询比例、基数、热点风险、PK/UK 状态和字段语义——然后选择所有维度综合评分最高的候选项。切勿仅依据单一维度作出决定。记得根据表和字段语义推断查询模式（例如，订单表中的 order_id 肯定会经常用于查询订单详情、检查状态和处理支付回调）。
3. 优先选择 PK/UK 列作为分区键：选择分区键时，优先从现有主键或唯一键列中选取——这样无需修改架构，就能自然地使 PK/UK 成为 Global（全局唯一）。不要修改用户现有的主键定义来添加分区列。如果 PK 列不适合作为分区键（例如，没有业务含义的自增 id），完全可以选择其他业务列作为分区键——此时 PK 将变为 Local（仅在分区内唯一）；向用户说明 Local PK 的风险，并确保自增/Sequence 机制能够避免跨分区 PK 冲突。
4. 合理创建 GSI：根据写入量决定 GSI 策略；返回行数少时使用普通 GSI，一对多场景使用 Clustered GSI，唯一性约束使用 UGSI；不要为低比例 SQL 创建 GSI；定期使用 INSPECT INDEX 清理冗余 GSI。每个 GSI 都必须有自己的 PARTITION BY KEY(...) PARTITIONS N 子句；绝不要编写没有 PARTITION BY 的裸 GLOBAL INDEX idx(col)。
5. 使用 256 个分区：256 个分区适用于绝大多数工作负载，其数量应为 DN 节点数的数倍。
6. 单表转分区表时采用三步法：先转换为 1 个分区（保留唯一性）→ 创建 GSI/UGSI → 更改为目标分区数，以避免唯一性约束出现空档。
7. 不要强求低比例 SQL 命中分区键：分区设计是一项务实的工作；低 QPS 跨分片查询的总成本有限，不要为每个查询字段都创建 GSI。
8. 使用表组优化 JOIN：将经常联接的表绑定到同一个表组，并使用相同的分区规则。
9. 避免不支持的 MySQL 语法：不要使用存储过程、触发器、EVENT、SPATIAL、NATURAL JOIN、:= 等。
10. 避免在 HAVING/JOIN ON 中使用子查询：改写为 JOIN 或 CTE。
11. 使用 EXPLAIN 命令进行诊断：遇到 SQL 性能问题时，优先使用 EXPLAIN SHARDING 和 EXPLAIN ANALYZE。
12. Online DDL 前检查长事务：执行 DDL 前检查是否存在长事务，避免等待 MDL 锁。
13. 使用 TTL 表管理冷数据：对于带有时间属性的大型表，使用 TTL 表自动清理过期数据。
14. 使用 Keyset 分页高效翻页：避免使用 LIMIT M, N 进行深度分页（成本为 O(M+N)，在分布式系统中甚至更高）；记录每批最后一行的排序值，作为下一批的 WHERE 条件；当排序列可能存在重复值时，使用 (sort_column, id) 元组比较；确保排序列上有合适的复合索引。
15. Range 分区表使用自动添加分区：PolarDB-X 使用专有的 ALTER TABLE ... MODIFY TTL SET 语法（包含 TTL_EXPR、TTL_PART_INTERVAL、ARCHIVE_TYPE、ARCHIVE_TABLE_PRE_ALLOCATE 等多个参数）配置自动预创建分区。此语法不是标准 SQL，不能靠猜测得出——**生成任何自动添加分区配置前，都必须阅读 references/auto-add-range-parts.md 以了解准确的 SQL 语法。**需要 5.4.20+ 版本。

参考链接

参考资料	描述
references/create-table.md	CREATE TABLE 语法、表类型（单表/广播表/分区表）、分区策略、二级分区、分区管理
references/partition-design-best-practice.md	分区设计最佳实践：分区键/GSI/算法/数量选择、三步迁移、完整示例
references/primary-key-unique-key.md	主键和唯一键的全局/本地分类、规则、风险和建议
references/gsi.md	全局二级索引 GSI/UGSI/聚簇 GSI 的创建、查询和限制
references/cci.md	聚簇列存索引 CCI 的创建、用法和适用场景
references/sequence.md	Sequence 类型（NEW/GROUP/SIMPLE/TIME）、创建和用法
references/transactions.md	分布式事务模型、隔离级别和注意事项
references/mysql-compatibility-notes.md	MySQL 与 PolarDB-X 的兼容性差异和开发限制
references/explain.md	EXPLAIN 命令变体和执行计划诊断
references/ttl-table.md	TTL 表定义、冷数据归档和清理调度
references/online-ddl.md	在线 DDL 评估、无锁执行策略、长事务检查、DMS 无锁变更
references/pagination-best-practice.md	高效分页：Keyset 分页、逐分片遍历、索引要求、Java 示例
references/auto-add-range-parts.md	范围分区自动添加：基于 TTL 的分区预创建、一级/二级配置、管理命令
references/cli-installation-guide.md	Alibaba Cloud CLI 安装指南