柚子快報(bào)邀請碼778899分享：MySQL 數(shù)據(jù)庫的優(yōu)化

http://yzkb.51969.com/

目錄

一. 常見故障

單實(shí)例常見故障

1. 故障一

2. 故障二

3.故障三

4. 故障四

5. 故障五

6.故障六

7.故障七

8.故障八

主從環(huán)境常見故障

1.故障一

2. 故障二

3. 故障三

二. 優(yōu)化

1.硬件方面

1.1 關(guān)于CPU

1.2 關(guān)于內(nèi)存

1.3 關(guān)于磁盤

2. 配置文件優(yōu)化

關(guān)于引擎是 innodb 的優(yōu)化如下：

關(guān)于引擎是 myisam 的優(yōu)化如下

3.SQL優(yōu)化

4. 架構(gòu)優(yōu)化

一. 常見故障

單實(shí)例常見故障

1. 故障一

故障現(xiàn)象

ERROR 2002 (HY000): Can't connect to local MySQL server through socket/data/mysql/mysql.sock'(2)

問題分析

數(shù)據(jù)庫未啟動或者數(shù)據(jù)庫端口被防火墻攔截

解決方法

啟動數(shù)據(jù)庫或者防火墻開放數(shù)據(jù)庫監(jiān)聽端口

2. 故障二

故障現(xiàn)象

ERROR 1045 (28000): Access denied for user 'root'@"ocalhost (using password:

NO)

問題分析

密碼不正確或者沒有權(quán)限訪問

解決方法

修改my.cnf主配置文件，在[mysqld]下添加 skip-grant-tables

????????update更新user表authentication_string字段

????????重新授權(quán)

3.故障三

故障現(xiàn)象

在使用遠(yuǎn)程連接數(shù)據(jù)庫時(shí)偶爾會發(fā)生遠(yuǎn)程連接數(shù)據(jù)庫很慢的問題

問題分析

DNS解析慢、客戶端連接過多

解決方法

修改my.cnf主配置文件(增加skip-name-resolve參數(shù))數(shù)據(jù)庫授權(quán)禁止使用主機(jī)名

4. 故障四

故障現(xiàn)象

Can't open file:'xxx_forums.MYI'.(errno: 145)

問題分析

服務(wù)器非正常關(guān)機(jī)，數(shù)據(jù)庫所在空間已滿，或一些其它未知的原因?qū)?shù)據(jù)庫表造成了損壞因拷貝數(shù)據(jù)庫導(dǎo)致文件的屬組發(fā)生變化解決方法

修復(fù)數(shù)據(jù)表 (myisamchk、phpMyAdmin)修改文件的屬組

5. 故障五

故障現(xiàn)象

ERROR 1129 (HY000): Host 'xxx.xxx.xxx.xxx' is blocked because of many

connection errors; unblock with 'mysqladmin flush-hosts'

問題分析

超出最大連接錯(cuò)誤數(shù)量限制

解決方法

清除緩存(flush-hosts關(guān)鍵字)修改mysql配置文件 (max_connect_errors=1000)

6.故障六

故障現(xiàn)象

Too many connections

問題分析

連接數(shù)超出MySQL的最大連接限制

解決方法

修改MySQL配置文件 (max_connections=2000)臨時(shí)修改參數(shù)

set GLOBAL max_connections=2000;

7.故障七

故障現(xiàn)象

Warning:World-writable config file '/etc/my.cnf' is ignored

ERROR! MySQL is running but PlD file could not be found

問題分析

MySQL的配置文件/etc/my.cnf 權(quán)限問題

解決方法

chmod 644 /etc/my.cnf

8.故障八

故障現(xiàn)象

InnoDB:Error: page 14178 log sequence number 29455369832

InnoDB: is in the future! Current system log sequence number 29455369832

問題分析

innodb數(shù)據(jù)文件損壞

解決方法

