数据库-研究生课件：第二部分 数据库系统实现技术

第二部分数据库系统实现技术主要内容第3章存储与文件结构(简介+自学)第4章索引与散列(简介+自学)第5章查询处理第6章查询优化第7章事务第8章并发控制第9章恢复系统开源代码数据库管理系统Derby/Cloudscape分析主要任务有选择的深入了解并掌握部分DBMS核心模块的实现细节通过对开源代码数据库管理系统Derby的分析,学习实现DBMS核心的相关技术原理开源代码分析方法和技巧基础收集整理相关资料对相关基本原理的深刻理解和掌握原则先总体(宏观)后局部(微观)先抽象(接口)后细节(实现)步骤从系统架构分析入手,逐步深入应用软件分析工具动手实验和阅读代码很重要ArchitectureofaDBMSDiskSpaceManagerDBMSIndexFilesDataFilesSystemCatalogDatabaseArchitectureofaDBMSDiskSpaceManagerDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)ArchitectureofaDBMSFiles&AccessMethodsBufferManagerDiskSpaceManagerDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)ArchitectureofaDBMSFiles&AccessMethodsBufferManagerDiskSpaceManagerOperatorEvaluatorPlanExecutorParserOptimizerQueryExecutionEngineDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)ArchitectureofaDBMSTransactionManagerLockManagerConcurrencyControlFiles&AccessMethodsBufferManagerDiskSpaceManagerOperatorEvaluatorPlanExecutorParserOptimizerQueryExecutionEngineDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)ArchitectureofaDBMSTransactionManagerLockManagerConcurrencyControlRecoveryManager(Logging&Recovery)Files&AccessMethodsBufferManagerDiskSpaceManagerOperatorEvaluatorPlanExecutorParserOptimizerQueryExecutionEngineDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)ArchitectureofaDBMSTransactionManagerLockManagerConcurrencyControlRecoveryManager(Logging&Recovery)Files&AccessMethodsBufferManagerDiskSpaceManagerOperatorEvaluatorPlanExecutorParserOptimizerQueryExecutionEngineDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)AccessControlManagerArchitectureofaDBMSTransactionManagerLockManagerConcurrencyControlRecoveryManager(Logging&Recovery)Files&AccessMethodsBufferManagerDiskSpaceManagerOperatorEvaluatorPlanExecutorParserOptimizerQueryExecutionEngineDBMSIndexFilesDataFilesSystemCatalogDatabaseApplications(WebForms,SQLInterface)IntegrityManagerAccessControlManagerArchitectureofaDBMS(9/30)ArchitectureofaDBMSArchitectureofaDBMSInterfaceSQLCommandsDBMSArchitectureIntroducingApacheDerbyAnOpenSourscePureJavaRelationalDatabaseEngineWhat

