我們究竟應(yīng)該如何對MySQL數(shù)據(jù)庫進(jìn)行優(yōu)化？下面我就從MySQL對硬件的選擇、MySQL的安裝、my.cnf的優(yōu)化、MySQL如何進(jìn)行架構(gòu)設(shè)計及數(shù)據(jù)切分等方面來說明這個問題。

服務(wù)器物理硬件的優(yōu)化

在挑選硬件服務(wù)器時，我們應(yīng)該從下面幾個方面著重對MySQL服務(wù)器的硬件配置進(jìn)行優(yōu)化，也就是說將項目中的資金著重投入到如下幾處：

1、磁盤尋道能力（磁盤I/O），我們現(xiàn)在用的都是SAS15000轉(zhuǎn)的硬盤，用6快這樣的硬盤作RAID1+0。MySQL每一秒鐘都在進(jìn)行大量、復(fù)雜的查詢操作，對磁盤的讀寫量可想而知，所以，通常認(rèn)為磁盤I/O是約制MySQL性能的最大因素之一。對于日均訪問量在100萬PV以上的論壇（Discuz）、博客（Wordpress），如果性能不好，造成的直接后果就是MySQL的性能會非常低下！解決這一制約因素可以考慮解決訪問是：使用RAID1+0磁盤陣列，注意不要嘗試RAID5，MySQL在PAID5磁盤陣列上的效率不會像你期待的那樣快，如果資金允許，可以選擇固態(tài)硬盤SSD來替代SAS硬盤做RAID1+0。

2、CPU對于MySQL的影響也是不容忽視的，建議選擇運算能力強(qiáng)悍的CPU。推薦使用DELL R710（雙四核），商家的賣點也是其強(qiáng)大的虛擬化和數(shù)據(jù)庫能力。

3、對于一臺使用MySQL的Database Server來說，建議服務(wù)器的內(nèi)存不要小于2GB，推薦使用4GB以上的物理內(nèi)存，不過內(nèi)存對于現(xiàn)在的服務(wù)器而言可以說是一個可以忽略的問題，如果是高端服務(wù)器，基本上內(nèi)存都超過了32GB，我們的數(shù)據(jù)流服務(wù)器都是32GB內(nèi)存。

我在工作中用的比較多的數(shù)據(jù)庫服務(wù)器是DELL R710/R720，其穩(wěn)定性和性能都不錯，同時我也發(fā)現(xiàn)許多同行都是采用它作為數(shù)據(jù)庫的服務(wù)器，所以在這里也向大家推薦一下。

MySQL應(yīng)采用編譯方式安裝

關(guān)于MySQL數(shù)據(jù)庫的線上環(huán)境安裝，我建議采取編譯安裝的方式，這樣性能會有較大的提升。服務(wù)器系統(tǒng)則建議CentOS6.7 X86_64，源碼包的編譯參數(shù)會默認(rèn)以Debug模式生成二進(jìn)制代碼，而Debug模式給MySQL帶來的性能損失是比較大的，所以當(dāng)我們編譯準(zhǔn)備安裝的產(chǎn)品代碼時，一定不要忘記使用–without-debug參數(shù)禁止Debug模式。如果把–with-mysqld-ldflags和–with-client-ld-flags兩個編譯參數(shù)設(shè)置為–all-static的話，可以告訴編譯器以靜態(tài)的方式編譯，編譯結(jié)果將得到最高的性能。使用靜態(tài)編譯和使用動態(tài)編譯的代碼相比，性能差距可能會達(dá)到5%至10%之多。在后面我會跟大家分享我們線上MySQL數(shù)據(jù)庫的編譯參數(shù)，大家可以參考下，然后根據(jù)自己的線上環(huán)境自行修改內(nèi)容。

MySQL配置文件優(yōu)化

[client]

port = 3306 #客戶端端口號為3306

socket = /data/3306/mysql.sock #

default-character-set = utf8 #客戶端字符集,(控制character_set_client、character_set_connection、character_set_results)

[mysql]

no-auto-rehash #僅僅允許使用鍵值的updates和deletes

[mysqld] #組包括了mysqld服務(wù)啟動的參數(shù)，它涉及的方面很多，其中有MySQL的目錄和文件，通信、網(wǎng)絡(luò)、信息安全，內(nèi)存管理、優(yōu)化、查詢緩存區(qū)，還有MySQL日志設(shè)置等。

user = mysql #mysql_safe腳本使用MySQL運行用戶(編譯時–user=mysql指定),推薦使用mysql用戶。

port = 3306 #MySQL服務(wù)運行時的端口號。建議更改默認(rèn)端口,默認(rèn)容易遭受攻擊。

socket = /data/3306/mysql.sock #socket文件是在Linux/Unix環(huán)境下特有的，用戶在Linux/Unix環(huán)境下客戶端連接可以不通過TCP/IP網(wǎng)絡(luò)而直接使用unix socket連接MySQL。

basedir = /application/mysql #mysql程序所存放路徑,常用于存放mysql啟動、配置文件、日志等

datadir = /data/3306/data #MySQL數(shù)據(jù)存放文件(極其重要)

