對(duì)于全棧而言，數(shù)據(jù)庫技能不可或缺，關(guān)系型數(shù)據(jù)庫或者nosql，內(nèi)存型數(shù)據(jù)庫或者偏磁盤存儲(chǔ)的數(shù)據(jù)庫，對(duì)象存儲(chǔ)的數(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)，主要包括運(yùn)行環(huán)境，配置參數(shù)，SQL性能，和系統(tǒng)架構(gòu)設(shè)計(jì)調(diào)優(yōu)。

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

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

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

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

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

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

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

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

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

elevator=deadline

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

禁用numa特性

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

numa=off

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

修改swappiness設(shè)置

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

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

# vmstat -a 1

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

vm.swappiness = 1擴(kuò)大文件描述符

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

ulimit -n 51200

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

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

* hardnofile 51200 * softnofile 51200

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

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

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

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

noatime表示不記錄訪問時(shí)間,nodiratime不記錄目錄的訪問時(shí)間。

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

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

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

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

基本配置

query_cache_size

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

innodb_buffer_pool_size

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

innodb_log_file_size

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

max_connections

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

back_log

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

Innodb配置

innodb_file_per_table

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

innodb_flush_log_at_trx_commit

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

innodb_flush_method

這項(xiàng)配置決定了數(shù)據(jù)和日志寫入硬盤的方式。一般來說，如果你有硬件RAID控制器，并且其獨(dú)立緩存采用write-back機(jī)制，并有著電池?cái)嚯姳Ｗo(hù)，那么應(yīng)該設(shè)置配置為O_DIRECT；否則，大多數(shù)情況下應(yīng)將其設(shè)為fdatasync（默認(rèn)值）。sysbench是一個(gè)可以幫助你決定這個(gè)選項(xiàng)的好工具。

innodb_log_buffer_size

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

其他配置

log_bin

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

interactive_timeout

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

table_open_cache

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

max_allowed_packet

接受的數(shù)據(jù)包大?。辉黾釉撟兞康闹凳职踩?，這是因?yàn)閮H當(dāng)需要時(shí)才會(huì)分配額外內(nèi)存。例如，僅當(dāng)你發(fā)出長查詢或MySQLd必須返回大的結(jié)果行時(shí)MySQLd才會(huì)分配更多內(nèi)存。該變量之所以取較小默認(rèn)值是一種預(yù)防措施，以捕獲客戶端和服務(wù)器之間的錯(cuò)誤信息包，并確保不會(huì)因偶然使用大的信息包而導(dǎo)致內(nèi)存溢出

skip_name_resolve

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

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

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

基本原則

避免全表掃描

建立索引

盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理

盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力

使用基于游標(biāo)的方法或臨時(shí)表方法之前，應(yīng)先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差。

雕蟲小技

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

應(yīng)盡量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。

應(yīng)盡量避免在 where 子句中使用 or 來連接條件,可以考慮使用union 代替

in 和 not in 也要慎用，對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in，exists 代替 in

盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作和函數(shù)操作

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

盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。

盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)樽冮L字段存儲(chǔ)空間小，對(duì)于查詢來說，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。

最好不要給數(shù)據(jù)庫留NULL，盡可能的使用 NOT NULL填充數(shù)據(jù)庫.備注、描述、評(píng)論之類的可以設(shè)置為 NULL，其他的，最好不要使用NULL。

任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

關(guān)于臨時(shí)表

避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗。對(duì)于一次性事件， 最好使用導(dǎo)出表。

在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert。

如果使用到了臨時(shí)表，在最后將所有的臨時(shí)表顯式刪除時(shí)，先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長時(shí)間鎖定。

關(guān)于索引

先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。

在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件 時(shí)才能保證系統(tǒng)使用該索引， 否則該索引將不會(huì) 被使用， 并且應(yīng)盡可能的讓字段順序與索引順序相一致。

索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert和update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以視具體情況而定。一個(gè)表的索引數(shù)最好不要超過7個(gè)，若太多則應(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雙活

…..

來自：http://blog.jobbole.com/107264/