你所不知道的MySQL數(shù)據(jù)庫(kù)性能優(yōu)化方案

關(guān)注公眾號(hào)，后臺(tái)回復(fù)“自動(dòng)化”

一、MySQL數(shù)據(jù)庫(kù)引擎的選擇

MySQL數(shù)據(jù)庫(kù)中最重要的一個(gè)概念就是數(shù)據(jù)庫(kù)引擎，不同的數(shù)據(jù)庫(kù)引擎的工作原理存在很大差異最終造成MySQL數(shù)據(jù)庫(kù)服務(wù)的性能差異。

例如如果數(shù)據(jù)庫(kù)引擎需要支持事務(wù)，就必須滿足事務(wù)的基本特性——AICD特性（AICD：原子性、隔離性、一致性和永久性。屬于基礎(chǔ)知識(shí)所以不在這里贅述），那么自然就需要一定處理機(jī)制來(lái)實(shí)現(xiàn)這些特性。這樣做的現(xiàn)實(shí)效果就是導(dǎo)致寫入同樣數(shù)據(jù)量的情況下，支持事務(wù)的數(shù)據(jù)庫(kù)引擎比不支持事務(wù)的數(shù)據(jù)庫(kù)引擎耗費(fèi)更多的時(shí)間。

這里我們首先為讀者列舉MySQL數(shù)據(jù)庫(kù)社區(qū)版中支持的數(shù)據(jù)庫(kù)引擎（部分）：

1. MEMORY：

MEMORY存儲(chǔ)引擎將表的數(shù)據(jù)完全存放在內(nèi)存中。在MySQL數(shù)據(jù)庫(kù)的歷史版本中和該數(shù)據(jù)庫(kù)引擎類似的其它引擎是HEAP，后者曾是MySQL數(shù)據(jù)庫(kù)中訪問(wèn)速度最快的數(shù)據(jù)庫(kù)引擎。

但由于這兩種數(shù)據(jù)庫(kù)引擎完全工作在內(nèi)存中，所以如果MySQL或者服務(wù)器重新啟動(dòng)，數(shù)據(jù)庫(kù)引擎中保存的數(shù)據(jù)將會(huì)丟失。

2. BLACKHOLE:

中文名“黑洞”，使用BLACKHOLE數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)表不存儲(chǔ)任何數(shù)據(jù)，只根據(jù)數(shù)據(jù)庫(kù)操作過(guò)程記錄二進(jìn)制日志。它的主要作用是作為MySQL主從復(fù)制的中繼器，并且可以在其上面添加業(yè)務(wù)過(guò)濾機(jī)制。

3. MyISAM:

MyISAM數(shù)據(jù)庫(kù)引擎是MySQL數(shù)據(jù)庫(kù)默認(rèn)的數(shù)據(jù)庫(kù)引擎。MyISAM使用一種表格鎖定的機(jī)制，來(lái)優(yōu)化多個(gè)并發(fā)的讀寫操作（實(shí)際上就是使用的一種避免數(shù)據(jù)臟讀的機(jī)制）。

但是這種機(jī)制對(duì)存儲(chǔ)空間的使用有一定的浪費(fèi)。MyISAM還有一些有用的擴(kuò)展，例如用來(lái)修復(fù)數(shù)據(jù)庫(kù)文件的MYISAMCHK工具和用來(lái)恢復(fù)浪費(fèi)空間的MYISAMPACK工具。本文所介紹的MySQL數(shù)據(jù)庫(kù)相關(guān)技術(shù)將不涉及到這種數(shù)據(jù)庫(kù)引擎。

4. InnoDB:

InnoDB數(shù)據(jù)庫(kù)引擎是在各種版本的MySQL數(shù)據(jù)庫(kù)中使用最廣泛的一種數(shù)據(jù)庫(kù)引擎，本文后續(xù)的介紹中如果沒(méi)有特別說(shuō)明都默認(rèn)是在說(shuō)InnoDB數(shù)據(jù)庫(kù)引擎。InnoDB數(shù)據(jù)庫(kù)引擎使用日志機(jī)制提供事務(wù)的支持

要了解MySQL數(shù)據(jù)庫(kù)中的性能問(wèn)題，就首先要搞清楚在客戶端向MySQL數(shù)據(jù)庫(kù)提交一個(gè)事務(wù)操作時(shí)后者到底做了些什么事情，以及主要是怎么做的。本節(jié)所描述的工作過(guò)程主要圍繞InnoDB數(shù)據(jù)庫(kù)引擎進(jìn)行：

