mysql 優(yōu)化

數(shù)據(jù)庫層面：

• 應(yīng)用系統(tǒng)層面優(yōu)化
  

1. SQL優(yōu)化

   SQL優(yōu)化一般通過分析慢查詢?nèi)罩緛碜ト￠L事務(wù)高消耗的sql，通過結(jié)合具體業(yè)務(wù)，對sql邏輯進(jìn)行分析and精簡，or重寫sql。通過配置slow_query_log=1和log_queries_not_using_indexes=1啟動慢查詢?nèi)罩居涗浐陀涗浵聸]有使用索引的查詢,后者會讓慢查詢?nèi)罩疚募芸炫蛎洠枰〞r對文件進(jìn)行切割。分析慢日志的工具一般使用pt工具包的pt-query-digest或者mysql自帶的mysqldumpslow，個人比較傾向用pt,分析出來的內(nèi)容比較詳細(xì)。需要注意pt-query-digest其實(shí)是一個perl腳本，如果慢日志文件較大（幾G多），需要大量消耗CPU資源。建議在業(yè)務(wù)低峰時候調(diào)度該工具。

2. 索引優(yōu)化

   索引是數(shù)據(jù)庫最為常見的對象。基本上90%的sql性能問題都是沒有建索引or低效索引導(dǎo)致的。所以根據(jù)實(shí)際業(yè)務(wù)場景建合適的索引，能夠使得sql優(yōu)化事半功倍。

   索引的設(shè)計規(guī)則：選擇唯一性索引or主鍵；為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引，盡量建立復(fù)合索引而非單列索引；為常作為查詢條件的字段建立索引；限制索引的數(shù)目；盡量使用數(shù)據(jù)量少的索引；盡量使用前綴來索引；刪除不再使用或者很少使用的索引。

   創(chuàng)建索引的一些注意事項(xiàng)：

   
   

   （1）避免在where子句中使用!=或者<>操作符，否則引擎會放棄索引而進(jìn)行全表掃描

   （2）避免在where子句中使用or來連接條件，考慮用union代替。

   （3）避免在where子句中對字段進(jìn)行表達(dá)式操作或者函數(shù)操作

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

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

   （6）索引固然可以提高相應(yīng)的 select 的效率，但是也需要成本去維護(hù)索引，因此表的索引個數(shù)并非越多越好，一般不要超過7個。

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

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

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

   （10）對于varchar的字段創(chuàng)建索引，指定索引長度，避免創(chuàng)建全字段索引，可以通過count(distinct left(列名，索引長度))/count(*)計算區(qū)分度來確定索引長度。

   （11）多表關(guān)聯(lián)查詢的時候，必須保證關(guān)聯(lián)的字段有索引。而且字段的類型必須一致，避免由于隱式轉(zhuǎn)換導(dǎo)致索引失效。

3. 庫表優(yōu)化

   舉個例子：業(yè)務(wù)前期不注重表設(shè)計，導(dǎo)致日志、報文、圖片這類數(shù)據(jù)都通過表的方式存儲在數(shù)據(jù)庫。而這類數(shù)據(jù)一般是通過text/blob等類型的字段存儲，極易使得表容量暴增，而且很難去優(yōu)化這類表的查詢sql。需要做好庫表設(shè)計，對圖片、報文這類數(shù)據(jù)，改為通過mongodb等NOSQL數(shù)據(jù)庫進(jìn)行存儲。或者將表的部分大字段進(jìn)行拆分，單獨(dú)出來一個表，通過冗余部分字段，實(shí)現(xiàn)表與表之間的數(shù)據(jù)關(guān)聯(lián)（不建議通過mysql的外鍵約束實(shí)現(xiàn)關(guān)聯(lián)，因?yàn)樵诟卟l(fā)的情況下，會出現(xiàn)大量行鎖影響數(shù)據(jù)庫性能，強(qiáng)烈建議通過應(yīng)用程序?qū)崿F(xiàn)數(shù)據(jù)關(guān)聯(lián)。）

   采用統(tǒng)一的字符集和校驗(yàn)集，使用innodb引擎，表設(shè)計中采用與業(yè)務(wù)無關(guān)的自增ID列作為主鍵，減少存儲過程and自定義函數(shù)，盡量不用text/blob這類字段類型。

4. 表設(shè)計規(guī)范

   請參考附件 阿里巴巴Java開發(fā)手冊 和 58到家數(shù)據(jù)庫30條軍規(guī)解讀 （https://www.oschina.net/question/54100_2231325）

   
   

5. 數(shù)據(jù)庫對象優(yōu)化

• 內(nèi)存配置優(yōu)化

  innodb緩沖池設(shè)置：innodb_buffer_pool_size，一般為整機(jī)內(nèi)存的70%~80%

  緩沖池臟頁占比：innodb_max_dirty_pages_pct，默認(rèn)為75%，建議按照業(yè)務(wù)場景進(jìn)行設(shè)置。

  強(qiáng)烈建議關(guān)閉query cache。通過配置文件設(shè)置query_cache_size = 0、query_cache_type = 0即可。

  redo log緩沖區(qū)設(shè)置：innodb_log_buffer_size，如果沒大事務(wù)，控制在8M-16M即可，生產(chǎn)環(huán)境目前配置到64M。

  
  

