我的MYSQL抗造

一、服務器硬件

1、cpu(頻率和數量)

（1）cpu頻率

cpu密集型：主要對sql執行效率，目前mysql不支持多cpu對同一sql并發處理；

（2）cpu數量（web應用）

主要提高吞吐量和并發處理量；

例子：

?2、內存大小

內存越多越好，但根據實際情況增加

內存的選擇：

建議：內存的主頻和cpu的主頻類似，選擇主板支持的最大內存

注意：（1）組成購買升級

（2）每個通道的內存：相同品牌、顆粒、頻率、電壓、檢驗技術和型號；

（3）單條容量內存盡可能大；

3、磁盤的配置和選擇

磁盤性能的限制： 延遲和吞吐量

（1）傳統機器硬盤

考慮因素：存儲容量；傳輸速度；訪問時間；主軸轉速；物理尺寸

（2）使用RAID增強傳統機器硬盤的性能

考慮因素：raid級別：比如raid0(速度最快)，raid1(可靠性)，raid5(以讀效率最好)，raid10(讀寫性能相對較好)

備注：raid卡最好選擇有緩存功能的

（3）使用固態存儲SSD和PCIe卡

考慮因素：隨機讀寫性能相對更好；相對更好支持并發；對比更容易損壞；價格相對較高

SSD使用場景：

1)適合于存在大量隨機I/O的場景；

2)適用于解決單線程負載的I/O瓶頸；

（4）使用網絡存儲NAS和SAN

1)SAN--存儲區域網絡

通過光纖連接服務器，設備通過塊接口訪問，可其當做硬盤使用。

特點：大量順序讀寫快，隨機讀寫慢

2)NAS--網絡附加存儲

通過網絡連接，基于文件的協議,如NFS或SMB來訪問；

? ? 網絡存儲適用的場景：

數據庫備份；

4、網絡對性能的影響: 延遲和帶寬

（1）網絡帶寬對性能的影響

（2）網絡質量對性能的影響

建議：采用高性能和高帶寬的網絡接口設備和交換機；

對多個網卡進行綁定，增強可用性和帶寬；

盡可能的進行網絡隔離；

5、服務器BIOS調整：

提升CPU效率參考設置：

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

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

（3） Memory Frequency（內存頻率）選擇Maximum Performance（最佳性能）

（4）內存設置菜單中，啟動Node Interleaving，避免NUMA問題

6、陣列卡調整：

（1）購置陣列卡同時配備CACHE及BBU模塊(機械盤)

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

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

二、服務器系統

1、windows系統---mysql大小寫問題

2、FreeBSD系統---選擇最新的

3、Solaris系統

4、Linux系統----Redhat/Centos

Centos系統參數優化：

查看命令：sysctl -a

生效命令：sysctl -p

1、系統內核相關參數（/etc/sysctl.conf）

?//網絡參數

net.core.somaxconn = 32768

#web應用中listen函數的backlog默認會給我們內核參數的net.core.somaxconn限制到128，而nginx定義的NGX_LISTEN_BACKLOG默認為511，所以有必要調整這個值。

net.core.netdev_max_backlog = 65535

#每個網絡接口接收數據包的速率比內核處理這些包的速率快時，允許送到隊列的數據包的最大數目。

net.ipv4.tcp_max_syn_backlog = 65535

#未收到客戶端確認信息的連接請求的最大值

//控制tcp等待時間參數，加快tcp回收，實現高負載

net.ipv4.tcp_tw_reuse = 1

#表示開啟重用。允許將TIME-WAIT sockets重新用于新的TCP連接，默認為0，表示關閉；

net.ipv4.tcp_tw_recycle = 1

#表示開啟TCP連接中TIME-WAIT sockets的快速回收，默認為0，表示關閉；

net.ipv4.tcp_fin_timeout = 10

#修改系統默認的 TIMEOUT 時間;

//以下四個參數表示tpc socket接受和發送緩沖區的默認值和最大值

net.core.wmem_default = 87380

net.core.rmem_max = 16777216 ? ? ? ? ? #最大socket讀buffer,可參考的優化值:873200

net.core.rmem_default = 8388608

net.core.wmem_max = 16777216 ? ? ? ? ? #最大socket寫buffer,可參考的優化值:873200

優化TCP接收/發送緩沖區

# Increase Linux autotuning TCP buffer limits

net.ipv4.tcp_rmem=4096 87380 16777216

