運維角度淺談MySQL數據庫優化(李振良)。本站提示廣大學習愛好者：（運維角度淺談MySQL數據庫優化(李振良)）文章只能為提供參考，不一定能成為您想要的結果。以下是運維角度淺談MySQL數據庫優化(李振良)正文
一個成熟的數據庫架構其實不是一開端設計就具有高可用、高伸縮等特征的，它是跟著用戶量的增長，基本架構才逐步完美。這篇博文重要談MySQL數據庫成長周期中所面對的成績及優化計劃，暫且拋開前端運用不說，年夜致分為以下五個階段：
1、數據庫表設計
項目立項後，開辟部依據產物部需求開辟項目，開辟工程師任務個中一部門就是對表構造設計。關於數據庫來講，這點很主要，假如設計欠妥，會直接影響拜訪速度和用戶體驗。影響的身分許多，好比慢查詢、低效的查詢語句、沒有恰當樹立索引、數據庫梗塞（逝世鎖）等。固然，有測試工程師的團隊，會做壓力測試，找bug。關於沒有測試工程師的團隊來講，年夜多半開辟工程師早期不會太多斟酌數據庫設計能否公道，而是盡快完勝利能完成和交付，等項目有必定拜訪量後，隱蔽的成績就會裸露，這時候再去修正就不是這麼輕易的事了。
2、數據庫安排
該運維工程師進場了，項目早期拜訪量不會很年夜，所以單台安排足以應對在1500閣下的QPS（每秒查詢率）。斟酌到高可用性，可采取MySQL主從復制+Keepalived做雙擊熱備，罕見集群軟件有Keepalived、Heartbeat。
雙機熱備博文：http://lizhenliang.blog.51cto.com/7876557/1362313
3、數據庫機能優化
假如將MySQL安排到通俗的X86辦事器上，在不經由任何優化情形下，MySQL實際值正常可以處置2000閣下QPS，經由優化後，有能夠會晉升到2500閣下QPS，不然，拜訪量當到達1500閣下並發銜接時，數據庫處置機能就會變慢，並且硬件資本還很充裕，這時候就該斟酌軟件成績了。那末如何讓數據庫最年夜化施展機能呢？一方面可以單台運轉多個MySQL實例讓辦事器機能施展到最年夜化，另外一方面是對數據庫停止優化，常常操作體系和數據庫默許設置裝備擺設都比擬守舊，會對數據庫施展有必定限制，可對這些設置裝備擺設停止恰當的調劑，盡量的處置更多銜接數。
詳細優化有以下三個層面：
3.1 數據庫設置裝備擺設優化
MySQL經常使用有兩種存儲引擎，一個是MyISAM，不支撐事務處置，讀機能處置快，表級別鎖。另外一個是InnoDB，支撐事務處置（ACID），設計目的是為處置年夜容量數據施展最年夜化機能，行級別鎖。
表鎖：開支小，鎖定粒度年夜，產生逝世鎖幾率高，絕對並發也低。
行鎖：開支年夜，鎖定粒度小，產生逝世鎖幾率低，絕對並發也高。
為何會湧現表鎖和行鎖呢？重要是為了包管數據的完全性，舉個例子，一個用戶在操作一張表，其他用戶也想操作這張表，那末就要等第一個用戶操作完，其他用戶能力操作，表鎖和行鎖就是這個感化。不然多個用戶同時操作一張表，確定會數據發生抵觸或許異常。
依據以上看來，應用InnoDB存儲引擎是最好的選擇，也是MySQL5.5今後版本中默許存儲引擎。每一個存儲引擎相干聯參數比擬多，以以下出重要影響數據庫機能的參數。
公共參數默許值：
max_connections = 151 #同時處置最年夜銜接數，推舉設置最年夜銜接數是下限銜接數的80%閣下 sort_buffer_size = 2M #查詢排序時緩沖區年夜小，只對order by和group by起感化，可增年夜此值為16M query_cache_limit = 1M #查詢緩存限制，只要1M以下查詢成果才會被緩存，以避免成果數據較年夜把緩存池籠罩 query_cache_size = 16M #檢查緩沖區年夜小，用於緩存SELECT查詢成果，下一次有異樣SELECT查詢將直接從緩存池前往成果，可恰當成倍增長此值 open_files_limit = 1024 #翻開文件數限制，假如show global status like 'open_files'檢查的值等於或許年夜於open_files_limit值時，法式會沒法銜接數據庫或卡逝世
MyISAM參數默許值：
key_buffer_size = 16M #索引緩存區年夜小，普通設置物理內存的30-40% read_buffer_size = 128K #讀操作緩沖區年夜小，推舉設置16M或32M
InnoDB參數默許值：
innodb_buffer_pool_size = 128M #索引和數據緩沖區年夜小，普通設置物理內存的60%-70% innodb_buffer_pool_instances = 1 #緩沖池實例個數，推舉設置4個或8個 innodb_flush_log_at_trx_commit = 1 #症結參數，0代表年夜約每秒寫入到日記並同步到磁盤，數據庫毛病會喪失1秒閣下事務數據。1為每履行一條SQL後寫入到日記並同步到磁盤，I/O開支年夜，履行完SQL要期待日記讀寫，效力低。2代表只把日記寫入到體系緩存區，再每秒同步到磁盤，效力很高，假如辦事器毛病，才會喪失事務數據。對數據平安性請求不是很高的推舉設置2，機能高，修正後後果顯著。 innodb_file_per_table = OFF #默許是同享表空間，同享表空間idbdata文件赓續增年夜，影響必定的I/O機能。推舉開啟自力表空間形式，每一個表的索引和數據都存在本身自力的表空間中，可以完成單表在分歧數據庫中挪動。 innodb_log_buffer_size = 8M #日記緩沖區年夜小，因為日記最長每秒鐘刷新一次，所以普通不消跨越16M
3.2 體系內核優化
年夜多半MySQL都安排在linux體系上，所以操作體系的一些參數也會影響到MySQL機能，以下對linux內核停止恰當優化。
net.ipv4.tcp_fin_timeout = 30 #TIME_WAIT超不時間，默許是60s net.ipv4.tcp_tw_reuse = 1 #1表現開啟復用，許可TIME_WAIT socket從新用於新的TCP銜接，0表現封閉 net.ipv4.tcp_tw_recycle = 1 #1表現開啟TIME_WAIT socket疾速收受接管，0表現封閉 net.ipv4.tcp_max_tw_buckets = 4096 #體系堅持TIME_WAIT socket最年夜數目，假如超越這個數，體系將隨機消除一些TIME_WAIT並打印正告信息 net.ipv4.tcp_max_syn_backlog = 4096 #進入SYN隊列最年夜長度，加年夜隊列長度可包容更多的期待銜接
在linux體系中，假如過程翻開的文件句柄數目跨越體系默許值1024，就會提醒“too many files open”信息，所以要調劑翻開文件句柄限制。
# vi /etc/security/limits.conf #參加以下設置裝備擺設，*代表一切用戶，也能夠指定用戶，重啟體系失效 * soft nofile 65535 * hard nofile 65535 # ulimit -SHn 65535 #連忙失效
3.3 硬件設置裝備擺設
加年夜物理內存，進步文件體系機能。linux內核會從內存平分配出緩存區（體系緩存和數據緩存）來寄存熱數據，經由過程文件體系延遲寫入機制，等知足前提時（如緩存區年夜小達到必定百分比或許履行sync敕令）才會同步到磁盤。也就是說物理內存越年夜，分派緩存區越年夜，緩存數據越多。固然，辦事器毛病會喪失必定的緩存數據。
SSD硬盤取代SAS硬盤，將RAID級別調劑為RAID1+0，絕對於RAID1和RAID5有更好的讀寫機能（IOPS），究竟數據庫的壓力重要來自磁盤I/O方面。
4、數據庫架構擴大
跟著營業量愈來愈年夜，單台數據庫辦事器機能已沒法知足營業需求，該斟酌加機械了，該做集群了~~~。重要思惟是分化單台數據庫負載，沖破磁盤I/O機能，熱數據寄存緩存中，下降磁盤I/O拜訪頻率。
4.1 主從復制與讀寫分別
由於臨盆情況中，數據庫年夜多都是讀操作，所以安排一主多從架構，主數據庫擔任寫操作，並做雙擊熱備，多台從數據庫做負載平衡，擔任讀操作，主流的負載平衡器有LVS、HAProxy、Nginx。
怎樣來完成讀寫分別呢？年夜多半企業是在代碼層面完成讀寫分別，效力比擬高。另外一個種方法經由過程署理法式完成讀寫分別，企業中運用較少，罕見署理法式有MySQL Proxy、Amoeba。在如許數據庫集群架構中，年夜年夜增長數據庫高並發才能，處理單台機能瓶頸成績。假如從數據庫一台從庫能處置2000 QPS，那末5台就可以處置1w QPS，數據庫橫向擴大性也很輕易。
有時，面臨年夜量寫操作的運用時，單台寫機能達不到營業需求。假如做雙主，就會碰到數據庫數據紛歧致景象，發生這個緣由是在運用法式分歧的用戶會有能夠操作兩台數據庫，同時的更新操作形成兩台數據庫數據庫數據產生抵觸或許紛歧致。在單庫時MySQL應用存儲引擎機制表鎖和行鎖來包管數據完全性，如何在多台主庫時處理這個成績呢？有一套基於perl說話開辟的主從復制治理對象，叫MySQL-MMM（Master-Master replication managerfor Mysql，Mysql主主復制治理器），這個對象最年夜的長處是在統一時光只供給一台數據庫寫操作，有用包管數據分歧性。
主從復制博文：http://lizhenliang.blog.51cto.com/7876557/1290431
讀寫分別博文：http://lizhenliang.blog.51cto.com/7876557/1305083
MySQL-MMM博文：http://lizhenliang.blog.51cto.com/7876557/1354576
4.2 增長緩存
給數據庫增長緩存體系，把熱數據緩存到內存中，假如緩存中有要要求的數據就不再去數據庫中前往成果，進步讀機能。緩存完成有當地緩存和散布式緩存，當地緩存是將數據緩存到當地辦事器內存中或許文件中。散布式緩存可以緩存海量數據，擴大性好，主流的散布式緩存體系有memcached、redis，memcached機能穩固，數據緩存在內存中，速度很快，QPS可達8w閣下。假如想數據耐久化就選擇用redis，機能不低於memcached。
任務進程：
4.3 分庫
分庫是依據營業分歧把相干的表切分到分歧的數據庫中，好比web、bbs、blog等庫。假如營業量很年夜，還可將切分後的庫做主從架構，進一步防止單個庫壓力過年夜。
4.4 分表
數據量的日劇增長，數據庫中某個表有幾百萬條數據，招致查詢和拔出耗時太長，怎樣能處理單表壓力呢？你就該斟酌能否把這個表拆分紅多個小表，來加重單個表的壓力，進步處置效力，此方法稱為分表。
分表技巧比擬費事，要修正法式代碼裡的SQL語句，還要手動去創立其他表，也能夠用merge存儲引擎完成分表，絕對簡略很多。分表後，法式是對一個總表停止操作，這個總表不寄存數據，只要一些分表的關系，和更新數據的方法，總表會依據分歧的查詢，將壓力分到分歧的小表上，是以進步並發才能和磁盤I/O機能。
分表分為垂直拆分和程度拆分：
垂直拆分：把本來的一個許多字段的表拆分多個表，處理表的寬度成績。你可以把不經常使用的字段零丁放到一個表中，也能夠把年夜字段自力放一個表中，或許把聯系關系親密的字段放一個表中。
程度拆分：把本來一個表拆分紅多個表，每一個表的構造都一樣，處理單表數據量年夜的成績。
4.5 分區
分區就是把一張表的數據依據表構造中的字段（如range、list、hash等）分紅多個區塊，這些區塊可以在一個磁盤上，也能夠在分歧的磁盤上，分區後，外面上照樣一張表，但數據散列在多個地位，如許一來，多塊硬盤同時處置分歧的要求，從而進步磁盤I/O讀寫機能，完成比擬簡略。
注：增長緩存、分庫、分表和分區重要由法式猿來完成。
5、數據庫保護
數據庫保護是運維工程師或許DBA重要任務，包含機能監控、機能剖析、機能調優、數據庫備份和恢復等。
5.1 機能狀況症結目標
QPS，Queries Per Second：每秒查詢數，一台數據庫每秒可以或許處置的查詢次數
TPS，Transactions Per Second：每秒處置事務數
經由過程show status檢查運轉狀況，會有300多條狀況信息記載，個中有幾個值幫可以我們盤算出QPS和TPS，以下：
Uptime：辦事器曾經運轉的現實，單元秒
Questions：曾經發送給數據庫查詢數
Com_select：查詢次數，現實操作數據庫的
Com_insert：拔出次數
Com_delete：刪除次數
Com_update：更新次數
Com_commit：事務次數
Com_rollback：回滾次數
那末，盤算辦法來了，基於Questions盤算出QPS：
mysql> show global status like 'Questions'; mysql> show global status like 'Uptime';
QPS = Questions / Uptime
基於Com_commit和Com_rollback盤算出TPS：
mysql> show global status like 'Com_commit'; mysql> show global status like 'Com_rollback'; mysql> show global status like 'Uptime';
TPS = (Com_commit + Com_rollback) / Uptime
另外一盤算方法：基於Com_select、Com_insert、Com_delete、Com_update盤算出QPS
mysql> show global status where Variable_name in('com_select','com_insert','com_delete','com_update');
期待1秒再履行，獲得距離差值，第二次每一個變量值減去第一次對應的變量值，就是QPS
TPS盤算辦法：
mysql> show global status where Variable_name in('com_insert','com_delete','com_update');
盤算TPS，就不算查詢操作了，盤算出拔出、刪除、更新四個值便可。
經網友對這兩個盤算方法的測試得出，當數據庫中myisam表比擬多時，應用Questions盤算比擬精確。當數據庫中innodb表比擬多時，則以Com_*盤算比擬精確。
5.2 開啟慢查詢日記
MySQL開啟慢查詢日記，剖析出哪條SQL語句比擬慢，應用set設置變量，重啟辦事掉效，可以在my.cnf添加參數永遠失效。
mysql> set global slow-query-log=on #開啟慢查詢功效 mysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log'; #指定慢查詢日記文件地位 mysql> set global log_queries_not_using_indexes=on; #記載沒有應用索引的查詢 mysql> set global long_query_time=1; #只記載處置時光1s以上的慢查詢
剖析慢查詢日記，可使用MySQL自帶的mysqldumpslow對象，剖析的日記較為簡略。
# mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log #檢查最慢的前三個查詢
也能夠應用percona公司的pt-query-digest對象，日記剖析功效周全，可剖析slow log、binlog、general log。
剖析慢查詢日記：pt-query-digest /var/log/mysql/mysql-slow.log
剖析binlog日記：mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql
pt-query-digest --type=binlog mysql-bin.000001.sql
剖析通俗日記：pt-query-digest --type=genlog localhost.log
5.3 數據庫備份
備份數據庫是最根本的任務，也是最主要的，不然效果很嚴重，你理解！但因為數據庫比擬年夜，上百G，常常備份都很消耗時光，所以就該選擇一個效力高的備份戰略，關於數據量年夜的數據庫，普通都采取增量備份。經常使用的備份對象有mysqldump、mysqlhotcopy、xtrabackup等，mysqldump比擬實用於小的數據庫，由於是邏輯備份，所以備份和恢復耗時都比擬長。mysqlhotcopy和xtrabackup是物理備份，備份和恢復速度快，不影響數據庫辦事情形下停止熱拷貝，建議應用xtrabackup，支撐增量備份。
Xtrabackup備份對象應用博文：http://lizhenliang.blog.51cto.com/7876557/1612800
5.4 數據庫修復
有時刻MySQL辦事器忽然斷電、異常封閉，會招致表破壞，沒法讀取表數據。這時候便可以用到MySQL自帶的兩個對象停止修復，myisamchk和mysqlcheck。
myisamchk：只能修復myisam表，須要停滯數據庫
經常使用參數：
-f --force 強迫修復，籠罩老的暫時文件，普通不應用
-r --recover 恢復形式
-q --quik 疾速恢復
-a --analyze 剖析表
-o --safe-recover 老的恢復形式，假如-r沒法修復，可使用此參數嘗嘗
-F --fast 只檢討沒有正常封閉的表
疾速修復weibo數據庫:
# cd /var/lib/mysql/weibo
# myisamchk -r -q *.MYI
mysqlcheck：myisam和innodb表都可以用，不須要停滯數據庫，如修復單個表，可在數據庫前面添加表名，以空格朋分
經常使用參數：
-a --all-databases 檢討一切的庫
-r --repair 修復表
-c --check 檢討表，默許選項
-a --analyze 剖析表
-o --optimize 優化表
-q --quik 最快檢討或修復表
-F --fast 只檢討沒有正常封閉的表
疾速修復weibo數據庫:
mysqlcheck -r -q -uroot -p123 weibo
5.5 別的，檢查CPU和I/O機能辦法
#檢查CPU機能
#參數-P是顯示CPU數，ALL為一切，也能夠只顯示第幾顆CPU
#檢查I/O機能
#參數-m是以M單元顯示，默許K
#%util：當到達100%時，解釋I/O很忙。
#await：要求在隊列中期待時光，直接影響read時光。
I/O極限：IOPS（r/s+w/s）,普通RAID0/10在1200閣下。（IOPS，每秒停止讀寫（I/O）操作次數）
I/O帶寬：在次序讀寫形式下SAS硬盤實際值在300M/s閣下，SSD硬盤實際值在600M/s閣下。
以上是自己應用MySQL三年來總結的一些重要優化計劃，才能無限，有些不太周全，但這些根本可以或許知足中小型企業數據庫需求。
因為關系型數據庫初志設計限制，一些BAT公司海量數據放到關系型數據庫中，在海量數據查詢和剖析方面曾經達不到更好的機能。是以NoSQL火起來了，非關系型數據庫，年夜數據量，具有高機能，同時也填補了關系型數據庫某方面缺乏，逐漸年夜多半公司曾經將部門營業數據庫寄存到NoSQL中，如MongoDB、HBase等。數據存儲方面采取散布式文件體系，如HDFS、GFS等。海量數據盤算剖析采取Hadoop、Spark、Storm等。這些都是與運維相干的前沿技巧，也是在存儲方面重要進修對象，小同伴們配合加油吧！哪位博友有更好的優化計劃，迎接交換哦。
本文出自 “李振良的技巧博客” 博客
