MySQL优化实践优化实践 MySQL性能优化知识分享性能优化知识分享Agenda内容提要MySQL常见工具应用MySQL优化MySQL架构MySQL备份Summary用于员工培训和分享，主要针对有一定开发经验的工程师适用于高并发，海量数据的互联网环境以解决具体问题为主要目标，比如个人空间的DB优化针对优化任何一个方面都是个很深的话题，本次技术分享只能做到概要，如果大家有需求，有时间可以针对某方面的优化在做详细的技术交流Agenda内容提要MySQL常见工具应用MySQL优化MySQL架构MySQL备份ySQL常见工具应用常见：mysql，mysqladmin，mysqldump，myslqimportmysqlcheck，myisamchk,，mysqlpack备份：mysqlhotcopy，xtrabackup性能：mysqlreport，mysqlbinlog，mytop，innotopmsyqldumpslow/mysqlsla，mysqltuner压力测试：mysqlslap其他官方工具和第三方工具常见工具一mysql:1类似于sqlplus，mysqlhelp2“-e”参数,常用于MySQL检查和监控脚本中3“-H”“-X”“-prompt”“-tee”4与my.cnf的联系mysqladmin:常用于监控脚本（状态检查，统计信息的flush,创建和删除数据库，关闭MySQLServer）mysqladminutestptesthlocalhostpingmysqladminutestptesthlocalhoststatusmysqladminutestptesthlocalhostprocesslistmysqladminutestptesthlocalhostshutdownps：mysql与mysqladmin区别常见工具二mysqldump:1“-T”指定格式文本文件同select*intoOUTFILEfrom2“-d”参数只生成结构“-delayed-insert”延迟插入（队列）mysqlimport:1导入指定格式的文件，如csv文件2LOADDATAINFILEmysqlcheck:14项功能：check,repair,analyze,optimite2innodb不支持修复功能myisamchk：1类似mysqlcheckc/-r,只对myisam的索引文件有效2常用修复表的二种方法：mysqlcheck-utest-ptest-r-oDBmyisamchk-r*.MYI性能分析一mysqlreport:1perl语言编写的MySQL数据库监控脚本2友好的方式显示MySQL状态变量3优点：快速的查看各种状态参数组，无须手工计算4mysqlreportuserrootpassword1mysqlreporthost117.79.91.30useradminpassword15报告样单：http:/hackmysql.com/mysqlreportguide6详细解释:http:/www.uini.net/2010/05/mysqlreport-report-describes.html性能分析二mytop:1类似于系统的top,对mysql进行即时监控2mytop-utest-ptest-hremot_host3报告样单：http:/jeremy.zawodny.com/mysql/mytop/4详细解释:第一行主机名称，至今运行时间第二行的Queries-至今查询总数，另外还有目前每秒处理的查询数和速度。第三行的KeyEfficiency-缓存命中率，如果太低了你可能要调整你的MySQL设置，或者调整一下表的结构，后面还有目前的进出速度。性能分析三mysqlsla:1一款MySQL的日志分析工具2功能非常强大.数据报表,非常有利于分析慢查询的原因,包括执行频率,数据量,查询消耗等3mysqlsla-user=root-password=-ex-socket=/tmp/mysql.sock-ltslowslow.logmysqlsla-user=root-password=-ex-socket=/tmp/mysql.sock-ltgeneral/data/mysql/test.log54报告样单：http:/hackmysql.com/mysqlsla5详细解释:http:/hi.baidu.com/linux_life/blog/item/41435f318de02da05edf0e94.htmlAgenda内容提要MySQL常见工具应用MySQL优化MySQL架构MySQL备份MySQL优化的大方向服务器硬件，存储，网络环境（磁盘读/写速度，CPU主频周期，内存带宽，网络连接速度，网络带宽等）服务器系统（版本选择，内核选择，内核参数等）应用:(缓存系统，TCMalloc)my.cnf各项配置（log-bin，禁用dns查询，超时时间，文件系统外部锁，table_cache,Query_cache等）存储引擎，表，SQL查询，索引（根据应用选择合适存储引擎（MyisamORINODBOROther），设计好SQL和索引(explain，profiling)，应用各种工具分析系统性能瓶颈，有针对性的调节）基础架构（mysql复制，负载均衡，读写分离，mysql集群,大表的切割(水平切割和垂直切割)等）MySQL架构影响MySQL性能的因素商业需求对性能的影响1不合理的需求，如论坛帖子总量的统计（实时更新）2无用功能堆积，使系统过于复杂，影响整体性能系统架构对于性能的影响1不适合存在库的数据（二进制多媒体数据，流水队列数据超大文本数据）2是否利用了应用层cache机制（系统各种配置及规则数据，活跃用户的基本信息数据，个性化定制数据，准实时的统计信息数据，访问频繁但变更少的数据）3数据层实现是否精简？（优化的SQL）Schema设计对性能的影响硬件环境对性能的影响（IOPS，磁盘和内存，CPU，存储）硬件设备多CPU多Core?，内存永远不嫌大-优化数据库最廉价有效的方案使用RAID10多磁盘提IO能力或者用NAS，SANUPS，RAID要带电池(BBU(电池备份单元)硬盘-优化完参数后，提高性能最显著的方法全千兆网络环境系统调优Linux最常见，熟悉的人多，好维护sun的Solaris和服务器对MySQL有专门的优化全部采用64位版本选择稳定内核（权衡稳定，性能，功能）综合比较XFS文件系统是个不错的选择调整系统默认内核参数（例如TCP/IP堆栈连接数）应用优化数据库只负责数据，不管逻辑使用Google的TCMalloc库，提高并发的稳定性对软件尽量都采用静态编译优化，提高性能避免硬盘操作使用大量的缓存，降低对数据库的查询请求-增加缓存层(持久化，非持久化)架构上的调整（基于主从复制的扩展，Sharding，分区）key-valuedatabase-TokyoCabinet；Redis；MongDB；MemcacheDB.把复杂的判断和逻辑留给代码，而不是数据库Schema的优化高效的模型设计（需求为首，性能为目标）表字段适度冗余-尽量减少join大字段垂直分拆-summary优化大表水平拆分-基于类型的分拆考虑扩充的情况下用最省的类型-boolorint；char()orvarchar();ip类型-inet_aton和inet_ntoa函数命名规范表的设计。范式，反范式索引的优化MySQL只能在索引的最左边上搜索有效索引在存储引擎上实现，而不是服务器层一般针对数据分散的关键字进行建立索引尽量把索引建立在int,varchar类似的字段上在建立聚集索引的时候，要照顾到查询的sql不要建立过多的索引（可考虑聚集索引），否则更新索引时间长尽量不要使用唯一索引索引的优化(续)主键占用空间越小越好不要用随机值做主键。比如MD5根据主键查询速度最快联合索引-最左前缀不做不必要的索引-空间；时间；缓存只有几个值的字段不必索引MySQLServer优化MySQL安装优化源码静态编译，定制化MySQLMySQL日志设置优化binlog：binlog_cache_sizemax_binlog_cache_sizemax_binlog_sizesync_binlogslowlog：slow_query_loglong_query_timeslow_query_log_filelong_queries_not_using_indexsMy.cnf的优化showstatuslikeshowinnodbstatusshow(global)variableslikeskip-name-resolvethread_concurrencydefault-character-setQueryCache优化实现原理（query语句-hash桶(hash链表)，ResultSet-内存Cache）ps:任何表的任何一条数据发生变化，会通知Querycache负面影响query语句的hash运算以及hash查找消耗资源（每秒几千?）querycache的失效问题（表变更频繁）querycache缓存的是ResultSet，而不是数据页(多次cache)适度使用querycache（扬长避短）SQLHint:SQL_NO_CACHE和SQL_CACHE有些ResultSet很大，内存不足？query_cache_limitQueryCache优化(续)showvariableslike%query_cache%;have_query_cachequery_cache_limit(1MB)query_cache_min_res_unit(4KB)query_cache_sizequery_cache_typequery_cache_wlock_invalidateshowstatuslikeQcache%;flushstatus/flushquerycache网络连接优化max_connections整个MySQL允许的最大连接（500-800）max_user_connections每个用户允许的最大连接back_log在连接请求等待队列中允许存放的最大请求数ps:注意OS级别对网络监听队列的限制net_buffer_length传输消息前的netbuffer初始化大小max_allowed_packet一次消息传输量的最大值关于timeout(connect_timeout,interactive_timeout,wait_timeout,net_read_timeout,net_write_timeout)ps：connect_timeout在获取连接阶段（authenticate）起作用，interactive_timeout和wait_timeout在连接空闲阶段（sleep）起作用，而net_read_timeout和net_write_timeout则是在连接繁忙阶段（query）起作用。线程池优化thread_cache_size线程池中应该存放的连接线程数ps:短连接，不小于实际并发请求数长连接（50-100）thread_stack每个连接线程被创建时，给他分配的内存大小showvariableslikethread%;showstatuslikeconnections;showstatuslike%thread%;Threadcache命中率：hit=(connectionsThread_created)/connections*100%SortBuffer,JoinBuffersort_buffer_size对数据进行排序时的buffer（单个thread）ps：orderby/groupbyjoin_buffer_size(join-ALL,index,rang,index_merge;”Fulljoin”)showvariableslike%buffer%;showstatuslikesort%;ps：看sort_merge_passes值，若很大，调sort_buffer_sizeMyISAM优化默认的存储引擎，使用B+Tree进行索引支持静态，动态，压缩的数据格式，但不支持事务，外键支持文本和索引的压缩数据和索引文件单独存放适合多读写少的操作，几乎没有并发性要定期优化表，提供外部的扩展工具来修复数据文件MyISAM优化(续一)key_buffer_size索引缓存大小指标：1系统索引的总大小2系统可用物理内存3根据系统当年的KeyCache命中率key_buffer_cache_size索引缓存中的CacheBlockSizekey_cache_division_limitLRU链表中的HotArea与WarmArea的分界值，取值1100，默认值为100key_cache_age_threshold控制CacheBlock中HotArea何时被降至WarmArea(100-300,默认值300)多个KeyCache问题MyISAM优化(续二)showstatuslikekey%;Hit=(1-Key_reads/Key_read_requests)*100%KeyCache使用：Key_Buffer_UsageRatio=(Key_blocks_used/(key_blocks_used+key_blocks_unused)*100%该值一般为99%以上甚至100%，若过低，则key_buffer_size过大Key_Buffer_Read_HitRatio=(1-Key_reads/Key_read_requests)*100%尽可能的高，若值过低，则key_buffer_size过小Key_Buffer_Write_HitRatio=(1-Key_writes/Key_read_requests)*100%MyISAM优化(续三)表读取缓存化（SequentialScan-全表/RandomScan-索引）ps:myisam不缓存数据（.MYD）文件，读数据需调用文件系统的相关指令，因为需为此操作分配内存缓冲区read_buffer_size：以SequentialScan方式扫描数据的bufferread_rnd_buffer_size:以RandomScan方式扫描数据的bufferbulk_insert_buffer_sizemyisam_sort_buffer_sizemyisam_max_sort_file_sizemyisam_max_extra_sort_file_sizemyisam_repair_threadsmyisam-recoverInnoDB优化使用表空间，数据和索引存放在一起，数据有自动恢复能力内存自我管理，有独立的内存缓冲池支持事务，外键，行级锁，支持聚集索引适合大量的读写操作，有一定的并发性参数的调优对Innodb非常重要-默认参数性能很差Innodb不会对参数做任何自我优化通过配置参数可以达到跟MyISAM读速度不相上下innodb_buffer_pool_size最重要的参数缓存Innodb的索引和数据-根据你的数据量来分配，太大就浪费可以设置为物理内存的80%-要给操作系统和其他缓存留有足够的内存，不然会有内存竞争负载过重时，Innodb分配的值可能会多10%innodb_log_file_szie对写操作频繁的数据库很重要更大的日志文件=更少的数据刷新更大的日志文件=更长的恢复时间怎么选择合适自己应用的值？-innodb_log_buffer_size设置为每秒的数据量，innodb_log_file_size设置为半个小时的数据量（因为默认有两个日志文件）-怎么确定每秒数据量见这里innodb_flush_log_at_trx_commit看应用的要求1：默认值，最安全。每个事务提交时不仅会写到日志，也会刷新到磁盘。即使停电也不会丢失数据0：最不安全，效率最高。事务提交时不做任何操作，每秒钟刷新到日志和磁盘。数据库崩溃会丢失1秒的事务2：每个事务提交时写到日志，每秒刷新到磁盘一次。系统崩溃会丢失1秒的事务innodb_flush_methodInnodb刷新数据到磁盘的方法默认是fsync()-操作系统和数据库会缓存两份数据(doublebuffering)O_DIRECT-绕过操作系统的缓存-如果是大量随机写入操作，O_DIRECT会提高效率。但是顺序写入和读取效率都会降低。所以使用O_DIRECT需要根据需求测试。My.cnf配置在取舍之间追求一种平衡（稳定/性能）业务决定存储引擎，存储引擎决定具体的配置参数当然，你的硬件选择，操作系统等都决定你的参数选择SQL语句如果你一眼看不明白这个SQL语句是干嘛的，就重写吧explain每一个SQL语句，确认是否用到索引-select*fromtablewhere不要在innodb引擎的表上使用count(*)批量insert和update开启慢查询日志，把超过一秒的操作拉出来分析利用profiling来分析查询.Agenda内容提要MySQL常见工具应用MySQL优化MySQL架构MySQL备份MySQL架构Replication常见架构常规复制架构（Master-Slaves）特点：读用于读压力大（简单读写分离）MasterSlaveslaveslaveDualMaster复制架构DualMaster复制架构特点：特定场合下Master的切换方便（维护）配合第三方HA可以实现自动切换，减少异常停机时间MasterMaster级联复制架构级联复制架构特点：解决Master因为附属Slave太多而成为瓶颈问题缺点是延时教长。SlaveslaveMasterSlaveSlaveSlaveDualMaster与级联复制结合架构DualMaster与级联复制结合架构MasterMasterSlaveSlaveSlave分区5.1之后才出现的分区RangePartitioningListPartitioningHashPartitioningKeyPartitioningSubpartitioning其他高可用方案：MySQLClusterDRBD磁盘网络镜像方案性能问题收集？系统相关命令（sar,iostat,vmstat,mpstat,uptime）数据库相关（showglobalprocesslist,status,variables,information_schema）性能收集脚本（mysqlreport,innotop,mytop）专业监控工具（MySQLMonitor,MySQLadministrator,cacti,nagios）Agenda内容提要MySQL常见工具应用MySQL优化MySQL架构MySQL备份备份目的灾难恢复防止用户的误操作程序上的bug应用测试业务特殊的需求审计备份的准备工作数据丢失的允许程度数据库的大小数据增长的速度数据库恢复或还原的时间，技术难度业务处理的频繁程度哪些表中数据变化是频繁的，哪些表中的数据句是固定的什么时候大量用数据库，导致频繁的插入和更新操作现有的数据库备份资源有哪些？(存储，磁盘，磁带，光盘)有无可能为数据库备份投入新的设备备份的方式备份的方式备份逻辑备份物理备份冷备份热备份备份带来的影响备份需要花费的时间是否影响应用如何选择冷备份，热备份，温备份数据库的大小业务的需求提早做出备份计划怎么去做备份统计你的数据库服务器(实例,库)建立自己的数据库备份服务器熟悉备份数据库的存储引擎(充分掌握存储引擎的特性)使用MySQL的异步复制自己编写脚本第三方备份工具和存储技术备份工具mysqldumpmysqlhotcopyLVMsnapshots/ZFSsnapshotsInnoDBHotBackup/XtrabackupZRMforMySQL推荐网站:iteye.comcsdn.netinfoq.comdbanotes.nethiadmin.comorczhou.com推荐书籍：MySQL性能调优与架构设计构建高性能Web站点Linux服务器性能调整谢谢!联络：weibo.com/wgs0120