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

MySQL數(shù)據(jù)庫技術(shù)的方方面面也是很多，這里只涉及必備的性能調(diào)優(yōu)，推崇從下向上的性能調(diào)優(yōu)，主要包括運行環(huán)境，配置參數(shù)，SQL性能，和系統(tǒng)架構(gòu)設(shè)計調(diào)優(yōu)。

運行環(huán)境調(diào)優(yōu)

這里是Linux的天下，MySQL 運行環(huán)境的調(diào)優(yōu)往往和Linux的內(nèi)核調(diào)優(yōu)一并完成。當(dāng)然了，對云服務(wù)RDS 也有一定的參考作用。

調(diào)整Linux默認的IO調(diào)度算法.

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

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

1、在線動態(tài)修改,重啟失效。

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

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

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

elevator=deadline

主要關(guān)注elevator這個參數(shù),設(shè)置內(nèi)核的話需要重啟系統(tǒng)才能生效。

禁用numa特性

新一代架構(gòu)的NUMA不適合跑數(shù)據(jù)庫,NUMA是為了內(nèi)存利用率的提高,但反而可能導(dǎo)致一CPU的內(nèi)存尚有剩余,另外一個卻不夠用了,發(fā)生swap的問題,因此一般建議關(guān)閉或修改NUMA的調(diào)度。

1、修改/etc/grub.conf關(guān)閉NUMA,重啟后生效。

numa=off

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

修改swappiness設(shè)置

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

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

# vmstat -a 1 

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

vm.swappiness = 1 

擴大文件描述符

這個是經(jīng)常修改的參數(shù),高并發(fā)的程序都會修改.

1、動態(tài)修改,重啟失效,只能使用root,并且當(dāng)前session有效。

ulimit -n 51200 

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

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

* hard nofile 51200 
 
* soft nofile 51200 

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

優(yōu)化文件系統(tǒng)掛載參數(shù)

對于文件系統(tǒng)，如無特殊要求，***采用ext4.

文件系統(tǒng)掛載參數(shù)是在/etc/fstab文件中修改,重啟時候生效。

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

barrier=0,表示關(guān)閉barrier功能.

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

配置參數(shù)調(diào)優(yōu)

my.cnf中的配置參數(shù)調(diào)優(yōu)取決于業(yè)務(wù)，負載或硬件，在慢內(nèi)存和快磁盤、高并發(fā)和寫密集型負載情況下，都需要特殊的調(diào)整。

基本配置

query_cache_size

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

innodb_buffer_pool_size

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

innodb_log_file_size

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

max_connections

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

back_log

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

Innodb配置

innodb_file_per_table

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

innodb_flush_log_at_trx_commit

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

innodb_flush_method

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

innodb_log_buffer_size

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

其他配置

log_bin

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

interactive_timeout

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

table_open_cache

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

max_allowed_packet

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

skip_name_resolve

當(dāng)客戶端連接數(shù)據(jù)庫服務(wù)器時，且當(dāng)DNS很慢時，建立連接也會很慢。因此建議在啟動服務(wù)器時關(guān)閉skip_name_resolve選項而不進行DNS查找。

SQL 語句調(diào)優(yōu)

在應(yīng)用層，通過pt工具和慢查詢?nèi)罩镜呐浜?，可以輕松地分辨出全表掃描的語句。

基本原則

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

雕蟲小技

關(guān)于where 后的條件

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

關(guān)于數(shù)據(jù)類型

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

關(guān)于臨時表

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

關(guān)于索引

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

數(shù)據(jù)庫架構(gòu)調(diào)優(yōu)

從底層來到了應(yīng)用層，最終到架構(gòu)層，然而脫離業(yè)務(wù)邏輯談架構(gòu)就是耍流氓。數(shù)據(jù)庫架構(gòu)同樣是依賴業(yè)務(wù)系統(tǒng)的，穩(wěn)定而又彈性地服務(wù)業(yè)務(wù)系統(tǒng)是關(guān)鍵。架構(gòu)調(diào)優(yōu)的方向有：

• 分區(qū)分表
• 業(yè)務(wù)分庫
• 主從同步與讀寫分離
• 數(shù)據(jù)緩存
• 主從熱備與HA雙活
• …..

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