部分数据库操作方式进行级别定制

本方案将对部分数据库操作方式进行级别定制，级别分别为：

禁用：不允许使用的数据库操作方式
限用：需要通过技术组讨论通过方可使用的操作规范
慎用：使用时需要以性能为优先考虑条件，在不会对性能产生较大影响的情况下方可考虑使用。
建议：在同等实现的情况下，需要优先考虑的实现方式
其它不在列的数据库一般增、删、改、查的操作方式暂无限制。

为了让应用程序运行得更快，有时候我们需要把部分操作交数据库去操作，但是如果没有正确地进行数据库操作，则应用程序中的 SQL 查询有可能不能按照预想的方式进行响应。要么不返回数据，要么耗费的时间长得出奇，降低了应用程序的响应速度，用户必须等待的时间过长，这样便会影响到系统用户的正常业务办理，此规范的定制目的就是为了规范SQL的使用，提高系统性能，更好地为业务部门服务。

1、 慎用 循环操作数据库
对于需要通过循环操作进行赋值的，在循环中慎用数据库操作语句进行操作，如果是大循环或者循环套循环的操作数据库Hibernate会带来与数据库的多次链接，影响了系统性能及数据库性能。对于大循环、循环套循环进行数据库操作的，建议考虑通过SQL实现一次交互实现。
对于批量更新、删除某一条件下的数据时，如业务目的已明确，建议采用SQL批量操作。例如要制某客户下的所有合同（或满足某一条件的合同）为终止状态，则没有必要先查找出一个列表的数据然后再遍历这个列表一个一个对象去循环更新状态，建议通过SQL直接实现。再如需要删除某一业务下的所有明细，亦无必要通过业务编号先找出业务明细的列表再一条一条去删除，建议通过SQL直接以业务编号为参数进行明细的删除。
当然，这些操作需要根据相应场景进行相应的考虑，但无论如何，大循环大对象的操作一定要慎重。
2、 慎用 返回VO的方法调用
对于程序中需要调用自己或其他模块的数据时，如果调用的方法返回的是VO，一定要慎重考虑，首先要看该VO返回的是否都是简单属性，如果不是，则看自己需要的内容是否仅是其中之一，其它无关信息的加载是否会产生大量的内存占用？如果是，则建议改用或新增方法实现数据的获取。
例如需要查找某集团的牵头行的信息，通过ICreditGroupBS. findCreditGroupByNumber查找到CreditGroupVO信息，再从里面获取牵头行的信息，这样的做法是不建议使用的。首先，该方法在组装该VO时，将该集团的管理团队信息及该集团下所有客户及所有客户的管理团队的信息都进行了加载，数据量很大。而如果只需要其中的牵头行的信息的话，其实可以通过ICustomerManagerTeamBS. findCustomerManagerTeam进行条件查询即可实现。且性能会有很大的提升。
因此对于大对象的查询，请慎用。
3、 限用 关键字段不加非空判断的多条件查询
目前系统中为了方便各种条件的数据查询，很多模块设置了INFO类的多条件查询，但是对于多条件查询，一定要在调用前对非空字段进行判断，例如要查找某客户的合同，查询条件包括客户编号、合同编号、业务编号、合同状态…….等，则对于查询条件不明确，例如客户编号和合同编号及业务编号等关键字段均为空，仅存在。则该查询查找出来的将是系统中的大量数据包括与该客户无关的合同信息，一来业务上存在风险，二来会带来性能的问题。这样的不进行非空字段判断的查询是限用的。对于DAO层，如果查询条件均为空应该返回空列表，对于需要进行全表操作的，单独新增方法进行数据库操作。
多条件查询对于关键字段在非常情况下需要通过技术组同意方可不加判断，其余应对关键字段增加非空判断。
对于多条件查询，查询结果是作为页面展现用的，建议进行分页操作。
4、 慎用 LEFT JOIN
LEFT JOIN将包含了两个关联查询的表中的第一个（左边）表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。但由于LEFT JOIN 比 INNER JOIN 消耗资源更多，所以如果可以重新编写查询语句使得该查询不使用 LEFT JOIN则可能会带来性能上的提高。但如果确实有需要使用LEFT JOIN进行数据库操作，则需要注意定义进行LEFT JOIN的字段不能为空，因为这样一来LEFT JOIN包含与 NULL（不存在）数据匹配的数据，消耗的资源非常多。因此在使用LEFT JOIN时必须在查询中调整数据的形态不能存在NULL，使之适应应用程序所要求，才能带来性能上的提升。

5、 限用 笛卡尔乘积（CROSS  JOIN）
在许多情况下，笛卡尔乘积 (CROSS JOIN)消耗的资源太多，从而无法高效使用。CROSS JOIN是简单地、不加任何约束条件地把表组合。CROSS JOIN后结果的行数是连接前两个表行数的乘积。如果对两个分别有好几千行的表进行连接，则结果是不可想象的。如果需要为所有的可能性都返回数据（两表的全表乘积），则笛卡尔乘积可能会非常有帮助。但是，在大多数方案中 INNER JOIN 的效率要高得多，建议通过INNER JOIN来实现。如果执行了 CROSS JOIN，然后使用 WHERE 子句、DISTINCT 或 GROUP BY 来筛选出大多数行，其对资源的消耗是非常大的。而对此如果通过使用 INNER JOIN 也可以获得同样的结果，而且效率高得多。CROSS JOIN应该限用，使用应得到技术组讨论认可方可使用。

