MySQL數(shù)據(jù)庫(kù)優(yōu)化

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

1.1 數(shù)據(jù)庫(kù)物理機(jī)采購(gòu)（*****）

• CPU（運(yùn)算）：64位CPU，一臺(tái)機(jī)器2-16顆CPU，至少2-4顆，L2越大越好。

  創(chuàng)新互聯(lián)長(zhǎng)期為上千多家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開(kāi)放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為老河口企業(yè)提供專業(yè)的網(wǎng)站建設(shè)、成都做網(wǎng)站，老河口網(wǎng)站改版等技術(shù)服務(wù)。擁有10多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開(kāi)發(fā)。

• 內(nèi)存：96G-256G（百度），3-4個(gè)實(shí)例。32-64G，跑1-2個(gè)實(shí)例（新浪）。

• disk(磁盤IO)：機(jī)械盤：選SAS，數(shù)量越多越好。

 

性能：SSD（高并發(fā)） > SAS（普通業(yè)務(wù)線上） >SATA（線下）

選SSD：使用SSD或者PCIe SSD設(shè)備，可提升上千倍的IOPS效率。

隨機(jī)IO：SAS單盤能力300IOPS SSD隨機(jī)IO：?jiǎn)伪P能力可達(dá)35000IOPS Flashcache HBA卡

 

raid磁盤陣列： 4快盤：RAID0>RAID1(推薦)>RAID5(少用)>RAID1

主庫(kù)選擇raid10，從庫(kù)可選raid5/raid0/raid10，從庫(kù)配置等于或大于主庫(kù)

網(wǎng)卡：使用多塊網(wǎng)卡bond，以及buffer，tcp優(yōu)化

千兆網(wǎng)卡及千兆、萬(wàn)兆交換機(jī)

提示：

數(shù)據(jù)庫(kù)屬于IO密集型服務(wù)，硬件盡量不要使用虛擬化。

Slave硬件要等于或大于Master的性能

 

 

1.2 企業(yè)案例：

百度：某部門IBM服務(wù)器為48核CPU，內(nèi)存96GB，一臺(tái)服務(wù)器跑3~4個(gè)實(shí)例：

sina：服務(wù)器是DELL R510居多，CPU是E5210，48GB內(nèi)存，硬盤12*300G SAS，做RAID10

 

1.3 服務(wù)器硬件配置調(diào)整

(1)服務(wù)器BIOS調(diào)整： （buffer和緩存調(diào)整大點(diǎn)。）

　

提升CPU效率參考設(shè)置：

a.打開(kāi)Perfirmance Per Watt Optimeized（DAPC）模式，發(fā)揮CPU最大性能，數(shù)據(jù)庫(kù)通常需要高運(yùn)算量

b.打開(kāi)CIE和C States等選項(xiàng)，目的也是為了提升CPU效率

c. MemoryFrequency（內(nèi)存頻率）選擇MaximumPerformance（最佳性能）

d.內(nèi)存設(shè)置菜單中，啟動(dòng)Node Interleaving，避免NUMA問(wèn)題

(2)陣列卡調(diào)整：

a.購(gòu)置陣列卡同時(shí)配備CACHE及BBU模塊(機(jī)械盤)

b.設(shè)置陣列寫策略為WEB，甚至OFRCE WB （對(duì)數(shù)據(jù)安全要求高）（wb指raid卡的寫策略：會(huì)寫（write back））

c.嚴(yán)禁使用WT策略，并且關(guān)閉陣列預(yù)讀策略

 

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

2.1 操作系統(tǒng)及MySQL實(shí)例選擇

1.一定要選擇x86_64系統(tǒng)，推薦使用CentOS6.8 linux，關(guān)閉NUMA特性

2.將操作系統(tǒng)和數(shù)據(jù)分開(kāi)，不僅僅是邏輯上，還包括物理上

3.避免使用Swap交換分區(qū)

4.避免使用軟件磁盤陣列

5.避免使用LVM邏輯卷

6.刪除服務(wù)器上未使用的安裝包和守護(hù)進(jìn)程

 

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

2.2.1 調(diào)整磁盤Cache mode

啟用WCE=1(Write Cache Enable),RCD=0(Read Cache Disable)模式

命令：sdparm -s WCE=1,RCD=0 -S /dev/sdb

