SQL编写规范

 

、数据库开发建议

SQL语句编写规则

1．查询语句的使用原则

（1）索引的正确使用

合理的使用正确的索引是提高系统执行效率的关键因素，对索引的使用需要注意以下一些问题：

①过度索引

一般情况下，使用索引可以缩短查询语句的执行时间，提高系统的执行效率，但是要避免以下两种过度索引的情况出现：

1. 对一个表建立了过多的索引，从而造成维护索引所需要的时间超过使用索引所降低的时间，从而造成整个系统效率的下降，这一般发生在对一些进行大量更新的表上面。因此一个联机表上的索引，最多不要超过5个。      
2. 由于索引数据的区分度不够，造成了使用索引而引起的效率的下降，这一般发生在对数据进行大的统计分析的时候。可以通过指定全表扫描等提示(hint)来避免。

②LIKE运算符

在应用程序中为了使用方便，对字符型变量进行比较时经常使用LIKE运算符进行字符模式的匹配。

需要注意的是对于LIKE运算，如果通配符%出现在字符串的尾部或中间，LIKE运算符将可以使用索引进行字符串的匹配，否则如果通配符%出现在字符串的开始，则LIKE必须使用全表扫描的方式去匹配字符串，这将产生较大的系统负荷。

一般情况下，为了提高系统的效率，我们希望用户能够在通配符的左端提供较多的数据信息以降低查询的数量。

③NULL值

NULL值是系统中目前尚无法确定的值，在Oracle数据库系统中NULL是一个比所有的确定值都大的值，然而又不能用大于小于等于运算符来比较，对NULL值的处理只能用是与否来判定，所有的对NULL值的判定都会引起全表扫描，除非同时使用其它的查询条件。

• 复合索引

复合索引是使用多个数据列的索引，第一个字段的数据区分度非常重要，也是影响一个联合索引效率的关键所在。改写查询语句：

①关联子查询与非关联子查询

对于一个关联子查询，子查询是每行外部查询的记录都要计算一次，然而对于一个非关联子查询，子查询只会执行一次，而且结果集被保存在内存中。

因此，通常在外部查询返回相对较少的记录时，关联子查询比非关联子查询执行得更快；而子查询中只有少量的记录的时候，则非关联子查询将会比关联子查询执行得更快。

②尽量用相同的数据类型的数据进行比较，以避免发生数据转换。

SQL语言对于数据类型不像JAVA和C++那样进行严格的数据类型检查，不同种数据间可以进行某些运算，但是在做数据操作时需要数据库进行隐含的类型转换，在大数据量的查询中，由于要对每一个数据项做同样的操作，会造成时间和CPU处理能力的浪费。

实际应用中通常发生的隐含的数据类型的转换有：

1. 字符型到数字型的转换，如：SELECT ‘1234’ +3  FROM DUAL等
2. 数字型到字符型的转换，如：UPDATE DEPT SET EMPNO=5678等
3. 日期型到字符型的转换，如：UPDATE EMP SET DNAME=SYSDATE等

上述的转换都是隐含发生的，在实际使用中要避免使用不同类型的数据操作。

减少排序的发生：

排序是数据库中执行频度比较大的一种操作，根据排序执行的范围不同又可以分为内排序和外排序。我们希望数据库中的排序操作的数量能够被尽量的减少同时每个排序的时间能够缩短。为此我们可以：

• 使用UNION ALL来代替UNION。
• 添加索引。在表连接的时候使用索引可以避免排序的发生，比如添加了合适的索引，可以使连接方式由排序合并连接(Sort Merge Join)转变为索引的嵌套循环连接(Indexed Nestted Loop Join)。
• 在DISTINCT，GROUP BY，ORDER BY子句涉及到的列上创建索引。
• 使用较大SORT_AREA_SIZE
• 在用户的临时表空间上使用大的extent大小。

使用并行查询：

并行查询适合下列情况：

• 全表扫描的查询语句。
• 返回大数据量的查询所改造的语句。
• 其它一些数据操作中的查询子句。

对于较大的数据量的查询，我们可以使用提示(hint)来强制数据库使用并行查询，在Oracle数据库中，并行查询的优先级为语句提示(hint)，表的定义，数据库初始化参数。

减少死锁的发生：

在Oracle数据库中大量的数据库的锁都是行级锁，不同的会话间竞争同一条记录的可能性较小，同时Oracle数据库中提供了自动的死锁检测机制来避免数据库的死锁，保证数据库系统的可用性。因此一般情况下应用系统不需要特殊的设计来解决系统的死锁问题，但是在下列情况下系统可能出现死锁：

• 表A上的列n上有一个索引，表B上的列m使用A上的列n作为外键，然后表A的列n上的索引被删除，此时更新表B上列m将造成对表A的表级锁，会导致死锁的发生。
• 应用大量的使用SELECT ……FOR UPDATE语句造成系统不必要的加锁。

