MySQL性能優(yōu)化與數(shù)據(jù)備份

MySQL優(yōu)化實(shí)踐

MySQL性能優(yōu)化知識(shí)分享Agenda內(nèi)容提要MySQL常見(jiàn)工具應(yīng)用MySQL優(yōu)化MySQL架構(gòu)MySQL備份Summary用于員工培訓(xùn)和分享，主要針對(duì)有一定開(kāi)發(fā)經(jīng)驗(yàn)的工程師適用于高并發(fā)，海量數(shù)據(jù)的互聯(lián)網(wǎng)環(huán)境以解決具體問(wèn)題為主要目標(biāo)，比如個(gè)人空間的DB優(yōu)化針對(duì)優(yōu)化任何一個(gè)方面都是個(gè)很深的話題，本次技術(shù)分享只能做到概要，如果大家有需求，有時(shí)間可以針對(duì)某方面的優(yōu)化在做詳細(xì)的技術(shù)交流

Agenda內(nèi)容提要MySQL常見(jiàn)工具應(yīng)用MySQL優(yōu)化MySQL架構(gòu)MySQL備份ＭySQL常見(jiàn)工具應(yīng)用常見(jiàn)：mysql，mysqladmin，mysqldump，myslqimportmysqlcheck，myisamchk,，mysqlpack

備份：mysqlhotcopy，xtrabackup性能：mysqlreport，mysqlbinlog，mytop，innotopmsyqldumpslow/mysqlsla，mysqltuner壓力測(cè)試：mysqlslap

其他官方工具和第三方工具常見(jiàn)工具一mysql:1類似于sqlplus，mysql–help2“-e”參數(shù),常用于MySQL檢查和監(jiān)控腳本中3“-H”“-X”“--prompt”“--tee”…4與f的聯(lián)系mysqladmin:常用于監(jiān)控腳本（狀態(tài)檢查，統(tǒng)計(jì)信息的flush,創(chuàng)建和刪除數(shù)據(jù)庫(kù)，關(guān)閉MySQLServer）mysqladmin–utest–ptest–hlocalhostpingmysqladmin–utest–ptest–hlocalhoststatusmysqladmin–utest–ptest–hlocalhostprocesslist

mysqladmin–utest–ptest–hlocalhostshutdownps：mysql與mysqladmin區(qū)別

常見(jiàn)工具二mysqldump:1“-T”—指定格式文本文件同select*intoOUTFILEfrom…2“-d”參數(shù)—只生成結(jié)構(gòu)“--delayed-insert”延遲插入（隊(duì)列）mysqlimport:1導(dǎo)入指定格式的文件，如csv文件2LOADDATAINFILE…mysqlcheck:14項(xiàng)功能：check,repair,analyze,optimite2innodb不支持修復(fù)功能myisamchk：1類似mysqlcheck–c/-r,只對(duì)myisam的索引文件有效2常用修復(fù)表的二種方法：mysqlcheck-utest-p‘test'-r-oDBmyisamchk-r*.MYI

性能分析一mysqlreport:1perl語(yǔ)言編寫(xiě)的MySQL數(shù)據(jù)庫(kù)監(jiān)控腳本2友好的方式顯示

MySQL狀態(tài)變量3優(yōu)點(diǎn)：快速的查看各種狀態(tài)參數(shù)組，無(wú)須手工計(jì)算4mysqlreport–userroot–password1mysqlreport–host0–useradmin–password15報(bào)告樣單：/mysqlreportguide6詳細(xì)解釋:/2010/05/mysqlreport-report-describes.html

性能分析二mytop:1類似于系統(tǒng)的top,對(duì)mysql進(jìn)行即時(shí)監(jiān)控2mytop-utest-p‘test'-hremot_host3報(bào)告樣單：

/mysql/mytop/4詳細(xì)解釋:

第一行主機(jī)名稱，至今運(yùn)行時(shí)間

第二行的Queries--至今查詢總數(shù)，另外還有目前每秒處理的查詢數(shù)和速度。

第三行的KeyEfficiency--緩存命中率，如果太低了你可能要調(diào)整你的MySQL設(shè)置，或者調(diào)整一下表的結(jié)構(gòu)，后面還有目前的進(jìn)出速度。

