聊聊MySQL配置。

大多數(shù)開發(fā)者可能不太會(huì)關(guān)注MySQL的配置，畢竟在基本配置沒有問題的情況下，把更多的精力放在schema設(shè)計(jì)、索引優(yōu)化和SQL優(yōu)化上，是非常務(wù)實(shí)的策略。這時(shí)，如果再花力氣去優(yōu)化配置項(xiàng)，獲得的收益通常都比較小。更多的時(shí)候，基于安全因素的考量，普通開發(fā)者很少能夠接觸到生產(chǎn)環(huán)境的MySQL配置。正是這樣，導(dǎo)致開發(fā)者（包括我）對(duì)MySQL的配置不甚了解，希望本文能幫你更好的了解MySQL配置。

如果讓你在某種環(huán)境上安裝配置MySQL，你會(huì)怎么做？安裝后，直接copy修改示例配置文件，應(yīng)該是大多數(shù)人的做法。但強(qiáng)烈建議不要怎么做，首先，示例配置文件有非常多注釋掉的配置項(xiàng)，它可能會(huì)誘使你打開一個(gè)你并不了解的配置，而且這些注釋還不一定準(zhǔn)確。其次，MySQL的一些配置對(duì)于現(xiàn)代化的硬件和工作負(fù)載來(lái)說(shuō)，有點(diǎn)過時(shí)了。

MySQL有非常多的配置項(xiàng)可以修改，但大多數(shù)情況下，你都不應(yīng)該隨便修改它，因?yàn)殄e(cuò)誤或者沒用的配置導(dǎo)致的潛在風(fēng)險(xiǎn)非常大，而且還很難定位問題。確?；九渲谜_，然后小心診斷問題，確認(rèn)問題恰好可以通過某個(gè)配置項(xiàng)解決，緊接著再修改這個(gè)配置吧。

其實(shí)，創(chuàng)建一個(gè)好的配置，最快方法不是從學(xué)習(xí)配置項(xiàng)開始，也不是問哪個(gè)配置項(xiàng)應(yīng)該怎么設(shè)置或者怎么修改開始，更不是從檢查服務(wù)器行為和詢問哪個(gè)配置項(xiàng)可以提升性能開始。最好是從理解MySQL內(nèi)核和行為開始，然后利用這些知識(shí)來(lái)指導(dǎo)你配置MySQL。

就從理解MySQL配置的工作原理開始吧。

MySQL配置的工作原理

MySQL從哪兒獲得配置信息：命令行參數(shù)和配置文件。類Unix系統(tǒng)中，配置文件一般位于 /etc/my.cnf 或者 /etc/mysql/my.cnf。在啟動(dòng)時(shí)，可以通過命令行參數(shù)指定配置文件的位置，當(dāng)然命令行中也可以指定其它參數(shù)，服務(wù)器會(huì)讀取配置文件的內(nèi)容，刪除所有注釋和換行，然后和命令行選項(xiàng)一起處理。

任何打算長(zhǎng)期使用的配置項(xiàng)都應(yīng)該寫入配置文件，而不是在命令行中指定。一定要清楚的知道MySQL使用的配置文件位置，在修改時(shí)不能想當(dāng)然，比如，修改了/etc/my.cnf的配置項(xiàng)，但MySQL實(shí)際并未使用這個(gè)配置文件。如果你不知道當(dāng)前使用的配置文件路徑，可以嘗試：

root@msc3:~# which mysqld
/usr/sbin/mysqld
root@msc3:~# /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

一個(gè)典型的配置文件包含多個(gè)部分，每個(gè)部分的開頭是一個(gè)方括號(hào)括起來(lái)的分段名稱。MySQL程序通常讀取跟它同名的分段部分，比如，許多客戶端程序讀取client部分。服務(wù)器通常讀取mysqld這一段，一定要確認(rèn)配置項(xiàng)放在了文件正確的分段中，否則配置是不會(huì)生效的。

MySQL每一個(gè)配置項(xiàng)均使用小寫，單詞之間用下劃線或者橫線隔開，雖然我們常用的分隔符是下劃線，但如果在命令行或者配置文件中見到如下配置，你要知道，它們其實(shí)是等價(jià)的：

# 配置文件
max_connections=5000
max-connections=5000
# 命令行
/usr/sbin/mysqld --max_connections=5000
/usr/sbin/mysqld --max-connections=5000

配置項(xiàng)可以有多個(gè)作用域：全局作用域、會(huì)話作用域(每個(gè)連接作用不同)、對(duì)象作用域。很多會(huì)話級(jí)配置項(xiàng)跟全局配置相等，可以認(rèn)為是默認(rèn)值，如果改變會(huì)話級(jí)配置項(xiàng)，它只影響改動(dòng)的當(dāng)前連接，當(dāng)連接關(guān)閉時(shí)，所有的參數(shù)變更都會(huì)失效。下面有幾個(gè)示例配置項(xiàng)：

• query-cache-size 全局配置項(xiàng)
• sort-buffer-size 默認(rèn)全局相同，但每個(gè)線程里也可以設(shè)置
• join-buffer-size 默認(rèn)全局，且每個(gè)線程也可以設(shè)置。但若一個(gè)查詢中關(guān)聯(lián)多張表，可以為每個(gè)關(guān)聯(lián)分配一個(gè)關(guān)聯(lián)緩存(join-buffer)，所以一個(gè)查詢可能有多個(gè)關(guān)聯(lián)緩沖。

配置文件中的變量(配置項(xiàng))有很多(但不是所有)可以在服務(wù)器運(yùn)行時(shí)修改，MySQL把這些歸為動(dòng)態(tài)配置變量：

# 設(shè)置全局變量，GLOBAL和@@global作用是一樣的
set   GLOBAL   sort-buffer-size  = <value>
set   @@global.sort-buffer-size := <value>

# 設(shè)置會(huì)話級(jí)變量，下面6種方式作用是一樣的
# 即：沒有修飾符、SESSION、LOCAL等修飾符作用是一致的
set  SESSION   sort-buffer-size  = <value>
set  @@session.sort-buffer-size := <value>
set          @@sort-buffer-size  = <value>
set  LOCAL     sort-buffer-size  = <value>
set     @@ocal.sort-buffer-size := <value>
set            sort-buffer-size  = <value>

# set命令可以同時(shí)設(shè)置多個(gè)變量，但其中只要有一個(gè)變量設(shè)置失敗，所有的變量都未生效
SET GLOBAL sort-buffer-size = 100, SESSION sort-buffer-size = 1000;
SET GLOBAL max-connections = 1000, sort-buffer-size = 1000000;

動(dòng)態(tài)的設(shè)置變量，MySQL關(guān)閉時(shí)這些變量都會(huì)失效。如果在服務(wù)器運(yùn)行時(shí)修改了變量的全局值，這個(gè)值對(duì)當(dāng)前會(huì)話和其他任何已經(jīng)存在的會(huì)話都不起效果，這是因?yàn)闀?huì)話的變量值是在連接創(chuàng)建時(shí)從全局值初始化而來(lái)的。注意，在配置修改后，需要確認(rèn)是否修改成功。

