数据库编程总结

数据库编程总结 当前各种主流数据库有很多，包括Oracle, MS SQL Server, Sybase, Informix, MySQL, DB2, Interbase / Firebird, PostgreSQL, SQLite, SAP/DB, TimesTen, MS ACCESS等等。数据库编程是对数据库的创建、读写等一列的操作。数据库编程分为数据 、ADO.NET、库客户端编程与数据库服务器端编程。数据库客户端编程主要使用ODBC API、ADOOCI、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类提供的数据库操作函数有限,支持的游标类型也有限,限制了高效的数据库开发。在编程层次上属于高级编程。 应用实例: 1.打开数据库 CDatabase database; database.OpenEx( _T( "DSN=zhuxue" ),CDatabase::noOdbcDialog);//zhuxue为数据源名称 2.关联记录集 CRecordset recset(&database); 3.查询记录 CString sSql1=""; sSql1 = "SELECT * FROM tablename" ; recset.Open(CRecordset::forwardOnly, sSql1, CRecordset::readOnly); int ti=0; CDBVariant var;//var可以转换为其他类型的值 while (!recset.IsEOF()) { //读取Excel内部数值 recset.GetFieldValue("id",var); jiangxiang[ti].id=var.m_iVal; recset.GetFieldValue("name", jiangxiang[ti].name); ti++; recset.MoveNext(); } recset.Close();//关闭记录集 4.执行sql语句 CString sSql=""; sSql+="delete * from 院系审核";//清空表 database.ExecuteSQL(sSql); sSql也可以为Insert ,Update等语句 5.读取字段名 sSql = "SELECT * FROM Sheet1" ; //读取的文件有Sheet1表的定义,或为本程序生成的表. // 执行查询语句 recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly); int excelColCount=recset.GetODBCFieldCount();//列数 CString excelfield[30]; //得到记录集的字段集合中的字段的总个数 for( i=0;i数据提供程序 数据提供程序拥有自己的数据并把数据以表格的形式呈现给使用者使用. <2>服务提供程序 服务提供程序是数据提供程序和使用者的结合。它是OLE DB体系结构中的中间件,它是OLE DB数据源的使用者和数据使用程序的提供者 <3>数据使用程序 数据使用程序对存储在数据提供程序中的数据进行使用和控制. OLE DB开发程序的一般步骤: <1>初始化COM环境 <2>连接数据源 <3>打开对话 <4>执行命令 <5>处理结果 <6>清除对象 应用实例: 使用OLEDB编写数据库应用程序 1 概述 OLE DB的存在为用户提供了一种统一的方法来访问所有不同种类的数据源。OLE DB可以在不同的数据源中进行转换。利用OLE DB，客户端的开发人员在进行数据访问时只需把精力集中在很少的一些细节上，而不必弄懂大量不同数据库的访问 协议 离婚协议模板下载合伙人协议 下载渠道分销协议免费下载敬业协议下载授课协议下载 。 OLE DB是一套通过COM接口访问数据的ActiveX接口。这个OLE DB接口相当通用，足以提供一种访问数据的统一手段，而不管存储数据所使用的方法如何。同时，OLE DB还允许开发人员继续利用基础数据库技术的优点，而不必为了利用这些优点而把数据移出来。 2 使用ATL使用OLE DB数据使用程序 由于直接使用OLE DB的对象和接口设计数据库应用程序需要书写大量的代码。为了简化程序设计，Visual C++提供了ATL模板用于设计OLE DB数据应用程序和数据提供程序。 利用ATL模板可以很容易地将OLE DB与MFC结合起来，使数据库的参数查询等复杂的编程得到简化。MFC提供的数据库类使OLE DB的编程更具有面向对象的特性。Viual C++所提供用于OLE DB的ATL模板可分为数据提供程序的模板和数据使用程序的模板。 使用ATL模板创建数据应用程序一般有以下几步骤: 1)、 创建应用框架 2)、 加入ATL产生的模板类 3)、 在应用中使用产生的数据访问对象 3 不用ATL使用OLE DB数据使用程序 利用ATL模板产生数据使用程序较为简单，但适用性不广，不能动态适应数据库的变化。下面我们介绍直接使用MFC OLE DB类来生成数据使用程序。 模板的使用 OLE DB数据使用者模板是由一些模板组成的，包括如下一些模板，下面对一些常用类作一些介绍。 1)、 会话类 CDataSource类 CDataSource类与OLE DB的数据源对象相对应。这个类代表了OLE DB数据提供程序和数据源之间的连接。只有当数据源的连接被建立之后，才能产生会话对象，可以调用Open来打开数据源的连接。 CSession类 CSession所创建的对象代表了一个单独的数据库访问的会话。一个用CDataSource类产生的数据源对象可以创建一个或者多个会话，要在数据源对象上产生一个会话对象，需要调用函数Open()来打开。同时，会话对象还可用于创建事务操作。 CEnumeratorAccessor类 CEnumeratorAccessor类是用来访问枚举器查询后所产生的行集中可用数据提供程序的信息的访问器，可提供当前可用的数据提供程序和可见的访问器。 2)、 访问器类 CAcessor类 CAccessor类代表与访问器的类型。当用户知道数据库的类型和结构时，可以使用此类。它支持对一个行集采用多个访问器，并且，存放数据的缓冲区是由用户分配的。 CDynamicAccessor类 CDynamicAccessor类用来在程序运行时动态的创建访问器。当系统运行时，可以动态地从行集中获得列的信息，可根据此信息动态地创建访问器。 CManualAccessor类 CManualAccessor类中以在程序运行时将列与变量绑定或者是将参数与变量捆定。 3)、 行集类 CRowSet类 CRowSet类封装了行集对象和相应的接口，并且提供了一些方法用于查询、设置数据等。可以用Move()等函数进行记录移动，用GetData()函数读取数据，用Insert()、Delete()、SetData()来更新数据。 CBulkRowset类 CBulkRowset类用于在一次调用中取回多个行句柄或者对多个行进行操作。 CArrayRowset类 CArrayRowset类提供用数组下标进行数据访问。 4)、 命令类 CTable类 CTable类用于对数据库的简单访问，用数据源的名称得到行集，从而得到数据。 CCommand类 CCommand类用于支持命令的数据源。可以用Open()函数来执行SQL命令，也可以Prepare()函数先对命令进行准备，对于支持命令的数据源，可以提高程序的灵活性和健壮性。 在stdafx.h头文件里，加入如下代码。 #include extern CComModule _Module; #include #include #include // if you are using schema templates 在stdafx.cpp文件里，加入如下代码。 #include CComModule _Module; 决定使用何种类型的存取程序和行集。 获取数据 在打开数据源，会话，行集对象后就可以获取数据了。所获取的数据类型取决于所用的存取程序，可能需要绑定列。按以下步骤。 1、 用正确的命令打开行集对象。 2、 如果使用CManualAccessor，在使用之前与相应列进行绑定。要绑定列，可以用函数GetColumnInfo,如下所示: // Get the column information ULONG ulColumns = 0; DBCOLUMNINFO* pColumnInfo = NULL; LPOLESTR pStrings = NULL; if (rs.GetColumnInfo(&ulColumns, &pColumnInfo, &pStrings) != S_OK) AfxThrowOLEDBException(rs.m_pRowset, IID_IColumnsInfo); struct MYBIND* pBind = new MYBIND[ulColumns]; rs.CreateAccessor(ulColumns, &pBind[0], sizeof(MYBIND)*ulColumns); for (ULONG l=0; l与ATL中的CComPtr类似，是一个在析构时自动调用Release的类。CComPtr的代码在ATLBASE.H中定义。 2.以下代码均在UNICODE环境下编译，因为执行的SQL语句必须是UNICODE的。设置工程为UNICODE的方法是:首先在project->settings->C/C++的属性页中的Preprocessor中，删除_MBCS写入UNICODE,_UNICODE。然后在link属性页中Category中选择output，在Entry-Point symbol 中添加wWinMainCRTStartup。 EAutoReleasePtr pIDBInitialize; HRESULT hResult = ConnectDatabase( &pIDBInitialize, _T("127.0.0.1"), _T(“sa”), _T("password") ); if( FAILED( hResult ) ) { //失败，可能是因为数据库没有启动、用户名密码错等等 return; } EAutoReleasePtr pIOpenRowset; hResult = CreateSession( pIDBInitialize, &pIOpenRowset ); if( FAILED( hResult ) ) { //出错 return; } EAutoReleasePtr pICommand; EAutoReleasePtr pICommandText; hResult = CreateCommand( pIOpenRowset, &pICommand, &pICommandText ); if( FAILED( hResult ) ) { //出错 return; } hResult = ExecuteSQL( pICommand, pICommandText, _T("USE PBDATA") ); if( FAILED( hResult ) ) { //如果这里失败，那就是SQL语句执行失败。在此处，就是PBDATA还未创建 return; } // 创建表 ExecuteSQL( pICommand, pICommandText, _T("CREATE TABLE 2005_1(Volume real NOT NULL,ID int NOT NULL IDENTITY)") ); // 添加记录 ExecuteSQL( pICommand, pICommandText, _T("INSERT INTO 2005_1 VALUES(100.0)") ); //... 其中几个函数的代码如下: HRESULT ConnectDatabase( IDBInitialize** ppIDBInitialize, LPCTSTR pszDataSource, LPCTSTR pszUserID, LPCTSTR pszPassword ) { ASSERT( ppIDBInitialize != NULL && pszDataSource != NULL && pszUserID != NULL && pszPassword != NULL ); UINT uTimeout , 15U; // 连接数据库超时(秒) TCHAR szInitStr[1024]; VERIFY( 1023 >= wsprintf( szInitStr, _T("Provider=SQLOLEDB;Data Source=%s;Initial Catalog=master;User Id=%s;Password=%s;Connect Timeout=%u"), pszDataSource, pszUserID, pszPassword, uTimeout ) ); //Initial Catalog=master指明连接成功后，"USE master"。 EAutoReleasePtr pIDataInitialize; HRESULT hResult = ::CoCreateInstance( CLSID_MSDAINITIALIZE, NULL, CLSCTX_INPROC_SERVER, IID_IDataInitialize, ( void** )&pIDataInitialize ); if( FAILED( hResult ) ) { return hResult; } EAutoReleasePtr pIDBInitialize; hResult = pIDataInitialize->GetDataSource( NULL, CLSCTX_INPROC_SERVER, ( LPCOLESTR )szInitStr, IID_IDBInitialize, ( IUnknown** )&pIDBInitialize ); if( FAILED( hResult ) ) { return hResult; } hResult = pIDBInitialize->Initialize( ); if( FAILED( hResult ) ) { return hResult; } * ppIDBInitialize = pIDBInitialize.Detach( ); return S_OK; } HRESULT CreateSession( IDBInitialize* pIDBInitialize, IOpenRowset** ppIOpenRowset ) { ASSERT( pIDBInitialize != NULL && ppIOpenRowset != NULL ); EAutoReleasePtr pSession; HRESULT hResult = pIDBInitialize->QueryInterface( IID_IDBCreateSession, ( void** )&pSession ); if( FAILED( hResult ) ) { return hResult; } EAutoReleasePtr pIOpenRowset; hResult = pSession->CreateSession( NULL, IID_IOpenRowset, ( IUnknown** )&pIOpenRowset ); if( FAILED( hResult ) ) { return hResult; } * ppIOpenRowset = pIOpenRowset.Detach( ); return S_OK; } HRESULT CreateCommand( IOpenRowset* pIOpenRowset, ICommand** ppICommand, ICommandText** ppICommandText ) { ASSERT( pIOpenRowset != NULL && ppICommand != NULL && ppICommandText != NULL ); HRESULT hResult; EAutoReleasePtr pICommand; { EAutoReleasePtr pICreateCommand; hResult = pIOpenRowset->QueryInterface( IID_IDBCreateCommand, ( void** )&pICreateCommand ); if( FAILED( hResult ) ) { return hResult; } hResult = pICreateCommand->CreateCommand( NULL, IID_ICommand, (IUnknown**)&pICommand ); if( FAILED( hResult ) ) { return hResult; } } EAutoReleasePtr pICommandText; hResult = pICommand->QueryInterface( &pICommandText ); if( FAILED( hResult ) ) { return hResult; } * ppICommand = pICommand.Detach( ); * ppICommandText = pICommandText.Detach( ); return S_OK; } HRESULT ExecuteSQL( ICommand* pICommand, ICommandText* pICommandText, LPCTSTR pszCommand, LONG* plRowsAffected ) { ASSERT( pICommand != NULL && pICommandText != NULL && pszCommand != NULL && pszCommand[0] != 0 ); HRESULT hResult = pICommandText->SetCommandText( DBGUID_DBSQL, ( LPCOLESTR )pszCommand ); if( FAILED( hResult ) ) { return hResult; } LONG lAffected; hResult = pICommand->Execute( NULL, IID_NULL, NULL, plRowsAffected == NULL ? &lAffected : plRowsAffected, ( IUnknown** )NULL ); return hResult; } 以上就是写数据库的全部代码了，是不是很简单呢,下面再来读的。 // 先用与上面代码中一样的步骤获取pICommand，pICommandText。此处省略 HRESULT hResult = pICommandText->SetCommandText( DBGUID_DBSQL, ( LPCOLESTR )_T("SELECT Volume FROM 2005_1 WHERE ID = @@IDENTITY") ); //取我们刚刚添加的那一条记录 if( FAILED( hResult ) ) { return; } LONG lAffected; EAutoReleasePtr pIRowset; hResult = pICommand->Execute( NULL, IID_IRowset, NULL, &lAffected, ( IUnknown** )&pIRowset ); if( FAILED( hResult ) ) { return; } EAutoReleasePtr pIAccessor; hResult = pIRowset->QueryInterface( IID_IAccessor, ( void** )&pIAccessor ); if( FAILED( hResult ) ) { return; } // 一个根据表中各字段的数值类型而定义的结构，用于存储返回的各字段的值 struct CLoadLastFromDB { DBSTATUS dwdsVolume; DWORD dwLenVolume; float fVolume; }; // 此处我们只查询了一个字段。如果要查询多个字段，CLoadLastFromDB中要添加相应的字段定义，下面的dbBinding也要相应扩充。dbBinding[].iOrdinal要分别指向各个字段， dbBinding[].wType要根据字段类型赋合适的值。 DBBINDING dbBinding[1]; dbBinding[0].iOrdinal = 1; // Volume 字段的位置，从 1 开始 dbBinding[0].obValue = offsetof( CLoadLastFromDB, fVolume ); dbBinding[0].obLength = offsetof( CLoadLastFromDB, dwLenVolume ); dbBinding[0].obStatus = offsetof( CLoadLastFromDB, dwdsVolume ); dbBinding[0].pTypeInfo = NULL; dbBinding[0].pObject = NULL; dbBinding[0].pBindExt = NULL; dbBinding[0].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH; dbBinding[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED; dbBinding[0].eParamIO = DBPARAMIO_NOTPARAM; dbBinding[0].cbMaxLen = 0; dbBinding[0].dwFlags = 0; dbBinding[0].wType = DBTYPE_R4; // float就是DBTYPE_R4，int就是DBTYPE_I4。参见MSDN dbBinding[0].bPrecision = 0; dbBinding[0].bScale = 0; HACCESSOR hAccessor = DB_NULL_HACCESSOR; DBBINDSTATUS dbs[1]; hResult = pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA, 1, dbBinding, sizeof( CLoadLastDataFromDB ), &hAccessor, dbs ); if( FAILED( hResult ) ) { return; } ASSERT( dbs[0] == DBBINDSTATUS_OK ); ULONG uRowsObtained = 0; HROW hRows[1]; // 这里我们只查询了最新的那一条记录 HROW* phRows = hRows; CLoadLastFromDB rmd; hResult = pIRowset->GetNextRows( NULL, 0, 1, &uRowsObtained, &phRows ); if( SUCCEEDED( hResult ) && uRowsObtained != 0U ) { hResult = pIRowset->GetData( phRows[0], hAccessor, &rmd ); if( FAILED( hResult ) ) { ASSERT( FALSE ); } ASSERT( rmd.dwdsVolume == DBSTATUS_S_OK ); // rmd.fVolume 就是我们要取的值 } pIRowset->ReleaseRows( uRowsObtained, phRows, NULL, NULL, NULL ); pIAccessor->ReleaseAccessor( hAccessor, NULL ); pIAccessor.Release( ); pIRowset.Release( ); 读操作也完成了，是不是仍然很简单呢,下面我们再来看看最麻烦的二进制数据(text、ntext、 image等)的读写。要实现BLOB数据的读写，我们需要一个辅助的类，定义如下: class CSequentialStream : public ISequentialStream // BLOB 数据访问类 { public: CSequentialStream( ); virtual ~CSequentialStream( ); virtual BOOL Seek( ULONG uPosition ); virtual BOOL Clear( ); virtual ULONG GetLength( ) { return m_uBufferUsed; }; virtual operator void* const( ) { return m_pBuffer; }; STDMETHODIMP_( ULONG ) AddRef( ) { return ++ m_uRefCount; }; STDMETHODIMP_( ULONG ) Release( ) { ASSERT( m_uRefCount != 0U ); -- m_uRefCount; if( m_uRefCount == 0U ) { delete this; } return m_uRefCount; }; STDMETHODIMP QueryInterface( REFIID riid, LPVOID* ppv ); STDMETHODIMP Read( void __RPC_FAR* pv, ULONG cb, ULONG __RPC_FAR* pcbRead ); STDMETHODIMP Write( const void __RPC_FAR* pv, ULONG cb, ULONG __RPC_FAR* pcbWritten ); void ResetPosition( ) { m_uPosition = 0U; }; HRESULT PreAllocBuffer( ULONG uSize ); private: ULONG m_uRefCount; // reference count void* m_pBuffer; // buffer ULONG m_uBufferUsed; // buffer used ULONG m_uBufferSize; // buffer size ULONG m_uPosition; // current index position in the buffer }; 实现如下: CSequentialStream::CSequentialStream( ) : m_uRefCount( 0U ), m_pBuffer( NULL ), m_uBufferUsed( 0U ), m_uBufferSize( 0U ), m_uPosition( 0U ) { AddRef( ); } CSequentialStream::~CSequentialStream( ) { Clear( ); } HRESULT CSequentialStream::QueryInterface( REFIID riid, void** ppv ) { if( riid == IID_IUnknown || riid == IID_ISequentialStream ) { * ppv = this; ( ( IUnknown* )*ppv )->AddRef( ); return S_OK; } * ppv = NULL; return E_NOINTERFACE; } BOOL CSequentialStream::Seek( ULONG uPosition ) { ASSERT( uPosition < m_uBufferUsed ); m_uPosition = uPosition; return TRUE; } BOOL CSequentialStream::Clear( ) { m_uBufferUsed = 0U; m_uBufferSize = 0U; m_uPosition = 0U; ( m_pBuffer != NULL ? CoTaskMemFree( m_pBuffer ) : 0 ); m_pBuffer = NULL; return TRUE; } HRESULT CSequentialStream::PreAllocBuffer( ULONG uSize ) { if( m_uBufferSize < uSize ) { m_uBufferSize = uSize; m_pBuffer = CoTaskMemRealloc( m_pBuffer, m_uBufferSize ); if( m_pBuffer == NULL ) { Clear( ); return STG_E_INSUFFICIENTMEMORY; } } return S_OK; } HRESULT CSequentialStream::Read( void* pv, ULONG cb, ULONG* pcbRead ) { ( pcbRead != NULL ? ( * pcbRead = 0U ) : 0 ); if( pv == NULL ) { return STG_E_INVALIDPOINTER; } if( cb == 0U ) { return S_OK; } ASSERT( m_uPosition <= m_uBufferUsed ); ULONG uBytesLeft = m_uBufferUsed - m_uPosition; if( uBytesLeft == 0U ) { return S_FALSE; } //no more bytes ULONG uBytesRead = ( cb > uBytesLeft ? uBytesLeft : cb ); memcpy( pv, ( BYTE* )m_pBuffer + m_uPosition, uBytesRead ); m_uPosition += uBytesRead; ( pcbRead != NULL ? ( * pcbRead = uBytesRead ) : 0 ); return ( cb != uBytesRead ? S_FALSE : S_OK ); } HRESULT CSequentialStream::Write( const void* pv, ULONG cb, ULONG* pcbWritten ) { if( pv == NULL ) { return STG_E_INVALIDPOINTER; } ( pcbWritten != NULL ? ( * pcbWritten = 0U ) : 0 ); if( cb == 0U ){ return S_OK; } ASSERT( m_uPosition <= m_uBufferUsed ); if( m_uBufferSize < m_uPosition + cb ) { m_uBufferSize = m_uPosition + cb; m_pBuffer = CoTaskMemRealloc( m_pBuffer, m_uBufferSize ); if( m_pBuffer == NULL ) { Clear( ); return STG_E_INSUFFICIENTMEMORY; } } m_uBufferUsed = m_uPosition + cb; memcpy( ( BYTE* )m_pBuffer + m_uPosition, pv, cb ); m_uPosition += cb; ( pcbWritten != NULL ? ( * pcbWritten = cb ) : 0 ); return S_OK; } 下面我们开始往一个包含ntext字段的表中添加记录。假设这个表(News)的结构为:ID int NOT NULL IDENTITY、Title nchar(80)、 Contents ntext。 // 先将记录添加进去，ntext字段留空。我们稍后再更新ntext的内容。 HRESULT hResult = ExecuteSQL( pICommand, pICommandText, _T("INSERT INTO News VALUES('TEST','')") ); DBPROP dbProp; dbPropSet.guidPropertySet = DBPROPSET_ROWSET; dbPropSet.cProperties = 1; dbPropSet.rgProperties = &dbProp; DBPROPSET dbPropSet; dbPropSet.rgProperties[0].dwPropertyID = DBPROP_UPDATABILITY; dbPropSet.rgProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED; dbPropSet.rgProperties[0].dwStatus = DBPROPSTATUS_OK; dbPropSet.rgProperties[0].colid = DB_NULLID; dbPropSet.rgProperties[0].vValue.vt = VT_I4; V_I4( &dbPropSet.rgProperties[0].vValue ) = DBPROPVAL_UP_CHANGE; EAutoReleasePtr pICommandProperties; hResult = pICommandText->QueryInterface( IID_ICommandProperties, ( void** )&pICommandProperties ); // 设置 Rowset 属性为“可以更新某字段的值” hResult = pICommandProperties->SetProperties( 1, &dbPropSet ); hResult = pICommandText->SetCommandText( DBGUID_DBSQL, ( LPCOLESTR )L"SELECT Contents FROM News WHERE ID = @@IDENTITY" ); LONG lAffected; EAutoReleasePtr pIRowsetChange; hResult = pICommand->Execute( NULL, IID_IRowsetChange, NULL, &lAffected, ( IUnknown** )&pIRowsetChange ); EAutoReleasePtr pIAccessor; hResult = pIRowsetChange->QueryInterface( IID_IAccessor, ( void** )&pIAccessor ); struct BLOBDATA { DBSTATUS dwStatus; DWORD dwLength; ISequentialStream* pISeqStream; }; // 有关DBOBJECT、DBBINDING的设置，建议参考MSDN，很容易懂。 DBOBJECT dbObj; dbObj.dwFlags = STGM_READ; dbObj.iid = IID_ISequentialStream; DBBINDING dbBinding; dbBinding.iOrdinal = 1; // BLOB 字段的位置，从 1 开始 dbBinding.obValue = offsetof( BLOBDATA, pISeqStream ); dbBinding.obLength = offsetof( BLOBDATA, dwLength ); dbBinding.obStatus = offsetof( BLOBDATA, dwStatus ); dbBinding.pTypeInfo = NULL; dbBinding.pObject = &dbObj; dbBinding.pBindExt = NULL; dbBinding.dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH; dbBinding.dwMemOwner = DBMEMOWNER_CLIENTOWNED; dbBinding.eParamIO = DBPARAMIO_NOTPARAM; dbBinding.cbMaxLen = 0; dbBinding.dwFlags = 0; dbBinding.wType = DBTYPE_IUNKNOWN; dbBinding.bPrecision = 0; dbBinding.bScale = 0; HACCESSOR hAccessor = DB_NULL_HACCESSOR; DBBINDSTATUS dbs; hResult = pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA, 1, &dbBinding, sizeof( BLOBDATA ), &hAccessor, &dbs ); EAutoReleasePtr pIRowset; hResult = pIRowsetChange->QueryInterface( IID_IRowset, ( void** )&pIRowset ); ULONG uRowsObtained = 0; HROW* phRows = NULL; hResult = pIRowset->GetNextRows( NULL, 0, 1, &uRowsObtained, &phRows ); CSequentialStream* pss = new CSequentialStream; pss->PreAllocBuffer( 1024 ); // 预先分配好内存，并读入数据 pss->Write( pszSomebuffer, 512, NULL ); // pss->Write可以连续调用 pss->Write( pszSomebuffer+512, 512, NULL ); pss->ResetPosition( ); BLOBDATA bd; bd.pISeqStream = ( ISequentialStream* )pss; bd.dwStatus = DBSTATUS_S_OK; bd.dwLength = pss->GetLength( ); // 将 BLOB 数据写入到数据库 hResult = pIRowsetChange->SetData( phRows[0], hAccessor, &bd ); pIAccessor->ReleaseAccessor( hAccessor, NULL ); pIRowset->ReleaseRows( uRowsObtained, phRows, NULL, NULL, NULL ); // pss was released by pIRowsetChange->SetData. 这样，我们就完成了一条记录的添加。读取BLOB字段的代码跟上面的完全类似，只要把 hResult = pIRowset->GetNextRows( NULL, 0, 1, &uRowsObtained, &phRows ); 后面的那些改成下面的代码即可。 BLOBDATA bd; hResult = pIRowset->GetData( phRows[0], hAccessor, &bd ); if( bd.dwStatus == DBSTATUS_S_ISNULL ) { // 此字段为空 } else if( bd.dwStatus != DBSTATUS_S_OK || bd.pISeqStream == NULL ) { // 失败 } else { // 从系统分配的 ISequentialStream 接口读入 BLOB 数据 BYTE szReadBuffer[1024]; for( ULONG uRead = 0U; ; ) { if( FAILED( bd.pISeqStream->Read( szReadBuffer, 1024, &uRead ) ) ) { break; } //szReadBuffer中就包含了BLOB字段的数据 if( uRead != 1024 ) { break; } } bd.pISeqStream->Release( ); } pIAccessor->ReleaseAccessor( hAccessor, NULL ); pIRowset->ReleaseRows( uRowsObtained, phRows, NULL, NULL, NULL ); 5. OLE DB Templates连接 使用OLE DB接口编程属于最低可能层，代码冗长并且很难维护。因此MS Visual Studio对OLE DB进一步抽象和封装，提供COM OLE DB Templates 这个可行的中间层，从而简化了OLE DB应用程序的编写。 OLE DB Templates编写客户数据库程序方法: <1>以MFC AppWizard为向导建立应用程序框架,添加OLE DB支持的头文件,然后使用OLE DB类进行数据库应用开发。 <2>以ATL COM AppWizard为向导建立应用程序框架,该框架直接支持OLE DB模板类。 OLE DB Templates包括:Consumer Templates和Provider Templates。 (1) Consumer Templates使用者模板 使用者模板(Consumer Templates)体系结构: (2) Provider Templates服务器模板 服务器模板类体系结构: 6. ADO连接 ADO(ActiveX Data Object,ActiveX数据对象)是MS为最新和最强大的数据访问接口OLE DB而设计,是一个便于使用的应用程序层接口。ADO是一种面向对象的、与语言无关的(Language_Neutral)数据访问应用编程接口。它对OLE DB API进行封装,实现对数据的高层访问,同时它也提供了多语言的访问技术，此外,由于ADO提供了访问自动化接口,它也支持脚本语言。ADO最主要的优点在于易于使用、速度快、内存支出少和磁盘遗迹小。ADO是用来访问OLE DB的数据库技术。在模型层次上它基于OLE DB,但在应用上又高于OLE DB,因此它简化了对对象模型的操作,并且不依赖于对象之间的相互层次关系。但是OLE的接口可以数据提供程序、服务提供程序和数据使用程序使用，而ADO所提供的对象只能被数据应用程序使用。并且,ADO对象使用了OLE DB服务提供程序和OLE DB数据提供程序所提供的接口和服务。 (1)ADO访问数据库的结构原理图: (2)ADO对象模型: ADO对象模型包括以下关键对象: <1>Connection对象:在数据库应用里操作数据源都通过该对象,这是数据交换的环境，代表与数据源的一个会话。 <2>Command对象:是一个对数据源执行命令的定义。 <3>Parameter对象:用于制定参数化查询或者存储过程的参数。 <4>Recordset对象:是执行结果集存储到本地的ADO对象。 <5>Field对象:ADO中对列进行操作的对象。 <6>Error对象:对ADO数据操作时发生错误的详细描述。 <7>Property对象:代表一个由提供者定义的ADO对象的动态特征。 ADO对象编程模型: Parameters Collection Execute Source Error Collection (Optional) Active Fields Connection Collection (3)ADO编程一般步骤: <1>创建一个Connection对象。 <2>打开数据源，建立同数据源的连接。 <3>执行一个SQL命令。 <4>使用结果集。 <5>终止连接。 (4)ADO的数据库访问规范 引入ADO支持 #import Program Files\Common Files\System\ado\msado*.dll 初始化和释放ADO环境: CoInitialize(NULL); CoUninitialize(); 封装的ADO类A set of ADO classes: 应用实例: 数据库操作准备 1、导入ADO动态链接库 在工程的stdafx.h中加入如下语句: #import "c:\program files\common files\system\ado\msado15.dll" no_namespace\ rename("EOF","adoEOF") 这一语句有何作用呢,其最终作用同我们熟悉的#include类似,编译的时候系统会为我们生成msado15.tlh和ado15.tli两个C++头文件来定义ADO库，即加载ADO动态库(msado15.dll)。 其中，no_namespace表明不使用命名空间，rename("EOF","adoEOF")表明把ADO中用到的EOF改为adoEOF，防止发生命名冲突。 注意:该代码需要在一行中完成，如果写成两行或者多行，行末要加上“\”符号，表示把这几行看成一行，如本例。 2、初始化OLE/COM库环境 在基于MFC的应用里，初始化OLE/COM库环境的一个比较好的位置是在应用类的InitInstance成员函数中，而且直接使用AfxOleInit，在退出应用时，该函数也负责COM资源的释放，将此函数添加在InitInstance中的如下位置: BOOL CExpApp::InitInstance() { AfxEnableControlContainer(); //初始化OLE DLLs if(!AfxOleInit()) { AfxMessageBox("初始化OLE DLL失败!"); Return FALSE; } ...... } 说明:也可以在InitInstance中使用::CoInitialize初始化OLE/COM库环境，但须在ExitInitInstance中使用::CoUninitialize释放占用的COM资源，显然使用AfxOleInit更为方便。 3、连接数据库 在Doc\View程序中，通常在应用类(App类)中进行数据库的连接。 1)声明一个Connection指针 _ConnectionPtr m_pConnection; 注:ADO最重要的三个对象有三个:连接对象(Connection)、命令对象(Command) 和记录集对象(RecordSet)。在使用这三个对象的时候，需要定义与之相对应的智能指针， 分别为_ConnectionPtr、_CommandPtr、_RecordsetPtr。 由上述ConnectionPtr指针的使用步骤可知，和C++中的类指针使用方法一样，智能 指针也要先定义指针变量、创建其实例(实例化)，然后就可以调用它的方法和属性。不同 的是，该智能指针最后是自动进行内存释放的。 所有的智能指针都是基于_com_ptr_t模板类的，该类封装了IUnknown接口的3个方 法:QueryInterface、Addref和Release。它具有自动计数的机制，即在构造对象时，自动 为该对象计数加1。析构对象时，自动调用Release方法。(即该类型的指针在使用后不需 要手动释放内存)(但需要调用Close方法，关闭连接或者记录集)所以智能指针会使代码 更加简洁并且不易出错。 2)创建Connection对象 m_pConnection.CreateInstance(__uuidof(Connection)); m_pConnection.CreateInstance("ADODB.Connection"); 上述两种方法均可。 注意:上面调用_ConnectionPtr接口指针的方法CreateInstance时，用的是 “.”而非“->”。 3)设置连接字符串，以便指定需要的连接 3.1) 使用JET数据库引擎实现对Acess2000类型的数据库info.mdb的连接 CString strSQL="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=info.mdb;User ID=admin;Passward=;"; 或者 CString strSQL=_T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=info.mdb;User ID=admin;Passward=;"); 3.2) 使用OLE DB提供者实现对SQL Server的标准安全连接串 strConnect=_T("Provider=sqloledb;Data Source=MyServerName;" "Initial Catalog=MyDateBaseName;" "User ID=MyUserName;Password=MyPassword;"); 例程: strConnection="Provider=SQLOLEDB;DataSource=local;InitialCatalog=DVDRentDB_Data .MDF;" "User ID=sa;Password=820415"; m_pConnection->Open((_bstr_t)strSQL,"","",adModeUnknown); 或者是在此处不设置User ID和Password，而直接在Open的第2、3个参数中设置。 strConnection="Provider=SQLOLEDB;DataSource=local;InitialCatalog=DVDRentDB_Data .MDF"; m_pConnection->Open((_bstr_t)strSQL,"sa","820415",adModeUnknown); 注意:上面设置连接字符串的时候，如果过长需要分行时，则每一行都要加上双引号，在最 后加上分号即可。 如果是本地服务器，则DataSource=local或本地服务器名均可 若数据库没有设置密码，在连接字符串中可以将其省略，但User ID不能省 若数据库和程序文件不在同一文件夹下，直接写数据库名即可，在InitialCatalog中不需加上该数据库的存储器地址 3.3) 使用OLE DB提供者实现对远程SQL Server的标准安全连接串 strConnect=_T("Provider=sqloledb;Network Library=DBMSSOCN;" "Data Source=130.120.110.001,1433;" "Initial Catalog=MyDateBaseName;" "User ID=MyUserName;Password=MyPassword;"); 4)、使用m_pConnection的Open方法实现对数据库的连接 在ADO的操作中建议使用try...catch( )来捕获错误信息，因为它有时会经常出现一些意想不到的错误 try { m_pConnection->Open( (_bstr_t) strSQL," "," ",adModeUnknown); } catch(_com_error e) //捕捉异常 { CString strError; strError.Format( "连接数据库发生异常! \r \n错误信息:%s",e.ErrorMessage( ) ); AfxMessageBox(errormessage); //显示错误信息 } 4、关闭连接 一般重载App类的ExitInstace( )函数实现 调用m_pConnection的Close方法关闭连接即可 m_pConnection->Close( ); m_pConnection=NULL; 注意:由于初始化COM库调用的是AfxOleInit，这种方法初始化COM库的优点就在于资源 的释放也是自动进行的，所以不必担心资源泄漏的问题。 数据库操作 ADO库中包含的三个基本接口为_ConnectionPtr接口、_CommandPtr接口、_RecordsetPtr接口。 1、_ConnectionPtr接口 该接口返回一个记录集或一个空指针。 通常用它来创建一个数据库连接，或执行一条不返回任何结果的SQL语句，如一个存储过程。 不推荐使用_ConnectionPtr接口返回一个记录集，对于要返回记录集的操作通常用_RecordsetPtr来实现，而且使用_ConnectionPtr时要想得到记录数目必须遍历所有记录，但使用_RecordsetPtr时则不需要。 2、_CommandPtr接口 该接口返回一个记录集。 它提供了一种简单的方法来执行返回记录集的存储过程和SQL语句。 在使用_CommandPtr接口时，可以利用全局_ConnectionPtr接口，也可以在_CommandPtr 接口里直接使用连接串。如果只执行一次或者几次数据库访问操作，后者是比较好的选择。但是，如果频繁访问数据库，并要返回很多记录集，那么应该使用全局_ConnectionPtr接口创建一个数据库连接，然后使用_CommandPtr接口执行存储过程和SQL语句。 3、_RecordsetPtr接口 该接口是一个记录集对象。 与前两种对象相比，它对记录集提供了更多的控制功能，如记录锁定、游标控制等。同_CommandPtr接口一样，它不一定要使用一个已经创建的数据库连接，可以用一个连接串代替连接指针赋给_RecordsetPtr的connection成员变量，让它自己创建数据库连接。如果使用多个记录集，最好的方法是同Command对象一样使用已经创建了数据连接的全局_ConnectionPtr接口，然后使用_RecordsetPtr执行存储过程和SQL语句。 注意:可以使用Recordset对象来执行查询命令，但如果查询或者存储过程是需要参数的，这时就只能使用Command对象。 使用Recordset对象操作数据库: 假定已经成功使用Connection对象创建了数据源的连接，连接指针为m_pConnection。 1)创建记录集 声明记录集指针 _RecordsetPtr m_pRecordset; 创建记录集 m_pRecordset.CreateInstance(__uuidof(Recordset)); 2)打开记录集 记录集指针创建完毕后，调用该指针的Open方法打开记录集。 该函数声明如下: HRESULT Recordset15::Open ( const _variant_t & Source, const _variant_t & ActiveConnection, enum CursorTypeEnum CursorType, enum LockTypeEnum LockType, long Options ) ; 各个参数的含义如下: 参数Source:为_variant_t类型的引用，可以为有效的Command对象、SQL语句、表名、存储过程调用等。 参数ActiveConnection:为_variant_t类型的引用，为已经建立好的连接。 参数CursorType:用于设置在打开Recordset时提供者应使用的游标类型，它可取CursorTypeEnum 中的任一值，默认值为adOpenForwardOnly。 参数 LockType:用于设置在打开Recordset时提供者应使用的锁定类型，它可取枚举LockTypeEnum中的任一值，默认值为adLockReadOnly。 参数 Options:用于设置获取Source(即Open第一个参数)的方式，其类型long。 例程1: CString strSQL = "select * from mytablename"; m_pRecordset->Open ( _variant_t (strSQL), m_pConnection.GetInterfacePtr( ), adOpenDynamic, adLockOptimistic, adCmdText ) ; 使用SQL语句作为Open方法的第一个参数Source的值，此时Options为 adCmdText 例程2: m_pRecordset->Open ( _variant_t ("tbDVDInfo") ), m_pConnection.GetInterfacePtr( ), adOpenDynamic, adLockOptimis tic, adCmdTable ) ; 直接使用表名作为第一个参数，此时Options应为adCmdTable 3)遍历记录集 一般在返回记录集时，通常要遍历结果记录集，以便查看或编辑某一条记录，Recoreset指针提供了几个用于实现遍历的方法。 注意:为了避免发生异常，一般在使用MoveFirst、MovePrev之前，需要使用记录集的指针BOF属性来检测当前的记录集指针是否位于第一条记录之前;在使用MoveLast、MoveNext之前需要使用记录集指针的EOF属性来检测当前的记录集指针是否位于最后一条记录之后。 4)记录集定位 记录集接口类提供了两种定位方法:绝对定位和书签定位 前者通过设置或者获取AbsolutePosition属性即可，其值从1开始，并且当前记录为记录集中第一条记录时等于1 对于后者可以通过设置或获取BookMark属性即可 5)访问记录集 最简单的方法是直接使用如下语句: m_pRecordset->GetCollect (字段名); 设置字段值: m_pRecordset->PutCollect (字段名,新值); 两个方法的原型: _variant_t GetCollect ( const _variant_t & Index ) void PutCollect ( const _variant_t & Index , const _variant_t &pvar ) 其中:参数Index可以是字符串表示字段名，也可以是整型，表示字段对应的序号。pvar表示要写入的变量值。 例如: _variant_t var; var=m_pRentRecordset->GetCollect("ID"); var=m_pRentRecordset->GetCollect(long(0)); 都可以 6)记录集更新 更新记录集包括添加新的记录、编辑当前记录和删除当前记录 记录集接口指针对这三种操作分别提供了相应的方法 添加新的记录:AddNew 编辑当前记录:Edit 删除当前记录:Delete 注意:记录集接口指针针对AddNew以及Edit方法提供了Update方法，用于在数据库中更新新添加或者编辑后的记录。 AddNew方法:用于添加新纪录(该添加是直接在表的末尾续加的)，该方法可以使用参数，在参数中指定要添加的新纪录;也可以不使用参数，而在后面使用PutCollect方法，并需使用Update函数保存新纪录。 Update方法:用于保存从调用AddNew方法以来所作的任何更改。 //++++++++++++++++++++++++++++++++++++++++++ //在表的末尾增加新纪录 m_pRecordset->AddNew(); //++++++++++++++++++++++++++++++++++++++++++ m_pRecordset->PutCollect("姓名",_variant_t(m_strName)); m_pRecordset->PutCollect("工作单位",_variant_t(m_strComName)); m_pRecordset->PutCollect("单位地址",_variant_t(m_strComAddr)); //更新数据库-将新纪录存入数据库 m_pRecordset->Update(); 7)记录集关闭 在对记录集的操作完成后，必须及时关闭记录集。 if ( m_pRecordset != NULL ) { m_pRecordset ->Close( ); m_pRecordset =NULL; } <2.>Oracle专用方法 1. OCI(Oracle Call Interface)访问 OCI(Oracle Call Interface)是由Oracle提供的一系列用于访问Oracles数据库服 务器的标准接口，它可以使用户将Oracle调用直接嵌入到高级语言中。 使用OCI应用程序访问数据库原理: 在高级语言中使用OCI编程的原理图: 用OCI开发Oracle客户端软件的一般流程: <1>初始化OCI编程环境 <2>分配必要的句柄,建立服务器连接和一个用户会话 <3>向服务器发出请求，进行必要的数据处理 <4>释放不再需要的语句和句柄 <5>终止会话和连接 2. Oracle Object OLE C++ Class Library 这个类库是一个提供编程接口访问Oracle对象服务器的C++类库，它是用OLE的方式实现的。Oracle提供的是一个进程内服务器，也就是服务器将与应用程序在同一个地址空间内, 它以DLL方式提供。应用程序在访问数据库之前必须先加载Oracle对象服务器(OStatup方法),然后与Oracle对象服务器通信，Oracle对象服务器其实是一些组件，它通过Oracle的OCI访问数据库。 Oracle对象服务器其实是一些COM组件，它通过Oracle的OCI访问数据库。 运用Oracle Objects for OLE C++ Class Library开发的步骤: 1>通过调用OStatup方法初始化类库。 2>连接数据库。 3>操纵数据库 断开数据库(类库自动为你自动执行) 4>通过调用OShutdown方法卸载类库。 <3>使用OTL进行数据库编程 OTL 是 Oracle, Odbc and DB2-CLI Template Library 的缩写，是一个C++编译中操控关系数据库的模板库，它目前几乎支持所有的当前各种主流数据库，例如Oracle, MS SQL Server, Sybase, Informix, MySQL, DB2, Interbase / Firebird, PostgreSQL, SQLite, SAP/DB, TimesTen, MS ACCESS等等。OTL中直接操作Oracle主要是通过Oracle提供的OCI接口进行，进行操作DB2数据库则是通过CLI接口来进行，至于MS的数据库和其它一些数据库，则OTL只提供了ODBC来操作的方式。当然Oracle和DB2也可以由OTL间接使用ODBC的方式来进行操纵。 在MS Windows and Unix 平台下，OTL目前支持的数据库版本主要有:Oracle 7 (直接使用 OCI7), Oracle 8 (直接使用 OCI8), Oracle 8i (直接使用OCI8i), Oracle 9i (直接使用OCI9i), Oracle 10g (直接使用OCI10g), DB2 (直接使用DB2 CLI), ODBC 3.x ,ODBC 2.5。OTL最新版本为4.0，参见，下载地址。 优点: a. 跨平台 b. 运行效率高，与C语言直接调用API相当 c. 开发效率高，起码比ADO.net使用起来更简单，更简洁 d. 部署容易，不需要ADO组件，不需要.net framework 等 缺点: a. 说明文档以及范例不足够丰富(暂时性的) 其实现在它提供有377个使用范例可参考，下载地址: 。 建立数据源 1(依次点击“开始->控制面板”，打开“控制面板”界面，双击“管理工具”，然后再双击“数据源(ODBC)”，就打开了“ODBC数据源管理器”，选择“系统DSN”。 2(单击“添加”，弹出“创建新数据源”对话框，选择“Microsoft Access Driver(*.mdb)”。 3(点击“完成”，弹出“ODBC Microsoft Access安装”对话框，单击“创建”，开始创建数据库，弹出“新建数据库”对话框，添加数据库名称my_db和选择数据库存放目录，单击“确定”，创建完成，然后添加数据源名:my_db。点击“确定”。 4(然后在系统数据源中就有我们刚才添加的数据源。 5(单击“确定”，完成数据源的创建。 OTL编程 下面我们用一个实例来说明: 1( 创建数据表:TestTable ( ColumA int , ColumB varchar(50),ColumC varchar(50) ) 2( 插入100条数据，ColumA 为数据的 id 范围:0-99 ， ColumB=”Test Data %d” , 其中 %d=id 。 3( 删除表中ColumA 中小于10和大于90的数据。 4( 将ColumA为3的倍数的记录中ColumC更新为ColumB的内容。 具体代码为: #include using namespace std; #include #include #include #define OTL_ODBC // 编译 OTL 4.0/ODBC // #define OTL_ODBC_UNIX // 如果在Unix下使用UnixODBC，则需要这个宏 #include "otlv4.h" // 包含 OTL 4.0 头文件 otl_connect db; // 连接对象 //此函数完成插入100条数据，ComulA为数据的id，范围为0-99， //ColumB="Test Data %d",其中%d=id void insert() // 向表中插入行 { // 打开一个通用的流，以模板的方式向表中插入多项数据 otl_stream o(1, // 流的缓冲值必须设置为1 "insert into TestTable values(:f1,:f2,:f3)", // SQL 语句 db // 连接对象 ); char tmp1[32]; char tmp2[30]; for(int i=0;i<100;++i){ sprintf(tmp1,"Test Data %d",i); sprintf(tmp2,""); o<90"); // rpc是作用效果的返回值，otl_cursor::direct_exec为直接执行sql语句 cout<<"Rows deleted: "< " " WHERE ColumA=:f1", // UPDATE 语句 db // 连接对象 ); otl_stream c(1,"select ColumB from TestTable where ColumA=:f3",db); char temp[10]; for(int i=10;i<91;i++) { if(i%3==0) { c << i; c >> temp; o << temp << i; } } } int main() { otl_connect::otl_initialize(); // 初始化 ODBC 环境 try{ db.rlogon("UID=scott;PWD=tiger;DSN=my_db"); // 连接到 ODBC //或者使用下面的连接语句方式。 // db.rlogon("scott/tiger@firebird"); // connect to ODBC, alternative format // of connect string otl_cursor::direct_exec ( db, "drop table TestTable", otl_exception::disabled // disable OTL exceptions ); // drop table //这里完成表的创建 otl_cursor::direct_exec ( db, "create table TestTable(ColumA int, ColumB varchar(50),ColumC varchar(50))" ); // create table insert(); // insert records into the table // update(10); // update records in the table delete_rows(); update(); } catch(otl_exception& p){ // intercept OTL exceptions cerr< ,< ，> =,< =)和order by、group by发生的列，可考虑建立群集索引;?.经常同时存取多列，且每列都含有重复值可考虑建立组合索引;?.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。 ?2、IS NULL 与 IS NOT NULL 不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。 ?3、IN和EXISTS EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN 操作符子查询改写为使用EXISTS的子查询。 ?4、在海量查询时尽量少用格式转换。 ?5、当在SQL SERVER 2000中，如果存储过程只有一个参数，并且是OUTPUT类型的，必须在调用这个存储过程的时候给这个参数一个初始的值，否则会出现调用错误。 ?6、ORDER BY和GROPU BY 使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，Optimizer将无法优化。 ?7、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。 ?8、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。 ?9、SET SHOWPLAN_ALL ON 查看执行 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 。DBCC检查数据库数据完整性。 DBCC(DataBase Consistency Checker)是一组用于验证 SQL Server 数据库完整性的程序。 ?10、慎用游标 在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。 一些常用的SQL语句供大家参考，希望对大家有所帮助。 说明:存储过程的使用,CREATE PROC 创建存储过程,SQL2000中用sp_xxx和xp_xxx存储过程;一般来说,sp_xxx是一般的存储过程,而xp_xxx是扩展的存储过程。使用这些系统存储过程时，一般使用USE MASTER然后在使用sp_xxx或者xp_xxx。 说明:复制表(只复制结构,源表名:a 新表名:b) SQL: select * into b from a where 1<>1 说明:拷贝表(拷贝数据,源表名:a 目标表名:b) SQL: insert into b(a, b, c) select d,e,f from b; 说明:显示文章、提交人和最后回复时间 SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 说明:外连接查询(表名1:a 表名2:b) SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 说明:日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 说明:两张关联表，删除主表中已经在副表中没有的信息 SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid 说明:-- SQL: SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1, (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ?? '/01','YYYY/MM/DD') - 1, 'YYYY/MM') Y, WHERE X.NUM = Y.NUM (+) AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND B WHERE A.NUM = B.NUM 说明:-- SQL: select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称 ='"&strprofessionname&"' order by 性别,生源地,高考总成绩 说明: 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源) SQL: SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax) a GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') 说明:四表联查问题: SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 说明:得到表中最小的未使用的ID号 SQL: SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a) 三、数据库优化 1、索引问题 在做性能跟踪分析过程中，经常发现有不少后台程序的性能问题是因为缺少合适索引造成的，有些表甚至一个索引都没有。这种情况往往都是因为在设计表时，没去定义索引，而开发初期，由于表记录很少，索引创建与否，可能对性能没啥影响，开发人员因此也未多加重视。然一旦程序发布到生产环境，随着时间的推移，表记录越来越多，这时缺少索引，对性能的影响便会越来越大了。 这个问题需要数据库设计人员和开发人员共同关注 法则:不要在建立的索引的数据列上进行下列操作: ?避免对索引字段进行计算操作 ?避免在索引字段上使用not，<>，!= ?避免在索引列上使用IS NULL和IS NOT NULL ?避免在索引列上出现数据类型转换 ?避免在索引字段上使用函数 ?避免建立索引的列中使用空值。 2、在可以使用UNION ALL的语句里，使用了UNION UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。还有一种情况大家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉重复记录，但由于脚本的特殊性，不可能存在重复记录，这时便应该使用UNION ALL，如xx模块的某个查询程序就曾经存在这种情况，见，由于语句的特殊性，在这个脚本中几个子集的记录绝对不可能重复，故可以改用UNION ALL) 3、对Where 语句的法则 3.1 避免在WHERE子句中使用in，not in，or 或者having。 可以使用 exist 和not exist代替 in和not in。 可以使用表链接代替 exist。Having可以用where代替，如果无法代替可以分两步处理。 例子 SELECT * FROM ORDERS WHERE CUSTOMER_NAME NOT IN (SELECT CUSTOMER_NAME FROM CUSTOMER) 优化 SELECT * FROM ORDERS WHERE CUSTOMER_NAME not exist (SELECT CUSTOMER_NAME FROM CUSTOMER) 3.2 不要以字符格式声明数字，要以数字格式声明字符值。(日期同样)否则会使索引无效，产生全表扫描。 例子使用: SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369; 不要使用:SELECT emp.ename, emp.job FROM emp WHERE emp.empno = ‘7369’ 4、对Select语句的法则 在应用程序、包和过程中限制使用select * from table这种方式。看下面例子 使用SELECT empno,ename,category FROM emp WHERE empno = '7369‘ 而不要使用SELECT * FROM emp WHERE empno = '7369' 5、排序 避免使用耗费资源的操作，带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。 优化SQL Server数据库方法: 查询速度慢的原因很多，常见如下几种: 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大(可以采用多次查询，其他的方法降低数据量) 7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) 8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。 9、返回了不必要的行和列 10、查询语句不好，没有优化 可以通过如下方法来优化查询 : 、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb 1 应放在RAID0上，SQL2000不在支持。数据量(尺寸)越大，提高I/O越重要. 2、纵向、横向分割表，减少表的尺寸(sp_spaceuse) 3、升级硬件 4、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小，使用字节数小的列建索引好(参照索引的创建),不要对有限的几个值的字段建单一索引如性别字段。 5、提高网速; 6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。配置虚拟内存:虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑:将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍(虚拟内存大小设置的一半)。 7、增加服务器 CPU个数;但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作Update,Insert， Delete还不能并行处理。 8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查询时，查询耗时和字段值总长 度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。 9、DB Server 和APPLication Server 分离;OLTP和OLAP分离 10、分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。(参照SQL帮助文件'分区视图') a、在实现分区视图之前，必须先水平分区表 b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。 11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。在T-sql的写法上有很大的讲究，下面列出常见的要点:首先，DBMS处理查询计划的过程是这样的: 1、 查询语句的词法、语法检查 2、 将语句提交给DBMS的查询优化器 3、 优化器做代数优化和存取路径的优化 4、 由预编译模块生成查询规划 5、 然后在合适的时间提交给系统处理执行 6、 最后将执行结果返回给用户其次，看一下SQL SERVER的数据存放的结构:一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。 12、Commit和rollback的区别 Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如: begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。 13、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。 14、SQL的注释申明对执行没有任何影响 15、尽可能不使用光标，它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如:在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。游标可以按照它所支持的提取选项进行分类: 只进 必须按照从第一行到最后一行的顺序提取行。FETCH NEXT 是唯一允许的提取操作,也是默认方式。可滚动性可以在游标中任何地方随机提取任意行。游标的技术在SQL2000下变得功能很强大，他的目的是支持循环。有四个并发选项 READ_ONLY:不允许通过游标定位更新(Update)，且在组成结果集的行中没有锁。 OPTIMISTIC WITH valueS:乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用于这样的情形，即在打开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一行。当某个游标以此选项打开时，没有锁控制其中的行，这将有助于最大化其处理能力。如果用户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。如果任何值发生改变，则服务器就会知道其他人已更新了此行，并会返回一个错误。如果值 OPTIMISTIC WITH ROW VERSIONING: 此乐观并发控制选项基于行版本控制。使用行版本控制，其中的表必须具有某种版本标识符，服务器可用它来确定该行在读入游标后是否有所更改。在 SQL Server 中，这个性能由 timestamp 数据类型提供，它是一个二进制数字，表示数据库中更改的相对顺序。每个数据库都有一个全局当前时间戳值:@@DBTS。每次以任何方式更改带有 timestamp 列的行时，SQL Server 先在时间戳列中存储当前的 @@DBTS 值，然后增加 @@DBTS 的值。如果某 个表具有 timestamp 列，则时间戳会被记到行级。服务器就可以比较某行的当前时间戳值和上次提取时所存储的时间戳值，从而确定该行是否已更新。服务器不必比较所有列的值，只需比较 timestamp 列即可。如果应用程序对没有 timestamp 列的表要求基于行版本控制的乐观并发，则游标默认为基于数值的乐观并发控制。 SCROLL LOCKS 这个选项实现悲观并发控制。在悲观并发控制中，在把数据库的行读入游标结果集时，应用程序将试图锁定数据库行。在使用服务器游标时，将行读入游标时会在其上放置一个更新锁。如果在事务内打开游标，则该事务更新锁将一直保持到事务被提交或回滚;当提取下一行时，将除去游标锁。如果在事务外打开游标，则提取下一行时，锁就被丢弃。因此，每当用户需要完全的悲观并发控制时，游标都应在事务内打开。更新锁将阻止任何其它任务获取更新锁或排它锁，从而阻止其它任务更新该行。然而，更新锁并不阻止共享锁，所以它不会阻止其它任务读取行，除非第二个任务也在要求带更新锁的读取。滚动锁根据在游标定义的 Select 语句中指定的锁提示，这些游标并发选项可以生成滚动锁。滚动锁在提取时在每行上获取，并保持到下次提取或者游标关闭，以先发生者为准。下次提取时，服务器为新提取中的行获取滚动锁，并释放上次提取中行的滚动锁。滚动锁独立于事务锁，并可以保持到一个提交或回滚操作之 后。如果提交时关闭游标的选项为关，则 COMMIT 语句并不关闭任何打开的游标，而且滚动锁被保留到提交之后，以维护对所提取数据的隔离。所获取滚动锁的类型取决于游标并发选项和游标 Select 语句中的锁提示。锁提示 只读 乐观数值 乐观行版本控制 锁定无提示 未锁定 未锁定 未锁定 更新 NOLOCK 未锁定 未锁定未锁定 未锁定 HOLDLOCK 共享 共享 共享 更新 UPDLOCK 错误 更新 更新 更新 TABLOCKX 错误 未锁定 未锁定更新其它 未锁定 未锁定 未锁定 更新 *指定 NOLOCK 提示将使指定了该提示的表在游标内是只读的。 16、用Profiler来跟踪查询，得到查询所需的时间，找出SQL的问题所在;用索引优化器优化索引 17、注意UNion和UNion all 的区别。UNION all好 18、注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢。重复的记录在查询里是没有问题的 19、查询时不要返回不需要的行、列 20、用sp_configure 'query governor cost limit'或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询,在查询之前就扼杀掉。 SET LOCKTIME设置锁的时间 21、用select top 100 / 10 Percent 来限制用户返回的行数或者SET ROWCOUNT来限制操作的行 22、在SQL2000以前，一般不要用如下的字句: "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'"，因为他们不走索引全是表扫描。也不要在Where字句中的列名加函数，如Convert，substring等,如果必须用函数的时候，创建计算列再创建索引来替代.还可以变通写法:Where SUBSTRING(firstname,1,1) = 'm'改为Where firstname like 'm%'(索引扫描)，一定要将函数和列名分开。并且索引不能建得太多和太大。NOT IN会多次扫描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用，现在2000的优化器能够处理了。相同的是IS NULL，"NOT", "NOT EXISTS", "NOT IN"能优化她，而"<>"等还是不能优化，用不到索引。 23、使用Query Analyzer，查看SQL语句的查询计划和评估分析是否是优化的SQL。一般的20%的代码占据了80%的资源，我们优化的重点是这些慢的地方。 24、如果使用了IN或者OR等时发现查询没有走索引，使用显示申明指定索引: Select * FROM PersonMember (INDEX = IX_Title) Where processid IN ('男'，'女') 25、将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段。例如医院的住院费计算。 26、MIN() 和 MAX()能使用到合适的索引。 27、数据库有一个原则是代码离数据越近越好，所以优先选择Default,依次为 Rules,Triggers, Constraint(约束如外健主健CheckUNIQUE„„,数据类型的最大长度等等都 是约束),Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快。 28、如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌Insert来插 入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍)， 服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作: 方法:Create procedure p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参 数，这样处理速度明显改善。 29、Between在某些时候比IN 速度更快,Between能够更快地根据索引找到范围。用查 询优化器可见到差别。 select * from chineseresume where title in ('男','女') Select * from chineseresume where between '男' and '女' 是一样的。由于in会在比较多次，所以有时会慢些。 30、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样， 因为索引也耗费大量的资源。他的创建同是实际表一样。 31、不要建没有作用的事物例如产生报表时，浪费资源。只有在必要使用事物时使用它。 32、用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度 只同是否使用索引有关,如果查询需要用到联合索引，用UNION all执行的效率更高.多个OR 的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到 索引。 33、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的 本质:它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数 据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增 加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。 34、没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它 们增加了额外的开销。这同UNION 和UNION ALL一样的道理。 select top 20 ad.companyname,comid,position,ad.referenceid,worklocation, convert(varchar(10),ad.postDate,120) as postDate1,workyear,degreedescription FROM jobcn_query.dbo.COMPANYAD_query ad where referenceID in('JCNAD00329667','JCNAD132168','JCNAD00337748','JCNAD00338345', 'JCNAD00333138','JCNAD00303570','JCNAD00303569', 'JCNAD00303568','JCNAD00306698','JCNAD00231935','JCNAD00231933', 'JCNAD00254567','JCNAD00254585','JCNAD00254608', 'JCNAD00254607','JCNAD00258524','JCNAD00332133','JCNAD00268618', 'JCNAD00279196','JCNAD00268613') order by postdate desc 35、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。 36、当用Select INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是 select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ' --commit 在另一个连接中Select * from sysobjects可以看到 Select INTO 会锁住系统表，Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它～～～这样的话如果是经常要用的临时表请使用实表，或者临时表变量。 37、一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优:select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By 个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快 38、一次更新多条记录比分多次更新每次一条快,就是说批处理好 39、少用临时表，尽量用结果集和Table类性的变量来代替它,Table 类型的变量比临时表好 40、在SQL2000下，计算字段是可以索引的，需要满足的条件如下: a、计算字段的表达是确定的 b、不能用在TEXT,Ntext，Image数据类型 c、必须配制如下选项 ANSI_NULLS = ON, ANSI_PADDINGS = ON, „„. 41、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程(临时表)被放在Tempdb中。以前由于SQL SERVER对复杂的数学计算不支持，所以不得不将这个工作放在其他的层上而增加网络的开销。SQL2000支持UDFs,现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量的资源，如果返回大的结果采用存储过程 42、不要在一句话里再三的使用相同的函数，浪费资源,将结果放在变量里再调用更快 43、Select COUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请注意区别: select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的返回值是不同的～～～ 44、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率;否则使用 配制线程数量<最大连接数启用SQL SERVER的线程池来解决,如果还是数量 = 最大连接数+5，严重的损害服务器的性能。 45、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你(不经意的)某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁很难被发现 46、通过SQL Server Performance Monitor监视相应硬件的负载 Memory: Page Faults / sec 计数器如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。 Process: 、% DPC Time 指在范例间隔期间处理器用在缓延程序调用(DPC)接收和提供服务的百 1 分比。(DPC 正在运行的为比标准间隔优先权低的间隔)。 由于 DPC 是以特权模式执行的，DPC 时间的百分比为特权时间百分比的一部分。这些时间单独计算并且不属于间隔计算总数的一部 分。这个总数显示了作为实例时间百分比的平均忙时。 2、%Processor Time计数器 如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。 3、% Privileged Time 指非闲置处理器时间用于特权模式的百分比。(特权模式是为操作系统组件和操纵硬件驱动程序而设计的一种处理模式。它允许直接访问硬件和所有内存。另一种模式为用户模式，它是一种为应用程序、环境分系统和整数分系统设计的一种有限处理模式。操作系统将应用程序线程转换成特权模式以访问操作系统服务)。特权时间的 % 包括为间断和 DPC 提供服务的时间。特权时间比率高可能是由于失败设备产生的大数量的间隔而引起的。这个计数器将平均忙时作为样本时间的一部分显示。 4、% User Time表示耗费CPU的数据库操作，如排序，执行aggregate functions等。如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。 Physical Disk: Curretn Disk Queue Length计数器该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。 SQLServer:Cache Hit Ratio计数器该值越高越好。如果持续低于80%，应考虑增加内存。 注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。 47、分析select emp_name form employee where salary > 3000 在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。 48、查询的关联同写的顺序 select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' (A = B ,B = '号码') select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' (A = B ,B = '号码'， A = '号码') select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' (B = '号码'， A = '号码') 49、 (1)IF 没有输入负责人代码 THEN code1=0 code2=9999 ELSE code1=code2=负责人代码 END IF 执行SQL语句为: Select 负责人名 FROM P2000 Where 负责人代码>=:code1 AND 负责人代码 <=:code2 (2)IF 没有输入负责人代码 THEN Select 负责人名 FROM P2000 ELSE code= 负责 人代码 Select 负责人代码 FROM P2000 Where 负责人代码=:code END IF 第一种方法只 用了一条SQL语句,第二种方法用了两条SQL语句。在没有输入负责人代码时,第二种方法 显然比第一种方法执行效率高,因为它没有限制条件; 在输入了负责人代码时,第二种方法仍 然比第一种方法效率高,不仅是少了一个限制条件,还因相等运算是最快的查询运算。我们写 程序不要怕麻烦 50、关于JOBCN现在查询分页的新方法(如下)，用性能优化器分析性能的瓶颈，如 果在I/O或者网络的速度上，如下的方法优化切实有效，如果在CPU或者内存上，用现在 的方法更好。请区分如下的方法，说明索引越小越好。 begin DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20)) insert into @local_variable (ReferenceID) select top 100000 ReferenceID from chineseresume order by ReferenceID select * from @local_variable where Fid > 40 and fid <= 60 end 和 begin DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20)) insert into @local_variable (ReferenceID) select top 100000 ReferenceID from chineseresume order by updatedate select * from @local_variable where Fid > 40 and fid <= 60 end 的不同 begin create table #temp (FID int identity(1,1),ReferenceID varchar(20)) insert into #temp (ReferenceID) select top 100000 ReferenceID from chineseresume order by updatedate select * from #temp where Fid > 40 and fid <= 60 drop table #temp end 存储过程编写经验和优化措施 一)、适合读者对象:数据库开发程序员，数据库的数据量很多，涉及到对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数据库优化 结构化查询语言(SQL)对于关系型DBMS是把双刃剑，利弊参半。因为从关系型数据库检索任何数据都需要SQL，本文所要探讨的话题就是:不论是终端用户还是开发人员或是数据库管理员(DBA)，他们将如何访问一个关系型数据库。当使用高效的SQL 时,系统会变得易于升级、灵活、 而且便于管理。当使用低效的SQL 时, 响应时间和程序运行时间都会延长，并且还会产生应用系统的中断。鉴于通常的数据库系统一般要花费90% 的处理时间用于从数据库检索数据，由此很明显的可以看出尽可能的保证SQL的高效是多么的重要。考察通常的SQL语句 问题譬如"SELECT * FROM"仅是冰山一角,我们将在本文中探讨其他容易确定的普遍的问题。需要记住的是, 检索得到同一数据的SQL 语句有很多种殊途同归的写法，所以不存在好的查询语句或是坏的查询语句，而只有满足适当需求的查询语句。各关系型数据库都有自己的方式来优化和执行查询语句。因此，各DBMS都拥有自己的最佳性能的查询技巧。本文将使用 Quest 软件中Quest Central for DB2 的例子和概述来集中讨论 DB2 for OS/390 和z/0S。 要是在十七年前，这张技巧单会更长，并且会包含对最小化的 SELECT 场景的矫正方法。每一个新版本的DB2 都会增加成千上万行的新代码，用以扩展智能优化，和查询重写及执行。例如，多年来一种被称为数据管理器的组件, 通常被提供作为"第一阶段处理"以增加它的过滤容量一百倍。另一组件是关系型数据服务器，通常被提供作为"第二阶段处理"来进行其主函数的查询重写和优化。另一关键组件就是基于当前的SQL，并使用存取路径以决定检索数据的DB2 优化器。DB2 优化器改善了每一个DB2 的版本, 考虑到另外的DB2目录中的统计, 可以提供新的和改善过的存取路径。图1显示了这些组件及其他更多的部分，并描述了DB2 如何处理数据或SQL的请求。这就是以下DB2 SQL性能技巧的来源。 图1:DB2 Engine 和一些组件介绍 在这篇文章中，我们将回顾一些更具有代表性的SQL 问题，有更多的SQL方面的性能技巧超出了本篇文章描述的范围。像所有指导方针一样，所有这些技巧也会有一些例外。 技巧1: 核实是否提供了适当的统计 对于DB2 优化器来说，最重要的资源除了SELECT语句本身，就是DB2 目录中创建的统计。优化器基于众多的选择而使用这些统计。DB2 优化器为了查询而选择一条非最佳存取路径的主要原因，归结于无效的或缺失的统计。DB2优化器使用以下目录统计: 图2:DB2 优化器验证过的列和用来确定的存取路径 经常的执行"RUNSTATS"命令，用来更新DB2的目录统计，这样可以在特别繁忙的生产环境里中得到全貌。为了使执行"RUNSTATS"命令的影响最小化，可以考虑使用采样技术。即使取样10%也够了。另外"RUNSTATS"命令可以更新统计，DB2给您可以额外更新1,000 个条 目的能力，以用于不均匀的分类统计。当心随着每一条目随着增量的增加，而涉及到对所有参考的绑定时间的影响。 假如当您缺少统计的时候您怎么知道呢? 当目录或使用工具不能提供这种功能的时候，您可以通过手工执行查询。 当前, DB2优化器不能给缺失的统计提供具体的警告。 技巧2: 尽可能的采用阶段1和阶段2 的谓词 不论是阶段1的数据管理器还是阶段2 的关系型数据服务器都将处理每一次查询。当您处理查询时，使用阶段1将会比使用阶段2有着巨大的性能优势。当谓词确定阶段1能够处理的时候，通常谓词会限制您只能使用阶段1查询。另外, 每一个谓词都会被检验评估是否比另一个谓词更有资袼作为索引路径。有一些谓词不能作为阶段1来处理，或是不符合索引的条件。关于您的查询是否可以被索引并且能够在阶段1被处理，理解这一点是很重要的。下面是文挡化的阶段1或Sargable(search + argument-able 谓词是一个可以由数据管理器来值的谓词)谓词: 图3:通常用表单来确定谓词是否合格 还有一些谓词不能看作阶段1被文档化，因为他们不能总处于阶段1。加入表序列和查询重写也能够影响谓词被过滤掉的阶段。让我们通过例子查询来显示重写您的SQL的影响。 例子1:COL1 和COL1 之间的值 任何类型的谓词如不能被阶段1识别，就是阶段2。如下所示就是阶段2 谓词。然而, 重写可能促进对可索引阶段1的查询。 Value >= COL1 AND value <= COL2 这意味着, 优化器也许会在多个索引中选择一个匹配的索引来使用谓词。 没有重写, 谓词的剩余被当作阶段2 。 例子2: COL3 NOT IN (K,S,T) 如果可能，非可索引的阶段1 的谓词也应该被重写。例如，符合以上条件的是阶段1，但不是可索引的。括号里值的列表辨认什么与COL3 不相等。为了确定重写的可行性，辨认出那些COL3不相等的、更长和更不稳定的表单，就越不具有可行性。如果对面的(K, S, T) 是少于200的静态值,就值得输入额外的重写。促进阶段1 的条件对于可索引的阶段1，提供了其它匹配索引选择的优化器。既使一个可支持的索引在绑定时间不可利用, 重写也将确保查询具有索引访问的资格，并且此索引将在以后被创建。一旦一个索引被创建并与COL3合并, 重新绑定的事务也许可能获得匹配的索引访问, 那里的旧谓词将不会对重新绑定有影响。 技巧3: 仅选择需要的列 每一个被选择的列必须单独地被传回到调用程序，除非对整个的DCLGEN 定义有精确匹配的。这也可能依赖于您向所有列发出的请求，但是，真正的损失发生在需要排序的时候。每一个被SELECTed的列, 和重复的排序列，使得排序文件的宽度更宽。文件越长越宽,排序越慢。例如，100,000 个四字节的列可能在大约一秒的时间内完成排序。而只有10,000个五十字节的列可能在同样时间内完成排序。 实际的时间是非常依赖于硬件的。 这个规则的例外是"Disallow SELECT*"，当几个处理需要一个表中行的不同的部分的时候。通过事务的整合, 一次取回所有行, 然后单独处理这些部分。 技巧 4: 选择唯一需要的行 越少的行被检索,查询将运行的越快。 符合要求的行不得不令自己在存储器中通过漫长之旅, 穿过缓冲池, 阶段1, 阶段2,可能的分类和转换，然后传递结果集到调用程序。数据库管理器管理所有的数据过滤;这对于检索一行是非常浪费的，测试在程序代码里的那一 行，然后过滤掉那行。禁止程序自动过滤是一个必须强制执行的铁的规则。开发商可能选择使用程序代码执行所有或部分的数据操作或者他们可能选择使用SQL 。典型地是混合在一起。 已知的叙述显示，过滤器可能被放入DB2 engine里的程序代码，类似: IF TABLE-COL4 > :VALUE GET NEXT RESULT ROW 技巧 5: 使用常量和字面值，如果值在以后的3 年中不改变(对于静态查询) DB2 优化器对所有不均匀的分类统计都充分的使用, 并为任何一个列统计提供了不同领域范围内的值，尤其当没有主机变量在谓词中被发现时, (WHERE COL5 >'X')。 主机变量的目的是使一个事务能适应一个可变化的变量;当一个用户请求输入这个值的时候是最经常被使用的。 主机变量不需要重新绑定一个程序，当这个变量每一次改变的时候。 这种可延伸性能得到优化器准确的耗费。 当主机变量刚被发现, (WHERE COL5 >:hv5), 优化器使 而不是使用目录统计: 用以下的图表来评估过滤器要素, 图 4:过滤器要素 列的基数性越高, 则谓词的过滤器要素就越低(保留部分行的预测) 。 多数时候，这种评估有助于优化器对适当存取路径的选取。然而, 有时谓词的过滤器要素远离实际。这就是通常需要对存取路径进行调优的时候。 解决方案 Quest Central for DB2是一个集成的控制台，可以提供核心功能，DBA (数据库管理员)需要执行他们日常的数据库管理任务, 空间管理, SQL调优和分析, 并且可以进行性能诊断监视。Quest Central for DB2 是由DB2 软件专家撰写的，并且提供具丰富的功能，以利于视图化的用户界面，并且支持在Unix, Linux,和windows主机上运行DB2 数据库。DB2 的客户不再被要求用独立的工具维护和使用他们的主机和分布式的DB2 系统。 Quest Central 的SQL调优组件提供一个完整的SQL 调优环境。 Quest Central是唯一可以提供完整的SQL 调优环境的针对DB2可用的产品。这个环境包括以下部分: 1: 调优实验室:通过场景的使用，一个单独的SQL 语句能够被改进很多次。然后这些场景能够立刻被比较以确定哪个SQL 语句提供了最有效率的存取路径。 2. 比较: 您立刻可以看出对于SQL 语句修改的性能改变效果。 由于比较多个场景, 您能看到对CPU 的效果, 消耗的时间, I/O 和其他更多的统计。 另外数据的比较将保证您的SQL 语句返回相同的数据子集。 3. 建议:由SQL 调优组件提供的建议，将会发现所有的在白皮书指定的条件等等。 另外, 如果一个新场景可以利用，SQL 调优组件甚至将会重写SQL ，并综合选择的建议。 4. 存取路径和对应的统计:在SQL的上下文中，对于DB2存取路径，所有适合的统计应被显示出来。 采取推测以设法理解为什么选择一个特殊的存取计划。 Quest Central for DB2 健壮的功能显现了上述SQL 调优中的技巧以及更多。 这篇白皮书剩余的部分将证明 Quest Central 是由更丰富和更透彻的知识恰当的组成的。 Quest Central 不仅可以提高您的SQL语句效率，更可以帮助您全面的提升数据库的性能。上面描述的各种调优技巧都被Quest Central所包括。 解决的技巧 1: 核实特定提供的统计 一旦一条SQL 语句在Quest Central中被描述，建议栏会提供一整套建议，包括当没有RUNSTATS时也可以发现的能力。Quest Central 一直以坚定的决心来探究这类建议。每一条建议都有相对应的"建议操作"。这种建议操作会指导如何矫正建议发现的问题。这将会打开一个新的场景由被重写的SQL或以促进对象分析的脚本组成。在这个例子中, 建议显示, 统计的缺失和相对应的建议操作将建立一个脚本，它包含RUNSTATS 命令，为了在建议操作的窗口中选择任何一个对象。 图6: SQL 调优的组件鉴别所有对象缺失的统计, 并且能够生成必要的命令对所有选择对象的统计进行更新。 另外, Quest Central Space 的管理能够自动的收集、维护和检验在表空间里的统计及表和索引等级。以下的例子显示了在数据库里所有表空间里的统计检验 报告 软件系统测试报告下载sgs报告如何下载关于路面塌陷情况报告535n,sgs报告怎么下载竣工报告下载 。 图7: Quest Central提供了一套容易使用的图形界面，以促进RUNSTATS 处理的自动化。 解决的技巧 2: 尽可能的提升阶段2 和 阶段1的谓词 SQL 的调优组件将列出所有的谓词并指出那些谓词是否是"Sargable" 或"Non-Sargable" 。 另外, 各个谓词都将被检查，以确定它是否具有索引存取的资袼。 这种单独的建议可以解决响应时间的问题和在谓词重写的期间内得到某些成果。在下面的例子中,一条查询被看作non-sargable 和non-indexable (阶段2) 。 这条最初的查询被输入在一个谓词间。 一个新场景被打开了并且谓词被重写使用大于, 小于符号。 这种比较确定了查询重写对性能方面的影响。 图8: Query that is non-indexable and non-sargable(stage 2) 一个新场景被创建并且查询被重写在列值中使用" a> = "和"a< = "。 注意，谓词现 在是可索引的和sargable 。 记住以上的信息, 谓词现在将由数据管理器(阶段1)处理，以 减少这次查询的潜在响应时间。 图9: 查询是indexable 和sargable (阶段1) 随后可以使用比较工具来比较他们和 "<>"之间的性能, 会发现"<>"更有效的减少消 耗的时间。 图10: 耗时减半 解决的技巧 3:选择唯一需要的列 SQL 调优的特点不仅是相对于使用"SELECT*"的建议, 更提供一个事半功倍的可以令产品自动重写SQL 的特点。 建议和相对应的建议操作将提供重写您SQL的能力，简单地检查想要的列并选择"apply advice"按钮, SQL调优将用被选择的列替换"*"。 图11: "apply advice" 的特点是将重写SQL ，并重视选择的建议操作。 解决的技巧 4:选择唯一需要的行 越少的行被检索, 查询将运行的越快。使用Quest Central能比较您最初的SQL 相对于选择较少行但相同的SQL 语句。使用多个场景和利用比较特点, 比较那些立刻显示发生变化的性能影响的场景。 在以下例子中，两张表单的加入，产生了一个有意义的结果集。 由于加入了"Fetch First 1Row Only'"执行时间显著的减少了。 图12: 为了减少行数而修改一个SELECT语句, 用来比较确认性能的受益 解决的技巧 5:使用常量和字面值，如果值在以后的3 年中不改变(对于静态查询) 在这个例子中，让我们进行一个基于Win2K 平台的DB2测试。 当使用主机变量时, DB2 优化器无法预测谓词过滤的值。没有这个值, DB2 将默认并使用上面列出的默认的过滤器要素。Quest Central SQL 调优将一直显示过滤器要素用以帮助了解有多少列将被过滤。 图13: Quest Central显示每个谓词的过滤器要素 技巧 6: 使数字和数据日期类型相匹配 在先前的版本中，对于处理谓词和比较数据类型长度的变化，阶段1 处理是非常精确的。在DB2 v7 之前, 这种不匹配导致了谓词被降级在阶段2 处理。 但是，一个DB2 v7的新特点允许数字化的数据类型可以被手动的转换以避免阶段2 降级。 ON DECIMAL(A.INTCOL, 7, 0) = B.DECICOL ON A.INTCOL = INTEGER(B.DECICOL) 如果两个列都被索引, 会得到到更大的结果集。 如果只有一个列被索引, 就转换同伴。对于促进阶段1，重新绑定索引是必要的。 技巧 7: 以表单和谓词类型从最高限制性到最低限制性进行排序过滤 当写一个SQL 语句用到多种谓词时, 确定的谓词将从结果集中过滤掉大多数的数据，并且把那个谓词放在列表的开始。由于采用这种方式排序您的谓词, 这令随后的谓词将会过滤较少的数据。 DB2 默认的优化器将分类您的谓词并将处理那个谓词的情况然后顺序的在下面列出。但是, 如果您的查询引用到多个谓词并且属于相同的种类, 那么这些谓词将以他们被输入的次序来执行。这就是为什么排序谓词是如此重要，排序并且把最大过滤能力的谓词放在序列的顶部(尽最大能力的排序)。在以后的版本中，最终的查询重写将考虑到这些, 但在今天，当您写查询的时候是应该知道这些的。 图 5:谓词过滤顺序 WHERE A.COL2 = 'abracadabra' AND A.COL4 < 999 AND A.COL3 < :hvcol3 AND A.COL5 LIKE '%SON' 最应该限制的条件应该被首先列出, 以便第二种情况的额外处理能够被避免。 技巧 8: 删除SELECT表单 被SELECTed 的每一列都消耗了很多资源进行处理。 有几处地方用以检查确定是否列选择的是真正必要的。 例子 1: WHERE (COL8 = 'X') 如果一个SELECT包含一个谓词，并且列与一个值相等, 那么这一列并不一定必须的为各列检索, 这个值将总是'X' 。 例子 2: SELECT COLA,COLB ,COLC ORDERY BY COLC DB2 不再要求简单地选择一列去做排序。 所以在这个例子中,如果最终用户不需要那个值，则COLC就不再要求选择。为了避免多余的处理，可以从SELECT列表中删除几条。 它不再被要求去SELECT一些列在ORDER BY 或GROUP BY从句中使用。 技巧 9: 已知结果的极限结果集 如果是已知的，这种" FETCH FIRST n ROWS ONLY "子句是应该使用的，最大的行数将被从一个结果集中得到。 这种子句调用一个快速而隐示的关闭，从而限制了从结果集返回的行数。当第n 项结果行被处理完时，页面在缓冲池被迅速的释放。 这种" FETCH FIRST n ROWS ONLY "子句不能调用一个快速而隐示的关闭，并且将保持死锁和取回状态，直到游标隐示或显示地被关闭。 与此相对照的是, " FETCH FIRST n ROWS ONLY "将不会只允许第n+1 列被得到而导致 SQLCODE = 100 。 如果n 是一样的，那么两种子句的优化都是一致的。 技巧 10: 分析和调优存取路径 使用 EXPLAIN 或工具来解释 EXPLAIN 的输出, 核实存取路径对于请求的处理是否恰当。在生产子系统中，通过绑定相对应的生产统计检查每次查询的存取路径。缓冲池, RID池, 排序池, 和 LOCKMAX 的阈值也应该类似于生产环境。测试环境里过大的RID池将使RID 池在生产中关闭。 RID 池的关闭可能发生在 List Prefetch、以及多索引存取和 Hybrid Join Type N 存取路径的期间。RID 池关闭会导致全表扫描。 调优查询使用一种技术，将经得起未来更加智能的优化和查询重写的考验。 典型的查询调优可能使用下面的一个或多个技术: -OPTIMIZE FOR n ROWS -FETCH FIRST n ROWS ONLY -No Operation (+0, -0, /1, *1, CONCAT ' ') -ON 1=1 -Bogus Predicates -Table expressions with DISTINCT -REOPT(VARS) -Index Optimization 所有这些技术都对存取路径的选择有影响。 比较评估多种场景的成本消耗，来检验调优的努力是否成功。 调优成果的目标应该是精确存取路径和优化的索引设计。当有以下任何情况发生的时候，就应该积极主动开始这项持续化的工作: , DB2 对象数量的增量 , DB2 对象的大小变化 , 动态SQL 的使用的增量 , 事务等级的变化 , 移植 解决方案 Quest Central for DB2是一个集成的控制台，可以提供核心功能，DBA (数据库管理员)需要执行他们日常的数据库管理任务, 空间管理, SQL调优和分析, 并且可以进行性能诊断监视。Quest Central for DB2 是由DB2 软件专家撰写的，并且提供具丰富的功能， 以利于视图化的用户界面，并且支持在Unix, Linux,和windows主机上运行DB2 数据库。DB2 的客户不再被要求用独立的工具维护和使用他们的主机和分布式的DB2 系统。 Quest Central 的SQL调优组件提供一个完整的SQL 调优环境。 Quest Central是唯一可以提供完整的SQL 调优环境的针对DB2可用的产品。这个环境包括以下部分: 1. 调优实验室:通过场景的使用，一个单独的SQL 语句能够被改进很多次。然后这些 场景能够立刻被比较以确定哪个SQL 语句提供了最有效率的存取路径。 2. 比较: 您立刻可以看出对于SQL 语句修改的性能改变效果。 由于比较多个场景, 您能看到对CPU 的效果, 消耗的时间, I/O 和其他更多的统计。另外数据的比较将 保证您的SQL 语句返回相同的数据子集。 3. 建议:由SQL 调优组件提供的建议，将会发现所有的在白皮书指定的条件等等。 另 外, 如果一个新场景可以利用，SQL 调优组件甚至将会重写SQL ，并综合选择的 建议。 4. 存取路径和对应的统计:在SQL的上下文中，对于DB2存取路径，所有适合的统 计应被显示出来。 采取推测以设法理解为什么选择一个特殊的存取计划。 Quest Central for DB2 健壮的功能显现了上述SQL 调优中的技巧以及更多。 这篇白皮书剩余的部分将证明 Quest Central 是由更丰富和更透彻的知识恰当的组成的。 Quest Central 不仅可以提高您的SQL语句效率，更可以帮助您全面的提升数据库的性能。上面描述的各种调优技巧都被Quest Central所包括。 解决的技巧 6:使数字化和日期数据类型相匹配 这个特殊的SQL 难题可能是最细微及最难被察觉的,特别是当使用主机变量的时候。 解释也许能显示索引存取的使用, 但在完成查询之后将采取表空间扫瞄。 这是常见的情况，当谓词比较两个项目的值并且那两个项目包含着不恰当的数据类型时。 Quest Central SQL Tuning 将在建议部分识别这种情况。 另外, 数据库管理组件能够修改列, 既使本地的DDL不支持修改(由卸载数据，删除表, 重载数据, 和重新绑定) 。 图14: Quest Central将确认谓词是否搭配不当 解决的技巧 7:以表和谓词类型从高限制到低限制进行排序过滤 SQL 调优被设计成允许测试这些条件的类型以确定适当的顺序。 例子 1: SELECT * FROM batting WHERE run_qty > 2 AND hit_qty > 10 这条SQL 被放入工具中并被放置在最初的SQL表中。hit_qty 列比run_qty 谓词能提供更好的过滤。 一个新场景被创建并且谓词以hit_qty 谓词的顺序被首先列出。 图15: 比较不同的谓词命令以检验性能的改善 解决的技巧 8: 删除SELECT的名单 当返回数据给用户时，选择比需要多的列会导致更多的消耗将会。 由于使用SQL 调优的场景特征, 您能修改最初的SQL语句以删除多余的列并且执行消耗比较来确定删除多余列的影响。 在下面的例子中, SQL 语句被修改以减少返回的列数。 在原始的SQL 语句和修改过的语句间的节省大约有60% 。 这种节省对大型数据库有着巨大的影响。 图16: Compare of a SELECT * and a SELECT of specific columns 解决的技巧 9:已知结果的极限结果集 为了确定加入了"FETCH FIRST n ROWS ONLY"子句的影响，将安排您的SQL 语句, 您 可以把您最初的SQL 语句放入SQL 调优组件中。 从而创建一个新场景和包括"FETCH FIRST n ROWS ONLY"子句。 比较将显示由于增加这条从句而获得的成本节省。 图17:用包含"Fetch for 1 row only"的从句比较同样的SQL 语句 解决的技巧 10:分析和调优存取路径 存取路径标签在SQL 调优中被发现，并为您存取路径提供全面的显示。 在执行第一步时，存取路径是自动地突出，并且"next step"按钮将突出在下一步, 您按步骤执行存取计划的每一步。 图19: Quest Central 全面显示提供的存取路径和相关对象，高量显示表单、索引、和列，及有关存取路径的步骤 业内最健壮的显示在将清楚而准确的描述您的SQL 语句的存取路径。 三、冷备份与热备份、双机热备与容错 冷备份与热备份 一、 冷备份 冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份时将关键性文件拷贝到另外的位置的一种说法。对于备份Oracle信息而言，冷备份时最快和最安全的方法。冷备份的优点是: ,、 是非常快速的备份方法(只需拷文件) ,、 容易归档(简单拷贝即可) ,、 容易恢复到某个时间点上(只需将文件再拷贝回去) ,、 能与归档方法相结合，做数据库“最佳状态”的恢复。 ,、 低度维护，高度安全。 但冷备份也有如下不足: ,、 单独使用时，只能提供到“某一时间点上”的恢复。 ,、 再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。 ,、 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。 ,、 不能按表或按用户恢复。 如果可能的话(主要看效率)，应将信息备份到磁盘上，然后启动数据库(使用户可以工作)并将备份的信息拷贝到磁带上(拷贝的同时，数据库也可以工作)。冷备份中必须拷贝的文件包括: ,、 所有数据文件 ,、 所有控制文件 ,、所有联机REDO LOG文件 ,、 Init.ora文件(可选) 值得注意的使冷备份必须在数据库关闭的情况下进行，当数据库处于打开状态时，执行数据库文件系统备份是无效的。 下面是作冷备份的完整例子。 (1) 关闭数据库 sqlplus /nolog sql>connect /as sysdba sql>shutdown normal; (2) 用拷贝命令备份全部的时间文件、重做日志文件、控制文件、初始化参数文件 sql>cp (3) 重启Oracle数据库 sql>startup 二、 热备份 热备份是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法。所以，如果你有昨天夜里的一个冷备份而且又有今天的热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可以做备份了。热备份的命令文件由三部分组成: 1( 数据文件一个表空间一个表空间的备份。 (1) 设置表空间为备份状态 (2) 备份表空间的数据文件 (3) 恢复表空间为正常状态 2( 备份归档log文件 (1) 临时停止归档进程 (2) log下那些在archive rede log目标目录中的文件 (3) 重新启动archive进程 (4) 备份归档的redo log文件 3( 用alter database bachup controlfile命令来备份控制文件热备份的优点是: 1( 可在表空间或数据库文件级备份，备份的时间短。 2( 备份时数据库仍可使用。 3( 可达到秒级恢复(恢复到某一时间点上)。 4( 可对几乎所有数据库实体做恢复 5( 恢复是快速的，在大多数情况下爱数据库仍工作时恢复。 热备份的不足是: 1( 不能出错，否则后果严重 2( 若热备份不成功，所得结果不可用于时间点的恢复 3( 因难于维护，所以要特别仔细小心，不允许“以失败告终”。 双机热备的实现模式 双机热备有两种实现模式，一种是基于共享的存储设备的方式，另一种是没有共享的存储设备的方式，一般称为纯软件方式。 基于存储共享的双机热备是双机热备的最标准方案。 对于这种方式，采用两台(或多台，参见:双机与集群的异同)服务器，使用共享的存储设备(磁盘阵列柜或存储区域网SAN)。两台服务器可以采用互备、主从、并行等不同的方式。在工作过程中，两台服务器将以一个虚拟的IP地址对外提供服务，依工作方式的不同，将服务请求发送给其中一台服务器承担。同时，服务器通过心跳线(目前往往采用建立私有网络的方式)侦测另一台服务器的工作状况。当一台服务器出现故障时，另一台服务器根据心跳侦测的情况做出判断，并进行切换，接管服务。对于用户而言，这一过程是全自动的，在很短时间内完成，从而对业务不会造成影响。由于使用共享的存储设备，因此两台服务器使用的实际上是一样的数据，由双机或集群软件对其进行管理。 对于纯软件的方式，则是通过镜像软件，将数据可以实时复制到另一台服务器上，这样同样的数据就在两台服务器上各存在一份，如果一台服务器出现故障，可以及时切换到另一台服务器。 对于这种方式的深入分析，请参见:纯软件方式的双机热备方案深入分析 纯软件方式还有另外一种情况，即服务器只是提供应用服务，而并不保存数据(比如只进行某些计算，做为应用服务器使用)。这种情况下同样也不需要使用共享的存储设备，而可以直接使用双机或集群软件即可。但这种情况其实与镜像软件无关，只不过是标准的双机热备的一种小的变化。 双机容错的工作原理 1、双机容错的两种方式 双机容错从工作原理上可以分为共享磁盘阵列柜方式和扩展镜像纯软件方式两种。这两种方式的共同特点都是围绕关键数据的可靠性，对操作系统、电源、CPU和主机主板进行容错。 双机共享磁盘阵列柜方式是以磁盘阵列柜为中心的双机容错方神机妙算，磁盘柜通过SCSI线连接到两个系统上，并能被两个系统所访问。关键数据放在共享磁盘柜中，在正常运行时，控制友在主用系统上，当主用系统发生故障或主用系统检查到某种故障后，系统控制权就切换到备用主机。主用系统修复后，主备角色互换，双机系统进入正常工作模式。 双机扩展镜像酏软件方式是纯软件方式的双机容错方案，两个系统之间通过以太网连接，关键数据在两个系统之间呈镜像存在。在正常运行时，控制权在主用系统上，数据实时地镜像到备用系统上。当主用系统发生故障或主用系统检查到某种故障后，系统控制权切换到备用主机。由于采用以太网作为系统的数据链路，主用系统可不干扰备用系统工作，自动脱离并在一个孤立的环境中进行故障的诊断和维修，主用系统修复后，控制权需要切回到主用系统，数据需要从备用系统恢复到主用系统，这个工作在后台自动完成，应用读取数据仍从备用系统上进行而不会中断。数据恢复完成后，双机系统进入正常工作模式。 以上两种双机容错的方式已经能很好地保证数据可靠，如果在主、备机上各运行一种应用还可实现相互备份。 2(共享磁盘阵列柜方式的工作原理 使用共享磁盘阵列柜方式的两台(或多台)服务器的数据同时存放在一个磁盘阵列柜里，因此，不需要进行数据复制，只需在其中一台服务器停机时将此服务器的工作转移到另外一台服务器，工作较为简单。由于数据存储在同一磁盘阵列柜里，一是磁盘阵列柜的数据捐赠 坏则数据全部丢失，有单点崩溃的可能性，而且由于服务器与磁盘阵列柜之间通常使用SCSI线连接，因此受到距离的了限制。 共享磁盘阵列车柜方式一般由监控系统与切换系统两部分组成。 (1) 监控系统 A、SCSI侦测。共享磁盘阵列柜方式内部含有侦测心跳通信线路，侦测结果置于共享磁盘阵列柜上的一个5MB的小区，用于监控，此小区一般在机柜逻辑盘的起始段，对于某一台服务器而言，将侦测信自己人以类似于记录方式写在该小区内，其中每一条记录包括如下内容。 系统对本服务器的监测状态信息 另一台服务器是滞看到本服务器状态信息，同时修改记录区内容。 B、网络侦测。当一台服务器有问题或出现故障时，对等服务器的可调变心跳频率不断提高。在最小心跳时间内发现记录内容没有更新，即会调用网络心跳侦测两次确认系统状态。当峡谷线心路都判断系统故障时，共享磁盘阵列柜方式将故障服务器的交易业务在最小安全切换时间内切换到对等服务器上继续运行。 C、切换系统 网络服务器。双服务器后台，对于用户一羰，由监控软件共享磁盘阵列柜方式提供一个逻辑的IP地址，如192.192.192.1,任一用户上网可以直接使用这一地址，当后台其中一台服务器出现故障时，另外一台服务器会自己将其网卡的IP地址替换为192.192.192.1，这样，用户一端的网络不会因为一台服务器出现故障而断掉。 数据库服务。当其中一台服务器出现故障时，另外一台服务器会自动接管数据库，同时启动数据库和应用程序，使用户数据库可以继续操作，对用户而言不受影响。 应用系统。当有一台服务器出现故障时，另外一台服务器会自动接管各类应用程序，同时启动应用程序，使用户可以继续操作，对用户而言不受影响。 3、扩展镜像纯软件方式的工作原理 使用纯软件方式的软件不需要共享磁盘阵列柜，它将数据存储于各自服务器内，通过镜相引擎将数据进行实时复制。当其中一台服务器停机时，设定的服务器接管停机服务器的工作。由于数据存储于不同服务器内，因此避免了单点崩溃的可能性，增加了数据的安全性。服务器之间通过网络连接，所以服务器之间的连接受距离的限制较小。由于数据存储在各自己服务器硬盘内，因此服务器之间有应用各不影响，提高了服务器正常使用时的效率。 4、热备份 热备份其实是计算机容错技术的一个概念，是实现计算机系统高可用性的主要方式。热备份采用磁盘镜相技术，将运行着的计算机系统数据和应用数据同时保存在不同的硬盘上，镜像在不同的磁盘上的数据在发生变化时同时刷新，从而保证数据一致性。当系统中的一个硬盘发生故障时，计算机可以使用镜像数据，避免因系统单点故障(如硬盘故障)导致整个计算机系统无法运行，从而实现计算机系统的高可用性。 现在的计算机系统在系统建设时都普遍采用了热备份方式，最典型的实现方式是双机热备份，即双机容错系统。双机容错系统在建设时选用两台同样服务器，运行相同的操作系统、应用软件(如数据库软件)，两台服务器共享一个磁盘阵列，采用磁盘镜像，将应用数据建立在磁盘阵列车上，实现双机容错。其中一台服务器被指定为工作机，由它处理当前运行的业务，另一台为备份服务器。一旦工作机发生故障，运行的业务请求将被人工(或自动)地切换到备份服务器，使运行着的业务不至于因为系统的单点故障中断，实现系统的高可用性。 热备份实现了计算机系统的高可用性，使一些对实时性要求很强的业务(如银行信用卡业务)得以保障。然而，热备份方式并不能解决所有计算机系统数据管理问题，举一个最简单的例子，如果操作人员误删除了一个文件，热备系统为保证数据的一致性，会同时将这个文件的镜像文件删除，造成数据丢失。为防止有用的数据因系统故障和人为误操作而损坏或丢失，实行数据存储管理必不可少，数据存储管理的核心是数据备份。 双机容错环境下Oracle数据库的具体应用 目前许多建立和应用信息系统的企业，在系统应用不断改进的同时，开始注意提高企业信息系统的可用性和可靠性。通过双机容错系统为企业提供系统高可用性保障是目前企业普遍采用的方法。 医疗机构工作性质的特殊性要求其信息系统7天×24小时不间断运行，采用双机容错方案 为系统提供了高可用解决方案。本文将对医疗信息系统的双机容错环境下Oracle数据库应用做详细介绍。 系统配置 该系统的硬件配置如下: 主数据库服务器: 富士通Primergy MS-610服务器(双Xeon 700MHz CPU，1GB内存)。 数据库备份服务器: 富士通Team Server C870ie GP5C875(双Pentium? 700MHz CPU，1GB内存)。 容错软件: 天地公司的SLHA 3.0软件包。 磁盘阵列: IQstore R1500(带2个SCSI接口)。 线路连接: 2台服务器用RS-232串口线和RJ-45网络线相连(如图1所示)。 软件配置如下。 操作系统: Windows NT Server 4.0 服务器软件配置: Windows NT 4.0 Service Pack 5、Internet Explorer 5.0、Microsoft Data Access Component 2.0，Oracle数据库为7.3.3企业版。 双机容错的实现 1. 操作系统的安装 我们用A机表示数据库服务器，用B机表示备份数据库服务器。首先在物理上将所需硬件设备连接好，分别在各自服务器上安装Windows NT Server 4.0操作系统及补丁包等。然后，进入磁盘管理器，将磁盘阵列划分为2个逻辑盘D和E，此时2台服务器都可访问磁盘阵列。 2.Oracle数据库的安装 先关闭B机，在A机上安装Oracle数据库，安装路径默认为D盘，归档日志放在E盘。安装完毕后，将Oracle的3个服务(此处SID为ORCL,所以3个服务就是OracleServiceORCL、OracleStartORCL和OracleTNSlistener)的启动方式改为手动并将此3个服务停止。注意: 改为手动的目的是为了让这3个服务由双机容错软件来启动,而不是由操作系统启动。 然后，关闭A机，启动B机，格式化D盘，将刚刚由A机建立在磁盘阵列上的Oracle目录也格式化掉; 在B机上安装Oracle数据库，安装路径默认为D盘，安装完毕，同样将Oracle的3个服务的启动方式改为手动并停止3个服务。 双机上安装Oracle的实质就是将Oracle系统分别装在2台服务器上，而数据只存 储在磁盘阵列上。 3.双机容错软件的安装及双机容错环境的建立 双机容错软件的安装非常简单，只需启动A机和B机，在2台服务器上分别安装该软件即可。建立双机容错环境是将磁盘阵列上的D盘和E盘以及Oracle 的3个服务交由双机容错软件控制，并由双机容错软件进行切换。 在双机容错软件SLHA的"Configuration"选项中将数据库服务器设为Active状态，即平时正常工作状态时，此时数据库服务器工作，备份服务器等待。当A机Active时，只有A机可以访问磁盘阵列，B机不能访问磁盘阵列。此时，Oracle数据库服务器实际上是A机，A机的IP地址就是Active IP Address，同时A机的主机名为Active Host Name; 当A机因故不能工作时，A机的状态会被"心跳线"侦测到，这时B机开始切换到Active状态，接管磁盘阵列，此时的Oracle数据库服务器改为B机，B机的IP地址就是Active IP Address，同时B机的主机名为Active Host Name。上述操作均由系统自动完成，实践证明切换所需的时间很快，对客户端的影响很小。 需要注意的问题 1(当在A机安装完Oracle数据库后在B机安装Oracle数据库时，一定要先将磁盘阵列D盘格式化，而不是只将D盘中已由A机安装的Oracle数据库删除，否则可能会出现意想不到的错误，例如Oracle侦听服务失败等; 2(最终安装好Oracle数据库后，要对D:\Oracle\Orant\network\Admin\ Listener.ora 文件进行修改，其中Server名称一定要改为Active host name Alias,如不进行修改将使客户端的Oracle数据库用户无法连接到Oracle数据库中。 3(在Hosts文件中增加一条记录，使Active IP Address和Active Host Name相互对应，这样系统就会自动起到解析作用。Hosts文件位于c:\Winnt\ system32\drivers\etc目录下。 4(要注意不到万不得已，不要强行切换，避免产生数据错误。如必须对双机进行切换，可先进入Svrmgr Oracle服务器控制台,用Shutdown命令关闭Oracle数据库，再进行切换。 双机容错的原理 Oracle数据库安装在磁盘阵列上(即图2中Public Drives)，2台服务器都可以访问它，但不能同时访问。Oracle Server for NT主要提供3个服务:OracleServiceSID、OracleStartSID和OracleTNSlistener。在数据库服务器正常工作时，由数据库服务器控制磁盘阵列柜，此时只有该服务器可以访问磁盘阵列，该服务器上的Oracle服务处于启动(Active)状态，此时该服务器就扮演图2中Active Server的角色，备份服务器处于等待(Standby)状态，即图2中Backup Server。 当数据库服务器发生故障不能工作时，双机容错系统会检测到数据库服务器的状态，从而使备份服务器自动激活，接管磁盘阵列并自动启动Oracle的3个服务，而对于客 户端来说，只经历一个短暂的服务器重启过程，访问的数据仍是磁盘阵列中的数据。 注意: 是双机容错软件而不是操作系统来控制Oracle 的启动和停止，即由双机容 错软件来控制这3个Oracle服务的启动和停止，实现Oracle数据库在双机之间的切换 .