摘要：數(shù)據(jù)庫物理機(jī)采購位，一臺機(jī)器顆。至少顆，緩存越大越好內(nèi)存，個實例。，實例硬盤機(jī)械選，數(shù)量越多越好，轉(zhuǎn)速越高越好性能高并發(fā)普通業(yè)務(wù)線上線下選使用或者設(shè)備，可提升上千倍的效率。

1、數(shù)據(jù)庫物理機(jī)采購

CPU： 64位CPU，一臺機(jī)器2-16顆CPU。至少2-4顆，L2（緩存）越大越好

內(nèi)存： 96-128G，MySQL 3-4個實例。32-64G，1-2實例

硬盤：機(jī)械：選SAS，數(shù)量越多越好，轉(zhuǎn)速越高越好15k

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

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

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

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

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

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

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

提示：

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

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

2、企業(yè)案例：

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

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

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

(1)服務(wù)器BIOS調(diào)整：

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

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

b.打開CIE和C States等選項，目的也是為了提升CPU效率

c. Memory Frequency（內(nèi)存頻率）選擇Maximum Performance（最佳性能）

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

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

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

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

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

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

1.操作系統(tǒng)及MySQL實例選擇

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

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

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

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

5.避免使用LVM邏輯卷

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

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

(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)采用Linux I/O scheduler算法deadline
deadline調(diào)度參數(shù)
對于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)度算法

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

(4)mount掛載文件系統(tǒng)
增加：async,noatime,nodiratime,nobarrier等

noatime

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

async

寫入時數(shù)據(jù)會先寫到內(nèi)存緩沖區(qū),只到硬盤有空檔才會寫入磁盤,這樣可以提升寫入效率！風(fēng)險為若服務(wù)器宕機(jī)或不正常，會損失緩沖區(qū)中未寫入磁盤的數(shù)據(jù) 解決辦法：服務(wù)器主板電池或加UPS不間斷電源

nodiratime

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

nobarrier

不使用raid卡上電池

(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)重等待

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

net.ipv4.tcp_tw_recyle=1

net.ipv4.tcp_tw_reuse=1

net.ipv4.tcp_keepalived_time=600

net.ipv4.tcp_max_syn_backlog = 16384

net,ipv4.tcp_synack_retries = 1

net.ipv4.tcp_sync_retries = 1

net.ipv4.ip_local_prot_range = 4500 65535

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

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

net.ipv4.tcp_rmem=4096 87380 16777216

net.ipv4.tcp_wmem=4096 65536 16777216

net.ipv4.tcp_mem = 94500000 915000000 927000000

net.core.netdev_max_backlog=3000

5.其他優(yōu)化

net.ipv4.tcp_timestamps = 0

net.ipv4.tcp_max_orphans = 3276800

net.ipv4.tcp_max_tw_buckets = 360000

提示：面試的時候說框架，然后說一兩個小的優(yōu)化參數(shù)即可
更多內(nèi)核可以參考“跟老男孩學(xué)運維書的第三章”以及我們的博客，近期將會更新

3
MySQL數(shù)據(jù)庫層面優(yōu)化

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

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

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

強烈推薦采用innodb引擎，default-storage-engine=Innodb

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

根據(jù)實際需要設(shè)置inno_flush_log_at_trx_commit，sync_binlog的值。

如果要需要數(shù)據(jù)不能丟失，那么兩個都設(shè)為1.如果允許丟失大一點數(shù)據(jù)，

則可分別設(shè)為2和0，在slave上可設(shè)為0

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