character-set-server = utf8 #數(shù)據(jù)庫和數(shù)據(jù)庫表的默認(rèn)字符集。(推薦utf8,以免導(dǎo)致亂碼)

log-error=/data/3306/mysql_xuliangwei.err #mysql錯誤日志存放路徑及名稱(啟動出現(xiàn)錯誤一定要看錯誤日志,百分之百都能通過錯誤日志排插解決。)

pid-file=/data/3306/mysql_xuliangwei.pid #MySQL_pid文件記錄的是當(dāng)前mysqld進(jìn)程的pid，pid亦即 ProcessID。

skip-locking #避免MySQL的外部鎖定，減少出錯幾率，增強(qiáng)穩(wěn)定性。

skip-name-resolv #禁止MySQL對外部連接進(jìn)行DNS解析，使用這一選項可以消除MySQL進(jìn)行DNS解析的時候。但是需要注意的是，如果開啟該選項，則所有遠(yuǎn)程主機(jī)連接授權(quán)都要使用IP地址方式了，否則MySQL將無法正常處理連接請求！

skip-networking #開啟該選項可以徹底關(guān)閉MySQL的TCP/IP連接方式，如果Web服務(wù)器是以遠(yuǎn)程連接的方式訪問MySQL數(shù)據(jù)庫服務(wù)器的，則不要開啟該選項，否則無法正常連接！

open_files_limit = 1024 #MySQLd能打開文件的最大個數(shù),如果出現(xiàn)too mant open files之類的就需要調(diào)整該值了。

back_log = 384 #back_log參數(shù)是值指出在MySQL暫時停止響應(yīng)新請求之前，短時間內(nèi)的多少個請求可以被存在堆棧中。如果系統(tǒng)在短時間內(nèi)有很多連接，則需要增加該參數(shù)的值，該參數(shù)值指定到來的TCP/IP連接的監(jiān)聽隊列的大小。不同的操作系統(tǒng)在這個隊列的大小上有自己的限制。如果試圖將back_log設(shè)置得高于操作系統(tǒng)的限制將是無效的，其默認(rèn)值為50.對于Linux系統(tǒng)而言，推薦設(shè)置為小于512的整數(shù)。

max_connections = 800 #指定MySQL允許的最大連接進(jìn)程數(shù)。如果在訪問博客時經(jīng)常出現(xiàn)
Too Many Connections的錯誤提示，則需要增大該參數(shù)值。

max_connect_errors = 6000 #設(shè)置每個主機(jī)的連接請求異常中斷的最大次數(shù)，當(dāng)超過該次數(shù)，MySQL服務(wù)器將禁止host的連接請求，直到MySQL服務(wù)器重啟或通過flush hosts命令清空此host的相關(guān)信息。

wait_timeout = 120 #指定一個請求的最大連接時間，對于4GB左右內(nèi)存的服務(wù)器來說，可以將其設(shè)置為5~10。

table_cache = 614K #table_cache指示表高速緩沖區(qū)的大小。當(dāng)MySQL訪問一個表時，如果在MySQL緩沖區(qū)還有空間，那么這個表就被打開并放入表緩沖區(qū)，這樣做的好處是可以更快速地訪問表中的內(nèi)容。一般來說，可以查看數(shù)據(jù)庫運行峰值時間的狀態(tài)值Open_tables和Open_tables，用以判斷是否需要增加table_cache的值，即如果Open_tables接近table_cache的時候，并且Opened_tables這個值在逐步增加，那就要考慮增加這個值的大小了。

external-locking = FALSE #MySQL選項可以避免外部鎖定。True為開啟。

max_allowed_packet =16M #服務(wù)器一次能處理最大的查詢包的值，也是服務(wù)器程序能夠處理的最大查詢

sort_buffer_size = 1M #設(shè)置查詢排序時所能使用的緩沖區(qū)大小，系統(tǒng)默認(rèn)大小為2MB。

注意：該參數(shù)對應(yīng)的分配內(nèi)存是每個連接獨占的，如果有100個連接，那么實際分配的總排序緩沖區(qū)大小為100 x6=600MB。所以，對于內(nèi)存在4GB左右的服務(wù)器來說，推薦將其設(shè)置為6MB~8MB

join_buffer_size = 8M #聯(lián)合查詢操作所能使用的緩沖區(qū)大小，和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每個連接獨享。

thread_cache_size = 64 #設(shè)置Thread Cache池中可以緩存的連接線程最大數(shù)量，可設(shè)置為0~16384，默認(rèn)為0.這個值表示可以重新利用保存在緩存中線程的數(shù)量，當(dāng)斷開連接時如果緩存中還有空間，那么客戶端的線程將被放到緩存中;如果線程重新被請求，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個線程將被重新創(chuàng)建，如果有很多線程，增加這個值可以改善系統(tǒng)性能。通過比較Connections和Threads_created狀態(tài)的變量，可以看到這個變量的作用。我們可以根據(jù)物理內(nèi)存設(shè)置規(guī)則如下:1GB內(nèi)存我們配置為8,2GB內(nèi)存我們配置為16,3GB我們配置為32,4GB或4GB以上我們給此值為64或更大的值。