修改 my.cnf 配置文件 (innodb_force_recovery=4)啟動數(shù)據(jù)庫后備份數(shù)據(jù)文件利用備份文件恢復(fù)數(shù)據(jù)

主從環(huán)境常見故障

1.故障一

故障現(xiàn)象

從庫的Slave_lO_Running為NO

The slave I/O thread stops because master and slave have equal MySQL serverids;

these ids must be different for replication to work (or the --replicate-same-server-id

option must be used on slave but this does not always make sense;

please check the manual before using it).

問題分析

主庫和從庫的server-id值一樣

解決方法

修改從庫的 server-id 的值，修改為和主庫不一樣重新啟動數(shù)據(jù)庫并再次同步

2. 故障二

故障現(xiàn)象

從庫的Slave_SQL_Running為NO

問題分析

主鍵沖突或者主庫刪除或更新數(shù)據(jù)，從庫內(nèi)找不到記錄，數(shù)據(jù)被修改導(dǎo)致

解決方法

方法一?

mysql> stop slave;

mysqI> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start slave;

方法二

set global read_only=true;

3. 故障三

故障現(xiàn)象

Error initializing relay log position: I/O error reading the header from

the binary log

問題分析

從庫的中繼日志 relay-bin 損壞

解決方法

手工修復(fù)，重新找到同步的 binlog 和 pos 點(diǎn)，然后重新同步即可

mysqI> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx;

二. 優(yōu)化

可以從不同角度進(jìn)行優(yōu)化：

硬件優(yōu)化MySQL配置文件優(yōu)化SQL優(yōu)化MySQL架構(gòu)優(yōu)化

1.硬件方面

服務(wù)器硬件，最主要的無非 CPU、內(nèi)存、磁盤三大關(guān)鍵因素

1.1 關(guān)于CPU

CPU 對于 MySQL 應(yīng)用，推薦使用 S.M.P.架構(gòu)的多路對稱 CPU。

例如：可以使用兩顆 Intel Xeon 3.6GHz 的 CPU?，F(xiàn)在比較推薦用 4U 的服務(wù)器來專門做數(shù)據(jù)庫服務(wù)器，不僅僅是針對于 MySQL。

1.2 關(guān)于內(nèi)存

物理內(nèi)存對于一臺使用 MySQL 的 Database Server 來說，服務(wù)器內(nèi)存建議不要小于2GB，推薦使用 4GB 以上的物理內(nèi)存。不過內(nèi)存對于現(xiàn)在的服務(wù)器而言，可以說是一個(gè)可以忽略的問題，工作中遇到了高端服務(wù)器基本上內(nèi)存都超過了 32G。

1.3 關(guān)于磁盤

磁盤尋道能力（磁盤 I/O）。以目前市場上普遍高轉(zhuǎn)速 SAS 硬盤(15000 轉(zhuǎn)/秒)為例， 這種硬盤理論上每秒尋道 15000 次，這是物理特性決定的，沒有辦法改變。 MySQL 每秒鐘都在進(jìn)行大量、復(fù)雜的查詢操作，對磁盤的讀寫量可想而知。所以通常認(rèn)為磁盤 I/O 是制約 MySQL 性能的最大因素之一，通常是使用 RAID-0+1 磁盤陣列，注意不要嘗試使用RAID-5，MySQL 在 RAID-5 磁盤陣列上的效率并不高。如果不考慮硬件的投入成本，也可以考慮固態(tài)（SSD）硬盤專門作為數(shù)據(jù)庫服務(wù)器使用。數(shù)據(jù)庫的讀寫性能肯定會提高很多。

2. 配置文件優(yōu)化

通常默認(rèn)的 my.cnf 配置文件無法發(fā)揮出 MySQL 最高的性能，所以需要根據(jù)不同的硬件進(jìn)行優(yōu)化，配置文件的優(yōu)化也是重點(diǎn)。下面是物理內(nèi)存為 32G 的數(shù)據(jù)庫優(yōu)化參數(shù)，具體從全局、二進(jìn)制日志、主從、innodb、myisam 幾個(gè)方面優(yōu)化