上圖中筆者只畫出了InnoDB數(shù)據(jù)庫(kù)引擎在insert/update一個(gè)事務(wù)的過(guò)程中所涉及的重要工作區(qū)域，InnoDB的實(shí)際工作細(xì)節(jié)要比上圖所示的步驟復(fù)雜得多。

上文已經(jīng)說(shuō)到InnoDB數(shù)據(jù)庫(kù)引擎是一個(gè)支持事務(wù)的數(shù)據(jù)庫(kù)引擎，那么如何解決異常崩潰情況下的數(shù)據(jù)一致性問(wèn)題就是它的設(shè)計(jì)中最重要的任務(wù)之一。

InnoDB數(shù)據(jù)庫(kù)引擎采用日志來(lái)解決這個(gè)問(wèn)題，請(qǐng)注意這里說(shuō)的InnoDB數(shù)據(jù)庫(kù)引擎日志，并不是MySQL數(shù)據(jù)庫(kù)全局的二進(jìn)制日志。InnoDB數(shù)據(jù)庫(kù)引擎日志還有另外一個(gè)名字：重做日志（redo log），這是因?yàn)檫@部分日志主要的作用就是在數(shù)據(jù)庫(kù)異常崩潰并重啟后進(jìn)行InnoDB引擎中數(shù)據(jù)的恢復(fù)。

為了提高M(jìn)ySQL數(shù)據(jù)庫(kù)的性能，InnoDB數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)操作過(guò)程基本上都在內(nèi)存中完成，然后通過(guò)一定的策略（后文會(huì)詳細(xì)介紹）將InnoDB Log Buffer內(nèi)存區(qū)域中的日志數(shù)據(jù)同步到磁盤上的InnoDB File Log Group區(qū)域。

InnoDB File Log Group區(qū)域主要用于存儲(chǔ)InnoDB數(shù)據(jù)庫(kù)引擎的日志文件，它由多個(gè)大小相同的日志文件構(gòu)成并且這些文件都采用順序讀寫。innodb_log_file_size參數(shù)將決定每個(gè)文件的大小，而innodb_log_files_in_group參數(shù)將決定整個(gè)日志組中有多少個(gè)日志文件。

當(dāng)MySQL數(shù)據(jù)庫(kù)完成初始化過(guò)程后這些日志文件將會(huì)按照參數(shù)的設(shè)置值，在磁盤上預(yù)占一個(gè)連續(xù)的磁盤空間。這樣做的現(xiàn)象就是雖然數(shù)據(jù)庫(kù)中還沒(méi)有任何數(shù)據(jù)，但是日志文件的總大小就已經(jīng)是 innodb_log_file_size * innodb_log_files_in_group所得到的數(shù)值了：

InnoDB數(shù)據(jù)庫(kù)引擎 日志文件示例....total 1.0G-rw-rw---- 1 mysql mysql 500M May 4 06:09 ib_logfile0-rw-rw---- 1 mysql mysql 500M May 4 06:09 ib_logfile1....這樣做的目的是保證了后續(xù)同步日志數(shù)據(jù)的操作都是順序?qū)懀皇请S機(jī)寫。當(dāng)日志數(shù)據(jù)寫到最后一個(gè)文件的末尾時(shí)，下一條日志數(shù)據(jù)又會(huì)重新從第一個(gè)日志文件的開始位置進(jìn)行寫入。

InnoDB Log Buffer內(nèi)存空間中的四個(gè)標(biāo)識(shí)指針是InnoDB數(shù)據(jù)庫(kù)引擎日志處理部分最重要元素，它們分別是：Log sequence、Log flushed、Pages flushed和Last checkpoint，這四個(gè)標(biāo)識(shí)涉及到InnoDB在崩潰重啟時(shí)不同的數(shù)據(jù)恢復(fù)策略，以及I/O性能優(yōu)化中的關(guān)鍵原理。

這四個(gè)標(biāo)識(shí)實(shí)際上是四個(gè)數(shù)值它們共享一個(gè)數(shù)值池（名叫LSN，日志序列號(hào)，其總長(zhǎng)度是64位無(wú)符號(hào)整數(shù)），代表當(dāng)前InnoDB對(duì)事務(wù)操作的處理狀態(tài)。并且它們數(shù)值有以下特點(diǎn)：Log sequence >= Log flushed >= Pages flushed >= Last checkpoint

1. I/O性能問(wèn)題（1）

2. I/O性能問(wèn)題（2）