thread_concurrency = 8 #該參數(shù)取值為服務(wù)器邏輯CPU數(shù)量x 2，在本例中，服務(wù)器有兩個物理CPU，而每個物理CPU又支持H.T超線程，所以實際取值為4 x 2 = 8。這也是雙四核主流服務(wù)器的配置。

query_cache_size = 64M #指定MySQL查詢緩沖區(qū)的大小?？梢酝ㄟ^在MySQL控制臺觀察，如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況;如果Qcache_hits的值非常大，則表明查詢緩沖使用得非常頻繁。另外如果改值較小反而會影響效率，那么可以考慮不用查詢緩沖。對于Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多。

query_cache_limit = 2M #只有小于此設(shè)置值的結(jié)果才會被緩存

query_cache_min_res_unit = 2k #設(shè)置查詢緩存分配內(nèi)存的最小單位，要適當(dāng)?shù)谠O(shè)置此參數(shù)，可以做到為減少內(nèi)存快的申請和分配次數(shù)，但是設(shè)置過大可能導(dǎo)致內(nèi)存碎片數(shù)值上升。默認(rèn)值為4K，建議設(shè)置為1K~16K。

default_table_type = InnoDB #默認(rèn)表的類型為InnoDB

thread_stack = 256K #設(shè)置MySQL每個線程的堆棧大小，默認(rèn)值足夠大，可滿足普通操作。可設(shè)置范圍為128KB至4GB，默認(rèn)為192KB

#transaction_isolation = Level #數(shù)據(jù)庫隔離級別 (READ UNCOMMITTED(讀取未提交內(nèi)容) READ COMMITTED(讀取提交內(nèi)容) REPEATABLE READ(可重讀) SERIALIZABLE(可串行化))

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

max_heap_table_size = 64M #獨立的內(nèi)存表所允許的最大容量。

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

table_open_cache = 512 #設(shè)置表高速緩存的數(shù)目。每個連接進(jìn)來，都會至少打開一個表緩存。因此，table_cache 的大小應(yīng)與max_connections 的設(shè)置有關(guān)。例如，對于 200 個并行運行的連接，應(yīng)該讓表的緩存至少有 200 × N ，這里 N 是應(yīng)用可以執(zhí)行的查詢的一個聯(lián)接中表的最大數(shù)量。此外，還需要為臨時表和文件保留一些額外的文件描述符。

long_query_time = 1 #慢查詢的執(zhí)行用時上限,默認(rèn)設(shè)置是10s,推薦(1s~2s)

log_long_format #沒有使用索引的查詢也會被記錄。(推薦,根據(jù)業(yè)務(wù)來調(diào)整)

log-slow-queries = /data/3306/slow.log #慢查詢?nèi)罩疚募窂?/span>(如果開啟慢查詢,建議打開此日志)

log-bin = /data/3306/mysql-bin #logbin數(shù)據(jù)庫的操作日志,例如update、delete、create等都會存儲到binlog日志,通過logbin可以實現(xiàn)增量恢復(fù)

relay-log = /data/3306/relay-bin #relay-log日志記錄的是從服務(wù)器I/O線程將主服務(wù)器的二進(jìn)制日志讀取過來記錄到從服務(wù)器本地文件,然后SQL線程會讀取relay-log日志的內(nèi)容并應(yīng)用到從服務(wù)器

relay-log-info-file = /data/3306/relay-log.info #從服務(wù)器用于記錄中繼日志相關(guān)信息的文件,默認(rèn)名為數(shù)據(jù)目錄中的relay-log.info。

binlog_cache_size = 4M #在一個事務(wù)中binlog為了記錄sql狀態(tài)所持有的cache大小，如果你經(jīng)常使用大的，多聲明的事務(wù)，可以增加此值來獲取更大的性能，所有從事務(wù)來的狀態(tài)都被緩沖在binlog緩沖中，然后再提交后一次性寫入到binlog中，如果事務(wù)比此值大，會使用磁盤上的臨時文件來替代，此緩沖在每個鏈接的事務(wù)第一次更新狀態(tài)時被創(chuàng)建。

max_binlog_cache_size = 8M #最大的二進(jìn)制Cache日志緩沖尺寸。

max_binlog_size = 1G #二進(jìn)制日志文件的最大長度(默認(rèn)設(shè)置1GB)一個二進(jìn)制文件信息超過了這個最大長度之前,MySQL服務(wù)器會自動提供一個新的二進(jìn)制日志文件接續(xù)上。

expire_logs_days = 7 #超過7天的binlog,mysql程序自動刪除(如果數(shù)據(jù)重要,建議不要開啟該選項)

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

注意：如果該參數(shù)值設(shè)置得過大反而會使服務(wù)器的整體效率降低！

read_buffer_size = 4M #讀查詢操作所能使用的緩沖區(qū)大小。和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每個連接獨享。

read_rnd_buffer_size = 16M #設(shè)置進(jìn)行隨機(jī)讀的時候所使用的緩沖區(qū)。此參數(shù)和read_buffer_size所設(shè)置的Buffer相反，一個是順序讀的時候使用，一個是隨機(jī)讀的時候使用。但是兩者都是針對與線程的設(shè)置，每個線程都可以產(chǎn)生兩種Buffer中的任何一個。默認(rèn)值256KB，最大值4GB。