性能分析三mysqlsla:1一款MySQL的日志分析工具2功能非常強(qiáng)大.數(shù)據(jù)報(bào)表,非常有利于分析慢查詢的原因,包括執(zhí)行頻率,數(shù)據(jù)量,查詢消耗等3mysqlsla--user=root--password=-ex--socket=/tmp/mysql.sock-ltslowslow.logmysqlsla--user=root--password=-ex--socket=/tmp/mysql.sock-ltgeneral/data/mysql/test.log54報(bào)告樣單：

/mysqlsla5詳細(xì)解釋:

/linux_life/blog/item/41435f318de02da05edf0e94.html

Agenda內(nèi)容提要MySQL常見(jiàn)工具應(yīng)用MySQL優(yōu)化MySQL架構(gòu)MySQL備份MySQL優(yōu)化的大方向

服務(wù)器硬件，存儲(chǔ)，網(wǎng)絡(luò)環(huán)境（磁盤(pán)讀/寫(xiě)速度，CPU主頻周期，內(nèi)存帶寬，網(wǎng)絡(luò)連接速度，網(wǎng)絡(luò)帶寬等）服務(wù)器系統(tǒng)（版本選擇，內(nèi)核選擇，內(nèi)核參數(shù)等）應(yīng)用:(緩存系統(tǒng)，TCMalloc)f各項(xiàng)配置（log-bin，禁用dns查詢，超時(shí)時(shí)間，文件系統(tǒng)外部鎖，table_cache,Query_cache等）

存儲(chǔ)引擎，表，SQL查詢，索引（根據(jù)應(yīng)用選擇合適存儲(chǔ)引擎（MyisamORINODBOROther），設(shè)計(jì)好SQL和索引(explain，profiling)，應(yīng)用各種工具分析系統(tǒng)性能瓶頸，有針對(duì)性的調(diào)節(jié)）基礎(chǔ)架構(gòu)（mysql復(fù)制，負(fù)載均衡，讀寫(xiě)分離，mysql集群,大表的切割(水平切割和垂直切割)等）

MySQL架構(gòu)

影響MySQL性能的因素

商業(yè)需求對(duì)性能的影響1不合理的需求，如論壇帖子總量的統(tǒng)計(jì)（實(shí)時(shí)更新）2無(wú)用功能堆積，使系統(tǒng)過(guò)于復(fù)雜，影響整體性能系統(tǒng)架構(gòu)對(duì)于性能的影響1不適合存在庫(kù)的數(shù)據(jù)（二進(jìn)制多媒體數(shù)據(jù)，流水隊(duì)列數(shù)據(jù)超大文本數(shù)據(jù)）2是否利用了應(yīng)用層cache機(jī)制（系統(tǒng)各種配置及規(guī)則數(shù)據(jù)，活躍用戶的基本信息數(shù)據(jù)，個(gè)性化定制數(shù)據(jù)，準(zhǔn)實(shí)時(shí)的統(tǒng)計(jì)信息數(shù)據(jù)，訪問(wèn)頻繁但變更少的數(shù)據(jù)）3數(shù)據(jù)層實(shí)現(xiàn)是否精簡(jiǎn)？（優(yōu)化的SQL）Schema設(shè)計(jì)對(duì)性能的影響硬件環(huán)境對(duì)性能的影響（IOPS，磁盤(pán)和內(nèi)存，CPU，存儲(chǔ)）硬件設(shè)備

多CPU多Core?，內(nèi)存永遠(yuǎn)不嫌大---優(yōu)化數(shù)據(jù)庫(kù)最廉價(jià)有效的方案使用RAID10多磁盤(pán)提IO能力或者用NAS，SANUPS，RAID要帶電池(BBU(電池備份單元))硬盤(pán)

---優(yōu)化完參數(shù)后，提高性能最顯著的方法全千兆網(wǎng)絡(luò)環(huán)境系統(tǒng)調(diào)優(yōu)

Linux最常見(jiàn)，熟悉的人多，好維護(hù)sun的Solaris和服務(wù)器對(duì)MySQL有專門(mén)的優(yōu)化全部采用64位版本選擇穩(wěn)定內(nèi)核（權(quán)衡穩(wěn)定，性能，功能）綜合比較XFS文件系統(tǒng)是個(gè)不錯(cuò)的選擇調(diào)整系統(tǒng)默認(rèn)內(nèi)核參數(shù)（例如TCP/IP堆棧連接數(shù)）應(yīng)用優(yōu)化

