178 lines
12 KiB
Markdown
178 lines
12 KiB
Markdown
|
||
|
||
## 4.1 SQL 编写
|
||
|
||
- **规则 1（建议）**：在编写 SQL 语句时，每行建议不要超过 80 字符，基于列对齐原则，应采用下一行缩进。
|
||
- **规则 2（建议）**：使用两个空格表示缩进，同层次的语句应保持纵向对齐。
|
||
- **规则 3（建议）**：建议使用预编译 SQL，PreparedStatement，预编译语句既可以提高性能，又可以有效防止 SQL 注入。
|
||
|
||
## 4.2 查询语句规范
|
||
|
||
- **规则 4（建议）**：尽量避免使用 COUNT(列名)来替代 COUNT(*)，COUNT(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。COUNT(*)会统计值为 NULL 的行，而 COUNT(列名)不会统计此列为 NULL 值的行。
|
||
|
||
- **规则 5（强制）**：禁止使用 SELECT * 操作，所有查询操作必须明确指定列名，避免查询过多无用的列。
|
||
|
||
- **规则 6（强制）**：禁止使用不带 WHERE 条件的 DELETE、UPDATE 语句操作全表，且要使用索引列操作，避免锁表。对于需要客户端传参来创建 SQL 的场景，必须要对参数进行非空判断，确保 SQL 含有 WHERE 条件。
|
||
|
||
- **规则 7（强制）**：对于同时使用包括 AND 和 OR 的复合查询语句，必须增加必要的括号，以保证查询优先级的准确性。
|
||
|
||
- **规则 8（建议）**：使用 IN 查询代替 OR。如使用 IN 查询，需要控制 IN 后边的集合元素数量，不宜过多，需控制在 1000 个之内。
|
||
|
||
- **规则 9（建议）**：建议以合理的方式使用分页查询，使用 LIMIT、OFFSET 查询时，页数越大，需要扫描的数据量越大，查询就越慢。
|
||
|
||
- **规则 10（建议）**：分库分表场景下，尽量避免进行聚合查询，建议 SQL 语句均加上分片键。若
|
||
|
||
有聚合查询的需求，可单独通过聚合库实现。
|
||
|
||
## 4.3 索引规范
|
||
|
||
- **规则 11（强制）**：禁止隐式类型转换，对于字段是字符串类型的，查询必须加引号。
|
||
- **规则 12（强制）**：禁止索引列使用函数运算或者使用表达式，否则会导致索引失效。
|
||
- **规则 13（强制）**：索引列使用 LIKE 模糊查询时，禁止使用%前导查询。
|
||
- **规则 14（强制）**：联合索引查询要遵守最左匹配原则。
|
||
- **规则 15（建议）**：尽量避免对索引列使用负向查询，例如 NOT IN、!=、NOT LIKE、<>，否则可能导致索引失效。
|
||
- **规则 16（建议）**：合理使用覆盖索引，可减少 IO 次数，有效避免回表查询引起的性能问题。
|
||
- **规则 17（建议）**：尽量避免全表扫描，需在生产环境执行的 SQL 原则上都应被索引，如 UPDATE、DELETE 的 WHERE 条件列、ORDER BY、GROUP BY、DISTINCT 字段、多表 JOIN 字段。
|
||
|
||
## 4.4 联结表规范
|
||
|
||
- **规则 18（强制）**：JOIN 联查时必须使用索引列联查，且索引列的类型要保持一致。
|
||
- **规则 19（建议）**：在进行 JOIN 联查时，联查的表数量尽量控制在 3 张表以内，过多的表联查会严重影响性能。
|
||
- **规则 20（建议）**：在进行多表查询时，要用 JOIN 代替子查询，子查询的性能相比于 JOIN 联查较差。
|
||
- **规则 21（建议）**：JOIN 联查时，如果两个表的编码不一致，也有可能导致索引失效，因此要尽量确保联查表的编码方式相同。
|
||
|
||
## 4.5 分组与排序
|
||
|
||
- **规则 22（建议）**：对查询结果顺序无要求时，不要使用 ORDER BY 进行排序，在需要排序的场景下尽量避免使用非索引列进行 ORDER BY 排序。
|
||
- **规则 23（建议）**：避免冗余的排序，使用 GROUP BY 时，默认会进行排序，如果不需要排序，可以使用 ORDER BY NULL。
|
||
- **规则 24（建议）**：避免使用 DISTINCT，可以使用 GROUP BY 代替，如果使用 DISTINCT 获取多列名称时，会对多列进行重复行去重，而不是单独对某一列去重。
|
||
- **规则 25（建议）**：由于 UNION 默认会对结果集进行排序、去重，因此在性能上要比 UNION ALL 差一些。如果结果集没有重复行或者不需要进行重复行合并，建议使用 UNION ALL。
|
||
|
||
## 4.6 事务处理
|
||
|
||
- **规则 26（建议）**：避免大事务（执行时间较长的事务），如耗时的操作、一次性操作的数据过多，数据库单事务提交的明细数量原则上不应超过 5000 笔。大事务可能会导致数据库出现大量的阻塞和锁等待的现象，同时在并发较高的场景下，会造成客户端连接池耗尽、主从延迟等问题。建议将大事务拆分成小事务，分多次操作。
|
||
- **规则 27（强制）**：禁止使用 INSERT INTO B SELECT * FROM A 语句，该语句当执行异常时会出现数据不一致的问题，同时可能会对原表 A 加表锁。
|
||
- **规则 28（建议）**：事务操作，注意获取资源的顺序，避免出现死锁的情况。
|
||
|
||
## 4.7 其他
|
||
|
||
- **规则 29（建议）**：获取大量数据时，建议分批次获取数据，每次数据量应小于 500 条，结果集应小于 1M。
|
||
- **规则 30（建议）**：大批量更新数据时，索引维护成本很高，因此大批量数据更新建议拆分出小粒度，分多次更新。
|
||
- **规则 31（强制）**：当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为 NULL，因此使用 sum()时要注意空指针异常问题。
|
||
- **规则 32（强制）**：禁止使用外键与级联更新，可通过应用程序来约束。级联更新是强阻塞，存在数据库更新风暴的风险，使用数据库外键可能出现死锁，同时影响数据库的插入速度。
|
||
- **规则 33（建议）**：SQL 中避免出现 random()、sysdate()等不确定结果的函数。
|
||
- **规则 34（强制）**：必须使用 IS NULL 来判断是否是 NULL 值。
|
||
- **规则 35（建议）**：谨慎使用存储过程，存储过程难以调试和扩展，不具有移植性。
|
||
|
||
# 6 SQL 语句编写规则
|
||
|
||
## 6.1 查询语句索引使用原则（建议）
|
||
|
||
- 查询列、排序列尽量使用索引列并与其次序保持一致。
|
||
- 应避免在 WHERE 子句中对索引列使用 IS NULL，IS NOT NULL。
|
||
- 应避免在 WHERE 子句中对索引列使用 LIKE ’%xxx%’、LIKE ’%xxx’，可以使用 LIKE ‘xxx%’。
|
||
- 对于复合索引，WHERE 子句中包含索引的第一列查询效率最高。
|
||
- 表结构中字段定义的数据类型与应用程序中的定义保持一致，避免隐式/显式转换情况发生。
|
||
- WHERE 子句中表字段尽量不参加计算。
|
||
- 建议使用||连接字符串，不使用 CONCAT 函数，CONCAT 函数不走索引。
|
||
- WHERE 子句索引列上尽量不用 NOT、OR、!=、<>。
|
||
|
||
## 6.2 查询语句排序原则（建议）
|
||
|
||
- 应尽量减少 ORDER BY 排序操作。
|
||
- 如果一定要排序，应尽量建立在索引列上,且 NULLS FIRST 或 NULLS LAST 与索引 NULLS FIRST 或 NULLS LAST 一致。
|
||
- 如果业务规则允许结果集不需要唯一确定或者各子查询的结果集没有重复，应使用 UNION ALL 替代 UNION。
|
||
- ORDER BY 明确指定列名。
|
||
|
||
## 6.3 使用 case when 减少数据库访问次数（建议）
|
||
|
||
- 合并一条语句，减少对数据库的多次访问。
|
||
|
||
## 6.4 使用 EXIST 代替 IN 语句（建议）
|
||
|
||
- 使用 EXIST 代替 IN 可能会提高性能，但并非所有情况都适用。具体要依据测试结果而定。
|
||
|
||
## 6.5 使用 NOT EXIST 或外连接代替 NOT IN 语句（建议）
|
||
|
||
- 使用 NOT EXIST 或外连接代替 NOT IN 可能会提高性能，但并非所有情况都适用。具体要依据测试结果而定。
|
||
- 注意 NOT IN 与 NOT EXISTS 不一定相等，需注意对空值的处理。
|
||
|
||
## 6.6 使用表连接替代 EXIST 子句（建议）
|
||
|
||
- 一般情况下，使用表连接替代 EXIST 子句可提高性能。
|
||
|
||
## 6.7 使用 EXIST 子句替代 DISTINCT 子句。（建议）
|
||
|
||
- 一般情况下，使用 EXIST 子句替代 DISTINCT 子句可提高性能。
|
||
|
||
## 6.8 慎用 with 子查询（建议）
|
||
|
||
- WITH语句（Common Table Expressions） CTEs 通用表达式，定义只在查询中存在临时表，占用一定的资源。
|
||
|
||
## 6.9 日期格式明确化 （建议）
|
||
|
||
- 日期格式应明确化。
|
||
|
||
## 6.10 列表查询（建议）
|
||
|
||
- 应当控制前台列表查询输出项个数，不要输出太多字段，需要查看更多字段时，从前台列表中选择单个记录，再显示详细信息，列表查询输出项所有字段所涉及表，建议控制在 3 个表以内，除 SELECT 字段列表内所涉及表外，其它表采用动态 SQL 方式。
|
||
|
||
## 6.11 增加查询过滤条件（建议）
|
||
|
||
- 建议添加时间、机构维度的 WHERE 过滤条件，例如：流水类数据、消息提醒等，提高查询性能。
|
||
- 在不改变查询结果的前提下，增加查询条件中的索引字段。没有索引字段的建议选择性大的字段放前面。
|
||
|
||
## 6.12 避免用*返回查询结果（强制）
|
||
|
||
- 任何地方都不要使用 SELECT * FROM t ,用具体的字段列表代表*，不要返回用不到的任何字段，另外表结构发生变化也容易出现问题。
|
||
|
||
## 6.13 避免使用 delete 全表（强制）
|
||
|
||
- 不要使用 DELETE 全表，性能很差，请使用 TRUNCATE 代替。
|
||
|
||
## 6.14 null 值的处理（强制）
|
||
|
||
- 注意 NULL 值的处理，对于数值计算，使用 COALESCE(value [, ...])函数为 NULL 值提供默认值 0。
|
||
|
||
## 6.15 表别名和列别名的命名（强制）
|
||
|
||
- 表别名，列别名命名时，尽量按照原表名和列名的省略缩写形式，保持 SQL 的可读性。
|
||
|
||
## 6.16 插入列明确指定（强制）
|
||
|
||
- 插入列时应明确指定列名。
|
||
|
||
## 6.17 查询事务（强制）
|
||
|
||
- 对于基于查询结果判断后续操作的，应在同一事务内，且查询使用 FOR UPDATE 进行查询加锁。
|
||
- 在查询事务中，应避免写复杂 SQL，可将复杂 SQL 拆分成多个小 SQL 来完成对应查询。
|
||
|
||
## 6.18 批量插入（建议）
|
||
|
||
- 批量插入可以显著减少与数据库的通信开销，提高数据库性能并降低事务处理的复杂性，INSERT INTO ... VALUES 语句可以一次插入多行数据。采用批量插入 SQL 语句时，单次建议不超过 1000 条。
|
||
|
||
## 6.19 多表联合查询（建议）
|
||
|
||
- 多表查询建议使用 JOIN 关键字进行多表关联查询，而不是使用子查询，要根据需求选择 JOIN 方式，并确保所有连接操作都有明确的连接条件，连接条件中的列有适当的索引，尽量控制关联的表数量在 3 个以内，同时建议将记录数较少的表放在前面，可以减少中间结果集的大小，从而提高查询效率。
|
||
- 对于多表关联查询时返回大量数据的情况，应通过定义合适的查询条件尽量控制返回结果集的大小，只获取需要的数据。
|
||
|
||
## 6.20 分页查询（建议）
|
||
|
||
- 应该尽量避免向客户端返回大数据量，若因业务需要获取大量数据时，建议不要一次性全量获取，而是分批次获取数据。每次获取的数据量应小于 500 条，结果集应小于 1M。
|
||
- 建议使用合理的分页方式以提高分页效率，分页时尽量避免直接使用 LIMIT OFFSET，否则可能数据越靠后，查询越慢，可以考虑使用游标分页或基于主键的分页，或者加上一些 WHERE 限制条件进行分页查询。
|
||
|
||
## 6.21 COMMENT 格式规范
|
||
|
||
### 6.21.1 表 COMMENT（建议）
|
||
|
||
- 数据库表应采用 Comment 进行注释。在注释中应明确表的中文名称。
|
||
- 语法：COMMENT ON TABLE table_name IS ‘string_literal’;
|
||
- string_literal 格式：表中文名
|
||
- string_literal 为必填项。
|
||
|
||
### 6.21.2 字段 COMMENT（建议）
|
||
|
||
- 数据库表的字段应采用Comment进行注释。在注释中应明确字段的中文名称、数据标准、数据质量等信息。
|
||
- 语法：COMMENT ON COLUMN table_name.column_name IS‘string_literal’;
|
||
- string_literal 格式：名称：字段中文名。标准：数据项编号|不贯标原因。质量：数据质量规则编号|无。
|
||
- string_literal 为必填项，包括字段名称、数据标准、数据质量三个必填部分，分别以“名称：”、“标准：”、“质量：”开头，以“。”结尾。其中标准部分，若已贯标，请填写引用的企业级数据字典数据项编号；若未贯标，请填写不贯标原因。质量部分，若匹配了企业级数据质量规则，请填写数据质量规则编号，多条规则间采用半角“,”分隔；若未匹配，请填写“无”。 |