isDerby?OpenSourceDatabaseTechnologyPureJavaEasytouseSmallfootprintStandardsbasedCompleterelationaldatabaseengineSecureSub-projectofApacheDBProject/derbyDerbyHistory1996:Cloudscapefounded–Oakland,CA1997:JDBMS1.0ReleasedApr1999:Cloudscape2.0Dec1999:AcquiredbyInformixSoftwareJune2001:Cloudscape4.0ReleasedJuly2001:AcquiredbyIBMDec2001:IBMCloudscape5.0Released2003:IBMCloudscape5.1,5.1FP1&FP2August2004:IBMdonatedCloudscapetoApacheJuly2005:DerbygraduatedfromApacheIncubatorDerby@ApacheSoftwareFoundationIBMcontributedtheCloudscapecodetoApacheSoftwareFoundationasDerbyReleasedundertheApachev2.0LicenseCommunityisgrowing,openinvitationtocontributetowardsthesuccessoftheprojectandcommunityParticipateasauser,contributor,orboth.ContributewithSourcecodeDocumentation(papers,presentationsetc.)Suggestions,ideas,bugreportsorjusthelpothersoutonthemaillistsMay16200521ApacheDerbyvs.CloudscapeDerby:DatabaseEngine+EmbeddedJDBCDriver+NetworkServer+Tools(ij,dblook,import/export)+Usermanuals(web-accessible)Clouldscape:Derby+TechnicalSupport+DB2UniversalJDBCDriver(JCC)+ODBC+DB2Plug-inforEclipse(databasebrowsingTool+Manuals(eclipse-basedandPDF)FeaturesofDerbyPureJavaEmbeddeddatabaseSmallfootprintStandardsbasedCompleterelationaldatabaseengineEasytouseanddeployPureJavaDatabasecodewritteninJavaWriteOnceRunAnywhereRequiresaJ2SE1.3,1.4or1.5virtualmachineAnyhardware,anyoperatingsystem,anyvendorSinglebinarydoesruneverywhereLinux,Windows,MacOs,AIX,Solaris,Z/OS,AS400,OS/390,…Databaseon-diskformatisplatformindependentSmallFootprintEnginejarfileisaround2MbOptionalJarfilesNetworkserver~150kTools~200kRuntimememoryuseDependentonapplication,datacachingetc.CanrunwhenJavaheapmemoryrestrictedto4MbHaveruninmachineswithonly16MbphysicalmemoryEmbeddedDatabaseDatabaseenginebecomesintegralpartoftheJavaapplicationNoadditionalprocess➢Application'sJDBCcallsnowjustresultinmethodcallswithinsameJVMJustaJarfiletotheapplicationDatabaseinvisibletoenduserofapplicationEmbedded

DerbywithApplicationDatabaseonlyaccessiblefromsingleJVMJava/JDBConlyNonetworkconnectivityTypicallyissingleapplicationperJVM(butcouldbemultiple)Embedded

DerbywithApplicationEasytouseFastZeroadministrationEmbeddedinApplication

ServerDerbyEmbeddedPros:Invisibleatthedeployedsite–simplystartyourapplicationVerysimpletodeployandmanageStillhavemulti-threaded,multi-connectioncapabilityPerformanceisverygoodCons:Nonetworkingcapability(butnosecurityissues)Only1JVMcanaccessadatabaseatanygiventimeTraditionalClient/ServerJavaVirtualMachineDerbyengineNetwork

ServerDRDAJDBCCLI/ODBCMultipleClientapplications

-RemoteorlocalPHPontopofODBCClientApplications

ClientsprovidedbyIBMDMDatabase(s)ondiskDerby’sClient/ServerModeAccessfromasingleVMinembeddedmodecanbearestrictionNetworkServerallowsDerbytoactasatraditionalclientserverdatabaseIndustrystandardDRDAoverTCP/IPNetworkServeritselfispureJavaUsesembeddedJDBCdriveragainstDerbyIt’saJavaDRDAtoJDBCconverterNetworkServerClientsJDBCusingIBM’sDB2UniversalJDBCClientDifferentdriverandJDBCURLtoembeddedDataSourceandConnectionPoolingDataSourceODBC/CLIusingIBM’sDB2’sUniversalODBC/CLIclientEnablesPHP/Perl/.NETUniversaldriversenabledbyuseofDRDAJavaVirtualMachineDerbyengineNetwork

ServerDRDAJDBCCLI/ODBCEspeciallyusefuldeveloping

&debuggingembeddeddatabaseusage

PHPontopofODBCClientApplications

ClientsprovidedbyIBMDMDatabase(s)ondiskEmbeddedNetworkServerApplicationEmbeddedNetworkServerAddsontoembeddedenginetoprovideaccesstodatabasefromoutsidetheapplication’sVM(fromlocalorremotemachine)AllowsdeveloperstoworkondatabasewhilestandaloneapplicationisrunningAllowsreportingcapabilitytobeaddedontoastandaloneapplicationEnabledbypropertysettingandadditionaljarfile,nocodechangestoapplicationderby.drda.startNetworkServer=trueODBC/DerbyenvironmentInstallDB2Run-TimeClientStartDerbyNetworkServer(Derbymustbeinstalled!)ConfigureDerbyDBasODBCDataSourceCatalognodeanddatabaseviaDB2CommandLineProcessor,CLPCatalogdatabaseasODBCdatasourcedb2=>catalogtcpipnodeDNSremotelocalhostserver1527--DerbyNetworkServerdb2=>catalogdbDDBatnodeDNSauthenticationserverdb2=>catalogsystemodbcdatasourceDDBWriteanODBCApplicationtoaccesstheDerbydatabase,DDBCompleteRelationalEngineSQLTables,indexes,views,triggers,procedures,functions,temptablesforeignkeysandconstraintsjoins,costbasedoptimizerMulti-user,transactions,isolationlevels,deadlockdetection,crashrecovery,backup&restoreDatacaching,statementcaching,writeaheadlogging,groupcommitMultipledatabasespersystemStandardstoAllowMigrationDerbydoesperformwellinitsownrightLackingfeaturesofanenterpriseDBDeveloponDerby,deployonenterpriseDBInitialdeployonDerby,migratetoenterpriseDBasneededStandardsBasedSQLSQL92,SQL99,SQL2003,SQL/XML,…JavaJ2SE1.3,1.4,1.5J2ME/OSGiJDBC2.0&3.0JSR169J2EE–JDBCpassedCTSforJ2EE1.4&1.3DRDAV3OpenGroup(/)DerbyToolsij–SQLscriptingtoolJDBCneutral,canbeusedagainstotherJDBCdriversdblook–schemaextractiontoolforDerbysysinfo–DerbyversioninformationOutputusefulforbugrepoRTINginJiraDerbyIntegrationDerbyisreportedtoworkwithActiveMQ,Cocoon,C-JDBC,Daffodil,db2db,DB2Everyplace,DOTS,DroneIRCBot,Eclipse,Geronimo,Hibernate,iBATIS,IBMDB2JDBCUniversalDriver,IKVM.NET,iSQL-Viewer,SunJ2EESDK,JBoss,JOnAS,JPOXJDO,Jython,Maven,RedHatApplicationServer,RIFE,SQuirreLSQL,Sync4j,TomcatEasytoUseandDeployDerbyiseasytouseDesignedforJavadevelopers(standardJDBCcommands)Simplyloadthedriverandconnecttoadatabaseandyou’reinbusinessDerbyiseasytodeploySimplydeploythejarfile(s)andsettheusersclasspathEmbeddedrequiresnostartingandstoppingofaserverDesignedtobezeroadmin:noupdatestatistics,spacereclamation,logmanagement,etc.requiredEverythingcanbedoneprogrammaticallyincludingdatabasebackups,settingproperties,etc.DerbyArchitectureModuleArchitectureModuleSetofusablefunctionalityWelldefinedAPI–“protocol”ProtocolseparatedfromimplementationTypicallydeclaredassetofJavainterfacesIdentifiedbysingleJavainterface,“factoryclass”➢.apache.derby.iapi.services.locks.LockFactoryMayreferenceothermoduleprotocolsZeroormoreimplementationsinarunningsystemImplementationcanimplementcontrolinterfacesto:➢Defineadditionalboot&shutdownactions➢DefinesuitabilityforrequestedfunctionalityServiceCollectionofco-operatingmodulesprovidingacompletesetoffunctionalitySingleprimarymoduledefiningexternalAPIPersistent➢Boot-upstateinsinglepertiesfile,includingtherequiredprimarymoduleidentification(astheprotocol,nottheimplementation)Non-persistent➢Purelyrun-timedefinitionModulesalwaysbootedthroughmonitorMonitorManagesDerbysystemBoots&shutdownServicesFindspertiesfilebaseduponservicebootMapsrequestsforamoduleprotocoltoanimplementationBasedupon:➢Virtualmachineenvironment(J2ME/J2SE1.3/1.4)➢Availableclasses(e.g.JCEencryptionclasses)➢SuitabilityforcurrentserviceEnsuressystemisnotgarbagecollectedawayDatabaseEngineSystemSingleActiveThreadper

SessionCache/Lock

ManagersCacheManagercachesobjectsthatimplementaCacheableinterfaceHandlesaging,pruning,etc.Cachespages,statements,stringtranslations,openfiles,dictionaryobjectsLockManagerlocksobjectsthatimplementaLockableinterfaceLockcompatibilitydefinedbyLockable,notmanagerCentralizes“hard”thread-safeissuesSQLCompilationPreparedStatementps=conn.prepareStatement(“SELECT*FROMTWHEREID=?”);1)Lookupincacheusingexacttextmatch(skipto5ifplanfoundincache)2)ParseusingJavaCCgeneratedparser3)Bindtodictionary,gettypes,etc.4)Generatecodeforplan5)CreateinstanceofplanParsePhaseTreeofQueryNodescreatedManyNodes,oneperoperationtype➢FromBaseTable,MethodCallNode,DistinctNode,CurrenUserNode,...➢BulkofcodefootprintisSQLcompilerNoexecutioncodeinQueryNodes,leadstosimilarsetofexecutionResultSetsDuplicatecheckingofstateinQueryGeneratePhaseGenerateJavabytecodedirectly,intoin-memorybytearrayLoadwithspecialClassLoaderthatloadsfromthebytearraySingleClassLoaderpergeneratedclassAllowsstatementstobeagedoutindependentlyGeneratedclassextendsaninternalclassBaseActivationwhichprovidessupportmethods,commonfunctionalityActivation–InstanceofplanInstanceofgeneratedclasscalledActivationHoldsqueryspecificstate,parameters,etc.ConnectedindirectlytoJDBCPreparedStatementthroughholder/wrapperclassthatimplementsActivationinterfaceHolderallowscompiledplantochangewithoutknowledgeofapplication,transparenttoPreparedStatementActivation–ExecutionPreparedStatement.execute()methodsCreatestreeofinternalResultSetobjectsthatmaptoSQLoperations➢ScanResultSet➢SortResultSet➢IndexScanResultSetGeneratedcodemaingluecodeExpressionsaregeneratedasmethodsingeneratedclassStatementResultsBenefitsofGeneratedCodeNoneedtohaveDerbyspecificinterpreterwritteninJava,justuseJVMGeneratedcodewillgetJIT'edandthusgaintheperformancebenefitsTightintegrationwithJavacallsfromSQL,e.g.SQLfunctionswritteninJava.Nouseof(slow)reflection,justcompilemethodc