⚡

SQL优化综合技能 data-sql-optimization

此技能专注于事务型(OLTP)数据库的SQL优化，提供查询性能分析、索引策略、模式设计、迁移、备份恢复、高可用性设置等全面指导，支持PostgreSQL、MySQL、SQL Server、Oracle、SQLite等主流平台。关键词：SQL优化，数据库性能，OLTP，索引策略，查询调优，数据库迁移，备份恢复，高可用性，安全性能。

后端开发 0 次安装 0 次浏览 更新于 3/7/2026

名称: data-sql-optimization 描述: “生产级SQL优化用于OLTP系统：EXPLAIN/计划分析、平衡索引、模式和查询设计、迁移、备份/恢复、高可用性、安全以及跨PostgreSQL、MySQL、SQL Server、Oracle、SQLite的安全性能调优。”

SQL优化 — 综合参考

此技能提供可操作的清单、模式和模板，用于事务型(OLTP) SQL优化：以测量为先的故障排查、EXPLAIN/计划解读、平衡索引（避免过度索引）、性能监控、模式演进、迁移、备份/恢复、高可用性和安全。

支持的平台： PostgreSQL、MySQL、SQL Server、Oracle、SQLite

对于OLAP/分析： 参见 data-lake平台（ClickHouse、DuckDB、Doris、StarRocks）

快速参考

任务 工具/框架 命令 何时使用
查询性能分析 EXPLAIN ANALYZE EXPLAIN (ANALYZE, BUFFERS) SELECT ...（PG）/ EXPLAIN ANALYZE SELECT ...（MySQL） 诊断慢查询，识别缺失索引
查找慢查询 pg_stat_statements / 慢查询日志 SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; 识别生产中的性能瓶颈
索引分析 pg_stat_user_indexes / SHOW INDEX SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0; 查找未使用的索引，验证索引覆盖
模式迁移 Flyway / Liquibase flyway migrate / liquibase update 版本控制的数据库变更
备份与恢复 pg_dump / mysqldump pg_dump -Fc dbname > backup.dump 点时间恢复，灾难恢复
复制设置 流式 / GTID 配置 postgresql.conf / my.cnf 高可用性，读取扩展
安全调优循环 测量 -> 解释 -> 变更 -> 验证 使用调优工作表模板 减少延迟/成本，无回归

决策树：选择正确方法

查询性能问题？
    ├─ 先识别慢查询？
    │   ├─ PostgreSQL -> pg_stat_statements（按total_exec_time排序的顶部查询）
    │   └─ MySQL -> 性能模式 / 慢查询日志
    │
    ├─ 分析执行计划？
    │   ├─ PostgreSQL -> EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
    │   ├─ MySQL -> EXPLAIN FORMAT=JSON 或 EXPLAIN ANALYZE
    │   └─ SQL Server -> SET STATISTICS IO ON; SET STATISTICS TIME ON;
    │
    ├─ 需要索引策略？
    │   ├─ PostgreSQL -> B-tree（默认）、GIN（JSONB）、GiST（空间）、部分索引
    │   ├─ MySQL -> BTREE（默认）、FULLTEXT（文本搜索）、SPATIAL
    │   └─ 检查：表大于10k行 AND 选择性小于10% AND 验证速度提升10倍以上
    │
    ├─ 需要模式变更？
    │   ├─ 新数据库 -> 模板模式设计.md
    │   ├─ 修改模式 -> 模板迁移.md（Flyway/Liquibase）
    │   └─ 大表（MySQL）-> gh-ost / pt-online-schema-change（避免锁）
    │
    ├─ 高可用性设置？
    │   ├─ PostgreSQL -> 流式复制（模板复制高可用.md）
    │   └─ MySQL -> GTID基于复制（模板复制高可用.md）
    │
    ├─ 备份/灾难恢复？
    │   └─ 模板备份恢复.md（pg_dump, mysqldump, PITR）
    │
    └─ 大数据集分析（OLAP）？
        └─ 参见 data-lake平台（ClickHouse、DuckDB、Doris、StarRocks）

