知識星球作者：李振良
來自：http://blog.51cto.com/lizhenliang/2095526
一個成熟的資料庫架構並不是一開始設計就具備高可用、高伸縮等特性的，它是隨著使用者量的增加，基礎架構才逐漸完善。這篇文章主要談談MySQL資料庫在發展週期中所面臨的問題及最佳化方案，暫且拋開前端應用不說，大致分為以下五個階段：
階段一：資料庫表設計
專案立項後，開發部門根據產品部門需求開發專案。
開發工程師在開發專案初期會對錶結構設計。對於資料庫來說，表結構設計很重要，如果設計不當，會直接影響到使用者訪問網站速度，使用者體驗不好！這種情況具體影響因素有很多，例如慢查詢（低效的查詢陳述句）、沒有適當建立索引、資料庫堵塞（鎖）等。當然，有測試部門的團隊，會做產品測試，找Bug。
由於開發工程師重視點不同，初期不會考慮太多資料庫設計是否合理，而是儘快完成功能實現和交付。等專案上線有一定訪問量後，隱藏的問題就會暴露，這時再去修改就不是這麼容易的事了！
階段二：資料庫部署
是時候運維工程師出場了，專案上線。
專案初期訪問量一般是寥寥無幾，此階段Web+資料庫單臺部署足以應對在1000左右的QPS（每秒查詢率）。考慮到單點故障，應做到高可用性，可採用MySQL主從複製+Keepalived實現雙機熱備。主流HA軟體有：Keepalived（推薦）、Heartbeat。
階段三：資料庫效能最佳化
如果將MySQL部署到普通的X86伺服器上，在不經過任何最佳化情況下，MySQL理論值正常可以處理1500左右QPS，經過最佳化後，有可能會提升到2000左右QPS。否則，訪問量當達到1500左右併發連線時，資料庫處理效能可能響應就會慢，而且硬體資源還比較富裕，這時就該考慮效能最佳化問題了。那麼怎樣能讓資料庫發揮最大效能呢？主要從硬體配置、資料庫配置、架構方面著手，具體分為以下：
3.1 硬體配置
如果有條件一定要SSD固態硬碟代替SAS機械硬碟，將RAID級別調整為RAID1+0，相對於RAID1和RAID5有更好的讀寫效能，畢竟資料庫的壓力主要來自磁碟I/O方面。
Linux內核有一個特性，會從物理記憶體中劃分出快取區（系統快取和資料快取）來存放熱資料，透過檔案系統延遲寫入機制，等滿足條件時（如快取區大小到達一定百分比或者執行sync命令）才會同步到磁碟。也就是說物理記憶體越大，分配快取區越大，快取資料越多。當然，伺服器故障會丟失一定的快取資料。建議物理記憶體至少富裕50%以上。
3.2 資料庫配置最佳化
MySQL應用最廣泛的有兩種儲存引擎：一個是MyISAM，不支援事務處理，讀效能處理快，表級別鎖。另一個是InnoDB，支援事務處理（ACID屬性），設計標的是為大資料處理，行級別鎖。
表鎖：開銷小，鎖定粒度大，發生死鎖機率高，相對併發也低。
行鎖：開銷大，鎖定粒度小，發生死鎖機率低，相對併發也高。
為什麼會出現表鎖和行鎖呢？主要為保證資料完整性。舉個例子，一個使用者在操作一張表，其他使用者也想操作這張表，那麼就要等第一個使用者操作完，其他使用者才能操作，表鎖和行鎖就是這個作用。否則多個使用者同時操作一張表，肯定會資料產生衝突或者異常。
根據這些方面看，使用InnoDB儲存引擎是最好的選擇，也是MySQL5.5+版本預設儲存引擎。每個儲存引擎相關執行引數比較多，以下列出可能影響資料庫效能的引數。
公共引數預設值：
max_connections = 151
# 同時處理最大連線數，建議設定最大連線數是上限連線數的80%左右
sort_buffer_size = 2M
# 查詢排序時緩衝區大小，只對order by和group by起作用，建議增大為16M
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
query_cache_type = ON
# 開啟查詢快取功能
query_cache_limit = 1M
# 查詢快取限制，只有1M以下查詢結果才會被快取，以免結果資料較大把快取池改寫
query_cache_size = 16M
# 檢視緩衝區大小，用於快取SELECT查詢結果，下一次有同樣SELECT查詢將直接從快取池傳回結果，可適當成倍增加此值
InnoDB引數預設值：
innodb_buffer_pool_size = 128M
# 索引和資料緩衝區大小，建議設定物理記憶體的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
# 是否共享表空間，5.7+版本預設ON，共享表空間idbdata檔案不斷增大，影響一定的I/O效能。建議開啟獨立表空間樣式，每個表的索引和資料都存在自己獨立的表空間中，可以實現單表在不同資料庫中移動。
innodb_log_buffer_size = 8M
# 日誌緩衝區大小，由於日誌最長每秒鐘掃清一次，所以一般不用超過16M
3.3 系統核心引數最佳化
大多數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
階段四：資料庫架構擴充套件
隨著業務量越來越大，單臺資料庫伺服器效能已無法滿足業務需求，該考慮增加伺服器擴充套件架構了。主要思想是分解單臺資料庫負載，突破磁碟I/O效能，熱資料存放快取中，降低磁碟I/O訪問頻率。
4.1 增加快取
給資料庫增加快取系統，把熱資料快取到記憶體中，如果快取中有請求的資料就不再去請求MySQL，減少資料庫負載。快取實現有本地快取和分散式快取，本地快取是將資料快取到本地伺服器記憶體中或者檔案中。分散式快取可以快取海量資料，擴充套件性好，主流的分散式快取系統：memcached、redis，memcached效能穩定，資料快取在記憶體中，速度很快，QPS理論可達8w左右。如果想資料持久化就選擇用redis，效能不低於memcached。
工作過程：
4.2 主從複製與讀寫分離
在生產環境中，業務系統通常讀多寫少，可部署一主多從架構，主資料庫負責寫操作，並做雙機熱備，多臺從資料庫做負載均衡，負責讀操作。主流的負載均衡器：LVS、HAProxy、Nginx。
怎麼來實現讀寫分離呢？大多數企業是在程式碼層面實現讀寫分離，效率高。另一個種方式透過代理程式實現讀寫分離，企業中應用較少，會增加中介軟體消耗。主流中介軟體代理系統有MyCat、Atlas等。
在這種MySQL主從複製拓撲架構中，分散單臺負載，大大提高資料庫併發能力。如果一臺從伺服器能處理1500 QPS，那麼3臺就能處理4500 QPS，而且容易橫向擴充套件。
有時，面對大量寫操作的應用時，單臺寫效能達不到業務需求。就可以做雙向複製（雙主），但有個問題得註意：兩臺主伺服器如果都對外提供讀寫操作，就可能遇到資料不一致現象，產生這個原因是程式有同時操作兩臺資料庫機率，同時的更新操作會造成兩臺資料庫資料發生衝突或者不一致。
可設定每個表ID欄位自增唯一：auto_increment_increment和auto_increment_offset，也可以寫演演算法生成隨機唯一。
官方近兩年推出的MGR（多主複製）叢集也可以考慮下。
4.3 分庫
分庫是根據業務將資料庫中相關的表分離到不同的資料庫中，例如web、bbs、blog等庫。如果業務量很大，還可將分離後的資料庫做主從複製架構，進一步避免單庫壓力過大。
4.4 分表
資料量的日劇增加，資料庫中某個表有幾百萬條資料，導致查詢和插入耗時太長，怎麼能解決單表壓力呢？你應該考慮把這個表拆分成多個小表，來減輕單個表的壓力，提高處理效率，此方式稱為分表。
分表技術比較麻煩，要修改程式程式碼裡的SQL陳述句，還要手動去建立其他表，也可以用merge儲存引擎實現分表，相對簡單許多。分表後，程式是對一個總表進行操作，這個總表不存放資料，只有一些分表的關係，以及更新資料的方式，總表會根據不同的查詢，將壓力分到不同的小表上，因此提高併發能力和磁碟I/O效能。
分表分為垂直拆分和水平拆分：
-
垂直拆分：把原來的一個很多欄位的表拆分多個表，解決表的寬度問題。你可以把不常用的欄位單獨放到一個表中，也可以把大欄位獨立放一個表中，或者把關聯密切的欄位放一個表中。
-
水平拆分：把原來一個表拆分成多個表，每個表的結構都一樣，解決單表資料量大的問題。
4.5 分割槽
分割槽就是把一張表的資料根據表結構中的欄位（如range、list、hash等）分成多個區塊，這些區塊可以在一個磁碟上，也可以在不同的磁碟上，分割槽後，錶面上還是一張表，但資料雜湊在多個位置，這樣一來，多塊硬碟同時處理不同的請求，從而提高磁碟I/O讀寫效能。
註：增加快取、分庫、分表和分割槽主要由程式猿或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陳述句比較慢，支援動態開啟：
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 資料庫備份
備份資料庫是最基本的工作，也是最重要的，否則後果很嚴重，你懂得！高頻率的備份策略，選用一個穩定快速的工具至關重要。資料庫大小在2G以內，建議使用官方的邏輯備份工具mysqldump。超過2G以上，建議使用percona公司的物理備份工具xtrabackup，否則慢的跟蝸牛似得。這兩個工具都支援InnoDB儲存引擎下熱備，不影響業務讀寫操作。
5.4 資料庫修複
有時候MySQL伺服器突然斷電、異常關閉，會導致表損壞，無法讀取表資料。這時就可以用到MySQL自帶的兩個工具進行修複，myisamchk和mysqlcheck。前者只能修複MyISAM表，並且停止資料庫，後者MyISAM和InnoDB都可以，線上修複。
註意：修複前最好先備份資料庫。
myisamchk常用引數：
-f --force 強制修複，改寫老的臨時檔案，一般不使用
-r --recover 恢復樣式
-q --quik 快速恢復
-a --analyze 分析表
-o --safe-recover 老的恢復樣式，如果-r無法修複，可以使用此引數試試
-F --fast 只檢查沒有正常關閉的表
例如：myisamchk -r -q *.MYI
mysqlcheck常用引數：
-a --all-databases 檢查所有的庫
-r --repair 修複表
-c --check 檢查表，預設選項
-a --analyze 分析表
-o --optimize 最佳化表
-q --quik 最快檢查或修複表
-F --fast 只檢查沒有正常關閉的表
例如：mysqlcheck -r -q -uroot -p123456 weibo
5.5 MySQL伺服器效能分析
重點關註：
-
id：CPU利用率百分比，平均小於60%正常，但已經比較繁忙了。
-
wa：CPU等待磁碟IO響應時間，一般大於5說明磁碟讀寫量大。
KB_read/s、KB_wrtn/s 每秒讀寫資料量，主要根據磁碟每秒最高讀寫速度評估。
r/s、w/s：每秒讀寫請求次數，可以理解為IOPS（每秒輸入輸出量），是衡量磁碟效能的主要指標之一。
await：IO平均每秒響應時間，一般大於5說明磁碟響應慢，超過自身效能。
util：磁碟利用率百分比，平均小於60%正常，但已經比較繁忙了。
小結
由於關係型資料庫初衷設計限制，在大資料處理時會顯得力不從心。因此NoSQL（非關係型資料庫）火起來了，天生勵志，具備分散式、高效能、高可靠等特性，彌補了關係型資料庫某方面先天性不足，非常適合儲存非結構化資料。主流NoSQL資料庫有：MongoDB、HBase、Cassandra等。
單純資料庫層面最佳化效果提升並不多明顯，主要還是要根據業務場景選擇合適的資料庫！
●編號321，輸入編號直達本文
●輸入m獲取文章目錄
Web開發
更多推薦《18個技術類公眾微信》
涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。