bulk_insert_buffer_size = 8M #如果經(jīng)常性的需要使用批量插入的特殊語句來插入數(shù)據(jù),可以適當(dāng)調(diào)整參數(shù)至16MB~32MB,建議8MB。

#myisam_sort_buffer_size = 8M #設(shè)置在REPAIR Table或用Create index創(chuàng)建索引或 Alter table的過程中排序索引所分配的緩沖區(qū)大小，可設(shè)置范圍4Bytes至4GB，默認(rèn)為8MB

lower_case_table_names = 1 #實現(xiàn)MySQL不區(qū)分大小。(發(fā)開需求–建議開啟)

slave-skip-errors = 1032,1062 #從庫可以跳過的錯誤數(shù)字值(mysql錯誤以數(shù)字代碼反饋,全的mysql錯誤代碼大全,以后會發(fā)布至博客)。

replicate-ignore-db=mysql #在做主從的情況下,設(shè)置不需要同步的庫。

server-id = 1 #表示本機(jī)的序列號為1,如果做主從，或者多實例,serverid一定不能相同。

myisam_sort_buffer_size = 128M #當(dāng)需要對于執(zhí)行REPAIR, OPTIMIZE, ALTER 語句重建索引時，MySQL會分配這個緩存，以及LOAD DATA INFILE會加載到一個新表，它會根據(jù)最大的配置認(rèn)真的分配的每個線程。 

myisam_max_sort_file_size = 10G #當(dāng)重新建索引（REPAIR，ALTER，TABLE，或者LOAD，DATA，TNFILE）時，MySQL被允許使用臨時文件的最大值。

myisam_repair_threads = 1 #如果一個表擁有超過一個索引, MyISAM 可以通過并行排序使用超過一個線程去修復(fù)他們.

myisam_recover #自動檢查和修復(fù)沒有適當(dāng)關(guān)閉的 MyISAM 表.

innodb_additional_mem_pool_size = 4M #用來設(shè)置InnoDB存儲的數(shù)據(jù)目錄信息和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小。應(yīng)用程序里的表越多，你需要在這里面分配越多的內(nèi)存。對于一個相對穩(wěn)定的應(yīng)用，這個參數(shù)的大小也是相對穩(wěn)定的，也沒有必要預(yù)留非常大的值。如果InnoDB用廣了這個池內(nèi)的內(nèi)存，InnoDB開始從操作系統(tǒng)分配內(nèi)存，并且往MySQL錯誤日志寫警告信息。默認(rèn)為1MB，當(dāng)發(fā)現(xiàn)錯誤日志中已經(jīng)有相關(guān)的警告信息時，就應(yīng)該適當(dāng)?shù)脑黾釉搮?shù)的大小。

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

innodb_data_file_path = ibdata1:128M:autoextend #設(shè)置配置一個可擴(kuò)展大小的尺寸為128MB的單獨文件，名為ibdata1.沒有給出文件的位置，所以默認(rèn)的是在MySQL的數(shù)據(jù)目錄內(nèi)。

innodb_file_io_threads = 4 #InnoDB中的文件I/O線程。通常設(shè)置為4，如果是windows可以設(shè)置更大的值以提高磁盤I/O

innodb_thread_concurrency = 8 #你的服務(wù)器有幾個CPU就設(shè)置為幾，建議用默認(rèn)設(shè)置，一般設(shè)為8。

innodb_flush_log_at_trx_commit = 1 #設(shè)置為0就等于innodb_log_buffer_size隊列滿后在統(tǒng)一存儲，默認(rèn)為1，也是最安全的設(shè)置。

innodb_log_buffer_size = 2M #默認(rèn)為1MB，通常設(shè)置為8~16MB就足夠了。

innodb_log_file_size = 32M #確定日志文件的大小，更大的設(shè)置可以提高性能，但也會增加恢復(fù)數(shù)據(jù)庫的時間。

innodb_log_files_in_group = 3 #為提高性能,MySQL可以以循環(huán)方式將日志文件寫到多個文件。推薦設(shè)置為3。

innodb_max_dirty_pages_pct = 90 #InnoDB主線程刷新緩存池中的數(shù)據(jù)。

innodb_lock_wait_timeout = 120 #InnoDB事務(wù)被回滾之前可以等待一個鎖定的超時秒數(shù)。InnoDB在它自己的鎖定表中自動檢測事務(wù)死鎖并且回滾事務(wù)。InnoDB用locak tables 語句注意到鎖定設(shè)置。默認(rèn)值是50秒。

innodb_file_per_table = 0 #InnoDB為獨立表空間模式，每個數(shù)據(jù)庫的每個表都會生成一個數(shù)據(jù)空間。0關(guān)閉，1開啟。

獨立表空間優(yōu)點：

1、每個表都有自己獨立的表空間。

2、每個表的數(shù)據(jù)和索引都會存在自己的表空間中。

3、可以實現(xiàn)單表在不同的數(shù)據(jù)庫中移動。