你可能注意到，上面的示例中，有些使用“=”，有些使用“:=”。對(duì)于set命令本身來(lái)說(shuō)，兩種賦值運(yùn)算符沒有任何區(qū)別，在命令行中使用任一運(yùn)算符符，均可以生效。而在其他語(yǔ)句中，賦值運(yùn)算符必須是“:=”，因?yàn)樵诜莝et語(yǔ)句中“=”被視為比較運(yùn)算符。具體可以參考如下示例：
詳細(xì)示例可以參考：stackoverflow

// @exp 表示用戶變量，上面的示例均是系統(tǒng)變量
// 錯(cuò)誤
set @user = 123456;
set @group = select GROUP from USER where User = @user;
select * from USER where GROUP = @group;

// 正確
SET @user := 123456;
SELECT @group := `group` FROM user WHERE user = @user;
SELECT * FROM user WHERE `group` = @group;

有一些配置使用了不同的單位，比如table-cache變量指定表可以被緩存的數(shù)量，而不是表可以被緩存的字節(jié)數(shù)。而key-buffer-size則是以字節(jié)為單位。

還有一些配置可以指定后綴單位，比如1M=1024*1024字節(jié)，但需要注意的是，這只能在配置文件或者作為命令行參數(shù)時(shí)有效。當(dāng)使用SQL的SET命令時(shí)，必須使用數(shù)字值1048576或者1024*1024這樣的表達(dá)式，但在配置文件中不能使用表達(dá)式。

小心翼翼的配置MySQL

們常常動(dòng)態(tài)的修改配置，但請(qǐng)務(wù)必小心，因?yàn)樗鼈兛赡軐?dǎo)致數(shù)據(jù)庫(kù)做大量耗時(shí)的工作，從而影響數(shù)據(jù)庫(kù)的整體性能。比如從緩存中刷新臟塊，不同的刷新方式對(duì)I/O的影響差別很大(后文會(huì)具體說(shuō)明)。最好把一些好的習(xí)慣作為規(guī)范合并到工作流程中去，就比如：

好習(xí)慣1：不要通過配置項(xiàng)的名稱來(lái)推斷一個(gè)變量的作用

不要通過配置項(xiàng)的名稱來(lái)推斷一個(gè)變量的作用，因?yàn)樗赡芨阆胂蟮耐耆灰粯?。比如?/p>

• read-buffer-size：當(dāng)MySQL需要順序讀取數(shù)據(jù)時(shí)，如無(wú)法使用索引，其將進(jìn)行全表掃描或者全索引掃描。這時(shí)，MySQL按照數(shù)據(jù)的存儲(chǔ)順序依次讀取數(shù)據(jù)塊，每次讀取的數(shù)據(jù)塊首先會(huì)暫存在緩存中，當(dāng)緩存空間被寫滿或者全部數(shù)據(jù)讀取結(jié)束后，再將緩存中的數(shù)據(jù)返回給上層調(diào)用者，以提高效率。
• read-rnd-buffer-size：和順序讀取相對(duì)應(yīng)，當(dāng)MySQL進(jìn)行非順序讀?。S機(jī)讀取）數(shù)據(jù)塊的時(shí)候，會(huì)利用這個(gè)緩沖區(qū)暫存讀取的數(shù)據(jù)。比如：根據(jù)索引信息讀取表數(shù)據(jù)、根據(jù)排序后的結(jié)果集與表進(jìn)行Join等等?？偟膩?lái)說(shuō)，就是當(dāng)數(shù)據(jù)塊的讀取需要滿足一定的順序的情況下，MySQL 就需要產(chǎn)生隨機(jī)讀取，進(jìn)而使用到read-rnd-buffer-size參數(shù)所設(shè)置的內(nèi)存緩沖區(qū)。

這兩個(gè)配置都是在掃描MyISAM表時(shí)有效，且MySQL會(huì)為每個(gè)線程分配內(nèi)存。對(duì)于前者，MySQL只會(huì)在查詢需要使用時(shí)才會(huì)為該緩存分配內(nèi)存，并且一次性分配該參數(shù)指定大小的全部?jī)?nèi)存，而后者同樣是需要時(shí)才分配內(nèi)存，但只分配需要的內(nèi)存大小而不是參數(shù)指定的數(shù)值，max-read-rnd-buffer-size(實(shí)際上沒有這個(gè)配置項(xiàng))這個(gè)名字更能表達(dá)這個(gè)變量的實(shí)際含義。

好習(xí)慣2：不要輕易在全局修改會(huì)話級(jí)別的配置

對(duì)于某些會(huì)話級(jí)別的設(shè)置，不要輕易的在全局增加它們的值，除非你確認(rèn)這樣做是對(duì)的。比如：sort-buffer-size，該參數(shù)控制排序操作的緩存大小，MySQL只會(huì)在查詢需要做排序操作時(shí)才會(huì)為該緩沖分配內(nèi)存，一旦需要排序，就會(huì)一次性分配指定大小的內(nèi)存，即使是非常小的排序操作。因此在配置文件中應(yīng)該配置的小一些，然后在某些查詢需要排序時(shí)，再在連接中把它調(diào)大。比如：

SET @@seession.sort-buffer-size := <value>
-- 執(zhí)行查詢的sql
SET @@seession.sort-buffer-size := DEFAULT #恢復(fù)默認(rèn)值
# 可以將類似的代碼封裝在函數(shù)中方便使用。

好習(xí)慣3：配置變量時(shí)，并不是值越大越好

配置變量時(shí)，并不是值越大越好，而且如果設(shè)置的值太高，可能更容易導(dǎo)致內(nèi)存問題。在修改完成后，應(yīng)該通過監(jiān)控來(lái)確認(rèn)變量的修改對(duì)服務(wù)器整體性能的影響。

好習(xí)慣4：規(guī)范注釋，版本控制

在配置文件中寫好注釋，可能會(huì)節(jié)省自己和同事大量的工作，一個(gè)更好的習(xí)慣是把配置文件置于版本控制之下。

說(shuō)完了好習(xí)慣，再來(lái)說(shuō)說(shuō)不好的習(xí)慣。

壞習(xí)慣1：根據(jù)一些“比率”來(lái)調(diào)優(yōu)

一個(gè)經(jīng)典的按“比率”調(diào)優(yōu)的經(jīng)驗(yàn)法則是，緩存的命中率應(yīng)該高于某個(gè)百分比，如果命中率過低，則應(yīng)該增加緩存的大小。這是非常錯(cuò)誤的意見，大家可以仔細(xì)思考一下：緩存的命中率跟緩存大小有必然聯(lián)系嗎？(分母變大，值就變大了？)除非確實(shí)是緩存太小了。關(guān)于MyISAM鍵緩沖命中率，下文會(huì)詳細(xì)說(shuō)明。

