ORACLE数据库应用开发的三十个注意事项_Oracle

引言

笔者及所在团队从2000年开始的CRM等系统开发，一直主要使用ORACLE数据库作为应用数据库，开发方式包括使用PLSQL编写存储过程/数据库函数/触发器、使用ODBC或OCI和ProC开发C++应用、使用JDBC开发Java应用、使用tuxdeo开发中间件应用等。这些应用开发笔者所在团队自己做过，也委托华为、亚信、思特奇等国内厂商合作做过，整体来说ORACLE数据库功能强大、性能出众、系统健壮，确实是OLTP联机事务处理的最受欢迎的数据库。

因ORACLE服务费居高不下、加上最近几年美国的操弄打压，国产数据库也走出了一条自己的路，因此数据库国产化也越来越被提上日程，也有部分应用走出了成功之路，但众多传统应用进行国产数据库的改造需要大量投入，也需要一个逐步试点及改造的过程，因此ORACLE仍然是国内众多单位持续应用的选择。

今天老猿结合二十余年的ORACLE数据库应用开发和运维的经验教训，总结在使用ORACLE数据库环境中的应用开发中需要注意的一些注意事项，这些问题不但可以作为ORACLE数据库开发的注意事项，大多数也适用于常见的关系型数据库开发甚至非关系型数据开发。

实际上，在数据库应用开发上，开发和维护关联度是非常大的，好的开发设计会给维护带来极大方便。因此虽然维护关注的角度和开发有所不同，但在部分内容上二者是统一的。

禁忌1：触发器代码忌复杂

数据库触发器由于可以基于表级进行所有应用或手工DML操作数据增删改查的前向或后向处理，易于收敛逻辑，使用方便，容易受到众多开发人员的喜爱。

但在使用上触发器与操作数据的事务处于同一个事务，因此比较适合简单处理逻辑，切忌不能在触发器上使用复杂逻辑，一般推荐在10行左右代码比较适合，否则容易导致事务处理出现问题。

如果一定要通过触发器进行复杂逻辑处理，最好的做法是通过触发器将需要处理的数据写入到单独的任务表中，然后使用单独进程对任务表数据进行处理。这样能使得触发器和触发源二者的事务解耦，又能收敛相关数据处理。

禁忌2：忌使用dblink

dblink提供的机制可以使得在一个数据库的存储过程、触发器、数据库函数中方便的访问另一个数据库，可以方便地为应用只需连接一个数据库就可以访问另一个数据库中的数据，因此给多数据库环境使用带来了很大的便利性。

但dblink在跨数据库事务提交上容易引发问题，一般可以在不带事务的DML简单查询中使用，如果一定要带事务必须确保事务提交迅速，否则容易引发分布式事务锁。而应用程序中使用时，由于运行的环境复杂多变，无法百分之百保障事务的完整性和响应快速，很容易引发分布式事务锁并有一定几率触发ORACLE的BUG，同时dblink本身会大概率甚至百分之百带来scn号跳变bug，并引发scn号跳变在数据库间传播。导致系统故障甚至数据库瘫痪。因此不要在代码开发中使用dblink。平时运维也尽量少用，如果一定要用最好不带事务，并尽快释放连接。

禁忌3：忌用大表关联统计

在一个系统中，除了实时类交易外，也存在一定要求数据实时的统计或查询需求，针对这种数据统计，切忌使用大表关联进行统计，因为会导致数据库消耗大量计算资源、占用过多的临时空间，影响其他实时业务的响应甚至导致系统无法响应。

对于这种需要跨多个大表的统计，最理想的是不放在OLTP数据库执行，如果一定要执行，一是要想办法限制数据的范围（如基于时间限制只能统计当天的），二是对于两个大表关联的SQL进行拆分，拆分成两个SQL，前一个SQL获取的数据通过游标打开后再逐条去另一个大表使用索引逐条数据进行访问，再用客户端进行统计运算，或者通过游标获取数据生产临时表再基于临时表进行统计。

禁忌4：忌用字典式字段索引

索引只有说数据在索引字段比较分散才有效果，如果基于一些字典式字段（如性别、课程等）建索引，起不到很好的效果不说，还浪费存储空间。这种字典式的字段如果一定要发挥类似索引的效果，可以按字典值建分区键。

禁忌5：慎用主键约束

某个表的主键理论上看起来是个很好的机制，但在一般性应用中，由于主键不能更新，因此在运维时会带来很多不便，一般建议慎用，而是可以用非空和唯一性约束方式来替代。

禁忌6：慎用外键关联

外键关联可以确保某个表的主键被其他表作为非主键使用时来保障两个表数据的一致性，但外键关联给程序开发、运维都带来了更多的复杂性，而好的开发习惯能确保两个有外键关联的表满足数据一致性的要求，因此一般情况下慎用外键关联。这其实是根据在方便性、数据一致性之间应用更倾向于哪方面来决策使用方式。

禁忌7：组合索引使用要注意

