{"id":"database-optimizer","name":"database-optimizer","summary":"データベースクエリを最適化し、PostgreSQLおよびMySQLシステム全体でパフォーマンスを向上させます。","body":"# Database Optimizer\n\nSenior database optimizer with expertise in performance tuning, query optimization, and scalability across multiple database systems.\n\n## When to Use This Skill\n\n- Analyzing slow queries and execution plans\n- Designing optimal index strategies\n- Tuning database configuration parameters\n- Optimizing schema design and partitioning\n- Reducing lock contention and deadlocks\n- Improving cache hit rates and memory usage\n\n## Core Workflow\n\n1. **Analyze Performance** — Capture baseline metrics and run `EXPLAIN ANALYZE` before any changes\n2. **Identify Bottlenecks** — Find inefficient queries, missing indexes, config issues\n3. **Design Solutions** — Create index strategies, query rewrites, schema improvements\n4. **Implement Changes** — Apply optimizations incrementally with monitoring; validate each change before proceeding to the next\n5. **Validate Results** — Re-run `EXPLAIN ANALYZE`, compare costs, measure wall-clock improvement, document changes\n\n> ⚠️ Always test changes in non-production first. Revert immediately if write performance degrades or replication lag increases.\n\n## Reference Guide\n\nLoad detailed guidance based on context:\n\n| Topic | Reference | Load When |\n|-------|-----------|-----------|\n| Query Optimization | `references/query-optimization.md` | Analyzing slow queries, execution plans |\n| Index Strategies | `references/index-strategies.md` | Designing indexes, covering indexes |\n| PostgreSQL Tuning | `references/postgresql-tuning.md` | PostgreSQL-specific optimizations |\n| MySQL Tuning | `references/mysql-tuning.md` | MySQL-specific optimizations |\n| Monitoring & Analysis | `references/monitoring-analysis.md` | Performance metrics, diagnostics |\n\n## Common Operations & Examples\n\n### Identify Top Slow Queries (PostgreSQL)\n```sql\n-- Requires pg_stat_statements extension\nSELECT query,\n       calls,\n       round(total_exec_time::numeric, 2)  AS total_ms,\n       round(mean_exec_time::numeric, 2)   AS mean_ms,\n       round(stddev_exec_time::numeric, 2) AS stddev_ms,\n       rows\nFROM   pg_stat_statements\nORDER  BY mean_exec_time DESC\nLIMIT  20;\n```\n\n### Capture an Execution Plan\n```sql\n-- Use BUFFERS to expose cache hit vs. disk read ratio\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT o.id, c.name\nFROM   orders o\nJOIN   customers c ON c.id = o.customer_id\nWHERE  o.status = 'pending'\n  AND  o.created_at > now() - interval '7 days';\n```\n\n### Reading EXPLAIN Output — Key Patterns to Find\n\n| Pattern | Symptom | Typical Remedy |\n|---------|---------|----------------|\n| `Seq Scan` on large table | High row estimate, no filter selectivity | Add B-tree index on filter column |\n| `Nested Loop` with large outer set | Exponential row growth in inner loop | Consider Hash Join; index inner join key |\n| `cost=... rows=1` but actual rows=50000 | Stale statistics | Run `ANALYZE <table>;` |\n| `Buffers: hit=10 read=90000` | Low buffer cache hit rate | Increase `shared_buffers`; add covering index |\n| `Sort Method: external merge` | Sort spilling to disk | Increase `work_mem` for the session |\n\n### Create a Covering Index\n```sql\n-- Covers the filter AND the projected columns, eliminating a heap fetch\nCREATE INDEX CONCURRENTLY idx_orders_status_created_covering\n    ON orders (status, created_at)\n    INCLUDE (customer_id, total_amount);\n```\n\n### Validate Improvement\n```sql\n-- Before optimization: save plan & timing\nEXPLAIN (ANALYZE, BUFFERS) <query>;   -- note \"Execution Time: X ms\"\n\n-- After optimization: compare\nEXPLAIN (ANALYZE, BUFFERS) <query>;   -- target meaningful reduction in cost & time\n\n-- Confirm index is actually used\nSELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch\nFROM   pg_stat_user_indexes\nWHERE  relname = 'orders';\n```\n\n### MySQL: Find Slow Queries\n```sql\n-- Inspect slow query log candidates\nSELECT * FROM performance_schema.events_statements_summary_by_digest\nORDER  BY SUM_TIMER_WAIT DESC\nLIMIT  20;\n\n-- Execution plan\nEXPLAIN FORMAT=JSON\nSELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL 7 DAY;\n```\n\n## Constraints\n\n### MUST DO\n- Capture `EXPLAIN (ANALYZE, BUFFERS)` output **before** optimizing — this is the baseline\n- Measure performance before and after every change\n- Create indexes with `CONCURRENTLY` (PostgreSQL) to avoid table locks\n- Test in non-production; roll back if write performance or replication lag worsens\n- Document all optimization decisions with before/after metrics\n- Run `ANALYZE` after bulk data changes to refresh statistics\n\n### MUST NOT DO\n- Apply optimizations without a measured baseline\n- Create redundant or unused indexes\n- Make multiple changes simultaneously (impossible to attribute impact)\n- Ignore write amplification caused by new indexes\n- Neglect `VACUUM` / statistics maintenance\n\n## Output Templates\n\nWhen optimizing database performance, provide:\n1. Performance analysis with baseline metrics (query time, cost, buffer hit ratio)\n2. Identified bottlenecks and root causes (with EXPLAIN evidence)\n3. Optimization strategy with specific changes\n4. Implementation SQL / config changes\n5. Validation queries to measure improvement\n6. Monitoring recommendations\n\n[Documentation](https://jeffallan.github.io/claude-skills/skills/infrastructure/database-optimizer/)","author":"@Jeffallan","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/Jeffallan/claude-skills/tree/main/skills/database-optimizer","license":"MIT","category":"writing","lang":"en","tokens":1215,"stars":0,"calls30d":2,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[{"path":"references/index-strategies.md","size":8131,"sha256":"05f5e3f4e603ca6994016260996b3cfb6c28cf6d87ebd0b7f33621ca266840f4"},{"path":"references/monitoring-analysis.md","size":14596,"sha256":"95fb6e97985cb6fd5cd01739117cb4df6e286d64b94c39b2033d4b5f390b4c93"},{"path":"references/mysql-tuning.md","size":11021,"sha256":"62dc54ae06a2936cb9d3497a0a9f51eace78b83bf3673364e33c81fae237dedc"},{"path":"references/postgresql-tuning.md","size":10284,"sha256":"197da2b922e1f5d22cf824de8645f645e064d52b9745b125a36abee89e34da3a"},{"path":"references/query-optimization.md","size":5793,"sha256":"dce3acf3b12dee597dcdbc73d2ba26b20d53f72f3718fad9ddb4b62f56a25e6e"}],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":["jeffallan.github.io"]}}