設(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%以上的場景；

不要將innodb_log_file_size參數(shù)設(shè)置太大，這樣可以更快同時又更多的磁盤空間，

丟掉多的日志通常是好的，在數(shù)據(jù)庫崩潰后可以降低恢復(fù)數(shù)據(jù)庫的事件

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

根據(jù)業(yè)務(wù)實際需要，適當(dāng)調(diào)整max_connection（最大連接數(shù)max_connection_error

（最大錯誤數(shù)，建議設(shè)置為10萬以上，而open_files_limit、innodb_open_files、

table_open_cache、table_definition_cache這幾個參數(shù)則可設(shè)為約10倍于

max_connection的大小；）不要設(shè)置太大，會將數(shù)據(jù)庫撐爆

tmp_table_szie、max_heap_table_size、sort_buffer_size、

join_buffer_size、read_buffer_size、read_rnd_buffer_size等都是

每個連接session分配的，因此不能設(shè)置過大

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

更多內(nèi)核參數(shù)：my-innodb-heavy-4G.cnf 配置文件參數(shù)介紹

MySQL工具mysqlreport 我們可以使用工具來分析MySQL的性能
如何才能做到網(wǎng)站高并發(fā)訪問? 生產(chǎn)環(huán)境中對于防范DDOS攻擊的討論
面試可能會問到DOOS 攻擊防護(hù)

關(guān)于庫表的設(shè)計規(guī)范

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

固定字符串的列盡可能多用定長char，少用varchar

存儲可變長度的字符串使用VARCHAR而不是CAHR---節(jié)省空間，因為固定

長度的CHAR，而VARCHAR長度不固定（UTF8不愁此影響）

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

字段長度滿足需求前提下，盡可能選擇長度小的

字段屬性盡量都加NOT NULL約束

對于某些文本字段，例如“省份”或者“性別”我們可以將他們定義為ENUM類型盡可能不使用TEXT/BLOB類型，確實需要的話，建議拆分到子表中，不要和主表

放在一起，避免SELECT *的時候讀性能太差。

讀取數(shù)據(jù)時，只選取所需要的列，不要每次都SELECT * 避免產(chǎn)生嚴(yán)重的隨機(jī)讀

問題，尤其是讀到一些TEXT/BLOB類型，確實需要的話，建議拆分到子表中，不要

和主表放在一起，避免SELECT *的時候讀性能太差

對一個VARCHAR（N）列創(chuàng)建索引時，通常取其50%（甚至更?。┳笥议L度創(chuàng)建前綴

索引就足以滿足80%以上的查詢需求了，沒必要創(chuàng)建整列的全長度索引。

多用符合索引，少用多個獨立索引，尤其是一些基礎(chǔ)（Cardinality）太小

（如果說：該列的唯一值總數(shù)少于255）的列就不要創(chuàng)建獨立索引了。

4
SQL語句的優(yōu)化

索引優(yōu)化
1)白名單機(jī)制一百度，項目開發(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點定時分析慢查詢，發(fā)到核心開發(fā)，DBA分析，及高級運維，CTO的郵箱里
DBA分析給出優(yōu)化建議-->核心開發(fā)確認(rèn)更新-->DBA線上操作處理
4)定期使用pt-duplicate-key-checker檢查并刪除重復(fù)的索引
定期使用pt-index-usage工具檢查并刪除使用頻率很低的索引
5)使用pt-online-schema-change來完成大表的ONLINE DDL需求
6)有時候MySQL會使用錯誤的索引，對于這種情況使用USE INDEX
7)使用explain及set profile優(yōu)化SQL語句

大的復(fù)雜的SQL語句拆分成多個小的SQL語句
子查詢，JOIN連表查詢，某個表4000萬條記錄
數(shù)據(jù)庫是存儲數(shù)據(jù)的地方，但不是計算數(shù)據(jù)的地方
對數(shù)據(jù)計算，應(yīng)用類處理，都要拿到前端應(yīng)用解決。禁止在數(shù)據(jù)庫上處理
搜索功能，like ‘%oldboy%’ 一般不要用MySQL數(shù)據(jù)庫
使用連接(JOIN)來代替子查詢（Sub_Queries）
避免在整個表上使用cout(*)，它可能鎖住整張表
多表聯(lián)接查詢時，關(guān)聯(lián)字段類型盡量一致，并且都要有索引。
在WHERE子句中使用UNION代替子查詢
多表連接查詢時，把結(jié)果集小的表（注意，這里是指過濾后的結(jié)果集，不一樣是全表數(shù)據(jù)量小的）作為驅(qū)動表

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

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

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

2.主從復(fù)制一主五從，采用mixed模式，盡量不要跨機(jī)房同步（進(jìn)程遠(yuǎn)程讀本地寫）

3.定期使用pt-table-checksum、pt-table-sync來檢查并修復(fù)mysql主從復(fù)制的數(shù)據(jù)差異

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

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

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

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

7.動態(tài)的數(shù)據(jù)庫靜態(tài)化，整個文件靜態(tài)化，頁面片段靜態(tài)化

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

9.單表超過800萬，拆庫拆表。人工拆表拆庫（登錄、商品、訂單）

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

6
MySQL 流程、制度控制優(yōu)化

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

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

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

c.客戶端管理，phpmyadmin

MySQL基礎(chǔ)安全

1.啟動程序700，屬主和用戶組為MySQL

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

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

4.登錄時盡量不要在命令行暴露密碼，備份腳本中如果有密碼，給設(shè)置700，

屬主和密碼組為mysql或root

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

6.初始刪除無用的用戶，只保留

| root | 127.0.0.1 |

| root | localhost |

7.不要一個用戶管理所有的庫，盡量專庫專用戶

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

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

10.phpmyadmin安全

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

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