data-sql-optimization by vasilyu1983/ai-agents-public
npx skills add https://github.com/vasilyu1983/ai-agents-public --skill data-sql-optimization此技能为事务型 (OLTP) SQL 优化提供可操作的检查清单、模式和模板:测量优先的故障排查、EXPLAIN/执行计划解读、平衡索引(避免过度索引)、性能监控、模式演进、迁移、备份/恢复、高可用性和安全性。
支持的平台: PostgreSQL, MySQL, SQL Server, Oracle, SQLite
对于 OLAP/分析: 参见 data-lake-platform (ClickHouse, DuckDB, Doris, StarRocks)
| 任务 | 工具/框架 | 命令 | 使用时机 |
|---|---|---|---|
| 查询性能分析 | EXPLAIN ANALYZE | EXPLAIN (ANALYZE, BUFFERS) SELECT ... (PG) / EXPLAIN ANALYZE SELECT ... (MySQL) | 诊断慢查询,识别缺失索引 |
| 查找慢查询 | pg_stat_statements / 慢查询日志 |
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
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 | 高可用性,读扩展 |
| 安全调优循环 | 测量 -> 解释 -> 变更 -> 验证 | 使用调优工作表模板 | 在不造成退步的情况下降低延迟/成本 |
Query performance issue?
├─ Identify slow queries first?
│ ├─ PostgreSQL -> pg_stat_statements (top queries by total_exec_time)
│ └─ MySQL -> Performance Schema / slow query log
│
├─ Analyze execution plan?
│ ├─ PostgreSQL -> EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
│ ├─ MySQL -> EXPLAIN FORMAT=JSON or EXPLAIN ANALYZE
│ └─ SQL Server -> SET STATISTICS IO ON; SET STATISTICS TIME ON;
│
├─ Need indexing strategy?
│ ├─ PostgreSQL -> B-tree (default), GIN (JSONB), GiST (spatial), partial indexes
│ ├─ MySQL -> BTREE (default), FULLTEXT (text search), SPATIAL
│ └─ Check: Table >10k rows AND selectivity <10% AND 10x+ speedup verified
│
├─ Schema changes needed?
│ ├─ New database -> template-schema-design.md
│ ├─ Modify schema -> template-migration.md (Flyway/Liquibase)
│ └─ Large tables (MySQL) -> gh-ost / pt-online-schema-change (avoid locks)
│
├─ High availability setup?
│ ├─ PostgreSQL -> Streaming replication (template-replication-ha.md)
│ └─ MySQL -> GTID-based replication (template-replication-ha.md)
│
├─ Backup/disaster recovery?
│ └─ template-backup-restore.md (pg_dump, mysqldump, PITR)
│
└─ Analytics on large datasets (OLAP)?
└─ See data-lake-platform (ClickHouse, DuckDB, Doris, StarRocks)
当用户询问以下内容时,Codex 应调用此技能:
在以下文件中查找详细的操作模式和快速参考:
每个文件包含:
模板按数据库技术组织,以确保精确性和清晰度:
基础设施与运维:
应用集成:
质量与安全:
数据工程:
资源
模板
数据
| 反模式 | 问题 | 修复方案 |
|---|---|---|
| **SELECT *** | 读取不必要的列 | 显式指定列列表 |
| N+1 查询 | 倍增的往返次数 | 使用 JOIN 或批量获取 |
| 缺失 WHERE | 全表扫描 | 添加谓词条件 |
| 对索引列使用函数 | 无法使用索引 | 将函数移到等号右侧 |
| 隐式类型转换 | 绕过索引 | 显式匹配类型 |
| LIKE '%prefix' | 前导通配符导致扫描 | 使用全文搜索 |
| 无限制的结果集 | 内存爆炸 | 添加 LIMIT/分页 |
| OR 条件 | 索引可能不被使用 | 使用 UNION 或重写 |
参见 references/sql-antipatterns.md 获取详细修复方案。
Is your query for...?
├─ Point lookups (by ID/key)?
│ └─ OLTP database (this skill)
│ - Ensure proper indexes
│ - Use connection pooling
│ - Optimize for low latency
│
├─ Aggregations over recent data (dashboard)?
│ └─ OLTP database (this skill)
│ - Consider materialized views
│ - Index common filter columns
│ - Watch for lock contention
│
├─ Full table scans or historical analysis?
│ └─ OLAP database (data-lake-platform)
│ - ClickHouse, DuckDB, Doris
│ - Columnar storage
│ - Partitioning by date
│
└─ Mixed workload (both)?
└─ Separate OLTP and OLAP
- OLTP for transactions
- Replicate to OLAP for analytics
- Avoid running analytics on primary
注意:AI 工具提供辅助,但需要人工验证其正确性。
对于 OLAP 数据库和数据湖基础设施,请参见 data-lake-platform:
此技能专注于事务型数据库优化 (PostgreSQL, MySQL, SQL Server, Oracle, SQLite)。对于分析型工作负载,请使用 data-lake-platform。
此技能专注于单个数据库内的查询优化。对于相关的工作流:
SQL 转换与分析工程: -> ai-ml-data-science 技能
数据摄取(加载到数据仓库): -> ai-mlops 技能
数据湖基础设施: -> data-lake-platform 技能
使用场景决策:
参见 data/sources.json 获取 62+ 个精选资源,包括:
核心文档:
现代优化(当前):
运维与基础设施:
使用 references/operational-patterns.md 和模板目录获取详细的工作流、迁移说明和可直接运行的命令。
This skill provides actionable checklists, patterns, and templates for transactional (OLTP) SQL optimization : measurement-first triage, EXPLAIN/plan interpretation, balanced indexing (avoiding over-indexing), performance monitoring, schema evolution, migrations, backup/recovery, high availability, and security.
Supported Platforms: PostgreSQL, MySQL, SQL Server, Oracle, SQLite
For OLAP/Analytics: See data-lake-platform (ClickHouse, DuckDB, Doris, StarRocks)
| Task | Tool/Framework | Command | When to Use |
|---|---|---|---|
| Query Performance Analysis | EXPLAIN ANALYZE | EXPLAIN (ANALYZE, BUFFERS) SELECT ... (PG) / EXPLAIN ANALYZE SELECT ... (MySQL) | Diagnose slow queries, identify missing indexes |
| Find Slow Queries | pg_stat_statements / slow query log | SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; | Identify performance bottlenecks in production |
| Index Analysis | pg_stat_user_indexes / SHOW INDEX | SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0; | Find unused indexes, validate index coverage |
| Schema Migration | Flyway / Liquibase | flyway migrate / liquibase update | Version-controlled database changes |
| Backup & Recovery | pg_dump / mysqldump | pg_dump -Fc dbname > backup.dump | Point-in-time recovery, disaster recovery |
| Replication Setup | Streaming / GTID | Configure postgresql.conf / my.cnf | High availability, read scaling |
| Safe Tuning Loop | Measure -> Explain -> Change -> Verify | Use tuning worksheet template | Reduce latency/cost without regressions |
Query performance issue?
├─ Identify slow queries first?
│ ├─ PostgreSQL -> pg_stat_statements (top queries by total_exec_time)
│ └─ MySQL -> Performance Schema / slow query log
│
├─ Analyze execution plan?
│ ├─ PostgreSQL -> EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
│ ├─ MySQL -> EXPLAIN FORMAT=JSON or EXPLAIN ANALYZE
│ └─ SQL Server -> SET STATISTICS IO ON; SET STATISTICS TIME ON;
│
├─ Need indexing strategy?
│ ├─ PostgreSQL -> B-tree (default), GIN (JSONB), GiST (spatial), partial indexes
│ ├─ MySQL -> BTREE (default), FULLTEXT (text search), SPATIAL
│ └─ Check: Table >10k rows AND selectivity <10% AND 10x+ speedup verified
│
├─ Schema changes needed?
│ ├─ New database -> template-schema-design.md
│ ├─ Modify schema -> template-migration.md (Flyway/Liquibase)
│ └─ Large tables (MySQL) -> gh-ost / pt-online-schema-change (avoid locks)
│
├─ High availability setup?
│ ├─ PostgreSQL -> Streaming replication (template-replication-ha.md)
│ └─ MySQL -> GTID-based replication (template-replication-ha.md)
│
├─ Backup/disaster recovery?
│ └─ template-backup-restore.md (pg_dump, mysqldump, PITR)
│
└─ Analytics on large datasets (OLAP)?
└─ See data-lake-platform (ClickHouse, DuckDB, Doris, StarRocks)
Codex should invoke this skill when users ask for:
Find detailed operational patterns and quick references in:
Each file includes:
Templates are organized by database technology for precision and clarity:
Infrastructure & Operations:
Application Integration:
Quality & Security:
Data Engineering:
Resources
Templates
Data
See references/operational-patterns.md for:
| Anti-Pattern | Problem | Fix |
|---|---|---|
| **SELECT *** | Reads unnecessary columns | Explicit column list |
| N+1 queries | Multiplied round trips | JOIN or batch fetch |
| Missing WHERE | Full table scan | Add predicates |
| Function on indexed column | Can't use index | Move function to RHS |
| Implicit type conversion | Index bypass | Match types explicitly |
| LIKE '%prefix' | Leading wildcard = scan | Full-text search |
| Unbounded result set | Memory explosion | Add LIMIT/pagination |
| OR conditions | Index may not be used |
See references/sql-antipatterns.md for detailed fixes.
Is your query for...?
├─ Point lookups (by ID/key)?
│ └─ OLTP database (this skill)
│ - Ensure proper indexes
│ - Use connection pooling
│ - Optimize for low latency
│
├─ Aggregations over recent data (dashboard)?
│ └─ OLTP database (this skill)
│ - Consider materialized views
│ - Index common filter columns
│ - Watch for lock contention
│
├─ Full table scans or historical analysis?
│ └─ OLAP database (data-lake-platform)
│ - ClickHouse, DuckDB, Doris
│ - Columnar storage
│ - Partitioning by date
│
└─ Mixed workload (both)?
└─ Separate OLTP and OLAP
- OLTP for transactions
- Replicate to OLAP for analytics
- Avoid running analytics on primary
Note : AI tools assist but require human validation of correctness.
For OLAP databases and data lake infrastructure, see data-lake-platform :
This skill focuses on transactional database optimization (PostgreSQL, MySQL, SQL Server, Oracle, SQLite). Use data-lake-platform for analytical workloads.
This skill focuses on query optimization within a single database. For related workflows:
SQL Transformation & Analytics Engineering: -> ai-ml-data-science skill
Data Ingestion (Loading into Warehouses): -> ai-mlops skill
Data Lake Infrastructure: -> data-lake-platform skill
Use Case Decision:
See data/sources.json for 62+ curated resources including:
Core Documentation:
Modern Optimization (Current):
Operations & Infrastructure:
Use references/operational-patterns.md and the templates directory for detailed workflows, migration notes, and ready-to-run commands.
Weekly Installs
85
Repository
GitHub Stars
47
First Seen
Jan 23, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykWarn
Installed on
gemini-cli72
cursor72
opencode69
codex66
claude-code60
github-copilot60
Azure 升级评估与自动化工具 - 轻松迁移 Functions 计划、托管层级和 SKU
104,900 周安装
| UNION or rewrite |