第1章 硬件層面優化

1.1 數據庫物理機采購（*****）

• CPU（運算）：64位CPU，一臺機器2-16顆CPU，至少2-4顆，L2越大越好。

  創新互聯長期為上千多家客戶提供的網站建設服務，團隊從業經驗10年，關注不同地域、不同群體，并針對不同對象提供差異化的產品和服務；打造開放共贏平臺，與合作伙伴共同營造健康的互聯網生態環境。為老河口企業提供專業的網站建設、成都做網站，老河口網站改版等技術服務。擁有10多年豐富建站經驗和眾多成功案例,為您定制開發。

• 內存：96G-256G（百度），3-4個實例。32-64G，跑1-2個實例（新浪）。

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

 

性能：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的性能

 

 

1.2 企業案例：

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

sina：服務器是DELL R510居多，CPU是E5210，48GB內存，硬盤12*300G SAS，做RAID10

 

1.3 服務器硬件配置調整

(1)服務器BIOS調整： （buffer和緩存調整大點。）

　

提升CPU效率參考設置：

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

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

c. MemoryFrequency（內存頻率）選擇MaximumPerformance（最佳性能）

d.內存設置菜單中，啟動Node Interleaving，避免NUMA問題

(2)陣列卡調整：

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

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

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

 

第2章 操作系統層面優化

2.1 操作系統及MySQL實例選擇

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

2.將操作系統和數據分開，不僅僅是邏輯上，還包括物理上

3.避免使用Swap交換分區

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

5.避免使用LVM邏輯卷

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

 

2.2 文件系統層優化（***）

2.2.1 調整磁盤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調度參數

對于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調度方法 Linux deadline io 調度算法。

 

2.2.3 采用xfs文件系統

　業務量不是很大也可采用ext4，業務量很大，推薦使用xfs：調整XFS文件系統日志和緩沖變量

XFS高性能設置。

2.2.4 mount掛載文件系統

　增加：async,noatime,nodiratime,nobarrier等

noatime

訪問文件時不更新inode的時間戳，高并發環境下，推薦顯示應用該選項，可以提高系統I/O性能

async

寫入時數據會先寫到內存緩沖區,只到硬盤有空檔才會寫入磁盤,這樣可以提升寫入效率！風險為若服務器宕機或不正常，會損失緩沖區中未寫入磁盤的數據 

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

nodiratime

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

nobarrier

不使用raid卡上電池

2.2.5 Linux 內核參數優化

1.將vm，swappiness設置為0-10

2.將vm，dirty_background_ratio設置為5-10，將vm，dirty_ratio設置為它的兩倍左右，以確保能持續將臟數據刷新到磁盤，避免瞬間I/O寫，產生嚴重等待

2.3 優化TCP協議棧

 

#減少TIME_WAIT，提高TCP效率

net.ipv4.tcp_tw_recyle=1

net.ipv4.tcp_tw_reuse=1

#減少處于FIN-WAIT-2連接狀態的時間，使系統可以處理更多的連接

net.ipv4.tcp_fin_timeout=2

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

net.ipv4.tcp_keepalived_time=600

#提高系統支持的最大SYN半連接數（默認1024）

net.ipv4.tcp_max_syn_backlog = 16384

#減少系統SYN連接重試次數(默認5)

net,ipv4.tcp_synack_retries = 1

net.ipv4.tcp_sync_retries = 1

#在內核放棄建立的連接之前發送SYN包的數量

net.ipv4.ip_local_prot_range = 450065535

#允許系統打開的端口范圍

2.4 網絡優化

#優化系統套接字緩沖區

 

#Increase TCP max buffer size

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

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

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

net.core.rmem_default = 8388608

#優化TCP接收/發送緩沖區

 

# 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

#優化網絡設備接收隊列

 

net.core.netdev_max_backlog=3000

2.5 其他優化

 

net.ipv4.tcp_timestamps = 0

net.ipv4.tcp_max_orphans = 3276800

net.ipv4.tcp_max_tw_buckets = 360000

 

第3章 MySQL數據庫層面優化（*****）

3.1 my.cnf參數優化

