【超硬核】-1万字详尽大厂团队SQL开发规范,Review没人能笑着出来_sql开发规范文档

前言

代码如脸面，增强别人眼中你的代码规范

历经无数的案例，由于SQL设计、编写不规范造成后期大量的BUG与性能隐患。

很多团队由于需求First，没精力建设团队统一规范，长期来看这是本末倒置的，它一定会在某一天积累爆发倒逼着你去花N倍的精力去重构…

1. 概述

1.1 编写目的

• 统一SQL语句编写格式。
• 让SQL语句更美观，更易阅读。
• 增强SQL可维护性。
• 避免低效SQL导致的性能问题。

1.2 适用范围

适用于MySQL5.7及以上版本。在使用C/C++、Java等各种编程语言进行开发活动及运维活动中编写SQL语句时，需要遵循此规范。

1.3 角色和职责

开发：负责按照SQL规范进行SQL编码
运维人员：负责根据SQL规范编写运维需要的SQL脚本。

2. SQL开发规范

2.1 SQL书写规范

2.1.1 【推荐】数据库名，表名，字段名全部小写。

说明：
例外：对于从ORACLE转MySQL的项目，数据库名，表名，字段名可继续保持ORACLE的SQL规范要求，用大写。

2.1.2 【推荐】脚本SQL语句必须以分号结尾；程序内部和配置文件中SQL语句不强制使用分号结尾。

2.1.3 【推荐】SQL格式建议参照Workbench工具格式化，美观统一方便阅读。

2.2 索引规约

2.2.1 【强制】超过三个实例表禁止 JOIN；需要 JOIN的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。

即使双表JOIN也要注意表索引、SQL性能。

ps: 超过3个实例表禁止JOIN,如何改写呢？ 这个我们可以做适当的冗余设计；或者中间表；

2.2.2 【强制】严禁左模糊或者全模糊搜索。

在SQL中尽量不使用LIKE。即使使用也要禁止使用前缀是%的LIKE匹配，因为索引文件具有 B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

2.2.3 【推荐】如果有 ORDER BY的场景，请注意利用索引的有序性。ORDER BY 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort（文件排序）的情况，影响查询性能。

反例：
索引中有范围查找，那么索引的有序性无法利用。
如：WHERE a>10 ORDER BY b; 索引a_b无法排序。

正例：
WHERE a=? AND b=? ORDER BY c;
索引：a_b_c

2.2.4 【推荐】利用覆盖索引来进行查询操作，避免回表。

如果一本书需要知道第 11章是什么标题，会翻开第 11章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。

能够建立索引的种类：主键索引、唯一索引、普通索引，而覆盖索引是查询的一种效果，使用 explain查看结果，extra列会出现：using index。

反例：

SELECT
	user,
	host
	FROM
	db AS a
WHERE
	a.user = 'mysql.sys';
Note：已知a.user上有单列索引，应用场景中只是为了获取user信息；host为非必须要获取的信息，该host信息查询时需要回表；

1
2
3
4
5
6
7
8
9

正例：

EXPLAIN SELECT
	user
	FROM
	db AS a
WHERE
	a. user = 'mysql.sys'

1
2
3
4
5
6
7

Note：已知a.user上有单列索引，应用场景中只是为了获取user信息，此时可以走索引不需要回表；

2.2.5 【推荐】利用 延迟关联 或者 子查询 优化超多分页场景。

MySQL并不是跳过 offset行，而是取 offset+N行，然后返回放弃前 offset行，返回N行，那当 offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL改写。

反例：

SELECT
	id,
	name
FROM
	user_info
WHERE
	dep_id = 1
LIMIT 100000,20

1
2
3
4
5
6
7
8
9

正例：
Note：先快速定位需要获取的 id字段，然后再关联：

SELECT 
    a.id, a.name
FROM
    user_info AS a,
    (SELECT 
        id
    FROM
        user_info
    WHERE
        dep_id = 1
    LIMIT 100000 , 20) AS b
WHERE
    a.id = b.id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.2.6 【推荐】SQL性能优化的目标：至少要达到 range 级别，要求是 ref级别，如果可以是 const最好。

1）const 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2）ref 指的是使用普通的索引（normal index）。
3）range 对索引进行范围检索。

反例：

explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index级别比较 range还低，与全表扫描是小巫见大巫。

EXPLAIN select
	event_id
FROM
	zcm_nms.alarm_event a
WHERE
	a.alarm_time >= now() - 10
AND a.alarm_time <= now()

1
2
3
4
5
6
7
8
9
10

正例：

EXPLAIN SELECT
	event_id
FROM
	zcm_nms.alarm_event a
WHERE
	a.event_id=30
1
2
3
4
5
6

