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

MySQL優(yōu)化實踐

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

Agenda內容提要MySQL常見工具應用MySQL優(yōu)化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檢查和監(jiān)控腳本中3“-H”“-X”“--prompt”“--tee”…4與f的聯系mysqladmin:常用于監(jiān)控腳本（狀態(tài)檢查，統(tǒng)計信息的flush,創(chuàng)建和刪除數據庫，關閉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”參數—只生成結構“--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數據庫監(jiān)控腳本2友好的方式顯示

MySQL狀態(tài)變量3優(yōu)點：快速的查看各種狀態(tài)參數組，無須手工計算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--至今查詢總數，另外還有目前每秒處理的查詢數和速度。

第三行的KeyEfficiency--緩存命中率，如果太低了你可能要調整你的MySQL設置，或者調整一下表的結構，后面還有目前的進出速度。

性能分析三mysqlsla:1一款MySQL的日志分析工具2功能非常強大.數據報表,非常有利于分析慢查詢的原因,包括執(zhí)行頻率,數據量,查詢消耗等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優(yōu)化MySQL架構MySQL備份MySQL優(yōu)化的大方向

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

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

MySQL架構

影響MySQL性能的因素

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

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

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

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

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

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

Schema的優(yōu)化

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

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

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

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

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

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

索引的優(yōu)化

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

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

MySQL安裝優(yōu)化

源碼靜態(tài)編譯，定制化MySQLMySQL日志設置優(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)化

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

SQLHint:SQL_NO_CACHE和SQL_CACHE

有些ResultSet很大，內存不足？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網絡連接優(yōu)化

max_connections

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

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

在連接請求等待隊列中允許存放的最大請求數ps:注意OS級別對網絡監(jiān)聽隊列的限制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）起作用。

線程池優(yōu)化

thread_cache_size

線程池中應該存放的連接線程數ps:短連接，不小于實際并發(fā)請求數

長連接（50-100）thread_stack

每個連接線程被創(chuàng)建時，給他分配的內存大小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優(yōu)化

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

key_buffer_size

索引緩存大小指標：1系統(tǒng)索引的總大小2系統(tǒng)可用物理內存3根據系統(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不緩存數據（.MYD）文件，讀數據需調用文件系統(tǒng)的相關指令，因為需為此操作分配內存緩沖區(qū)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優(yōu)化

使用表空間，數據和索引存放在一起，數據有自動恢復能力內存自我管理，有獨立的內存緩沖池支持事務，外鍵，行級鎖，支持聚集索引適合大量的讀寫操作，有一定的并發(fā)性參數的調優(yōu)對Innodb非常重要---默認參數性能很差Innodb不會對參數做任何自我優(yōu)化通過配置參數可以達到跟MyISAM讀速度不相上下innodb_buffer_pool_size

最重要的參數緩存Innodb的索引和數據

---根據你的數據量來分配，太大就浪費可以設置為物理內存的80%

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

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

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

---怎么確定每秒數據量見這里innodb_flush_log_at_trx_commit

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

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

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

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

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

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

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

---select*fromtablewhere

…不要在innodb引擎的表上使用count(*)批量insert和update開啟慢查詢日志，把超過一秒的操作拉出來分析

利用profiling來分析查詢.

Agenda內容提要MySQL常見工具應用MySQL優(yōu)化MySQL架構MySQL備份MySQL架構

Replication常見架構常規(guī)復制架構（Master-Slaves）

特點：讀用于讀壓力大（簡單讀寫分離）MasterSlaveslaveslaveDualMaster復制架構

DualMaster復制架構

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

配合第三方HA可以實現自動切換，減少異常停機時間MasterMaster級聯復制架構

級聯復制架構

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

缺點是延時教長。SlaveslaveMasterSlaveSlaveSlaveDualMaster與級聯復制結合架構

DualMaster與級聯復制結合架構

MasterMasterSlaveSlaveSlave分區(qū)

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