4、空間可以回收（除drop table操作處，表空不能自己回收。）

[mysqldump]

quick

max_allowed_packet = 2M #設(shè)定在網(wǎng)絡(luò)傳輸中一次消息傳輸量的最大值。系統(tǒng)默認(rèn)值為1MB，最大值是1GB，必須設(shè)置為1024的倍數(shù)。單位為字節(jié)。

[mysqld_safe]

值得注意：

• 強(qiáng)烈建議不要武斷地將InnoDB的Buffer Pool值配置為物理內(nèi)存的50%~80%，應(yīng)根據(jù)具體環(huán)境而定。
  

• 如果key_reads太大，則應(yīng)該把my.cnf中的key_buffer_size變大，保持key_reads/key_read_re-quests至少在1/100以上，越小越好。
  

• 如果qcache_lowmem_prunes很大，就要增加query_cache_size的值。
  

不過很多時候需要具體情況具體分析，其他參數(shù)的變更我們可以等MySQL上線穩(wěn)定一段時間后在根據(jù)status值進(jìn)行調(diào)整。

電商MySQL數(shù)據(jù)庫配置文件

這是一份電子商務(wù)網(wǎng)站MySQL數(shù)據(jù)庫調(diào)整后所運行的配置文件/etc/my.cnf(服務(wù)器為DELL R710、16GB內(nèi)存、RAID10)，大家可以根據(jù)實際的MySQL數(shù)據(jù)庫硬件情況進(jìn)行調(diào)整配置文件如下：

[client]

port = 3306

socket = /data/3306/mysql.sock

default-character-set = utf8

[mysqld]

user = mysql

port = 3306

character-set-server = utf8

socket = /data/3306/mysql.sock

basedir = /application/mysql

datadir = /data/3306/data

log-error=/data/3306/mysql_err.log

pid-file=/data/3306/mysql.pid

log_slave_updates = 1

log-bin = /data/3306/mysql-bin

binlog_format = mixed

binlog_cache_size = 4M

max_binlog_cache_size = 8M

max_binlog_size = 1G

expire_logs_days = 90

binlog-ignore – db = mysql

binlog-ignore – db = information_schema

key_buffer_size = 384M

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

join_buffer_size = 2M

thread_cache_size = 8

query_cache_size = 32M

query_cache_limit = 2M

query_cache_min_res_unit = 2k

thread_concurrency = 32

table_cache = 614

table_open_cache = 512

open_files_limit = 10240

back_log = 600

max_connections = 5000

max_connect_errors = 6000

external-locking = FALSE

max_allowed_packet =16M

thread_stack = 192K

transaction_isolation = READ-COMMITTED

tmp_table_size = 256M

max_heap_table_size = 512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

long_query_time = 2

slow_query_log

slow_query_log_file = /data/3306/slow.log

skip-name-resolv

skip-locking

skip-networking

server-id = 1

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 512M

innodb_data_file_path = ibdata1:256M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 16M

innodb_log_file_size = 128M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

[mysqldump]

quick

max_allowed_packet = 64M

[mysql]

no – auto – rehash

MySQL上線后根據(jù)status狀態(tài)進(jìn)行優(yōu)化

MySQL數(shù)據(jù)庫上線后，可以等其穩(wěn)定運行一段時間后再根據(jù)服務(wù)器的status狀態(tài)進(jìn)行適當(dāng)優(yōu)化，我們可以用如下命令列出MySQL服務(wù)器運行的各種狀態(tài)值：

mysql > show global status;

我個人比較喜歡的用法是 show status like ‘查詢%’;

1.慢查詢

有時我們?yōu)榱硕ㄎ幌到y(tǒng)中效率比較低下的Query語法，需要打開慢查詢?nèi)罩?，也就是Slow Que-ry log。打開慢查詢?nèi)罩镜南嚓P(guān)命令如下：

mysql> show variables like ‘%slow%’;

+———————+—————————————–+

| Variable_name | Value |

+———————+—————————————–+

| log_slow_queries | ON |

| slow_launch_time | 2 |

+———————+—————————————–+

mysql> show global status like ‘%slow%’;

+———————+——-+

| Variable_name | Value |

+———————+——-+

| Slow_launch_threads | 0 |

| Slow_queries | 2128 |

+———————+——-+

打開慢查詢?nèi)罩究赡軙ο到y(tǒng)性能有一點點影響，如果你的MySQL是主從結(jié)構(gòu)，可以考慮打開其中一臺從服務(wù)器的慢查詢?nèi)罩荆@樣既可以監(jiān)控慢查詢，對系統(tǒng)性能影響也會很小。另外，可以用MySQL自帶的命令mysqldumpslow進(jìn)行查詢。比如：下面的命令可以查出訪問次數(shù)最多的20個SQL語句：

mysqldumpslow -s c -t 20 host-slow.log

2.連接數(shù)

我們?nèi)绻?jīng)常遇見MySQL：ERROR1040：Too manyconnections的情況，一種情況是訪問量確實很高，MySQL服務(wù)器扛不住了，這個時候就要考慮增加從服務(wù)器分散讀壓力。另外一種情況是MySQL配置文件中max_connections的值過小。來看一個例子。