壞習(xí)慣2：隨便使用調(diào)優(yōu)腳本

盡量不要使用調(diào)優(yōu)腳本！不同的業(yè)務(wù)場(chǎng)景、不同的硬件環(huán)境對(duì)MySQL的性能要求是不一樣的。比如有些業(yè)務(wù)對(duì)數(shù)據(jù)的完整性要求較高，那么就一定要保證數(shù)據(jù)不丟失，出現(xiàn)故障后可恢復(fù)數(shù)據(jù)，而有些業(yè)務(wù)卻對(duì)數(shù)據(jù)的完整性要求沒那么高，但對(duì)性能要求更高。因此，即使是同一個(gè)變量，在這兩個(gè)不同場(chǎng)景下，其配置的值也應(yīng)該是不同的。那你還能放心的使用網(wǎng)上找到的腳本嗎 ？

本小節(jié)示例的幾個(gè)配置項(xiàng)，僅用于舉例說(shuō)明，并不代表它們有多么重要，請(qǐng)根據(jù)實(shí)際應(yīng)用場(chǎng)景配置它們。就比如sort-buffer-size，你真的需要100M內(nèi)存來(lái)緩存10行數(shù)據(jù)？

給你一個(gè)基本的MySQL配置

前面已經(jīng)說(shuō)到，MySQL可配置性太強(qiáng)，看起來(lái)需要花很多時(shí)間在配置上，但其實(shí)大多數(shù)配置的默認(rèn)值已經(jīng)是最佳的，最好不要輕易改動(dòng)太多的配置，你甚至不需要知道某些配置的存在。這里有一個(gè)最小的示例配置文件，可以作為服務(wù)器配置文件的一個(gè)起點(diǎn)，其中有一些配置項(xiàng)是必須的。本節(jié)將為你詳細(xì)剖析每個(gè)配置有何作用？為什么要配置它？怎么確定合適的值？

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
port                           = 3306
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10240

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 12G

分段

MySQL配置文件的格式為集中式，通常會(huì)分成好幾部分，可以為多個(gè)程序提供配置，如[client]、[mysqld]、[mysql]等等。MySQL程序通常是讀取與它同名的分段部分。

• [client] 客戶端默認(rèn)設(shè)置內(nèi)容
• [mysql] 使用mysql命令登錄mysql數(shù)據(jù)庫(kù)時(shí)的默認(rèn)設(shè)置
• [mysqld] 數(shù)據(jù)庫(kù)本身的默認(rèn)設(shè)置

例如服務(wù)器mysqld通常讀取[mysqld]分段下的相關(guān)配置項(xiàng)。如果配置項(xiàng)位置不正確，該配置是不會(huì)生效的。

GENERAL

首先創(chuàng)建一個(gè)用戶mysql來(lái)運(yùn)行mysqld進(jìn)程，請(qǐng)確保這個(gè)用戶擁有操作數(shù)據(jù)目錄的權(quán)限。設(shè)置默認(rèn)端口為3306，有時(shí)為了安全，可能會(huì)修改一下。默認(rèn)選擇Innodb存儲(chǔ)引擎，在大多數(shù)情況下是最好的選擇。但如果默認(rèn)是InnoDB，卻需要使用MyISAM存儲(chǔ)引擎，請(qǐng)顯式地進(jìn)行配置。許多用戶認(rèn)為其數(shù)據(jù)庫(kù)使用了某種存儲(chǔ)引擎但實(shí)際上卻使用的是另外一種，就是因?yàn)槟J(rèn)配置的問題。

接著設(shè)置數(shù)據(jù)文件的位置，這里把pid文件和socket文件放到相同的位置，當(dāng)然也可以選擇其它位置，但要注意的是不要將socket文件和pid文件放到MySQL編譯的默認(rèn)位置，因?yàn)椴煌姹镜腗ySQL，這兩個(gè)文件的默認(rèn)路徑可能會(huì)不一致，最好明確地設(shè)置這些文件的位置，以免版本升級(jí)時(shí)出現(xiàn)問題。

在類UNIX系統(tǒng)下本地連接MySQL可以采用UNIX域套接字方式，這種方式需要一個(gè)套接字（socket）文件，即配置中的mysql.sock文件。
當(dāng)MySQL實(shí)例啟動(dòng)時(shí)，會(huì)將自己的進(jìn)程ID寫入一個(gè)文件中——該文件即為pid文件。該文件可由參數(shù)pid-file控制，默認(rèn)位于數(shù)據(jù)庫(kù)目錄下，文件名為主機(jī)名.pid

DATA STORAGE

datadir用于配置數(shù)據(jù)文件的存儲(chǔ)位置，沒有什么好說(shuō)的。

為緩存分配內(nèi)存

接下來(lái)有許多涉及到緩存的配置項(xiàng)，緩存設(shè)置多大，最直接的因素肯定是服務(wù)器內(nèi)存的大小。如果服務(wù)器只運(yùn)行MySQL，所有不需要為OS以及查詢處理保留的內(nèi)存都可以用在MySQL緩存。為MySQL緩存分配更多內(nèi)存，可以有效的避免磁盤訪問，提升數(shù)據(jù)庫(kù)性能。大部分情況來(lái)說(shuō)最為重要的緩存：

• InnoDB緩沖池
• InnoDB日志文件和MyISAM數(shù)據(jù)的操作系統(tǒng)緩存(MyISAM依賴于OS緩存數(shù)據(jù))
• MyISAM鍵緩存
• 查詢緩存
• 無(wú)法配置的緩存，比如：bin-log或者表定義文件的OS緩存

還有一些其他緩存，但它們通常不會(huì)使用太多內(nèi)存。關(guān)于查詢緩存，前面文章(參考本系列的第一篇)已有介紹，大多數(shù)情況下我們不建議開啟查詢緩存，因此上文的配置中query-cache-type=0表示禁用了查詢緩存，相應(yīng)的查詢緩存大小query-cache-size=0。除開查詢緩存，剩下關(guān)于InnoDB和MyISAM的相關(guān)緩存，在接下來(lái)會(huì)做詳細(xì)介紹。

如果只使用單一存儲(chǔ)引擎，配置服務(wù)器就會(huì)簡(jiǎn)單許多。如果只使用MyISAM表，就可以完全關(guān)閉InnoDB，而如果只使用InnoDB，就只需要分配最少的資源給MyISAM（MySQL內(nèi)部系統(tǒng)表使用MyISAM引擎）。但如果是混合使用各種存儲(chǔ)引擎，就很難在他們之間找到恰當(dāng)?shù)钠胶猓虼酥荒芨鶕?jù)業(yè)務(wù)做一個(gè)猜測(cè)，然后在運(yùn)行中觀察服務(wù)器運(yùn)行狀況后做出調(diào)整。

MyISAM

key-buffer-size