2.2.2 采用Linux I/O scheduler算法deadline

deadline調(diào)度參數(shù)

對(duì)于Centos Linux建議 read_expire = 1/2 write_expire

echo 500 >/sys/block/sdb/queue/iosched/read_expire

echo 1000 >/sys/block/sdb/queue/iosched/write_expire

Linux I/O調(diào)度方法 Linux deadline io 調(diào)度算法。

 

2.2.3 采用xfs文件系統(tǒng)

　業(yè)務(wù)量不是很大也可采用ext4，業(yè)務(wù)量很大，推薦使用xfs：調(diào)整XFS文件系統(tǒng)日志和緩沖變量

XFS高性能設(shè)置。

2.2.4 mount掛載文件系統(tǒng)

　增加：async,noatime,nodiratime,nobarrier等

noatime

訪問(wèn)文件時(shí)不更新inode的時(shí)間戳，高并發(fā)環(huán)境下，推薦顯示應(yīng)用該選項(xiàng)，可以提高系統(tǒng)I/O性能

async

寫入時(shí)數(shù)據(jù)會(huì)先寫到內(nèi)存緩沖區(qū),只到硬盤有空檔才會(huì)寫入磁盤,這樣可以提升寫入效率！風(fēng)險(xiǎn)為若服務(wù)器宕機(jī)或不正常，會(huì)損失緩沖區(qū)中未寫入磁盤的數(shù)據(jù) 

解決辦法：服務(wù)器主板電池或加UPS不間斷電源

nodiratime

不更新系統(tǒng)上的directory inode時(shí)間戳，高并發(fā)環(huán)境，推薦顯示該應(yīng)用，可以提高系統(tǒng)I/O性能

nobarrier

不使用raid卡上電池

2.2.5 Linux 內(nèi)核參數(shù)優(yōu)化

1.將vm，swappiness設(shè)置為0-10

2.將vm，dirty_background_ratio設(shè)置為5-10，將vm，dirty_ratio設(shè)置為它的兩倍左右，以確保能持續(xù)將臟數(shù)據(jù)刷新到磁盤，避免瞬間I/O寫，產(chǎn)生嚴(yán)重等待

2.3 優(yōu)化TCP協(xié)議棧

 

#減少TIME_WAIT，提高TCP效率

net.ipv4.tcp_tw_recyle=1

net.ipv4.tcp_tw_reuse=1

#減少處于FIN-WAIT-2連接狀態(tài)的時(shí)間，使系統(tǒng)可以處理更多的連接

net.ipv4.tcp_fin_timeout=2

#減少TCP KeepAlived連接偵測(cè)的時(shí)間，使系統(tǒng)可以處理更多的連接。

net.ipv4.tcp_keepalived_time=600

#提高系統(tǒng)支持的最大SYN半連接數(shù)（默認(rèn)1024）

net.ipv4.tcp_max_syn_backlog = 16384

#減少系統(tǒng)SYN連接重試次數(shù)(默認(rèn)5)

net,ipv4.tcp_synack_retries = 1

net.ipv4.tcp_sync_retries = 1

#在內(nèi)核放棄建立的連接之前發(fā)送SYN包的數(shù)量

net.ipv4.ip_local_prot_range = 450065535

#允許系統(tǒng)打開(kāi)的端口范圍

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

#優(yōu)化系統(tǒng)套接字緩沖區(qū)

 

#Increase TCP max buffer size

net.core.rmem_max=16777216 #最大socket讀buffer

net.core.wmem_max=16777216 #最大socket寫buffer

net.core.wmem_default = 8388608 #該文件指定了接收套接字緩沖區(qū)大小的缺省值(以字節(jié)為單位)

net.core.rmem_default = 8388608

#優(yōu)化TCP接收/發(fā)送緩沖區(qū)

 

# Increase Linux autotuning TCPbuffer limits

net.ipv4.tcp_rmem=4096 87380 16777216

net.ipv4.tcp_wmem=4096 65536 16777216

net.ipv4.tcp_mem = 94500000 915000000927000000

#優(yōu)化網(wǎng)絡(luò)設(shè)備接收隊(duì)列

 

net.core.netdev_max_backlog=3000

2.5 其他優(yōu)化

 

net.ipv4.tcp_timestamps = 0

net.ipv4.tcp_max_orphans = 3276800

