sql-best-practices

SQL 最佳实践

核心原则

• 编写清晰、易读且格式一致的 SQL，并使用有意义的别名
• 通过适当的索引和优化优先保障查询性能
• 落实安全最佳实践，防止 SQL 注入
• 恰当使用事务以确保数据完整性
• 使用行内注释说明复杂查询

查询编写规范

格式和风格

• SQL 关键字使用大写（SELECT、FROM、WHERE、JOIN）
• 将每个主要子句单独放在一行，以提高可读性
• 使用有意义的表别名（例如，customers AS c，而不是 customers AS x）
• 以一致的方式缩进子查询和嵌套条件
• 对齐列清单和条件，以提升视觉清晰度

SELECT
    c.customer_id,
    c.customer_name,
    o.order_date,
    o.total_amount
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
    AND o.status = 'completed'
ORDER BY o.order_date DESC;

列选择

• 避免在生产代码中使用 SELECT *；明确列出所需列
• 使用列别名来明确输出：SELECT first_name AS "First Name"
• 考虑 SELECT 中列的顺序，以便进行逻辑分组

筛选和条件

• 在 WHERE 子句中优先放置限制性最强的条件
• 使用适当的运算符：优先使用 IN，而不是多个 OR 条件
• 在子查询中检查是否存在时，使用 EXISTS 替代 IN
• 尽可能避免在 WHERE 子句中对索引列使用函数
• 使用参数化查询防止 SQL 注入

-- Preferred: Use EXISTS for existence checks
SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = c.customer_id
    AND o.order_date > '2024-01-01'
);

-- Avoid: Function on indexed column
WHERE YEAR(order_date) = 2024

-- Preferred: Range comparison
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

JOIN 最佳实践

• 始终使用显式 JOIN 语法，而不是在 WHERE 中使用隐式连接
• 明确指定连接类型（INNER、LEFT、RIGHT、FULL OUTER）
• 尽可能按从大到小的顺序连接表
• 根据数据需求使用适当的连接类型
• 谨慎使用 CROSS JOIN；确保使用意图明确

-- Explicit join (preferred)
SELECT c.name, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id;

-- Avoid implicit join
SELECT c.name, o.order_id
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;

性能优化

索引指南

• 在 WHERE、JOIN 和 ORDER BY 子句中使用的列上创建索引
• 对涉及多列的查询，考虑使用复合索引
• 避免索引过多；每个索引都会增加写入开销
• 定期分析和维护索引
• 对频繁执行的查询使用覆盖索引

查询优化

• 使用 EXPLAIN/EXPLAIN ANALYZE 了解查询执行计划
• 无需完整结果时，使用 TOP/LIMIT 限制结果集
• 对大型结果集使用分页
• 尽可能避免相关子查询；改用 JOIN
• 对频繁执行的查询考虑使用查询缓存

-- Pagination example
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;

聚合最佳实践

• 尽可能在分组前筛选（WHERE 与 HAVING）
• 使用适当的聚合函数（COUNT、SUM、AVG 等）
• 对运行总计和排名，考虑使用窗口函数

-- Efficient: Filter before aggregation
SELECT category_id, COUNT(*) AS product_count
FROM products
WHERE active = true
GROUP BY category_id
HAVING COUNT(*) > 10;

事务管理

• 尽可能缩短事务
• 根据使用场景选择适当的隔离级别
• 始终通过 ROLLBACK 进行错误处理
• 避免在事务开启期间与用户交互
• 对复杂的多步骤操作使用保存点

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

IF @@ERROR <> 0
    ROLLBACK TRANSACTION;
ELSE
    COMMIT TRANSACTION;

安全最佳实践

• 始终使用参数化查询或预处理语句
• 切勿将用户输入直接拼接到 SQL 字符串中
• 数据库用户应遵循最小权限原则
• 审计并记录敏感数据访问
• 对静态和传输中的敏感数据进行加密

-- Use parameterized queries (pseudo-code)
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
EXECUTE stmt USING @username;

数据修改最佳实践

INSERT 操作

• 始终明确指定列名
• 尽可能对多行数据使用批量插入
• 对插入或更新场景，考虑使用 MERGE/UPSERT

INSERT INTO customers (customer_name, email, created_at)
VALUES
    ('John Doe', '[email protected]', CURRENT_TIMESTAMP),
    ('Jane Smith', '[email protected]', CURRENT_TIMESTAMP);

UPDATE 操作

• 始终包含 WHERE 子句（有意更新所有行时除外）
• 先使用 SELECT 测试 UPDATE 查询
• 对关键更新操作，考虑使用事务

DELETE 操作

• 始终包含 WHERE 子句
• 对可恢复的数据使用软删除（状态标记）
• 考虑对相关表的级联影响

命名约定

• 表名和列名使用 snake_case
• 表名使用单数名词（customer，而不是 customers）
• 主键使用表名作为前缀：customer_id
• 使用描述性名称：order_total，而不是 ot
• 布尔列使用适当的前缀：is_active、has_shipped

文档

• 在查询中为复杂业务逻辑添加注释
• 记录存储过程的用途、参数和示例
• 为表和列维护数据字典
• 对数据库架构变更进行版本控制

错误处理

• 在存储过程中实现适当的错误处理
• 记录错误并提供足够的上下文以便调试
• 向调用应用返回有意义的错误消息
• 在支持的情况下使用 TRY-CATCH 块