高并發(fā)網(wǎng)站集群場(chǎng)景下如何優(yōu)化MySQL數(shù)據(jù)庫(kù)

本篇文章給大家主要講的是關(guān)于高并發(fā)網(wǎng)站集群場(chǎng)景下如何優(yōu)化MySQL數(shù)據(jù)庫(kù)的內(nèi)容，感興趣的話就一起來(lái)看看這篇文章吧，相信看完高并發(fā)網(wǎng)站集群場(chǎng)景下如何優(yōu)化MySQL數(shù)據(jù)庫(kù)對(duì)大家多少有點(diǎn)參考價(jià)值吧。

1、硬件層面優(yōu)化

1.1數(shù)據(jù)庫(kù)物理機(jī)

a.CPU:64位，服務(wù)器2-16個(gè)CPU,2-4顆，L1,L2越大越好；

b.mem（內(nèi)存）：48G-96G-128G-256G(48g 2-3個(gè)實(shí)例；96g 3-4個(gè)實(shí)例 )；

c.disk(磁盤IO)數(shù)據(jù)庫(kù)是IO密集應(yīng)用

  機(jī)械盤：SAS(不要選擇SATA)，300G*12塊，磁盤數(shù)量越多IO越高，SAS 15K轉(zhuǎn)的硬盤。

  SSD:固態(tài)硬盤

測(cè)試對(duì)比：SAS單盤隨機(jī)IO,300IOPS,SSD單盤隨機(jī)IO達(dá)到上萬(wàn)。

d.raid陣列：選硬件RAID（0>10>5>1），選10

e．網(wǎng)卡至少千兆（bond）,萬(wàn)兆交換機(jī)

f.數(shù)據(jù)庫(kù)服務(wù)器盡量不用虛擬化

g.SLAVE服務(wù)器配置最好是大于等于Master

案例：

百度：IBM服務(wù)器，內(nèi)存96-128G，CPU48核，3-4個(gè)實(shí)例

SINA: DELL R510,內(nèi)存48G，磁盤300*12塊，raid10

多實(shí)例：就是一臺(tái)服務(wù)器多個(gè)數(shù)據(jù)庫(kù)，比喻一個(gè)房子多個(gè)臥室。

1.2 硬件調(diào)整：

Bios調(diào)整提高CPU性能

1)    打開DAPC模式，發(fā)揮CPU性能。

2)    啟動(dòng)Node Interleaving,避免NUMA問(wèn)題（NUMA=0）。

3)    關(guān)鍵C1E和State等選項(xiàng)。

陳列卡：

1)    配置CACHE和BBU模塊(機(jī)械盤)

2)    寫策略調(diào)為(wb)

3)    不用wt策略,關(guān)閉陳列預(yù)讀策略

2、軟件層面優(yōu)化

2.1 操作系統(tǒng)層面優(yōu)化

1)    選擇x86_64位系統(tǒng)

2)    將系統(tǒng)盤和數(shù)據(jù)盤分開

3)    盡量避免是用swap

4)    避免使用操作系統(tǒng)軟raid

5)    避免使用LVM

6)    專庫(kù)專用（不要跑LNMP，TOMCAT）

7)    調(diào)整Cache mode                                                    

啟動(dòng)wce=1(Write Cache Enable)  RCD=0(Read Cache Disable)

8)    調(diào)度算法默認(rèn)cfq,noop,deadline。針對(duì)deadline可以調(diào)參（內(nèi)核參數(shù)）

9)    Centos6.8默認(rèn)ext4可以作為數(shù)據(jù)為的文件系統(tǒng)，訪問(wèn)量大的話，XFS就更好。Centos7默認(rèn)也選了XFS，調(diào)整XFS日志，緩沖參數(shù)。

10)             mount參數(shù)很重要，async,noatime,nodirname,nobarrier等。

2.2 文件系統(tǒng)層優(yōu)化

2.3 內(nèi)核層面優(yōu)化

1)    vm.swappiness設(shè)置0，或者0-5,讓數(shù)據(jù)庫(kù)盡量不使用swap.

2)    Vm.dirty_background_ration設(shè)置5-10,vm.dirty_ration設(shè)置前面的2倍。持續(xù)將系統(tǒng)臟數(shù)據(jù)刷到磁盤。

3)    net.ipv4.tcp_tw_recyle=1,net.ipv4.tcp_reuse=1, net.ipv4.tcp_fin_timeout=2,net.ipv4.tcp_keepalived_time=600減少time_wait

4)    內(nèi)核優(yōu)化，參考老男孩博客；

3、MySQL層面優(yōu)化

3.1 my.conf參數(shù)的優(yōu)化

1)    如果采用myisam引擎，key_buffer_szie加大。盡量采用innnodb.

2)    推薦使用innodb,5.5.5以后默認(rèn)就是innodb引擎

3)    innodb_buffer_pool_szie,調(diào)整為內(nèi)存的50%，單實(shí)例。

4)    innodb_flush_log_at_trx_commit,sync_binlog,設(shè)置為1,數(shù)據(jù)可以丟，設(shè)置0，從庫(kù)設(shè)置為0。

5)    使用獨(dú)立表空間。innodb_file_per_table=1

6)    innodb_log_file_size=256M

7)    log_query_time=1 ,超過(guò)1秒的語(yǔ)句記錄LOG。

