Sql编写规范

SQL编写规范

禁止 select *、join、子查询、limit 约束

---

查询规范

【Requirement】查询必须显式列出所需字段，禁止 SELECT *；必须使用参数化查询，禁止字符串拼接 SQL。

1.1 字段选择

• 只查询业务需要的字段，减少网络传输与内存占用
• 禁止 SELECT *，表结构变更时会导致返回字段不一致
• 多表查询时字段必须加表别名前缀

-- 正确
SELECT id, order_no, total_amount, create_time
FROM order_order
WHERE user_id = ? AND order_status = 1;
 
-- 多表带别名
SELECT o.id, o.order_no, u.username
FROM order_order o
JOIN sys_user u ON o.user_id = u.id
WHERE o.id = ?;

反模式：

-- 错误：SELECT *，全表字段返回
SELECT * FROM order_order WHERE id = ?;
 
-- 错误：多表无别名，字段歧义
SELECT id, name FROM order_order JOIN sys_user ON ...; -- id 歧义

1.2 条件查询

• WHERE 条件中的字段必须能命中索引
• 禁止在索引字段上使用函数或运算，导致索引失效
• 范围查询放在联合索引最后

-- 正确：索引字段无函数
SELECT id, username FROM sys_user WHERE create_time >= '2024-01-01';
 
-- 错误：索引字段使用函数，索引失效
SELECT id, username FROM sys_user WHERE DATE(create_time) >= '2024-01-01';
 
-- 错误：索引字段运算
SELECT id FROM order_order WHERE id + 1 = 100;

1.3 参数化查询

所有 SQL 必须使用预编译参数（? 占位符），禁止字符串拼接：

// 正确：PreparedStatement
String sql = "SELECT id, username FROM sys_user WHERE id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, userId);
 
// MyBatis
@Select("SELECT id, username FROM sys_user WHERE id = #{userId}")
User selectById(@Param("userId") Long userId);

反模式：

// 错误：字符串拼接，SQL 注入风险
String sql = "SELECT id FROM sys_user WHERE username = '" + username + "'";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
 
// 错误：MyBatis ${} 拼接（除 ORDER BY 字段外禁止）
@Select("SELECT id FROM sys_user WHERE username = '${username}'")

---

禁止项

【Requirement】以下 SQL 写法严禁在生产代码中出现。

禁止项	原因	替代方案
SELECT *	返回冗余字段，表变更风险	显式列出字段
WHERE 1=1	无意义条件，可能影响优化器	动态拼接时使用 <where> 标签
OR 连接不同字段	索引失效，全表扫描	拆分为 UNION ALL 或建立联合索引
!= / <>	索引失效	用 IN 枚举或范围查询替代
IS NOT NULL	索引失效（多数情况）	设计时字段设 NOT NULL + 默认值
LIKE '%xxx'	左模糊不走索引	使用全文索引或 ES
IN (子查询)	子查询性能差	改为 JOIN 或 EXISTS
LIMIT M, N（深分页）	扫描大量行	游标分页（WHERE id > lastId）
INSERT INTO ... SELECT *	字段不对应	显式列出字段

示例：

-- 正确：用 UNION ALL 替代 OR
SELECT id FROM order_order WHERE user_id = 1
UNION ALL
SELECT id FROM order_order WHERE merchant_id = 1;
 
-- 错误：OR 连接不同索引字段
SELECT id FROM order_order WHERE user_id = 1 OR merchant_id = 1;

---

Join 规则

【Standard】JOIN 必须有 ON 条件且关联字段有索引；单条 SQL 关联表不超过 3 张；优先使用 INNER JOIN，谨慎使用 LEFT JOIN。

3.1 JOIN 基本原则

1. 关联字段必须有索引，否则全表扫描
2. 驱动表（小表）在前，被驱动表（大表）在后
3. ON 条件用于关联，WHERE 用于过滤，不要混用
4. 禁止隐式 JOIN（逗号分隔表名）

-- 正确：显式 JOIN + 索引字段关联
SELECT o.id, o.order_no, u.username
FROM order_order o
INNER JOIN sys_user u ON o.user_id = u.id
WHERE o.order_status = 1;
 
-- 错误：隐式 JOIN
SELECT o.id, u.username FROM order_order o, sys_user u WHERE o.user_id = u.id;

3.2 LEFT JOIN 注意事项

• LEFT JOIN 中右表的过滤条件必须放在 ON 中，放在 WHERE 会退化为 INNER JOIN

-- 正确：右表过滤条件在 ON 中
SELECT o.id, p.pay_time
FROM order_order o
LEFT JOIN pay_payment p ON o.id = p.order_id AND p.pay_status = 1;
 
