MySQL性能优化与数据备份

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，mysql–help2“-e”参数,常用于MySQL检查和监控脚本中3“-H”“-X”“--prompt”“--tee”…4与f的联系mysqladmin:常用于监控脚本（状态检查，统计信息的flush,创建和删除数据库，关闭MySQLServer）mysqladmin–utest–ptest–hlocalhostpingmysqladmin–utest–ptest–hlocalhoststatusmysqladmin–utest–ptest–hlocalhostprocesslist

mysqladmin–utest–ptest–hlocalhostshutdownps：mysql与mysqladmin区别

常见工具二mysqldump:1“-T”—指定格式文本文件同select*intoOUTFILEfrom…2“-d”参数—只生成结构“--delayed-insert”延迟插入（队列）mysqlimport:1导入指定格式的文件，如csv文件2LOADDATAINFILE…mysqlcheck:14项功能：check,repair,analyze,optimite2innodb不支持修复功能myisamchk：1类似mysqlcheck–c/-r,只对myisam的索引文件有效2常用修复表的二种方法：mysqlcheck-utest-p‘test'-r-oDBmyisamchk-r*.MYI

性能分析一mysqlreport:1perl语言编写的MySQL数据库监控脚本2友好的方式显示

MySQL状态变量3优点：快速的查看各种状态参数组，无须手工计算4mysqlreport–userroot–password1mysqlreport–host0–useradmin–password15报告样单：/mysqlreportguide6详细解释:/2010/05/mysqlreport-report-describes.html

性能分析二mytop:1类似于系统的top,对mysql进行即时监控2mytop-utest-p‘test'-hremot_host3报告样单：

/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报告样单：

/mysqlsla5详细解释:

/linux_life/blog/item/41435f318de02da05edf0e94.html

Agenda内容提要MySQL常见工具应用MySQL优化MySQL架构MySQL备份MySQL优化的大方向

服务器硬件，存储，网络环境（磁盘读/写速度，CPU主频周期，内存带宽，网络连接速度，网络带宽等）服务器系统（版本选择，内核选择，内核参数等）应用:(缓存系统，TCMalloc)f各项配置（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_indexs

Mf的优化

showstatuslike‘…’showinnodbstatusshow(global)variableslike‘…’…skip-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_cache

query_cache_limit(1MB)

query_cache_min_res_unit(4KB)

query_cache_sizequery_cache_typequery_cache_wlock_invalidateshowstatuslike‘Qcache%’;

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

每个连接线程被创建时，给他分配的内存大小showvariableslike‘thread%’;showstatuslike‘connections’;showstatuslike‘%thread%’;Threadcache命中率：hit=(connections–Thread_created)/connections*100%SortBuffer,JoinBuffer

sort_buffer_size对数据进行排序时的buffer（单个thread）ps：orderby/groupbyjoin_buffer_size(join--ALL,index,rang,index_merge;”Fulljoin”)showvariableslike‘%buffer%’;showstatuslike‘sort%’;ps：看sort_merge_passes值，若很大，调sort_buffer_size

MyISAM优化

默认的存储引擎，使用B+Tree进行索引支持静态，动态，压缩的数据格式，但不支持事务，外键支持文本和索引的压缩数据和索引文件单独存放适合多读写少的操作，几乎没有并发性要定期优化表，提供外部的扩展工具来修复数据文件MyISAM优化(续一)

key_buffer_size

索引缓存大小指标：1系统索引的总大小2系统可用物理内存3根据系统当年的KeyCache命中率key_buffer_cache_size

索引缓存中的CacheBlockSizekey_cache_division_limitLRU链表中的HotArea与WarmArea的分界值，取值1~100，默认值为100key_cache_age_threshold

控制CacheBlock中HotArea何时被降至WarmArea(100-300,默认值300)多个KeyCache问题MyISAM优化(续二)

showstatuslike'key%';

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-recover

InnoDB优化

使用表空间，数据和索引存放在一起，数据有自动恢复能力内存自我管理，有独立的内存缓冲池支持事务，外键，行级锁，支持聚集索引适合大量的读写操作，有一定的并发性参数的调优对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_method

Innodb刷新数据到磁盘的方法默认是fsync()---操作系统和数据库会缓存两份数据(doublebuffering)O_DIRECT

---绕过操作系统的缓存---如果是大量随机写入操作，O_DIRECT会提高效率。但是顺序写入和读取效率都会降低。所以使用O_DIRECT需要根据需求测试。Mf配置

在取舍之间追求一种平衡（稳定/性能）业务决定存储引擎，存储引擎决定具体的配置参数当然，你的硬件选择，操作系统等都决定你的参数选择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之后才出现的分区RangePartitioningListPartitioningHashPartitioningKey