default-time-zone=+8:00

#默認(rèn) MySQL 使用的是系統(tǒng)時(shí)區(qū)，修改為北京時(shí)間，也就是所說的東八區(qū)

interactive_timeout = 120

#服務(wù)器關(guān)閉交互式連接前等待活動的秒數(shù)

wait_timeout = 120

#服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù)

open_files_limit = 10240

#MySQL 服務(wù)器打開文件句柄數(shù)限制

group_concat_max_len = 102400

#MySQL 默認(rèn)的拼接最大長度為 1024 個(gè)字節(jié)，由于 1024 個(gè)字節(jié)會出現(xiàn)不夠用的情況， 根據(jù)實(shí)際情況進(jìn)行修改

user=mysql

#使用 mysql 用戶運(yùn)行

character-set-server=utf8、init_connect='SET NAMES utf8'

#設(shè)置字符集為 utf8

back_log = 600

#在 MySQL 暫時(shí)停止響應(yīng)新請求之前，短時(shí)間內(nèi)的多少個(gè)請求可以被存在堆棧中。

如果系統(tǒng)在短時(shí)間內(nèi)有很多連接，則需要增大該參數(shù)的值，

該參數(shù)值指定到來的 TCP/IP 連接的監(jiān)聽隊(duì)列的大小。默認(rèn)值 50

max_connections = 5000

#MySQL 允許最大的進(jìn)程連接數(shù)，如果經(jīng)常出現(xiàn) Too Many Connections 的錯(cuò)誤提示， 則需要增大此值

max_connect_errors = 6000

#設(shè)置每個(gè)主機(jī)的連接請求異常中斷的最大次數(shù)。當(dāng)超過該次數(shù)，MySQL 服務(wù)器將禁止

host 的連接請求，

直到 MySQL 服務(wù)器重啟或通過flush hosts 命令清空此host 的相關(guān)信息

table_cache = 1024

數(shù)據(jù)表調(diào)整緩沖區(qū)大小。它設(shè)置表高速緩存的數(shù)目。每個(gè)連接進(jìn)來，都會至少打開一個(gè)表緩存。因此，table_cache 的大小與 max_connections 的設(shè)置有關(guān)。例如，對于 200 個(gè)并行運(yùn)行的連接，應(yīng)該讓表的緩存至少有 200×N。這里 N 是應(yīng)用可以執(zhí)行查詢的一個(gè)連接中表的最大數(shù)量。 此外，還需要為臨時(shí)表和文件保留一些額外的文件描述符。 當(dāng) MySQL 訪問一個(gè)表時(shí)， 如果該表在緩存中已經(jīng)被打開，則可以直接訪問緩存。如果還沒有被緩存，但是在 MySQL 表緩沖區(qū)中還有空間，那么這個(gè)表就被打開并放入表緩沖區(qū)。如果表緩存滿了，則會按照一定的規(guī)則將當(dāng)前未用的表釋放，或者臨時(shí)擴(kuò)大表緩存來存放，使用表緩存的好處是可以更快速地訪問表中的內(nèi)容。執(zhí)行 flushtables 會清空緩存的內(nèi)容。 一般來說，可以通過 showstatus 命令查看數(shù)據(jù)庫運(yùn)行峰值時(shí)間的狀態(tài)值 Open_tables 和 Opened_tables，判斷是否需要增加 table_cache 的值（其中 open_tables 是當(dāng)前打開的表的數(shù)量，Opened_tables 則是已經(jīng)打開的表的數(shù)量）。若 open_tables 接近 table_cache， 并且 Opened_tables 值在逐步增加， 那就要考慮增加這個(gè)值的大小了。還有就是Table_locks_waited 比較高的時(shí)候，也需要增加 table_cache

table_open_cache = 2048

指定表高速緩存的大小。每當(dāng) MySQL 訪問一個(gè)表時(shí)，如果在表緩沖區(qū)中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內(nèi)容