net.ipv4.tcp_max_tw_buckets = 360000

 

第3章 MySQL數(shù)據(jù)庫(kù)層面優(yōu)化（*****）

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

此優(yōu)化主要針對(duì)innodb引擎

如果采用MyISAM引擎，需要key_buffer_size加大。

       key_buffer_size = 256M  

       #指定用于索引的緩沖區(qū)大小，增加它可得到更好的索引處理性能。對(duì)于內(nèi)存在4GB左右的服務(wù)器來(lái)        說(shuō)，該參數(shù)可設(shè)置為256MB或384MB。

強(qiáng)烈推薦采用innodb引擎，default-storage-engine=Innodb

調(diào)整innodb_buffer_pool_size大小，考慮設(shè)置為物理內(nèi)存的50%-60%左右。

        innodb_buffer_pool_size = 64M  

         #InnoDB使用一個(gè)緩沖池來(lái)保存索引和原始數(shù)據(jù)，設(shè)置越大，在存取表里面數(shù)據(jù)時(shí)所需要的磁盤I/O越少。強(qiáng)烈建議不要武斷地將InnoDB的Buffer Pool值配置為物理內(nèi)存的50%~80%，應(yīng)根據(jù)具體環(huán)境而定。

根據(jù)實(shí)際需要設(shè)置inno_flush_log_at_trx_commit，sync_binlog的值。如果要需要數(shù)據(jù)不能丟失，那么兩個(gè)都設(shè)為1.如果允許丟失大一點(diǎn)數(shù)據(jù)，則可分別設(shè)為2和0，在slave上可設(shè)為0

                           

設(shè)置innodb_file_per_table = 1，使用獨(dú)立表空間

設(shè)置innodb_data_file_path = ibdata1:1G:autoextend,不要使用默認(rèn)的10%

設(shè)置innodb_log_file_size=256M,設(shè)置innodb_log_files_in_group=2，基本可滿足90%以上的場(chǎng)景；

不要將innodb_log_file_size參數(shù)設(shè)置太大，這樣可以更快同時(shí)又更多的磁盤空間，丟掉多的日志通常是好的，在數(shù)據(jù)庫(kù)崩潰后可以降低恢復(fù)數(shù)據(jù)庫(kù)的事件

設(shè)置long_query_time = 1記錄那些執(zhí)行較慢的SQL，用于后續(xù)的分析排查；

根據(jù)業(yè)務(wù)實(shí)際需要，適當(dāng)調(diào)整max_connection（最大連接數(shù)）max_connection_error（最大錯(cuò)誤數(shù)，建議設(shè)置為10萬(wàn)以上，而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache這幾個(gè)參數(shù)則可設(shè)為約10倍于max_connection的大??；）不要設(shè)置太大，會(huì)將數(shù)據(jù)庫(kù)撐爆

建議關(guān)閉query cache功能或降低設(shè)置不要超過(guò)512M

query_cache_size = 64M 

#指定MySQL查詢緩沖區(qū)的大小。可以通過(guò)在MySQL控制臺(tái)觀察，如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況;如果Qcache_hits的值非常大，則表明查詢緩沖使用得非常頻繁。另外如果改值較小反而會(huì)影響效率，那么可以考慮不用查詢緩沖。對(duì)于Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多。

tmp_table_size = 64M  

#設(shè)置內(nèi)存臨時(shí)表最大值。如果超過(guò)該值，則會(huì)將臨時(shí)表寫入磁盤，其范圍1KB到4GB。

max_heap_table_size = 64M 

 #獨(dú)立的內(nèi)存表所允許的最大容量。

table_cache = 614 

#給經(jīng)常訪問(wèn)的表分配的內(nèi)存，物理內(nèi)存越大，設(shè)置就越大。調(diào)大這個(gè)值，一般情況下可以降低磁盤IO，但相應(yīng)的會(huì)占用更多的內(nèi)存,這里設(shè)置為614。

3.2 關(guān)于庫(kù)表的設(shè)計(jì)規(guī)范

1.推薦utf-8字符集，雖然有人說(shuō)談沒(méi)有l(wèi)atin1快

2.固定字符串的列盡可能多用定長(zhǎng)char，少用varchar

   存儲(chǔ)可變長(zhǎng)度的字符串使用VARCHAR而不是CAHR---節(jié)省空間，因?yàn)楣潭ㄩL(zhǎng)度的CHAR，而VARCHAR長(zhǎng)度不固定（UTF8不愁此影響）

