高性能MySQL

图书标准信息

• 作者 Baron Schwartz 著
• 出版社 东南大学出版社
• 出版时间 2012-06
• 版次 3
• ISBN 9787564134457
• 定价 98.00元
• 装帧 平装
• 开本 16开
• 纸张 胶版纸
• 页数 793页
• 字数 1013千字

【内容简介】

如何充分发挥mysql数据库的所有能力？在《高性能MySQL（第3版）（影印版）》一书中，你将学到与mysql数据库模式、索引和查询设计相关的所有高级技巧，通过使用这些技巧，你将能够对mysql数据库服务器，操作系统和硬件进行润优以发挥它们的最大潜力。这本指南同时也提供了通过复制、负载均衡，高可用性及故障转移等技术对应用进行扩展的安全实用方法。
第三版中更新了mysql数据库和innodb存储引擎在性能、特性和工具等相关领域取得的最新进展，不仅通过大量的特定示例讲解了mysql数据库如何工作，同时也围绕mysql数据库的设计原则、采用生动的故事和案例研究的形式解释了其对应的工作原理。本书教给读者如何采用mysql的方式进行思考。
学习mysql5．5版提供的新特性，包括存储过程。数据库分区、触发器和视图。
实现在复制、高可用性和集群上的改进。
实现mysql在云环境中运行的高性能。
优化高级查询特性，如全文检索。
充分利用多核处理器和固态硬盘等硬件带来的优势。
探索备份和恢复策略——包括新的在线热备份工具。

【作者简介】

BaronSchwartz是Percona公司的首席性能架构师。他的主要工作是通过创建一系列工具和技术来提高MySQL数据库的易用性和可靠性。

【目录】