max_heap_table_size = 256M

這個(gè)變量定義了用戶可以創(chuàng)建的內(nèi)存表 (memory table) 的大小。這個(gè)值用來計(jì)算內(nèi)存表的最大行數(shù)值。這個(gè)變量支持動態(tài)改變，即 set @max_heap_table_size=#。但是對于已經(jīng)存在的內(nèi)存表就沒有什么用了，除非這個(gè)表被重新創(chuàng)建(create table)、修改(alter table)或者truncate table。服務(wù)重啟也會設(shè)置已經(jīng)存在的內(nèi)存表為全局 max_heap_table_size 的值

external-locking = false

使用 skip-external-lockingMySQL 選項(xiàng)以避免外部鎖定。該選項(xiàng)默認(rèn)開啟

max_allowed_packet = 32M

設(shè)置在網(wǎng)絡(luò)傳輸中一次消息傳輸量的最大值。系統(tǒng)默認(rèn)值為 1MB，最大值是 1GB，必須設(shè)置 1024 的倍數(shù)

sort_buffer_size = 512M

Sort_Buffer_Size 是一個(gè) connection 級參數(shù)，在每個(gè) connection（session）第一次需要使用這個(gè) buffer 的時(shí)候，一次性分配設(shè)置的內(nèi)存。Sort_Buffer_Size 并不是越大越好，由于是 connection 級的參數(shù)，過大的設(shè)置+高并發(fā)可能會耗盡系統(tǒng)內(nèi)存資源

join_buffer_size = 8M

用于表間關(guān)聯(lián)緩存的大小，和 sort_buffer_size 一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享

thread_cache_size = 300

服務(wù)器線程緩存這個(gè)值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開連接時(shí)如果緩存中還有空間,那么客戶端的線程將被放到緩存中；如果線程重新被請求，那么請求將從緩存中讀取；如果緩存中是空的或者是新的請求，那么這個(gè)線程將被重新創(chuàng)建；如果有很多新的線程，增加這個(gè)值可以改善系統(tǒng)性能。通過比較 Connections 和 Threads_created 狀態(tài)的變量，可以看到這個(gè)變量的作用。設(shè)置規(guī)則如下：1GB 內(nèi)存配置為 8，2GB 配置為 16， 3GB 配置為 32，4GB 或更高內(nèi)存，可配置更大

thread_concurrency = 8

設(shè)置 thread_concurrency 值的正確與否,對 MySQL 的性能影響很大,在多個(gè) CPU(或多核)的情況下， 錯(cuò)誤設(shè)置了 thread_concurrency 的值， 會導(dǎo)致 MySQL 不能充分利用多CPU(或多核)，出現(xiàn)同一時(shí)刻只能一個(gè) CPU 在工作的情況。thread_concurrency 應(yīng)設(shè)為CPU

核數(shù)的 2 倍。比如有一個(gè)雙核的 CPU，那么 thread_concurrency 的應(yīng)該為 4；2 個(gè)雙核的 cpu，thread_concurrency 的值應(yīng)為 8

query_cache_size = 512M