1 innodb_flush_log_at_trx_commit = 0時(shí)，InnoDB將按照1秒鐘為單位向磁盤寫入這個(gè)階段所有已完成的事務(wù)日志信息。這里的寫入成功并不是說(shuō)寫入到Linux操作系統(tǒng)的Page Cache中就算成功，而是需要等待操作系統(tǒng)真正寫到了物理磁盤上的通知（具體請(qǐng)參見(jiàn)之前講解文件系統(tǒng)的文章）。

這意味 著即使InnoDB Buffer Pool中的數(shù)據(jù)操作是成功的，但是一旦數(shù)據(jù)庫(kù)系統(tǒng)異常崩潰，那么業(yè)務(wù)系統(tǒng)將會(huì)丟失前1秒內(nèi)寫入的數(shù)據(jù)：因?yàn)闆](méi)有磁盤介質(zhì)上的日志就無(wú)法在異常重啟后恢復(fù)數(shù)據(jù)信息。

2 innodb_flush_log_at_trx_commit = 1時(shí)，InnoDB按照完成一個(gè)日志操作就向磁盤寫入事務(wù)日志信息的方式來(lái)工作（執(zhí)行一個(gè)事務(wù)就寫入一個(gè)事務(wù)日志）。同樣，這里的寫入成功同樣是要等待操作系統(tǒng)返回真正寫入了物理磁盤的通知。

3 innodb_flush_log_at_trx_commit = 2時(shí)，InnoDB按照完成一個(gè)日志操作就向磁盤寫入日志信息的方式來(lái)工作。但是，這種工作模式下InnoDB不會(huì)等待操作系統(tǒng)返回物理磁盤上寫入成功的通知，就會(huì)繼續(xù)工作。

實(shí)際上這個(gè)時(shí)候，數(shù)據(jù)一般還存在于Linux操作系統(tǒng)的cache memory區(qū)塊中，所以這種模式下最好使用帶有日志功能的文件系統(tǒng)，并且確認(rèn)開啟了文件系統(tǒng)的日志功能。

InnoDB數(shù)據(jù)庫(kù)引擎在這一步驟的最后一個(gè)動(dòng)作是更改Log flushed標(biāo)識(shí)指針值為當(dāng)前最后完成刷新動(dòng)作的事務(wù)日志LSN值。實(shí)際上執(zhí)行完這個(gè)步驟，一個(gè)事務(wù)處理操作才算真正成功。

3. I/O性能問(wèn)題（3）

4. I/O性能問(wèn)題（4）

1 當(dāng)代表事務(wù)的LSN數(shù)值在Log sequence——Log flushed范圍內(nèi)時(shí)（不包括Log flushed），說(shuō)明在數(shù)據(jù)庫(kù)崩潰時(shí)內(nèi)存中的事務(wù)并沒(méi)有處理完，這部分事務(wù)操作將在恢復(fù)時(shí)被丟棄。

2 當(dāng)代表事務(wù)的LSN數(shù)值在Log flushed——Pages flushed范圍內(nèi)時(shí)（不包括Pages flushed），說(shuō)明數(shù)據(jù)庫(kù)崩潰時(shí)磁盤上已經(jīng)擁有這些事務(wù)完整的日志記錄。

InnoDB數(shù)據(jù)庫(kù)引擎將讀取這些日志數(shù)據(jù)，并繼續(xù)執(zhí)行下去，直到代表這些事務(wù)的LSN值被標(biāo)記為Checkpoint（或者小于Checkpoint標(biāo)識(shí)的LSN值）。這里要注意，在數(shù)據(jù)庫(kù)崩潰時(shí)處于這個(gè)范圍內(nèi)的某些事務(wù)可能已經(jīng)完成了一部分的數(shù)據(jù)同步動(dòng)作，但是肯定是不完整的。所以即使是這樣的事務(wù)也要重新進(jìn)行磁盤同步，才能保證數(shù)據(jù)的一致性。

3 實(shí)際上在MySQL version 5.5的早期版本，InnoDB數(shù)據(jù)庫(kù)引擎中只有三個(gè)標(biāo)識(shí)：Log sequence、Log flushed和Checkpoint。也就是說(shuō)當(dāng)臟頁(yè)成功同步到磁盤后，就會(huì)直接更新Checkpoint標(biāo)識(shí)的LSN值。

后續(xù)版本的MySQL數(shù)據(jù)庫(kù)增加了Pages flushed標(biāo)識(shí)點(diǎn)，這樣做的目的是保證Checkpoint和Pages flush的更新可以擁有獨(dú)立的周期，從而降低其帶來(lái)的性能消耗

1. Log flush和Pages flush

