MySQL資料庫的最佳化

MySQL資料庫的最佳化

我們究竟應該如何對MySQL資料庫進行最佳化？下面我就從MySQL對硬體的選擇、MySQL的安裝、my.cnf的最佳化、MySQL如何進行架構設計及資料切分等方面來說明這個問題。

伺服器物理硬體的最佳化

在挑選硬體伺服器時，我們應該從下面幾個方面著重對MySQL伺服器的硬體設定進行最佳化，也就是說將項目中的資金著重投入到如下幾處：

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

2、CPU對於MySQL的影響也是不容忽視的，建議選擇運算能力強悍的CPU。推薦使用DELL R710（雙四核），商家的賣點也是其強大的虛擬化和資料庫能力。

3、對於一台使用MySQL的Database Server來說，建議伺服器的記憶體不要小於2GB，推薦使用4GB以上的實體記憶體，不過記憶體對於現在的伺服器而言可以說是一個可以忽略的問題，如果是高端伺服器，基本上記憶體都超過了32GB，我們的資料流伺服器都是32GB記憶體。

我在工作中用的比較多的資料庫伺服器是DELL R710/R720，其穩定性和效能都不錯，同時我也發現許多同行都是採用它作為資料庫的伺服器，所以在這裡也向大家推薦一下。

MySQL應採用編譯方式安裝

關於MySQL資料庫的線上環境安裝，我建議採取編譯安裝的方式，這樣效能會有較大的提升。伺服器系統則建議CentOS6.7 X86_64，源碼包的編譯參數會預設以Debug模式產生二進位代碼，而Debug模式給MySQL帶來的效能損失是比較大的，所以當我們編譯準備安裝的產品代碼時，一定不要忘記使用–without-debug參數禁止Debug模式。如果把–with-mysqld-ldflags和–with-client-ld-flags兩個編譯參數設定為–all-static的話，可以告訴編譯器以靜態方式編譯，編譯結果將得到最高的效能。使用靜態編譯和使用動態編譯的代碼相比，效能差距可能會達到5%至10%之多。在後面我會跟大家分享我們線上MySQL資料庫的編譯參數，大家可以參考下，然後根據自己的線上環境自行修改內容。

MySQL設定檔最佳化