數(shù)據(jù)庫(kù)只負(fù)責(zé)數(shù)據(jù)，不管邏輯使用Google的TCMalloc庫(kù)，提高并發(fā)的穩(wěn)定性對(duì)軟件盡量都采用靜態(tài)編譯優(yōu)化，提高性能避免硬盤(pán)操作使用大量的緩存，降低對(duì)數(shù)據(jù)庫(kù)的查詢請(qǐng)求---增加緩存層(持久化，非持久化)架構(gòu)上的調(diào)整（基于主從復(fù)制的擴(kuò)展，Sharding，分區(qū)）key-valuedatabase

---TokyoCabinet；Redis；MongDB；MemcacheDB...把復(fù)雜的判斷和邏輯留給代碼，而不是數(shù)據(jù)庫(kù)

Schema的優(yōu)化

高效的模型設(shè)計(jì)（需求為首，性能為目標(biāo)）

表字段適度冗余----盡量減少join

大字段垂直分拆----summary優(yōu)化

大表水平拆分----基于類型的分拆考慮擴(kuò)充的情況下用最省的類型

---boolorint；char()orvarchar();ip類型

---inet_aton和inet_ntoa函數(shù)命名規(guī)范表的設(shè)計(jì)。范式，反范式

索引的優(yōu)化

MySQL只能在索引的最左邊上搜索有效索引在存儲(chǔ)引擎上實(shí)現(xiàn)，而不是服務(wù)器層一般針對(duì)數(shù)據(jù)分散的關(guān)鍵字進(jìn)行建立索引盡量把索引建立在int,varchar類似的字段上在建立聚集索引的時(shí)候，要照顧到查詢的sql不要建立過(guò)多的索引（可考慮聚集索引），否則更新索引時(shí)間長(zhǎng)盡量不要使用唯一索引索引的優(yōu)化(續(xù))

主鍵占用空間越小越好不要用隨機(jī)值做主鍵。比如MD5根據(jù)主鍵查詢速度最快聯(lián)合索引---最左前綴不做不必要的索引---空間；時(shí)間；緩存只有幾個(gè)值的字段不必索引MySQLServer優(yōu)化

MySQL安裝優(yōu)化

源碼靜態(tài)編譯，定制化MySQLMySQL日志設(shè)置優(yōu)化binlog：binlog_cache_sizemax_binlog_cache_sizemax_binlog_sizesync_binlogslowlog：slow_query_loglong_query_timeslow_query_log_filelong_queries_not_using_indexs

Mf的優(yōu)化

showstatuslike‘…’showinnodbstatusshow(global)variableslike‘…’…skip-name-resolvethread_concurrencydefault-character-setQueryCache優(yōu)化

實(shí)現(xiàn)原理（query語(yǔ)句---hash桶(hash鏈表)，ResultSet---內(nèi)存Cache）ps:任何表的任何一條數(shù)據(jù)發(fā)生變化，會(huì)通知Querycache負(fù)面影響query語(yǔ)句的hash運(yùn)算以及hash查找消耗資源（每秒幾千?）querycache的失效問(wèn)題（表變更頻繁）querycache緩存的是ResultSet，而不是數(shù)據(jù)頁(yè)(多次cache)適度使用querycache（揚(yáng)長(zhǎng)避短）

SQLHint:SQL_NO_CACHE和SQL_CACHE

有些ResultSet很大，內(nèi)存不足？query_cache_limitQueryCache優(yōu)化(續(xù))

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網(wǎng)絡(luò)連接優(yōu)化

max_connections

整個(gè)MySQL允許的最大連接（500-800）max_user_connections

每個(gè)用戶允許的最大連接back_log

在連接請(qǐng)求等待隊(duì)列中允許存放的最大請(qǐng)求數(shù)ps:注意OS級(jí)別對(duì)網(wǎng)絡(luò)監(jiān)聽(tīng)隊(duì)列的限制net_buffer_length

傳輸消息前的netbuffer初始化大小max_allowed_packet一次消息傳輸量的最大值關(guān)于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）起作用。

線程池優(yōu)化

thread_cache_size

線程池中應(yīng)該存放的連接線程數(shù)ps:短連接，不小于實(shí)際并發(fā)請(qǐng)求數(shù)

