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

MySQL優(yōu)化實踐

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

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

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

其他官方工具和第三方工具常見工具一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)計信息的flush,創(chuàng)建和刪除數(shù)據(jù)庫，關(guān)閉MySQLServer）mysqladmin–utest–ptest–hlocalhostpingmysqladmin–utest–ptest–hlocalhoststatusmysqladmin–utest–ptest–hlocalhostprocesslist

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

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

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

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

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

/mysql/mytop/4詳細解釋:

第一行主機名稱，至今運行時間

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

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

性能分析三mysqlsla:1一款MySQL的日志分析工具2功能非常強大.數(shù)據(jù)報表,非常有利于分析慢查詢的原因,包括執(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報告樣單：

/mysqlsla5詳細解釋:

/linux_life/blog/item/41435f318de02da05edf0e94.html

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

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

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

MySQL架構(gòu)

影響MySQL性能的因素

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

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

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

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

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

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

Schema的優(yōu)化

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

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

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

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

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

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

索引的優(yōu)化

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

主鍵占用空間越小越好不要用隨機值做主鍵。比如MD5根據(jù)主鍵查詢速度最快聯(lián)合索引---最左前綴不做不必要的索引---空間；時間；緩存只有幾個值的字段不必索引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)化

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

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

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

每個用戶允許的最大連接back_log

在連接請求等待隊列中允許存放的最大請求數(shù)ps:注意OS級別對網(wǎng)絡(luò)監(jiān)聽隊列的限制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:短連接，不小于實際并發(fā)請求數(shù)

長連接（50-100）thread_stack

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

sort_buffer_size對數(shù)據(jù)進行排序時的buffer（單個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)化

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

key_buffer_size

索引緩存大小指標：1系統(tǒng)索引的總大小2系統(tǒng)可用物理內(nèi)存3根據(jù)系統(tǒng)當年的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優(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%，若過低，則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優(yōu)化(續(xù)三)

表讀取緩存化（SequentialScan-全表/RandomScan-索引）ps:myisam不緩存數(shù)據(jù)（.MYD）文件，讀數(shù)據(jù)需調(diào)用文件系統(tǒng)的相關(guā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ù)有自動恢復(fù)能力內(nèi)存自我管理，有獨立的內(nèi)存緩沖池支持事務(wù)，外鍵，行級鎖，支持聚集索引適合大量的讀寫操作，有一定的并發(fā)性參數(shù)的調(diào)優(yōu)對Innodb非常重要---默認參數(shù)性能很差I(lǐng)nnodb不會對參數(shù)做任何自我優(yōu)化通過配置參數(shù)可以達到跟MyISAM讀速度不相上下innodb_buffer_pool_size

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

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

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

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

---innodb_log_buffer_size設(shè)置為每秒的數(shù)據(jù)量，innodb_log_file_size設(shè)置為半個小時的數(shù)據(jù)量（因為默認有兩個日志文件）

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

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

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

2：每個事務(wù)提交時寫到日志，每秒刷新到磁盤一次。系統(tǒng)崩潰會丟失1秒的事務(wù)innodb_flush_method

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

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

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

如果你一眼看不明白這個SQL語句是干嘛的，就重寫吧explain每一個SQL語句，確認是否用到索引

---select*fromtablewhere

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

利用profiling來分析查詢.

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

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

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

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

特點：特定場合下Master的切換方便（維護）

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

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

特點：解決Master因為附屬Slave太多而成為瓶頸問題

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

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

MasterMasterSlaveSlaveSlave分區(qū)

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