使用 MySQL 的用戶，對于這個(gè)變量一定不會陌生。前幾年的 MyISAM 引擎優(yōu)化中， 這個(gè)參數(shù)也是一個(gè)重要的優(yōu)化參數(shù)。但隨著發(fā)展，這個(gè)參數(shù)也爆露出來一些問題。機(jī)器的內(nèi)存越來越大，人們也都習(xí)慣性的把以前有用的參數(shù)分配的值越來越大。這個(gè)參數(shù)加大后也引發(fā)了一系列問題。首先分析一下 query_cache_size 的工作原理：一個(gè) SELECT 查詢在 DB 中工作后，DB 會把該語句緩存下來。當(dāng)同樣的一個(gè) SQL 再次來到 DB 里調(diào)用時(shí)，DB 在該表沒發(fā)生變化的情況下把結(jié)果從緩存中返回給 Client。這里有一個(gè)關(guān)建點(diǎn)，就是 DB 在利用Query_cache 工作時(shí)，要求該語句涉及的表在這段時(shí)間內(nèi)沒有發(fā)生變更。那如果該表在發(fā)生變更時(shí)，Query_cache 里的數(shù)據(jù)又怎么處理呢？首先要把 Query_cache 和該表相關(guān)的語句全部設(shè)置為失效，然后再寫入更新。那么如果 Query_cache 非常大，該表的查詢結(jié)構(gòu)又比較多，查詢語句失效也慢，一個(gè)更新或是 Insert 就會很慢，這樣看到的就是 Update 或是Insert 怎么這么慢了。所以在數(shù)據(jù)庫寫入量或是更新量也比較大的系統(tǒng)，該參數(shù)不適合分配過大。而且在高并發(fā)，寫入量大的系統(tǒng)，建議把該功能禁掉

query_cache_limit = 4M

指定單個(gè)查詢能夠使用的緩沖區(qū)大小，缺省為 1M

query_cache_min_res_unit = 2k

默認(rèn)是 4KB，設(shè)置值大對大數(shù)據(jù)查詢有好處，但如果查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費(fèi)，查詢緩存碎片率=Qcache_free_blocks/Qcache_total_blocks*100%。 ?如果查詢緩存碎片率超過 20%，可以用 FLUSHQUERYCACHE 整理緩存碎片，或者嘗試減小 query_cache_min_res_unit 。 如果查詢都是小數(shù)據(jù)量， 那么查詢緩存利用率 =(query_cache_size–Qcache_free_memory)/query_cache_size*100%。查詢緩存利用率在 25%以下，說明 query_cache_size 設(shè)置的過大，可適當(dāng)減小。查詢緩存利用率在 80%以上而且 Qcache_lowmem_prunes>50 的話說明 query_cache_size 可能有點(diǎn)小，要不就是碎片太多。查詢緩存命中率=(Qcache_hits–Qcache_inserts)/Qcache_hits*100%

default-storage-engine = innodb

默認(rèn)引擎，現(xiàn)在一般都是 innodb 引擎表居多

thread_stack = 192K

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

transaction_isolation = READ-COMMITTED

設(shè)定默認(rèn)的事務(wù)隔離級別，READCOMMITTEE 是讀已提交

tmp_table_size = 256M

tmp_table_size 的默認(rèn)大小是 32M。如果一張臨時(shí)表超出該大小，MySQL 產(chǎn)生一個(gè)Thetabletbl_nameisfull 形 式 的 錯(cuò)誤 ； 如 果 執(zhí) 行很 多 高 級 GROUPBY 查 詢， 增 加tmp_table_size 值。如果超過該值，則會將臨時(shí)表寫入磁盤

key_buffer_size = 1024M

指定用于索引的緩沖區(qū)大小，增加它可以得到更好的索引處理性能

read_buffer_size = 2M

MySQL 讀入緩沖區(qū)大小。對表進(jìn)行順序掃描的請求將分配一個(gè)讀入緩沖區(qū)，MySQL 會為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size 變量控制這一緩沖區(qū)的大小。如果對表的順序掃描請求非常頻繁，并且認(rèn)為頻繁掃描進(jìn)行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。和 sort_buffer_size 一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享

read_rnd_buffer_size = 256M

MySQL 的隨機(jī)讀（查詢操作）緩沖區(qū)大小。當(dāng)按任意順序讀取行時(shí)(例如，按照排序順序)，將分配一個(gè)隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢時(shí)，MySQL 會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度。如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但 MySQL 會為每個(gè)客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開銷過大

bulk_insert_buffer_size = 64M

批量插入數(shù)據(jù)緩存大小，可以有效提高插入效率，默認(rèn)為 8M。(31)skip-name-resolve 禁止域名解析，包括主機(jī)名.所以授權(quán)的時(shí)候要使用 IP 地址

ft_min_word_len = 1

