深度最佳化LNMP之MySQL

標籤：flush   上電   raid1   efault   模組   img   軟體   odi   而不是   

MySQL資料庫最佳化架構體系

1.硬體層面最佳化 

2.作業系統層面最佳化 

3.MySQL資料庫層面最佳化 

4.MySQL安全最佳化 

5.網站集合群架構上的最佳化 

6.MySQL流程、制度控制最佳化

 

1

硬體層面最佳化

1、資料庫物理機採購

CPU： 64位CPU，一台機器2-16顆CPU。至少2-4顆，L2（緩衝）越大越好 

記憶體： 96-128G，MySQL 3-4個執行個體。32-64G，1-2執行個體 

硬碟：機械：選SAS，數量越多越好，轉速越高越好15k 

效能：SSD（高並發） > SAS（普通業務線上） >SATA（線下） 

選SSD：使用SSD或者PCIe SSD裝置，可提升上千倍的IOPS效率。 

隨機IO：SAS單盤能力300IOPS SSD隨機IO：單盤能力可達35000IOPS Flashcache HBA卡

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

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

網卡：使用多塊網卡bond，以及buffer，tcp最佳化 

千兆網卡及千兆、萬兆交換器 

提示： 

資料庫屬於IO密集型服務，硬體盡量不要使用虛擬化。 

Slave硬體要等於或大於Master的效能

 

2、企業案例：

百度：某部門IBM伺服器為48核CPU，記憶體96GB，一台伺服器跑3~4個執行個體： 

sina：伺服器是DELL R510居多，CPU是E5210，48GB記憶體，硬碟12*300G SAS，做RAID10

 

3、伺服器硬體設定調整

(1)伺服器BIOS調整： 

提升CPU效率參考設定： 

a.開啟Perfirmance Per Watt Optimeized（DAPC）模式，發揮CPU最大效能，資料庫通常需要高運算量 

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

c. Memory Frequency（記憶體頻率）選擇Maximum Performance（最佳效能） 

d.記憶體設定菜單中，啟動Node Interleaving，避免NUMA問題 

(2)陣列卡調整： 

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

b.設定陣列寫策略為WEB，甚至OFRCE WB （對資料安全要求高）（wb指raid卡的寫策略：會寫（write back）） 

c.嚴禁使用WT策略，並且關閉陣列預讀策略

2

作業系統層面最佳化

1.作業系統及MySQL執行個體選擇

1.一定要選擇x86_64系統，推薦使用CentOS6.8 linux，關閉NUMA特性 

2.將作業系統和資料分開，不僅僅是邏輯上，還包括物理上 

3.避免使用Swap交換分區 

4.避免使用軟體磁碟陣列 

5.避免使用LVM邏輯卷 

6.刪除伺服器上未使用的安裝包和守護進程

2.檔案系統層最佳化

(1)調整磁碟Cache mode

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

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

(2)採用Linux I/O scheduler演算法deadline
deadline調度參數 
對於Centos Linux建議 read_expire = 1/2 write_expire

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

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

Linux I/O調度方法 Linux deadline io 調度演算法

(3)採用xfs檔案系統 
業務量不是很大也可採用ext4，業務量很大，推薦使用xfs：調整XFS檔案系統日誌和緩衝變數 
XFS高效能設定 

(4)mount掛載檔案系統 
增加：async,noatime,nodiratime,nobarrier等

noatime

訪問檔案時不更新inode的時間戳記，高並發環境下，推線顯示應用該選項，可以提高系統I/O效能

async

寫入時資料會先寫到記憶體緩衝區,只到硬碟有空檔才會寫入磁碟,這樣可以提升寫入效率！風險為若伺服器宕機或不正常，會損失緩衝區中未寫入磁碟的資料 解決辦法：伺服器主板電池或加UPS不斷電供應系統

nodiratime

不更新系統上的directory inode時間戳記，高並發環境，推薦顯示該應用，可以提高系統I/O效能 
　　 
nobarrier

不使用raid卡上電池 

(5)Linux 核心參數最佳化 
1.將vm，swappiness設定為0-10 
2.將vm，dirty_background_ratio設定為5-10，將vm，dirty_ratio設定為它的兩倍左右，以確保能持續將髒資料重新整理到磁碟，避免瞬間I/O寫，產生嚴重等待

3.最佳化TCP協議棧

1. #減少TIME_WAIT，提高TCP效率

2. net.ipv4.tcp_tw_recyle=1

3. net.ipv4.tcp_tw_reuse=1

