聊聊 MySQL 配置。

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

如果讓你在某種環境上安裝配置 MySQL，你會怎么做？安裝后，直接 copy 修改示例配置文件，應該是大多數人的做法。但強烈建議不要怎么做，首先，示例配置文件有非常多注釋掉的配置項，它可能會誘使你打開一個你并不了解的配置，而且這些注釋還不一定準確。其次，MySQL 的一些配置對于現代化的硬件和工作負載來說，有點過時了。

MySQL 有非常多的配置項可以修改，但大多數情況下，你都不應該隨便修改它，因為錯誤或者沒用的配置導致的潛在風險非常大，而且還很難定位問題。確保基本配置正確，然后小心診斷問題，確認問題恰好可以通過某個配置項解決，緊接著再修改這個配置吧。

其實，創建一個好的配置，最快方法不是從學習配置項開始，也不是問哪個配置項應該怎么設置或者怎么修改開始，更不是從檢查服務器行為和詢問哪個配置項可以提升性能開始。最好是從理解 MySQL 內核和行為開始，然后利用這些知識來指導你配置 MySQL。

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

MySQL 配置的工作原理

MySQL 從哪兒獲得配置信息：命令行參數和配置文件。類 Unix 系統中，配置文件一般位于 /etc/my.cnf 或者 /etc/mysql/my.cnf。在啟動時，可以通過命令行參數指定配置文件的位置，當然命令行中也可以指定其它參數，服務器會讀取配置文件的內容，刪除所有注釋和換行，然后和命令行選項一起處理。

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

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

一個典型的配置文件包含多個部分，每個部分的開頭是一個方括號括起來的分段名稱。MySQL 程序通常讀取跟它同名的分段部分，比如，許多客戶端程序讀取[client]部分。服務器通常讀取[mysqld]這一段，一定要確認配置項放在了文件正確的分段中，否則配置是不會生效的。

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

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

配置項可以有多個作用域：全局作用域、會話作用域 (每個連接作用不同)、對象作用域。很多會話級配置項跟全局配置相等，可以認為是默認值，如果改變會話級配置項，它只影響改動的當前連接，當連接關閉時，所有的參數變更都會失效。下面有幾個示例配置項：

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

配置文件中的變量 (配置項) 有很多 (但不是所有) 可以在服務器運行時修改，MySQL 把這些歸為動態配置變量：

-- 設置全局變量，GLOBAL和@@global作用是一樣的 set GLOBAL sort-buffer-size = <value> set @@global.sort-buffer-size := <value>-- 設置會話級變量，下面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命令可以同時設置多個變量，但其中只要有一個變量設置失敗，所有的變量都未生效 SET GLOBAL sort-buffer-size = 100, SESSION sort-buffer-size = 1000; SET GLOBAL max-connections = 1000, sort-buffer-size = 1000000;

動態的設置變量，MySQL 關閉時這些變量都會失效。如果在服務器運行時修改了變量的全局值，這個值對當前會話和其他任何已經存在的會話都不起效果，這是因為會話的變量值是在連接創建時從全局值初始化而來的。注意，在配置修改后，需要確認是否修改成功。

你可能注意到，上面的示例中，有些使用 “=”，有些使用 “:=”。對于 set 命令本身來說，兩種賦值運算符沒有任何區別，在命令行中使用任一運算符符，均可以生效。而在其他語句中，賦值運算符必須是 “:=”，因為在非 set 語句中 “=” 被視為比較運算符。具體可以參考如下示例：
詳細示例可以參考：stackoverflow

-- @exp 表示用戶變量，上面的示例均是系統變量 -- 錯誤 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變量指定表可以被緩存的數量，而不是表可以被緩存的字節數。而key-buffer-size則是以字節為單位。

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

小心翼翼的配置 MySQL

我們常常動態的修改配置，但請務必小心，因為它們可能導致數據庫做大量耗時的工作，從而影響數據庫的整體性能。比如從緩存中刷新臟塊，不同的刷新方式對 I/O 的影響差別很大 (后文會具體說明)。最好把一些好的習慣作為規范合并到工作流程中去，就比如：