8)    一些session參數(shù)不要設(shè)置過(guò)大，一個(gè)連接就會(huì)占用參數(shù)設(shè)置的大小。Sort_buffer_size,join_buffer_size這類參數(shù)都是session級(jí)別參數(shù)。

9)    查詢緩存參數(shù)要設(shè)置小一些：query_cache_size=64M,要想緩存，前端加mc，redis。

3.2 庫(kù)和表的設(shè)計(jì)優(yōu)化

1)    字符集UTF-8

2)    固定字符串的內(nèi)容，可以選擇char

3)    數(shù)據(jù)庫(kù)都要給一個(gè)自增的主鍵，沒(méi)有什么業(yè)務(wù)用途

4)    字段長(zhǎng)度，在滿足需求前提下，用最短的。Varchar(16)

5)    省份，性別，這類內(nèi)容字段可以設(shè)置ENUM類型，mysql系統(tǒng)表(char ,ENUM)

6)    盡可能不用text/blob,如果使用的話，放到子表里。

7)    針對(duì)字段索引，盡量采用字段的前N個(gè)字符索引，不要整個(gè)字段索引。

8)    多用聯(lián)合索引，前綴特性，少用獨(dú)立索引，性別列不要建立獨(dú)立索引了。

3.3 SQL語(yǔ)句的優(yōu)化

A、索引優(yōu)化

 1）抓出來(lái)慢查詢

  百度：白名單的方法，設(shè)計(jì)程序時(shí)候參與設(shè)計(jì)，程序上線連接數(shù)據(jù)庫(kù)，有個(gè)控制查庫(kù)的東西，請(qǐng)示放我?guī)炖?，才能查詢，?shù)據(jù)庫(kù)沒(méi)有或者減少慢查詢。

   給常給開發(fā)做培訓(xùn)，DB水平更高。

   現(xiàn)在網(wǎng)站慢了，show full processlist;抓慢查詢，連續(xù)執(zhí)行兩下。間隔1-2秒，如果還有，懷疑他是慢查詢。

日常：把慢查詢語(yǔ)句記錄到log。

 My.cnf

 Long_query_time=2

 Log_queryies_not_using_indexes

 Log-slow-queries=/data/3306/slow.log

每天生成slow.log,按天切割slow.log,切割以后用分析軟件分析(mysqlsla,-pt-query-digest)

Mysqldumpslow,myprofi。優(yōu)化的語(yǔ)句不一定是單條占用時(shí)間長(zhǎng)的，頻率高單條不長(zhǎng)，但是總時(shí)間很長(zhǎng)的，這些可能也是優(yōu)化的重點(diǎn)。

運(yùn)維來(lái)講，慢查詢SQL發(fā)給開發(fā)。

Explain測(cè)試語(yǔ)句是否走索引。Set profile深度查看語(yǔ)句執(zhí)行情況。

檢查刪除重復(fù)的索引，pt-duplicate-key-checker,效率很低的索引檢查刪除，pt-index-usage

2)盡量不用子查詢，用join替代

數(shù)據(jù)庫(kù)是存放數(shù)據(jù)的地方，不是計(jì)算數(shù)據(jù)的地方，計(jì)算放在前面Web。搜索功能，like “%daf%”,不用數(shù)據(jù)庫(kù)搜索。

3)語(yǔ)句中盡量去掉in or <>

4、網(wǎng)站集群架構(gòu)上優(yōu)化數(shù)據(jù)庫(kù)

1)    服務(wù)器跑多實(shí)例2-4個(gè)

2)    主從復(fù)制最多9個(gè)，1主5從，采用mixed,不要跨機(jī)房復(fù)制(遠(yuǎn)程寫，本地讀)

3)    業(yè)務(wù)拆分：搜索功能，like “%daf%”,不用數(shù)據(jù)庫(kù)搜索。

搜索軟件：Sphinx,Xapian,Solr

4)    粉絲關(guān)注，好關(guān)系，統(tǒng)計(jì)這類應(yīng)用比較簡(jiǎn)單，不用數(shù)據(jù)，放到redis。

5)    數(shù)據(jù)庫(kù)前端加緩存

6)    動(dòng)態(tài)內(nèi)轉(zhuǎn)靜態(tài)化（數(shù)據(jù)庫(kù)的數(shù)據(jù)，轉(zhuǎn)成html文件，放到存儲(chǔ)上）使用CDN。

7)    數(shù)據(jù)庫(kù)采用讀寫分離，MyCat,atlas,cobar,amoeba,MySQL-proxy

8)    單表超過(guò)800萬(wàn)，拆庫(kù)拆表。自動(dòng)擴(kuò)容，自動(dòng)收縮。

9)    選擇從庫(kù)備份，鎖表，備份時(shí)間很長(zhǎng)，影響數(shù)據(jù)訪問(wèn)。

10)  備份時(shí)采用分表分庫(kù)。

5、流程、制度、安全:50%的故障都是人為造成的。

1)    操作流程：開發(fā)-核心開發(fā)—運(yùn)維或DBA

2)    測(cè)試流程：辦公室測(cè)試-IDC測(cè)試-IDC正式

以上關(guān)于高并發(fā)網(wǎng)站集群場(chǎng)景下如何優(yōu)化MySQL數(shù)據(jù)庫(kù)詳細(xì)內(nèi)容，對(duì)大家有幫助嗎？如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。