mysql>
show variables like ‘max_connections’; 

+—————–+——-+

| Variable_name | Value |

+—————–+——-+

| max_connections | 800 |

+—————–+——-+

這臺服務(wù)器最大連接數(shù)是256，然后查詢一下該服務(wù)器響應(yīng)的最大連接數(shù)；

mysql> show global status like ‘Max_used_connections’;

+———————-+——-+

| Variable_name | Value |

+———————-+——-+

| Max_used_connections | 245 |

+———————-+——-+

MySQL服務(wù)器過去的最大連接數(shù)是245，沒有達(dá)到服務(wù)器連接數(shù)的上線800，不會出現(xiàn)1040錯誤。

Max_used_connections /max_connections * 100% = 85%

最大連接數(shù)占上限連接數(shù)的85%左右,如果發(fā)現(xiàn)比例在10%以下，則說明MySQL服務(wù)器連接數(shù)的上限設(shè)置得過高了。

3.key_buffer_size

key_buffer_size是設(shè)置MyISAM表索引緩存空間的大小，此參數(shù)對MyISAM表性能影響最大。下面是一臺MyISAM為主要存儲引擎服務(wù)器的配置：

mysql> show variables like ‘key_buffer_size’;

+—————–+———–+

| Variable_name | Value |

+—————–+———–+

| key_buffer_size | 536870912 |

+—————–+———–+

從上面可以看出，分配了512MB內(nèi)存給key_buffer_size。再來看key_buffer_size的使用情況：

mysql> show global status like ‘key_read%’;

+——————-+————–+

| Variable_name | Value |

+——————-+——-+

| Key_read_requests | 27813678766 |

| Key_reads | 6798830 |

+——————-+————–+

一共有27813678766個索引讀取請求，有6798830個請求在內(nèi)存中沒有找到，直接從硬盤讀取索引。

key_cache_miss_rate = key_reads / key_read_requests * 100%

比如上面的數(shù)據(jù)，key_cache_miss_rate為0.0244%，4000%個索引讀取請求才有一個直接讀硬盤，效果已經(jīng)很好了，key_cache_miss_rate在0.1%以下都很好，如果key_cache_miss_rate在0.01%以下的話，則說明key_buffer_size分配得過多，可以適當(dāng)減少。

4.臨時表

當(dāng)執(zhí)行語句時，關(guān)于已經(jīng)被創(chuàng)建了隱含臨時表的數(shù)量，我們可以用如下命令查詢其具體情況：

mysql> show global status like ‘created_tmp%’;

+————————-+———-+

| Variable_name | Value |

+————————-+———-+

| Created_tmp_disk_tables | 21119 |

| Created_tmp_files | 6 |

| Created_tmp_tables | 17715532 |

+————————-+———-+

每次創(chuàng)建臨時表時，Created_tmp_table都會增加，如果磁盤上創(chuàng)建臨時表，Created_tmp_disk_tables也會增加。Created_tmp_files表示MySQL服務(wù)創(chuàng)建的臨時文件數(shù)，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_files * 100% <= 25%

比如上面的服務(wù)器Created_tmp_disk_tables / Created_tmp_files * 100% =1.20%，就相當(dāng)不錯。我們在看一下MySQL服務(wù)器對臨時表的配置：

mysql> show variables where Variable_name in (‘tmp_table_size’,’max_heap_table_size’);

+———————+———+

| Variable_name | Value |

+———————+———+

| max_heap_table_size | 2097152 |

| tmp_table_size | 2097152 |

+———————+———+

5.打開表的情況

Open_tables表示打開表的數(shù)量，Opened_tables表示打開過的表數(shù)量，我們可以用如下命令查看其具體情況：

mysql> show global status like ‘open%tables%’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_tables | 351 |

| Opened_tables | 1455 |

如果Opened_tables數(shù)量過大，說明配置中table_open_cache的值可能太小。我們查詢下服務(wù)器table_open_cache;

mysql> show variables like ‘table_open_cache’;

+——————+——-+

| Variable_name | Value |

+——————+——-+

| table_open_cache | 2048 |

+——————+——-+

比較合適的值為：

open_tables / opened_tables* 100% > = 85%

open_tables / table_open_cache* 100% < = 95%

6.進(jìn)程使用情況

如果我們在MySQL服務(wù)器的配置文件中設(shè)置了thread_cache_size，當(dāng)客戶端斷開時，服務(wù)器處理此客戶請求的線程將會緩存起來以響應(yīng)一下客戶而不是銷毀(前提是緩存數(shù)未達(dá)上線)Thread_created表示創(chuàng)建過的線程數(shù)，我們可以用如下命令查看：

mysql> show global status like ‘thread%’;

+——————-+——-+

| Variable_name | Value |

+——————-+——-+

| Threads_cached | 40 |

| Threads_connected | 1 |

| Threads_created | 330 |

| Threads_running | 1 |

+——————-+——-+

如果發(fā)現(xiàn)Threads_created的值過大的話，表明MySQL服務(wù)器一直在創(chuàng)建線程，這也是比較耗費資源的，可以適當(dāng)增大配置文件中thread_cache_size的值。查詢服務(wù)器thread_cache_size配置如下：