長(zhǎng)連接（50-100）thread_stack

每個(gè)連接線程被創(chuàng)建時(shí)，給他分配的內(nèi)存大小showvariableslike‘thread%’;showstatuslike‘connections’;showstatuslike‘%thread%’;Threadcache命中率：hit=(connections–Thread_created)/connections*100%SortBuffer,JoinBuffer

sort_buffer_size對(duì)數(shù)據(jù)進(jìn)行排序時(shí)的buffer（單個(gè)thread）ps：orderby/groupbyjoin_buffer_size(join--ALL,index,rang,index_merge;”Fulljoin”)showvariableslike‘%buffer%’;showstatuslike‘sort%’;ps：看sort_merge_passes值，若很大，調(diào)sort_buffer_size

MyISAM優(yōu)化

默認(rèn)的存儲(chǔ)引擎，使用B+Tree進(jìn)行索引支持靜態(tài)，動(dòng)態(tài)，壓縮的數(shù)據(jù)格式，但不支持事務(wù)，外鍵支持文本和索引的壓縮數(shù)據(jù)和索引文件單獨(dú)存放適合多讀寫(xiě)少的操作，幾乎沒(méi)有并發(fā)性要定期優(yōu)化表，提供外部的擴(kuò)展工具來(lái)修復(fù)數(shù)據(jù)文件MyISAM優(yōu)化(續(xù)一)

key_buffer_size

索引緩存大小指標(biāo)：1系統(tǒng)索引的總大小2系統(tǒng)可用物理內(nèi)存3根據(jù)系統(tǒng)當(dāng)年的KeyCache命中率key_buffer_cache_size

索引緩存中的CacheBlockSizekey_cache_division_limitLRU鏈表中的HotArea與WarmArea的分界值，取值1~100，默認(rèn)值為100key_cache_age_threshold

控制CacheBlock中HotArea何時(shí)被降至WarmArea(100-300,默認(rèn)值300)多個(gè)KeyCache問(wèn)題MyISAM優(yōu)化(續(xù)二)

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%，若過(guò)低，則key_buffer_size過(guò)大Key_Buffer_Read_HitRatio=(1-Key_reads/Key_read_requests)*100%

盡可能的高，若值過(guò)低，則key_buffer_size過(guò)小Key_Buffer_Write_HitRatio=(1-Key_writes/Key_read_requests)*100%

MyISAM優(yōu)化(續(xù)三)

表讀取緩存化（SequentialScan-全表/RandomScan-索引）ps:myisam不緩存數(shù)據(jù)（.MYD）文件，讀數(shù)據(jù)需調(diào)用文件系統(tǒng)的相關(guān)指令，因?yàn)樾铻榇瞬僮鞣峙鋬?nèi)存緩沖區(qū)read_buffer_size：以SequentialScan方式掃描數(shù)據(jù)的bufferread_rnd_buffer_size:以RandomScan方式掃描數(shù)據(jù)的bufferbulk_insert_buffer_sizemyisam_sort_buffer_sizemyisam_max_sort_file_sizemyisam_max_extra_sort_file_sizemyisam_repair_threadsmyisam-recover

InnoDB優(yōu)化

使用表空間，數(shù)據(jù)和索引存放在一起，數(shù)據(jù)有自動(dòng)恢復(fù)能力內(nèi)存自我管理，有獨(dú)立的內(nèi)存緩沖池支持事務(wù)，外鍵，行級(jí)鎖，支持聚集索引適合大量的讀寫(xiě)操作，有一定的并發(fā)性參數(shù)的調(diào)優(yōu)對(duì)Innodb非常重要---默認(rèn)參數(shù)性能很差I(lǐng)nnodb不會(huì)對(duì)參數(shù)做任何自我優(yōu)化通過(guò)配置參數(shù)可以達(dá)到跟MyISAM讀速度不相上下innodb_buffer_pool_size

最重要的參數(shù)緩存Innodb的索引和數(shù)據(jù)

---根據(jù)你的數(shù)據(jù)量來(lái)分配，太大就浪費(fèi)可以設(shè)置為物理內(nèi)存的80%

---要給操作系統(tǒng)和其他緩存留有足夠的內(nèi)存，不然會(huì)有內(nèi)存競(jìng)爭(zhēng)負(fù)載過(guò)重時(shí)，Innodb分配的值可能會(huì)多10%innodb_log_file_szie

