Back to Details

sql-best-practices

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

SQL Best Practices

SQL最佳实践

Core Principles

核心原则

  • Write clear, readable SQL with consistent formatting and meaningful aliases
  • Prioritize query performance through proper indexing and optimization
  • Implement security best practices to prevent SQL injection
  • Use transactions appropriately for data integrity
  • Document complex queries with inline comments
  • 编写清晰、可读性强的SQL，使用一致的格式和有意义的别名
  • 通过合理的索引和优化优先保障查询性能
  • 实施安全最佳实践以防止SQL注入
  • 合理使用事务保障数据完整性
  • 为复杂查询添加行内注释进行文档说明

Query Writing Standards

查询编写规范

Formatting and Style

格式与风格

  • Use uppercase for SQL keywords (SELECT, FROM, WHERE, JOIN)
  • Place each major clause on a new line for readability
  • Use meaningful table aliases (e.g., 
    customers AS c
    not 
    customers AS x
    )
  • Indent subqueries and nested conditions consistently
  • Align column lists and conditions for visual clarity
sql
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;
  • SQL关键字使用大写（SELECT、FROM、WHERE、JOIN）
  • 每个主要子句单独占一行以提升可读性
  • 使用有意义的表别名（例如
    customers AS c
    而非
    customers AS x
    ）
  • 对子查询和嵌套条件进行一致的缩进
  • 对齐列列表和条件以提升视觉清晰度
sql
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;

Column Selection

列选择

  • Avoid 
    SELECT *
    in production code; explicitly list required columns
  • Use column aliases to clarify output: 
    SELECT first_name AS "First Name"
  • Consider the order of columns in SELECT for logical grouping
  • 生产代码中避免使用
    SELECT *
    ；明确列出所需列
  • 使用列别名明确输出：
    SELECT first_name AS "First Name"
  • 考虑SELECT中列的顺序以实现逻辑分组

Filtering and Conditions

过滤与条件

  • Place most restrictive conditions first in WHERE clauses
  • Use appropriate operators: prefer 
    IN
    over multiple 
    OR
    conditions
  • Use 
    EXISTS
    instead of 
    IN
    for subqueries when checking existence
  • Avoid functions on indexed columns in WHERE clauses when possible
  • Use parameterized queries to prevent SQL injection
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'
  • 将限制性最强的条件放在WHERE子句的最前面
  • 使用合适的运算符：优先使用
    IN
    而非多个
    OR
    条件
  • 检查存在性时，使用
    EXISTS
    而非子查询中的
    IN
  • 尽可能避免在WHERE子句的索引列上使用函数
  • 使用参数化查询防止SQL注入
sql
-- 推荐：使用EXISTS检查存在性
SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = o.customer_id
    AND o.order_date > '2024-01-01'
);

-- 避免：在索引列上使用函数
WHERE YEAR(order_date) = 2024

-- 推荐：范围比较
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

Join Best Practices

连接最佳实践

  • Always use explicit JOIN syntax instead of implicit joins in WHERE
  • Specify join type explicitly (INNER, LEFT, RIGHT, FULL OUTER)
  • Order joins from largest to smallest table when possible
  • Use appropriate join types based on data requirements
  • Be cautious with CROSS JOINs; ensure they are intentional
sql
-- 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;
  • 始终使用显式JOIN语法，而非WHERE子句中的隐式连接
  • 明确指定连接类型（INNER、LEFT、RIGHT、FULL OUTER）
  • 尽可能按照从大表到小表的顺序进行连接
  • 根据数据需求使用合适的连接类型
  • 谨慎使用CROSS JOIN；确保其使用是有意为之
sql
-- 显式连接（推荐）
SELECT c.name, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id;

-- 避免隐式连接
SELECT c.name, o.order_id
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;

Performance Optimization

性能优化

Indexing Guidelines

索引指南

  • Create indexes on columns used in WHERE, JOIN, and ORDER BY clauses
  • Consider composite indexes for multi-column queries
  • Avoid over-indexing; each index adds write overhead
  • Regularly analyze and maintain indexes
  • Use covering indexes for frequently executed queries
  • 为WHERE、JOIN和ORDER BY子句中使用的列创建索引
  • 考虑为多列查询创建复合索引
  • 避免过度索引；每个索引都会增加写入开销
  • 定期分析和维护索引
  • 为频繁执行的查询使用覆盖索引

Query Optimization

查询优化

  • Use EXPLAIN/EXPLAIN ANALYZE to understand query execution plans
  • Limit result sets with TOP/LIMIT when full results are not needed
  • Use pagination for large result sets
  • Avoid correlated subqueries when possible; use JOINs instead
  • Consider query caching for frequently executed queries
sql
-- Pagination example
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;
  • 使用EXPLAIN/EXPLAIN ANALYZE理解查询执行计划
  • 当不需要完整结果时，使用TOP/LIMIT限制结果集
  • 对大型结果集使用分页
  • 尽可能避免关联子查询；改用JOIN
  • 考虑对频繁执行的查询使用查询缓存