net.ipv4.tcp_wmem=4096 65536 16777216

net.ipv4.tcp_mem = 94500000 915000000 927000000

// 以下三個參數用于減少失效鏈接所占用的tcp系統資源，加快資源回收效率

查看命令（sysctl -a|grep tcp_keepalive）

net.ipv4.tcp_keepalive_time = 600

#表示當keepalive起用的時候，TCP發送keepalive消息的頻度；減少TCP KeepAlived連接偵測的時間，使系統可以處理更多的連接。缺省是2小時，改為10分鐘。

net.ipv4.tcp_keepalive_intvl = 30

#當探測沒有確認時，重新發送探測的頻度。缺省是75秒。

net.ipv4.tcp_keepalive_probes = 3

#認定連接失效之前，發送多少個TCP的keepalive探測包。缺省值是9。這個值乘以tcp_keepalive_intvl之后決定了，一個連接發送了keepalive之后可以有多少時間沒有回應

net.ipv4.tcp_syncookies = 1

#表示開啟SYN Cookies。當出現SYN等待隊列溢出時，啟用cookies來處理，可防范少量SYN***，默認為0，表示關閉；

net,ipv4.tcp_synack_retries = 1

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

net.ipv4.tcp_sync_retries = 1

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

net.ipv4.ip_local_prot_range = 4500 65535

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

net.ipv4.tcp_max_tw_buckets = 4096

# 系統保持TIME_WAIT socket最大數量，如果超出這個數，系統將隨機清除一些TIME_WAIT并打印警告信息

net.ipv4.tcp_max_syn_backlog = 4096

# 進入SYN隊列最大長度，加大隊列長度可容納更多的等待連接（默認1024）

//內存參數

#設置最大內存共享段大小bytes

kernel.shmmax = 68719476736

#重要參數之一，用于定義單個共享內存段的最大值。

注意：

1）建議設置的足夠大，以便一個共享內存段容納整個的Innodb緩存池的大小；

2）可取最大為物理內存-1byte,建議值大于物理內存的一半，一般取值大于innodb緩沖池的大小即可。

kernel.shmall = 4294967296

#系統一次可以使用的共享內存大小，以頁為單位；Linux 共享內存頁大小為4KB,shmall=shmmax/4;

vm.swappiness=0

#內存交換分區；當物理內存使用到100%時使用內存交換分區；

備注：

如果禁用內存交換分區會降低操作系統的性能；容易造成內存溢出，崩潰，會別系統kill掉。

在MySQL的服務器上最好設置vm.swappiness=1或0

//文件緩存

vm.dirty_background_ratio = 10

vm.dirty_background_bytes = 0

vm.dirty_ratio = 20

vm.dirty_bytes = 0

vm.dirty_writeback_centisecs = 500

vm.dirty_expire_centisecs = 3000

vm.dirty_background_ratio 是內存可以填充“臟數據”的百分比。這些“臟數據”在稍后是會寫入磁盤的，pdflush/flush/kdmflush這些后臺進程會稍后清理臟數據。

vm.dirty_ratio 是絕對的臟數據限制，內存里的臟數據百分比不能超過這個值。如果臟數據超過這個數量，新的IO請求將會被阻擋，直到臟數據被寫進磁盤。這是造成IO卡頓的重要原因，但這也是保證內存中不會存在過量臟數據的保護機制。

vm.dirty_expire_centisecs 指定臟數據能存活的時間。在這里它的值是30秒。當 pdflush/flush/kdmflush 進行起來時，它會檢查是否有數據超過這個時限，如果有則會把它異步地寫到磁盤中。畢竟數據在內存里待太久也會有丟失風險。

vm.dirty_writeback_centisecs 指定多長時間 pdflush/flush/kdmflush 這些進程會起來一次。

備注：

? 將vm.dirty_background_ratio設置為5-10；

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

查看內存中有多少臟數據：

? cat /proc/vmstat | egrep "dirty|writeback"

備注：根據實際線上調整相關參數，更多可以參考官方

2、文件系統層優化

（1）調整磁盤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（參考Linux欄目詳細配置）

磁盤調度策略：

# cat /sys/block/sda/queue/scheduler

noop(No Operation) --- 適合閃存設備、RAM及嵌入式系統

? ?cfq(Completely Fair Scheduler ) ---完全公平調度器

? ?Deadline ---適合數據庫類應用

（3）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