好習慣 1：不要通過配置項的名稱來推斷一個變量的作用

不要通過配置項的名稱來推斷一個變量的作用，因為它可能跟你想象的完全不一樣。比如：

• read-buffer-size：當 MySQL 需要順序讀取數據時，如無法使用索引，其將進行全表掃描或者全索引掃描。這時，MySQL 按照數據的存儲順序依次讀取數據塊，每次讀取的數據塊首先會暫存在緩存中，當緩存空間被寫滿或者全部數據讀取結束后，再將緩存中的數據返回給上層調用者，以提高效率。
• read-rnd-buffer-size：和順序讀取相對應，當 MySQL 進行非順序讀取（隨機讀取）數據塊的時候，會利用這個緩沖區暫存讀取的數據。比如：根據索引信息讀取表數據、根據排序后的結果集與表進行 Join 等等。總的來說，就是當數據塊的讀取需要滿足一定的順序的情況下，MySQL 就需要產生隨機讀取，進而使用到read-rnd-buffer-size參數所設置的內存緩沖區。

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

好習慣 2：不要輕易在全局修改會話級別的配置

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

SET @@seession.sort-buffer-size := <value> -- 執行查詢的sql SET @@seession.sort-buffer-size := DEFAULT -- 恢復默認值 -- 可以將類似的代碼封裝在函數中方便使用。

好習慣 3：配置變量時，并不是值越大越好

配置變量時，并不是值越大越好，而且如果設置的值太高，可能更容易導致內存問題。在修改完成后，應該通過監控來確認變量的修改對服務器整體性能的影響。

好習慣 4：規范注釋，版本控制

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

說完了好習慣，再來說說不好的習慣。

壞習慣 1：根據一些 “比率” 來調優

一個經典的按 “比率” 調優的經驗法則是，緩存的命中率應該高于某個百分比，如果命中率過低，則應該增加緩存的大小。這是非常錯誤的意見，大家可以仔細思考一下：緩存的命中率跟緩存大小有必然聯系嗎？(分母變大，值就變大了？) 除非確實是緩存太小了。關于 MyISAM 鍵緩沖命中率，下文會詳細說明。

壞習慣 2：隨便使用調優腳本

盡量不要使用調優腳本！不同的業務場景、不同的硬件環境對 MySQL 的性能要求是不一樣的。比如有些業務對數據的完整性要求較高，那么就一定要保證數據不丟失，出現故障后可恢復數據，而有些業務卻對數據的完整性要求沒那么高，但對性能要求更高。因此，即使是同一個變量，在這兩個不同場景下，其配置的值也應該是不同的。那你還能放心的使用網上找到的腳本嗎 ？

本小節示例的幾個配置項，僅用于舉例說明，并不代表它們有多么重要，請根據實際應用場景配置它們。就比如sort-buffer-size，你真的需要 100M 內存來緩存 10 行數據？

給你一個基本的 MySQL 配置

前面已經說到，MySQL 可配置性太強，看起來需要花很多時間在配置上，但其實大多數配置的默認值已經是最佳的，最好不要輕易改動太多的配置，你甚至不需要知道某些配置的存在。這里有一個最小的示例配置文件，可以作為服務器配置文件的一個起點，其中有一些配置項是必須的。本節將為你詳細剖析每個配置有何作用？為什么要配置它？怎么確定合適的值？

