對于全棧而言，數據庫技能不可或缺，關系型數據庫或者nosql，內存型數據庫或者偏磁盤存儲的數據庫，對象存儲的數據庫或者圖數據庫……林林總總,但是***必備技能還應該是MySQL。從LAMP的興起，到Mariadb的出現，甚至PG的到來，熟練的MySQL技能都是大有用武之地的。

MySQL數據庫技術的方方面面也是很多，這里只涉及必備的性能調優，推崇從下向上的性能調優，主要包括運行環境，配置參數，SQL性能，和系統架構設計調優。

運行環境調優

這里是Linux的天下，MySQL 運行環境的調優往往和Linux的內核調優一并完成。當然了，對云服務RDS 也有一定的參考作用。

調整Linux默認的IO調度算法.

IO調度器的總體目標是希望讓磁頭能夠總是往一個方向移動,移動到底了再往反方向走,這恰恰就是現實生活中的電梯模型,所以IO調度器也被叫做電梯 (elevator)，而相應的算法也就被叫做電梯算法.而Linux中IO調度的電梯算法有好幾種,一個叫做as(Anticipatory),一個叫做 cfq(Complete Fairness Queueing),一個叫做deadline,還有一個叫做noop(No Operation).

IO對數據庫的影響較大，linux默認的IO調度算法為cfq,需要修改為deadline,如果是SSD或者PCIe-SSD設備,需要修改為noop,可以使用下面兩種修改方式。

1、在線動態修改,重啟失效。

echo “deadline” > /sys/block/sda/queue/scheduler 

2、修改/etc/grub.conf,***生效。

修改/etc/grub.conf配置文件,在kernel那行增加一個配置,例如:

elevator=deadline

主要關注elevator這個參數,設置內核的話需要重啟系統才能生效。

禁用numa特性

新一代架構的NUMA不適合跑數據庫,NUMA是為了內存利用率的提高,但反而可能導致一CPU的內存尚有剩余,另外一個卻不夠用了,發生swap的問題,因此一般建議關閉或修改NUMA的調度。

1、修改/etc/grub.conf關閉NUMA,重啟后生效。

numa=off

2、修改/etc/init.d/mysql或mysqld_safe腳本,設置啟動mysqld進程時的NUMA調度機制,如 numactl –interleave=all。

修改swappiness設置

swappiness是linux的一個內核參數,用來控制物理內存交換出去的策略.它允許一個百分比的值,最小的為0,***的為100,改值默認是60.這個設置值到底有什么影響呢?

vm.swappiness設置為0表示盡量少使用swap,100表示盡量將inactive的內存頁交換到swap里或者釋放cache。inactive內存的意思是程序映射著，但是”長時間”不用的內存。我們可以利用vmstat查看系統里面有多少inactive的內存。

# vmstat -a 1 

這個值推薦設置為1,設置方法如下,在/etc/sysctl.conf文件中增加一行。

vm.swappiness = 1 

擴大文件描述符

這個是經常修改的參數,高并發的程序都會修改.

1、動態修改,重啟失效,只能使用root,并且當前session有效。

ulimit -n 51200 

2、修改配置文件,***生效。

在/etc/security/limits.conf配置文件中增加

* hard nofile 51200 
 
* soft nofile 51200 

面向session的進程文件描述符的修改稍有不同，在云上的修改也略有差異，可以參見一樣的“open too many files”

優化文件系統掛載參數

對于文件系統，如無特殊要求，***采用ext4.

文件系統掛載參數是在/etc/fstab文件中修改,重啟時候生效。

noatime表示不記錄訪問時間,nodiratime不記錄目錄的訪問時間。

barrier=0,表示關閉barrier功能.

barrier的主要目的是為了保證磁盤寫數據的安全性,但是會降低性能。如果有BBU之類的電池備份電源保證控制卡不瞬間掉電,那么這個功能就可以放心大膽的關閉。

