sql开发规范

SQL编写规约

SQL编写总原则

声明：
在没有把握或者根本没意识时，遵守规约可以保障你的程序不会“越界”。
所以规约并不代表一种最优方案或者某种教条。
当你相当有把握，明确知道自己在做什么的时候，请读者使用自己以为合适的SQL，且时刻准备为你的SQL负责。

无论是单表查询还是多表联查，不许有大结果集的操作。
无论是单表查询还是多表联查，小心使用排序等耗性能的语句。
例如：order by , group by , distinct , union等
无论是单表查询还是多表联查，查看执行计划，确认能用索引的地方必须走到索引。
开发和测试库的数据量有限，开发和测试过程中，无论功能实现还是执行计划，必须考虑生产库庞大的数据量。
需求可以妥协，修改，拆分重构。性能有一条红线不能逾越。
powered by wanglifeng https://www.cnblogs.com/wanglifeng717

强制类规约

【强制】禁止使用select * ，需要什么字段，显式表示出来。

错误写法：select * from table;

正确写法：select t.id, t.name,t.age from table1 t;

【强制】禁止使用不加约束条件的查询，必须有where过滤条件（除非明确现在及未来数据量级都很小可控）

错误写法：select t.id from table1 t;

正确写法：select t.id from table1 t where t.id<=30;

正确写法：select t.id from table1 t where t.id<=30 and rownum<50;(如id<30的量也很多)

正确写法：select t.id from table1 t where rownum<50;(oracle推荐)

正确写法：select t.id from table1 t where limit 50;(mysql推荐)

【强制】insert语句必须明确指定列名。

错误写法：insert into tbl_pay_order_info values(101803309908713,101803309983716,91);

正确写法：insert into tbl_pay_order_info (main_orderid,pay_orderid,pay_channel) values (1018033099483713,1018033099083716,91);

【强制】组合索引的顺序强制等值查询列在前，范围查找列在后
错误写法：create index idx_t on t(age,gender)

Select t.name,t.address,t.age from t where t.age>20 and t.gender=’boy’

正确写法：create index idx_t on t(gender,age)

Select t.name,t.address,t.age from t where t.age>20 and t.gender=’boy’

例如：对于如下组合索引，各种查询写法效率

CREATE INDEX B_C_D_IDX ON DATA_TABLE(B,C,D);

【强制】批量更新语句的事务不能太大。逐条commit不合适，更新1000条以上commit一次也不合适，推荐500左右

【强制】禁止不加过滤条件的全表数据排序。

尽量减少order by和group by排序操作,distinct排重也会涉及到排序,有些排序操作可以由应用程序完成。如必须使用排序操作,请遵循如下规则:

1.排序列上有索引

2.如结果集不需要唯一,使用union all代替union

3.避免大结果集的排序，用where条件限制

错误写法：select * from t order by t.pay_order_id;

正确写法：select * from t where t.pay_order_id<101803300910017574 order by t.pay_order_id;【*注】--至少要过滤成小结果集

【强制】oracle 的rownum和order by的优先级，要分情况。执行结果，取决于执行计划。

强制用括号分层，区分优先级

有隐患写法：

select * from s where rownum<4 order by id ;--局部排序结果table full scan

select id from s where rownum<4 order by id ;--执行计划index full scan全局排序结果

正确写法：

select id from(select id from s order by id ) where rownum<4;--全局排序结果。【*注】实际运用，内层结果集不能太大，加where条件限制，且要利用索引抵消order by的开销。

实例：批上送平时每天运行没问题，中断一段时间，量积累大了出现问题

select MAIN_ORDER_ID,CREATE_TIME from (

SELECT ROWNUM r, MAIN_ORDER_ID,CREATE_TIME

FROM TBL_SPTCC_TRANSACTION

WHERE ROWNUM <= 1000

and CARD_SEQ_NO = -1

ORDER BY CREATE_TIME )

where r > 0

【*注】这个语句先拿1000条，后进行排序，局部排序结果。

【强制】超过三个表慎用join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引；即使双表 join 也要注意表索引、SQL 性能；跨表联查必须添加where条件筛选控制结果集，结果集不能太大

错误写法：select t.id t.name from t,n where t.id = n.id;

正确写法：select t.id t.name from t,n where t.id = n.id and t.status=3;

正确写法：select t.id t.name from t,n where t.id = n.id and t.status=3 and rownum<50;

