教程專用隨堂mysql03

MySQLMySQL主講：馬永亮(馬哥QQ群客服QQ：2813150558,MySQLStorageMySQLStorageBackupandCachingandMySQLArchitactureMySQLArchitactureandStorageEngines主講：馬永亮(馬哥QQ群客服QQ：2813150558,ThetopmostlayercontainsThetopmostlayercontainstheservicesthataren’tuniquetoServicesmostnetwork-basedclient/servertoolsorserversneed:connectionhandling,authentication,security,andsoThesecondMuchofMySQL’sbrainsarehere,includingthecodeforqueryparsing,analysis,optimization,caching,andallthebuilt-infunctions(e.g.,dates,times,math,andencryption)Anyfunctionalityprovidedacrossstorageengineslivesatthislevel:storedprocedures,triggers,andviews,forexampleThethirdlayercontainsthestorageTheyareresponsibleforstoringandretrievingalldatastored“in”MySQLthe EachEachclientconnectiongetsitsownthreadwithintheserverTheconnection’squeriesexecutewithinthatsinglethread,whichinturnresidesononecoreorCPUTheservercachesthreads,sotheydon’tneedtobecreatedanddestroyedforeachnewconnectionWhenclients(applications)connecttotheMySQLserver,theserverneedstoauthenticatethemAuthenticationisbasedonusername,originatinghost,andX.509certificatescanalsobeusedacrossanSecureSocketsLayer(SSL)connectionOnceaclienthasconnected,theserververifieswhethertheclienthasprivilegesforeachqueryitissuesMySQLMySQLparsesqueriestocreateaninternalstructure(theparsetree),andthenappliesavarietyofoptimizations,suchasrewritingthedeterminingtheorderinwhichitwillreadchoosingwhichindexestoYoucanpasshintstotheoptimizerthroughspecialkeywordsinthequery,affectingitsdecision-makingYoucanalsoasktheservertoexplainvariousaspectsofoptimizationBeforeevenparsingthequery,though,theserverconsultsBeforeevenparsingthequery,though,theserverconsultsthequerycache,whichcanstoreonlySELECTstatements,alongwiththeirresultsetsIfanyoneissuesaquerythat’sidenticaltoonealreadyinthecache,theserverdoesn’tneedtoparse,optimize,orexecutethequeryatall—itcansimplypassbackthestoredresultsetTheoptimizerdoesnotreallycarewhatstorageengineaparticulartableuses,butthestorageenginedoesaffecthowtheserveroptimizesqueryAnytimeAnytimemorethanonequeryneedstochangedataatthesametime,theproblemofconcurrencycontrolMySQLhastodothisattwolevels:theserverlevelandthestorageenginelevelLikeemailboxonUnixTheThesolutiontothisclassicproblemofconcurrencycontrolisrathersimpleSystemsthatdealwithconcurrentread/writeaccesstypicallyimplementalockingsystemthatconsistsoftwolocktypesTheselocksareusuallyknownassharedlocksandexclusivelocks,orreadlocksandwritelocksOneOnewaytoimprovetheconcurrencyofasharedresourceistobemoreselectiveaboutwhatyoulockRatherthanlockingtheentireresource,lockonlythepartthatcontainsthedatayouneedtochangeTheproblemislocksconsumeEverylockoperation—gettingalock,checkingtoseewhetheralockisfree,releasingalock,andsoon—hasoverheadAlockingstrategyisacompromisebetweenlockoverheadanddatasafety,andthatcompromiseaffectsperformanceLockmanagementisaveryimportantdecisioninstorageenginedesign;fixingthegranularityatacertainlevelcangivebetterperformanceforcertainuses,yetmakethatenginelesssuitedforotherpurposesTheThemostbasiclockingstrategyavailableinMySQL,andtheonewiththelowestoverhead,istablelocksTablelockshavevariationsforgoodperformanceinspecificsituationsForexample,READLOCALtablelocksallowsometypesofconcurrentwriteoperationsWritelocksalsohaveahigherprioritythanreadTheThelockingstylethatoffersthegreatestconcurrency(andcarriesthegreatestoverhead)istheuseofrowRow-levellocking,asthisstrategyiscommonlyknown,isavailableintheInnoDBandFalconstorageengines,amongothersRowlocksareimplementedinthestorageengine,nottheserverAAtransactionisagroupofSQLqueriesthataretreatedatomically,asasingleunitofworkTransactionsaren’tenoughunlessthesystempassestheACIDtestAtransactionmustfunctionasasingleindivisibleunitofworksothattheentiretransactioniseitherappliedorrolledbackThedatabaseshouldalwaysmovefromoneconsistentstatetothenextTheresultsofatransactionareusuallyinvisibletoothertransactionsuntilthetransactioniscompleteOncecommitted,atransaction’schangesareTheSQLstandarddefinesfourisolationlevels,withspecificrulesTheSQLstandarddefinesfourisolationlevels,withspecificrulesforwhichchangesareandaren’tvisibleinsideandoutsideaREADTransactionscanviewtheresultsofuncommittedREADAtransactionwillseeonlythosechangesmadebytransactionsthatwerealreadycommittedwhenitbegan,anditschangeswon’tbevisibletoothersuntilithascommittedREPEATABLEItguaranteesthatanyrowsatransactionreadswill“l(fā)ookthesame”insubsequentreadswithinthesametransaction,butintheoryitstillallowsanothertrickyproblem:Solvesthephantomreadproblembyforcingtransactionstobeorderedsothattheycan’tpossiblyconflictuser-defineduser-definedpointsthatcanbeusedtomarksubstageswithinatransactionSavepointswithinatransactionaresetwiththeSAVEPOINTcommand,whichacceptsauser-definedTheROLLBACKTOSAVEPOINTcommandcanthenbeusedtorollanin-progresstransactionbacktothenamedsavepoint,reversingallchangesmadeaftertheMySQLstillenablesuserstoimplementaprimitiveformMySQLstillenablesuserstoimplementaprimitiveformoftransactionsthroughtheuseoftablelocksTablePageAlockappliedtoaportionofatableknownasaBDB(BerkeleyDB)tablesuselevellockingon8-KBRowAdeadlockiswhentwoormoretransactionsaremutuallyAdeadlockiswhentwoormoretransactionsaremutuallyholdingandrequestinglocksonthesameresources,creatingacycleofdependenciesDeadlocksoccurwhentransactionstrytolockresourcesinadifferentorderTocombatthisproblem,databasesystemsimplementvariousformsofdeadlockdetectionandtimeoutsLockbehaviorandorderarestorageengine-specific,sosomestorageenginesmightdeadlockonacertainsequenceofstatementseventhoughotherswon’tTransactionTransactionlogginghelpsmaketransactionsmoreInsteadofupdatingthetablesondiskeachtimeachangeoccurs,thestorageenginecanchangeitsinmemorycopyofthedataThestorageenginecanthenwritearecordofthechangetothetransactionlog,whichisondiskandthereforedurableThisisalsoarelativelyfastoperation,becauseappendinglogeventsinvolvessequentialI/OinonesmallareaofthediskinsteadofrandomI/OinmanyplacesThen,atsomelatertime,aprocesscanupdatethetableondiskMySQLABprovidesthreetransactionalstorageengines:MySQLABprovidesthreetransactionalstorageengines:InnoDB,NDBCluster,andFalcon,xtraDBSeveralthird-partyenginesarealsoavailable;thebest-knownenginesrightnowaresolidDBandPBXTMixingstorageenginesinImplicitandexplicitMySQLMySQLoperatesinAUTOCOMMITmodebyYoucanenableordisableAUTOCOMMITforthecurrentconnectionbysettingavariableChangingthevalueofAUTOCOMMIThasnoeffectonnontransactionaltables,suchasMyISAMorMemorytables,whichessentiallyalwaysoperateinAUTOCOMMITmodeIfIfyoumixtransactionalandnontransactionaltables(forinstance,InnoDBandMyISAMtables)inatransaction,thetransactionwillworkproperlyifallgoeswellHowever,ifarollbackisrequired,thechangestothenontransactionaltablecan’tbeundoneMySQLwillnotusuallywarnyouorraiseerrorsifyoudotransactionaloperationsonanontransactionaltableInnoDBusesatwo-phaselockingprotocol.ItcanacquireInnoDBusesatwo-phaselockingprotocol.Itcanacquirelocksatanytimeduringatransaction,butitdoesnotreleasethemuntilaCOMMITorItreleasesallthelocksatthesameThelockingmechanismsdescribedearlierareallInnoDBhandleslocksautomatically,accordingtoyourisolationlevelHowever,However,InnoDBalsosupportsexplicitlocking,whichtheSQLstandarddoesnotmentionatall:SELECT...LOCKINSHARESELECT...FORMySQLalsosupportstheLOCKTABLESandUNLOCKTABLEScommandsImplementedintheserver,notinthestorageThesehavetheiruses,buttheyarenotasubstituteforIfyouneedtransactions,useatransactionalstorageMostMostofMySQL’stransactionalstorageengines,userow-levellockinginconjunctionwithatechniqueforincreasingconcurrencyknownasVItavoidstheneedforlockingatallinmanycasesandcanhavemuchloweroverheadMVCCworksbykeepingasnapshotofthedataasitexistedatsomepointintimeThismeanstransactionscanseeaconsistentviewofthedata,nomatterhowlongtheyrunItalsomeansdifferenttransactionscanseedifferentdatainthesametablesatthesametimeAAisasubsystemthatmanagesMostdatabasemanagementsystemshaveonesubsystemtomanagetables;MySQLServercanusedifferentsubsystems.Becauseastorageengineisappliedatthetablelevel,itissometimescalledeTheMySQLpluggablestorageengineisanarchitecturaldesignthatseparatesthecodethatmanagesthetablesfromthedatabaseservercoreThiscorecodemanagesthecomponentssuchasthequerycache,theoptimizer,andtheconnectionThestorageenginecodehandlestheactualI/OoftheMyISAMMyISAMisthedefaultstorageengineinmysqld,andisthestorageengineusedbythesystemtablesMyISAMprovidesagoodcompromisebetweenperformanceandusefulfeatures,suchasfull-textindexing,compression,andspatial(GIS)functionsMyISAMdoesn’tsupporttransactionsorrow-levelFeatureFeatureNon-NoforeignkeyFULLTEXTindexesfortextNodataIndexcaches—canbespecifiedbyImplementsbothHASHandBTREEindexes(BTREEbyTable-levelVeryfastreadactivity,suitablefordataCompresseddata(withOnlinebackupwithMaximumof64indexesperThreeThreeutilityprogramsaredesignedforworkingwithMyISAMtables:myisamchk—Usedtoanalyze,optimize,andrepairMyISAMtablesmyisampack—Usedtocreatecompressed,read-onlyMyISAMtablesTheThemyisamchkprogramhasfourdifferentmodesofItcanbeusedtoanalyze,optimize,check,andrepairMyISAMtablesThedefaultmodeofoperationisthecheckmodewhereitchecksforpossiblecorruptionTocreatecompressedread-onlyTocreatecompressedread-onlyversionsoftablescanprovideforagoodvperformanceincreasefordatathatisnolongerbeingupdatedbutstillneedstobeAcompressedMyISAMtableisread-only,andcannothavenewrowsinsertedlikeanArchivetablecanOncethecompressionisdoneyouhavetorunthemyisamchkprogramtorebuildindexes--TheMergestorageTheMergestorageengineisactuallyasortofwrappertablethatwrapsaroundMyISAMtableswiththesameschemasAlltheunderlyingtablescanbequeriedatoncebyqueryingtheMergetableMergetableswillusethesamebuffersandconfigurationoptionsasfortheunderlyingMyISAMtablesWhenyoucreateaMergetable,twofilesarealwayscreatedinthefilesystemThereisonefilecontainingthetableformatthathasafilenameofthetablefollowedbyasuffixof.frmThesecondfilealsohasafilenameofthetablebutendswithasuffixof.MRGYoucanalsoputtheindividualMyISAMtablesondifferentharddrivestohelpincreaseperformanceInnoDBwasdesignedfortransactionprocessing—specifically,processingofmanyshort-livedtransactionsInnoDBwasdesignedfortransactionprocessing—specifically,processingofmanyshort-livedtransactionsthatusuallycompleteratherthanbeingrolledbackInnoDBstoresitsdatainaseriesofoneormoredatafilesthatarecollectivelyknownasatablespaceAtablespaceisessentiallyablackboxthatInnoDBmanagesallbyitselfInnoDB’sindexstructuresareverydifferentfromthoseofmostotherMySQLstorageengines.Asaresult,itprovidesveryfastprimarykeylookupsFeatureFeatureTransactionalsupportprovidedbyMVCC(MultiVersionConcurrencyControl)Row-levelForeignkeyIndexingusingclusteredB-treeConfigurablebuffercachingofbothindexesandOnlinenon-blockingbackupthroughseparatecommercialbackupprogramAAtablespaceisalogicalgroupofoneormoredatafilesinadatabaseThefullpathtoeachsharedtablespaceisformedbyaddinginnodb_data_home_dirtoeachpathspecifiedintheinnodb_data_file_pathBydefaultifinnodb_data_file_pathisnotdefined,a10Mbibdata1fileiscreatedinthedatadirectorymysql>SHOWENGINEINNODBByBydefaultInnoDBusesasharedtablespaceforalltablesthatconsistsofoneormorefilesThesefilesareusedtostoremetadataforalltheInnoDBtablesacrossalldatabasesontheserver,andareusuallyreferredtoasibdatafiles,becausethedefaultnameforthefirstfileisibdata1Bydefault,ibdatafilesalsocontainallthedataandindexesforalltheInnoDBtablesWhenanInnoDBtableisdefragmentedinasharedtablespaceconfiguration,theibdatafileswillnotshrink,eventhoughthedataandindexesaresuccessfullyBydefaultibdatafilesarelocatedindatadir.AnyoptionsBydefaultibdatafilesarelocatedindatadir.AnyoptionsfortheInnoDBstorageengineshouldbelistedinthe[mysqld]directiveoftheconfigurationfileTheinnodb_data_home_diroptionisusedtospecifythelocationoftheibdatafilesexplicitly,tostorethemsomewhereotherthandatadirinnodb_data_home_dir=WhenWhendataischanged,affecteddatapagesarechangedintheinnodb_buffer_poolThen,thechangeiswrittentotheredolog,whichinmysqldisstoredintheInnoDBlogfiles(ib_logfile0andib_logfile1)Thepagesaremarkedasdirty,becausetheyhavebeenchanged,andeventuallygetflushedandwrittentodiskSomeofthedifferentparametersfortheInnoDBlogfilesSomeofthedifferentparametersfortheInnoDBlogfilestheirramificationsinnodb_log_files_in_groupissetwithadefaultofThelogfilesarewritteninacircularinnodb_log_file_sizeisthesizeofeachlogfileintheThetotal,combinedsizeofallthelogfileshastobelessthan40,thelogfilesarepurgedwhenmysqldshuts1,whichmeansthatthelogfilesarenotpurgedbeforea2,simulatesacrash,andatthenextstartupInnoDBwilldoainnodb_flush_log_at_trx_commitcontrolshowoftenthefilesarewritten0,tobewrittenandflushedtodiskonceper1,causesthelogbuffertobewrittenandflushedtodiskaftertransaction2,whichcausesthelogbuffertobewrittenaftereverycommitandflushesthelogfilestodiskonceperinnodb_log_buffer_sizeisthewritebufferforInnoDBlogUserUser主講：馬永亮(馬哥QQ群客服QQ：2813150558,TheMySQLaccesscontrolsystemenablesyoutocreateTheMySQLaccesscontrolsystemenablesyoutocreateMySQLaccountsanddefinewhateachaccountcandoAuserinMySQLisacombinationofausernameandhoststringInSQLstatementsthatrequireaccountnames,thenameisgivenin'user_name'@'host_name'formatMySQLMySQLkeepstheACLs(alsocalledgranttables)cachedinmemoryWhenausertriestovauthenticateorrunacommand,MySQLcheckstheauthenticationinformationandpermissionsagainsttheACLs,inapredeterminedorderIfyouarenotsurewhatuseryouareactuallyloggedinasyoucanusetheUSER()andCURRENT_USER()functionstodeterminehowyouareconnectedSHOWGRANTSstatementwithnoargumentsshowstheprivilegesfortheusertheconnectionwasauthenticated—theprivilegesfortheWildcardWildcardcharacters(%and_)areallowedinhostMySQLorderstheaccesscontrollistwiththeleastspecifichostslastHostnamesandIPswithoutwildcardsornetmasksareplacedbeforehostnamesandIPswithwildcardsandAlltheuserandpermissionAlltheuserandpermissioninformationisstoredinthemysqldatabaseinasetoftablesknownasthegranttablesInadditiontothegranttables,themysqldatabasehastablescontainingothersysteminformationForexample,atablecalledeventisusedbytheeventThetablesthatareofinterestwhenitcomes,andtheGRANT,REVOKE,CREATEUSER,DROPUSER,RENAMEUSERcommandsaredesignedforuserToTocreateauser,youcanusetheCREATEUSERTodropauser,youshouldusetheDROPUSERTheRENAMEUSERcommandrenamesanexistingSeveraltypesofprivilegescanbegrantedtoanPrivilegesshouldbegrantedaccordingtohowtheaccountistobeusedWhenWhenyouusestatementssuchasCREATEUSERandGRANT,theserverdeterminesthenecessarymodificationstothegranttablesandmakesthechangesforyouIfyoumodifythetablesdirectlywithastatementsuchasINSERTorDELETE,youmustexplicitlytelltheservertoreloadthetablesafterwardbyusingaFLUSHPRIVILEGESstatementoramysqladminflush-privilegescommandThereTherearealsosomespecialprivilegeALLandALLPRIVILEGESareshorthandfor"allprivilegesexceptGRANTOPTION."USAGEmeans"noprivileges"otherthanbeingallowedtoconnecttotheserverPrivilegescanexistatPrivilegescanexistatdifferentAnyprivilegecanbegrantedglobally.Anaccountpossessesaglobalprivilegecanexerciseitatanytime.GlobalprivilegesarethereforequitepowerfulandarenormallygrantedonlytoadministrativeaccountsSomeprivilegescanbegrantedforspecificdatabases:ALTER,CREATE,CREATETEMPORARYTABLES,CREATEVIEW,DELETE,DROP,GRANTOPTION,INDEX,INSERT,LOCKTABLES,SELECT,SHOWVIEW,andUPDATE.Adatabase-levelprivilegeappliestoalltablesandstoredroutinesintheSomeprivilegescanbegrantedforspecifictables:ALTER,CREATE,DELETE,DROP,GRANTOPTION,INDEX,INSERT,SELECT,andUPDATE.Atable-levelprivilegeappliestoallcolumnsinthetableSomeprivilegescanbegrantedforspecifictablecolumns:INSERT,SELECT,andUPDATESomeprivilegescanbegrantedforspecificstoredroutines:EXECUTE,ALTERROUTINE,andGRANTOPTIONTheuserTheusertablecontainsarecordforeachaccountknowntotheTheuserrecordforanaccountlistsitsglobalItalsoindicatesotherinformationabouttheaccount,suchasanyresourcelimitsitissubjectto,andwhetherclientconnectionsthatusetheaccountmustbemadeoverasecureconnectionusingtheSecureSocketsLayer(SSL)Thedbtablelistsdatabase-specificprivilegesforaccountsThetables_privtableliststable-specificprivilegesforaccountsThecolumns_privtablelistscolumn-specificprivilegesforTheprocs_privtablelistsprivilegesthataccountshaveforstoredproceduresandfunctionsGRNATprivilegesIDENTIFIEDBYGRNATprivilegesIDENTIFIEDBYWhengrantingprivilegesforatableorstoredroutine,thereisanambiguityiftherearemultipleobjects(table,procedure,orfunction)withthesamename.Toindicatethetypeofobjectexplicitly,usethekeywordTABLE,PROCEDURE,orFUNCTIONprecedingthename:ONTABLEONPROCEDUREONFUNCTIONTograntprivilegesatacolumn-specificlevel,useanONclausethatnamesaparticulartable,andspecifyacomma-separatedlistofcolumnnameswithinparenthesesaftereachprivilegetobegrantedGRANTONIfyouwanttogiveanaccountthecapabilitytograntitsprivilegestootheraccounts,addaWITHGRANTOPTIONclausetothestatement.TheThehostpartofanaccountnamemaybegiveninanyofthefollowingformats:ThenameAhostname,suchasAnIPnumber,suchasApatterncontainingthe'%'or'_'wildcardSuchas%.orAhostvalueof%matchesanyhost,allowingtheclienttoconnectfromanywhereAnIPnumber/netmaskThevalueallowsaclienttoconnectfromanyhostwithanaddressthatmatchestheIPnumberforallbitsthatare1inthenetmaskForexample,avalueof/matchesanyhostwith10.0.0inthefirst24bitsofitsIPnumberToTofindoutwhatprivilegesaparticularaccounthas,usetheSHOWGRANTSstatementItdisplaystheGRANTstatementsthatwouldberequiredtosetuptheaccountToseeyourownprivileges,useSHOWGRANTSwithoutanaccountnameorwithCURRENT_USER():SHOWSHOWGRANTSFORToseetheprivilegesforaspecificaccount,specifythataccountnameinthestatementSHOWGRANTFORUsetheREVOKEstatementUsetheREVOKEstatementtorevokeprivilegesfromanaccountREVOKEprivilegesFROMTorevoketheGRANTOPTIONprivilegefromanaccountthathasit,youmustrevokeitinaseparateREVOKEGRANTOPTIONdb.tableUseUsetheSETPASSWORDstatement,specifyingtheaccountnameandthenewpasswordSETPASSWORDFOR‘user’@’host’=Anynon-anonymousclientcanchangeitsownpasswordbyomittingtheFORclause:SETPASSWORD=UseGRANTwiththeUSAGEprivilegespecifieratthegloballevelandanIDENTIFIEDBYclause:GRANTUSAGEON*.*TO‘user'@‘host'BYUSAGEmeans"noprivileges,"sothestatementchangesthepasswordwithoutgrantinganyprivileges.TheThe--skip-grant-tablesoptiontellstheservernottousethegranttablestocontrolclientaccessAnyonecanconnectfromanywherewithnopassword,and,onceconnected,hasfullprivilegestodoanythingThatisconvenientifyou'veforgottentherootpasswordandneedtoresetit,becauseyoucanconnectwithoutknowingthepasswordTopreventremoteclientsfromconnectingoverTCP/IP,youmaywanttousethe--skip-networkingoptionaswell--skip-grant-tablesdisablestheaccount-managementstatements(CREATEUSER,GRANT,REVOKE,SETPASSWORD,andsoforth)TableTableandcolumnprivilegechangesapplytoallstatementsissuedafterthechangesaremadeDatabaseprivilegechangesapplywiththenextUSEChangestoglobalprivilegesandpasswordsdonotapplytoaconnectedclientCachingwithCachingwith主講：馬永亮(馬哥QQ群客服QQ：2813150558,OneOneofthewaysofmakingqueriesrespondfasteristoimplementcachingTheservercanskiptheSQLparsingandoptimizationstagesforrepeatedqueriesTherearethreecommonwaystocachedatawithCreatingmanualcacheUsinginternalcachinginUsingthememcacheddistributedcachingItonlycachesSELECTstatementsandtheircorrespondingItonlycachesSELECTstatementsandtheircorrespondingresultsets,andonlyfordeterministicSELECTstatementsDeterministicSELECTstatementsarestatementsthatalwaysproducethesameresultsnomatterwhat,giventhesameThequerycachestoresarudimentaryhashofthequeryalongwithitsresultsetThequerybeingcomparedmustbebyte-for-byteidenticaltothecachedquery—thequeriesthemselvesarenotcompared;theirhashesarecompared.SothefollowingtwoaredifferentSELECTcustomer_id,namefromSELECTcustomer_id,nameFROMQueriesareviewedasdifferentbythequerycacheiftheyusedifferentdatabases,protocolversions,orcharactersetsFurthermore,notQueriesareviewedasdifferentbythequerycacheiftheyusedifferentdatabases,protocolversions,orcharactersetsFurthermore,notallSELECTstatementswillbecached.SELECTstatementswiththefollowingpropertiesarenotcached:Useofnon-deterministicMostUseofuser-definedUseoftemporarySELECTstatementsinstoredfunctions,triggers,views,andResultsetslargerthanquery_cache_limit(1MbbyUsingQuerycacheinvalidationiswhenaqueryisremovedfromQuerycacheinvalidationiswhenaqueryisremovedfromthequerycachebecauseitsresultsetmayhaveWhenatableismodifiedbyeitherDDLorDML(eitherschemachangesordatachanges),allofthequeriesinthequerycachereferencingthattableareinvalidatedandremoved.QueryQuerycachepruningiswhenaqueryisremovedfromthequerycachebecausethequerycachecannotfindafreeblockinwhichtostoreanewqueryThequerycacheprunesolderentriesusingaleastrecentlyused(LRU)algorithmCachingCachingqueriesisn’tautomaticallymoreefficientthannotcachingIntheory,youcantellwhetherthecacheishelpfulbycomparingtheamountofworktheserverhastodowiththecacheenabledanddisabledIt’shardtoaccuratelycalculateorpredictthequerycache’sThetypeofquerythatbenefitsmostfromcachingisonewhoseresultisexpensivetogeneratebutdoesn’ttakeupmuchspaceinthecachequerycachehitQcache_hits/Com_selectisthestatusvariablethatholdsthenumberofexecutedSELECTqueriesthatgothroughthefullexecutionplanAnyAnySELECTquerythatMySQLdoesn’tservefromthecacheisasAcachemisscanoccurforanyofthefollowingThequeryisnotcacheable,eitherbecauseitcontainsanondeterministicconstruct(suchasCURRENT_DATE)orbecauseitsresultsetistoolargetostoreTheserverhasneverseenthequerybefore,soitneverhadachancetocacheitsresultThequery’sresultwaspreviouslycached,buttheserverremoveditThiscanhappenbecausetherewasn’tenoughmemorytokeepbecausesomeoneinstructedtheservertoremoveorbecauseitwasTheThequerycachefortheserverisstoredcompletelyinmemory,anddoesnotpersistacrossmysqldrestartsAsidefromabout40kofmemoryusedbythequerycache,theentirepoolofmemoryisinitializedasasinglecontiguouschunkofmemoryDuringquerycacheoperationthischunkofmemoryisdividedupintosmallerblocksEachblockmustbeatleastthesizespecifiedbyquery_cache_min_res_unit,whichdefaultsto4KbInadditiontotheseblockscontainingtheresultsetsofthequeriestherearetwohashtablesQueryThelistofdatabasetablesthateachcachedqueryWhenatableismodified,thissecondhashtableisconsultedtodeterminewhichqueriesshouldberemovedduetoquerycacheSevensystemvariablesareusedtocontroltheconfigurationSevensystemvariablesareusedtocontroltheconfigurationandmemoryusageofthequerycacheThevaluesofthesecanbeseenbyusingSHOWGLOBALVARIABLESstatementorintheGLOBAL_VARIABLESsystemviewintheThereThereareeightstatusvariablesrelatingtothequeryThesevariablesareusedformanagingandtuningthequerycacheThevaluesofthesecanbeseenbyusingtheSHOWGLOBALSTATUSstatementorintheGLOBAL_STATUSsystemviewintheWithWithaquerycachetherearetwocausesofTheresultsetsdonotalwaysfitexactlyintoablockofTheinvalidationofqueriesstoredinthecache.WhenaqueryisinvalidateditisdeletedfromthecacheThebestwaytohandlequerycachefragmentationistominimizetheamountoffragmentationTominimizefragmentationistosettheblocksizeofthequerycachetotheaverageresultsetsizeofyour(query_cache_size-TominimizethequerycacheinvalidationandThememcachedcachingsystemisadistributed,in-memorycachesystemthatThememcachedcachingsystemisadistributed,in-memorycachesystemthatspeedsupwebapplicationsbyminimizingtheamountofdatabasequeriesneededThebasicprincipleofmemcachedisthatanumberofmemcacheddaemonsworktogethertoprovidedataisd worktogethertocacheBackupsandBackupsand主講：馬永亮(馬哥QQ群客服QQ：2813150558,BackupsandBackupsand主講：馬永亮(馬哥客服MySQLMySQL主講：馬永亮(馬哥客服bufferbuffer錯錯誤日一般查詢日慢查詢日二進制日中繼日事務日志(redo,查查詢日general-general-log-CREATEDATABASECREATEDATABASEUPDATEtbSETINSERINTOtbSET慢慢查詢日log-slow-long-query-slow-query-slow-query-log-錯錯誤日log-log-二二進制日binlog-sql-log-max-binlog- 中中繼日FourFourlogfilesareusedbytheerrorthebinarythegeneralquerytheslowqueryAfifthlogtype,therelaylog,isusedbyaslaveIfthelogsareenabled,mysqldwritesthemtothedatadirectoryunlessotherwisespecifiedBydefault,nologgingisContainsentriesContainsentriesforwhenthemysqlddaemonstartedandstoppedandalsoanycriticalerrorsthatoccurwhiletheserverisrunningInformationsuchaswhentheeventschedulerexecutesaneventandwhenreplicationstartsandstopsisalsowrittentotheerrorlog服務器啟動和關閉過程中的信服務器運行過程中的錯誤信事件調度器運行一個事件時產生的信在從服務器上啟動從服務器進程時產生的信ToenableToenabletheerrorlog,specifythelog-errorTheerrorlogwillbewrittentothedatadirectoryusingtheformathost_name.errTocustomizethefilename,givethelog-erroroptionafilename,suchas:log-Thelog_warningsoptionisusedtocontrolwhetherornotwarningmessagesareloggedtotheerrorlogThedefaultvalueis1Ifthevalueisgreaterthan1,abortedconnectionsarewrittentotheerrorlogIfyoudonotspecifylog-error,orifyouusethe--consoleoptiononWindows,errorsarewrittentostderr,thestandarderroroutputTheThebinarylogsareusedforseveralCanbeusedtoperformapoint-in-timerecoveryduringarecoveryprocess做即時點恢Canbeusedtoenable復Thecontentsofthebinarylogareanystatementsthatoccurintheserverthatcouldpotentiallymodifythedatabases二進制，二進二進制，二進制日志事InsertInsertintostudents(BirthDate)VALUE‘2012-04-08ToToenablebinarylogging,usethelog-binThebinarylogindexfileisaplaintextfilethatkeepstrackofthecurrentbinarylogsBydefault,itsnameismysql-Tosetthefilenameandpathofthebinarylogsandbinarylogindexfile,specifythefollowingoptions:log-bin=log-bin-index=BinaryBinarylogdataisstoredinabinaryTodisplaythebinarylogsintext/readableformatyoumustusethemysqlbin