4. #減少處於FIN-WAIT-2串連狀態的時間，使系統可以處理更多的串連net.ipv4.tcp_fin_timeout=2

5. #減少TCP KeepAlived串連偵測的時間，使系統可以處理更多的串連。

6. net.ipv4.tcp_keepalived_time=600

7. #提高系統支援的最大SYN半串連數（預設1024）

8. net.ipv4.tcp_max_syn_backlog = 16384

9. #減少系統SYN串連重試次數(預設5)

10. net,ipv4.tcp_synack_retries = 1

11. net.ipv4.tcp_sync_retries = 1

12. #在核心放棄建立的串連之前發送SYN包的數量

13. net.ipv4.ip_local_prot_range = 4500 65535

14. #允許系統開啟的連接埠範圍

4.網路最佳化 
#最佳化系統通訊端緩衝區

1. #Increase TCP max buffer size

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

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

4. net.core.wmem_default = 8388608 #該檔案指定了接收通訊端緩衝區大小的預設

5. 值(以位元組為單位)

6. net.core.rmem_default = 8388608

#最佳化TCP接收/發送緩衝區

1. # Increase Linux autotuning TCP buffer limits

2. net.ipv4.tcp_rmem=4096 87380 16777216

3. net.ipv4.tcp_wmem=4096 65536 16777216

4. net.ipv4.tcp_mem = 94500000 915000000 927000000

#最佳化網路裝置接收隊列

1. net.core.netdev_max_backlog=3000

5.其他最佳化

1. net.ipv4.tcp_timestamps = 0

2. net.ipv4.tcp_max_orphans = 3276800

3. net.ipv4.tcp_max_tw_buckets = 360000

提示：面試的時候說架構，然後說一兩個小的最佳化參數即可 
更多核心可以參考“跟老男孩學營運書的第三章”以及我們的部落格，近期將會更新

3

MySQL資料庫層面最佳化

my.cnf參數最佳化

此最佳化主要針對innodb引擎

1. 如果採用MyISAM引擎，需要key_buffer_size加大。

2. 強烈推薦採用innodb引擎，default-storage-engine=Innodb

3. 調整innodb_buffer_pool_size大小，考慮設定為實體記憶體的50%-60%左右

4. 根據實際需要設定inno_flush_log_at_trx_commit，sync_binlog的值。

5. 如果要需要資料不能丟失，那麼兩個都設為1.如果允許丟失大一點資料，

6. 則可分別設為2和0，在slave上可設為0

7. 設定innodb_file_per_table = 1，使用獨立資料表空間

8. 設定innodb_data_file_path = ibdata1:1G:autoextend,不要使用預設的10%

9. 設定innodb_log_file_size=256M,設定innodb_log_files_in_group=2，基本可

10. 滿足90%以上的情境；

11. 不要將innodb_log_file_size參數設定太大，這樣可以更快同時又更多的磁碟空間，

12. 丟掉多的日誌通常是好的，在資料庫崩潰後可以降低恢複資料庫的事件

13. 設定long_query_time = 1記錄那些執行較慢的SQL，用於後續的分析排查；

14. 根據業務實際需要，適當調整max_connection（最大串連數max_connection_error

15. （最大錯誤數，建議設定為10萬以上，而open_files_limit、innodb_open_files、

16. table_open_cache、table_definition_cache這幾個參數則可設為約10倍於

17. max_connection的大小；）不要設定太大，會將資料庫撐爆

18. tmp_table_szie、max_heap_table_size、sort_buffer_size、

19. join_buffer_size、read_buffer_size、read_rnd_buffer_size等都是

20. 每個串連session分配的，因此不能設定過大

21. 建議關閉query cache功能或降低設定不要超過512M

更多核心參數：my-innodb-heavy-4G.cnf 設定檔參數介紹 

MySQL工具mysqlreport 我們可以使用工具來分析MySQL的效能 
如何才能做到網站高並發訪問? 生產環境中對於防範DDOS攻擊的討論 
面試可能會問到DOOS 攻擊防護

關於庫表的設計規範

1. 推薦utf-8字元集，雖然有人說談沒有latin1快

2. 固定字串的列儘可能多用定長char，少用varchar

3. 儲存可變長度的字串使用VARCHAR而不是CAHR---節省空間的，因為固定

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

5. 所有的InnoDB表都設計一個無業務的用途的自增列做主鍵

6. 欄位長度滿足需求前提下，儘可能選擇長度小的

7. 欄位屬性盡量都加NOT NULL約束

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

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