mysql> show variables like ‘thread_cache_size’;

+——————-+——-+

| Variable_name | Value |

+——————-+——-+

| thread_cache_size | 100 |

+——————-+——-+

示例中的MySQL服務(wù)器還是挺健康的。

7.查詢緩存(query cache)

它主要涉及兩個參數(shù)，query_cache_size是設(shè)置MySQL的Query Cache大小，query_cache_type是設(shè)置使用查詢緩存的類型，我們可以用如下命令查看其具體情況：

mysql> show global status like ‘qcache%’;

+————————-+———–+

| Variable_name | Value |

+————————-+———–+

| Qcache_free_blocks | 22756 |

| Qcache_free_memory | 76764704 |

| Qcache_hits | 213028692 |

| Qcache_inserts | 208894227 |

| Qcache_lowmem_prunes | 4010916 |

| Qcache_not_cached | 13385031 |

| Qcache_queries_in_cache | 43560 |

| Qcache_total_blocks | 111212 |

+————————-+———–+

MySQL查詢緩存變量的相關(guān)解釋如下：

Qcache_free_blocks：
緩存中相領(lǐng)內(nèi)存快的個數(shù)。數(shù)目大說明可能有碎片。flush query cache會對緩存中的碎片進(jìn)行整理，從而得到一個空間塊。

Qcache_free_memory：緩存中的空閑空間。

Qcache_hits：多少次命中。通過這個參數(shù)可以查看到Query Cache的基本效果。

Qcache_inserts：插入次數(shù)，沒插入一次查詢時就增加1。命中次數(shù)除以插入次數(shù)就是命中比率。

Qcache_lowmem_prunes：多少條Query因為內(nèi)存不足而被清楚出Query Cache。通過Qcache_lowmem_prunes和Query_free_memory相互結(jié)合，能夠更清楚地了解到系統(tǒng)中Query Cache的內(nèi)存大小是否真的足夠，是否非常頻繁地出現(xiàn)因為內(nèi)存不足而有Query被換出的情況。

Qcache_not_cached：不適合進(jìn)行緩存的查詢數(shù)量，通常是由于這些查詢不是select語句或用了now()之類的函數(shù)。

Qcache_queries_in_cache：當(dāng)前緩存的查詢和響應(yīng)數(shù)量。

Qcache_total_blocks：緩存中塊的數(shù)量。

我們在查詢一下服務(wù)器上關(guān)于query_cache的配置命令：

mysql> show variables like ‘query_cache%’;

+——————————+———+

| Variable_name | Value |

+——————————+———+

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 2048 |

| query_cache_size | 2097152 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

+——————————+———+

字段解釋如下：

query_cache_limit：超過此大小的查詢將不緩存。

query_cache_min_res_unit：緩存塊的最小值。

query_cache_size：查詢緩存大小。

query_cache_type：緩存類型，決定緩存什么樣的查詢，示例中表示不緩存select sql_no_cache查詢。

query_cache_wlock_invalidat：表示當(dāng)有其他客戶端正在對MyISAM表進(jìn)行寫操作，讀請求是要等WRITE LOCK釋放資源后再查詢還是允許直接從Query Cache中讀取結(jié)果，默認(rèn)為OFF（可以直接從Query Cache中取得結(jié)果。）

query_cache_min_res_unit的配置是一柄雙刃劍，默認(rèn)是4KB，設(shè)置值大對大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費。

查詢緩存碎片率 = Qcache_free_blocks /Qcache_total_blocks * 100%

如果查詢碎片率超過20%，可以用 flush query cache 整理緩存碎片，或者試試減少query_cache_min_res_unit，如果你查詢都是小數(shù)據(jù)庫的話。

查詢緩存利用率 = (Qcache_free_size – Qcache_free_memory)/query_cache_size * 100%

查詢緩存利用率在25%一下的話說明query_cache_size設(shè)置得過大，可適當(dāng)減少;查詢緩存利用率在80%以上而且Qcache_lowmem_prunes > 50的話則說明query_cache_size可能有點小，不然就是碎片太多。

查詢命中率
= (Qcache_hits – Qcache_insert)/Qcache)hits * 100%

示例服務(wù)器中的查詢緩存碎片率等于20%左右，查詢緩存利用率在50%，查詢命中率在2%，說明命中率很差，可能寫操作比較頻繁，而且可能有些碎片。

8.排序使用情況

它表示系統(tǒng)中對數(shù)據(jù)進(jìn)行排序時所用的Buffer，我們可以用如下命令查看：

mysql> show global status like ‘sort%’;

+——————-+———-+

| Variable_name | Value |

+——————-+———-+

| Sort_merge_passes | 10 |

| Sort_range | 37431240 |

| Sort_rows | 6738691532 |

| Sort_scan | 1823485 |

+——————-+———-+