[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服務啟動的參數，它涉及的方面很多，其中有MySQL的目錄和檔案，通訊、網路、資訊安全，記憶體管理、最佳化、查詢快取區，還有MySQL日誌設定等。

user = mysql #mysql_safe指令碼使用MySQL運行使用者(編譯時間–user=mysql指定),推薦使用mysql使用者。

port = 3306 #MySQL服務運行時的連接埠號碼。建議更改預設連接埠,預設容易遭受攻擊。

socket = /data/3306/mysql.sock #socket檔案是在Linux/Unix環境下特有的，使用者在Linux/Unix環境下用戶端串連可以不通過TCP/IP網路而直接使用unix socket串連MySQL。

basedir = /application/mysql #mysql程式所存放路徑,常用於存放mysql啟動、設定檔、日誌等

datadir = /data/3306/data #MySQL資料存放檔案(極其重要)

character-set-server = utf8 #資料庫和資料庫表的預設字元集。(推薦utf8,以免導致亂碼)

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

pid-file=/data/3306/mysql_xuliangwei.pid #MySQL_pid檔案記錄的是當前mysqld進程的pid，pid亦即 ProcessID。

skip-locking #避免MySQL的外部鎖定，減少出錯幾率，增強穩定性。

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

skip-networking #開啟該選項可以徹底關閉MySQL的TCP/IP串連方式，如果Web伺服器是以遠端連線的方式訪問MySQL資料庫伺服器的，則不要開啟該選項，否則無法正常串連！

open_files_limit = 1024 #MySQLd能開啟檔案的最大個數,如果出現too mant open files之類的就需要調整該值了。

back_log = 384 #back_log參數是值指出在MySQL暫時停止回應新請求之前，短時間內的多少個請求可以被存在堆棧中。如果系統在短時間內有很多串連，則需要增加該參數的值，該參數值指定到來的TCP/IP串連的監聽隊列的大小。不同的作業系統在這個隊列的大小上有自己的限制。如果試圖將back_log設定得高於作業系統的限制將是無效的，其預設值為50.對於Linux系統而言，推薦設定為小於512的整數。

max_connections = 800 #指定MySQL允許的最大串連進程數。如果在訪問部落格時經常出現
Too Many Connections的錯誤提示，則需要增大該參數值。

max_connect_errors = 6000 #設定每個主機的串連請求異常中斷的最大次數，當超過該次數，MySQL伺服器將禁止host的串連請求，直到MySQL伺服器重啟或通過flush hosts命令清空此host的相關資訊。

wait_timeout = 120 #指定一個請求的最大連線時間，對於4GB左右記憶體的伺服器來說，可以將其設定為5~10。

table_cache = 614K #table_cache指示表高速緩衝區的大小。當MySQL訪問一個表時，如果在MySQL緩衝區還有空間，那麼這個表就被開啟並放入表緩衝區，這樣做的好處是可以更快速地訪問表中的內容。一般來說，可以查看資料庫運行峰值時間的狀態值Open_tables和Open_tables，用以判斷是否需要增加table_cache的值，即如果Open_tables接近table_cache的時候，並且Opened_tables這個值在逐步增加，那就要考慮增加這個值的大小了。

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

max_allowed_packet =16M #伺服器一次能處理最大的查詢包的值，也是伺服器程式能夠處理的最大查詢

sort_buffer_size = 1M #設定查詢排序時所能使用的緩衝區大小，系統預設大小為2MB。

注意：該參數對應的分配記憶體是每個串連獨佔的，如果有100個串連，那麼實際分配的總排序緩衝區大小為100 x6=600MB。所以，對於記憶體在4GB左右的伺服器來說，推薦將其設定為6MB~8MB

join_buffer_size = 8M #聯集查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該參數對應的分配記憶體也是每個串連獨享。

thread_cache_size = 64 #設定Thread Cache池中可以緩衝的連接線程最大數量，可設定為0~16384，預設為0.這個值表示可以重新利用儲存在緩衝中線程的數量，當中斷連線時如果緩衝中還有空間，那麼用戶端的線程將被放到緩衝中;如果線程重新被請求，那麼請求將從緩衝中讀取,如果緩衝中是空的或者是新的請求，那麼這個線程將被重新建立，如果有很多線程，增加這個值可以改善系統效能。通過比較Connections和Threads_created狀態的變數，可以看到這個變數的作用。我們可以根據實體記憶體設定規則如下:1GB記憶體我們配置為8,2GB記憶體我們配置為16,3GB我們配置為32,4GB或4GB以上我們給此值為64或更大的值。

thread_concurrency = 8 #該參數取值為伺服器邏輯CPU數量x 2，在本例中，伺服器有兩個物理CPU，而每個物理CPU又支援H.T超執行緒，所以實際取值為4 x 2 = 8。這也是雙四核主流伺服器的配置。

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

query_cache_limit = 2M #只有小於此設定值的結果才會被緩衝

query_cache_min_res_unit = 2k #設定查詢快取分配記憶體的最小單位，要適當第設定此參數，可以做到為減少記憶體快的申請和分配次數，但是設定過大可能導致記憶體片段數值上升。預設值為4K，建議設定為1K~16K。

default_table_type = InnoDB #預設表的類型為InnoDB

thread_stack = 256K #設定MySQL每個線程的堆棧大小，預設值足夠大，可滿足普通操作。可設定範圍為128KB至4GB，預設為192KB

#transaction_isolation = Level #資料庫隔離等級 (READ UNCOMMITTED(讀取未提交內容) READ COMMITTED(讀取提交內容) REPEATABLE READ(可重讀) SERIALIZABLE(可序列化))

tmp_table_size = 64M #設定記憶體暫存資料表最大值。如果超過該值，則會將暫存資料表寫入磁碟，其範圍1KB到4GB。

max_heap_table_size = 64M #獨立的記憶體表所允許的最大容量。

table_cache = 614 #給經常訪問的表分配的記憶體，實體記憶體越大，設定就越大。調大這個值，一般情況下可以降低磁碟IO，但相應的會佔用更多的記憶體,這裡設定為614。

table_open_cache = 512  #設定表快取的數目。每個串連進來，都會至少開啟一個表緩衝。因此， table_cache 的大小應與max_connections 的設定有關。例如，對於 200 個並行啟動並執行串連，應該讓表的緩衝至少有 200 × N ，這裡 N 是應用可以執行的查詢的一個聯結中表的最大數量。此外，還需要為暫存資料表和檔案保留一些額外的檔案描述符。

long_query_time = 1 #慢查詢的執行用時上限,預設設定是10s,推薦(1s~2s)

log_long_format #沒有使用索引的查詢也會被記錄。(推薦,根據業務來調整)

log-slow-queries = /data/3306/slow.log #慢查詢記錄檔路徑(如果開啟慢查詢,建議開啟此日誌)

log-bin = /data/3306/mysql-bin #logbin資料庫的動作記錄,例如update、delete、create等都會儲存到binlog日誌,通過logbin可以實現增量恢複

relay-log = /data/3306/relay-bin #relay-log日誌記錄的是從伺服器I/O線程將主���務器的二進位日誌讀取過來記錄到從伺服器本地檔案,然後SQL線程會讀取relay-log日誌的內容並應用到從伺服器

relay-log-info-file = /data/3306/relay-log.info #從伺服器用於記錄中繼日誌相關資訊的檔案,預設名為資料目錄中的relay-log.info。

binlog_cache_size = 4M #在一個事務中binlog為了記錄sql狀態所持有的cache大小，如果你經常使用大的，多聲明的事務，可以增加此值來擷取更大的效能，所有從事務來的狀態都被緩衝在binlog緩衝中，然後再提交後一次性寫入到binlog中，如果事務比此值大，會使用磁碟上的臨時檔案來替代，此緩衝在每個連結的事務第一次更新狀態時被建立。

max_binlog_cache_size = 8M #最大的二進位Cache日誌緩衝尺寸。

max_binlog_size = 1G #二進位記錄檔的最大長度(預設設定1GB)一個二進位檔案資訊超過了這個最大長度之前,MySQL伺服器會自動提供一個新的二進位記錄檔接續上。

expire_logs_days = 7 #超過7天的binlog,mysql程式自動刪除(如果資料重要,建議不要開啟該選項)

key_buffer_size = 256M #指定用於索引的緩衝區大小，增加它可得到更好的索引處理效能。對於記憶體在4GB左右的伺服器來說，該參數可設定為256MB或384MB。

注意：如果該參數值設定得過大反而會使伺服器的整體效率降低！

read_buffer_size = 4M #讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該參數對應的分配記憶體也是每個串連獨享。

read_rnd_buffer_size = 16M #設定進行隨機讀的時候所使用的緩衝區。此參數和read_buffer_size所設定的Buffer相反，一個是順序讀的時候使用，一個是隨機讀的時候使用。但是兩者都是針對與線程的設定，每個線程都可以產生兩種Buffer中的任何一個。預設值256KB，最大值4GB。

bulk_insert_buffer_size = 8M #如果經常性的需要使用批量插入的特殊語句來插入資料,可以適當調整參數至16MB~32MB,建議8MB。

#myisam_sort_buffer_size = 8M #設定在REPAIR Table或用Create index建立索引或 Alter table的過程中排序索引所分配的緩衝區大小，可設定範圍4Bytes至4GB，預設為8MB

lower_case_table_names = 1 #實現MySQL不區分大小。(發開需求–建議開啟)

slave-skip-errors = 1032,1062 #從庫可以跳過的錯誤數字值(mysql錯誤以數字代碼反饋,全的mysql錯誤碼大全,以後會發布至部落格)。

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

server-id = 1 #表示原生序號為1,如果做主從，或者多執行個體,serverid一定不能相同。

myisam_sort_buffer_size = 128M  #當需要對於執行REPAIR, OPTIMIZE, ALTER 語句重建索引時，MySQL會分配這個緩衝，以及LOAD DATA INFILE會載入到一個新表，它會根據最大的配置認真的分配的每個線程。

myisam_max_sort_file_size = 10G #當重建立索引（REPAIR，ALTER，TABLE，或者LOAD，DATA，TNFILE）時，MySQL被允許使用臨時檔案的最大值。

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

myisam_recover #自動檢查和修複沒有適當關閉的 MyISAM 表.

innodb_additional_mem_pool_size = 4M #用來設定InnoDB儲存的資料目錄資訊和其他內部資料結構的記憶體池大小。應用程式裡的表越多，你需要在這裡面分配越多的記憶體。對於一個相對穩定的應用，這個參數的大小也是相對穩定的，也沒有必要預留非常大的值。如果InnoDB用廣了這個池內的記憶體，InnoDB開始從作業系統分配記憶體，並且往MySQL錯誤記錄檔寫警告資訊。預設為1MB，當發現錯誤記錄檔中已經有相關的警告資訊時，就應該適當的增加該參數的大小。

innodb_buffer_pool_size = 64M #InnoDB使用一個緩衝池來儲存索引和未經處理資料，設定越大，在存取表裡面資料時所需要的磁碟I/O越少。強烈建議不要武斷地將InnoDB的Buffer Pool值配置為實體記憶體的50%~80%，應根據具體環境而定。

innodb_data_file_path = ibdata1:128M:autoextend #設定配置一個可擴充大小的尺寸為128MB的單獨檔案，名為ibdata1.沒有給出檔案的位置，所以預設的是在MySQL的資料目錄內。

innodb_file_io_threads = 4 #InnoDB中的檔案I/O線程。通常設定為4，如果是windows可以設定更大的值以提高磁碟I/O

innodb_thread_concurrency = 8 #你的伺服器有幾個CPU就設定為幾，建議用預設設定，一般設為8。

innodb_flush_log_at_trx_commit = 1 #設定為0就等於innodb_log_buffer_size隊列滿後在統一儲存，預設為1，也是最安全的設定。

innodb_log_buffer_size = 2M #預設為1MB，通常設定為8~16MB就足夠了。

innodb_log_file_size = 32M #確定記錄檔的大小，更大的設定可以提高效能，但也會增加恢複資料庫的時間。

innodb_log_files_in_group = 3 #為提高效能,MySQL可以以迴圈方式將記錄檔寫到多個檔案。推薦設定為3。

innodb_max_dirty_pages_pct = 90 #InnoDB主線程重新整理緩衝池中的資料。

innodb_lock_wait_timeout = 120 #InnoDB事務被復原之前可以等待一個鎖定的逾時秒數。InnoDB在它自己的鎖定表中自動檢測事務死結並且復原事務。InnoDB用locak tables 語句注意到鎖定設定。預設值是50秒。

innodb_file_per_table = 0 #InnoDB為獨立資料表空間模式，每個資料庫的每個表都會產生一個資料空間。0關閉，1開啟。

獨立資料表空間優點：

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

2、每個表的資料和索引都會存在自己的資料表空間中。

3、可以實現單表在不同的資料庫中移動。

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

[mysqldump]

quick

max_allowed_packet = 2M #設定在網路傳輸中一次訊息傳輸量的最大值。系統預設值為1MB，最大值是1GB，必須設定為1024的倍數。單位為位元組。

[mysqld_safe]

值得注意：

•強烈建議不要武斷地將InnoDB的Buffer Pool值配置為實體記憶體的50%~80%，應根據具體環境而定。

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

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

不過很多時候需要具體情況具體分析，其他參數的變更我們可以等MySQL上線穩定一段時間後在根據status值進行調整。

 

電商MySQL資料庫設定檔

這是一份電子商務網站MySQL資料庫調整後所啟動並執行設定檔/etc/my.cnf(伺服器為DELL R710、16GB記憶體、RAID10)，大家可以根據實際的MySQL資料庫硬體情況進行調整設定檔如下：

[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

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