3.所有的InnoDB表都設(shè)計(jì)一個(gè)無(wú)業(yè)務(wù)的用途的自增列做主鍵

4.字段長(zhǎng)度滿足需求前提下，盡可能選擇長(zhǎng)度小的

5.字段屬性盡量都加NOT NULL約束（空的字段不能走索引，查詢速度慢）

   對(duì)于某些文本字段，例如“省份”或者“性別”我們可以將他們定義為ENUM類型

6.盡可能不使用TEXT/BLOB類型，確實(shí)需要的話，建議拆分到子表中，不要和主表放在一起，避免SELECT*的時(shí)候讀性能太差。

7.讀取數(shù)據(jù)時(shí)，只選取所需要的列，不要每次都SELECT * 避免產(chǎn)生嚴(yán)重的隨機(jī)讀問(wèn)題，尤其是讀到一些TEXT/BLOB類型，確實(shí)需要的話，建議拆分到子表中，不要和主表放在一起，避免SELECT*的時(shí)候讀性能太差

8.對(duì)一個(gè)VARCHAR（N）列創(chuàng)建索引時(shí)，通常取其50%（甚至更?。┳笥议L(zhǎng)度創(chuàng)建前綴索引就足以滿足80%以上的查詢需求了，沒(méi)必要?jiǎng)?chuàng)建整列的全長(zhǎng)度索引。

9.多用符合索引，少用多個(gè)獨(dú)立索引，尤其是一些基礎(chǔ)（Cardinality）太?。ㄈ绻f(shuō)：該列的唯一值總數(shù)少于255）的列就不要?jiǎng)?chuàng)建獨(dú)立索引了。

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

3.3.1 索引優(yōu)化

1）白名單機(jī)制一百度，項(xiàng)目開(kāi)發(fā)，DBA參與，減少上線后的慢SQL數(shù)據(jù)

抓出慢SQL,配置my.cnf

long_query_time = 2

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

log_queries_not_using_indexs

按天輪詢：slow-log.log

2）慢查詢的日志分析工具——mysqlsla或pt-query-digest（推薦）

 

pt-quey-diges,mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfileter

3)每天晚上0點(diǎn)定時(shí)分析慢查詢，發(fā)到核心開(kāi)發(fā)，DBA分析，及高級(jí)運(yùn)維，CTO的郵箱里

DBA分析給出優(yōu)化建議-->核心開(kāi)發(fā)確認(rèn)更新-->DBA線上操作處理

4)定期使用pt-duplicate-key-checker檢查并刪除重復(fù)的索引

定期使用pt-index-usage工具檢查并刪除使用頻率很低的索引

5)使用pt-online-schema-change來(lái)完成大表的ONLINE DDL需求

6)有時(shí)候MySQL會(huì)使用錯(cuò)誤的索引，對(duì)于這種情況使用USE INDEX

7)使用explain及set profile優(yōu)化SQL語(yǔ)句

 

網(wǎng)站打開(kāi)慢之慢查詢

3.3.2 大的復(fù)雜的SQL語(yǔ)句拆分成多個(gè)小的SQL語(yǔ)句

子查詢，JOIN連表查詢，某個(gè)表4000萬(wàn)條記錄

3.3.3 數(shù)據(jù)庫(kù)是存儲(chǔ)數(shù)據(jù)的地方，但不是計(jì)算數(shù)據(jù)的地方

對(duì)數(shù)據(jù)計(jì)算，應(yīng)用類處理，都要拿到前端應(yīng)用解決。禁止在數(shù)據(jù)庫(kù)上處理

3.3.4 搜索功能，like ‘%oldboy%’ 一般不要用MySQL數(shù)據(jù)庫(kù)

使用連接(JOIN)來(lái)代替子查詢（Sub_Queries）

避免在整個(gè)表上使用cout(*)，它可能鎖住整張表

多表聯(lián)接查詢時(shí)，關(guān)聯(lián)字段類型盡量一致，并且都要有索引。

在WHERE子句中使用UNION代替子查詢

多表連接查詢時(shí)，把結(jié)果集小的表（注意，這里是指過(guò)濾后的結(jié)果集，不一樣是全表數(shù)據(jù)量小的）作為驅(qū)動(dòng)表

 