6、 建议 使用内连接代替 内嵌视图、EXISTS (NOT EXISTS)、IN（NOT IN）子句
用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。
但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：
Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。里面关系到full table scan和range scan，这也就是使用EXISTS比使用IN通常查询速度快的原因。临时表会消耗一定资源，通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这相对于临时表节省了时间。但是如果通过内连接进行组装SQL进行查询，则效率会有更大的提升。
因此建议：在业务密集的SQL当中尽量不采用IN操作符，通过内连接来实现数据库查询操作在性能上能有大的提升。

7、 带条件的查询 建议 将可过滤大量不满足条件的数据的条件放在最后
以往的数据库SQL执行情况是从后往往前执行，虽然现在数据库在数据库操作已可以忽略数据库操作时查询条件的组装顺序，但是从良好的SQL语言编写习惯出发，建议：在执行带条件查询的语句时将可过滤大量不满足条件的数据的条件放在SQL最尾部。

8、 慎用 IS NULL 与 IS NOT NULL 字段使用索引
不能用null作为索引，任何包含null值的列都不要被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器必须慎用。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null

9、 慎用 NOT
我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and（与）、or（或）以及not（非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子：
... where not (status ='VALID')
如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例：
... where status <>'INVALID'
再看下面这个例子：
select * from employee where salary<>3000;
对这个查询，可以改写为不使用NOT：
select * from employee where salary<3000 or salary>3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。
因此应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

10、 慎用 比较不匹配的数据类型
比较不匹配的数据类型也是比较难于发现的性能问题之一。
注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。
select bank_name,address,city,state,zip
from banks
where account_number = 990354;
Oracle可以自动把where子句查询条件属性字段account_number对应的数据库值进行转换变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询（正确做法）就可以使用索引：
select bank_name,address,city,state,zip
from banks
where account_number ='990354';
特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一 次“全表扫描”。
因此一定要慎用比较不匹配的数据类型，特别是我们系统中目前存在的对VARCHAR2型数据作为查询条件时没有对条件值增加引号。
(经过测试,当account_numer列中的值为字符类型或者查询条件中的值“account_number ='字符型'”时，Oracle会自动转换并使用索引，但是如果列中的值为数值类型时出现了全表扫描:
Select * from corporation_customer cc where cc. party_id=1   使用了索引
Select * from corporation_customer cc where cc. party_id=’1’  使用了索引
Select * from corporation_customer cc where cc. customer_num=’1’  使用索引
Select * from corporation_customer cc where cc. customer_num=1  全表扫描
通过Hibernate: from CustomerBaseBO cc where cc. customerNum=1  报ORA-01722错
Select * from corporation_customer cc where cc. customer_num=W1 报ORA-00904错
Select * from corporation_customer cc where cc. customer_num=1E报ORA-00933错)

11、 建议 对于已知范围的查询尽量少用 or 、in
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20  进行相应的测试  测试结果：oracle已不存在该现象
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)  进行相应的测试  测试结果：oracle已不存在该现象
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。

12、 建议 尽量避免大事务操作，提高系统并发能力。尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理,是否有可优化的空间。在海量查询时尽量少用格式转换。

13、 建议 对字段的计算尽量少用
对字段的计算会引起全表扫描.
所以,能用:
select * from 表 where 字段=1
就不要用:
select * from 表 where 字段-1=0

14、 建议 使用索引来更快地遍历表。
a.缺省情况下建立的索引是非群集索引nonclustered index，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说：有大量重复值、且经常有范围查询（ > ,< ，> =,< =）和order by、group by发生的列，可考虑建立群集（聚集）索引clustered index；群集（聚集）索引确定表中数据的物理顺序。群集（聚集）索引类似于电话簿，后者按姓氏排列数据。由于群集（聚集）索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引）。但由于群集索引是一种通过键值的逻辑顺序来确定表中相应行的物理顺序的索引，其表中存储的数据按照索引的顺序存储,检索效率比普通索引高,但对数据新增/修改/删除的影响比较大,因此该索引请(限用).
b.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
c.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。

索引带来查询上的速度的大大提升,但索引也占用了额外的硬盘空间(当然现在一般硬盘空间不成问题),而且往表中插入新记录时索引也要随着更新这也需要一定时间.有些表如果经常insert,而较少select,就不用加索引了.不然每次写入数据都要重新改写索引,花费时间;这个视实际情况而定,通常情况下索引是必需的.

合理使用索引
索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。索引的使用要恰到好处，其使用原则如下：
●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。
●在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。
●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
●如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。

15、 具体的SQL语句在很多情况下需要结合实际的应用情况来写，但首先需要考虑的就是性能问题，任何情况下都不能以大量的性能消耗作为代价实现应用的处理。