• IO配置優(yōu)化

  
  

  sync_binlog:

  sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓Filesystem自行決定什么時候來做同步，或者cache滿了之后才同步到磁盤。
  

  sync_binlog=n，當(dāng)每進(jìn)行n次事務(wù)提交之后，MySQL將進(jìn)行一次fsync之類的磁盤同步指令來將binlog_cache中的數(shù)據(jù)強(qiáng)制寫入磁盤。

  
  

  sync_relay_log:

  同sync_binlog參數(shù)功能一樣，只不過對象是relay log而不是binlog。

  
  

  innodb_flush_log_at_trx_commit:

  如果innodb_flush_log_at_trx_commit設(shè)置為0，log buffer將每秒一次地寫入log file中，并且log file的flush(刷到磁盤)操作同時進(jìn)行.該模式下，在事務(wù)提交的時候，不會主動觸發(fā)寫入磁盤的操作。
  如果innodb_flush_log_at_trx_commit設(shè)置為1，每次事務(wù)提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file，并且flush(刷到磁盤)中去.
  如果innodb_flush_log_at_trx_commit設(shè)置為2，每次事務(wù)提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file.但是flush(刷到磁盤)操作并不會同時進(jìn)行。該模式下,MySQL會每秒執(zhí)行一次 flush(刷到磁盤)操作。

  
  

  sync_master_info:

  每間隔多少事務(wù)刷新master.info，如果是table（innodb）設(shè)置無效，每個事務(wù)都會更新

  
  

  sync_relay_log_info:

  每間隔多少事務(wù)刷新relay-log.info，如果是table（innodb）設(shè)置無效，每個事務(wù)都會更新

  
  

  master_info_repository:

  記錄主庫binlog的信息，可以設(shè)置FILE（master.info）或者TABLE（mysql.slave_master_info）

  
  

  relay_log_info_repository:

  記錄備庫relaylog的信息，可以設(shè)置FILE（relay-log.info）或者TABLE（mysql.slave_relay_log_info）

  
  

  innodb_io_capacity:默認(rèn)為200，如果是SSD盤，建議調(diào)整到5000
  

  
  

• 高并發(fā)設(shè)置

    擴(kuò)大文件描述符：

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

    2、修改配置文件,永久生效，在/etc/security/limits.conf配置文件中增加：          

        * soft nofile 65535
        * soft nproc 65535
        * hard nofile 65535
        * hard nproc 65535

主機(jī)層面：

1、CPU

將其調(diào)整為性能模式，即：performance，可以參考博客：http://blog.csdn.net/myarrow/article/details/7917181/

2、內(nèi)存

（1）關(guān)閉NUMA特性。NUMA陷阱現(xiàn)象是當(dāng)你的服務(wù)器還有內(nèi)存的時候，發(fā)現(xiàn)它已經(jīng)在開始使用swap了，甚至已經(jīng)導(dǎo)致機(jī)器出現(xiàn)停滯的現(xiàn)象。這個就有可能是由于numa的限制，如果一個進(jìn)程限制它只能使用自己的numa節(jié)點(diǎn)的內(nèi)存，那么當(dāng)自身numa node內(nèi)存使用光之后，就不會去使用其他numa node的內(nèi)存了，會開始使用swap，甚至更糟的情況，機(jī)器沒有設(shè)置swap的時候，可能會直接宕機(jī)。所以，強(qiáng)烈建議在操作系統(tǒng)層面關(guān)閉NUMA特性。直接在/etc/grub.conf的kernel行最后添加numa=off即可。

（2）盡量配置高內(nèi)存。這種mysql可以充分利用內(nèi)存資源緩存熱塊數(shù)據(jù)，避免由于內(nèi)存不足導(dǎo)致臟數(shù)據(jù)不斷地刷盤從而產(chǎn)生IO瓶頸，也可以避免熱塊數(shù)據(jù)被擠出緩存區(qū)的情況發(fā)生。

（3）修改swappiness設(shè)置。swappiness是linux的一個內(nèi)核參數(shù),用來控制物理內(nèi)存交換出去的策略.它允許一個百分比的值,最小的為0,最大的為100,改值默認(rèn)是60.m.swappiness設(shè)置為0表示盡量少使用swap,100表示盡量將inactive的內(nèi)存頁交換到swap里或者釋放cache。inactive內(nèi)存的意思是程序映射著，但是”長時間”不用的內(nèi)存。這個值推薦設(shè)置為1,設(shè)置方法如下,在/etc/sysctl.conf文件中增加一行：vm.swappiness = 1

3、磁盤IO

（1）盡量將數(shù)據(jù)文件和日志文件分開，各自承載相應(yīng)的磁盤。避免日志刷盤和數(shù)據(jù)刷盤之間爭用IO。