1 例如在讀取Page時(shí)，采用“預(yù)讀”思路將目標(biāo)Page所臨近的Page一起讀取出來(lái)；在寫入Page時(shí)將目標(biāo)Page所臨近的Page一起寫入（“臨近寫”）。“預(yù)讀”策略可以通過(guò)innodb_read_ahead_threshold參數(shù)進(jìn)行設(shè)置，并通過(guò)read thread完成，另外 innodb_flush_neighors參數(shù)可以控制是否開啟“臨近寫”策略。

總的來(lái)說(shuō)“預(yù)讀”/“臨近寫”在默認(rèn)情況下都是開啟的，但“預(yù)讀”/“臨近寫”思路本身就需要一定的準(zhǔn)確性，低命中率的“預(yù)讀”反而會(huì)降低InnoDB的I/O性能。

還有一種“隨機(jī)預(yù)讀”，它在MySQL version 5.6版本中默認(rèn)就是關(guān)閉的，并且在隨后的版本中將會(huì)慢慢廢除，所以這里就不再介紹了。

2 例如將向磁盤提交Page的動(dòng)作設(shè)計(jì)為周期性且批量進(jìn)行，并且始終保持InnoDB Buffer Pool內(nèi)存區(qū)域的臟頁(yè)（Dirty Page）在一定的比例，這些策略主要由innodb_io_capacity、innodb_max_dirty_pages_pct、innodb_io_capacity_max等參數(shù)控制。

3 例如通過(guò)調(diào)整Innodb_Buffer_Pool_size參數(shù)獲得更大的InnoDB Buffer Pool內(nèi)存區(qū)域，存儲(chǔ)更多的Page。實(shí)際上Innodb Buffer Pool區(qū)域不僅包括我們已經(jīng)介紹的Page Cache數(shù)據(jù)部分，還包括其它的數(shù)據(jù)區(qū)塊。

例如為了快速定位B+樹索引的Hash Index結(jié)構(gòu)。調(diào)整Innodb_Buffer_Pool_size參數(shù)將會(huì)使這些數(shù)據(jù)區(qū)域都享受到內(nèi)存容量帶來(lái)的優(yōu)勢(shì)——至少不會(huì)頻繁地發(fā)生內(nèi)容空間的強(qiáng)制清理。

2. 基礎(chǔ)硬件條件

為了解決上一節(jié)中提到的I/O性能問(wèn)題，本文這里基于之前介紹的塊存儲(chǔ)方案的知識(shí)，列出這個(gè)問(wèn)題的幾種解決方案。

除了根據(jù)I/O吞吐量要求對(duì)MySQL數(shù)據(jù)庫(kù)特別是InnoDB引擎的配置參數(shù)進(jìn)行更改以外，本文提到的硬件層解決方法所需要花費(fèi)的資金和能夠得到的I/O性能和擴(kuò)展能力基本上成正比。

上一節(jié)我們已經(jīng)對(duì)InnoDB數(shù)據(jù)庫(kù)引擎（以下簡(jiǎn)稱InnoDB引擎）進(jìn)行事務(wù)操作時(shí)的I/O過(guò)程進(jìn)行了簡(jiǎn)單說(shuō)明，主要介紹了Log flush和Pages flush兩個(gè)過(guò)程。

如果我們需要挖掘正式生產(chǎn)環(huán)境上MySQL數(shù)據(jù)庫(kù)服務(wù)的性能潛力，那么對(duì)MySQL數(shù)據(jù)庫(kù)服務(wù)中的默認(rèn)參數(shù)進(jìn)行更改就是必須要做的事情。在進(jìn)行配置修改之前我們先來(lái)看看如何查看當(dāng)前MySQL數(shù)據(jù)庫(kù)特別是InnoDB引擎的工作狀態(tài)：

通過(guò)執(zhí)行以下命令，我們可以查看當(dāng)前InnoDB引擎的工作狀態(tài)show engine innodb status;

執(zhí)行后可以得到類似如下的執(zhí)行結(jié)果（已省去一部分與本文沒(méi)有涉及到的知識(shí)點(diǎn)所相關(guān)的狀態(tài)描述信息）：

參數(shù)設(shè)置規(guī)則介紹：

1. innodb_log_file_size

單個(gè)日志文件的大小不宜過(guò)小，例如設(shè)置為500MB。由于InnoBD引擎對(duì)日志文件采用順序?qū)懙牟僮鞣绞剑圆槐負(fù)?dān)心日志文件的操作消耗比數(shù)據(jù)文件操作更多的性能。

2. innodb_log_files_in_group

