.claude/skills/sql-slow-query-doctor/
├── SKILL.md          # 唯一必需：说明书
├── scripts/
│   └── sql_doctor.py # 可选：挂载的确定性脚本
└── references/
    └── rules.md      # 可选：深入规则，按需引用

mkdir -p .claude/skills/sql-slow-query-doctor/scripts
mkdir -p .claude/skills/sql-slow-query-doctor/references
cd .claude/skills/sql-slow-query-doctor

---
name: sql-slow-query-doctor
description: Analyzes a SQL SELECT/UPDATE/DELETE statement for performance anti-patterns (SELECT *, leading-wildcard LIKE, functions on indexed columns, missing WHERE, ORDER BY RAND(), large LIMIT) and returns prioritized, severity-ranked optimization suggestions with index recommendations. Use this skill when the user shares a slow or suspect SQL query, asks to optimize/tune a query, reports high database load, or wants an index plan.
allowed-tools: Bash, Read, Write
---

# SQL 慢查询诊断

## 你的工作流程（严格按序执行）

1. 获取待诊断的 SQL：
   - 用户直接贴了语句 → 用它；
   - 用户给了 `.sql` 文件路径 → 用 Read 读取；
   - 都没有 → 主动问用户要，不要编造。

2. 把 SQL 写入临时文件 `/tmp/_sqldoctor_input.sql`（脚本从文件读取，避免 shell 转义把引号吃掉）。

3. 运行确定性分析器：
   ```bash
   python3 scripts/sql_doctor.py /tmp/_sqldoctor_input.sql
   ```
   它输出 JSON，内含 findings 数组，每条带 severity / rule / problem / suggestion。

4. 把 JSON 翻译成用户能看懂的报告：
   - 按 severity（high→medium→low）排序；
   - 每条引用原文里出问题的片段（给出行号或子串）；
   - 末尾给一节"建议索引"，仅当命中索引相关规则时输出。

5. 绝不要修改用户的项目代码，除非用户明确要求改。输出仅限诊断报告。

## 边界
- 只做**静态**分析，不连数据库、不执行 SQL。
- DDL（CREATE/ALTER）不分析，提示用户本工具只看 DML。

#!/usr/bin/env python3
"""sql_doctor.py — SQL 慢查询静态分析，零依赖、不连库。"""
import re, sys, json

RULES = []  # 每条规则: (id, severity, regex, problem_tpl, suggestion_tpl)

def rule(rid, sev, pattern, problem, suggestion):
    RULES.append((rid, sev, re.compile(pattern, re.IGNORECASE), problem, suggestion))

# ---- 注册规则 ----
rule("SELECT_STAR", "medium",
     r"\bselect\s+\*\s+from", "SELECT * 拉取全部列，浪费 IO 且破坏覆盖索引",
     "只查需要的列；若走索引，把所需列都纳入索引以触发 index-only scan")

rule("LEADING_WILDCARD_LIKE", "high",
     r"like\s+'\s*%", "前导通配 LIKE '%xx' 无法走 B-Tree 索引，触发全表扫描",
     "改全文索引，或改右匹配 LIKE 'xx%'；业务允许时用前缀匹配")

rule("FUNC_ON_INDEX", "high",
     r"where\s+\w+\s*\(.*\)\s*=", "WHERE 列上套函数/运算会让索引失效",
     "把函数改写到常量侧，如 DATE(col)=x 改为 col >= x AND col < x+1")

rule("MISSING_WHERE", "high",
     r"^(select|update|delete)\b(?!.*\bwhere\b)", "无 WHERE 的 DML 会全表扫描/全表更新",
     "必须加 WHERE 限定范围；UPDATE/DELETE 前先 SELECT 验证影响行数")

rule("ORDER_BY_RAND", "high",
     r"order\s+by\s+rand\s*\(", "ORDER BY RAND() 对每行求随机值再排序，O(n log n) 且无法索引",
     "先算随机主键再 JOIN，或限制候选集后再随机")

rule("HUGE_LIMIT", "medium",
     r"limit\s+\d{6,}", "LIMIT 百万级会先物化再截断，深分页性能差",
     "用游标分页(keyset pagination)：WHERE id > last_id ORDER BY id LIMIT n")

rule("OR_BLOCKING_INDEX", "medium",
     r"\bor\b", "OR 连接的条件可能阻止单索引命中（需多列或 index merge）",
     "评估拆成 UNION ALL，或建立覆盖 OR 的复合索引")

rule("NESTED_IN_SUBQUERY", "medium",
     r"in\s*\(\s*select", "IN (SELECT ...) 子查询在某些优化器下退化为相关子查询",
     "改 JOIN 或 EXISTS；确保被驱动表有索引")

def analyze(sql):
    findings = []
    for rid, sev, pat, problem, suggestion in RULES:
        m = pat.search(sql)
        if m:
            findings.append({
                "id": rid, "severity": sev,
                "match": m.group(0).strip(),
                "problem": problem, "suggestion": suggestion,
            })
    order = {"high": 0, "medium": 1, "low": 2}
    findings.sort(key=lambda f: order[f["severity"]])
    return findings

if __name__ == "__main__":
    if len(sys.argv) > 1:
        with open(sys.argv[1], encoding="utf-8") as f:
            sql = f.read()
    else:
        sql = sys.stdin.read()
    print(json.dumps(
        {"sql_preview": sql.strip()[:120], "findings": analyze(sql)},
        ensure_ascii=False, indent=2))

/skill

> 帮我诊断这条：SELECT * FROM orders WHERE DATE(created_at) = '2026-07-01'

> 这条查询在线上很慢，帮我看看怎么优化：SELECT * FROM orders WHERE DATE(created_at) = '2026-07-01'

-- /tmp/bad_queries.sql
SELECT * FROM users;
UPDATE orders SET status=1;
SELECT id FROM t WHERE name LIKE '%abc' ORDER BY RAND() LIMIT 1000000;
SELECT * FROM a WHERE id IN (SELECT id FROM b WHERE x=1);

SELECT * FROM user_logs
WHERE DATE(created_at) = '2026-07-01'
  AND message LIKE '%error%'
ORDER BY RAND()
LIMIT 5000000;

flowchart TD
    U[用户贴慢查询 / 说'帮我优化'] --> M{Claude 判断相关性}
    M -->|description 命中| L[读入 SKILL.md 正文]
    M -->|不命中| X[不触发, 当普通对话处理]
    L --> W[把 SQL 写入临时文件]
    W --> R[Bash 调用 sql_doctor.py]
    R --> J[脚本输出 JSON: findings+severity]
    J --> F[Claude 翻译成排序报告+索引建议]
    F --> O[输出诊断, 不碰项目代码]
    subgraph 渐进披露
      FM[frontmatter: 常驻, 便宜] -.按需.-> BD[SKILL.md 正文]
      BD -.按需.-> SC[scripts/sql_doctor.py]
      BD -.按需.-> RF[references/rules.md]
    end