從 MySQL4.0 開始就支持全文索引功能，但是 MySQL 默認(rèn)的最小索引長度是 4。如果是英文默認(rèn)值是比較合理的，但是中文絕大部分詞都是 2 個(gè)字符，這就導(dǎo)致小于 4 個(gè)字的詞都不能被索引。MySQL 全文索引是專門為了解決模糊查詢提供的，可以對整篇文章預(yù)先按照詞進(jìn)行索引，搜索效率高，能夠支持百萬級的數(shù)據(jù)檢索。 下面幾個(gè)參數(shù)時(shí)關(guān)于 MySQL 二進(jìn)制日志文件的優(yōu)化。

log-bin=mysql-bin

打開 MySQL 二進(jìn)制功能

binlog_cache_size = 4M

在事務(wù)過程中容納二進(jìn)制日志 SQL 語句的緩存大小。二進(jìn)制日志緩存是服務(wù)器支持事務(wù)存儲引擎并且服務(wù)器啟用了二進(jìn)制日志(—log-bin 選項(xiàng))的前提下為每個(gè)客戶端分配的內(nèi)存。 注意：是每個(gè) Client 都可以分配設(shè)置大小的 binlogcache 空間?？梢酝ㄟ^ MySQL 的以下兩個(gè) 狀態(tài)變量來判 斷當(dāng)前的 binlog_cache_size 的狀況 ： Binlog_cache_use 和Binlog_cache_disk_use

max_binlog_cache_size = 128M

表 示 binlog 能 夠使 用的 最大 cache 內(nèi) 存大 小。 執(zhí)行 多語 句事 務(wù)的 時(shí)候 ， max_binlog_cache_size 如 果 不 夠 大 的 話 ， 系 統(tǒng) 可 能 會 報(bào) 出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage” 的錯(cuò)誤

max_binlog_size = 1G

Binlog 日志最大值，一般來說設(shè)置為 512M 或者 1G，但不能超過 1G。該大小并不能非常嚴(yán)格控制 Binlog 大小，尤其是當(dāng)?shù)竭_(dá) Binlog 比較靠近尾部而又遇到一個(gè)較大事務(wù)的時(shí)候，系統(tǒng)為了保證事務(wù)的完整性，不可能做切換日志的動作，只能將該事務(wù)的所有 SQL 都記錄進(jìn)入當(dāng)前日志，直到該事務(wù)結(jié)束。這一點(diǎn)和Oracle 的Redo 日志有點(diǎn)不一樣，因?yàn)镺racle 的 Redo 日志所記錄的是數(shù)據(jù)文件的物理位置的變化，而且里面同時(shí)記錄了 Redo 和 Undo 相關(guān)的信息，所以同一個(gè)事務(wù)是否在一個(gè)日志中對 Oracle 來說并不關(guān)鍵。而 MySQL 在Binlog 中所記錄的是數(shù)據(jù)庫邏輯變化信息，MySQL 稱之為 Event，實(shí)際上就是帶來數(shù)據(jù)庫變化的 DML 之類的 Query 語句

sync_binlog=1

在 MySQL 中系統(tǒng)默認(rèn)的設(shè)置是 sync_binlog=0，也就是不做任何強(qiáng)制性的磁盤刷新指令，這時(shí)候的性能是最好的，但是風(fēng)險(xiǎn)也是最大的。因?yàn)橐坏┫到y(tǒng) Crash，在 binlog_cache 中的所有 binlog 信息都會被丟失。而當(dāng)設(shè)置為“1”的時(shí)候最安全，但也是性能損耗最大的設(shè)置。因?yàn)楫?dāng)設(shè)置為 1 的時(shí)候，即使系統(tǒng) Crash，也最多丟失 binlog_cache 中未完成的一個(gè)事務(wù)，對實(shí)際數(shù)據(jù)沒有任何實(shí)質(zhì)性影響。從以往經(jīng)驗(yàn)和相關(guān)測試來看，對于高并發(fā)事務(wù)的系統(tǒng)來說，“sync_binlog”設(shè)置為 0 和設(shè)置為 1 的系統(tǒng)寫入性能差距可能高達(dá) 5 倍甚至更多