對(duì)寫(xiě)操作頻繁的數(shù)據(jù)庫(kù)很重要更大的日志文件=更少的數(shù)據(jù)刷新更大的日志文件=更長(zhǎng)的恢復(fù)時(shí)間怎么選擇合適自己應(yīng)用的值？

---innodb_log_buffer_size設(shè)置為每秒的數(shù)據(jù)量，innodb_log_file_size設(shè)置為半個(gè)小時(shí)的數(shù)據(jù)量（因?yàn)槟J(rèn)有兩個(gè)日志文件）

---怎么確定每秒數(shù)據(jù)量見(jiàn)這里innodb_flush_log_at_trx_commit

看應(yīng)用的要求1：默認(rèn)值，最安全。每個(gè)事務(wù)提交時(shí)不僅會(huì)寫(xiě)到日志，也會(huì)刷新到磁盤(pán)。即使停電也不會(huì)丟失數(shù)據(jù)

0：最不安全，效率最高。事務(wù)提交時(shí)不做任何操作，每秒鐘刷新到日志和磁盤(pán)。數(shù)據(jù)庫(kù)崩潰會(huì)丟失1秒的事務(wù)

2：每個(gè)事務(wù)提交時(shí)寫(xiě)到日志，每秒刷新到磁盤(pán)一次。系統(tǒng)崩潰會(huì)丟失1秒的事務(wù)innodb_flush_method

Innodb刷新數(shù)據(jù)到磁盤(pán)的方法默認(rèn)是fsync()---操作系統(tǒng)和數(shù)據(jù)庫(kù)會(huì)緩存兩份數(shù)據(jù)(doublebuffering)O_DIRECT

---繞過(guò)操作系統(tǒng)的緩存---如果是大量隨機(jī)寫(xiě)入操作，O_DIRECT會(huì)提高效率。但是順序?qū)懭牒妥x取效率都會(huì)降低。所以使用O_DIRECT需要根據(jù)需求測(cè)試。Mf配置

在取舍之間追求一種平衡（穩(wěn)定/性能）業(yè)務(wù)決定存儲(chǔ)引擎，存儲(chǔ)引擎決定具體的配置參數(shù)當(dāng)然，你的硬件選擇，操作系統(tǒng)等都決定你的參數(shù)選擇SQL語(yǔ)句

如果你一眼看不明白這個(gè)SQL語(yǔ)句是干嘛的，就重寫(xiě)吧explain每一個(gè)SQL語(yǔ)句，確認(rèn)是否用到索引

---select*fromtablewhere

…不要在innodb引擎的表上使用count(*)批量insert和update開(kāi)啟慢查詢?nèi)罩荆殉^(guò)一秒的操作拉出來(lái)分析

利用profiling來(lái)分析查詢.

Agenda內(nèi)容提要MySQL常見(jiàn)工具應(yīng)用MySQL優(yōu)化MySQL架構(gòu)MySQL備份MySQL架構(gòu)

Replication常見(jiàn)架構(gòu)常規(guī)復(fù)制架構(gòu)（Master-Slaves）

特點(diǎn)：讀用于讀壓力大（簡(jiǎn)單讀寫(xiě)分離）MasterSlaveslaveslaveDualMaster復(fù)制架構(gòu)

DualMaster復(fù)制架構(gòu)

特點(diǎn)：特定場(chǎng)合下Master的切換方便（維護(hù)）

配合第三方HA可以實(shí)現(xiàn)自動(dòng)切換，減少異常停機(jī)時(shí)間MasterMaster級(jí)聯(lián)復(fù)制架構(gòu)

級(jí)聯(lián)復(fù)制架構(gòu)

特點(diǎn)：解決Master因?yàn)楦綄賁lave太多而成為瓶頸問(wèn)題

缺點(diǎn)是延時(shí)教長(zhǎng)。SlaveslaveMasterSlaveSlaveSlaveDualMaster與級(jí)聯(lián)復(fù)制結(jié)合架構(gòu)

DualMaster與級(jí)聯(lián)復(fù)制結(jié)合架構(gòu)

MasterMasterSlaveSlaveSlave分區(qū)

5.1之后才出現(xiàn)的分區(qū)RangePartitioningListPartitioningHashPartitioningKey