此優化主要針對innodb引擎

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

       key_buffer_size = 256M  

       #指定用于索引的緩沖區大小，增加它可得到更好的索引處理性能。對于內存在4GB左右的服務器來        說，該參數可設置為256MB或384MB。

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

調整innodb_buffer_pool_size大小，考慮設置為物理內存的50%-60%左右。

        innodb_buffer_pool_size = 64M  

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

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

                           

設置innodb_file_per_table = 1，使用獨立表空間

設置innodb_data_file_path = ibdata1:1G:autoextend,不要使用默認的10%

設置innodb_log_file_size=256M,設置innodb_log_files_in_group=2，基本可滿足90%以上的場景；

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

設置long_query_time = 1記錄那些執行較慢的SQL，用于后續的分析排查；

根據業務實際需要，適當調整max_connection（最大連接數）max_connection_error（最大錯誤數，建議設置為10萬以上，而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache這幾個參數則可設為約10倍于max_connection的大小；）不要設置太大，會將數據庫撐爆

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

query_cache_size = 64M 

#指定MySQL查詢緩沖區的大小。可以通過在MySQL控制臺觀察，如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖不夠的情況;如果Qcache_hits的值非常大，則表明查詢緩沖使用得非常頻繁。另外如果改值較小反而會影響效率，那么可以考慮不用查詢緩沖。對于Qcache_free_blocks，如果該值非常大，則表明緩沖區中碎片很多。

tmp_table_size = 64M  

#設置內存臨時表最大值。如果超過該值，則會將臨時表寫入磁盤，其范圍1KB到4GB。

max_heap_table_size = 64M 

 #獨立的內存表所允許的最大容量。

table_cache = 614 

#給經常訪問的表分配的內存，物理內存越大，設置就越大。調大這個值，一般情況下可以降低磁盤IO，但相應的會占用更多的內存,這里設置為614。

3.2 關于庫表的設計規范

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

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

   存儲可變長度的字符串使用VARCHAR而不是CAHR---節省空間，因為固定長度的CHAR，而VARCHAR長度不固定（UTF8不愁此影響）

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

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

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

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

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

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

8.對一個VARCHAR（N）列創建索引時，通常取其50%（甚至更小）左右長度創建前綴索引就足以滿足80%以上的查詢需求了，沒必要創建整列的全長度索引。

9.多用符合索引，少用多個獨立索引，尤其是一些基礎（Cardinality）太小（如果說：該列的唯一值總數少于255）的列就不要創建獨立索引了。

3.3 SQL語句的優化

3.3.1 索引優化

1）白名單機制一百度，項目開發，DBA參與，減少上線后的慢SQL數據

抓出慢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點定時分析慢查詢，發到核心開發，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語句

 

網站打開慢之慢查詢

3.3.2 大的復雜的SQL語句拆分成多個小的SQL語句

子查詢，JOIN連表查詢，某個表4000萬條記錄

3.3.3 數據庫是存儲數據的地方，但不是計算數據的地方

對數據計算，應用類處理，都要拿到前端應用解決。禁止在數據庫上處理

3.3.4 搜索功能，like ‘%oldboy%’ 一般不要用MySQL數據庫

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

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

多表聯接查詢時，關聯字段類型盡量一致，并且都要有索引。

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

多表連接查詢時，把結果集小的表（注意，這里是指過濾后的結果集，不一樣是全表數據量小的）作為驅動表

 

• 爬蟲獲取數據的過程

 

第4章 網站集成架構優化（*****）

網站集群架構上的優化

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.百度、阿里國內前×××司，會選擇從庫進行備份，對數據庫進行分庫分表

 

 

第5章 MySQL數據庫管理流程（*****）

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

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

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

c.客戶端管理，phpmyadmin

 

第6章 MySQL數據庫安全優化（*****）

6.1 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控制

文章名稱：MySQL數據庫優化
分享鏈接：http://www.yijiale78.com/article8/pcsgip.html

成都網站建設公司_創新互聯，為您提供做網站、網站改版、標簽優化、網站設計公司、電子商務、定制開發

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