Sort_merge_passes包括如下步驟：MySQL首先會嘗試在內(nèi)存中做排序，使用的內(nèi)存大小由系統(tǒng)變量sort_buffer_size來決定，如果它不夠大則把所有的記錄都讀在內(nèi)存中，而MySQL則會把每次在內(nèi)存中排序的結(jié)果存到臨時文件中，等MySQL找到所有記錄之后，再把臨時文件中的記錄做一次排序。這次再排序就會增加sort_merge_passes。實際上，MySQL會用另外一個臨時文件來存儲再次排序的結(jié)果，所以我們通常會看到sort_merge_passes增加的數(shù)值是建臨時文件數(shù)的兩倍。因為用到了臨時文件，所以速度可能會比較慢，增大sort_buffer_size會減少sort_merge_passes和創(chuàng)建臨時文件的次數(shù)，但盲目地增大sort_buffer_size并不一定能提高速度。

9.文件打開數(shù)(open_files)

我們現(xiàn)在處理MySQL故障時，發(fā)現(xiàn)當(dāng)Open_files大于open_files_limit值時，MySQL數(shù)據(jù)庫就會發(fā)生卡住的現(xiàn)象，導(dǎo)致Nginx服務(wù)器打不開相應(yīng)頁面。這個問題大家在工作中應(yīng)注意，我們可以用如下命令查看其具體情況：

show global status like ‘open_files’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_files | 1481 |

+—————+——-+

mysql> show global status like ‘open_files_limit’;

+——————+——-+

| Variable_name | Value |

+——————+——–+

| Open_files_limit | 4509 |

+——————+——–+

比較合適的設(shè)置是：Open_files / Open_files_limit * 100% < = 75%

10.InnoDB_buffer_pool_cache合理設(shè)置

InnoDB存儲引擎的緩存機(jī)制和MyISAM的最大區(qū)別就在于，InnoDB不僅僅緩存索引，同時還會緩存實際的數(shù)據(jù)。此參數(shù)用來設(shè)置InnoDB最主要的Buffer的大小，也就是緩存用戶表及索引數(shù)據(jù)的最主要緩存空間，對InnoDB整體性能影響也最大。

無論是MySQL官方手冊還是網(wǎng)絡(luò)上許多人分享的InnoDB優(yōu)化建議，都是簡單地建議將此值設(shè)置為整個系統(tǒng)物理內(nèi)存的50%~80%。這種做法其實不妥，我們應(yīng)根據(jù)實際的運行場景來正確設(shè)置此項參數(shù)。

MySQL優(yōu)化小思想

很多時候我們會發(fā)現(xiàn)，通過參數(shù)設(shè)置進(jìn)行性能優(yōu)化所帶來的性能提升，并不如許多人想象的那樣會產(chǎn)生質(zhì)的飛躍，除非是之前的設(shè)置存在嚴(yán)重不合理的情況。我們不能將性能調(diào)優(yōu)完全依托與通過DBA在數(shù)據(jù)庫上線后進(jìn)行參數(shù)調(diào)整，而應(yīng)該在系統(tǒng)設(shè)計和開發(fā)階段就盡可能減少性能問題。(重點在于前期架構(gòu)合理的設(shè)計及開發(fā)的程序合理)

MySQL數(shù)據(jù)庫的可擴(kuò)展架構(gòu)方案

如果憑借MySQL的優(yōu)化任無法頂住壓力，這個時候我們就必須考慮MySQL的可擴(kuò)展性架構(gòu)了(有人稱為MySQL集群)它有以下明顯的優(yōu)勢：

• 成本低，很容易通過價格低廉Pc server搭建出一個處理能力非常強(qiáng)大的計算機(jī)集群。
  

• 不太容易遇到瓶頸，因為很容易通過添加主機(jī)來增加處理能力。
  

• 單節(jié)點故障對系統(tǒng)的整體影響較小。
  

目前可行的方案如下：

（1）MySQL Cluter

其特點為可用性非常高，性能非常好。每份數(shù)據(jù)至少可在不同主機(jī)上存一份副本，且冗余數(shù)據(jù)拷貝實時同步。但它的維護(hù)非常復(fù)雜，存在部分Bug，目前還不適合比較核心的線上系統(tǒng)，所以暫時不推薦。

（2）DRBD磁盤網(wǎng)絡(luò)鏡像方案

其特點為軟件功能強(qiáng)大，數(shù)據(jù)可在底層快設(shè)備級別跨物理主機(jī)鏡像，且可根據(jù)性能可靠性要求配置不同級別的同步。I/O操作會保持順序，可滿足數(shù)據(jù)庫對數(shù)據(jù)一致性的苛刻要求。但非分布式文件系統(tǒng)環(huán)境無法支持鏡像數(shù)據(jù)同時可見，性能和可靠性兩者互相矛盾，無法適用于性能和可靠性要求都比較苛刻的環(huán)境，維護(hù)成本高于MySQL Replication。另外，DRBD是官方推薦的可用于MySQL的搞可用方案之一，大家可根據(jù)實際環(huán)境來考慮是否部署。

（3）MySQL Replication

在工作中，此種MySQL搞可用、高擴(kuò)展性架構(gòu)也是用得最多的，我也推薦此方案，一主多從、雙主多從是生產(chǎn)環(huán)境常見的高可用架構(gòu)方案。