MySQL性能优化Workshop_架构和引擎

MySQL性能优化Workshop-架构和引擎Sun大中华区开源推广中心Sun开源技术高级研讨班内容•MySQL架构•MySQL系统组件介绍•MySQL存储引擎特性Sun大中华区开源推广中心Sun开源技术高级研讨班MySQL架构•MySQL包括两层服务器本身存储引擎•MySQLServer提供网络连接服务为客户端提供一系列接口提供相关通用的组件如解析器，优化器不提供具体的物理数据存储访问Sun大中华区开源推广中心Sun开源技术高级研讨班MySQL架构Sun大中华区开源推广中心Sun开源技术高级研讨班MySQL架构•MySQL存储引擎处理Data处理SQL语句，以及和Data相关操作功能物理数据存储执行查询事务支持并发控制恢复管理引擎层优化其他特性MySQL支持标准的存储引擎接口•Demo1mysqlshowenginesSun大中华区开源推广中心Sun开源技术高级研讨班InternalStorageEnginesISVStorageEnginesCommunity/CustomStorageEnginesMemcacheMyISAMClusterMemoryFalconMore…开放、先进、灵活提供可插拔的存储引擎架构为不同的表，选择不同的存储引擎MySQL逻辑上可以分为两层SQL层：查询的解析、优化、执行存储引擎：数据存储、锁、事务管理、恢复MySQL插件式存储引擎架构Sun大中华区开源推广中心Sun开源技术高级研讨班MySQL架构ClientProgramClientProgramCclientAPICclientAPIQueryCacheQueryCacheStorageEngineImplementationsStorageEngineImplementationsMyISAMhandlerandlibraryMEMORYhandlerandlibraryInnoDBhandlerandlibraryNDBclusterhandlerandlibraryAndsoon...QueryParsingQueryParsingOptimizationOptimizationExecutionExecutionCoreSharedSubsystemsCoreSharedSubsystemsBaseFunctionLibraryProcess,Thread,andResourceManagementCacheandBufferManagementNetworkingManagementandCommunicationLogManagementAccessandGrantManagementStorageEngineAbstractionStorageEngineAbstractionSun大中华区开源推广中心Sun开源技术高级研讨班Server端核心通用子系统(1)CoreSharedSubsystemsCoreSharedSubsystemsBaseFunctionLibraryProcess,Thread,andResourceManagementCacheandBufferManagementNetworkingManagementandCommunicationLogManagementAccessandGrantManagement•MySQL’smulti-threadedsingleprocessarchitectureensuresthatmultipleexecutingthreadsdonotconflictwitheachotheroroverwritecriticaldata–Costsavings–Switchinginexpensive–MinimaloverheadSun大中华区开源推广中心Sun开源技术高级研讨班Server端核心通用子系统(2)CoreSharedSubsystemsCoreSharedSubsystemsBaseFunctionLibraryProcess,Thread,andResourceManagementCacheandBufferManagementNetworkingManagementandCommunicationLogManagementAccessandGrantManagement•Thissubsystemcachesandretrievesnumeroustypeofdatausedbyallthethreadsexecutingwithintheserverprocess–Reducesthenumberofrequestsfordisk-basedI/OoperationsSun大中华区开源推广中心Sun开源技术高级研讨班Server端核心通用子系统(3)CoreSharedSubsystemsCoreSharedSubsystemsBaseFunctionLibraryProcess,Thread,andResourceManagementCacheandBufferManagementNetworkingManagementandCommunicationLogManagementAccessandGrantManagement•ThissubsystemhandlestheworkofsendingandreceivingnetworkpacketscontainingMySQLconnectionrequestsandcommandsacrossavarietyofplatforms–Makesthevariouscommunicationprotocols(TCP/IP,NamedPipes,etc.)transparentfortheconnectionthreadincludingsecuredsocketlayers(SSL)Sun大中华区开源推广中心Sun开源技术高级研讨班Server端核心通用子系统(4)CoreSharedSubsystemsCoreSharedSubsystemsBaseFunctionLibraryProcess,Thread,andResourceManagementCacheandBufferManagementNetworkingManagementandCommunicationLogManagementAccessandGrantManagement•Thissubsystemallowsavarietyoflogeventsubclassestobemaintainedbyonelogclass–Providesdeveloperstheabilitytoaddlogsandlogeventswithoutbreakingthesystem’scorefunctionality–Avarietyofsystemactivities(startup,multi-statementtransactions,auto-incrementvaluechanges,etc.)canbeloggedviathesubclasseventsSun大中华区开源推广中心Sun开源技术高级研讨班Server端核心通用子系统(5)CoreSharedSubsystemsCoreSharedSubsystemsBaseFunctionLibraryProcess,Thread,andResourceManagementCacheandBufferManagementNetworkingManagementandCommunicationLogManagementAccessandGrantManagement•Thissubsystemmanagesthesecuritybetweentheclientandtheserver–DefinesalltheGRANT’sneededtoexecuteagivencommand–Verifiesuseraccessrightsduringloginprocess/queryexecution–Modifiesthein-memoryversionsofthegranttables–ContainsfunctionsforpasswordgenerationSun大中华区开源推广中心Sun开源技术高级研讨班查询缓存ClientProgramClientProgramCclientAPICclientAPIQueryCacheQueryCacheStorageEngineImplementationsStorageEngineImplementationsMyISAMhandlerandlibraryMEMORYhandlerandlibraryInnoDBhandlerandlibraryNDBclusterhandlerandlibraryAndsoon...QueryParsingQueryParsingOptimizationOptimizationExecutionExecutionStorageEngineAbstractionStorageEngineAbstraction•This“subsystem”isasetofclassesthatfunctionasacomponent–ResponsibleforcachingtheSQLcommandsexecutedontheserver–ResponsibleforstoringtheresultsoftheexecutedcommandsSun大中华区开源推广中心Sun开源技术高级研讨班查询解析优化执行ClientProgramClientProgramCclientAPICclientAPIQueryCacheQueryCacheStorageEngineImplementationsStorageEngineImplementationsMyISAMhandlerandlibraryMEMORYhandlerandlibraryInnoDBhandlerandlibraryNDBclusterhandlerandlibraryAndsoon...QueryParsingQueryParsingOptimizationOptimizationExecutionExecutionStorageEngineAbstractionStorageEngineAbstraction•ThesesubsystemsarethebrainsoftheMySQLserver–Parsing-ResponsiblefordeconstructingtherequestedSQLstatementsintoanabstractsyntaxuse–Optimizing–Responsibleforfindingtheoptimalexecutionplanforthequery–Executing-ResponsibleforexecutingtheoptimizedpathfortheSQLcommandpassedthroughtheparserandoptimizerSun大中华区开源推广中心Sun开源技术高级研讨班存储引擎接口ClientProgramClientProgramCclientAPICclientAPIQueryCacheQueryCacheStorageEngineImplementationsStorageEngineImplementationsMyISAMhandlerandlibraryMEMORYhandlerandlibraryInnoDBhandlerandlibraryNDBclusterhandlerandlibraryAndsoon...QueryParsingQueryParsingOptimizationOptimizationE