# SQL优化——综合参考该技能为**事务（OLTP）SQL 优化**提供了可操作的清单、模式和模板：测量优先分类、EXPLAIN/计划解释、平衡索引（避免过度索引）、性能监控、模式演变、迁移、备份/恢复、高可用性和安全性。 **支持的平台：** PostgreSQL、MySQL、SQL Server、Oracle、SQLite **对于 OLAP/Analytics：** 请参阅 [data-lake-platform]()（ClickHouse、DuckDB、Doris、StarRocks） ** ** ## 快速参考任务|工具/框架 |命令 |何时使用 ---|---|---|--- 查询性能分析|解释分析 | `解释（分析，缓冲区）选择 ...` (PG) / `解释分析选择 ...` (MySQL) |诊断慢速查询，识别丢失的索引查找慢查询 | pg_stat_statements / 慢查询日志 | `SELECT * FROM pg_stat_statements ORDER BY Total_exec_time DESC LIMIT 10;` |识别生产中的性能瓶颈指数分析| pg_stat_user_indexes / 显示索引 | `从 pg_stat_user_indexes 中选择 *，其中 idx_scan = 0;` |查找未使用的索引，验证索引覆盖率架构迁移 | Flyway/Liquibase| `flyway 迁移` / `liquibase 更新` |版本控制的数据库更改备份与恢复| pg_dump / mysqldump | pg_dump / mysqldump | `pg_dump -Fc 数据库名称 > backup.dump` |时间点恢复、灾难恢复复制设置 |流媒体/GTID |配置postgresql.conf/my.cnf |高可用性、读取扩展安全调谐环|测量 -> 解释 -> 更改 -> 验证 |使用调整工作表模板 |减少延迟/成本而不回归 ** ** ## 决策树：选择正确的方法查询性能问题？ ├─ 首先识别慢查询？ │ ├─ PostgreSQL -> pg_stat_statements (total_exec_time 的热门查询) │ └─ MySQL -> 性能架构/慢查询日志 │ ├─ 分析执行计划？ │ ├─ PostgreSQL -> 解释（分析、缓冲区、详细） │ ├─ MySQL -> EXPLAIN FORMAT=JSON 或 EXPLAIN ANALYZE │ └─ SQL Server -> 设置统计IO ON;设置统计时间； │ ├─ 需要索引策略吗？ │ ├─ PostgreSQL -> B-tree（默认）、GIN（JSONB）、GiST（空间）、部分索引 │ ├─ MySQL -> BTREE (默认), FULLTEXT (文本搜索), SPATIAL │ └─ 检查：表 >10k 行并且选择性 <10% 并且已验证 10x+ 加速 │ ├─ 需要更改架构吗？ │ ├─ 新建数据库 -> template-schema-design.md │ ├─ 修改schema -> template-migration.md (Flyway/Liquibase) │ └─ 大表(MySQL) -> gh-ost / pt-online-schema-change (避免锁) │ ├─ 高可用性设置？ │ ├─ PostgreSQL -> 流式复制(template-replication-ha.md) │ └─ MySQL -> 基于 GTID 的复制（template-replication-ha.md） │ ├─ 备份/灾难恢复？ │ └─ template-backup-restore.md (pg_dump, mysqldump, PITR) │ └─ 大数据集分析（OLAP）？ └─ 请参阅数据湖平台（ClickHouse、DuckDB、Doris、StarRocks） ** ** ## 何时使用此技能当用户提出以下要求时，Codex 应调用此技能： ### 查询优化（现代方法） * SQL查询性能审查和调优 * EXPLAIN/计划解释与优化建议 * 平衡方法的索引创建策略（避免过度索引） * 使用 pg_stat_statements 或 Performance Schema 对慢速查询进行故障排除 * 通过操作修复来识别和修复 SQL 反模式 * 查询重写建议或从慢速模式迁移到快速模式 * 统计维护和自动分析配置 ### 数据库操作 * 具有标准化和性能权衡的模式设计 * 具有版本控制的数据库迁移（Liquibase、Flyway） * 备份和恢复策略（时间点恢复、自动化测试） * 高可用性和复制设置（流式传输、基于 GTID） * 数据库安全审计（访问控制、加密、防止SQL注入） * 锁分析和死锁排查 * 连接池（pgBouncer、Pgpool-II、ProxySQL） ### 性能调整（现代标准） * 内存配置（work_mem、shared_buffers、 effective_cache_size） * 使用 pg_stat_statements 和查询模式分析进行自动监控 * 索引健康状况监控（未使用索引检测、索引膨胀分析） * Vacuum策略和autovacuum调优（PostgreSQL） * InnoDB缓冲池优化（MySQL） * 分区修剪改进（PostgreSQL 18+） ** ** ## 资源（最佳实践指南）在以下位置查找详细的操作模式和快速参考： * **SQL 最佳实践** : [references/sql-best-practices.md]() * **查询调优模式** : [references/query-tuning-patterns.md]() * **索引策略** : [references/index-patterns.md]() * **解释/分析**：[references/explain-analysis.md]() * **SQL 反模式** : [references/sql-antipatterns.md]() * **外部来源**：[data/sources.json]() — 供应商文档和参考链接 * **操作标准**：[references/operational-patterns.md]() — 深入的操作清单、特定于数据库的指导和模板选择树 * **连接池** : [references/connection-pooling-patterns.md]() — PgBouncer、RDS 代理、池大小调整、连接泄漏故障排除 * **分区策略** : [references/partition-strategies.md]() — 范围/列表/哈希分区、修剪、维护、迁移模式 * **监控和警报**：[references/monitoring-alerting-patterns.md]() — pg_stat_statements 仪表板、警报阈值、慢速查询管道每个文件包括： * 复制粘贴准备清单（例如，“查询审查”、“索引设计”、“解释审查”） * 具有操作修复和替代方案的反模式 * 查询重写和索引策略示例 * 故障排除指南（分步） ** ** ## 模板（复制粘贴就绪）模板通过数据库技术进行组织，以确保精确性和清晰度： ### 跨平台模板（所有数据库） * [assets/cross-platform/template-query-tuning.md]() \- 通用查询优化 * [assets/cross-platform/template-explain-analysis.md]() \- 执行计划分析 * [assets/cross-platform/template-performance-tuning-worksheet.md]() \- **新** 4步调整工作流程（测量 -> 解释 -> 更改 ->验证） * [assets/cross-platform/template-index.md]() \- 索引设计模式 * [assets/cross-platform/template-slow-query.md]() \- 慢速查询分类 * [assets/cross-platform/template-schema-design.md]() \- 模式建模 * [assets/cross-platform/template-migration.md]() \- 数据库迁移 * [assets/cross-platform/template-backup-restore.md]() \- 备份/灾难恢复规划 * [assets/cross-platform/template-security-audit.md]() \- 安全审查 * [assets/cross-platform/template-diagnostics.md]() \- 性能诊断 * [assets/cross-platform/template-lock-analysis.md]() \- 锁定故障排除 ### PostgreSQL 模板 * [assets/postgres/template-pg-explain.md]() \- PostgreSQL EXPLAIN 分析 * [assets/postgres/template-pg-index.md]() \- PostgreSQL 索引 (B-tree, GIN, GiST) * [assets/postgres/template-replication-ha.md]() \- 流式复制和 HA ### MySQL 模板 * [assets/mysql/template-mysql-explain.md]() \- MySQL EXPLAIN 分析 * [assets/mysql/template-mysql-index.md]() \- MySQL/InnoDB 索引 * [assets/mysql/template-replication-ha.md]() \- MySQL 复制和 HA ### Microsoft SQL Server 模板 * [assets/mssql/template-mssql-explain.md]() \- SQL Server EXPLAIN/SHOWPLAN 分析 * [assets/mssql/template-mssql-index.md]() \- SQL Server 索引和调优 ### Oracle 模板 * [assets/oracle/template-oracle-explain.md]() \- Oracle EXPLAIN 计划审查和调整 ### SQLite 模板 * [assets/sqlite/template-sqlite-optimization.md]() \- SQLite 优化和编译指示指导 ** ** ## 相关技能 **基础设施和运营：** * [../ops-devops-platform/SKILL.md]() — 基础设施、备份、监控和事件响应 * [../qa-observability/SKILL.md]() — 性能监控、分析和指标 * [../qa-debugging/SKILL.md]() — 生产调试模式 **应用程序集成：** * [../software-backend/SKILL.md]() — API/数据库集成和应用模式 * [../software-architecture-design/SKILL.md]() — 系统设计和数据架构 * [../dev-api-design/SKILL.md]() — REST API 和数据库交互模式 **质量与安全：** * [../qa-resilience/SKILL.md]() — 弹性、断路器和故障处理 * [../software-security-appsec/SKILL.md]() — 数据库安全、身份验证、SQL 注入预防 * [../qa-testing-strategy/SKILL.md]() — 数据库测试策略 **数据工程：** * [../ai-ml-data-science/SKILL.md]() — SQLMesh、dbt、数据转换 * [../ai-mlops/SKILL.md]() — 数据管道、ETL 和仓库加载 (dlt) * [../ai-ml-timeseries/SKILL.md]() — 时间序列数据库和预测 ** ** ## 导航 **资源** * [references/explain-analysis.md]() * [references/query-tuning-patterns.md]() * [references/operational-patterns.md]() * [references/sql-antipatterns.md]() * [references/index-patterns.md]() * [references/sql-best-practices.md]() * [references/connection-pooling-patterns.md]() * [references/partition-strategies.md]() * [references/monitoring-alerting-patterns.md]() **模板** * [assets/cross-platform/template-slow-query.md]() * [assets/cross-platform/template-backup-restore.md]() * [assets/cross-platform/template-schema-design.md]() * [assets/cross-platform/template-explain-analysis.md]() * [assets/cross-platform/template-performance-tuning-worksheet.md]() * [assets/cross-platform/template-security-audit.md]() * [assets/cross-platform/template-diagnostics.md]() * [assets/cross-platform/template-index.md]() * [assets/cross-platform/template-migration.md]() * [assets/cross-platform/template-lock-analysis.md]() * [assets/cross-platform/template-query-tuning.md]() * [assets/oracle/template-oracle-explain.md]() * [assets/sqlite/template-sqlite-optimization.md]() * [assets/postgres/template-pg-index.md]() * [assets/postgres/template-replication-ha.md]() * [assets/postgres/template-pg-explain.md]() * [assets/mysql/template-mysql-explain.md]() * [assets/mysql/template-mysql-index.md]() * [assets/mysql/template-replication-ha.md]() * [assets/mssql/template-mssql-index.md]() * [assets/mssql/template-mssql-explain.md]() **数据** * [data/sources.json]() — 策划的外部参考 ** ** ## 运营深入探讨请参阅 [references/operational-patterns.md]()： * 端到端优化清单和反模式修复 * 特定于数据库的快速参考（PostgreSQL、MySQL、SQL Server、Oracle、SQLite） * 慢查询故障排除工作流程和可靠性演练 * 模板选择决策树和平台迁移说明 ** ** ## 做/避免 ### 好：做 * 在任何优化之前测量基线 * 一次改变一个变量 * 查询更改后验证结果是否匹配 * 在得出“需求指数”之前更新统计数据 * 使用类似生产的数据量进行测试 * 记录所有优化决策 * 包括 CI/CD 中的性能测试 ### 不好：避免 * 添加索引而不检查它们是否会被使用 * 在生产查询中使用 SELECT * * 优化测试数据（使用代表性体积） * 忽略索引写入性能影响 * 跳过更改前的 EXPLAIN 分析 * 多个同时发生的变化（不能归因于改进） * 应用程序代码中的N+1查询模式 ** ** ## 反模式快速参考反模式 |问题 |修复 ---|---|--- **选择*** |读取不必要的列 |显式列列表 **N+1 次查询** |成倍增加的往返行程 | JOIN 或批量获取 **缺少哪里** |全表扫描|添加谓词 **索引列上的函数** |无法使用索引 |将功能移至 RHS **隐式类型转换** |索引绕过 |显式匹配类型 **LIKE '%prefix'** |前导通配符 = 扫描 |全文检索 **无限结果集** |内存爆炸|添加限制/分页 **或条件** |不能使用索引 |联合或重写有关详细修复，请参阅 [references/sql-antipatterns.md]()。 ** ** ## OLTP 与 OLAP 决策树您的查询是...？ ├─ 点查找（通过 ID/key）？ │ └─ OLTP数据库（本技能） │ - 确保正确的索引 │ - 使用连接池 │ - 优化低延迟 │ ├─ 最近数据的聚合（仪表板）？ │ └─ OLTP数据库（本技能） │ - 考虑物化视图 │ - 索引常用过滤列 │ - 监视锁争用 │ ├─ 全表扫描还是历史分析？ │ └─ OLAP数据库（数据湖平台） │ - ClickHouse、DuckDB、Doris │ - 列式存储 │ - 按日期分区 │ └─ 混合工作负载（两者）？ └─ 分离OLTP和OLAP - 用于事务的OLTP - 复制到 OLAP 进行分析 - 避免在主数据库上运行分析 ** ** ## 可选：人工智能/自动化 > **注意**：人工智能工具可以提供帮助，但需要人工验证正确性。 * **解释总结** — 从复杂的计划中识别瓶颈 * **查询重写建议** — 必须验证结果等效性 * **索引建议** — 首先检查选择性并写入影响 ### 有界声明 * AI无法判断正确的查询结果 * 自动索引建议可能会错过工作负载上下文 * 生产变更需要人工审核 ** ** ## 分析数据库 (OLAP) 对于 OLAP 数据库和数据湖基础设施，请参阅 **[data-lake-platform]()** ： * **查询引擎：** ClickHouse、DuckDB、Apache Doris、StarRocks * **表格格式：** Apache Iceberg、Delta Lake、Apache Hudi * **转换：** SQLMesh、dbt（分段/集市层） * **摄取：** dlt、Airbyte（连接器） * **流媒体：** Apache Kafka 模式该技能重点关注**事务数据库优化**（PostgreSQL、MySQL、SQL Server、Oracle、SQLite）。使用数据湖平台进行分析工作负载。 ** ** ## 相关技能该技能侧重于单个数据库内的**查询优化**。对于相关工作流程： **SQL 转换和分析工程：** -> **[ai-ml-data-science]()** 技能 * 用于构建暂存/中间/集市层的 SQLMesh 模板 * 增量模型（FULL、INCRMENTAL_BY_TIME_RANGE、INCRMENTAL_BY_UNIQUE_KEY） * DAG管理和模型依赖 * SQL 转换的单元测试和审计 **数据摄取（加载到仓库中）：** -> **[ai-mlops]()**技能 * 用于从 REST API、数据库中提取的 dlt 模板 * 加载到 Snowflake、BigQuery、Redshift、Postgres、DuckDB * 增量加载模式（时间戳、基于 ID、合并/更新插入） * 数据库复制（Postgres、MySQL、MongoDB -> 仓库） **数据湖基础设施：** -> **[data-lake-platform]()**技能 * ClickHouse、DuckDB、Doris、StarRocks 查询引擎 * Iceberg、Delta Lake、Hudi 表格式 * Kafka 流、Dagster/Airflow 编排 **用例决策：** * **生产中查询速度很慢** -> 使用此技能（data-sql-optimization） * **在 SQL 中构建功能管道** -> 使用 ai-ml-data-science (SQLMesh) * **将数据从 API/DB 加载到仓库** -> 使用 ai-mlops (dlt) * **大型数据集分析 (OLAP)** -> 使用 data-lake-platform ** ** ## 外部资源请参阅 [data/sources.json]() 了解 62 多个精选资源，包括： **核心文档：** * **RDBMS 文档**：PostgreSQL、MySQL、SQL Server、Oracle、SQLite、DuckDB 官方文档 * **查询优化** ：使用索引、Luke、SQL 性能解释、供应商优化指南 * **架构设计**：数据库重构（Fowler）、规范化指南、数据类型选择 **现代优化（当前）：** * **PostgreSQL**：官方发行说明和规划器/优化器更改的“当前”文档 * **MySQL** ：关于解释、优化器和性能模式的官方参考手册部分 * **SQL Server / Oracle**：执行计划、索引和并发控制的官方文档 **运营和基础设施：** * **HA 和复制**：流式复制、基于 GTID 的复制、故障转移自动化 * **迁移**：Liquibase、Flyway 版本控制和部署模式 * **备份/恢复** ：pgBackRest、Percona XtraBackup、时间点恢复 * **监控**：pg_stat_statements、性能模式、EXPLAIN 可视化工具（Dalibo、depesz） * **安全性**：OWASP SQL 注入防护、Postgres 强化、加密标准 * **分析数据库**：DuckDB 扩展、Parquet 规范、列式存储模式 ** ** 使用 [references/operational-patterns.md]() 和模板目录来获取详细的工作流程、迁移说明和准备运行的命令。

数据SQL优化

包含技能