? （4）文件系統，建議xfs(centos7默認)

Windows：FAT和NTFS

Linux：EXT3、EXT4和XFS

（5）mount掛載文件系統(如果是EXT3和EXT4注意如下選項)

data=writeback ，ordered，journal

選項：async,noatime,nodiratime,nobarrier等

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

async：寫入時數據會先寫到內存緩沖區,只到硬盤有空檔才會寫入磁盤,這樣可以提升寫入效率！風險為若服務器宕機或不正常，會損失緩沖區中未寫入磁盤的數據 解決辦法：服務器主板電池或加UPS不間斷電源。

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

nobarrier：不使用raid卡上電池

例子：/dev/sda1/ext4 noatime,nodiratime,data=writeback 1 1

（6）資源限制（/etc/security/limits.conf ）---打開文件數的限制。

查看命令：ulimit -a（根據實際情況調整）

備注：limits.conf是Linux PAM(插入式認證模塊)

* soft nofile 65535

* hard nofile 65535

* soft noproc 65535

* hard noproc 65535

#上述兩個參數控制打開文件數的限制

參數說明：

* ? ?----表示對所有用戶有效

soft----指的是當前系統生效的設置

hard---是代表系統中所能設定的最大值

nofile--指所限制的資源是打開文件的最大數目

noproc--是代表系統中所能設定最大進程數

注意：重啟系統才生效

3、關閉seliunx（安全機制詳細參考其他文章）

# vim /etc/selinux/config

SELINUX=disabled

4、關閉numa（建議關閉bios）

可以從BIOS，操作系統，啟動進程時臨時關閉.

三、數據庫體系結構

客戶端----鏈接管理器（mysql服務層）----存儲引擎

備注：存儲引擎是針對表的而不是針對于庫（一個庫中不同表可以使用不同的存儲引擎）

1、存儲引擎之MyISAM

MYISAM存儲引擎表有MYD(數據)和MYI(索引)組成。

特性：

1）并發性與鎖級別----修改加鎖級表；讀的加共享鎖；讀寫交互不好，單讀效率高

2）表損壞修復----容易造成數據丟失

#查看表是否損壞：

mysql > check table tablename；

#修復表：

mysql > repair table tablename；

3）MYISAM表支持的索引類型

4）MYISAM表支持數據壓縮---壓縮后的表只允許讀，不能寫

命令：myisampack

例子：myisampack -b tablename.MYI

限制:根據版本不同存儲大小不同

場景：

1）非事務型應用

2）只讀類應用

3）空間類應用

2、存儲引擎之innodb

2.1、innodb使用表空間進行 數據存儲

通過innodb_file_per_table狀態存儲

mysql > show variables like 'innodb_file_per_table';

ON：獨立表空間:tablename.ibd

OFF:系統表空間：ibdataX

對比：

1）系統表空間無法簡單的收縮文件大小；

獨立表空間可以通過optimize table命令收縮系統文件；

2）系統表空間會產生IO瓶頸；

獨立空間可以同時想多個文件刷新數據；

表轉移步驟：(系統空間轉獨立表空間)

1）使用MySQLdump導出所有數據庫表數據；

2）停止mysql服務，修改參數，并刪除innodb相關文件；

3）重啟mysql服務，重建innodb系統表空間；

4）重新導入數據；

兩個重要關鍵字：innodb數據字典信息和Undo回滾段

2.2、兩個特殊日志(Redi Log和Undo Log)

1)Redo log文件包含一組log files，通常是物理日志，記錄的是數據頁的物理修改，其會被循環使用。

?innodb_log_file_size 和 innodb_log_files_in_group

生成兩個文件：ib_logfile0和ib_logfile1

2）Undo Log(隨機讀取)---用來回滾行記錄到某個版本。undo log一般是邏輯日志，根據每行記錄進行記錄；

2.3、innodb檢查狀態

mysql> show engine innodb status\G;

2.4、場景

1）使用于大多數OLTP應用

3、其他存儲引擎

（1）CSV存儲引擎---以文本方式存儲在文件中

特點：

1）以CSV格式進行數據存儲；

2）所有列必須都是不能為UNLL的；

3）不支持索引（不適合大表，不適合在線處理）；

4）可以對數據文件直接編輯；

場景：適合最為數據交換的中間表

（2）Archive存儲引擎

特點：

1）只支持insert和select操作；

2）只允許在自增id列上加索引；