配置參數調優

my.cnf中的配置參數調優取決于業務，負載或硬件，在慢內存和快磁盤、高并發和寫密集型負載情況下，都需要特殊的調整。

基本配置

query_cache_size

query cache是一個眾所周知的瓶頸，甚至在并發并不多時也如此。 ***是一開始就停用，設置query_cache_size = 0，并利用其他方法加速查詢：優化索引、增加拷貝分散負載或者啟用額外的緩存(比如memcache或redis)。如果已經啟用了query cache并且還沒有發現任何問題，query cache可能有用。如果想停用它，那就得小心了。

innodb_buffer_pool_size

緩沖池是數據和索引緩存的地方：這個值越大越好，這能保證你在大多數的讀取操作時使用的是內存而不是硬盤。典型的值是5-6GB(8GB內存)，20-25GB(32GB內存)，100-120GB(128GB內存)。

innodb_log_file_size

redo日志被用于確保寫操作快速而可靠并且在崩潰時恢復。從MySQL 5.5之后，崩潰恢復的性能的到了很大提升，可以同時擁有較高的寫入性能和崩潰恢復性能。在MySQL 5.6里可以被提高到4GB以上。如果應用程序需要頻繁的寫入數據，可以一開始就把它這是成4G。

max_connections

max_connection值被設高了(例如1000或更高)之后一個主要缺陷是當服務器運行1000個或更高的活動事務時會變的沒有響應。在應用程序里使用連接池或者在MySQL里使用進程池有助于解決這一問題。

back_log

要求 mysql 能有的連接數量。當主要mysql線程在一個很短時間內得到非常多的連接請求，這就起作用，然后主線程花些時間檢查連接并且啟動一個新線程。back_log指明在mysql暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。只有如果期望在一個短時間內有很多連接，需要增加它，換句話說，該值對到來的tcp/ip連接的偵聽隊列的大小。

Innodb配置

innodb_file_per_table

這項設置告知InnoDB是否需要將所有表的數據和索引存放在共享表空間里(innodb_file_per_table = OFF)或者為每張表的數據單獨放在一個.ibd文件(innodb_file_per_table = ON)。每張表一個文件允許你在drop、truncate或者rebuild表時回收磁盤空間。這對于一些高級特性也是有必要的，比如數據壓縮。但是它不會帶來任何性能收益。MySQL 5.6中，這個屬性默認值是ON。

innodb_flush_log_at_trx_commit

默認值為1，表示InnoDB完全支持ACID特性。當關注點是數據安全的時候這個值是最合適的，比如在一個主節點上。但是對于磁盤(讀寫)速度較慢的系統，它會帶來很巨大的開銷，因為每次將改變flush到redo日志都需要額外的fsyncs。如果值為0速度就更快了，但在系統崩潰時可能丟失一些數據, 所以一遍只適用于備份節點。

innodb_flush_method

這項配置決定了數據和日志寫入硬盤的方式。一般來說，如果你有硬件RAID控制器，并且其獨立緩存采用write-back機制，并有著電池斷電保護，那么應該設置配置為O_DIRECT;否則，大多數情況下應將其設為fdatasync(默認值)。sysbench是一個可以幫助你決定這個選項的好工具。

innodb_log_buffer_size

這項配置決定了為尚未執行的事務分配的緩存。但是如果事務中包含有二進制大對象或者大文本字段的話，看Innodb_log_waits狀態變量，如果它不是0，增加innodb_log_buffer_size。

其他配置

log_bin

如果數據庫服務器充當主節點的備份節點，那么開啟二進制日志是必須的。就算只有一個服務器，如果你想做基于時間點的數據恢復，這也是很有用的。二進制日志一旦創建就將***保存。如果不想讓磁盤空間耗盡，你可以用 PURGE BINARY LOGS 來清除舊文件，或者設置 expire_logs_days 來指定過多少天日志將被自動清除。記錄二進制日志不是沒有開銷的，所以如果你在一個非主節點的復制節點上不需要它的話，那么建議關閉這個選項。

