數據庫層面：應用系統層面優化SQL優化

SQL優化一般通過分析慢查詢日志來抓取長事務高消耗的sql，通過結合具體業務，對sql邏輯進行分析and精簡，or重寫sql。通過配置slow_query_log=1和log_queries_not_using_indexes=1啟動慢查詢日志記錄和記錄下沒有使用索引的查詢,后者會讓慢查詢日志文件很快膨脹，需要定時對文件進行切割。分析慢日志的工具一般使用pt工具包的pt-query-digest或者mysql自帶的mysqldumpslow，個人比較傾向用pt,分析出來的內容比較詳細。需要注意pt-query-digest其實是一個perl腳本，如果慢日志文件較大(幾G多)，需要大量消耗CPU資源。建議在業務低峰時候調度該工具。

索引優化

索引是數據庫最為常見的對象。基本上90%的sql性能問題都是沒有建索引or低效索引導致的。所以根據實際業務場景建合適的索引，能夠使得sql優化事半功倍。

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

創建索引的一些注意事項：

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

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

(3)避免在where子句中對字段進行表達式操作或者函數操作

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

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

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

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

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

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

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

(11)多表關聯查詢的時候，必須保證關聯的字段有索引。而且字段的類型必須一致，避免由于隱式轉換導致索引失效。

庫表優化

舉個例子：業務前期不注重表設計，導致日志、報文、圖片這類數據都通過表的方式存儲在數據庫。而這類數據一般是通過text/blob等類型的字段存儲，極易使得表容量暴增，而且很難去優化這類表的查詢sql。需要做好庫表設計，對圖片、報文這類數據，改為通過mongodb等NOSQL數據庫進行存儲。或者將表的部分大字段進行拆分，單獨出來一個表，通過冗余部分字段，實現表與表之間的數據關聯(不建議通過mysql的外鍵約束實現關聯，因為在高并發的情況下，會出現大量行鎖影響數據庫性能，強烈建議通過應用程序實現數據關聯。)

采用統一的字符集和校驗集，使用innodb引擎，表設計中采用與業務無關的自增ID列作為主鍵，減少存儲過程and自定義函數，盡量不用text/blob這類字段類型。

表設計規范

請參考附件?阿里巴巴Java開發手冊 和?58到家數據庫30條軍規解讀 (https://www.oschina.net/question/54100_2231325)

數據庫對象優化

內存配置優化

innodb緩沖池設置：innodb_buffer_pool_size，一般為整機內存的70%~80%

緩沖池臟頁占比：innodb_max_dirty_pages_pct，默認為75%，建議按照業務場景進行設置。

強烈建議關閉query cache。通過配置文件設置query_cache_size = 0、query_cache_type = 0即可。

redo log緩沖區設置：innodb_log_buffer_size，如果沒大事務，控制在8M-16M即可，生產環境目前配置到64M。

IO配置優化

sync_binlog:

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

sync_binlog=n，當每進行n次事務提交之后，MySQL將進行一次fsync之類的磁盤同步指令來將binlog_cache中的數據強制寫入磁盤。

sync_relay_log:

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

innodb_flush_log_at_trx_commit:

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

sync_master_info:

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

sync_relay_log_info:

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

master_info_repository:

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

relay_log_info_repository:

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

innodb_io_capacity:默認為200，如果是SSD盤，建議調整到5000

高并發設置

擴大文件描述符：

1、動態修改,重啟失效,只能使用root,并且當前session有效：ulimit -n 65535

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

*?soft?nproc?65535

*?hard?nofile?65535

*?hard?nproc?65535

主機層面：

1、CPU

2、內存

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

(2)盡量配置高內存。這種mysql可以充分利用內存資源緩存熱塊數據，避免由于內存不足導致臟數據不斷地刷盤從而產生IO瓶頸，也可以避免熱塊數據被擠出緩存區的情況發生。

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

3、磁盤IO

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

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

4、網絡優化

集群內機器最好部署在同一內網or專線直連的網絡環境，以保證低延遲，高吞吐的網絡環境。避免由于網絡問題導致的集群內腦裂or主從復制異常的情況。

操作系統層面：

1、文件系統

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

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

2、IO調度算法

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

CFQ:CFQ算法的全寫為Completely Fair Queuing。該算法的特點是按照IO請求的地址進行排序，而不是按照先來后到的順序來進行響應。

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

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

系統架構層面優化負載均衡

這里可以分為兩類：

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

2、mysql一主多從架構：由于主庫必須單獨承載寫操作，故均衡負載只是針對于讀操作。也是通過在數據庫前端部署haproxy或者LVS的方式實現讀操作負載均衡。

緩存

一般采用內存數據庫如Redis、memcached同mysql結合，將熱點數據放在內存數據庫上實現高并發。可以參考博客：http://blog.csdn.net/stubborn_cow/article/details/50586990

分布式優化

分庫分表：

這里也可以分為2類：

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

(2)通過中間件的方式實現，目前常用的mycat、cobar實現數據分片。

讀寫分離：

一般通過數據庫中間件的方式實現，常用的中間件例如：maxscale、mycat、cobar、altas等