对于第一种情况要对出现死锁的相关表进行检查，确认是否相关索引被错误的删除。对于第二种情况要修改应用，避免对数据的不必要的加锁。

集合运算符的使用：

Oracle数据库的集合运算包括: UNION， UNION ALL， INTERSECT和MINUS操作。

一般情况下当两个集合中的数据都比较多时，集合运算都是比较耗时的操作，使用时需要谨慎小心。如果可能，可以使用UNION ALL操作代替UNION操作。

（2）使用连接方式的原则

嵌套循环连接（NESTED LOOP JOIN）：

知识点描述

嵌套循环连接操作关系到两个表，一个内部表和一个外部表。Oracle比较内部数据集的每一条记录和外部数据集的每一条记录，并返回满足条件的记录。

嵌套循环连接通常会产生巨大的数据量，所以对嵌套循环连接的使用要有严格的限制。

当一个嵌套循环连接的内部表中存在索引的情况，嵌套循环连接变为改进的有索引的嵌套循环连接(INDEXED NESTED LOOP JOIN)，通常有索引的嵌套循环连接在产生较小的数据量的情况下可以较快的执行。

在使用有索引的嵌套循环连接是必须确保在查询中使用了正确的驱动表和正确的驱动数据集，通常情况下我们使用包含数据量较小的表作为驱动表。

一般如果我们使用基于成本的优化器，系统会自动选择驱动表，如果是使用基于规则的优化器，则后表作为驱动表。

应用原则

一般的嵌套循环连接的速度较慢，产生的数据量较大，应该严格控制其使用。

在使用有索引的嵌套循环连接时，必须保证其驱动表有合适的索引，最好为主键或唯一键，同时希望在另外一张表在相同的列上有索引。

散列连接(Hash Join)：

知识点描述

散列连接将驱动表加载进内存，使用散列技术将驱动表与较大的表进行连接，连接过程中，对大表的访问使用了散列访问。散列连接可以提高等连接的速度。

如果可用的散列空间内存足够大，可以加载构建输入，那么散列连接算法能够很好地运行简单的散列连接，但是并不需要将整个输入放入hash_area_size内存。如果散列连接中较小的驱动表无法放入hash_area_size，那么Oracle将拆分该散列连接，并使用temp表空间中的临时段来管理这个溢出。

Oracle推荐将驱动表的hash_area_size设置为驱动表字节总数的1.6倍。

应用原则

一般的散列连接发生在一个大表和一个小表做连接的时候，此时小表中的数据全部被读入内存，其处理的速度较快。

排序合并连接(Sort Merge Join)：

知识点描述

排序合并连接是指从目标表中读取两个记录数据集，并使用连接字段将两个记录集分别排序的操作。合并过程将来自一个数据集的每一条记录同来自另一个数据集与之匹配的记录相连接，并返回记录数据集的交集。

排序合并连接有几种不同的排序方法：外部合并连接，反合并连接和半合并连接。这些不同的排列方法使得合并步骤服务于不同的目的，可以是找到记录数据集的交集，也可以是找到满足SQL语句中WHERE子句条件的那些记录。

应用原则

一般的排序合并连接是在散列连接不能达到应用的要求或Oracle优化器认为排序合并连接效率更高的情况下使用。在下述的条件下排序合并连接被使用：

1. 数据表之间的连接不是等值连接而是其它连接
2. 数据库使用的优化模式是基于RBO而不是CBO

（3）进行复杂查询的原则

限制表连接操作所涉及的表的个数：

对于数据库的连接操作操作，我们可以简单的将其想象为一个循环匹配的过程，每一次匹配相当于一次循环，每一个连接相当于一层循环，则N个表的连接操作就相当于一个N-1层的循环嵌套。

一般的情况下在数据库的查询中涉及的数据表越多，则其查询的执行计划就越复杂，其执行的效率就越低，为此我们需要尽可能的限制参与连接的表的数量。

①3-5个表的处理方法

对于较少的数据表的连接操作，需要合理的确定连接的驱动表，从某种意义上说，确定合理的驱动表就是确定多层循环嵌套中的最外层的循环，可以最大限度的提高连接操作的效率，可见选择合适的驱动表的重要性。

RBO模式下，在SQL语句中FROM子句后面的表就是我们要进行连接操作的数据表，Oracle 按照从右到左的顺序处理这些表，让它们轮流作为驱动表去参加连接操作，这样我们可以把包含参与连接的数据量最少的表放在FROM子句的最右端，按照从右到左的顺序依次增加表中参与连接数据的量。

CBO模式下，则不需要考虑表放置的位置。

②5个表以上的处理方法

对于涉及较多的表(>5+)的数据连接查询，其查询的复杂度迅速增加，其连接的存取路径的变化更大，存取路径的个数与连接的表的个数的阶乘有关：当n=5时存取路径＝1X2X3X4X5=120个，而当连接的表的个数为6时存取路径变为1X2X3X4X5X6=720个，数据库优化器对于数据的存取路径的判断近乎为不可能，