何时使用此技能

Codex应在用户请求以下内容时调用此技能：

查询优化（现代方法）

  • SQL查询性能审查和调优
  • EXPLAIN/计划解读与优化建议
  • 索引创建策略与平衡方法（避免过度索引）
  • 使用pg_stat_statements或性能模式排查慢查询
  • 识别和修复SQL反模式，并提供操作修正
  • 查询重写建议或从慢模式迁移到快模式
  • 统计维护和自动分析配置

数据库操作

  • 模式设计，考虑规范化与性能权衡
  • 数据库迁移，使用版本控制（Liquibase、Flyway）
  • 备份和恢复策略（点时间恢复、自动化测试）
  • 高可用性和复制设置（流式、GTID基于）
  • 数据库安全审计（访问控制、加密、SQL注入预防）
  • 锁分析和死锁故障排除
  • 连接池（pgBouncer、Pgpool-II、ProxySQL）

性能调优（现代标准）

  • 内存配置（work_mem、shared_buffers、effective_cache_size）
  • 自动化监控，使用pg_stat_statements和查询模式分析
  • 索引健康监控（未使用索引检测、索引膨胀分析）
  • 真空策略和自动真空调优（PostgreSQL）
  • InnoDB缓冲池优化（MySQL）
  • 分区剪裁改进（PostgreSQL 18+）

资源（最佳实践指南）

在以下文件中找到详细的操作模式和快速参考：

每个文件包括：

  • 可复制的清单（例如，“查询审查”、“索引设计”、“解释审查”）
  • 反模式，带操作修正和替代方案
  • 查询重写和索引策略示例
  • 故障排除指南（分步）

模板（可复制粘贴）

模板按数据库技术组织，以确保精确和清晰：

跨平台模板（所有数据库）

PostgreSQL模板

MySQL模板

Microsoft SQL Server模板

Oracle模板

SQLite模板

相关技能

基础设施与操作：

应用集成：

质量与安全：

数据工程：

导航

资源

模板

数据

操作深度探讨

参见 references/operational-patterns.md 获取：

  • 端到端优化清单和反模式修正
  • 数据库特定快速参考（PostgreSQL、MySQL、SQL Server、Oracle、SQLite）
  • 慢查询故障排除工作流和可靠性演练
  • 模板选择决策树和平台迁移说明

做 / 避免

好：做

  • 在任何优化前测量基线
  • 一次只改变一个变量
  • 验证变更后结果匹配
  • 在得出结论“需要索引”前更新统计
  • 用生产类似数据量测试
  • 记录所有优化决策
  • 在CI/CD中包含性能测试

坏：避免

  • 添加索引而不检查是否会被使用
  • 在生产查询中使用SELECT *
  • 为测试数据优化（使用代表性数据量）
  • 忽略索引对写入性能的影响
  • 变更前跳过EXPLAIN分析
  • 多个同时变更（无法归因改进）
  • 应用代码中的N+1查询模式

反模式快速参考

反模式 问题 修正
**SELECT *** 读取不必要的列 显式列列表
N+1查询 倍增的往返 JOIN或批量获取
缺失WHERE 全表扫描 添加谓词
索引列上的函数 无法使用索引 将函数移到右侧
隐式类型转换 索引绕过 明确匹配类型
LIKE ‘%前缀’ 前导通配符 = 扫描 全文搜索
无界结果集 内存爆炸 添加LIMIT/分页
OR条件 索引可能不被使用 UNION或重写

参见 references/sql-antipatterns.md 获取详细修正。

OLTP vs OLAP决策树

您的查询用于...？
├─ 点查找（按ID/键）？
│   └─ OLTP数据库（此技能）
│       - 确保适当索引
│       - 使用连接池
│       - 优化低延迟
│
├─ 最近数据的聚合（仪表板）？
│   └─ OLTP数据库（此技能）
│       - 考虑物化视图
│       - 索引常见过滤列
│       - 注意锁争用
│
├─ 全表扫描或历史分析？
│   └─ OLAP数据库（data-lake平台）
│       - ClickHouse、DuckDB、Doris
│       - 列存储
│       - 按日期分区
│
└─ 混合工作负载（两者）？
    └─ 分离OLTP和OLAP
        - OLTP用于事务
        - 复制到OLAP进行分析
        - 避免在主数据库上运行分析