該參數(shù)控制了文件組中日志文件的總數(shù)。設(shè)置為2-5的范圍都不會(huì)有太大影響。更重要的是讀者應(yīng)該清楚innodb_log_file_size * innodb_log_files_in_group就是InnoDB引擎在磁盤上可用日志空間的總大小

3. innodb_log_buffer_size

這個(gè)參數(shù)決定了InnoDB引擎可使用的日志內(nèi)存空間。只要沒(méi)有類似插入blob類型數(shù)據(jù)的操作（也不建議有這樣的操作），這個(gè)內(nèi)存空間都不需要設(shè)置得太大。5MB-10MB是一個(gè)推薦的設(shè)置值，不過(guò)這個(gè)參數(shù)還是要和innodb_flush_log_at_trx_commit參數(shù)配合使用。

4. innodb_flush_log_at_trx_commit

該參數(shù)可以說(shuō)是InnoDB引擎日志操作策略部分最重要的設(shè)置參數(shù)之一。如果您將innodb_flush_log_at_trx_commit設(shè)置為0，代表著InnoDB引擎將會(huì)按照1秒鐘的周期進(jìn)行日志從內(nèi)存到磁盤的同步。

這時(shí)innodb_log_buffer_size的值就不能過(guò)小，因?yàn)樵谝粋€(gè)同步周期內(nèi)如果待刷新的日志超過(guò)了innodb_log_buffer_size設(shè)置的大小，InnoDB就會(huì)強(qiáng)制執(zhí)行同步操作。

如果您的Linux操作系統(tǒng)使用的是帶有日志功能的文件系統(tǒng)并且日志功能是開啟的，那么還是建議將該參數(shù)設(shè)置為2。

5. innodb_buffer_pool_size

這個(gè)參數(shù)調(diào)整分配給InnoDB引擎使用的可用數(shù)據(jù)內(nèi)存區(qū)域的大小。實(shí)際上這個(gè)數(shù)據(jù)區(qū)域不止包括了本文中一直強(qiáng)調(diào)的Page Cache區(qū)域，它還有很多數(shù)據(jù)區(qū)域。

例如InnoDB中用來(lái)進(jìn)行查詢排序的Sort Buffer區(qū)域。建議的設(shè)置大小是MySQL數(shù)據(jù)庫(kù)服務(wù)所在物理服務(wù)器上總內(nèi)存的60%——80%（文件系統(tǒng)的Cache Memory/Buffer Memory等其它程序還要使用）。

8GB的物理服務(wù)器可設(shè)置6GB的InnoDB Buffer Pool可用內(nèi)存區(qū)域。注意，當(dāng)MySQL數(shù)據(jù)庫(kù)啟動(dòng)時(shí)并不是立刻就會(huì)占據(jù)所有數(shù)據(jù)區(qū)域。

6. innodb_buffer_pool_instances

本小節(jié)和本文中多處位置都提及到innodb_buffer_pool_size參數(shù)以及它的含義。這個(gè)參數(shù)值在生產(chǎn)環(huán)境下一般設(shè)置得都比較大（例如4GB、8GB、12GB、24GB等等）。

但是由于臟數(shù)據(jù)刷盤的周期性，在I/O性能強(qiáng)勁的物理機(jī)器上可能就會(huì)存在I/O間歇性低谷。為了將I/O操作一直保持在一定的工作效能上，也為了發(fā)揮CPU的計(jì)算性能，InnoDB引擎允許將innodb_buffer_pool劃分為多個(gè)獨(dú)立的運(yùn)行實(shí)例，當(dāng)InnoDB需要讀取新的Page時(shí)，它們會(huì)按照一定的算法被分配到某個(gè)獨(dú)立運(yùn)行的buffer pool instance中。

這些buffer pool instance有各自獨(dú)立的LRU算法隊(duì)列、獨(dú)立計(jì)算臟頁(yè)比例，并且獨(dú)立進(jìn)行臟頁(yè)刷新。innodb_buffer_pool_instances參數(shù)在具有較高I/O性能并且具有較大innodb_buffer_pool_size設(shè)定值的物理設(shè)備上能夠?qū)/O性能產(chǎn)生非常明顯的影響。

如果您采用的是固態(tài)磁盤或者磁盤陣列作為MySQL服務(wù)器的硬件層存儲(chǔ)介質(zhì)，那么建議1-2GB的innodb_buffer_pool就分配一個(gè)獨(dú)立的運(yùn)行實(shí)例（這樣算下來(lái)12GB的buffer pool可以設(shè)置6-12個(gè)運(yùn)行實(shí)例，注意進(jìn)行生產(chǎn)環(huán)境下的實(shí)測(cè)調(diào)整哦-）。

