SQL脚本编写注意事项【不定时补充】

时间:2022-12-18 17:16:14

-------分界线--------

To myself : 在写sql脚本老是会忘记一些脚本编写规范与技巧,导致脚本健壮性并不强,每每说要及时总结、吸取经验,可是工作一忙起来转眼就会忘,所以强制给自己定个时间来整理这方面的东东,算是立个flag吧@_@

1. IN 与 EXISTS 的区别

① IN 适用于子表数据量较小的子查询;EXISTS 适用于子表数据量较大的子查询;

eg: 主表A,n条记录;子表B,m条记录;

a. select * from A where A.id in (select B.id from B );

in()只执行一次,它先把子查询 (select B.id from B ) 的结果集找出来放在缓存,然后再检查判断主表A.id是否与B.id相等,如果相等则把记录添加进结果集,直到遍历完主表A;

所以,如果子表B的数据量很大,它会先遍历子表B,再遍历主表A,至多遍历n*m次, 不仅耗费资源,且效率差;

b. select * from A where A.id exists (select 1 from B.id = A.id);

与 a 不同的是,EXISTS()会执行n次(A有n条记录),但子查询结果集并不重要,也不会保存在缓存,它只是验证子表B中是否有该记录,如果该记录存在返回true,没有则false。

所以,如果子表B数据量很大,远大于主表A,适合使用EXISTS(),因为它没有遍历操作,只需要再执行一次查询就行。

2. WHERE条件中使用LIKE ,可以走索引,前提是不使用 like '%..%' 格式;

--参考https://jeffkemponoracle.com/2008/01/17/like-with-wildcard-at-start-can-use-an-index/

① 对于 like '..%' (以 % 结尾),可以走colunm上的index;

② 对于 like '..%..' (不以 % 开头,均以常量结尾),Oracle可以应用 colunm上的index;

③ 对于 like '%...' 的 (不以 % 结尾,以常量结尾),可以利用 reverse + function index 的形式,变化成 like '..%' 代码

eg:create index xxx on table(table.xx); ---> create index xxx on table(reverse(table.xx));

      reverse函数:可以实现将一个对象反向转换;

      eg:select reverse('abc') from dual;--> 'cba'

3. SELECT 字句中避免使用 '*'

    ORACLE在解析的过程中, 会将 '*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

4. 避免在字段上使用函数

    在字段上使用函数,在查询时不会使用该字段的索引。

    如where to_char(sqsj,’yyyymmdd’)>=’20150101’,将不会使用索引。

5. 尽量避免使用 IS NULL IS NOT NULL操作

    IS NULL和IS NOT NULL不会使用索引。

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值,通常称为UNKNOWN或空值,即是未知的,不确定的。

  由于NULL存在无数可能,因此NUll值也不等于NULL值,所以与NULL值相关的操作同样都是NULL值。

  正是基于这样一个特性,对于值列上的B树索引导致了is null/is not null不走索引的情形,下面描述了NULL值与索引以及索引NULL列上的执

  行计划,如何使得NULL值走索引的情形。