转贴一个有关MYSQL的文章.E文的.MySQL's Query Cache

北大青鸟大学城校区logo 北大青鸟大学城校区
招生简章校园环境师资力量就业明星招生问答软件工程师北京大学学历学员项目联系我们 报名通道

免费在线咨询通道>>

免费在线报名通道>>

北大青鸟报名电话
当前位置:北大青鸟 > 软件教程 > web开发教程 >

转贴一个有关MYSQL的文章.E文的.MySQL's Query Cache

标签:   分类:web开发教程


Atypicalscenario
Boss:OurnewWebsiteiscrawling!Howcanitbe,wehavefourstate-of-the-artwebservers-what'stheproblem?
You:Well,thewebserversarefine-it'sthedatabaseserverthat'sstruggling.
Boss:What?YoutoldmethisMySQLthingwasfast,thatwedidn'tneedOracle,andnowyousayitcan'tcope!Howcanthisbe?
You:Well,thewebserversarebehavingsowellthatthey'repushingthroughlotsofqueries,andthedatabasecan'tmanagetoprocessallofthematthesametime.It'sonlyonedatabase,andlotsofwebservers...
Boss:It'stoolatetobuyOraclenow-whatarewegoingtodo!?
BigBosstoBoss(intheboss'smind):Thisprojecthasbeenadisasterfromthebeginning-nowyouwantmetodelayitwhileweinstallanewdatabase,andspendawholelotmore!Doyouthinkwe'remadeofmoney!?I'mcallinginsomeonewhoknowswhatthey'redoing-you'rehistorybuddy.
Colleague(abouttotakeyourjob):Wait,IthinkIcansolvetheproblem!

So,whatdoesyourcolleagueknowthatyoudon't?Howcanhesavethedayandletthebossgetallthecredit?Ourscenarioistooimprecisetogeneralize,andtherearemanypossiblesolutions.Youcanreadaboutoptimizingqueriesandindexes,optimizingbyimprovingthehardware,andtweakingtheMySQLvariables,usingtheslowquerylog,andofcourse,thereareothermethodssuchasreplication.However,MySQL4providesonefeaturethatcanproveveryhandy-aquerycache.Inasituationwherethedatabasehastorepeatedlyrunthesamequeriesonthesamedataset,returningthesameresultseachtime,MySQLcancachetheresultset,avoidingtheoverheadofrunningthroughthedataoverandover.Usually,youwouldwanttoimplementsomesortofcachingonthewebserver,buttherearetimeswhenthisisnotpossible,andthenitisthequerycacheyouwilllooktoforhelp.



Settingupthequerycache
TomakesureMySQLusesthequerycache,thereareafewvariablesyouneedtosetintheconfigurationfile(usuallymy.cnformy.ini).First,isthequery_cache_type.Therearethreepossiblesettings:0(foroff,donotuse),1(foron,cachequeries)and2(ondemand,discussedmorebelow).Toensureitisalwayson,place:

query-cache-type=1
intheconfigurationfile.Ifyoustartedtheserverhavingonlymadethischange,youwouldseethefollowingcachevariablesset:

mysql>SHOWVARIABLESLIKE'%query_cache%';
------------------- ---------
|Variable_name |Value |
------------------- ---------
|have_query_cache |YES |
|query_cache_limit|1048576|
|query_cache_size |0 |
|query_cache_type |ON |
------------------- ---------
4rowsinset(0.06sec)

NotethattheseareresultsfromMySQL4.0.x-you'llseemoreinversions4.1.xandbeyond.Thequery_cache_typewillbesettoONorOFFasappropriate.However,thereisonemoretoset,andthatisthequery_cache_size.Ifsetto0(thedefault),thecachewillbedisabled.Thisvariabledeterminesthememory,inbytes,usedforthequerycache.Forourpurposes,wewillsetitto20MB:

query-cache-size=20M
Theamountisshowninbytes:

mysql>SHOWVARIABLESLIKE'%query_cache%';
------------------- ----------
|Variable_name |Value |
------------------- ----------
|have_query_cache |YES |
|query_cache_limit|1048576 |
|query_cache_size |20971520|
|query_cache_type |ON |
------------------- ----------
4rowsinset(0.06sec)

若有疑问请拨打北大青鸟咨询热线:010-80146691或点击免费在线咨询!
  • xml地图 网站地图 招生简章 合作企业 学员项目 联系我们
  • 关闭窗口