key-buffer-size用于配置MyISAM鍵緩存大小，默認(rèn)只有一個(gè)鍵緩存，但是可以創(chuàng)建多個(gè)。MyISAM自身只緩存索引，不緩存數(shù)據(jù)(依賴OS緩存數(shù)據(jù))。如果大部分表都是MyISAM，那么應(yīng)該為鍵緩存設(shè)置較多的內(nèi)存。但如何確定該設(shè)置多大？

假設(shè)整個(gè)數(shù)據(jù)庫(kù)中表的索引大小為X，肯定不需要把緩存設(shè)置得比X還大，所以當(dāng)前的索引大小就成為這個(gè)配置項(xiàng)的重要依據(jù)?？梢酝ㄟ^下面兩種方式來(lái)查詢當(dāng)前索引的大小：

// 1.通過SQL語(yǔ)句查詢
SELECT SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MYISAM'
// 2.統(tǒng)計(jì)索引文件的大小
$ du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`
比如：
root@dev-msc3:# du -sch `find /var/lib/mysql -name "*.MYI"`
72K        /var/lib/mysql/static/t_global_region.MYI
40K        /var/lib/mysql/mysql/db.MYI
12K        /var/lib/mysql/mysql/proxies_priv.MYI
12K        /var/lib/mysql/mysql/tables_priv.MYI
4.0K       /var/lib/mysql/mysql/func.MYI
4.0K       /var/lib/mysql/mysql/columns_priv.MYI
4.0K       /var/lib/mysql/mysql/proc.MYI
4.0K       /var/lib/mysql/mysql/event.MYI
4.0K       /var/lib/mysql/mysql/user.MYI
4.0K       /var/lib/mysql/mysql/procs_priv.MYI
4.0K       /var/lib/mysql/mysql/ndb_binlog_index.MYI
164K       total

你可能會(huì)問，剛創(chuàng)建好的數(shù)據(jù)庫(kù)，根本就沒什么數(shù)據(jù)，索引文件大小為0，那如何配置鍵緩存大??？這時(shí)候只能根據(jù)經(jīng)驗(yàn)值：不超過為操作系統(tǒng)緩存保留內(nèi)存的25% ~ 50%。設(shè)置一個(gè)基本值，等運(yùn)行一段時(shí)間后，根據(jù)運(yùn)行情況來(lái)調(diào)整鍵緩存大小?？偨Y(jié)來(lái)說(shuō)，索引大小與OS緩存的25%~50%兩者間取小者。當(dāng)然還可以計(jì)算鍵緩存的使用情況，如果一段時(shí)間后還是沒有使用完所有的鍵緩存，就可以把緩沖區(qū)調(diào)小一點(diǎn)，計(jì)算緩存區(qū)的使用率可以通過以下公式：

// key_blocks_unused的值可以通過 SHOW STATUS獲取
// key_cache_block_size的值可以通過 SHOW VARIABLES獲取 
(key_blocks_unused * key_cache_block_size) / key_buffer_size

鍵緩存塊大小是一個(gè)比較重要的值，因?yàn)樗绊慚yISAM、OS緩存以及文件系統(tǒng)之間的交互。如果緩存塊太小，可能會(huì)碰到寫時(shí)讀取(OS在寫數(shù)據(jù)之前必須先從磁盤上讀取一些數(shù)據(jù))，關(guān)于寫時(shí)讀取的相關(guān)知識(shí)，大家可以自行查閱。

關(guān)于緩存命中率，這里再說(shuō)一點(diǎn)。緩存命中率有什么意義？其實(shí)這個(gè)數(shù)字沒太大的作用。比如99%和99.9%之間看起來(lái)差距很小，但實(shí)際上代表了10倍的差距。緩存命中率的實(shí)際意義與應(yīng)用也有很大關(guān)系，有些應(yīng)用可以在命中率99%下良好的工作，有些I/O密集型應(yīng)用，可能需要99.99%。所以從經(jīng)驗(yàn)上來(lái)說(shuō)，每秒未命中次數(shù)這個(gè)指標(biāo)實(shí)際上會(huì)更有用一些。比如每秒5次未命中可能不會(huì)導(dǎo)致IO繁忙，但每秒100次緩存未命中則可能出現(xiàn)問題。

MyISAM鍵緩存的每秒未命中次數(shù)可以通過如下命令監(jiān)控：

# 計(jì)算每隔10s緩存未命中次數(shù)的增量
# 使用此命令時(shí)請(qǐng)帶上用戶和密碼參數(shù)：mysqladmin -uroot -pxxx extended-status -r -i 10 | grep Key_reads
$ mysqladmin extended-status -r -i 10 | grep Key_reads

最后，即使沒有使用任何MyISAM表，依然需要將key-buffer-size設(shè)置為較小值，比如32M，因?yàn)镸ySQL內(nèi)部會(huì)使用MyISAM表，比如GROUP BY語(yǔ)句可能會(huì)創(chuàng)建MyISAM臨時(shí)表。

myisam-recover

myisam-recover選項(xiàng)用于配置MyISAM怎樣尋找和修復(fù)錯(cuò)誤。打開這個(gè)選項(xiàng)會(huì)通知MySQL在打開表時(shí)，檢查表是否損壞，并在找到問題時(shí)進(jìn)行修復(fù)，它可以設(shè)置如下值：

• DEFAULT：表示不設(shè)置，會(huì)嘗試修復(fù)崩潰或者未完全關(guān)閉的表，但在恢復(fù)數(shù)據(jù)時(shí)不會(huì)執(zhí)行其它動(dòng)作
• BACKUP：將數(shù)據(jù)文件備份到.bak文件，以便隨后進(jìn)行檢查
• FORCE：即使.myd文件中丟失的數(shù)據(jù)超過1行，也讓恢復(fù)動(dòng)作繼續(xù)執(zhí)行
• QUICK：除非有刪除塊，否則跳過恢復(fù)

可以設(shè)置多個(gè)值，每個(gè)值用逗號(hào)隔開，比如配置文件中的BACKUP,FORCE會(huì)強(qiáng)制恢復(fù)并且創(chuàng)建備份，這樣配置在只有一些小的MyISAM表時(shí)有用，因?yàn)榉?wù)器運(yùn)行著一些損壞的MyISAM表是非常危險(xiǎn)的，它們有時(shí)可能會(huì)導(dǎo)致更多數(shù)據(jù)損壞，甚至服務(wù)器崩潰。然而如果有很大的表，它會(huì)導(dǎo)致服務(wù)器打開所有的MyISAM表時(shí)都檢查和修復(fù)，大表的檢查和修復(fù)可能會(huì)耗費(fèi)大量時(shí)間，且在這段時(shí)間里，MySQL會(huì)阻止這個(gè)連接做其它任何操作，這顯然是不切實(shí)際的。

因此，在默認(rèn)使用InnoDB存儲(chǔ)引擎時(shí)，數(shù)據(jù)庫(kù)中只有非常小的MyISAM表時(shí)，只需要配置key-buffe-size于一個(gè)很小的值(32M)以及myisam-recover=BACKUP,FORCE。當(dāng)數(shù)據(jù)庫(kù)中大部分表為MyISAM表時(shí)，請(qǐng)根據(jù)上文的公式合理配置key-buffer-size，而myisam-recover則可以關(guān)閉，在啟動(dòng)后使用CHECK TABLES和REPAIR TABLES命令來(lái)做檢查和修復(fù)，這樣對(duì)服務(wù)器的影響比較小。

SAFETY

基本配置設(shè)置到位后，MySQL已經(jīng)比較安全了，這里僅僅列出兩個(gè)需要注意的配置項(xiàng)，如果需要啟用一些使服務(wù)器更安全和可靠的設(shè)置，可以參考MySQL官方手冊(cè)，但需要注意的是，它們其中的一些選項(xiàng)可能會(huì)影響性能，畢竟保證安全和可靠需要付出一些代價(jià)。

max-allowed-packet

max-allowed-packet防止服務(wù)器發(fā)送太大的數(shù)據(jù)包，也控制服務(wù)器可以接收多大的包。默認(rèn)值4M，可能會(huì)比較小。如果設(shè)置太小，有時(shí)復(fù)制上會(huì)出問題，表現(xiàn)為從庫(kù)不能接收主庫(kù)發(fā)過來(lái)的復(fù)制數(shù)據(jù)。如果表中有Blob或者Text字段，且數(shù)據(jù)量較大的話，要小心，如果數(shù)據(jù)量超過這個(gè)變量的大小，它們可能被截?cái)嗷蛘咧脼镹ULL，這里建議設(shè)置為16M。

max-connect-errors

這個(gè)變量是一個(gè)MySQL中與安全相關(guān)的計(jì)數(shù)器值，它主要防止客戶端暴力破解密碼。如果某一個(gè)客戶端嘗試連接MySQL服務(wù)器失敗超過n次，則MySQL會(huì)無(wú)條件強(qiáng)制阻止此客戶端連接，直到再次刷新主機(jī)緩存或者重啟MySQL服務(wù)器。

這個(gè)值默認(rèn)為10，太小了，有時(shí)候網(wǎng)絡(luò)抽風(fēng)或者應(yīng)用配置出現(xiàn)錯(cuò)誤導(dǎo)致短時(shí)間內(nèi)不斷嘗試重連服務(wù)器，客戶端就會(huì)被列入黑名單，導(dǎo)致無(wú)法連接。如果在內(nèi)網(wǎng)環(huán)境，可以確認(rèn)沒有安全問題可以把這個(gè)值設(shè)置的大一點(diǎn)，默認(rèn)值太容易導(dǎo)致問題。

LOGGING

接下來(lái)看下日志的配置，對(duì)于MySQL來(lái)說(shuō)，慢日志和bin-log是非常重要的兩種日志，前者可以幫助應(yīng)用程序監(jiān)控性能問題，后者在數(shù)據(jù)同步、備份等方面發(fā)揮著非常重要的作用。

關(guān)于bin-log的3個(gè)配置，log-bin用于配置文件存放路徑，expire_logs_days讓服務(wù)器在指定天數(shù)之后清理舊的日志，即配置保留最近多少天的日志。除非有運(yùn)維手動(dòng)備份清理bin-log，否則強(qiáng)烈建議打開此配置，如果不啟用，服務(wù)器空間最終將會(huì)被耗盡，導(dǎo)致服務(wù)器卡住或者崩潰。

sync-binlog

sync-binlog控制當(dāng)事務(wù)提交之后，MySQL是否將bin-log刷新到磁盤。如果其值等于0或者大于1時(shí)，當(dāng)事務(wù)提交之后，MySQL不會(huì)將bin-log刷新到磁盤，其性能最高，但存在的風(fēng)險(xiǎn)也是最大的，因?yàn)橐坏┫到y(tǒng)崩潰，bin-log將會(huì)丟失。而當(dāng)其值等于1時(shí)，是最安全的，這時(shí)候即使系統(tǒng)崩潰，最多也就丟失本次未完成的事務(wù)，對(duì)實(shí)際的數(shù)據(jù)沒有實(shí)質(zhì)性的影響，但性能較差。

需要注意的是，在5.7.7之前的版本，這個(gè)選擇的默認(rèn)值為0，而后默認(rèn)值為1，也就是最安全的策略。對(duì)于高并發(fā)的性能，需要關(guān)注這一點(diǎn)，防止版本升級(jí)后出現(xiàn)性能問題。

剩下的4個(gè)配置項(xiàng)就沒太多要說(shuō)的。

• log-error：用于配置錯(cuò)誤日志的存放目錄
• slow-query-log：打開慢日志，默認(rèn)關(guān)閉
• slow-query-log-file：配置慢日志的存放目錄
• log-queries-not-using-indexes：如果該sql沒有使用索引，會(huì)將其寫入到慢日志，但是否真的執(zhí)行很慢，需要區(qū)分，默認(rèn)關(guān)閉。

CACHES AND LIMITS

tmp-table-size && max-heap-table-size

這兩個(gè)配置控制使用Memory引擎的內(nèi)存臨時(shí)表可以使用多大的內(nèi)存。如果隱式內(nèi)存臨時(shí)表的大小超過這兩個(gè)值，將會(huì)被轉(zhuǎn)為磁盤MyISAM表(隱式臨時(shí)表由服務(wù)器創(chuàng)建，用戶保存執(zhí)行中的查詢的中間結(jié)果)。

如果查詢語(yǔ)句沒有創(chuàng)建龐大的臨時(shí)表(通過合理的索引和查詢?cè)O(shè)計(jì)來(lái)避免)，可以把這個(gè)值設(shè)大一點(diǎn)，以免需要把內(nèi)存臨時(shí)表轉(zhuǎn)換為磁盤臨時(shí)表。但要謹(jǐn)防這個(gè)值設(shè)置得過大，如果查詢確實(shí)會(huì)創(chuàng)建很大的臨時(shí)表，那么還是使用磁盤比較好，畢竟并發(fā)數(shù)一起來(lái)，所需要的內(nèi)存就會(huì)急劇增長(zhǎng)。

應(yīng)該簡(jiǎn)單的把這兩個(gè)變量設(shè)為同樣的值，這里選擇了32M，可以通過仔細(xì)檢查created-tmp-disk-tables和created-tmp-tables兩個(gè)變量來(lái)指導(dǎo)你設(shè)置，這兩個(gè)變量的值將展示臨時(shí)表的創(chuàng)建有多頻繁。

query-cache-type && query-cache-size

看前面

max-connections

用于設(shè)置用戶的最大連接數(shù)，保證服務(wù)器不會(huì)應(yīng)為應(yīng)用程序激增的連接而不堪重負(fù)。如果應(yīng)用程序有問題，或者服務(wù)器遇到連接延遲問題，會(huì)創(chuàng)建很多新連接。但如果這些連接不能執(zhí)行查詢，那打開一個(gè)連接沒什么好處，所以被“太多的連接”錯(cuò)誤拒絕是一種快速而且代價(jià)小的失敗方式。

在服務(wù)器資源允許的情況下，可以把max-connections設(shè)置的足夠大，以容納正?？赡苓_(dá)到的負(fù)載。若認(rèn)為正常情況將有300或者更多連接，可以設(shè)置為500或者更多(應(yīng)對(duì)高峰期)。默認(rèn)值是100，太小了，這里設(shè)置為500，但并不意味著其是一個(gè)合理的值，應(yīng)該監(jiān)控應(yīng)用有多少連接，然后根據(jù)監(jiān)控值(觀察max_used_connections隨時(shí)間的變化)來(lái)設(shè)置。

thread-cache-size

線程緩存保存那些當(dāng)前沒有與連接關(guān)聯(lián)但是準(zhǔn)備為后面新連接服務(wù)的線程。當(dāng)一個(gè)新的連接創(chuàng)建時(shí)，如果緩存中有線程存在，MySQL則從緩存中刪除一個(gè)線程，并且把它分配給這個(gè)新連接。當(dāng)連接關(guān)閉時(shí)，如果線程緩存還有空間的話，MySQL又會(huì)把線程放回緩存。如果沒有空間的話，MySQL會(huì)銷毀這個(gè)線程。只要MySQL在緩存里還有空閑的線程，它就可以迅速響應(yīng)連接請(qǐng)求，因?yàn)檫@樣就不用為每個(gè)連接創(chuàng)建新線程。thread-cache-size指定MySQL可以保存在緩存中的線程數(shù)量。如果服務(wù)器沒有很多的連接請(qǐng)求，一般不需要配置這個(gè)值。

如何判斷這個(gè)值該設(shè)置多大？

觀察threads-connected變量，如果threads-connected在100-120，那么thread-cache-size設(shè)置為20。如果它保持在500-700，200的線程緩存應(yīng)該足夠大了?？梢赃@么理解：當(dāng)同時(shí)有700個(gè)連接時(shí)，可能緩存中沒有線程。在500個(gè)連接時(shí)，有200個(gè)緩存的線程準(zhǔn)備為負(fù)載再次增加到700個(gè)連接時(shí)使用。

open-files-limit

在類Uinux系統(tǒng)上我們把它設(shè)置得盡可能大?，F(xiàn)代OS中打開句柄開銷都很小，如果此參數(shù)設(shè)置過小，可能會(huì)遇到“打開的文件太多(too many open files)”錯(cuò)誤。

table_cache_size

表緩存跟線程緩存類似，但存儲(chǔ)的對(duì)象是表，其包含表.frm文件的解析結(jié)果和一些其他數(shù)據(jù)。準(zhǔn)確的說(shuō)，緩存的數(shù)據(jù)依賴于存儲(chǔ)引擎，比如，對(duì)于MyISAM，緩存表的數(shù)據(jù)和索引的文件描述符。表緩存對(duì)InnoDB的存儲(chǔ)引擎來(lái)說(shuō)，重要性會(huì)小很多，因?yàn)镮nnoDB不依賴它來(lái)做那么多的事。

從5.1版本及以后，表緩存就被分為兩個(gè)部分：打開表緩存和定義表緩存，分別通過table-open-cache-size和table-definition-cache-size變量來(lái)配置。通常可以把table-definition-cache-size設(shè)置得足夠高，以緩存所有的表定義，因?yàn)榇蟛糠执鎯?chǔ)引擎都能從table-definition-cache獲益。

INNODB

InnoDB應(yīng)該是使用最廣發(fā)的存儲(chǔ)引擎，最重要的配置選項(xiàng)是下面這兩個(gè)：innodb-buffer-pool-size與innodb-log-file-size，解決這兩個(gè)配置基本上就解決了真實(shí)場(chǎng)景下的大部分配置問題。

innodb-buffer-pool-size

如果大部分是InnoDB表，那么InnoDB緩沖池或許比其他任何東西都更需要內(nèi)存，InnoDB緩沖池緩沖的數(shù)據(jù)：索引、行數(shù)據(jù)、自適應(yīng)哈希索引、插入緩沖、鎖以及其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)。InnoDB還使用緩沖池來(lái)幫助延遲寫入，這樣就可以合并多個(gè)寫入操作，然后一起順序?qū)懭?，提升性能?？傊琁nnoDB嚴(yán)重依賴緩沖池，必須為其分配足夠的內(nèi)存。

當(dāng)然，如果數(shù)據(jù)量不大且不會(huì)快速增長(zhǎng)，就沒有必要為緩沖池分配過多的內(nèi)存，把緩沖池配置得比需要緩存的表和索引還要大很多，實(shí)際上也沒有什么意義。很大的緩沖池也會(huì)帶來(lái)一些挑戰(zhàn)，例如，預(yù)熱和關(guān)閉都會(huì)花費(fèi)很長(zhǎng)的時(shí)間。如果有很多臟頁(yè)在緩沖池里，InnoDB關(guān)閉時(shí)可能會(huì)花很長(zhǎng)時(shí)間來(lái)把臟頁(yè)寫回?cái)?shù)據(jù)文件。雖然可以快速關(guān)閉，但是在啟動(dòng)時(shí)需要做更多的恢復(fù)工作，也就是說(shuō)我們無(wú)法同時(shí)加速關(guān)閉和重啟兩個(gè)操作。當(dāng)有一個(gè)很大的緩沖池，重啟服務(wù)需要花費(fèi)很長(zhǎng)時(shí)間（幾小時(shí)或者幾天）來(lái)預(yù)熱，尤其是磁盤很慢的時(shí)候，如果想加快預(yù)熱時(shí)間，可以在重啟后立刻進(jìn)行全表掃描或者索引掃描，把索引載入緩沖池。

可以看到示例的配置文件中把這個(gè)值配置為12G，這不是一個(gè)標(biāo)準(zhǔn)配置，需要根據(jù)具體的硬件來(lái)估算。那如何估算？

前面的小節(jié)，我們說(shuō)到，MySQL中最重要的緩存有5種，可以簡(jiǎn)單的使用下面的公式計(jì)算：

InnoDB緩沖池 = 服務(wù)器總內(nèi)存 - OS預(yù)留 - 服務(wù)器上的其他應(yīng)用占用內(nèi)存 - MySQL自身需要的內(nèi)存 - InnoDB日志文件占用內(nèi)存 - 其它內(nèi)存(MyISAM鍵緩存、查詢緩存等)

具體來(lái)看，至少需要為OS保留1~2G內(nèi)存，如果機(jī)器內(nèi)存大的話可以預(yù)留多一些，建議2GB和總內(nèi)存的5%為基準(zhǔn)，以較大者為準(zhǔn)，如果機(jī)器上還運(yùn)行著一些內(nèi)存密集型任務(wù)，比如，備份任務(wù)，那么可以為OS再預(yù)留多一些內(nèi)存。不要為OS緩存增加任何內(nèi)存，因?yàn)镺S通常會(huì)利用所有剩下的內(nèi)存來(lái)做文件緩存。

一般來(lái)說(shuō)，運(yùn)行MySQL的服務(wù)器很少會(huì)運(yùn)行其他應(yīng)用程序，但如果有的話，請(qǐng)為這些應(yīng)用程序預(yù)留足夠多的內(nèi)存。

MySQL自身運(yùn)行還需要一些內(nèi)存，但通常都不會(huì)太大。需要考慮MySQL每個(gè)連接需要的內(nèi)存，雖然每個(gè)連接需要的內(nèi)存都很少，但它還要求一個(gè)基本量的內(nèi)存來(lái)執(zhí)行任何給定的查詢，而且查詢過程中還需要為排序、GROUP BY等操作分配臨時(shí)表內(nèi)存，因此需要為高峰期執(zhí)行大量的查詢預(yù)留足夠的內(nèi)存。這個(gè)內(nèi)存有多大？只能在運(yùn)行過程中監(jiān)控。

如果大部分表都是InnoDB，MyISAM鍵緩存配置一個(gè)很小值足矣，查詢緩存也建議關(guān)閉。

公式中就剩下InnoDB日志文件了，這就是我們接下來(lái)要說(shuō)的。

innodb-log-file-size && innodb-log-files-in-group

如果對(duì)InnoDB數(shù)據(jù)表有大量的寫入操作，那么選擇合適的innodb-log-file-size值對(duì)提升MySQL性能很重要。InnoDB使用日志來(lái)減少提交事務(wù)時(shí)的開銷。日志中記錄了事務(wù)，就無(wú)須在每個(gè)事務(wù)提交時(shí)把緩沖池的臟塊(緩存中與磁盤上數(shù)據(jù)不一致的頁(yè))刷新到磁盤。事務(wù)修改的數(shù)據(jù)和索引通常會(huì)映射到表空間的隨機(jī)位置，所以刷新這些變更到磁盤需要很多隨機(jī)I/O。一旦日志安全的寫入磁盤，事務(wù)就持久化了，即使變更還沒有寫到數(shù)據(jù)文件，在一些意外情況發(fā)生時(shí)(比如斷電了)，InnoDB可以重放日志并且恢復(fù)已經(jīng)提交的事務(wù)。

InnoDB使用一個(gè)后臺(tái)線程智能地刷新這些變更到數(shù)據(jù)文件。實(shí)際上，事務(wù)日志把數(shù)據(jù)文件的隨機(jī)I/O轉(zhuǎn)換為幾乎順序地日志文件和數(shù)據(jù)文件I/O，讓刷新操作在后臺(tái)可以更快的完成，并且緩存I/O壓力。

整體的日志文件大小受控于innodb-log-file-size和innodb-log-files-in-group兩個(gè)參數(shù)，這對(duì)寫性能非常重要。日志文件的總大小是每個(gè)文件的大小之和。默認(rèn)情況下，只有兩個(gè)5M的文件，總共10M，對(duì)高性能工作來(lái)說(shuō)太小了，至少需要幾百M(fèi)或者上G的日志文件。這里要注意innodb-log-files-in-group這個(gè)參數(shù)，它控制日志文件的數(shù)量，從名字上看好似配置一個(gè)日志組有幾個(gè)文件，實(shí)際上，log group表示一個(gè)重做日志的文件集合，沒有參數(shù)也沒有必要配置有多少個(gè)日志組。

修改日志文件的大小，需要完全關(guān)閉MySQL，然后將舊的日志文件遷移到其他地方，重新配置參數(shù)，然后重啟。重啟時(shí)需要將舊的日志遷移回來(lái)，然后等待MySQL恢復(fù)數(shù)據(jù)后，再刪除舊的日志文件，請(qǐng)一定要查看錯(cuò)誤日志，確認(rèn)MySQL重啟成功后再刪除舊的日志文件。

想要確定理想的日志文件大小，需要權(quán)衡正常數(shù)據(jù)變更的開銷，以及崩潰時(shí)恢復(fù)需要的時(shí)間。如果日志太小，InnoDB將必須要做更多的檢查點(diǎn)，導(dǎo)致更多的日志寫，在極個(gè)別情況下，寫語(yǔ)句還會(huì)被拖累，在日志沒有空間繼續(xù)寫入前，必須等待變更被刷新到數(shù)據(jù)文件。另一方面，如果日志太大，在崩潰時(shí)恢復(fù)就得做大量的工作，這可能增大恢復(fù)時(shí)間。InnoDB會(huì)采用checkpoint機(jī)制來(lái)刷新和恢復(fù)數(shù)據(jù)，這會(huì)加快恢復(fù)數(shù)據(jù)的時(shí)間，具體可以參考：

• MySQL-checkpoint技術(shù)
• How InnoDB performs a checkpoint

innodb-flush-log-at-trx-commit

前面討論了很多緩存，InnoDB日志也是有緩存的。當(dāng)InnoDB變更任何數(shù)據(jù)時(shí)，會(huì)寫一條變更記錄到日志緩存區(qū)。在緩沖慢的時(shí)候、事務(wù)提交的時(shí)候，或者每一秒鐘，InnoDB都會(huì)將緩沖區(qū)的日志刷新到磁盤的日志文件。如果有大事務(wù)，增加日志緩沖區(qū)大小可以幫助減少I/O，變量innodb-log-buffer-size可以控制日志緩沖區(qū)的大小。通常不需要把日志緩沖區(qū)設(shè)置的非常大，畢竟上述3個(gè)條件，任一條件先觸發(fā)都會(huì)把緩沖區(qū)的內(nèi)容刷新到磁盤，所以緩沖區(qū)的數(shù)據(jù)肯定不會(huì)太多，出入你的數(shù)據(jù)中有很多相當(dāng)大的BLOB記錄。通常來(lái)說(shuō)，配置1M~8M即可。

既然存在緩沖區(qū)，怎樣刷新日志緩沖就是我們需要關(guān)注的問題。日志緩沖必須刷新到磁盤，以確保提交的事務(wù)完全被持久化。如果和持久化相比，更在乎性能，可以修改innodb-flush-log-at-trx-commit變量來(lái)控制日志緩沖刷新的頻率。

• 0：每1秒鐘將日志緩沖寫到日志文件并刷新到磁盤，事務(wù)提交時(shí)不做任何處理
• 1：每次事務(wù)提交時(shí)，將日志緩沖寫到日志文件并刷新到磁盤
• 2：每次事務(wù)提交時(shí)，將日志緩沖寫到日志文件，然后每秒刷新一次到磁盤

1是最安全的設(shè)置，保證不會(huì)丟失任何已經(jīng)提交的事務(wù)，這也是默認(rèn)的設(shè)置。0和2最主要的區(qū)別是，如果MySQL掛了，2不會(huì)丟失事務(wù)，但0有可能，2在每次事務(wù)提交時(shí)，至少將日志緩沖刷新到操作系統(tǒng)的緩存，而0則不會(huì)。如果整個(gè)服務(wù)器掛了或者斷電了，則還是可能會(huì)丟失一些事務(wù)。

innodb-flush-method

前面都在討論使用什么樣的策略刷新、以及何時(shí)刷新日志或者數(shù)據(jù)，那InnoDB具體是怎樣刷新數(shù)據(jù)的？使用innodb-flush-method選項(xiàng)可以配置InnoDB如何跟文件系統(tǒng)相互作用。從名字上看，會(huì)以為只能影響InnoDB怎么寫數(shù)據(jù)，實(shí)際上還影響了InnoDB怎么讀數(shù)據(jù)。windows和非Windows操作系統(tǒng)下這個(gè)選項(xiàng)的值是互斥的，也就是說(shuō)有些值只能Windows下使用，有些只能在非Windows下使用，其中Windows下可取值：async_unbuffered、unbuffered、normal、Nosync與littlesync，非Windows取值：fdatasync、0_DIRECT、 0_DSYNC。

這個(gè)選項(xiàng)既會(huì)影響日志文件，也會(huì)影響數(shù)據(jù)文件，而且有時(shí)候?qū)Σ煌愋偷奈募奶幚硪膊灰粯?，?dǎo)致這個(gè)選項(xiàng)有些難以理解。如果有一個(gè)選項(xiàng)來(lái)配置日志文件，一個(gè)選項(xiàng)來(lái)配置數(shù)據(jù)文件，應(yīng)該會(huì)更好，但實(shí)際上它們混合在同一個(gè)配置項(xiàng)中。這里只介紹類Unix操作系統(tǒng)下的選項(xiàng)。

fdatasync

InnoDB調(diào)用fsync()和fdatasync()函數(shù)來(lái)刷新數(shù)據(jù)和日志文件，其中fdatasync()只刷文件的數(shù)據(jù)，但不包含元數(shù)據(jù)(比如：訪問權(quán)限、文件擁有者、最后修改時(shí)間等描述文件特征的系統(tǒng)數(shù)據(jù))，因此fsync()相比fdatasync()會(huì)產(chǎn)生更多的I/O，但在某些場(chǎng)景下fdatasync()會(huì)導(dǎo)致數(shù)據(jù)損壞，因此InnoDB開發(fā)者決定用fsync()來(lái)代替fdatasync()。

fsync()的缺點(diǎn)是操作系統(tǒng)會(huì)在自己的緩存中緩沖一些數(shù)據(jù)，理論上雙重緩沖是浪費(fèi)的，因?yàn)镮nnoDB自己會(huì)管理緩沖，而且比操作系統(tǒng)更加智能。但如果文件系統(tǒng)能有更智能的I/O調(diào)度和批量操作，雙重緩沖也并不一定是壞事：

• 有的文件系統(tǒng)和os可以累積寫操作后合并執(zhí)行，通過對(duì)I/O的重排序來(lái)提升效率、或者并發(fā)寫入多個(gè)設(shè)備
• 有的還可以做預(yù)讀優(yōu)化，比如連續(xù)請(qǐng)求幾個(gè)順序的塊，它會(huì)通知硬盤預(yù)讀下一個(gè)塊

這些優(yōu)化在特定的場(chǎng)景下才會(huì)起作用，fdatasync為innodb-flush-method的默認(rèn)值。

0_DIRCET

這個(gè)設(shè)置不影響日志文件并且不是所有的類Unix系統(tǒng)都有效，但至少在Linux、FreeBSD以及Solaris是支持的。這個(gè)設(shè)置依然使用fsync來(lái)刷新文件到磁盤，但是它完全關(guān)閉了操作系統(tǒng)緩存，并且是所有的讀和寫都直接通過存儲(chǔ)設(shè)置，避免了雙重緩沖。如果存儲(chǔ)設(shè)備支持寫緩沖或預(yù)讀，那么這個(gè)選項(xiàng)并不會(huì)影響到設(shè)備的設(shè)置，比如RAID卡。

0_DSYNC

這個(gè)選項(xiàng)使得所有的寫同步，即只有數(shù)據(jù)寫到磁盤后寫操作才返回，但它只影響日志文件，而不影響數(shù)據(jù)文件。

說(shuō)完了每個(gè)配置的作用，最后是一些建議：如果使用類Unix操作系統(tǒng)并且RAID控制器帶有電池保護(hù)的寫緩存，建議使用0_DIRECT，如果不是，默認(rèn)值或者0_DIRECT都可能是最好的選擇。

innodb-file-per-table

最后一個(gè)配置，說(shuō)說(shuō)InnoDB表空間，InnoDB把數(shù)據(jù)保存在表空間內(nèi)，它本質(zhì)上是一個(gè)由一個(gè)或者多個(gè)磁盤文件組成的虛擬文件系統(tǒng)。InnoDB表空間并不只是存儲(chǔ)表和索引，它還保存了回滾日志、插入緩沖、雙寫緩沖以及其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)，除此之外，表空間還實(shí)現(xiàn)了很多其它的功能?？梢酝ㄟ^innodb-data-file-path配置項(xiàng)定制表空間文件，innodb-data-home-dir配置表空間文件存放的位置，比如：

innodb-data-home-dir = /var/lib/mysql 
innodb-data-file-path = ibdata1:1G;ibdata2:1G;ibdata3:1G 

這里在3個(gè)文件中創(chuàng)建了3G表空間，為了允許表空間在超過了分配的空間時(shí)還能增長(zhǎng)，可以像這樣配置最后一個(gè)文件自動(dòng)擴(kuò)展

innodb-data-file-path =ibdata1:1G;ibdata2:1G;ibdata3:1G:autoextend 

innodb-file-per-table選項(xiàng)讓InnoDB為每張表使用一個(gè)文件，這使得在刪除一張表時(shí)回收空間容易很多，而且特別容易管理，并且可以通過查看文件大小來(lái)確定表大小，所以這里建議打開這個(gè)配置。

總結(jié)

MySQL有太多的配置項(xiàng)，這里沒有辦法一一列舉，重要的是了解每個(gè)配置的工作原理，從一個(gè)基礎(chǔ)配置文件開始，設(shè)置符合服務(wù)器軟硬件環(huán)境與工作負(fù)載的基本選項(xiàng)。

參考資料

[1] Baron Scbwartz 等著；寧海元 周振興等譯；高性能MySQL（第三版）; 電子工業(yè)出版社， 2013

備注：水平有限，難免疏漏，如果問題請(qǐng)留言
本文已經(jīng)同步更新到微信公眾號(hào)：輕描淡寫CODE ? 我必須得告訴你的MySQL優(yōu)化原理3