深入解析MySQLInnoDB引擎

《深入解析MySQLInnoDB引擎》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、InnoDBInternalsCalvinSunSeniorManager,TwitterWHOAMI?•CalvinSun(孙春生)，email:csun@twitter.com•JoinedTwitterinMar2013•SeniorManageratOracle,Feb2008–Mar2013•ManageratMySQL,Jan2006–Jan2008•Teamlead,productarchitectatPervasiveSoftware,Jul1997–Dec2005•MSincomputersciencefromUSTC(中国科Agenda

2、IntroductiontoInnoDBInnoDBDataFormatInnoDBLoggingInnoDBExecutionInnoDBOnlineOperationsInnoDBMonitoring&DiagnosticsGetInvolved!IntroductiontoInnoDBInnoDBTimeline19901995200020052010MySQLServerArchitectureMySQLServerEnterpriseManagementToolsandUtilitiesMySQLEnterpriseMonitorMyS

3、QLQueryAnalyzerMySQLWorkBenchBackup&RecoverySecurityReplicationClusterPartitioningINFORMATION_SCHEMAPERFORMANCE_SCHEMACSVNoSQLtoInnoDBviaMemcachedAPIFast,simpleaccesstoInnoDB•AccessedviaMemcachedAPIApplication•UseexistingMemcachedclientsNoSQLSQL•BypassesSQLtransformations(Memcache

4、d(MySQLClient)Protocol)SQL/NoSQLaccess•NoSQLforkey-valueoperationsmysqld•SQLforrichqueries,JOINs,MySQLServerMemcachedpluginFKs,etc.Implementation•Memcacheddaemonplug-intoInnoDBStorageEnginemysqld•MemcachedprotocolmappedtothenativeInnoDBAPI•Sharedprocessspaceforultra-lowlatencyInn

5、oDBFeatures-TransactionsFulltransactionsupport•Atomicity•Consistency•Isolation•DurabilitySQL-standardisolationlevelsRow-levellockingMulti-versionconcurrencycontrol(MVCC)AutomaticdeadlockdetectionPlus•Automaticcrashrecovery•ReferentialintegrityInnoDBDesignConsiderationsModele

6、donGray&Reuter’s“TransactionProcessing:Concepts&Techniques”•NextkeylockingAlsoemulatedtheOraclearchitecture•Multi-versionconcurrencycontrol(MVCC)•Undoinfointhedatabase,notthelogs•Tablespacesfordata&indexstorageAddeduniquesubsystems/featuresInnoDBInnovativeFeaturesAdaptiveHashInd

7、exes:automaticallycreatedonprefixofkeyforfrequentqueries•Approximatesin-memorydatabasesChangeBuffering:buffersmodificationstosecondaryindexeswhentheleafpagesarenotinthebufferpool•BatchedmergesresultinlessrandomaccesspatternsDoublewriteBuffer:datafirstwrittenintothebuffer,thenflus

8、htothedatafiles•Preventing