binlog_format=mixed

默認(rèn)使用 statement 模式，基于 SQL 語句的復(fù)制，另外一種是基于行的復(fù)制，為提升效率，可以將以上兩種模式混合使用。一般的復(fù)制使用 STATEMENT 模式保存 binlog，對于 STATEMENT 模式無法復(fù)制的操作使用 ROW 模式保存 binlog，MySQL 會根據(jù)執(zhí)行的SQL 語句選擇日志保存方式

expire_logs_days = 7

二進(jìn)制日志只留存最近 7 天，不用人工手動刪除

log-slave-updates

這條參數(shù)只讀主從架構(gòu)適用，當(dāng)從庫 log_slave_updates 參數(shù)沒有開啟時(shí)，從庫的 binlog 不會記錄來源于主庫的操作記錄。只有開啟 log_slave_updates，從庫 binlog 才會記錄主庫同步的操作日志

slow_query_log

打開慢查詢?nèi)罩?/p>

slow_query_log_file=slow.log

慢查詢?nèi)罩疚募恢?/p>

long_query_time = 2

記錄超過 2 秒的 SQL 查詢

關(guān)于引擎是 innodb 的優(yōu)化如下：

innodb_additional_mem_pool_size = 64M

這個(gè)參數(shù)用來設(shè)置 InnoDB 存儲的數(shù)據(jù)目錄信息和其它內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小，類似于 Oracle 的 library cache。這不是一個(gè)強(qiáng)制參數(shù)，可以被突破

innodb_buffer_pool_size = 20480M

用于緩存索引和數(shù)據(jù)的內(nèi)存大小，這個(gè)選項(xiàng)的值越多越好， 數(shù)據(jù)讀寫在內(nèi)存中非?？?， 減少了對磁盤的讀寫。當(dāng)數(shù)據(jù)提交或滿足檢查點(diǎn)條件后才一次性將內(nèi)存數(shù)據(jù)刷新到磁盤中。 ? 然而內(nèi)存還有操作系統(tǒng)或數(shù)據(jù)庫其他進(jìn)程使用，推薦設(shè)置 innodb-buffer-pool-size 為服務(wù)器總可用內(nèi)存的 75%。 若設(shè)置不當(dāng)， 內(nèi)存使用可能浪費(fèi)或者使用過多。 對于繁忙的服務(wù)器， buffer pool 將劃分為多個(gè)實(shí)例以提高系統(tǒng)并發(fā)性， 減少線程間讀寫緩存的爭用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影響，當(dāng)然影響較小

innodb_data_file_path = ibdata1:1024M:autoextend

用 來 指 定 innodb tablespace 文 件 ， 如 果 我 們 不 在 my.cnf 文 件 中 指 定innodb_data_home_dir 和innodb_data_file_path 那么默認(rèn)會在datadir 目錄下創(chuàng)建ibdata1 作為 innodb tablespace

innodb_file_io_threads = 4

文件 IO 的線程數(shù)，一般為 4，但是在 Windows 下，可以設(shè)置得較大

innodb_thread_concurrency = 8

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

innodb_write_io_threads = 8

InnoDB 使用后臺線程處理數(shù)據(jù)頁上寫 I/O（輸入輸出）請求的數(shù)量。一般設(shè)置為 CPU 核數(shù)，比如 CPU 是 2 顆 8 核的，可以設(shè)置為 8

innodb_read_io_threads = 8

InnoDB 使用后臺線程處理數(shù)據(jù)頁上讀 I/O（輸入輸出）請求的數(shù)量。一般設(shè)置為 CPU 核數(shù)，比如 CPU 是 2 顆 8 核的，可以設(shè)置為 8

innodb_flush_log_at_trx_commit = 2

