SQL开发规范[mysql]

傍晚时分，你坐在屋檐下，看着天慢慢地黑下去，心里寂寞而凄凉，感到自己的生命被剥夺了。当时我是个年轻人，但我害怕这样生活下去，衰老下去。在我看来，这是比死亡更可怕的事。——–王小波

写在前面

---

• 嗯，公司有考核，整理的笔记
• 理解不足小伙伴帮忙指正

傍晚时分，你坐在屋檐下，看着天慢慢地黑下去，心里寂寞而凄凉，感到自己的生命被剥夺了。当时我是个年轻人，但我害怕这样生活下去，衰老下去。在我看来，这是比死亡更可怕的事。——–王小波

---

SQL 开发规范

数据库名，表名，字段名全部小写

脚本 SQL 语句必须以分号结尾,程序内部和配置文件中 SQL 语句不强制使用分号结尾

SQL 格式建议参照 Workbench 工具格式化，美观统一方便阅读

SELECT
  cust_id,
  cust_code
FROM
  cust
WHERE
  cust_name LIKE 'ja%';

超过三个实例表禁止 JOIN；需要JOIN的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。 即使双表 JOIN 也要注意表索引、SQL 性能。

SELECT
	a.pay_channel_id,
	a.pay_channel_name,
	b.attr_id,
	b.attr_value 
FROM
	pay_channel AS a
	JOIN pay_channel_attr AS b ON a.pay_channel_id = b.pay_channel_id

严禁左模糊或者全模糊搜索。

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

SELECT
	cust_id,
	cust_code 
WHERE
	cust_name LIKE 'Ja%';

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

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

# 索引：a_b_c

利用覆盖索引来进行查询操作，避免回表。

如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，
这个目录就是起到覆盖索引的作用。
能够建立索引的种类：主键索引、唯一索引、普通索引，而覆盖索引是查询的一种效果，使用 explain
查看结果，`extra 列会出现：using index`。

SELECT
  user,
  host
FROM
  db AS a
WHERE
  a.user = 'mysql.sys';

已知a.user上有单列索引，应用场景中只是为了获取 user 信息；host 为非必须要获取的信息，该 host 信息查询时需要回表

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

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

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

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

反例

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

正例先快速定位需要获取的 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;

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

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

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

SQL 语句禁止使用非同类型的列进行等值查询

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

SELECT NAME 
FROM
	test 
WHERE
	NAME = 1;
其中 NAME 为字符类型字段， 1 为 INT 数字类型，索引失效；

例外： SELECT
id 
FROM
	test 
WHERE
	id = ’ 1’;
其中 id 列为 INT 数字类型，数字类型转字符类型，虽然索引不失效，但不建议这么使用。

SELECT NAME 
FROM
	test 
WHERE
	NAME = ‘ whalecloud’;

Note：其中 NAME 为字符类型字段，类型一致

SQL规约

使用 COUNT(*)来统计记录行数

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

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

• COUNT(DISTINCT col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0;

• COUNT(*) 和 COUNT(常量) 的性能并没有明显的差异;

MySql 官网：InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is noperformance difference. 适用于 MySQL5.7+版本。

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

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

SELECT
	IFNULL( SUM( g ), 0 ) 
FROM
	TABLE;

使用 ISNULL()来判断是否为 NULL 值

NULL 与任何值的直接比较都为 NULL。换句话讲，考虑到数据的不确定性，Null一般不做为一个比较运算的确定值，而是通过函数的方式确认。

• NULL<>NULL 的返回结果是 NULL，而不是 false。
• NULL=NULL 的返回结果是 NULL，而不是 true。
• NULL<>1 的返回结果是 NULL，而不是 true。

ISNULL(expr) 的用法：如 expr 为 null，那么 ISNULL() 的返回值为 1，否则返回值为 0。

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

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

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

SQL 语句中表的别名前加 AS

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

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

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

此场景适应 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 );

此场景适应 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 );