-- 错误：右表条件在 WHERE 中，退化为 INNER JOIN
SELECT o.id, p.pay_time
FROM order_order o
LEFT JOIN pay_payment p ON o.id = p.order_id
WHERE p.pay_status = 1;  -- 会过滤掉未支付的订单

3.3 多表关联限制

【Caution】单条 SQL 关联表超过 3 张时，必须评估是否可拆分查询或冗余字段。

-- 可接受：2 表关联
SELECT o.id, p.pay_amount
FROM order_order o
JOIN pay_payment p ON o.id = p.order_id;
 
-- 需评估：4 表关联
SELECT o.id, u.name, p.amount, i.item_name
FROM order_order o
JOIN sys_user u ON o.user_id = u.id
JOIN pay_payment p ON o.id = p.order_id
JOIN order_order_item i ON o.id = i.order_id;
-- 建议：拆分为订单查询 + 批量查询支付信息

---

子查询

【Standard】优先使用 JOIN 替代子查询；子查询结果集超过 1000 行时必须改为 JOIN；禁止在 SELECT 列中使用子查询（相关子查询）。

4.1 子查询 vs JOIN

-- 推荐：JOIN
SELECT DISTINCT u.id, u.username
FROM sys_user u
JOIN order_order o ON u.id = o.user_id
WHERE o.order_status = 1;
 
-- 可接受但次优：IN 子查询（结果集小时）
SELECT id, username FROM sys_user
WHERE id IN (SELECT user_id FROM order_order WHERE order_status = 1);
 
-- 禁止：相关子查询（每行执行一次）
SELECT id, username,
  (SELECT COUNT(*) FROM order_order o WHERE o.user_id = u.id) AS order_count
FROM sys_user u;
 
-- 替代：LEFT JOIN + GROUP BY
SELECT u.id, u.username, COUNT(o.id) AS order_count
FROM sys_user u
LEFT JOIN order_order o ON u.id = o.user_id
GROUP BY u.id, u.username;

4.2 EXISTS 用法

EXISTS 用于判断存在性，比 IN 在大结果集时更优：

-- EXISTS：找到即返回，不继续扫描
SELECT id, username FROM sys_user u
WHERE EXISTS (SELECT 1 FROM order_order o WHERE o.user_id = u.id AND o.order_status = 1);

4.3 派生表

派生表（FROM 子查询）必须加别名，且结果集不宜过大：

-- 正确：派生表带别名
SELECT t.user_id, t.cnt
FROM (
  SELECT user_id, COUNT(*) AS cnt FROM order_order GROUP BY user_id
) t
WHERE t.cnt > 10;

---

分页限制

【Requirement】分页必须使用 LIMIT 且每页 ≤ 100 条；深分页（offset > 10000）必须使用游标分页。

5.1 基础分页

-- 正确：显式 LIMIT，限制每页条数
SELECT id, order_no, create_time
FROM order_order
WHERE user_id = ?
ORDER BY create_time DESC
LIMIT 20 OFFSET 0;
 
-- 简写
SELECT id, order_no FROM order_order WHERE user_id = ? ORDER BY id DESC LIMIT 0, 20;

5.2 深分页优化

传统 LIMIT M, N 需要扫描前 M 行，offset 越大越慢，必须改用游标分页：

-- 传统分页（offset=10000，慢）
SELECT id, order_no FROM order_order ORDER BY id DESC LIMIT 10000, 20;
 
-- 游标分页（快，基于上一页最后一条的 id）
SELECT id, order_no FROM order_order
WHERE id < ?lastId
ORDER BY id DESC
LIMIT 20;

5.3 批量查询限制

【Caution】单次查询返回行数超过 5000 条时，必须分批处理（每次 500-1000 条）。

-- 分批查询
SELECT id, order_no FROM order_order
WHERE id > ?lastId AND create_time >= ?startTime
ORDER BY id ASC
LIMIT 500;

5.4 禁止项

• 禁止 LIMIT 不带 ORDER BY，结果顺序不确定
• 禁止 LIMIT 0, 999999 式的全量分页（应改为批量游标）
• 禁止 SELECT ... LIMIT 1 代替唯一索引查询（应直接用主键/唯一键）

反模式：

-- 错误：无 ORDER BY 的 LIMIT
SELECT id FROM order_order LIMIT 10;  -- 顺序不确定
 
-- 错误：用 LIMIT 1 查唯一记录（应走主键索引）
SELECT * FROM sys_user WHERE username = ? LIMIT 1;
-- 正确：username 建唯一索引，直接查询
SELECT id, username FROM sys_user WHERE username = ?;