10. 讀取資料時，只選取所需要的列，不要每次都SELECT * 避免產生嚴重的隨機讀

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

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

13. 對一個VARCHAR（N）列建立索引時，通常取其50%（甚至更小）左右長度建立首碼

14. 索引就足以滿足80%以上的查詢需求了，沒必要建立整列的全長度索引。

15. 多用符合索引，少用多個獨立索引，尤其是一些基礎（Cardinality）太小

16. （如果說：該列的唯一值總數少於255）的列就不要建立獨立索引了。

4

SQL語句的最佳化

索引最佳化 
1)白名單機制一百度，項目開發啊，DBA參與，減少上線後的慢SQL資料 
抓出慢SQL,配置my.cnf

1. long_query_time = 2

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

3. log_queries_not_using_indexs

4. 按天輪詢：slow-log.log

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

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

3)每天晚上0點定時分析慢查詢，發到核心開發，DBA分析，及進階營運，CTO的郵箱裡 
DBA分析給出最佳化建議-->核心開發確認更新-->DBA線上操作處理 
4)定期使用pt-duplicate-key-checker檢查並重複資料刪除的索引 
定期使用pt-index-usage工具檢查並刪除使用頻率很低的索引 
5)使用pt-online-schema-change來完成大表的ONLINE DDL需求 
6)有時候MySQL會使用錯誤的索引，對於這種情況使用USE INDEX 
7)使用explain及set profile最佳化SQL語句

大的複雜的SQL語句拆分成多個小的SQL語句 
子查詢，JOIN連表查詢，某個表4000萬條記錄 
資料庫是儲存資料的地方，但不是計算資料的地方 
對資料計算，應用類處理，都要拿到前端應用解決。禁止在資料庫上處理 
搜尋功能，like ‘%oldboy%’ 一般不要用MySQL資料庫 
使用串連(JOIN)來代替子查詢（Sub_Queries） 
避免在整個表上使用cout(*)，它可能鎖住整張表 
多表聯結查詢時，關聯欄位類型盡量一致，並且都要有索引。 
在WHERE子句中使用UNION代替子查詢 
多表串連查詢時，把結果集小的表（注意，這裡是指過濾後的結果集，不一樣是全表資料量小的）作為驅動表

爬蟲擷取資料的過程

5

網站集合群架構上的最佳化

1.伺服器上跑多執行個體，2-4個（具體需要看伺服器的硬體資訊）

2.主從複製一主五從，採用mixed模式，盡量不要跨機房同步（進程遠程讀本地寫）

3.定期使用pt-table-checksum、pt-table-sync來檢查並修複mysql主從複製的資料差異

4.業務拆分：搜尋功能，like ‘%oldboy% ‘ 一般不要用MySQL資料庫

5.業務拆分：某些業務應用使用nosql持久化儲存，例如：memcached、redis、ttserver

  例如粉絲關注，好友關係等

6.資料庫前端必須要加cache，例如：memcached，使用者登入，商品查詢

7.動態資料庫靜態化，整個檔案靜態化，頁面片段靜態化

8.資料庫叢集與讀寫分離。一主多從，通過程式或dbproxy進行叢集讀寫分離

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

10.百度、阿里國內前三公司，會選擇從庫進行備份，對資料庫進行分庫分表

6

MySQL 流程、制度控制最佳化

任何一次人為資料庫記錄的更新，都要走一個流程：

a.人的流程：開發-->核心開發-->營運或DBA

b.測試流程：內網測試-->IDC測試-->線上執行

c.用戶端管理，phpmyadmin

MySQL基礎安全

1.啟動程式700，屬主和使用者組為MySQL

2.為MySQL超級使用者root設定密碼

3.如果要求嚴格可以刪除root使用者，建立其他系統管理使用者，例如admin

4.登入時盡量不要在命令列暴露密碼，備份指令碼中如果有密碼，給設定700，

  屬主和密碼組為mysql或root

5.刪除預設存在的test庫

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

| root | 127.0.0.1 |

| root | localhost |

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

8.清理mysql動作記錄檔案~/.mysql_history（許可權600，可以不刪）

9.禁止開發獲得到web串連的密碼，禁止開發串連操作生產對外的庫

10.phpmyadmin安全

11.伺服器禁止設定外網IP

12.防SQL注入（WEB）php.ini或web開發外掛程式監控，waf控制

文章轉自：https://www.abcdocker.com/abcdocker/952

深度最佳化LNMP之MySQL