禁止在开发代码中使用 TRUNCATE TABLE 语句

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

DELETE FROM, UPDATE语句，必须带 WHERE 条件

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

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

禁止使用跨库查询，包括同一实例也禁止使用跨库查询

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

SELECT
	a.prefix,
	a.acc_nbr,
	b.acct _name 
FROM
	subs AS a,
	acct AS b 
WHERE
	a.acct_id = b.acct_id 
	AND b.acct_id = 3421;

subs 表在 ocs 库，acct 表在 ocs 库，当前是 ocs 库。

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

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 (?);	

事务要简单，整个事务的时间长度不要太长，要及时提交。

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

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

WHERE id=1 or id=2 or id=3;
WHERE id IN (1,2,3);

当只有一行数据时使用 LIMIT 1

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

SELECT
	cust_name 
FROM
	cust 
WHERE
	email = ? 

email 字段上无索引，即使找到一条记录也会继续往后找，性能低。

SELECT
	cust_name 
FROM
	cust 
WHERE
	email = ? LIMIT1

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

避免使用大表做 JOIN、GROUP BY 分组、排序

SELECT
	txn_type_id,
	SUM( amount ) 
FROM
	bc_transaction 
WHERE
	acct_id = ? 
GROUP BY
	txn_type_id;

应该代码里面根据txn_type_id对金额进行汇总。

SELECT
	txn_type_id,
	amount 
FROM
	bc_transaction 
WHERE
	acct_id = ?;

尽量不使用 NOT IN

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

合理选择 UNION ALL 与 UNION。

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

• UNION ALL 操作只是简单的将两个结果合并后就返回，所以可能存在重复记录。需要结合业务需求分析使用 UNION ALL 的可行性。

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

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

• 当需要查询表中的所有列时，也需列出所有的字段名。
• 例外：如果有子查询，而且子查询有列名的，可以使用 SELECT *。

禁止在代码中拼接 sql，推荐使用预编译 sql

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

禁止使用 ORDER BY RAND()

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

禁止单条 SQL 语句同时更新多个表。

禁止使用 SELECT … FOR UPDATE 的操作，会导致锁表。

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

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

用 WHERE 子句替换 HAVING 子句

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

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

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

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

主 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;

分布式数据库 SQL 开发规范

名詞介紹

名詞	説明
全局表	在业务系统中，往往存在大量的类似字典表的数据库表，这类表的数据量一般较小，变化不频繁，如：字典、配置、工号、基表、区域等，这类表定义为全局表，即在每个库都保存一份完整的相同数据，全局表就是用于解决这一类表的跨库关联查询问题。全局表也叫广播表。
分片表	分片(水平)是根据某种规则将数据分散至多个库中,每个分片仅包含数据的一部分。这类表即为分片表，这些库即为分片。
库内分表	逻辑表在同一个数据库实例的同一个 schema 内进行分表，以解决单表数据量过大、分片数量过多和跨分片事务的问题。例如将 goods 表分成多个子表，分别为 goods_0,goods_1, goods_2……可用于替换 MySQL 的分区表。库内分表和水平分库组合使用。狭义的库内分表是不分片纯库内分表。
分片键	用于分片的字段

SQL 兼容性约束

不支持 BEGIN…END、LOOP…END LOOP、REPEAT…UNTIL…END REPEAT、WHILE…DO…END WHILE 等复合语句

不支持类似 IF 、WHILE 等流程控制类语句。

不支持 SAVEPOINT 操作；禁止使用在 SQL 里带 SCHEMA 操作。

不支持 CREATE TABLE tbl_name LIKE old_tbl_name；不支持 CREATE TABLE tbl_name SELECT statement。

不支持 UPDATE 分片键、分表键的值。

不支持 SELECT INTO OUTFILE/INTO DUMPFILE/INTO var_name。

不支持 SQL 中带聚合条件的关联子查询(Correlate Subquery)

不支持 SQL 中对于变量的引用和操作，比如 SET @c=1, @d=@c+1; SELECT @c, @d。