（2）盡量使用高IO的磁盤，或者使用raid10這類磁盤陣列，或者直接采用SSD盤

4、網(wǎng)絡(luò)優(yōu)化

集群內(nèi)機(jī)器最好部署在同一內(nèi)網(wǎng)or專線直連的網(wǎng)絡(luò)環(huán)境，以保證低延遲，高吞吐的網(wǎng)絡(luò)環(huán)境。避免由于網(wǎng)絡(luò)問題導(dǎo)致的集群內(nèi)腦裂or主從復(fù)制異常的情況。

操作系統(tǒng)層面：

1、文件系統(tǒng)

    強(qiáng)烈建議采用xfs文件系統(tǒng)，ext4文件系統(tǒng)存在bug，觸發(fā)會占用自身大部分IO，造成IO瓶頸。詳見http://1057212.blog.51cto.com/1047212/1891734

    優(yōu)化文件系統(tǒng)掛載參數(shù)：文件系統(tǒng)掛載參數(shù)是在/etc/fstab文件中修改,重啟時候生效。noatime表示不記錄訪問時間,nodiratime不記錄目錄的訪問時間。barrier=0,表示關(guān)閉barrier功能。其中nobarrier是xfs文件系統(tǒng)特有，ext4文件系統(tǒng)并無此參數(shù)。

2、IO調(diào)度算法

    NOOP:NOOP算法的全寫為No Operation。該算法實(shí)現(xiàn)了最最簡單的FIFO隊(duì)列，所有IO請求大致按照先來后到的順序進(jìn)行操作。

    CFQ:CFQ算法的全寫為Completely Fair Queuing。該算法的特點(diǎn)是按照IO請求的地址進(jìn)行排序，而不是按照先來后到的順序來進(jìn)行響應(yīng)。

    DEADLINE:DEADLINE在CFQ的基礎(chǔ)上，解決了IO請求餓死的極端情況。除了CFQ本身具有的IO排序隊(duì)列之外，DEADLINE額外分別為讀IO和寫IO提供了FIFO隊(duì)列。讀FIFO隊(duì)列的最大等待時間為500ms，寫FIFO隊(duì)列的最大等待時間為5s。FIFO隊(duì)列內(nèi)的IO請求優(yōu)先級要比CFQ隊(duì)列中的高，，而讀FIFO隊(duì)列的優(yōu)先級又比寫FIFO隊(duì)列的優(yōu)先級高。優(yōu)先級可以表示為：FIFO(Read) > FIFO(Write) > CFQ

    一般mysql服務(wù)器的磁盤IO調(diào)度算法采用deadline，既可以保證IO請求不被餓死，又可以使得讀IO的處理優(yōu)先級大于寫IO。

系統(tǒng)架構(gòu)層面優(yōu)化

1. 負(fù)載均衡

   這里可以分為兩類：

   1、PXC or mysql cluster or mysql group replication這類數(shù)據(jù)庫集群，由于支持多點(diǎn)寫入的方式，這里的負(fù)載均衡可以實(shí)現(xiàn)讀和寫都均衡負(fù)載的情況，通過在數(shù)據(jù)庫前端部署haproxy或者LVS的方式實(shí)現(xiàn)負(fù)載均衡。但是需要強(qiáng)調(diào)的是，目前這類型集群在多點(diǎn)寫入的情況很容易產(chǎn)生鎖沖突和更新丟失的情況，一般官方建議開啟單點(diǎn)寫入。也就是說，一般也只能實(shí)現(xiàn)單節(jié)點(diǎn)承載寫操作，剩余節(jié)點(diǎn)均衡負(fù)載讀操作。

   2、mysql一主多從架構(gòu)：由于主庫必須單獨(dú)承載寫操作，故均衡負(fù)載只是針對于讀操作。也是通過在數(shù)據(jù)庫前端部署haproxy或者LVS的方式實(shí)現(xiàn)讀操作負(fù)載均衡。

2. 緩存

   一般采用內(nèi)存數(shù)據(jù)庫如Redis、memcached同mysql結(jié)合，將熱點(diǎn)數(shù)據(jù)放在內(nèi)存數(shù)據(jù)庫上實(shí)現(xiàn)高并發(fā)?？梢詤⒖疾┛停篽ttp://blog.csdn.net/stubborn_cow/article/details/50586990

3. 分布式優(yōu)化

   分庫分表：

   這里也可以分為2類：

   （1）通過前端應(yīng)用代碼邏輯實(shí)現(xiàn)的方式，實(shí)現(xiàn)表分拆的方式。這樣做對應(yīng)用程序的侵入性比較大，但是數(shù)據(jù)處理邏輯的過程把控在自己手上，有異常可以自主定位。

   （2）通過中間件的方式實(shí)現(xiàn)，目前常用的mycat、cobar實(shí)現(xiàn)數(shù)據(jù)分片。

   讀寫分離：

   一般通過數(shù)據(jù)庫中間件的方式實(shí)現(xiàn)，常用的中間件例如：maxscale、mycat、cobar、altas等