sql
-- 分页示例
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;

Aggregation Best Practices

聚合最佳实践

  • Filter before grouping when possible (WHERE vs HAVING)
  • Use appropriate aggregate functions (COUNT, SUM, AVG, etc.)
  • Consider window functions for running totals and rankings
sql
-- Efficient: Filter before aggregation
SELECT category_id, COUNT(*) AS product_count
FROM products
WHERE active = true
GROUP BY category_id
HAVING COUNT(*) > 10;
  • 尽可能在分组前进行过滤（WHERE与HAVING的区别）
  • 使用合适的聚合函数（COUNT、SUM、AVG等）
  • 考虑使用窗口函数计算累计总和和排名
sql
-- 高效方式：聚合前过滤
SELECT category_id, COUNT(*) AS product_count
FROM products
WHERE active = true
GROUP BY category_id
HAVING COUNT(*) > 10;

Transaction Management

事务管理

  • Keep transactions as short as possible
  • Use appropriate isolation levels for your use case
  • Always include error handling with ROLLBACK
  • Avoid user interaction during open transactions
  • Use savepoints for complex multi-step operations
sql
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;
  • 尽可能缩短事务时长
  • 根据使用场景选择合适的隔离级别
  • 始终包含带ROLLBACK的错误处理
  • 避免在打开的事务期间进行用户交互
  • 为复杂的多步骤操作使用保存点
sql
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;

Security Best Practices

安全最佳实践

  • Always use parameterized queries or prepared statements
  • Never concatenate user input directly into SQL strings
  • Apply principle of least privilege for database users
  • Audit and log sensitive data access
  • Encrypt sensitive data at rest and in transit
sql
-- Use parameterized queries (pseudo-code)
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
EXECUTE stmt USING @username;
  • 始终使用参数化查询或预编译语句
  • 永远不要将用户输入直接拼接到SQL字符串中
  • 为数据库用户应用最小权限原则
  • 审计并记录敏感数据的访问
  • 对静态和传输中的敏感数据进行加密
sql
-- 使用参数化查询（伪代码）
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
EXECUTE stmt USING @username;

Data Modification Best Practices

数据修改最佳实践

INSERT Operations

INSERT操作

  • Always specify column names explicitly
  • Use bulk inserts for multiple rows when possible
  • Consider using MERGE/UPSERT for insert-or-update scenarios
sql
INSERT INTO customers (customer_name, email, created_at)
VALUES
    ('John Doe', 'john@example.com', CURRENT_TIMESTAMP),
    ('Jane Smith', 'jane@example.com', CURRENT_TIMESTAMP);
  • 始终明确指定列名
  • 插入多行时尽可能使用批量插入
  • 考虑使用MERGE/UPSERT处理插入或更新场景
sql
INSERT INTO customers (customer_name, email, created_at)
VALUES
    ('John Doe', 'john@example.com', CURRENT_TIMESTAMP),
    ('Jane Smith', 'jane@example.com', CURRENT_TIMESTAMP);

UPDATE Operations

UPDATE操作

  • Always include a WHERE clause (unless intentionally updating all rows)
  • Test UPDATE queries with SELECT first
  • Consider using transactions for critical updates
  • 始终包含WHERE子句（除非有意更新所有行）
  • 先使用SELECT测试UPDATE查询
  • 对关键更新操作考虑使用事务

DELETE Operations

DELETE操作

  • Always include a WHERE clause
  • Use soft deletes (status flags) for recoverable data
  • Consider CASCADE effects on related tables
  • 始终包含WHERE子句
  • 对可恢复数据使用软删除（状态标记）
  • 考虑对关联表的CASCADE影响

Naming Conventions

命名规范

  • Use snake_case for table and column names
  • Use singular nouns for table names (customer, not customers)
  • Prefix primary keys with table name: 
    customer_id
  • Use descriptive names: 
    order_total
    not 
    ot
  • Prefix boolean columns appropriately: 
    is_active
    , 
    has_shipped
  • 表和列名使用蛇形命名法（snake_case）
  • 表名使用单数名词（customer而非customers）
  • 主键以表名为前缀：
    customer_id
  • 使用描述性名称：
    order_total
    而非
    ot
  • 为布尔列添加合适的前缀：
    is_active
    、
    has_shipped

Documentation

文档说明

  • Comment complex business logic within queries
  • Document stored procedures with purpose, parameters, and examples
  • Maintain a data dictionary for table and column descriptions
  • Version control database schema changes
  • 在查询中对复杂业务逻辑添加注释
  • 为存储过程编写文档，说明其用途、参数和示例
  • 维护表和列的数据字典
  • 对数据库架构变更进行版本控制

Error Handling

错误处理

  • Implement proper error handling in stored procedures
  • Log errors with sufficient context for debugging
  • Return meaningful error messages to calling applications
  • Use TRY-CATCH blocks where supported
  • 在存储过程中实施适当的错误处理
  • 记录包含足够调试上下文的错误信息
  • 为调用应用返回有意义的错误消息
  • 在支持的情况下使用TRY-CATCH块