sql-development

majiayu000 的头像作者 majiayu000

设计高效的数据库模式,使用适当的索引编写优化的查询,并按照最佳实践管理数据操作

📖 中文导读:设计高效的数据库模式,使用适当的索引编写优化的查询,并按照最佳实践管理数据操作 以下正文为该技能的原始 SKILL.md 内容(英文),上方安装命令可直接使用。

name: "SQL Development" description: "Design efficient database schemas, write optimized queries with proper indexes, and manage data operations following best practices" category: "database" required_tools: ["Read", "Write", "Edit", "Bash"]

SQL Development

Purpose

Design efficient database schemas, write optimized SQL queries, and manage data operations following database best practices and performance patterns.

When to Use

  • Designing database schemas
  • Writing data queries
  • Optimizing slow queries
  • Creating database migrations
  • Managing data relationships

Key Capabilities

  1. Schema Design - Create normalized, efficient table structures
  2. Query Optimization - Write performant SELECT, INSERT, UPDATE, DELETE
  3. Index Strategy - Design indexes for query performance

Approach

  1. Design schema following normalization principles
  2. Define primary keys, foreign keys, and constraints
  3. Write queries using proper JOINs and WHERE clauses
  4. Create indexes for frequently queried columns
  5. Use EXPLAIN to analyze query performance
  6. Test with realistic data volumes

Example

Context: Task management database

-- Schema Design
CREATE TABLE tasks (
    id VARCHAR(50) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    assigned_agent VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL,
    priority VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    INDEX idx_status (status),
    INDEX idx_agent_status (assigned_agent, status),
    INDEX idx_created (created_at)
);

-- Optimized Query
SELECT t.id, t.title, t.status, a.name as agent_name
FROM tasks t
JOIN agents a ON t.assigned_agent = a.agent_file
WHERE t.status IN ('pending', 'active')
  AND t.priority = 'high'
ORDER BY t.created_at DESC
LIMIT 10;

Best Practices

  • ✅ Use indexes on frequently queried columns
  • ✅ Avoid SELECT * - specify needed columns
  • ✅ Use prepared statements to prevent SQL injection
  • ✅ Normalize data to reduce redundancy
  • ❌ Avoid: N+1 query problems
  • ❌ Avoid: Missing WHERE clause on large tables