不支持 SELECT 语句包含 ESCAPE 定义特殊转义符。

不支持 SELECT 语句空字符串为别名

关联的分片表的分布必须一致。

关联的分片表的分布一致，比如保证分片规则，分片节点，分片键值(两个字段的名称可以不一样，但实际含义必须一样)一致

分片表关联查询 SQL 上必须带有分片键字段的关联。

使用 UNION 关键字的 SQL，要求其中涉及分片表的分片规则及分片数一致，使用 UNION ALL关键字的 SQL 则无此要求。

分片表不支持 UNION/UNION ALL 与聚合函数、LIMIT、GROUP BY、HAVING、ORDER BY 等关键字联用

不支持分片表和全局表 UNION。

库内分表间的自连接时，需要以分表字段作为关联条件。

其中 routing_id 为分表键

SELECT
	acc_nbr 
FROM
	subs AS a 
WHERE
	a.subs_id = ( SELECT MAX( subs_id ) FROM subs AS b WHERE a.routing_id = b.routing_id AND a.acc_nbr = b.acc_nbr );

不支持包含 库内分表 的 UNION 运算，比如两个库内分表，库内分表与分片表，库内分表与全局表。

不支持包含 库内分表 的 UNION/UNION ALL 与聚合函数、LIMIT、GROUP BY、HAVING、ORDER BY 等关键字联用。

不支持在包含 分片表或库内分表的 SQL 中使用包含聚合函数及运算的表达式(诸如count(*)+’’)。

不支持子查询中 库内分表 的表的数量大于 1。

不支持包含 库内分表 的子查询中使用包含聚合函数及运算的的表达式(诸如count(*)+’’)。

不支持包含 库内分表 的子查询中使用 UNION/UNION ALL

不支持 库内分表 子查询中包含与主 SQL 的关联条件，且包含 LIMIT/ORDER BY/ GROUP BY/ HAVING 关键字。

不支持 DELETE、UPDATE 语句中使用包含 库内分表 的子查询。

不支持 DELETE、UPDATE 语句包含 库内分表 在内的多表操作

跨节点操作约束

谨慎使用分布式事务，引入分布式后，根据 CAP 理论，强一致性与可用性不可兼得，事务边界越大，那么系统的锁冲突概率越高，系统越难以扩展，性能也越低。因此一般工程实践中 ，若想将系统做到很好的扩展性，
解决分布式事务最好的方法就是尽量规避分布式事务，
一个最重要的原则就是业务侧想办法划小事务边界，并尽可能让事务的边界限制在单实例 MySQL 内。
业务侧需考虑如何保证业务数据整体的一致性。少数无法规避且必须使用中间件的分布式事务的场景，也需谨慎选择中间件提供的分布式事务功能，注意对应的约束说明

现有分布式现状：
DRDS 支持，但不推荐使用
UDAL 支持，但不推荐使用
ZDaas 不支持
ZDaas-JDBC 不支持

JDBC 约束

不支持 rewriteBatchedStatements=true 参数设置(默认为 false)。

不支持 useServerPrepStmts=true 参数设置(默认为 false)。

BLOB, BINARY, VARBINARY 字段不能使用 setBlob() 或 setBinaryStream() 方法设置参数。

库内分表间禁止使用 LEFT JOIN/RIGHT JOIN/INNER JOIN；【违反结果】慢查询

禁止 UNION、UNION ALL 中使用两个及两个以上的库内分表的场景；【违反结果】可能导致性能低。

高频查询语句中应尽量带上分片键，避免广播语句产生；【违反结果】广播查询，降低查询速度。

分表或分片字段尽量建立物理数据库索引，以提升查询速度；【违反结果】查询响应时间长，数据库性能消耗高

子查询若包含库内分表，使用 UNION ALL 的形式组合子查询内的 SQL，建议尽量使用 JOIN的写法来替代子查询写法；【违反结果】查询响应时间长，数据库性能消耗高。