(有可能status=3的条数依然很多，oracle用rownum控制,mysql用limit)

正确写法：select t.id t.name from t,n where t.id = n.id and rownum<50;

【强制】严禁使用左模糊或者全模糊。

错误写法：select * from table t where t.name like '%df%'

正确写法：1.改需求

2. select * from table t where t.name like 'cdf%'

【强制】大数据量的表，必须分页拿数据，禁止全量拿数据，一次500-1000条合适

【强制】oracle经典三层select分页语句，如果数据量大的时候慎用，越往后分页越慢。（开发测试库数据量不足体现不出来）

最内层建议：最好可以加where条件控制结果集

SELECT *

FROM (SELECT TA.*, ROWNUM ROW_NUM

FROM (select UUID, pay_order_id

from t

order by pay_order_id) TA

WHERE ROWNUM <= 100)

WHERE ROW_NUM > 0;

【强制】不要使用 count(列名)或 count(常量)来替代 count(*)，count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

【强制】多表连接时,使用表的别名来引用列

示例：Select s.name, s.age ,t.address,t.phone from student s ,teacher t where s.teacher_id=t.id and t.age=21

【强制】尽量避免对索引列进行计算,或者对列进行函数操作然后查询（会使索引失效）

错误写法：**select t.id from test t where upper(t.nick) = :1

正确写法: select t.id from test t where t.nick = upper(:1)

【强制】更新时，如果涉及状态机的操作，必须加上前置状态校验，防止多线程导致的状态更新错乱(乐观锁机制)。

错误写法：

1.select t.status from t where t.id=1; --第一步校验id=1的记录状态是否为2，如果为2则更新状态机 status=3

2.update t set t.status=3 where t.id=1; --多线程的情况下，可能status已经被修改了。

正确写法：

1.select t.status from t where t.id=1;

2.update t set t.status=3 where t.id=1 and t.status=2; --加入前置状态校验

【强制】建表语句，如业务字段不为空，或者该字段要作为查询条件，将来要建立索引，则必须添加非空约束。

【强制】如果表有分区，where条件必须带上分区条件

例如：tbl_pay_order_info按照create_time时间进行了分区，则必须加where create_time限制条件。

【强制】对于频繁甚至大并发访问的表，无论数据量多少，哪怕只有2条数据，都需要根据相关SQL建立索引。

【强制】跨表联查，如果无业务需求，禁止使用left/right join，推荐使用where条件形式。

参考类规约

【参考】【*注】开发禁用。用truncate 代替delete 注意：truncate 无法回滚,且无法使用where条件。仅适用于大量清理数据。

delete 比较慢，耗性能，日志非常多，产生空间碎片。尤其大量数据删除时，尤其明显

参考：delete from t

参考：truncate table t

【参考】函数索引场景适合可以使用，性能肯定比B+树索引低一点，但查询性能提升了，需要平衡。

函数索引在索引块中保存的是函数的计算结果,固化函数计算结果，提升性能。

select ename, hiredate

from emp

where my_soundex(ename) = my_soundex('Kings')

如果没有函数索引，假如有 n 行，会调用 my_soundex 函数 n 次。如果有，则只需 1 次。

另外一个应用，在某些行上面建索引而忽略其他行，以节省空间。某些情景下可代替位图索引，比位图索引有更好的并发行，而且空间也小。例如processed_flag只有两个值，大部分都是’Y’,少部分是’N’,为了快速找到N。

建表
create table z
(id number primary key,
processed_flag varchar2(1),
payload varchar2(20)
)
processed_flag 只有两种值 'Y' or 'N'
只需要对该列的'N'值建索引
create index t_idx on z( decode(processed_flag, 'N', 'N'))
因为decode语法 DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )
当值为'N'时返回'N'，非'N'时返回NULL，而B*树索引不为NULL值做键。

【参考】 在一个表中剩余的库存中获取一个可用的库存

SELECT * FROM tbl_bts_goods_coupon WHERE STATUS =1;
上述这种写法当并发时，大家取到的可能是同一条库存，最终事务提交时导致只有一条成功。

此时应从设计上避免而不应靠数据库，如果确实靠数据库，下面提供一种sql参考，核心是在合法状态的库存集合中随机获取一个。

本文来自博客园，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/15846677.html

posted @ 2022-01-26 16:19 王李峰阅读(492) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

wanglifeng

sql开发规范

强制类规约

推荐类规约

参考类规约

公告