使用多个字段的混合索引是常见的，但索引使用的字段越多，就意味着开发时需要关注的字段越多，开发时部分人员容易忘记索引字段，导致容易写出用不到索引的语句。因此一般建议复合索引使用字段不超过5个；
组合索引中字段的顺序是非常重要的，越是唯一的字段越是要靠前；
程序代码使用组合索引时，在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，并且应尽可能的让字段顺序与索引顺序相一致。

禁忌8：慎重考虑表字段调整

当一个初始设计的表在运行一段时间后，随着业务的发展和系统的持续运营，对表结构进行调整是迟早的事，但调整表结构如增加新字段、字段长度调整等都需要慎重，特别是针对数据量大访问频繁的表更要谨慎。

在评估表结构调整时，一般需要考虑：

是否需要停系统调整，对于高并发访问频繁的表至少要等到业务闲时进行调整；
如果是调整字段大小需要评估是否有代码限制了字段大小；
如果是新增字段需要评估是否有代码采用了select *方式访问；
是否需要初始化历史数据？如果有是否会造成行迁移？是否需要重建表？
是否会影响外围接口或系统数据的交互？

为了应对字段增加可能带来的风险，有2个方法来提取预防：

给一些大表预留一定的字段，这样可以避免停系统、减少行数据迁移、并避免系统运行时进行表结构调整的风险，但要规划好预留字段的数量、并做好启用管理；
尽量不动大表本身，而是设计扩展表来解决。

禁忌9：忌直接使用用户名和密码连接数据

在信息安全非常重视的今天，数据库的安全性是重中之重，应用系统不应该在程序代码或配置文件中直接使用用户名和密码方式连接访问业务数据。如果这样，对开发人员和维护人员密码就和没有密码一样，另外如果出现数据库必须修改密码时，需要到处改密码相关的代码或配置文件。

比较好的解决办法是用最小权限的用户登录，登录后通过专用加密配置表获取用户真正使用的用户和密码，这就是二次登录。

禁忌10：慎用数据库连接

在一个大型系统内，数据库连接是宝贵的资源，ORACLE的连接数单实例一般限制在4096个，看起来不少，但如果连接节点多真正使用起来后会发现连接数往往不够用。为此需要对数据库的访问进行连接收敛管控，实现连接的复用。
要实现连接的收敛，有如下做法：

WEB服务器通过连接池管理收敛客户端的数据访问；
后台进程或中间间通过数据访问代理层来进行连接的复用和收敛；
后台维护限制单机登录会话数。

禁忌11：忌用并行

在程序代码或表的参数设置里，都可以设置并行参数，并行对于单表或单语句能起到迅速提高执行效率的作用，但这种并行是以抢占其他任务的资源为代价，因此在OLTP数据库应用中，最好别使用并行的DML语句或将表的并行参数打开。临时执行任务考虑到执行速度需要使用并行时，一定要与DBA协商是否可以开启并行，并在任务执行结束后关闭
表的并行参数。

前几年，有个工程割接时为了提升割接速度，当晚一个参与割接的工程师未和任何人商议就将一个重要的访问频繁的大表的并行参数设置调整多倍，割接后也未关闭，导致第二天业务起来后数据库出现了锁、主机CPU全忙，影响业务超过4个小时。经过较长时间定位才发现是该表并行被打开导致的，问题最终解决但造成了不良影响。而那个导致问题的工程师也在华为年底考核中得到了处罚离职走人。

禁忌12：忌SQL语句不使用绑定变量

在应用开发中，SQL语句大多数是动态SQL，这种动态SQL又分为两种，一种是字段取值是变化的，一种是连数据对象名都是动态的。

对于字段取值变化的情况，基本的访问SQL是固定的，针对变化的字段值就需要用绑定变量方式传值，这时数据库才不会重新编译该SQL语句，可以提高执行效率，如果不使用绑定变量，直接将该SQL语句用字符串方式拼接，其实就与数据对象也是不同变换的方式相同，这样SQL语句每回都会被编译，效率会大幅下降。

对于数据对象名都是动态的SQL，不适合通过绑定变量方式传递数据对象名。

禁忌13：忌索引数量过多

一个表的索引是需要单独占用存储空间的，过多的索引会导致表数据发生变化时索引的调整占用过多的时间，会引发数据的增删改性能下降。对于频繁进行插入、删除、更新的数据表，应控制索引的数量，提高效率，一般一个表的索引数控制在5个以内。如果一个表要建5个以上索引，一般说明表和应用的设计是存在问题的。

禁忌14：select for update 要带nowait

select ... for update语句对于保证事务的完整性很有必要，但在一个并发环境中，使用该语句的应用很可能会导致数据库锁甚至死锁，正确的做法是select ... for update nowait，并且在获取锁失败时要有重新处理的机制。

禁忌15：批量任务要控制好事务提交的频度

在后台进程或存储过程处理大批量数据时，事务提交禁止单条提交或者全部完成后再提交，提交太频繁会产生过多的数据库日志，一次性提交容易导致回滚段不够等问题。建议使用100、500或者1000条提交的频度。