場景：日志和數據采集類應用

（3）Memory存儲引擎

特點：

1）支持HASH索引(等值查找)和BTree索引(范圍查找)；

2）所有字段都為固定長度；

3）不支持BLOG和TEXT等大字段；

4）Memory存儲引擎使用表級鎖；

5）最大大小由max_heap_table_size參數決定(已存無效,如需則重建)；

場景：

1）用于查找或者映射表，例如郵編和地區的對應表；

2）用于保存數據分析中產生的中間表；

3）用于緩存周期性聚合數據的結果表；

（4）Federated存儲引擎

特點：

1）提供了訪問遠程mysql服務器上表的方法；

2）本地不存儲數據，數據全部放到遠程服務器上；

3）本地需要保存表結構和遠程服務器的鏈接信息；

場景：偶爾的統計分析及手工查詢中使用

4、如何選擇存儲引擎

參考條件：事物(innodb)、備份(innodb)、崩潰恢復(MYISAM)、存儲引擎的特性

四、數據庫參數文件my.cnf 配置

1、mysql基礎

（1）mysql配置路徑

1）命令行參數（版本不用有所差異）

mysqld_dafe --datadir=/data/sql_data

2）配置文件

查看mysql讀取文件順序命令：

# mysqld --help --verbose | grep -A 1 'Default options'

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

（2）mysql作用域

（1）全局參數

set global 參數名=參數值；

set @@global.參數名:=參數值；

（2）會話參數

set [session] 參數名=參數值；

set @@session.參數名:=參數值；

2、內存配置參數

（1）每個連接(線程)使用的內存

sort_buffer_size-----排序緩沖大小

join_buferr_size-----連接緩沖大小

read_buferr_size---讀查詢操作所能使用的緩沖區大小，4k倍數

read_rnd_buferr_size-隨機讀的時所使用的索引緩沖區大小

（2）緩存池配置

Innodb_buferr_pool_size-----innodb緩存池

key_buffer_size ----MYISAM緩存池

3、I/O配置參數

（1）innodb I/O配置

innodb_log_file_size

innodb_log_files_in_group

事務日志總大小=innodb_log_file_size*innodb_log_files_in_group

innodb_log_buffer_size ----事務日志的大小

innodb_flush_log_at_trx_commit ---刷新日志模式，選項[0-2]建議2

其他的

innodb_flush_method=O_DIRECT---innodb刷新方法

innodb_file_per_table=1 ----打開獨立表空間

innodb_doublewrite=1 ?--開啟兩次寫，保證數據可靠性

? ?innodb_data_file_path ---mysql的ibdata1建議設置1G，防止高并發受影響

? ? ?設置：innodb_data_file_path?=?ibdata1:1G:autoextend

（2）MyISAM I/O配置

delay_key_write

OFF:每次寫操作后刷新鍵緩沖中的臟塊到磁盤(最安全，性能差)；

ON:只對在建表時指定了該選項參數的表使用延遲刷新；

ALL:對所有MYISAM表都使用延遲鍵寫入；（索引易損壞）

4、安全配置

expire_logs_days ---指定自動清理binlog的天數

max_allowed_packet --控制mysql可以接收的包大小

skip_name_resolve----禁止DNS查找

sysdate_is_now---確保sysdate()返回確定性日期

read_only---禁止非super權限的用戶寫權限(主從里在從庫啟動，保證數據一致性)

skip_slave_start---禁用Slave自動恢復(主從里在從庫啟動，阻止mysql重啟后自動復制數據)

sql_mode---設置MYSQL所使用的SQL模式（不要輕易改動）

5、其他常用配置參數

sync_binlog ----控制MYSQL如何向磁盤刷新binlog，建議設置為1

tmp_table_size和max_heap_table_size---控制內存臨時表大小

max_connections----控制允許的最大連接數

五、數據庫結構設計和sql語句

1、需要分析：全面了解產品設計的存儲需求；

存儲需求

數據處理需求

數據的安全性和完整性

2、邏輯設計：設計數據的邏輯存儲結構

數據實體之間的邏輯關系，解決數據冗余

和數據維護異常

3、物理設計：根據所使用的數據庫特點進行表結構設計

關系數據庫：oracle，SQLServer，Mysql,postgresSQL

非關系數據庫：mongodb，Redis，Hadoop

存儲引擎：innodb，myisan

4、維護優化：根據實際情況對索引、存儲結構等進行優化