如果將此參數(shù)設(shè)置為 1，將在每次提交事務(wù)后將日志寫入磁盤。為提高性能，可以設(shè)置為 0 或 2，但要承擔(dān)在發(fā)生故障時(shí)丟失數(shù)據(jù)的風(fēng)險(xiǎn)。設(shè)置為 0 表示事務(wù)日志寫入日志文件， 而日志文件每秒刷新到磁盤一次。設(shè)置為 2 表示事務(wù)日志將在提交時(shí)寫入日志，但日志文件每次刷新到磁盤一次

innodb_log_buffer_size = 16M

此參數(shù)確定日志文件所用的內(nèi)存大小，以 M 為單位。緩沖區(qū)更大能提高性能，但意外的故障將會丟失數(shù)據(jù)。MySQL 開發(fā)人員建議設(shè)置為 1－8M 之間

innodb_log_file_size = 256M

此參數(shù)確定數(shù)據(jù)日志文件的大小，以 M 為單位，較大的值可以提高性能，但也會增加恢復(fù)故障數(shù)據(jù)庫所需的時(shí)間

innodb_log_files_in_group = 3

為提高性能，MySQL 可以以循環(huán)方式將日志文件寫到多個(gè)文件

innodb_file_per_table = 1

獨(dú)享表空間（關(guān)閉）

innodb_max_dirty_pages_pct = 90

Buffer_Pool 中 Dirty_Page 所占的數(shù)量， 直接影響 InnoDB 的關(guān)閉時(shí)間。 參數(shù)innodb_max_dirty_pages_pct 可以直接控制了 Dirty_Page 在 Buffer_Pool 中所占的比率， 而且幸運(yùn)的是 innodb_max_dirty_pages_pct 是可以動態(tài)改變的。所以，在關(guān)閉 InnoDB 之前先將 innodb_max_dirty_pages_pct 調(diào)小，強(qiáng)制數(shù)據(jù)塊 Flush 一段時(shí)間，就能夠大大縮短MySQL 關(guān)閉的時(shí)間

innodb_lock_wait_timeout = 120

InnoDB 有其內(nèi)置的死鎖檢測機(jī)制，能導(dǎo)致未完成的事務(wù)回滾。但是，如果結(jié)合 InnoDB 使用 MyISAM 的 locktables 語句或第三方事務(wù)引擎,InnoDB 就無法識別死鎖。為消除這種可能性，可以將 innodb_lock_wait_timeout 設(shè)置為一個(gè)整數(shù)值，設(shè)置 MySQL 在允許其他事務(wù)修改那些最終受事務(wù)回滾的數(shù)據(jù)之前要等待多長時(shí)間(秒數(shù))

innodb_open_files = 8192 innodb

打開文件句柄數(shù)

關(guān)于引擎是 myisam 的優(yōu)化如下

myisam_sort_buffer_size = 128M

MyISAM 表發(fā)生變化時(shí)重新排序所需的緩沖大小

myisam_max_sort_file_size = 10G

MySQL 重建索引時(shí)所允許的最大臨時(shí)文件的大小(當(dāng) REPAIR，ALTERTABLE 或者

LOADDATAINFILE)。如果文件大小比此值更大,索引會通過鍵值緩沖創(chuàng)建(更慢)

myisam_repair_threads = 1

如果一個(gè)表擁有超過一個(gè)索引，MyISAM 可以通過并行排序使用超過一個(gè)線程去修復(fù)。這對于擁有多個(gè) CPU 以及大量內(nèi)存情況的用戶是一個(gè)很好的選擇

myisam_recover

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

3.SQL優(yōu)化

盡量使用索引進(jìn)行查詢優(yōu)化分頁GROUP BY優(yōu)化

4. 架構(gòu)優(yōu)化

架構(gòu)選擇: 主從、主主、一主多從、多主多從

柚子快報(bào)邀請碼778899分享：MySQL 數(shù)據(jù)庫的優(yōu)化

http://yzkb.51969.com/

參考文章