但如果您只是使用的機(jī)械磁盤又或者innodb_buffer_pool_size的值并不大，那么將innodb_buffer_pool_instances參數(shù)設(shè)置為1就可以了。

7. innodb_io_capacity

該參數(shù)控制著InnoDB Buffer Pool數(shù)據(jù)內(nèi)存區(qū)域進(jìn)行磁盤同步時(shí)每次可以同步的臟頁(yè)數(shù)量。在磁盤I/O性能不足時(shí)，如果innodb_io_capacity參數(shù)值過(guò)大就會(huì)造成I/O阻塞，并且造成InnoDB引擎性能較大的降低。但如果您使用的是固態(tài)硬盤或者RAID磁盤陣列,就可以將innodb_io_capacity參數(shù)默認(rèn)的200設(shè)置大一些，例如設(shè)置成500——800）。

8. innodb_adaptive_flushing

該參數(shù)一定要打開，保證臟頁(yè)的同步周期由InnoDB引擎根據(jù)實(shí)時(shí)I/O性能情況自行控制同步頻率（實(shí)際上只有兩種頻率：1秒或者10秒）。

9. innodb_max_dirty_pages_pct

該參數(shù)默認(rèn)為75，一般情況下無(wú)需更改。另外innodb_io_capacity_max參數(shù)表示當(dāng)臟頁(yè)數(shù)量在InnoDB Buffer Pool內(nèi)存中的比例超過(guò)了innodb_max_dirty_pages_pct參數(shù)設(shè)置的上限后，就按照innodb_io_capacity_max設(shè)置的臟頁(yè)數(shù)量強(qiáng)制進(jìn)行臟頁(yè)的刷新（建議采用默認(rèn)值即可）。

但是設(shè)想一下這個(gè)問(wèn)題：什么情況下最可能使臟頁(yè)在內(nèi)存中的占比超過(guò)上限呢？當(dāng)然是InnoDB引擎的事務(wù)不斷快速執(zhí)行，并且I/O性能又不足以快速完成同步。這時(shí)InnoDB引擎將停止事務(wù)的執(zhí)行，并且進(jìn)行強(qiáng)制刷新。所以，當(dāng)問(wèn)題真正發(fā)生時(shí)innodb_io_capacity_max參數(shù)設(shè)置得再大也不可能解決I/O擁堵的問(wèn)題，反而可能使問(wèn)題更嚴(yán)重。

10. innodb_page_size（截止其他參數(shù)）

該參數(shù)決定了InnoDB引擎中每一頁(yè)的大小。每一個(gè)page包含多條row數(shù)據(jù)，更大的page size意味著內(nèi)存中存儲(chǔ)的每頁(yè)信息有更多的數(shù)據(jù)條數(shù)。由于文件系統(tǒng)和底層硬件設(shè)置的結(jié)構(gòu)，所以該值都為4KB的整數(shù)倍（默認(rèn)值為16KB，可選值為4KB、8KB、16KB）。

注意如果您需要更改這個(gè)參數(shù)值，那么就必須在MySQL數(shù)據(jù)庫(kù)初始化啟動(dòng)時(shí)，就加入到my.cnf配置文件中。否則一旦創(chuàng)建了用戶數(shù)據(jù)表，再對(duì)這個(gè)參數(shù)進(jìn)行修改，MySQL數(shù)據(jù)庫(kù)就會(huì)報(bào)錯(cuò)。

11. innodb_read_io_threads

該參數(shù)設(shè)置InnoDB數(shù)據(jù)庫(kù)中的負(fù)責(zé)從磁盤上讀取數(shù)據(jù)的線程數(shù)量，另外這些線程還負(fù)責(zé)在預(yù)讀選項(xiàng)開啟時(shí)承擔(dān)起預(yù)讀的工作任務(wù)。innodb_read_io_threads的建議值為CPU的內(nèi)核數(shù)量.

12. innodb_write_io_threads

該參數(shù)設(shè)置InnoDB數(shù)據(jù)庫(kù)中負(fù)責(zé)將臟頁(yè)同步到磁盤上的線程數(shù)量。innodb_write_io_threads的建議值為CPU的內(nèi)核數(shù)量。

13. innodb_read_ahead_threshold

該參數(shù)表示InnoDB引擎中的順序預(yù)讀閥值。在buffer pool中的page也有一個(gè)組織結(jié)構(gòu)：64個(gè)page組成一個(gè)extent結(jié)構(gòu)。當(dāng)InnoDB發(fā)現(xiàn)在一個(gè)extent結(jié)構(gòu)中已經(jīng)連續(xù)讀取N個(gè)page，那么InnoDB會(huì)接著將另外64 - N個(gè)后續(xù)的page讀入到buffer pool中。

