SQL

用户偏好和记忆数据存储在 ~/Clawic/data/sql/ 目录下（首次使用请参考 setup.md，文件格式详见 memory-template.md）。若旧数据位于 ~/sql/ 或 ~/clawic/sql/，请将其移至 ~/Clawic/data/sql/。

何时使用

• 编写、审查或优化 SQL：包括查询、JOIN、CTE、窗口函数、UPSERT 等
• 设计表结构、主键、字段类型、索引与约束，或对现有模型进行范式化处理
• 诊断慢查询、死锁、锁超时、汇总值错误或结果重复等问题
• 规划迁移与 DDL 操作，确保不影响线上数据库可用性
• 数据库运维：备份、恢复、监控、连接池管理、复制延迟处理、分区策略
• 数据导入导出：CSV 加载、数据转储、跨数据库引擎迁移
• 不适用于 PostgreSQL 服务器内部调优（如 vacuum 调优、work_mem 配置、xid 周期问题）——该场景由 pg 处理；也不适用于框架内 ORM 层的模型设计——该场景由 prisma 处理

快速参考

| 其他情况 | 将问题缩小到最小可复现表，然后：

• 模式相关 → modeling.md / schemas.md
• 查询模式相关 → patterns.md
• 性能问题 → performance.md
• 运维问题 → operations.md |

核心规则

1. 参数化值，白名单标识符。使用占位符（?、$1）防止值注入攻击，但表名/列名无法绑定——动态名称必须通过硬编码白名单校验，禁止拼接用户输入。完整攻击面涵盖 LIKE 和 ORDER BY 注入（→ 参见 security.md）。
2. 默认使用 BIGINT（或 UUIDv7）作为主键。INT 类型在 2,147,483,647 时溢出——以每秒 100 次插入计算，约 248 天后发生，修复需停机。随机 UUIDv4 会破坏 B 树插入局部性；推荐使用 UUIDv7/ULID 保持插入局部性（→ 参见 modeling.md）。
3. 索引顺序遵循查询模式：等值列在前，范围/排序列在后。(user_id, created_at) 可支持 WHERE user_id = ? AND created_at > ? 以及仅 WHERE user_id = ? 的查询，但不能支持 WHERE created_at = ? 单独使用。当筛选条件命中超过 5%-10% 行时，全表扫描是优化器合理判断，非故障。
4. 手动为每个外键列创建索引。MySQL/InnoDB 自动创建索引；而 PostgreSQL、SQLite、SQL Server 不会。缺少索引会导致每次外键关联和父表 DELETE（尤其带 ON DELETE CASCADE 时）都需扫描整张子表——这是多数系统中最慢的删除操作。
5. 事务应短且不依赖外部世界。禁止在 BEGIN...COMMIT 中发起 HTTP 请求或等待用户输入：长时间打开的事务会持有锁，在 PostgreSQL 中还会阻塞 vacuum，导致表膨胀。超过 1 分钟的事务（监控阈值）将被调查。
6. NULL 是三值逻辑。NOT IN (子查询) 在子查询返回任意一个 NULL 时返回空集——应改用 NOT EXISTS。x = NULL 永远不为真——应使用 IS NULL。COUNT(col) 忽略 NULL 值；COUNT(*) 统计行数。聚合在无数据时返回 NULL 而非 0——如图表或不变量需要数值，应使用 COALESCE 包裹。
7. 选择可减少未来迁移的类型。金额字段使用 NUMERIC/DECIMAL（浮点数会丢失精度）；时间戳使用 TIMESTAMPTZ 存储为 UTC（→ 参见 datetime.md）；字符串使用 TEXT（PostgreSQL 与 SQLite），避免 varchar(255) 这类无意义的限制；MySQL 字符集使用 utf8mb4（MySQL 的 utf8 仅支持 3 字节，无法存储表情符号）。
8. 迁移应优先采用增量方式。重命名、重类型、删除操作应在多个部署周期中并行存在（expand-migrate-contract，→ 参见 operations.md）。单次部署的列重命名会中断所有仍在运行旧代码的实例。
9. 先排序，再优化。通过 pg_stat_statements 按 total_exec_time 排序（或使用 pt-query-digest 解析 MySQL 慢查询日志），找出总成本最高的查询——通常不是用户抱怨的那个。总成本 = 平均延迟 × 调用次数：一个每分钟调用 10,000 次的 5ms 查询（每分钟 50 秒）远超每小时一次的 2 秒报表。未排序就优化等于盲目猜测。

阅读 EXPLAIN

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 5;  -- PostgreSQL
EXPLAIN ANALYZE SELECT ...;                                         -- MySQL >=8.0.18
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 5;          -- SQLite
SET STATISTICS PROFILE ON;                                          -- SQL Server（或图形化执行计划）

重点查看实际执行行为，而非仅看计划。普通 EXPLAIN 仅显示估算值，而估算值正是最容易失真的部分。