[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 配置文件的格式為集中式，通常會分成好幾部分，可以為多個程序提供配置，如[client]、[mysqld]、[mysql]等等。MySQL 程序通常是讀取與它同名的分段部分。

• [client]客戶端默認設置內容
• [mysql]使用 mysql 命令登錄 MySQL 數據庫時的默認設置
• [mysqld]數據庫本身的默認設置

例如服務器 mysqld 通常讀取[mysqld]分段下的相關配置項。如果配置項位置不正確，該配置是不會生效的。

GENERAL

首先創建一個用戶 mysql 來運行 mysqld 進程，請確保這個用戶擁有操作數據目錄的權限。設置默認端口為 3306，有時為了安全，可能會修改一下。默認選擇 Innodb 存儲引擎，在大多數情況下是最好的選擇。但如果默認是 InnoDB，卻需要使用 MyISAM 存儲引擎，請顯式地進行配置。許多用戶認為其數據庫使用了某種存儲引擎但實際上卻使用的是另外一種，就是因為默認配置的問題。

接著設置數據文件的位置，這里把 pid 文件和 socket 文件放到相同的位置，當然也可以選擇其它位置，但要注意的是不要將 socket 文件和 pid 文件放到 MySQL 編譯的默認位置，因為不同版本的 MySQL，這兩個文件的默認路徑可能會不一致，最好明確地設置這些文件的位置，以免版本升級時出現問題。

在類 UNIX 系統下本地連接 MySQL 可以采用 UNIX 域套接字方式，這種方式需要一個套接字（socket）文件，即配置中的mysql.sock文件。

當 MySQL 實例啟動時，會將自己的進程 ID 寫入一個文件中——該文件即為 pid 文件。該文件可由參數pid-file控制，默認位于數據庫目錄下，文件名為主機名.pid。

DATA STORAGE

datadir用于配置數據文件的存儲位置，沒有什么好說的。

為緩存分配內存

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

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

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

如果只使用單一存儲引擎，配置服務器就會簡單許多。如果只使用 MyISAM 表，就可以完全關閉 InnoDB，而如果只使用 InnoDB，就只需要分配最少的資源給 MyISAM（MySQL 內部系統表使用 MyISAM 引擎）。但如果是混合使用各種存儲引擎，就很難在他們之間找到恰當的平衡，因此只能根據業務做一個猜測，然后在運行中觀察服務器運行狀況后做出調整。

MyISAM

key-buffer-size

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

假設整個數據庫中表的索引大小為 X，肯定不需要把緩存設置得比 X 還大，所以當前的索引大小就成為這個配置項的重要依據。可以通過下面兩種方式來查詢當前索引的大小：

通過 SQL 語句查詢

SELECT SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MYISAM'

?

統計索引文件的大小

$ 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

?

你可能會問，剛創建好的數據庫，根本就沒什么數據，索引文件大小為 0，那如何配置鍵緩存大小？這時候只能根據經驗值：不超過為操作系統緩存保留內存的 25% ~ 50%。設置一個基本值，等運行一段時間后，根據運行情況來調整鍵緩存大小。總結來說，索引大小與 OS 緩存的 25%~50% 兩者間取小者。當然還可以計算鍵緩存的使用情況，如果一段時間后還是沒有使用完所有的鍵緩存，就可以把緩沖區調小一點，計算緩存區的使用率可以通過以下公式：

(key_blocks_unused * key_cache_block_size) / key_buffer_size

說明：
key_blocks_unused 的值可以通過 SHOW STATUS 獲取
key_cache_block_size 的值可以通過 SHOW VARIABLES 獲取

鍵緩存塊大小是一個比較重要的值，因為它影響 MyISAM、OS 緩存以及文件系統之間的交互。如果緩存塊太小，可能會碰到寫時讀取 (OS 在寫數據之前必須先從磁盤上讀取一些數據)，關于寫時讀取的相關知識，大家可以自行查閱。

關于緩存命中率，這里再說一點。緩存命中率有什么意義？其實這個數字沒太大的作用。比如 99% 和 99.9% 之間看起來差距很小，但實際上代表了 10 倍的差距。緩存命中率的實際意義與應用也有很大關系，有些應用可以在命中率 99% 下良好的工作，有些 I/O 密集型應用，可能需要 99.99%。所以從經驗上來說，每秒未命中次數這個指標實際上會更有用一些。比如每秒 5 次未命中可能不會導致 IO 繁忙，但每秒 100 次緩存未命中則可能出現問題。

MyISAM 鍵緩存的每秒未命中次數可以通過如下命令監控：

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

最后，即使沒有使用任何 MyISAM 表，依然需要將key-buffer-size設置為較小值，比如 32M，因為 MySQL 內部會使用 MyISAM 表，比如 GROUP BY 語句可能會創建 MyISAM 臨時表。

myisam-recover

myisam-recover選項用于配置 MyISAM 怎樣尋找和修復錯誤。打開這個選項會通知 MySQL 在打開表時，檢查表是否損壞，并在找到問題時進行修復，它可以設置如下值：

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

可以設置多個值，每個值用逗號隔開，比如配置文件中的BACKUP,FORCE會強制恢復并且創建備份，這樣配置在只有一些小的 MyISAM 表時有用，因為服務器運行著一些損壞的 MyISAM 表是非常危險的，它們有時可能會導致更多數據損壞，甚至服務器崩潰。然而如果有很大的表，它會導致服務器打開所有的 MyISAM 表時都檢查和修復，大表的檢查和修復可能會耗費大量時間，且在這段時間里，MySQL 會阻止這個連接做其它任何操作，這顯然是不切實際的。

因此，在默認使用 InnoDB 存儲引擎時，數據庫中只有非常小的 MyISAM 表時，只需要配置key-buffe-size于一個很小的值 (32M) 以及myisam-recover=BACKUP,FORCE。當數據庫中大部分表為 MyISAM 表時，請根據上文的公式合理配置key-buffer-size，而myisam-recover則可以關閉，在啟動后使用CHECK TABLES和REPAIR TABLES命令來做檢查和修復，這樣對服務器的影響比較小。

SAFETY

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

max-allowed-packet

max-allowed-packet防止服務器發送太大的數據包，也控制服務器可以接收多大的包。默認值 4M，可能會比較小。如果設置太小，有時復制上會出問題，表現為從庫不能接收主庫發過來的復制數據。如果表中有 Blob 或者 Text 字段，且數據量較大的話，要小心，如果數據量超過這個變量的大小，它們可能被截斷或者置為 NULL，這里建議設置為 16M。

max-connect-errors

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

這個值默認為 10，太小了，有時候網絡抽風或者應用配置出現錯誤導致短時間內不斷嘗試重連服務器，客戶端就會被列入黑名單，導致無法連接。如果在內網環境，可以確認沒有安全問題可以把這個值設置的大一點，默認值太容易導致問題。

LOGGING

接下來看下日志的配置，對于 MySQL 來說，慢日志和 bin-log 是非常重要的兩種日志，前者可以幫助應用程序監控性能問題，后者在數據同步、備份等方面發揮著非常重要的作用。

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

sync-binlog

sync-binlog控制當事務提交之后，MySQL 是否將 bin-log 刷新到磁盤。如果其值等于 0 或者大于 1 時，當事務提交之后，MySQL 不會將 bin-log 刷新到磁盤，其性能最高，但存在的風險也是最大的，因為一旦系統崩潰，bin-log 將會丟失。而當其值等于 1 時，是最安全的，這時候即使系統崩潰，最多也就丟失本次未完成的事務，對實際的數據沒有實質性的影響，但性能較差。

需要注意的是，在 5.7.7 之前的版本，這個選擇的默認值為 0，而之后的版本默認值為 1，也就是最安全的策略。對于高并發的性能，需要關注這一點，防止版本升級后出現性能問題。

剩下的 4 個配置項就沒太多要說的。

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

CACHES AND LIMITS

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

這兩個配置控制使用 Memory 引擎的內存臨時表可以使用多大的內存。如果隱式內存臨時表的大小超過這兩個值，將會被轉為磁盤 MyISAM 表 (隱式臨時表由服務器創建，用戶保存執行中的查詢的中間結果)。

如果查詢語句沒有創建龐大的臨時表 (通過合理的索引和查詢設計來避免)，可以把這個值設大一點，以免需要把內存臨時表轉換為磁盤臨時表。但要謹防這個值設置得過大，如果查詢確實會創建很大的臨時表，那么還是使用磁盤比較好，畢竟并發數一起來，所需要的內存就會急劇增長。

應該簡單的把這兩個變量設為同樣的值，這里選擇了 32M，可以通過仔細檢查created-tmp-disk-tables和created-tmp-tables兩個變量來指導你設置，這兩個變量的值將展示臨時表的創建有多頻繁。

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

看前面

max-connections

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

在服務器資源允許的情況下，可以把max-connections設置的足夠大，以容納正常可能達到的負載。若認為正常情況將有 300 或者更多連接，可以設置為 500 或者更多 (應對高峰期)。默認值是 100，太小了，這里設置為 500，但并不意味著其是一個合理的值，應該監控應用有多少連接，然后根據監控值 (觀察max_used_connections隨時間的變化) 來設置。

thread-cache-size

線程緩存保存那些當前沒有與連接關聯但是準備為后面新連接服務的線程。當一個新的連接創建時，如果緩存中有線程存在，MySQL 則從緩存中刪除一個線程，并且把它分配給這個新連接。當連接關閉時，如果線程緩存還有空間的話，MySQL 又會把線程放回緩存。如果沒有空間的話，MySQL 會銷毀這個線程。只要 MySQL 在緩存里還有空閑的線程，它就可以迅速響應連接請求，因為這樣就不用為每個連接創建新線程。thread-cache-size指定 MySQL 可以保存在緩存中的線程數量。如果服務器沒有很多的連接請求，一般不需要配置這個值。

如何判斷這個值該設置多大？

觀察threads-connected變量，如果threads-connected在 100-120，那么thread-cache-size設置為 20。如果它保持在 500-700，200 的線程緩存應該足夠大了。可以這么理解：當同時有 700 個連接時，可能緩存中沒有線程。在 500 個連接時，有 200 個緩存的線程準備為負載再次增加到 700 個連接時使用。

open-files-limit

在類 Uinux 系統上我們把它設置得盡可能大。現代 OS 中打開句柄開銷都很小，如果此參數設置過小，可能會遇到 “打開的文件太多 (too many open files)” 錯誤。

table_cache_size

表緩存跟線程緩存類似，但存儲的對象是表，其包含表. frm 文件的解析結果和一些其他數據。準確的說，緩存的數據依賴于存儲引擎，比如，對于 MyISAM，緩存表的數據和索引的文件描述符。表緩存對 InnoDB 的存儲引擎來說，重要性會小很多，因為 InnoDB 不依賴它來做那么多的事。

從 5.1 版本及以后，表緩存就被分為兩個部分：打開表緩存和定義表緩存，分別通過table-open-cache-size和table-definition-cache-size變量來配置。通常可以把table-definition-cache-size設置得足夠高，以緩存所有的表定義，因為大部分存儲引擎都能從table-definition-cache獲益。

InnoDB

InnoDB 應該是使用最廣發的存儲引擎，最重要的配置選項是下面這兩個：innodb-buffer-pool-size與innodb-log-file-size，解決這兩個配置基本上就解決了真實場景下的大部分配置問題。

innodb-buffer-pool-size

如果大部分是 InnoDB 表，那么 InnoDB 緩沖池或許比其他任何東西都更需要內存，InnoDB 緩沖池緩沖的數據：索引、行數據、自適應哈希索引、插入緩沖、鎖以及其他內部數據結構。InnoDB 還使用緩沖池來幫助延遲寫入，這樣就可以合并多個寫入操作，然后一起順序寫入，提升性能。總之，InnoDB 嚴重依賴緩沖池，必須為其分配足夠的內存。

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

可以看到示例的配置文件中把這個值配置為 12G，這不是一個標準配置，需要根據具體的硬件來估算。那如何估算？

前面的小節，我們說到，MySQL 中最重要的緩存有 5 種，可以簡單的使用下面的公式計算：

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

具體來看，至少需要為 OS 保留 1~2G 內存，如果機器內存大的話可以預留多一些，建議 2GB 和總內存的 5% 為基準，以較大者為準，如果機器上還運行著一些內存密集型任務，比如，備份任務，那么可以為 OS 再預留多一些內存。不要為 OS 緩存增加任何內存，因為 OS 通常會利用所有剩下的內存來做文件緩存。

一般來說，運行 MySQL 的服務器很少會運行其他應用程序，但如果有的話，請為這些應用程序預留足夠多的內存。

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

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

公式中就剩下 InnoDB 日志文件了，這就是我們接下來要說的。

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

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

InnoDB 使用一個后臺線程智能地刷新這些變更到數據文件。實際上，事務日志把數據文件的隨機 I/O 轉換為幾乎順序地日志文件和數據文件 I/O，讓刷新操作在后臺可以更快的完成，并且緩存 I/O 壓力。

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

修改日志文件的大小，需要完全關閉 MySQL，然后將舊的日志文件遷移到其他地方，重新配置參數，然后重啟。重啟時需要將舊的日志遷移回來，然后等待 MySQL 恢復數據后，再刪除舊的日志文件，請一定要查看錯誤日志，確認 MySQL 重啟成功后再刪除舊的日志文件。

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

• MySQL-checkpoint 技術
• How InnoDB performs a checkpoint

innodb-flush-log-at-trx-commit

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

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

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

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

innodb-flush-method

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

這個選項既會影響日志文件，也會影響數據文件，而且有時候對不同類型的文件的處理也不一樣，導致這個選項有些難以理解。如果有一個選項來配置日志文件，一個選項來配置數據文件，應該會更好，但實際上它們混合在同一個配置項中。這里只介紹類 Unix 操作系統下的選項。

fdatasync

InnoDB 調用fsync()和fdatasync()函數來刷新數據和日志文件，其中fdatasync()只刷文件的數據，但不包含元數據 (比如：訪問權限、文件擁有者、最后修改時間等描述文件特征的系統數據)，因此fsync()相比fdatasync()會產生更多的 I/O，但在某些場景下fdatasync()會導致數據損壞，因此 InnoDB 開發者決定用fsync()來代替fdatasync()。

fsync()的缺點是操作系統會在自己的緩存中緩沖一些數據，理論上雙重緩沖是浪費的，因為 InnoDB 自己會管理緩沖，而且比操作系統更加智能。但如果文件系統能有更智能的 I/O 調度和批量操作，雙重緩沖也并不一定是壞事：

• 有的文件系統和 os 可以累積寫操作后合并執行，通過對 I/O 的重排序來提升效率、或者并發寫入多個設備
• 有的還可以做預讀優化，比如連續請求幾個順序的塊，它會通知硬盤預讀下一個塊

這些優化在特定的場景下才會起作用，fdatasync為innodb-flush-method的默認值。

0_DIRCET

這個設置不影響日志文件并且不是所有的類 Unix 系統都有效，但至少在 Linux、FreeBSD 以及 Solaris 是支持的。這個設置依然使用 fsync 來刷新文件到磁盤，但是它完全關閉了操作系統緩存，并且是所有的讀和寫都直接通過存儲設置，避免了雙重緩沖。如果存儲設備支持寫緩沖或預讀，那么這個選項并不會影響到設備的設置，比如 RAID 卡。

0_DSYNC

這個選項使得所有的寫同步，即只有數據寫到磁盤后寫操作才返回，但它只影響日志文件，而不影響數據文件。

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

innodb-file-per-table

最后一個配置，說說 InnoDB 表空間，InnoDB 把數據保存在表空間內，它本質上是一個由一個或者多個磁盤文件組成的虛擬文件系統。InnoDB 表空間并不只是存儲表和索引，它還保存了回滾日志、插入緩沖、雙寫緩沖以及其他內部數據結構，除此之外，表空間還實現了很多其它的功能。可以通過 innodb-data-file-path 配置項定制表空間文件，innodb-data-home-dir配置表空間文件存放的位置，比如：

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

這里在 3 個文件中創建了 3G 表空間，為了允許表空間在超過了分配的空間時還能增長，可以像這樣配置最后一個文件自動擴展

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

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

總結

MySQL 有太多的配置項，這里沒有辦法一一列舉，重要的是了解每個配置的工作原理，從一個基礎配置文件開始，設置符合服務器軟硬件環境與工作負載的基本選項。

參考資料

高性能 MySQL（第 3 版）

• 本文作者：?Yibo
• 本文鏈接：?https://windmt.com/2018/05/04/mysql-optimization-principle-3/
• 版權聲明：?本博客所有文章除特別聲明外，均采用?CC BY-NC-SA 4.0?許可協議。轉載請注明出處！

總結

以上是生活随笔為你收集整理的MySQL 优化原理（三）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。