2.2.7 【强制】SQL语句禁止使用非同类型的列进行等值查询

字段类型不同会造成隐式转换，导致索引失效。

2.3 SQL规约

2.3.1 【推荐】使用COUNT(*)来统计记录行数

不要使用 COUNT(列名) 或 COUNT(常量) 来替代 COUNT()，COUNT () 是 SQL92定义的标准统计行数的语法，跟数据库无关，跟 NULL和 非NULL无关。COUNT(DISTINCT col) 计算该列除 NULL之外的不重复行数。

COUNT(*) 会统计值为 NULL的行，而 COUNT(列名)不会统计此列为 NULL值的行。

Note：

• a. COUNT(DISTINCT col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0;
• b. COUNT() 和 COUNT(常量) 的性能并没有明显的差异; MySql官网：InnoDB handles SELECT COUNT() and SELECT COUNT(1) operations in the same way. There is no performance difference. 适用于MySQL5.7+版本。

2.3.2 【强制】 当某一列的值全是NULL时，COUNT(col)的返回结果为 0，但 SUM(col)的返回结果为NULL，因此使用 SUM()时需注意NPE（空指针）问题。

可以使用如下方式来避免 SUM()的 NPE问题：

SELECT
	IFNULL(SUM(g), 0)
FROM
	TABLE;
1
2
3
4

2.3.3 【强制】使用 ISNULL()来判断是否为 NULL值。

NULL与任何值的直接比较都为 NULL。
1） NULL<>NULL的返回结果是 NULL，而不是 false。
2） NULL=NULL的返回结果是 NULL，而不是 true。
3） NULL<>1的返回结果是 NULL，而不是 true。

1
2
3
4
5

2.3.4 【强制】禁止使用：触发器、自定义函数、存储过程、视图、事件等MySQL高级功能

存储过程难以调试和扩展，更没有移植性。
为避免业务逻辑与数据存储发生耦合，禁止使用上述功能，否则不利于后期scale out（扩展）、sharding（分库分表）。

Note：MySQL数据库原生函数可以用，自定义函数不可用。

2.3.5 【推荐】SQL语句中表的别名前加AS。

• 1）别名可以是表的简称。
• 2）别名前加 AS 使别名更容易识别。

2.3.6 【推荐】IN操作能避免则避免，若实在避免不了，需要仔细评估 IN后边的集合元素数量，控制在 500个之内。

可以用EXIST代替IN，EXIST在某些场景比IN效率高。

例1：
此场景适应A表数据量大于B表（B表数据量较少），且WHERE后的字段加了索引。这种情况用IN效率高的原因是利用了大表的索引。
SELECT
	a.ecs_goods_id, a.ecs_goods_name
FROM
	ecs_goods AS a
WHERE
	a.cat_id IN (
		SELECT
			b.cat_id
		FROM
			ecs_category AS b
	);

例2：
此场景适应B表数据量大于A表，且WHERE后的字段加了索引。这种情况用EXISTS效率高的原因是利用了大表的索引。
SELECT
	a.ecs_goods_id, a.ecs_goods_name
FROM
	ecs_goods AS a
WHERE
	EXISTS (
		SELECT
			cat_id
		FROM
			ecs_category AS b
		WHERE
			a.cat_id = b.cat_id
	);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

2.3.7 【强制】禁止在开发代码中使用TRUNCATE TABLE语句

TRUNCATE TABLE可能会造成生产的性能事故和安全事故。

2.3.8 【强制】DELETE FROM, UPDATE语句，必须带WHERE条件

如若不加WHERE条件，则是对全表进行删除、更新操作，可能会引起非常严重的后果，所以必须要加上相应的WHERE条件方可。

Note：如果不带WHERE条件的DELETE操作，会将表中所有记录都删除。如果表中数据量过大，也可能会造成性能事故。

2.3.9 【强制】禁止使用跨库查询，包括同一实例也禁止使用跨库查询。

禁止使用跨库（跨schema）查询，方便后续分库分表。

2.3.10 【推荐】核心业务流程SQL包含：数学运算（数据库不擅长数学运算和逻辑判断）、多表关联、表遍历CASE WHEN等复杂查询，建议拆分成单表简单查询

提示：本条目与ORACLE开发规范有差异

反例：

SELECT
SUM(a.amount)
FROM
payment AS a,
acct_book AS b
WHERE
a.payment_id = b.acct_book_id
AND b.acct_id = ?;

Note：payment和acct_book都是大表，不建议关联查询

1
2
3
4
5
6
7
8
9
10
11

正例：

SELECT
	b.acct_book_id
FROM
	acct_book AS b
WHERE
	b.acct_id = ?;

SELECT
	SUM(a.amount)
FROM
	payment AS a
WHERE
	a.payment_id IN (?);

Note：payment和acct_book都是大表，拆成单表简单查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.3.11 【推荐】事务要简单，整个事务的时间长度不要太长，要及时提交。

2.3.12 【推荐】对数据库的批量增删改操作，应拆分成多个事务进行操作。限制单个事务所操作的数据集大小，不能超过 10000 条记录。

2.3.13 【强制】条件中对于同一个字段使用到OR的SQL语句必须改写成用IN()。

MySQL 中OR的效率比IN低很多

2.3.14 【推荐】当只有一行数据时使用LIMIT 1。

大数据量，过滤条件未加索引，且事先知道结果只需要一条记录时使用LIMIT 1，可加快执行效率。

SELECT
cust_name
FROM
cust
WHERE
email = ? LIMIT1

Note：email字段上无索引，找到一条记录后即返回

1
2
3
4
5
6
7
8
9

2.3.15 【推荐】避免使用大表做JOIN、GROUP BY分组、排序。

SELECT
txn_type_id,
amount
FROM
bc_transaction
WHERE
	acct_id = ?;

Note：代码里面根据txn_type_id对金额进行汇总。

1
2
3
4
5
6
7
8
9
10

2.3.16 【建议】尽量不使用NOT IN。

数据库不善于反向查找，故不建议使用NOT IN。

2.3.17 【建议】合理选择UNION ALL与UNION。

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果，如果表数据量大的话可能会导致用磁盘进行排序。

UNION ALL操作只是简单的将两个结果合并后就返回，所以可能存在重复记录。

需要结合业务需求分析使用UNION ALL的可行性。 

1
2
3
4
5
6

mysql> SELECT id,name,age FROM std1 WHERE 1=1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
| 11 | May   |  18 |
| 12 | Jane  |  15 |
| 13 | Sunny |  19 |
+----+-------+-----+
3 rows in set

mysql> SELECT id,name,age FROM std2 WHERE 1=1;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 11 | May  |  18 |
| 18 | Anni |  16 |
+----+------+-----+
2 rows in set

mysql> SELECT id,name,age FROM std1
UNION
SELECT id,name,age FROM std2;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
| 11 | May   |  18 |
| 12 | Jane  |  15 |
| 13 | Sunny |  19 |
| 18 | Anni  |  16 |
+----+-------+-----+
4 rows in set

mysql> SELECT id,name,age FROM std1
UNION ALL
SELECT id,name,age FROM std2;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
| 11 | May   |  18 |
| 12 | Jane  |  15 |
| 13 | Sunny |  19 |
| 11 | May   |  18 |
| 18 | Anni  |  16 |
+----+-------+-----+
5 rows in set

Note：上述举例说明UNION ALL和UNION对于查询结果的区别，请结合实际场景合理选择。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

2.3.18 【强制】禁止在OLTP类型系统中使用没有WHERE条件的查询。

反例：

SELECT
acc_nbr,
acct_id
FROM
	subs;

1
2
3
4
5
6

2.3.19 【强制】使用SELECT、INSERT语法时必须写上具体的字段名，避免在表结构变更后出现不必要的麻烦。

Note：
a．当需要查询表中的所有列时，也需列出所有的字段名。
b．例外：如果有子查询，而且子查询有列名的，可以使用SELECT *。目前框架的分页是对业务侧SQL进行了一层包装，必须要select * ,因为框架不知道业务SQL细节。 这个规则在框架侧可以例外。

2.3.20 【强制】禁止在代码中拼接sql，推荐使用预编译sql

Java代码中使用 prepared statement 对象，只传参数，比传递 SQL 语句更高效；一次解析，多次使用；降低SQL 注入概率。

反例：

String sql = "SELECT id,name FROM tb_name WHERE name= '"+varname+"' AND p-asswd='"+varpasswd+"'";

（1）如果我们把[' or '1' = '1]作为varpasswd传入进来.用户名随意
SELECT id,name FROM tb_name WHERE name= '随意' AND p-asswd = '' or '1' = '1';
因为'1'='1'肯定成立,所以可以任何通过验证.

（2）如果把[';drop table tb_name;]作为varpasswd传入进来,则:
SELECT * FROM tb_name WHERE name= '随意' AND p-asswd = '';drop table tb_name;
MySQL数据库可以使这些语句得到执行。 

1
2
3
4
5
6
7
8
9
10

正例：

perstmt = con.prepareStatement("insert into tb_name (col1,col2,col2,col4) values (?,?,?,?)");
perstmt.setString(1,var1);
perstmt.setString(2,var2);
perstmt.setString(3,var3);
perstmt.setString(4,var4);
perstmt.executeUpdate();

1
2
3
4
5
6
7

2.3.21 【强制】禁止使用 ORDER BY RAND()。

ORDER BY RAND() 生成随机结果，会降低查询效率.

2.3.22 【强制】禁止单条 SQL 语句同时更新多个表。

反例：

UPDATE subs AS s
LEFT JOIN acct AS a ON s.acct_id = a.acct_id
SET s.prefix = ‘0086’
SET e.salary = 10000,
t.name= ‘ABC’
WHERE
e.id = 3
AND t.id = 1;

1
2
3
4
5
6
7
8
9

2.3.23 【强制】禁止使用SELECT … FOR UPDATE的操作，会导致锁表。

反例：

SELECT
	acct_item_type,
	charge
FROM
	acct_item AS a
WHERE
	acct_id = ?
AND acct_item_type_id = 3 FOR UPDATE;

1
2
3
4
5
6
7
8
9

2.3.24 【建议】减少对函数的使用，方便MySQL与Oracle之间迁移，同时降低数据库CPU的消耗。

UPDATE subs AS s
SET s.update_date = SYSDATE - 1
WHERE
	s.subs_id = ?;

NOTE: Oracle中SYSDATE – 1表示当前时间减1天；MySQL中SYSDATE() – 1表示当前时间减1秒。这种数据库间的差异已经导致过程序故障。

1
2
3
4
5
6
7

正例：

UPDATE subs AS s
SET s.update_date = ?
WHERE
	s.subs_id = ?;

Note: SQL中需要传入实例化后的时间参数。

1
2
3
4
5
6
7

PS: 某些平台需要部署到不同的客户环境所以需要考虑到各个数据库之间的兼容

2.3.25 【推荐】用WHERE子句替换HAVING子句。

避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、统计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

2.3.26 【强制】HAVING条件中不要使用“AND”或“OR”连接的多个表达式；【违反后果】可能导致性能低。

2.3.27 【强制】HAVING条件中请在表达式左侧使用字段或别名，右侧使用过滤值；【违反后果】可能导致性能低。

-- 反例：
SELECT
	p.product_type,
	COUNT(*)
FROM
	product AS p
GROUP BY
	p.product_type
HAVING
	2 = COUNT(*);


-- 正例：
SELECT
	p.product_type,
	COUNT(*)
FROM
	product AS p
GROUP BY
	p.product_type
HAVING
	COUNT(*) = 2;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

2.3.28 【强制】HAVING条件中使用字段或函数的别名，勿使用函数本身；【违反后果】可能导致性能低。

-- 反例
SELECT
	s.cam_id
	c.cam_name,
	AVG(s.id) AS studentAvg
FROM
	Students AS s
JOIN campus AS c ON c.cam_id = s.cam_id
GROUP BY
	s.cam_id,
	c.cam_name
HAVING
	AVG (s.id) > 2200000
ORDER BY
	s.cam_id;


-- 正例：
SELECT
	s.cam_id,
	c.cam_name,
	AVG (s.id) AS studentAvg
FROM
	Students AS s
JOIN campus AS c ON c.cam_id = s.cam_id
GROUP BY
	s.cam_id,
	c.cam_name
HAVING
	studentAvg > 2200000
ORDER BY
	s.cam_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

2.3.29 【强制】主SQL语句的HAVING中不要使用子查询（Subquery），只能处理常量；【违反后果】可能导致性能低。

-- 反例
SELECT
	a.deptno,
	AVG(a.sal)
FROM
	emp AS a
GROUP BY
	a.deptno
HAVING
	a. AVG (sal) > (SELECT b. AVG (sal) FROM emp AS b);

-- 正例
SELECT
	a.deptno,
	AVG(a.sal)
FROM
	emp AS a
GROUP BY
	a.deptno
HAVING
	a. AVG (sal) > 15000;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3. 分布式数据库SQL开发规范

3.1 分布式数据库名词解释

全局表：
在业务系统中，往往存在大量的类似字典表的数据库表，这类表的数据量一般较小，变化不频繁，如：字典、配置、工号、基表、区域等，这类表定义为全局表，即在每个库都保存一份完整的相同数据，全局表就是用于解决这一类表的跨库关联查询问题。全局表也叫广播表。

分片表
分片（水平）是根据某种规则将数据分散至多个库中,每个分片仅包含数据的一部分。这类表即为分片表，这些库即为分片。

库内分表
逻辑表在同一个数据库实例的同一个schema内进行分表，以解决单表数据量过大、分片数量过多和跨分片事务的问题。例如将goods表分成多个子表，分别为goods_0, goods_1, goods_2……可用于替换MySQL的分区表。库内分表和水平分库组合使用。狭义的库内分表是不分片纯库内分表。

分片键
用于分片的字段

3.2 SQL兼容性约束

pass

3.3 跨节点操作约束

pass

3.4 JDBC约束

pass

3.5 性能优化

pass