順序預(yù)讀在“連續(xù)讀”性能較高的硬件設(shè)備上，對(duì)性能的影響非常小。所以如果讀者使用了I/O性能比較強(qiáng)勁的固態(tài)磁盤環(huán)境或者磁盤陣列環(huán)境，則建議直接關(guān)閉該功能（設(shè)置為0即可）。

14. innodb_random_read_ahead

該參數(shù)表示是否開啟隨機(jī)預(yù)讀，默認(rèn)是關(guān)閉的。

15. innodb_flush_neighbors

既然InnoDB引擎提供Page的預(yù)讀功能，當(dāng)然就提供預(yù)寫功能。該參數(shù)表示當(dāng)Buffer Pool中的臟頁(yè)被同步到磁盤時(shí)，是否一起刷新和這個(gè)臟頁(yè)臨近的頁(yè)信息。

該參數(shù)在I/O性能比較強(qiáng)勁的固態(tài)磁盤環(huán)境或者磁盤陣列環(huán)境下，對(duì)性能提升并不明顯。所以建議在這樣的情況下直接關(guān)閉這個(gè)功能（設(shè)置為0即可）。

16. sort_buffer_size

后文介紹數(shù)據(jù)庫(kù)查詢優(yōu)化時(shí)會(huì)討論到這個(gè)參數(shù)。該參數(shù)對(duì)數(shù)據(jù)庫(kù)引擎的查詢性能，特別是有對(duì)結(jié)果進(jìn)行排序要求的查詢性能影響非常大。

17. join_buffer_size

后文介紹數(shù)據(jù)庫(kù)查詢優(yōu)化時(shí)會(huì)討論到這個(gè)參數(shù)。該參數(shù)對(duì)數(shù)據(jù)庫(kù)引擎的查詢性能，特別是有各種join連接要求的查詢性能影響非常大。

18. binlog_cache_size

在MySQL數(shù)據(jù)庫(kù)中處理InnoDB層存在“重做日志”以外，在數(shù)據(jù)庫(kù)管理層還有一個(gè)獨(dú)立工作的二進(jìn)制日志模塊。這個(gè)日志模塊的工作方式和“重做日志”的工作方式相似，它們采用的辦法都是在內(nèi)存中進(jìn)行日志數(shù)據(jù)變更，然后按照一定的策略周期性/直接同步到磁盤上，binlog_cache_size參數(shù)設(shè)置的就是可供二進(jìn)制日志在內(nèi)存中進(jìn)行暫存的空間大小。

需要注意的是：binlog_cache_size和innodb_buffer_pool_size不同的是，前者的大小以MySQL數(shù)據(jù)庫(kù)的客戶端連接為單位。也就是說(shuō)MySQL數(shù)據(jù)庫(kù)會(huì)為兩個(gè)獨(dú)立的數(shù)據(jù)庫(kù)客戶端連接分別分配獨(dú)立運(yùn)行的binlog cache空間。

正式環(huán)境的數(shù)據(jù)庫(kù)中為每一個(gè)數(shù)據(jù)庫(kù)連接設(shè)置的binlog cache空間不需要太大，當(dāng)然這還要考慮實(shí)際的客戶端請(qǐng)求頻度和數(shù)據(jù)類型，還要考慮下面將介紹的sync_binlog參數(shù)設(shè)定。該參數(shù)建議的幾個(gè)設(shè)置值為：32768（32KB為默認(rèn)值，沒(méi)有特別的要求可以保留該設(shè)置）、65536（64KB）、131072（128KB）、262144（KB）、524288（512KB）、1048576（1MB）以內(nèi)。

19. sync_binlog

在MySQL數(shù)據(jù)庫(kù)中除了InnoDB的“重做日志”需要同步以外，二進(jìn)制日志也需要進(jìn)行同步。這個(gè)參數(shù)是指MySQL數(shù)據(jù)庫(kù)在內(nèi)存中進(jìn)行X次二進(jìn)制日志操作后，就將內(nèi)存中的二進(jìn)制日志同步到磁盤中

1. 概述如下：

以下是一組可以在配置有固態(tài)硬盤和磁盤陣列的正式MySQL數(shù)據(jù)庫(kù)環(huán)境下使用的配置項(xiàng)參考，主要是為讀者總結(jié)InnoDB引擎中和I/O性能相關(guān)的重要參數(shù)（只和I/O性能有關(guān)，因?yàn)楹罄m(xù)的文章中還會(huì)介紹其他參數(shù)）。

