oracle 数据库开发原则（转自求道的路上）
目前绝大多数的MIS系统，都是基于对数据库的交互式操作，开发人员大部分的工作都是直接或者间接与数据库打交道。作为开发人员，正确的使用数据库技术，不但可以提高数据库相关开发的效率，而且可以有效的减少投产运维阶段的工作量。本篇从笔者的实际工作体会入手，简单介绍几个Oracle相关开发原则。
1、 “Never Treate Database Like A blackbox”
“绝不要将数据库视为一个黑盒”是Oracle大师Thomas Kyte给所有数据库开发人员的一句忠告。作为一个开发人员，无论使用何种类型的数据库，都要遵守这个基本规则。
现实中，将数据库作为Blackbox的开发人员是很多的。很多开发人员将数据库就是作为一个数据留存的载体，通过标准化的SQL进行操作，不去对具体使用的数据库进行了解。
这个问题的原因是很多的。其中一个重要原因就是在开发人员的观念中，SQL语句都是通用标准的。只要输入相同描述SQL，数据库都能返回相同的结果。开发人员似乎不用去关注数据库本身。
这种想法其实是错误的。目前我们使用最多的关系型数据库系统，都是建立在关系数据库模型的基础之上。同时，行业中也有专门的SQL系列标准。流行的开源或者商用数据库都宣称支持标准SQL和关系模型。但是实际上，各个数据库产品在实现标准的过程中是有所取舍、有所个性化的。
比如在Oracle中，一个重要的特性就是“查询select不会被修改阻塞”，也就是任何时候都可以对数据表进行select，不在乎查询数据是否正在被修改。这个特性大大提升了Oracle数据库的并发能力。但是并不是每种RDBMS都是如此，如SQL Server或者SQLite等DBMS，就存在select被其他DML操作阻塞的情况。
另一个例子是关于锁定范围。如果要对一条记录进行修改，Oracle数据库只会对该数据行进行锁定（行锁机制）。而其他一些数据库进行锁定的范围就会有差异。比如，SQL Server就是基于对页page的锁定，而另一些DBMS甚至会对表级别进行锁定。
对这些数据库机制的了解，对我们进行开发的意义重大，可以帮助我们不犯根本性质的错误。比如，了解SQL Server中select会被DML操作阻塞，就要意识到SQL Server环境下事务Transaction是要及时的提交。
另一部分的机制了解就是SQL。SQL是一种描述性语言。同一个结果集合，使用不同的SQL描述，数据库都会生成不同的执行计划。不同的执行计划方式，意味着不同的效率。所以，书写一手“漂亮”的SQL是不容易的。需要开发人员了解优化器、SQL转换机制和执行计划。
所以，作为一个数据库相关的开发人员不是一件容易的事情。会写几句SQL，用用预定义函数绝不是数据库开发的全部。了解你的项目所使用的数据库，知道自己在做什么，是一个数据库开发人员所具备的基本素质。
2、 Be Responsible To Your SQL
对于一个初学者，SQL是一种描述性语言。你需要什么样子的数据，不管什么方法，只要能描述出来，理论上DBMS都可以返回正确的结果。但是，执行效率是有差异的。相同的结果集合，好SQL可能不到一秒钟就可以返回结果，坏SQL可能几个小时不能有结果。
我们经常在开发团队遇到的场景是这样的：开发人员书写了一个SQL，测试入一个很小数据集合，可以返回正确的结果。就直接提交测试投产了。投产之后，随着数据量的积累，原有SQL执行性能下降严重，最后很可能都执行不出结果。此时，开发团队已经解散，修改源代码的可能性很少，于是变成了DBA的噩梦。
这样的场景，本质问题就出现在开发阶段开发人员对SQL的责任缺失。开发人员应该对自己书写的SQL负责，在保证结果正确的前提下，不断优化SQL的书写。这样做的目的就是让SQL在生产环境下也可以正确的执行。
做到SQL负责，听起来是一个很抽象的概念。实际中，开发人员只需要从两个方面入手：
首先，看看自己书写SQL的执行计划。查看执行计划，评估执行计划不是DBA的专利。每个开发人员应该具有看懂SQL执行计划，辨别常见Join的方式操作，识别好坏的能力。能看清SQL的缺点，才有改造的动力。
第二就是时刻注意自己的SQL在做什么？听起来这条规则很有问题，SQL几种操作，我们都能够书写出来，难道还不知道自己的SQL在做什么吗？这里笔者要强调的是数据操作范围。修改一条记录的SQL和修改一千万条记录的SQL书写起来，形式可以是一样的。但是，这样的SQL执行时候的效果是天差地别。一次性修改一千万记录的SQL也许一瞬间就让数据库处在崩溃的边缘。所以，一条SQL语句写出来，开发人员一定要明白这个SQL处理的范畴是多大？一百条还是一百万条？如果可能出现百万条的可能，就一定要预先处理，设置一次操作的范围上限。避免由于一次性的操作带来的风险。
3、 Index or Not Index
这里说说Index索引这个老话题。在Oracle环境下，最常见的索引是B+平衡树索引。普通B树索引具有几个特征：
ü 始终维持平衡状态。从根节点到达所有叶子节点的距离相同；
ü 树节点包括三类：一个根节点、若干分支节点和若干的叶子节点；
ü 叶子节点之间，通过双向链表结构加以连接。可以方便的在叶子节点层进行导航；
ü 叶子节点上，保存着索引列键值和对应的行rowid；
ü 索引是一个单独的段结构segment，一个enable的索引是和数据表索引列实时对应；
在开发SQL的执行计划中，我们经常可以看到两种路径方式：Full Table Scan和Index Range Scan。这是数据表访问的两个代表性的方式。
ü Full Table Scan（全表扫描）：此种方式是对数据表数据的全部检索。首先，Oracle会从对象的数据字典中，获取到数据段头块的信息（文件号、块号）。找到头块之后，从头块信息中，获取到该数据表所有相关extents的信息和位置。之后依据extents的分配依次检索数据块。直到检索到HWM（高水位线）下。FTS方式有个特点：堆表结构下，会检索到所有分配给的数据块（无论是否有数据内容）。所以，FTS操作的效率是随着数据表的膨胀而变化的；
ü Index Range Scan（索引范围扫描）：对数据表的检索，并不直接入手数据表。而是从索引入手，通过读少量的索引块，获取定位到符合条件的叶子节点rowid列表。之后，直接借助rowid列表，就可以定位到符合条件的数据行。这种方式的特点是查询性能不随着数据表的胀大而发生变化。
通常情况下，索引路径是我们追求的优化方向。在CBO时代，索引路径因其少量的IO块读取成本通常小于FTS方式的。但是，并不是意味着所有SQL都会优化为索引路径。索引是有成本的，添加索引是会给select带来很多好处。但是，Oracle要维护数据索引列与索引树的同步结构。这也就意味着索引会带来DML操作的性能低下。所以，索引是一种有成本的优化手段，要统筹规划。
4、The Trap In Your Where Condition and Order/Group clause
我们进行的日常数据库开发，主要集中在DML操作类型，以select/update/insert/delete为核心。在这些类型操作，我们最需要关注的并不是select的结果集合列表，也不是insert/update的具体数值，而是定位操作对象的where条件和进行大规模计算操作的group/order。
Where条件的作用是让Oracle可以定位到我们需要进行检索处理的记录。Where条件的书写起到两个层面的作用，其一是描述了结果集合属性，另一个是间接影响到Oracle定位数据的方式，也就是执行计划。
SQL是一种描述性语言，我们只需要描述需要的数据属性就可以了。但是也就是这个特性，往往会让我们书写出很糟糕的SQL。同样的结果集合，不同的描述方式（SQL where条件），执行效果和执行计划可能会千差万别。
写好where条件的规则技巧有很多，比如in用exists替换、is null不选择等等，每种技巧都是基于特定的应用场景和内部背景。这里列举一个对条件列不要轻易处理的例子。
如果我们在where条件中书写一些表达式，通常Oracle在SQL预处理前就会将表达式进行处理。但是，对于携带数据列的条件表达式，这种改写变化是不会越过等号的。下面进行试验。
SQL> create table t as select * from dba_objects;
Table created
SQL> create index idx_t_id on t(object_id);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
选择在数据表t的object_id列添加索引。两个SQL含义等价，但是执行计划完全不同。
SQL> explain plan for select * from t whereobject_id=999+1;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 514881935
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 2 (0)| 00
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 93 | 2 (0)| 00
|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
14 rows selected
SQL> explain plan for select * from t whereobject_id-1=999;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 513 | 47709 | 160 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 513 | 47709 | 160 (3)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"-1=999)
13 rows selected
相同的含义SQL，只是进行简单的算术移向操作，就有如此大的执行计划成本差异。在第一个SQL中，Oracle自动将运算完成后走索引路径。而第二个SQL因为减一操作在object_id同侧，视为将object_id处理的表达式。表达式是不会选择普通索引路径的，只有创建特定的函数索引。
此类问题还可以引申到对列进行函数处理或者表达式处理，这样的SQL语句非常容易出现在where条件中。作为一名开发人员，应该尽可能的消除这样的SQL语句，增加SQL优化的概率。
Group by/order也是同样的问题。在Oracle中，分组操作和排序操作是相当“昂贵”的。当进行group by/order的时候，对应的数据集合需要存放在PGA的专门区域中进行。这种操作消耗PGA甚至临时Temp表空间空间，同时也会消耗一定的CPU资源。所以，如果没有明确的需求，我们尽量少用这两种类型操作。
Oracle开发人员平时听到最多的数据库SQL技巧恐怕就是绑定变量SQL的书写了。使用绑定变量的原因简单的说，就是为了增加SQL共享游标的共享概率，减少硬解析hard parse。
对Oracle来说，内存的缓存cache技术是贯穿在整个体系框架中的。当一个新的SQL语句输入时，要经历语法、语义和权限等一系列检查，之后要进行parse过程。如果在SGA的Library Cache中没有能找到，就会自己生成该SQL的执行计划和共享游标，这个过程要消耗SGA空间和CPU成本，同时还会带来一定数量的library Cache Latch和Library Cache Pin。进行执行计划生成之后，该SQL以shared cursor的形式缓存在library cache中，等待再次被使用。这个过程被称为hard parse，硬解析。
当library cache中存在该SQL的执行计划时，另一次SQL调用输入。如果新SQL与原来的SQL字面值和其他一些参数相同，就存在游标共享的可能。这样，新SQL不需要进行SGA空间分配和执行计划生成，会使用原有的执行计划。这个过程我们称之为Soft Parse。
无论是Oracle自身的SQL和PL/SQL，还是Java/.NET的接口语言，都存在使用绑定变量的接口API。使用绑定变量可以增加SQL出现soft parse的几率，增加数据库并行性。
最后，我们谈一下绑定变量的适用环境。并不是什么样的场景使用绑定变量都是没有问题的，在OLTP这类事务并发和事务密集型的系统中，使用绑定变量会提高系统整体并发能力。但是在OLAP和DSS类的系统中，一个SQL执行次数很少，但是执行时间很长，这样场景下使用绑定变量的意义就不大。
此外，使用绑定变量存在出现bind peeking的可能性，这方面的性能抖动问题也不能忽视。
6、Prioritize your Use Cases for Tunning
最后说说性能优化。系统从业务需求分析、设计、开发到投产运维，性能分析优化是贯穿整个生命周期的。性能分析优化手段越是往前规划，我们可以使用的优化选择手段就越多，性价比就越好。传统意义上的SQL调优，都是谈在投产运维阶段进行的DBA运维调优。在运维阶段进行的手段很有限，而且收效往往很低。
在开发阶段，我们进行优化的方式主要是SQL改写和索引选择。大多数性能优化手段都是需要付出额外的成本。比如索引，建立索引的确可以获取很好的select效率，但是另一方面要付出update/insert/delete成本，而且索引本身也是要有空间占用和管理成本。所以，我们追求的优化，实际上就是最优性价比的优化。
那么，面对诸多的需求场景，我们如何选择呢？笔者以为：所谓关键用例确定架构，关键用例同样决定优化策略方向。我们面对的需求不可能是相同优先级别的，对用户而言，必然存在轻重缓急。我们开发系统的目标是实现用户的愿景，实现用户目标的最大化。但是，用户的目标实现是不可能完全实现。在“质量-工期-成本”三角形的控制下，必然有需求会被裁减。我们追求的目标就是将优先的优化资源分配给尽可能高优先级别的用例需求中。
举一个例子，两个数据列都有加索引优化的需求，但是资源限制下只能加一个索引。一个用例是在界面上显示系统参数，这个界面对应SQL如果是全表扫描，要多消耗5秒钟。但是该用例很少有人用，每年只会打开一次。另一个用例是每日的Daily Job，每天都会运行成百上千次的SQL。经过详细分析，用例的优先级别立刻可以看出来了，优化方案自然也就出来了。
7、结论
本系列集中介绍了开发阶段我们需要关注的技术和原则，大部分的技巧是思路和指导原则，希望对读者有所帮助。
