MySQL性能优化概述

Copyright2007MySQLABMySQL性能优化概述陈慧MySQL工程师Copyright2007MySQLAB内容•MySQL调优概述•如何定位性能瓶颈•存储引擎和调优•MySQLClusterCopyright2007MySQLABMySQL调优概述•硬件软件网络环境•数据表结构•索引•SQL语句•参数•存储引擎Copyright2007MySQLABMySQL调优概述Client2ClientNConnectionThreadPoolParserQuery101101Client1OptimizerStorageEnginesƒInnoDBƒMyISAMƒMERGEƒMEMORYƒFederatedƒARCHIVEƒNDBClusterQueryCacheMySQLServerCopyright2007MySQLAB如何定位性能瓶颈Copyright2007MySQLAB定位性能问题•MySQLMonitor&Advisor•Explain•ProfilingCopyright2007MySQLABMySQLMonitor&AdvisorCopyright2007MySQLABMySQLMonitor&AdvisorCopyright2007MySQLABQueryExecutionPlan(EXPLAIN)•EXPLAIN模拟优化器执行查询,返回执行计划•EXPLAINtellsyou:–Inwhichorderthetablesareread–Whattypesofreadoperationsthataremade–Whichindexescouldhavebeenused–Whichindexesareused–Howthetablesrefertoeachother–HowmanyrowstheoptimizerestimatestoretrievefromeachtableCopyright2007MySQLABQueryExecutionPlan(EXPLAIN)mysqlEXPLAINSELECTCountry.NameFROMCountryJOINCountryLanguage-ONCode=CountryCodeWHERELanguage='Russian'\G***************************1.row***************************id:1select_type:SIMPLEtable:Countrytype:ALLpossible_keys:PRIMARYkey:NULLkey_len:NULLref:NULLrows:239Extra:***************************2.row***************************id:1select_type:SIMPLEtable:CountryLanguagetype:eq_refpossible_keys:PRIMARYkey:PRIMARYkey_len:33ref:world_test.Country.Code,constrows:1Extra:Usingwhere;Usingindex2rowsinset(0.00sec)Copyright2007MySQLABQueryExecutionPlan(EXPLAIN)mysqlALTERTABLECountryLanguageADDINDEX(Language);QueryOK,984rowsaffected(0.24sec)Records:984Duplicates:0Warnings:0mysqlEXPLAINSELECTCountry.NameFROMCountryJOINCountryLanguage-ONCode=CountryCodeWHERELanguage='Russian'\G***************************1.row***************************id:1select_type:SIMPLEtable:CountryLanguagetype:refpossible_keys:PRIMARY,Languagekey:Languagekey_len:30ref:constrows:15Extra:Usingwhere***************************2.row***************************id:1select_type:SIMPLEtable:Countrytype:eq_refpossible_keys:PRIMARYkey:PRIMARYkey_len:3ref:world_test.CountryLanguage.CountryCoderows:1Extra:2rowsinset(0.00sec)Copyright2007MySQLABProfilingCopyright2007MySQLABProfilingCopyright2007MySQLABProfilingCopyright2007MySQLAB存储引擎和调优Copyright2007MySQLABMySQL的存储引擎Client2ClientNConnectionThreadPoolParserQuery101101Client1OptimizerStorageEnginesƒInnoDBƒMyISAMƒMERGEƒMEMORYƒFederatedƒARCHIVEƒNDBClusterQueryCacheMySQLServerCopyright2007MySQLAB可插存储引擎结构•MySQL支持许多存储引擎，它们处理不同类型的表•通过选择，创建或扩展存储引擎来更好的适应应用的特殊要求•对你来说最重要的是什么?-集中读-复制-OLTP（联机事务处理）-在线备份-事务处理-数据仓库-性能-不相关关键字-可伸缩性-占用空间小-并发级别-行级别锁-索引类型-嵌入式-存储利用-表级别锁-高可靠性-集群Copyright2007MySQLAB存储引擎特点•MyISAM–特点:非常有效率的存储,易于处理高速数据加载–适用于:高流量网站数据仓库•InnoDB–特点:提供ACID事务处理支持–适用于:在线事务处理应用•Archive–特点:自动数据压缩–适用于:历史数据仓库,数据存档,数据审计•NDB–特点:支持事务处理提供高可靠机制–适用于:高可靠不停顿业务,快速目录/关键字查找应用Copyright2007MySQLAB存储引擎的特点•锁的机制•索引•对事务的支持•参数•备份机制Copyright2007MySQLAB锁•InnoDB支持–行级锁•Finestleveloflockgranularity•Onlytherowthatisreadorupdatedislocked•Allowsotherconcurrenttransactionstoaccessotherrowsonthesamepage–表级锁•Onlyusedwhentherearechangestothetablestructureitself(asisthecasewithAltertable)•Ensurestheutmostintegrityofthedataitcontainswhendoingdirectchangestothetablestructureitself•Controlledbythe--InnoDB-table-locksserversettingCopyright2007MySQLAB索引InnoDB11.5InnoDBIndexesRowRowRowRowRowPKVPKVPKVPKVPKVPrimaryKeyValuesPrimaryKeyValuesNon-leafindexpagesLeafindexpagesClusteredindexPrimaryKeyIndexSecondaryindexNon-leafindexpagesLeafindexpagesClusteredindexPrimaryKeyIndexSecondaryindexCopyright2007MySQLAB参数[mysqld]#YoucanwriteyourotherMySQLserveroptionshere#...#Datafilesmustbeabletoholdyourdataandindexes.#Makesurethatyouhaveenoughfreediskspace.innodb_data_file_path=ibdata1:10M:autoextend##Setbufferpoolsizeto50-80%ofyourcomputer'smemoryinnodb_buffer_pool_size=1024Minnodb_additional_mem_pool_size=10M##Setthelogfilesizetoabout25%ofthebufferpoolsizeinnodb_log_file_size=250Minnodb_log_buffer_size=8M#innodb_flush_log_at_trx_commit=1Copyright2007MySQLABInnoDB调优窍门•尽量使用短的,整型主键•Load/Insert数据时尽量用主键的顺序•增加日志文件大小•避免大的事务回滚•避免大量插入•尽量使用前缀索引Copyright2007MySQLABMySQLClusterCopyright2007MySQLAB高可靠性解决方案:集群•成本–Usecommodityhardwaretoaccommodatethegrowthofusers,traffic,anddata•容错–Nosinglepointoffailure•高可靠性–Dataisreplicatedacrossnodesandalwaysavailable–Automaticfail-over•可扩展性–Distributeslargeworkloads–ReplicasforRead–PartitionsforWrite–Supports“ScaleOut”•高性能–Loadbalanced–Memorybasedstorageengine–Designedtohandlethousandsofrequestspersecond•简化管理–Clustermanagementutilities–CommoditycomponentsApplicationsMySQLServerNodesDataNodesManagementServer&ClientCopyright2007MySQLABMySQLCluster概述•并行数据库–Shared-nothingarchitecture•数据在节点上分散存储–InaRAID10fashion•冗余–Synchronousreplication–Automaticfailoverandrecovery•性能–Abilitytomixandmatchdiskandmemorytables–Differentaccessmethods(SQLornativeNDBAPI)Copyright2007MySQLAB高可靠性解决方案:MySQL集群Copyright2007MySQLAB2节点集群的数据分布Copyright2007MySQLAB高性能配置Copyright2007MySQLAB内容•MySQL调优概述•如何定位性能瓶颈•存储引擎和调优•MySQLClusterCopyright2007MySQLAB谢谢大家!欢迎访问我们的网站