讀者在進(jìn)行參數(shù)配置是還是需要按照自己團(tuán)隊(duì)的生產(chǎn)環(huán)境情況，對(duì)配置項(xiàng)進(jìn)行調(diào)整（這些參數(shù)信息都在MySQL數(shù)據(jù)庫(kù)的my.cnf主配置文件中進(jìn)行設(shè)置）：

2. 配置完成后可以通過(guò)以下命令查看當(dāng)前MySQL數(shù)據(jù)庫(kù)和InnoDB引擎中相關(guān)的配置參數(shù)（為節(jié)約篇幅，已省去一部分查詢結(jié)果）：

三、提供更優(yōu)的硬件方案

這是最基本的硬件層改造方式，目前大多數(shù)廠商提供的PC Server基本上都集成了RAID控制器。所以這樣做一般不需要額外增加購(gòu)買硬件設(shè)備的費(fèi)用。

在MySQL官網(wǎng)上并沒(méi)有明確推薦使用哪一種磁盤陣列模式，但是從搭建磁盤陣列支持MySQL的實(shí)際引用情況來(lái)看，更多是使用RAID 10陣列模式（另外磁盤陣列的整體性能和陣列控制芯片有很大關(guān)系）。

RAID 10陣列模式可以在提升了整個(gè)系統(tǒng)I/O性能的基礎(chǔ)上兼顧了存儲(chǔ)的安全性。為了使用RAID 10磁盤陣列模式，讀者至少需要為準(zhǔn)備4塊磁盤。其中2/4的磁盤容量用來(lái)存儲(chǔ)數(shù)據(jù)冗余，另外2/4的磁盤容量用來(lái)分散存儲(chǔ)數(shù)據(jù)。對(duì)于RAID 10磁盤陣列模式的詳細(xì)工作方式介紹，讀者可以參看另一篇文章（《架構(gòu)設(shè)計(jì)：系統(tǒng)存儲(chǔ)（2）——塊存儲(chǔ)方案（2）》）

以上解決方案中，每一個(gè)機(jī)械磁盤的I/O性能將會(huì)成為整個(gè)RAID 10磁盤陣列的性能瓶頸（不考慮陣列控制芯片的處理性能）。所以如果技術(shù)團(tuán)隊(duì)還有多余的資金支持那么下一步要做的就是將構(gòu)成RAID 10磁盤陣列多個(gè)機(jī)械磁盤全部替換成固態(tài)磁盤。如下圖所示：

USB3.0接口的理論帶寬只有600MB/S，而且PC Server內(nèi)置的磁盤陣列控制器由于服務(wù)器內(nèi)部空間的限制，也存在磁盤數(shù)量擴(kuò)展困難的問(wèn)題。如果讀者確認(rèn)生產(chǎn)環(huán)境的某個(gè)物理服務(wù)器將以I/O讀寫操作為主，且I/O性能將成為其上工作的應(yīng)用軟件的瓶頸。

那么這時(shí)最好的硬件方案就是直接采用外置企業(yè)級(jí)磁盤陣列柜 + 光纖接口的方式搭建硬件層支持。

目前主流的光纖線路帶寬為16Gb/s，這遠(yuǎn)遠(yuǎn)高于USB3.0 6Gb/s的理論帶寬、高于SAS 12Gb/s的理論帶寬。另外單個(gè)企業(yè)級(jí)磁盤陣列柜可容納的磁盤數(shù)量就已經(jīng)很高了（例如IBM Storwize V5000 單柜提供24個(gè)磁盤位，單柜支持最大72TB存儲(chǔ)容量），并且這些企業(yè)級(jí)盤柜一般支持?jǐn)U展成多柜。

這兩種特性有效解決了硬件層面磁盤I/O速度和容量的問(wèn)題，但代價(jià)就是這些IT基礎(chǔ)折本的價(jià)格一般比較昂貴，技術(shù)團(tuán)隊(duì)所在企業(yè)需要有比較寬裕的項(xiàng)目/產(chǎn)品建設(shè)預(yù)算。

Bye~

轉(zhuǎn)載自：http://6tt.co/nKdc

-- RECOMMEND --01｜測(cè)開之Java全棧自動(dòng)化

02｜接口測(cè)試——官方requsets庫(kù)

03｜接口測(cè)試數(shù)據(jù)庫(kù)斷言的實(shí)現(xiàn)與設(shè)計(jì)

南京兆柏?cái)?shù)據(jù)恢復(fù)中心