禁忌16：sequence使用注意

对于部分表的内部关键ID字段如流水号等使用sequence是个非常不错的主意，可以有效保障并发环境下这种序列号分配的高效和唯一性。但在应用中使用sequence时要注意几点：

长度设置要充分考虑业务发展的速度，有效序列在初期增长缓慢，但随着业务发展会迅速增长，因此开发设计时要进行充分的评估，防止出现sequence很快被耗尽；
对于长期增长的序列号，最后采用时间戳+序列号的方式来作为字段值，防止序列号翻转；
序列号分配时，由于序列号在不同数据库实例的缓冲机制，可能会出现后面的数据记录序列号比前面插入记录序列号还要小的情况，因此序列号字段的值只能作为唯一键值使用，而不能作为业务排序的依据。

禁忌17：慎用rowid更新数据

rowid是数据库记录的一个内部记录ID，使用rowid访问数据比普通索引还要高效，但如果在一个表的数据频繁插入、删除时，不要使用rowid来进行数据的操作，因为ORACLE的rowid在数据有删除机制时，是有一定几率出现重复的。

禁忌18：慎用子查询

通过子查询方式进行数据的嵌套查询SQL的可读性比较好，但子查询很容易导致全表扫描，且容易导致回滚段或临时表空间使用过高，因此慎用子查询，特别是子查询的结果集也很大时忌用。

禁忌19：忌用SELECT *

在查询SQL语句中，要尽量减少返回的结果行，包括行数和字段列数。返回的结果越大，意味着相应的SQL语句的logical reads 就越大，对服务器的性能影响就越甚。

一个特别不好的设计就是使用SELECT * 返回表的所有数据，除了影响服务器性能外，还可能会由于表结构的调整在编译阶段无法发现，导致运行时错误。

禁忌20：where 子句中慎用!=或<>操作符

使用!=或<>意味着对应字段的访问不会使用索引，因此只有在其他字段使用了索引的基础上才使用!=或<>操作。

禁忌21：where 子句中慎用like

如果在where字句中使用字段A like ‘%xxx' 或字段A like ‘%xxx%'方式匹配结果记录时，该字段不会使用索引，因此只有在其他字段使用了索引的基础上才使用字段A like ‘%xxx' 或字段A like ‘%xxx%'方式，即忌用前置百分号匹配。

禁忌22：where 子句中慎用in和not in

如果in后面的结果数据比较多，很可能会导致全表扫描。因此只能和其他能使用索引的条件组合使用时使用。

禁忌23：where字句中慎用字段函数操作

应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

禁忌24：忌用select count（*）

使用select count（*）的效率不如使用select count(1)。

禁忌25：索引字段访问慎用OR

索引字段用OR的字句容易导致全表扫描，一定要判断多个值可以分解成多个查询，并且通过UNION 连接多个查询。

禁忌26：慎重考虑字符集

在一个应用系统中，涉及应用服务器主机字符集、数据库字符集以及客户端字符集的兼容性问题，要在系统构架时统一考虑，确保从前台到后台到数据库的字符集都是兼容的。

禁忌27、慎用视图嵌套

视图最好建在表上，尽量不要基于视图嵌套建立视图，一方面一般视图嵌套不要超过2个为宜，视图嵌套层级多访问效率会下降，并且可维护性变差。

禁忌28：忌数据对象名过长

oracle的表名、字段名等对象命名字节个数限制在了30个字节，不能超过30。

禁忌29：谨慎表和索引的inittrans设置

每个块都有一个块首部。这个块首部中有一个事务表。事务表中会建立一些条目来描述哪些事务将块上的哪些行/元素锁定。这个事务表的初始大小由对象的INITRANS 设置指定。对于表，这个值默认为2（索引的INITRANS 也默认为2）。事务表会根据需要动态扩展，最大达到MAXTRANS 个条目（假设块上有足够的自由空间）。所分配的每个事务条目需要占用块首部中的23～24 字节的存储空间。注意，对于Oracle 10g以上版本，MAXTRANS 则会忽略，所有段的MAXTRANS 都是255。

也就是说，如果某个事物锁定了这个块的数据，则会在这个地方记录事务的标识，当然那个事务要先看一下这个地方是不是已经有人占用了，如果有，则去看看那个事务是否为活动状态。如果不活动，比如已经提交或者回滚，则可以覆盖这个地方。如果活动，则需要等待(闩的作用)。

所以，如果有大量的并发访问使用的这个块，则参数不能太小，否则资源竞争将导致系统并发性能下降。

在创建表和索引时，需要根据表数据的访问频度和数据量来评估设置的inittrans值，虽然不够时Oracle会自动增加，但增加的过程需要ORACLE进行评估，且该评估机制是针对所有对象的，需要排队，如果inittrans值设置过小，就会影响系统的并发性能。因此系统设计和部署时需要考虑不同表的inittrans的设置。