• Seq Scan / type: ALL 在大表上配合选择性高的过滤条件 → 缺失或无法使用的索引（→ 参见《陷阱》中导致索引失效的原因）
• Rows Removed by Filter 数值高 → 索引找到了候选行，但过滤条件仍需大量计算；应扩展索引以覆盖过滤条件
• 估算行数与实际行数相差 超过 10 倍 → 统计信息陈旧，执行 ANALYZE tablename; 更新；若仍不准，说明优化器假设列间独立，需声明相关性（PostgreSQL ≥10 使用 CREATE STATISTICS，MySQL 8 支持直方图）
• Buffers: read 大于 hit → 数据来自磁盘；在缓存预热后再次检查，避免误判
• 嵌套循环遍历数千个外层行 → 通常是上述 10 倍以上估算误差引发的错误连接选择
• 节点逐个解读、连接算法分析及对应优化措施：参见 performance.md

索引策略

-- 复合索引：等值列在前，范围/排序列在后（规则 3）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 覆盖索引：索引扫描无需回表（PostgreSQL ≥11，SQL Server INCLUDE）
CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (total);

-- 部分索引：仅索引查询中实际使用的行（PostgreSQL、SQLite、SQL Server）
CREATE INDEX idx_orders_pending ON orders(user_id) WHERE status = 'pending';

-- 表达式索引：使函数可被索引使用（MySQL ≥8.0.13 支持函数索引）
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

• 在低基数列（如仅有 5 个值的 status）上建立普通索引效果有限；对实际查询的稀有值建立部分索引更有效。
• (a, b) 上已有索引时，WHERE a = ? 已被覆盖，无需额外创建 (a) 单独索引——会增加写入开销且无收益。添加前检查是否存在冗余前缀。
• PostgreSQL 在非 C 语言环境忽略 B 树索引对 LIKE 'term%' 的支持——需添加 text_pattern_ops 操作类实现前缀搜索。
• 索引仅扫描仍可能访问堆页（当页未标记为全部可见时）；若 EXPLAIN 显示 Heap Fetches 高，需执行 VACUUM（→ 参见 operations.md）。
• 每个索引都会增加写入开销并占用磁盘空间：定期清理未使用的索引（如 pg_stat_user_indexes 中 idx_scan = 0），但需在完整业务周期（如月结报告）后执行，避免误删临时使用索引。

可移植性

日期函数、引号规则、NULL 排序顺序、排序规则等差异详见 dialects.md。

输出校验

在输出 SQL 前，请确认：

• 所有值均为占位符，所有动态标识符均来自白名单？
• UPDATE/DELETE 是否包含 WHERE 子句？或全表影响是明确意图？
• 破坏性 DML 是否已预先以等价的 SELECT 预览？
• 1:N JOIN 是否在聚合前完成？是否用 DISTINCT 掩盖了潜在问题？
• LIMIT/TOP 是否有确定的 ORDER BY，且包含唯一断言？
• 新建表：主键类型符合规则 2，时间戳带时区，唯一性作用于正确列，每个外键列均已索引（规则 4）？
• 对线上表执行 DDL：是否设置了 lock_timeout？变更是否为可扩展模式？
• 使用的每个语法元素在目标引擎中均存在？（→ 参见《可移植性》与 dialects.md）

配置

用户自定义变量。默认值在用户未指定前生效；配置文件保存于 ~/Clawic/data/sql/config.yaml。

可定制维度（偏好领域）

• 工具链：客户端（psql、mysql、sqlite3、sqlcmd、GUI）、迁移工具、SQL 格式化/校验工具、本地数据库容器 vs 安装服务 —— 影响 operations.md 与 testing.md 示例
• 命名规范：索引与约束命名、关键字大小写、CTE 与子查询偏好、DDL 注释风格 —— 影响所有生成语句
• 平台环境：托管服务（RDS、Aurora、Cloud SQL、Neon、PlanetScale） vs 自建，可用扩展、排序规则与区域设置 —— 影响可假设的功能
• 安全策略：破坏性 DDL/DML 的确认强度、生产凭证是否直接使用、只读会话默认开启 —— 影响 operations.md 与 destructive_guard 机制
• 输出格式：完整可运行脚本 vs 代码片段、每个 up 操作是否伴随 down/回滚迁移、是否包含内联注释 —— 影响交付物形态
• 工作流程：以 schema 优先还是 query 优先设计、迁移上线前是否需评审 —— 影响 modeling.md 与 operations.md 顺序
• 集成系统：使用的 ORM、目标仓库/BI 工具、监控栈 —— 影响 orm.md 与 analytics.md 建议
• 约束限制：禁用功能（无触发器、无存储过程、无厂商扩展）、合规要求（保留窗口、GDPR 数据擦除）—— 影响 schemas.md 与 security.md 选择
• 运营阈值：团队标准的操作预算：lock_timeout、batch_size、死锁/序列化失败重试上限、连接池大小规则、告警阈值（连接饱和度、磁盘剩余、事务年龄、备份时效）—— 影响 transactions.md 重试逻辑与 operations.md 告警表
• 执行频率：定时任务周期：恢复演练、滚动汇总与物化视图刷新、尾随重新计算窗口、数据质量检查、自动分区创建、备份间隔 —— 影响 operations.md、analytics.md、schemas.md、testing.md 的调度安排

陷阱