9

SQL开发规范[mysql]

 1 year ago
source link: https://liruilongs.github.io/2022/05/19/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql%E8%80%83%E8%AF%95%E7%AC%94%E8%AE%B0/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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的写法来替代子查询写法；【违反结果】查询响应时间长，数据库性能消耗高。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK