SQL编码规范
1 综述
1.1 概述
本文主要描述了在开发过程中编写SQL语句的各种规范。
本文主要面向读者为 业务设计人员、程序设计人员、程序开发人员。
1.2 使用范围
本项目,以及使用本项目框架的后续项目。
2 SQL语句格式
2.1 SQL基本格式要求
2.1.1 全大写
SQL 关键字应保持全大写,默认SQL中不添加注释。
2.1.2 换行
保持每行仅一个字段和每行仅一个条件。
保持子句关键字右对齐(SELECT FROM WHERE ORDER GROUP)
2.1.3 嵌套缩进
嵌套的SQL子句应缩进2 字符并保持对齐。
2.2 SQL关联方式
默认使用 INNER JOIN 和 LEFT JOIN,尽量避免使用OUTER JOIN ,FULL JOIN 及 RIGHT JOIN。(此处因考虑数据库兼容性,使用标准SQL语法。实际使用时,如果仅使用ORACLE数据库,建议使用ORACLE语法,以提高效率和可读性)
2.2.1 建议关联表不超过3个
超过3个表关联的SQL,要向上反馈,尽量从业务和编码上避免3表以上关联,特别是对于千万级的表。
2.2.2 表关联字段必须有索引
写sql注意,表关联字段必须加索引,如果没有,要向上反馈,添加合适索引。
2.2.3 LEFT JOIN
LEFT JOIN 示例格式如下。
对于LEFT JOIN ,因为设置左表为主表,在LEFT JOIN 的 ON中添加条件,是在关联前进行过滤。
WHERE 条件中添加的子表条件,会对整体关联后的结果做过滤。 所以如果关联不到的记录,在关联后子表所有字段值为空,再添加条件会将所有记录过滤掉。 因此,禁止在WHERE 子句中添加子表条件,除非能保证结果正确。
以上示例中,WHERE 子句的 APFD.STATUS=‘2’ 应添加在ON 子句中。
2.2.4 INNER JOIN
INNER JOIN示例格式如下。
INNER JOIN 为全关联。 仍然建议条件添加在ON 子句中,以优化性能。
2.2.5 其他关联
建议不使用其他关联。 有特殊业务需求时,可以使用,但应明确了解查询结果的匹配,保证结果正确,并对SQL进行适当的优化。
2.3 别名规范
在SQL 查询中,所有表名应使用别名。 字段名尽量避免使用别名。 使用别名时,不使用 AS 关键字进行分隔。
例如:
2.3.1 表别名
表别名默认以”_” 分隔表名,每部分取首字母,例如 ABS_PROJECT_FLOW 别名为 APF, ABS_PROJECT_FLOW_DICT别名为APFD。
特殊情况下,如果表别名重复或单表关联的情况下,可使用字母或数字区分别名。
尽量避免简单粗暴的使用 a、b、c类似的纯字母命令。
2.3.2 字段别名
默认不使用字段别名。
对结果列运算或多表关联后重复的列,需要使用别名的,可以考虑使用运算前的列名作为别名,重复的,可以添加数字后缀作为别名。
非必要情况,应尽量避免对列结果进行运算。(减少SQL语句对数据库兼容性的约束,方便代码移植)
2.4 参数名
参数名应与列名保持一致,对同一列做多个限制的,可以根据限制的类别,例如MIN MAX等,添加MIN MAX后缀。
3 SQL性能优化要求
3.1 子查询
禁止在 SELECT 子句中使用任何子查询。
尽量避免在WHERE 条件值中使用子查询(根据条件而定,使用IN和EXISTS子句不可避免 )
尽量使用表关联完成功能。
3.2 SELECT子句
默认SELECT 子句中不允许使用 “*” 。
Count函数中根据结果需求可选择使用 “”。(使用列为空时计算该列, 使用 列名等,如果该列为 null 则不计算。)
通用分页方法中使用 “*” 基本不可避免,不考虑。
3.3 分组查询优化
WHERE子句替换HAVING子句。
3.4 避免隐式转换
在使用纯数字常量时,根据数据库字段的类型,数据库会自动转换数字类型为数字或字符。 实际写SQL 时,应避免此类情况发生。
在SQL中,所有数字字符应以 ‘1’这样的形式出现。 纯数字应只匹配NUMBER类型。
DATE‘2017-02-23’ 语法等于 TO_DATE(‘2017-02-23’,’YYYY-MM-DD’)
3.5 运算添加在常量端
严格禁止对WHERE 子句中的列所任何运算。 应将运算添加在条件常量或参数一端。
例如:
3.6 EXISTS代替IN
使用EXISTS语法代替IN语法以提高性能。
3.7 NOT EXISTS代替NOT IN
语法同上。
3.8 表连接替换EXISTS
3.9 用EXISTS替换DISTINCT
3.10 注意索引列
设计时应根据数据查询频度设计表索引。 查询时应适当注意索引类型,以优化查询效率。(索引应尽量在设计数据库表时根据数据量和查询特性确定,当然后期仍然可以添加)
注意: 模糊查询的前置 “%” 会使索引失效。 必须支持全模糊查询的,目前没有更好的优化方法。
禁止在索引列上使用计算列。(同3.5,但严格禁止)
3.11 索引列上禁止使用IS NULL和IS NOT NULL
在索引列上使用IS NULL 和IS NOT NULL 将使索引失效。 应在设计时,对可以规避允许为空的列进行规避,设定默认值。
3.12 使用UNION-ALL和UNION
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序。
如果用UNION ALL替代UNION,这样排序就不是必要了,效率就会因此得到提高。
需要注意的是,UNION ALL将重复输出两个结果集合中相同记录,因此还是要从业务需求分析使用UNION ALL的可行性。
关于索引下列经验请参考:
如果检索数据量超过30%的表中记录数,使用索引将没有显著的效率提高;
在特定情况下,使用索引也许会比全表扫描慢,但这是同一个数量级上的差距;而通常情况下,使用索引比全表扫描要快几倍乃至几千倍!