• 爬蟲獲取數(shù)據(jù)的過(guò)程

 

第4章 網(wǎng)站集成架構(gòu)優(yōu)化（*****）

網(wǎng)站集群架構(gòu)上的優(yōu)化

1.服務(wù)器上跑多實(shí)例，2-4個(gè)（具體需要看服務(wù)器的硬件信息）

2.主從復(fù)制一主五從，采用mixed模式（混合或行模式），盡量不要跨機(jī)房同步（進(jìn)程遠(yuǎn)程讀本地寫），（數(shù)據(jù)要一致，拉光纖，沒(méi)有網(wǎng)絡(luò)延遲）

3.定期使用pt-table-checksum、pt-table-sync來(lái)檢查并修復(fù)mysql主從復(fù)制的數(shù)據(jù)差異（重構(gòu)）

4.業(yè)務(wù)拆分：搜索功能，like '%oldboy% ' 一般不要用MySQL數(shù)據(jù)庫(kù)

5.業(yè)務(wù)拆分：某些業(yè)務(wù)應(yīng)用使用NOSQL持久化存儲(chǔ)，例如：memcached、redis、ttserver

 例如粉絲關(guān)注，好友關(guān)系等

6.數(shù)據(jù)庫(kù)前端必須要加cache，例如：memcached，用戶登錄，商品查詢

7.動(dòng)態(tài)的數(shù)據(jù)庫(kù)靜態(tài)化，整個(gè)文件靜態(tài)化，頁(yè)面片段靜態(tài)化

8.數(shù)據(jù)庫(kù)集群與讀寫分離。一主多從，通過(guò)程序或dbproxy進(jìn)行集群讀寫分離

9.單表超過(guò)800萬(wàn)，拆庫(kù)拆表。人工拆表拆庫(kù)（登錄、商品、訂單）

10.百度、阿里國(guó)內(nèi)前×××司，會(huì)選擇從庫(kù)進(jìn)行備份，對(duì)數(shù)據(jù)庫(kù)進(jìn)行分庫(kù)分表

 

 

第5章 MySQL數(shù)據(jù)庫(kù)管理流程（*****）

任何一次人為數(shù)據(jù)庫(kù)記錄的更新，都要走一個(gè)流程：

a.人的流程：開(kāi)發(fā)-->核心開(kāi)發(fā)-->運(yùn)維或DBA

b.測(cè)試流程：內(nèi)網(wǎng)測(cè)試-->IDC測(cè)試-->線上執(zhí)行

c.客戶端管理，phpmyadmin

 

第6章 MySQL數(shù)據(jù)庫(kù)安全優(yōu)化（*****）

6.1 MySQL基礎(chǔ)安全

 

1.啟動(dòng)程序700，屬主和用戶組為MySQL。

2.為MySQL超級(jí)用戶root設(shè)置密碼。

3.如果要求嚴(yán)格可以刪除root用戶，創(chuàng)建其他管理用戶，例如admin。

4.登錄時(shí)盡量不要在命令行暴露密碼，備份腳本中如果有密碼，給設(shè)置700，屬主和密碼組為mysql或root。

5.刪除默認(rèn)存在的test庫(kù)。

6.初始刪除無(wú)用的用戶，只保留。

| root | 127.0.0.1 |

| root | localhost |

7.不要一個(gè)用戶管理所有的庫(kù)，盡量專庫(kù)專用戶(少量庫(kù))

8.清理mysql操作日志文件~/.mysql_history（權(quán)限600，可以不刪）

9.禁止開(kāi)發(fā)獲得到web連接的密碼，禁止開(kāi)發(fā)連接操作生產(chǎn)對(duì)外的庫(kù)

10.phpmyadmin安全

11.服務(wù)器禁止設(shè)置外網(wǎng)IP

12.防SQL注入（WEB）php.ini或web開(kāi)發(fā)插件監(jiān)控，waf控制

文章名稱：MySQL數(shù)據(jù)庫(kù)優(yōu)化
分享鏈接：http://bm7419.com/article8/pcsgip.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、網(wǎng)站改版、標(biāo)簽優(yōu)化、網(wǎng)站設(shè)計(jì)公司、電子商務(wù)、定制開(kāi)發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)