可选：AI/自动化

注意：AI工具辅助但需要人工验证正确性。

  • EXPLAIN总结 — 从复杂计划中识别瓶颈
  • 查询重写建议 — 必须验证结果等价性
  • 索引推荐 — 首先检查选择性和写入影响

有限声明

  • AI无法确定正确查询结果
  • 自动化索引建议可能错过工作负载上下文
  • 生产变更需要人工审查

分析数据库（OLAP）

对于OLAP数据库和数据湖基础设施，参见 data-lake平台：

  • 查询引擎： ClickHouse、DuckDB、Apache Doris、StarRocks
  • 表格式： Apache Iceberg、Delta Lake、Apache Hudi
  • 转换： SQLMesh、dbt（暂存/集市层）
  • 摄取： dlt、Airbyte（连接器）
  • 流处理： Apache Kafka模式

此技能专注于事务型数据库优化（PostgreSQL、MySQL、SQL Server、Oracle、SQLite）。使用data-lake平台进行分析工作负载。

相关技能

此技能专注于查询优化在单个数据库内。对于相关工作流：

SQL转换与分析工程： -> ai-ml-data-science 技能

  • SQLMesh模板，用于构建暂存/中间/集市层
  • 增量模型（FULL、INCREMENTAL_BY_TIME_RANGE、INCREMENTAL_BY_UNIQUE_KEY）
  • DAG管理和模型依赖
  • SQL转换的单元测试和审计

数据摄取（加载到数据仓库）： -> ai-mlops 技能

  • dlt模板，用于从REST API、数据库提取
  • 加载到Snowflake、BigQuery、Redshift、Postgres、DuckDB
  • 增量加载模式（时间戳、基于ID、合并/更新插入）
  • 数据库复制（Postgres、MySQL、MongoDB -> 数据仓库）

数据湖基础设施： -> data-lake平台 技能

  • ClickHouse、DuckDB、Doris、StarRocks查询引擎
  • Iceberg、Delta Lake、Hudi表格式
  • Kafka流处理、Dagster/Airflow编排

用例决策：

  • 查询在生产中慢 -> 使用此技能（data-sql-optimization）
  • 用SQL构建特征管道 -> 使用ai-ml-data-science（SQLMesh）
  • 从API/数据库加载数据到数据仓库 -> 使用ai-mlops（dlt）
  • 大数据集分析（OLAP） -> 使用data-lake平台

外部资源

参见 data/sources.json 获取62+精选资源，包括：

核心文档：

  • RDBMS文档：PostgreSQL、MySQL、SQL Server、Oracle、SQLite、DuckDB官方文档
  • 查询优化：Use The Index, Luke、SQL Performance Explained、供应商优化指南
  • 模式设计：Database Refactoring（Fowler）、规范化指南、数据类型选择

现代优化（当前）：

  • PostgreSQL：官方发布说明和“当前”文档，用于规划器/优化器变更
  • MySQL：官方参考手册部分，用于EXPLAIN、优化器、性能模式
  • SQL Server / Oracle：官方文档，用于执行计划、索引、并发控制

操作与基础设施：

  • 高可用性与复制：流式复制、GTID基于复制、故障转移自动化
  • 迁移：Liquibase、Flyway版本控制和部署模式
  • 备份/恢复：pgBackRest、Percona XtraBackup、点时间恢复
  • 监控：pg_stat_statements、性能模式、EXPLAIN可视化器（Dalibo、depesz）
  • 安全：OWASP SQL注入预防、Postgres硬化、加密标准
  • 分析数据库：DuckDB扩展、Parquet规范、列存储模式

使用 references/operational-patterns.md 和模板目录获取详细工作流、迁移说明和可运行命令。