interactive_timeout

服務器在關閉它前在一個交互連接上等待行動的秒數。一個交互的客戶被定義為對 mysql_real_connect()使用 client_interactive 選項的客戶。 默認數值是28800，建議改為7200。

table_open_cache

MySQL每打開一個表，都會讀入一些數據到table_open_cache緩存中，當MySQL在這個緩存中找不到相應信息時，才會去磁盤上讀取。假定系統有200個并發連接，則需將此參數設置為200*N(N為每個連接所需的文件描述符數目);當把table_open_cache設置為很大時，如果系統處理不了那么多文件描述符，那么就會出現客戶端失效，連接不上。

max_allowed_packet

接受的數據包大小;增加該變量的值十分安全，這是因為僅當需要時才會分配額外內存。例如，僅當你發出長查詢或MySQLd必須返回大的結果行時MySQLd才會分配更多內存。該變量之所以取較小默認值是一種預防措施，以捕獲客戶端和服務器之間的錯誤信息包，并確保不會因偶然使用大的信息包而導致內存溢出

skip_name_resolve

當客戶端連接數據庫服務器時，且當DNS很慢時，建立連接也會很慢。因此建議在啟動服務器時關閉skip_name_resolve選項而不進行DNS查找。

SQL 語句調優

在應用層，通過pt工具和慢查詢日志的配合，可以輕松地分辨出全表掃描的語句。

基本原則

• 避免全表掃描
• 建立索引
• 盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理
• 盡量避免大事務操作，提高系統并發能力
• 使用基于游標的方法或臨時表方法之前，應先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。盡量避免使用游標，因為游標的效率較差。

雕蟲小技

關于where 后的條件

• 應盡量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進行全表掃描。
• 應盡量避免在 where 子句中使用 or 來連接條件,可以考慮使用union 代替
• in 和 not in 也要慎用，對于連續的數值，能用 between 就不要用 in，exists 代替 in
• 盡量避免在 where 子句中對字段進行表達式操作和函數操作

關于數據類型

• 盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。
• 盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為變長字段存儲空間小，對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。
• ***不要給數據庫留NULL，盡可能的使用 NOT NULL填充數據庫.備注、描述、評論之類的可以設置為 NULL，其他的，***不要使用NULL。
• 任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

關于臨時表

• 避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。對于一次性事件， ***使用導出表。
• 在新建臨時表時，如果一次性插入數據量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度;如果數據量不大，為了緩和系統表的資源，應先create table，然后insert。
• 如果使用到了臨時表，在***將所有的臨時表顯式刪除時，先 truncate table ，然后 drop table ，這樣可以避免系統表的較長時間鎖定。

關于索引

• 先應考慮在 where 及 order by 涉及的列上建立索引。
• 在使用索引字段作為條件時，如果該索引是復合索引，那么必須使用到該索引中的***個字段作為條件 時才能保證系統使用該索引， 否則該索引將不會 被使用， 并且應盡可能的讓字段順序與索引順序相一致。
• 索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert和update 的效率，因為 insert 或 update 時有可能會重建索引，所以視具體情況而定。一個表的索引數***不要超過7個，若太多則應考慮一些不常使用到的列上建的索引是否有必要.

數據庫架構調優

從底層來到了應用層，最終到架構層，然而脫離業務邏輯談架構就是耍流氓。數據庫架構同樣是依賴業務系統的，穩定而又彈性地服務業務系統是關鍵。架構調優的方向有：

• 分區分表
• 業務分庫
• 主從同步與讀寫分離
• 數據緩存
• 主從熱備與HA雙活
• …..

【本文來自51CTO專欄作者老曹的原創文章，作者微信公眾號：喔家ArchiSelf,id:wrieless-com】