SQL 数据库助手

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
) ranked WHERE rn <= 3;

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM transactions;

SELECT curr.id, curr.seq_num, prev.seq_num AS prev_seq
FROM records curr
LEFT JOIN records prev ON prev.seq_num = curr.seq_num - 1
WHERE prev.id IS NULL AND curr.seq_num > 1;

INSERT INTO settings (key, value, updated_at)
VALUES ('theme', 'dark', NOW())
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, updated_at = EXCLUDED.updated_at;

INSERT INTO settings (key_name, value, updated_at)
VALUES ('theme', 'dark', NOW())
ON DUPLICATE KEY UPDATE value = VALUES(value), updated_at = VALUES(updated_at);

SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

SELECT tc.table_name, kcu.column_name,
  ccu.table_name AS foreign_table, ccu.column_name AS foreign_column
F

MySQL — 表大小

SQLite — 架构导出

SQL Server — 列及其类型

### 从架构生成文档

使用 scripts/schema_explorer.py 生成 Markdown 或 JSON 文档：

---

查询优化
EXPLAIN 分析工作流
1. 运行 EXPLAIN ANALYZE (PostgreSQL) 或 EXPLAIN FORMAT=JSON (MySQL)
2. 识别成本最高的节点 — 大表的全表扫描 (Seq Scan)、行估算值较高的嵌套循环 (Nested Loop)
3. 检查缺失的索引 — 过滤列上出现顺序扫描
4. 查找估算错误 — 计划行数与实际行数的分歧通常意味着统计信息已过期
5. 评估 JOIN 顺序 — 确保结果集最小的表驱动连接
索引建议清单
WHERE 子句中选择性高的列
JOIN 条件中的列（外键）
与 LIMIT 结合使用的 ORDER BY 列
匹配多列 WHERE 谓词的复合索引（最具有选择性的列在前）
针对带有常量过滤查询的部分索引（例如 WHERE status = 'active'）
覆盖索引，以避免读密集型查询进行表回查
查询重写模式
| 反模式 | 重写方案 |
|-------------|---------|
| SELECT * FROM orders | SELECT id, status, total FROM orders (明确指定列) |
| WHERE YEAR(created_at) = 2025 | WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01' (可利用索引/Sargable) |
| SELECT 中的相关子查询 | 使用 LEFT JOIN 配合聚合 |
| 包含 NULL 的 NOT IN (SELECT ...) | NOT EXISTS (SELECT 1 ...) |
| 不需要去重时使用 UNION | UNION ALL |
| LIKE '%search%' | 全文检索索引 (GIN/FULLTEXT) |
| ORDER BY RAND() | 应用端随机采样或使用 TABLESAMPLE |
N+1 问题检测
症状：
应用程序循环中，每行父记录执行一次查询

ORM 在循环内部延迟加载相关实体

查询日志显示数百个模式相同但 ID 不同的 SELECT 语句

解决方案：
使用预加载（Prisma 中的 include，SQLAlchemy 中的 joinedload）

使用 WHERE id IN (...) 进行批量查询

为 GraphQL 解析器使用 DataLoader 模式

静态分析工具

> 详见 references/optimization_guide.md 以了解 EXPLAIN 执行计划阅读、索引类型和连接池。
---
迁移生成
零停机 (Zero-Dow)
运行时迁移模式
添加列（安全）

重命名列（扩容-收缩模式）

添加 NOT NULL 列（安全序列）

创建索引（非阻塞，PostgreSQL）

### 数据回填策略

分批更新 — 每批处理 1000-10000 行，以避免锁竞争
后台任务 — 异步执行回填并跟踪进度
双写 — 在过渡期间同时写入旧列和新列
验证查询 — 每批次后验证行数和数据完整性
回滚策略
每次迁移必须配备可逆的 down 脚本。对于不可逆的变更：
1. 执行前备份 — 对受影响的表执行 pg_dump
2. 特性开关 (Feature flags) — 应用可在新旧 Schema 读取之间切换
3. 影子表 — 在迁移窗口期间保留原表的副本
迁移生成工具

---
多数据库支持
方言差异
| 特性 | PostgreSQL | MySQL | SQLite | SQL Server |
|---------|-----------|-------|--------|------------|
| UPSERT | ON CONFLICT DO UPDATE | ON DUPLICATE KEY UPDATE | ON CONFLICT DO UPDATE | MERGE |
| 布尔值 | 原生 BOOLEAN | TINYINT(1) | INTEGER | BIT |
| 自增 | SERIAL / GENERATED | AUTO_INCREMENT | INTEGER PRIMARY KEY | IDENTITY |
| JSON | JSONB (可索引) | JSON | 文本 (扩展) | NVARCHAR(MAX) |
| 数组 | 原生 ARRAY | 不支持 | 不支持 | 不支持 |
| CTE (递归) | 完全支持 | 8.0+ | 3.8.3+ | 完全支持 |
| 窗口函数 | 完全支持 | 8.0+ | 3.25.0+ | 完全支持 |
| 全文检索 | tsvector + GIN | FULLTEXT 索引 | FTS5 扩展 | 全文目录 |
| LIMIT/OFFSET | LIMIT n OFFSET m | LIMIT n OFFSET m | LIMIT n OFFSET m | OFFSET m ROWS FETCH NEXT n ROWS ONLY |
兼容性建议
始终使用参数化查询 — 防止所有方言中的 SQL 注入
在共享代码中避免使用特定方言函数 — 将其封装在适配层中
在目标引擎上测试迁移 — 不同引擎的 information_schema 存在差异
使用 ISO 日期格式 — 'YYYY-MM-DD' 在所有环境下通用
对标识符加引号 — 使用双引号（SQL 标准）或反引号（MySQL）
---
ORM 模式
Prisma
Schema 定义

At DateTime @default(now())
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User   @relation(fields: [authorId], references: [id])
  authorId Int
}