foreword
preface
1.mysqlarchitectureandhistory
mysql'slogicalarchitecture
connectionmanagementandsecurity
optimizationandexecution
concurrencycontrol
read/writelocks
lockgranularity
transactions
isolationlevels
deadlocks
transactionlogging
transactionsinmysql
multiversionconcurrencycontrol
mysql'sstorageengines
theinnodbengine
themylsamengine
otherbuilt-inmysqlengines
third-partystorageengines
selectingtherightengine
tableconversions
amysqltimeline
mysql'sdevelopmentmodel
summary
2.benchmarkingmysql
whybenchmark?
benchmarkingstrategies
whattomeasure
benchmarkingtactics
designingandplanningabenchmark
howlongshouldthebenchmarklast?
capturingsystemperformanceandstatus
gettingaccurateresults
runningthebenchmarkandanalyzingresults
theimportanceofplotting
benchmarkingtools
full-stacktools
single-componenttools
benchmarkingexamples
httpload
mysqlbenchmarksuite
sysbench
dbt2tpc-conthedatabasetestsuite
percona'stpcc-mysqltool
summary
3.profilingserverperformance
introductiontoperformanceoptimization
optimizationthroughprofiling
interpretingtheprofile
profilingyourapplication
instrumentingphpapplications
profilingmysqlqueries
profilingaserver'sworkload
profilingasinglequery
usingtheprofileforoptimization
diagnosingintermittentproblems
single-queryversusserver-wideproblems
capturingdiagnosticdata
acasestudyindiagnostics
otherprofilingtools
usingtheuser_statisticstables
usingstrace
summary
4.optimizingschemaanddatatypes
choosingoptimaldatatypes
wholenumbers
realnumbers
stringtypes
dateandtimetypes
bit-packeddatatypes
choosingidentifiers
specialtypesofdata
schemadesigngotchasinmysql
normalizationanddenormalization
prosandconsofanormalizedschema
prosandconsofadenormalizedschema
amixtureofnormalizedanddenormalized
cacheandsummarytables
materializedviews
countertables
speedingupaltertable
modifyingonlythefrmfile
buildingmylsamindexesquickly
summary
5.indexingforhighperformance
indexingbasics
typesofindexes
benefitsofindexes
indexingstrategiesforhighperformance
isolatingthecolumn
prefixindexesandindexselectivity
multicolumnindexes
choosingagoodcolumnorder
clusteredindexes
coveringindexes
usingindexscansforsorts
packed(prefix-compressed)indexes
redundantandduplicateindexes
unusedindexes
indexesandlocking
anindexingcasestudy
supportingmany,kindsoffiltering
avoidingmultiplerangeconditions
optimizingsorts
indexandtablemaintenance
findingandrepairingtablecorruption
updatingindexstatistics
reducingindexanddatafragmentation
summary
6.queryperformanceoptimization
whyarequeriesslow?
slowquerybasics:optimizedataaccess
areyouaskingthedatabasefordatayoudon'tneed?
ismysqlexaminingtoomuchdata?
waystorestructurequeries
complexqueriesversusmanyqueries
choppingupaquery
joindecomposition
queryexecutionbasics
themysqlclient/serverprotocol
thequerycache
thequeryoptimizationprocess
thequeryexecutionengine
returningresultstotheclient
limitationsofthemysqlqueryoptimizer
correlatedsubqueries
unionlimitations
indexmergeoptimizations
equalitypropagation
parallelexecution
hashjoins
looseindexscans
min0andmax()
selectandupdateonthesametable
queryoptimizerhints
optimizingspecifictypesofqueries
optimizingcount()queries
optimizingjoinqueries
optimizingsubqueries
optimizinggroupbyanddistinct
optimizinglimitandoffset
optimizingsql_calc_found_rows
optimizingunion
staticqueryanalysis
usinguser-definedvariables
casestudies
buildingaqueuetableinmysql
computingthedistancebetweenpoints
usinguser-definedfunctions
summary
7.advancedmy$qlfeatures
partitionedtables
howpartitioningworks
typesofpartitioning
howtousepartitioning
whatcangowrong
optimizingqueries
mergetables
views
updatableviews
performanceimplicationsofviews
limitationsofviews
foreignkeyconstraints
storingcodeinsidemysql
storedproceduresandfunctions
triggers
events
preservingcommentsinstoredcode
cursors
preparedstatements
preparedstatementoptimization
thesqlinterfacetopreparedstatements
limitationsofpreparedstatements
user-definedfunctions
plugins
charactersetsandcollations
howmysqlusescharactersets
choosingacharactersetandcollation
howcharactersetsandcollationsaffectqueries
full-textsearching
natural-languagefull-textsearches
booleanfull-textsearches
full-textchangesinmysql5.1
full-texttradeoffsandworkarounds
full-textconfigurationandoptimization
distributed(xa)transactions
internalxatransactions
externalxatransactions
themysqlquerycache
howmysqlchecksforacachehit
howthecacheuesmemory
whenthequerycacheishelpful
howtoconfigureandmaintainthequerycache
innodbandthequerycache
generalquerycacheoptimizations
alternativestothequerycache
summary
8.optimizingserversettings
howmysql'sconfigurationworks
syntax,scope,anddynamism
sideeffectsofsettingvariables
gettingstarted
iterativeoptimizationbybenchmarking
whatnottodo
creatingamysqlconfigurationfile
inspectingmysqlserverstatusvariables
configuringmemoryusage
howmuchmemorycanmysqluse?
per-connectionmemoryneeds
reservingmemoryfortheoperatingsystem
allocatingmemoryforcaches
theinnodbbufferpool
themyisamkeycaches
thethreadcache
thetablecache
theinnodbdatadictionary
configuringmysql'si/obehavior
innodbi/oconfiguration
myisami/oconfiguration
configuringmysqlconcurrency
innodbconcurrencyconfiguration
mylsamconcurrencyconfiguration
workload-basedconfiguration
optimizingforblobandtextworkloads
optimizingforfilesorts
completingthebasicconfiguration
safetyandsanitysettings
advancedinnodbsettings
summary
9.operatingsystemandhardwareoptimization
whatlimitsmysql'sperformance?
howtoselectcpusformysql
whichisbetter:fastcpusormanycpus?
cpuarchitecture
scalingtomanycpusandcores
balancingmemoryanddiskresources
randomversussequentiali/o
caching,reads,andwrites
what'syourworkingset?
findinganeffectivememory-to-diskratio
choosingharddisks
solid-statestorage
anoverviewofflashmemory
flashtechnologies
benchmarkingflashstorage
solid-statedrives(ssds)
pciestoragedevices
othertypesofsolid-statestorage
whenshouldyouuseflash?
usingflashcache
optimizingmysqlforsolid-statestorage
choosinghardwareforareplica
raidperformanceoptimization
raidfailure,recovery,andmonitoring
balancinghardwareraidandsoftwareraid
raidconfigurationandcaching
storageareanetworksandnetwork-attachedstorage
sanbenchmarks
usingasanovernfsorsmb
mysqlperformanceonasan
shouldyouuseasan?
usingmultiplediskvolumes
networkconfiguration
choosinganoperatingsystem
choosingafilesystem
choosingadiskqueuescheduler
threading
swapping
operatingsystemstatus
howtoreadvmstatoutput
howtoreadiostatoutput
otherhelpfultools
acpu-boundmachine
ani/o-boundmachine
aswappingmachine
anidlemachine
summary
10.replication.
replicationoverview
problemssolvedbyreplication
howreplicationworks
settingupreplication
creatingreplicationaccounts
configuringthemasterandreplica
startingthereplica
initializingareplicafromanotherserver
recommendedreplicationconfiguration
replicationunderthehood
statement-basedreplication
row-basedreplication
statement-basedorrow-based:whichisbetter?
replicationfiles
sendingreplicationeventstootherreplicas
replicationfilters
replicationtopologies
masterandmultiplereplicas
master-masterinactive-activemode
master-masterinactive-passivemode
master-masterwithreplicas
ringreplication
master,distributionmaster,andreplicas
treeorpyramid
customreplicationsolutions
replicationandcapacityplanning
whyreplicationdoesn'thelpscalewrites
whenwillreplicasbegintolag?
plantounderutilize
replicationadministrationandmaintenance
monitoringreplication
measuringreplicationlag
determiningwhetherreplicasareconsistentwiththemaster
resyncingareplicafromthemaster
changingmasters
switchingrolesinamaster-masterconfiguration
replicationproblemsandsolutions
errorscausedbydatacorruptionorloss
usingnontransactionaltables
mixingtransactionalandnontransactionaltables
nondeterministicstatements
differentstorageenginesonthemasterandreplica
datachangesonthereplica
nonuniqueserverids
undefinedserverids
dependenciesonnonreplicateddata
missingtemporarytables
notreplicatingallupdates
lockcontentioncausedbyinnodblockingselects
writingtobothmastersinmaster-masterreplication
excessivereplicationlag
oversizedpacketsfromthemaster
limitedreplicationbandwidth
nodiskspace
replicationlimitations
howfastisreplication?
advancedfeaturesinmysqlreplication
otherreplicationtechnologies
summary
11.scalingmysql
whatisscalability?
aformaldefinition'
scalingmysql
planningforscalability
buyingtimebeforescaling
scalingup
scalingout
scalingbyconsolidation
scalingbyclustering
scalingback
loadbalancing
connectingdirectly
introducingamiddleman
loadbalancingwithamasterandmultiplereplicas
summary
12.highavailability
whatishighavailability?
whatcausesdowntime?
achievinghighavailability
improvingmeantimebetweenfailures
improvingmeantimetorecovery
avoidingsinglepointsoffailure
sharedstorageorreplicateddisk
synchronousmysqlreplication
replication-basedredundancy
failoverandfailback
promotingareplicaorswitchingroles
virtualipaddressesoriptakeover
middlemansolutions
handlingfailoverintheapplication
summary
13.mysqlinthecl0ud
benefits,drawbacks,andmythsofthecloud
theeconomicsofmysqlinthecloud
mysqlscalingandhainthecloud
thefourfundamentalresources
mysqlperformanceincloudhosting
benchmarksformysqlinthecloud
mysqldatabaseasaservice(dbaas)
amazonrds
otherdbaassolutions
summary
14.application-leveloptimization
commonproblems
webserverissues
findingtheoptimalconcurrency
caching
cachingbelowtheapplication
application-levelcaching
cachecontrolpolicies
cacheobjecthierarchies
pregeneratingcontent
thecacheasaninfrastructurecomponent
usinghandlersocketandmemcachedaccess
extendingmysql
alternativestomysql
summary
15.backupandrecovery
whybackups?
definingrecoveryrequirements
designingamysqlbackupsolution
onlineoroffiinebackups?
logicalorrawbackups?
whattobackup
storageenginesandconsistency
replication
managingandbackingupbinarylogs
thebinarylogformat
purgingoldbinarylogssafely
backingupdata
makingalogicalbackup
filesystemsnapshots
recoveringfromabackup
restoringrawfiles
restoringlogicalbackups
point-in-timerecovery
moreadvancedrecoverytechniques
innodbcrashrecovery
backupandrecoverytools
mysqlenterprisebackup
perconaxtrabackup
mylvmbackup
zmandarecoverymanager
mydumper
mysqldump
scriptingbackups
summary
16.toolsformysqlusers
interfacetools
command-lineutilities
sqlutilities
monitoringtools
opensourcemonitoringtools
commercialmonitoringsystems
command-linemonitoringwithinnotop
summary
a.forksandvariantsofmysql
b.mysqlserverstatus
c.transferringlargefiles
d.usingexplain
e.debugginglocks
f.usingsphinxwithmysql
index