技术资料 - 数据库编程总结(心得体会)

    当前各种主流数据库有很多,包括Oracle, MS SQL Server, Sybase, Informix, MySQL, DB2, Interbase / Firebird, PostgreSQL, SQLite, SAP/DB, TimesTen, MS ACCESS等等。数据库编程是对数据库的创建、读写等一列的操作。数据库编程分为数据库客户端编程与数据库服务器端编程。数据库客户端编程主要使用ODBC API、ADO、ADO.NET、OCI、OTL等方法;数据库服务端编程主要使用OLE DB等方法。数据库编程需要掌握一些访问数据库技术方法,还需要注意怎么设计高效的数据库、数据库管理与运行的优化、数据库语句的优化。一、访问数据库技术方法数据库编程分为数据库客户端编程与数据库服务器端编程。数据库客户端编程主要使用ODBC API、ADO、ADO.NET、OCI、OTL等方法;数据库服务端编程主要使用OLE DB等方法。1、几种是数据库访问方法比较ODBC API是一种适合数据库底层开发的编程方法,ODBC API提供大量对数据源的操作,ODBC API能够灵活地操作游标,支持各种帮定选项,在所有ODBC相关编程中,API编程具有最高的执行速度。 DAO提供了很好的数据库编程的对象模型.但是,对数据库的所有调用以及输出的数据都必须通过Access/Jet数据库引擎,这对于使用数据库应用程序,是严重的瓶颈。 OLE DB提供了COM接口,与传统的数据库接口相比,有更好的健壮性和灵活性,具有很强的错误处理能力,能够同非关系数据源进行通信。 ADO最主要的优点在于易于使用、速度快、内存支出少和磁盘遗迹小。 ADO.NET 是利用数据集的概念将数据库数据读入内存中,然后在内存中对数据进行操作,最后将数据集数据回写到源数据库中。OTL 是 Oracle, Odbc and DB2-CLI Template Library 的缩写,是一个C++编译中操控关系数据库的模板库, OTL中直接操作Oracle主要是通过Oracle提供的OCI接口进行,进行操作DB2数据库则是通过CLI接口来进行,至于MS的数据库和其它一些数据库,则OTL只提供了ODBC来操作的方式。当然Oracle和DB2也可以由OTL间接使用ODBC的方式来进行操纵。具有以下优点:跨平台;运行效率高,与C语言直接调用API相当;开发效率高,起码比ADO.net使用起来更简单,更简洁;部署容易,不需要ADO组件,不需要.net framework 等。 2、VC数据库编程几种方法VC数据库编程几种方法,包括ODBC连接、MFC ODBC连接、DAO连接、OLE DB、OLE DB Templates连接、ADO、Oracle专用方法(OCI(Oracle Call Interface)访问、Oracle Object OLE C++ Class Library )。<1.>通用方法 1. ODBC连接 ODBC(Open DataBase Connectivity)是MSOA的一部分,是一个标准数据库接口。它提供对关系数据库访问的统一接口,实现对异构数据源的一致访问。ODBC数据访问由以下部分组成: <1>句柄(Handles):ODBC使用句柄来标识ODBC环境、连接、语句和描述器. <2>缓存区(Buffers): <3>数据类型(Data types) <4>一致性级别(Conformance levels) 用ODBC设计客户端的一般步骤: <1>分配ODBC环境 <2>分配连接句柄 <3>连接数据源 <4>构造和执行SQL语句 <5>获得查询结果 <6>断开数据源的连接 <7>释放ODBC环境 ODBC API是一种适合数据库底层开发的编程方法,ODBC API提供大量对数据源的操作,ODBC API能够灵活地操作游标,支持各种帮定选项,在所有ODBC相关编程中,API编程具有最高的执行速度.因此,ODBC API编程属于底层编程。 2. MFC ODBC连接 MFC ODBC是MFC对ODBC进行的封装,以简化对ODBC API的 调用,从而实现面向对象的数据库编程接口. MFC ODBC的封装主要开发了CDatabase类和CRecordSet类 (1) CDatabase类 CDatabase类用于应用程序建立同数据源的连接。CDatabase类中包含一个m_hdbc变量,它代表了数据源的连接句柄。如果要建立CDatabase类的实例,应先调用该类的构造函数,再调用Open函数,通过调用,初始化环境变量,并执行与数据源的连接。在通过Close函数关闭数据源。 CDatabase类提供了对数据库进行操作的函数及事务操作。 (2) CRecordSet类 CRecordSet类定义了从数据库接收或者发送数据到数据库的成员变量,以实现对数据集的数据操作。 CRecordSet类的成员变量m_hstmt代表了定义该记录集的SQL语句句柄,m_nFields为记录集中字段的个数,m_nParams为记录集所使用的参数个数。 CRecordSet的记录集通过CDatabase实例的指针实现同数据源的连接,即CRecordSet的成员变量m_pDatabase. MFC ODBC编程更适合于界面型数据库应用程序的开发,但由于CDatabase类和CRecordSet类提供的数据库操作函数有限,支持的游标类型也有限,限制了高效的数据库开发。在编程层次上属于高级编程。

    存储过程编写经验和优化措施

      一)、适合读者对象:数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人。  

      二)、介绍:在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作。如果项目的SP较多,书写又没有一定的规范,将会影响以后的系统维护困难和大SP逻辑的难以理解,另外如果数据库的数据量大或者项目对SP的性能要求很,就会遇到优化的问题,否则速度有可能很慢,经过亲身经验,一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。  

      三)、内容:  

      1、开发人员如果用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接使用“databse.dbo.table_name”,因为sp_depends不能显示出该SP所使用的跨库table或view,不方便校验。  

      2、开发人员在提交SP前,必须已经使用set showplan on分析过查询计划,做过自身的查询优化检查。

    3、高程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点:   

      a)SQL的使用规范:

       i. 尽量避免大事务操作,慎用holdlock子句,提高系统并发能力。

       ii. 尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接。

       iii. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。

       iv. 注意where字句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。

       v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

       vi. 尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。

       vii. 尽量使用“>=”,不要使用“>”。

       viii. 注意一些or子句和union子句之间的替换

       ix. 注意表之间连接的数据类型,避免不同类型数据之间的连接。

       x. 注意存储过程中参数和数据类型的关系。

       xi. 注意insert、update操作的数据量,防止与其他应用冲突。如果数据量超过200个数据页面(400k),那么系统将会进行锁升级,页级锁会升级成表级锁。   

      b)索引的使用规范:

       i. 索引的创建要与应用结合考虑,建议大的OLTP表不要超过6个索引。

       ii. 尽可能的使用索引字段作为查询条件,尤其是聚簇索引,必要时可以通过index index_name来强制指定索引

       iii. 避免对大表查询时进行table scan,必要时考虑新建索引。

       iv. 在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。

       v. 要注意索引的维护,周期性重建索引,重新编译存储过程。  

      c)tempdb的使用规范:

       i. 尽量避免使用distinct、order by、group by、having、join、cumpute,因为这些语句会加重tempdb的负担。

       ii. 避免频繁创建和删除临时表,减少系统表资源的消耗。

       iii. 在新建临时表时,如果一次性插入数据量很大,那么可以使用select into代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先create table,然后insert。

       iv. 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。

        v. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncate table,然后drop table,这样可以避免系统表的较长时间锁定。

        vi. 慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。  

      d)合理的算法使用:   

      根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令:set statistics io on, set statistics time on , set showplan on 等。

    51、SET SHOWPLAN_ALL ON 查看执行方案。DBCC检查数据库数据完整性。DBCC(DataBase Consistency Checker)是一组用于验证SQL Server数据库完整性的程序。

    52、谨慎使用游标

    在某些必须使用游标的场合,可考虑将符合条件的数据行转入临时表中,再对临时表定义游标进行操作,这样可使性能得到明显提高。

    Oracle SQL 性能优化:

    1.选用适合的ORACLE优化器
    ORACLE的优化器共有3种

    A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性)

    设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS 。 你当然也在SQL句级或是会话(session)级对其进行覆盖。

    为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性。

    如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关。 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器。

    在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器。

    2.访问Table的方式
    ORACLE 采用两种访问表中记录的方式:

    A、 全表扫描

    全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。

    B、 通过ROWID访问表

    你可以采用基于ROWID的访问方式情况,提高访问表的效率, ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高。

    3.共享SQL语句
    为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。

    可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering),这个功能并不适用于多表连接查询。

    数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了。

    当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等)。

    数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了。

    共享的语句必须满足三个条件:

    A、 字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同。

    B、 两个语句所指的对象必须完全相同:

    C、 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。

    4.选择最有效率的表名顺序(只在基于规则的优化器中有效)
    ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时, 会运用排序及合并的方式连接它们。首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

    如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。

    5.WHERE子句中的连接顺序
    ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

    6.SELECT子句中避免使用 ' * '
    当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 '*' 是一个方便的方法。不幸的是,这是一个非常低效的方法。实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

    7.减少访问数据库的次数
    当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等。由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量。

    8.使用DECODE函数来减少处理时间
    使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

    9.整合简单,无关联的数据库访问
    如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

    10.删除重复记录

    11.用TRUNCATE替代DELETE
    当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息。 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。

    而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息。当命令运行后,数据不能被恢复。因此很少的资源被调用,执行时间也会很短。

    12.尽量多使用COMMIT
    只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少

    COMMIT所释放的资源:

    A、 回滚段上用于恢复数据的信息。

    B、被程序语句获得的锁。

    C、 redo log buffer 中的空间。

    D、ORACLE为管理上述3种资源中的内部花费。

    13.计算记录条数
    和一般的观点相反,count(*) 比count(1)稍快,当然如果可以通过索引检索,对索引列的计数仍旧是最快的。例如 COUNT(EMPNO)

    14.用Where子句替换HAVING子句
    避免使用HAVING子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序,总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。

    15.减少对表的查询
    在含有子查询的SQL语句中,要特别注意减少对表的查询。

    16.通过内部函数提高SQL效率。

    17.使用表的别名(Alias)
    当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

    18.用EXISTS替代IN
    在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率。

    19.用NOT EXISTS替代NOT IN
    在子查询中,NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

    20.用表连接替换EXISTS
    通常来说 , 采用表连接的方式比EXISTS更有效率 。

    21.用EXISTS替换DISTINCT
    当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。 一般可以考虑用EXIST替换 。

    DB2数据库优化

    为了帮助 DB2 DBA 避免性能灾难并获得高性能,我为我们的客户、用户和 DB2 专家同行总结了一套故障诊断流程。以下详细说明在 Unix、Windows 和 OS/2 环境下使用 DB2 UDB 的电子商务 OLTP 应用程序的 10 条最重要的性能改善技巧 - 并在本文的结束部分作出 总结。

      每隔大约几个星期,我们就会接到苦恼的 DBA 们的电话,抱怨有关性能的问题。“我们 Web 站点速度慢得像蜗牛一样”,他们叫苦道,“我们正在失去客户,情况严重。你能帮忙吗?”为了回答这些问题,我为我的咨询公司开发了一个分析流程,它能让我们很快找到性能问题的原因,开发出补救措施并提出调整意见。这些打电话的人极少询问费用和成本 - 他们只关心制止损失。当 DB2 或电子商务应用程序的运行不能达到预期的性能时,组织和财务的收益将遭受极大的损失。