INNODB：使用最廣的存儲引擎

innodb-buffer-pool-size

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

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

能夠看到示例的配置文件中把這個值配置為12G，這不是一個標準配置，須要根據具體的硬件來估算。那如何估算？前面的小節，咱們說到，MySQL中最重要的緩存有5種，能夠簡單的使用下面的公式計算：sql

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

具體來看，至少須要為OS保留1~2G內存，若是機器內存大的話能夠預留多一些，建議2GB和總內存的5%為基準，以較大者為準，若是機器上還運行著一些內存密集型任務，好比，備份任務，那么能夠為OS再預留多一些內存。不要為OS緩存增長任何內存，由于OS一般會利用全部剩下的內存來作文件緩存。緩存

通常來講，運行MySQL的服務器不多會運行其余應用程序，但若是有的話，請為這些應用程序預留足夠多的內存。安全

MySQL自身運行還須要一些內存，但一般都不會太大。須要考慮MySQL每一個鏈接須要的內存，雖然每一個鏈接須要的內存都不多，但它還要求一個基本量的內存來執行任何給定的查詢，并且查詢過程當中還須要為排序、GROUP BY等操做分配臨時表內存，所以須要為高峰期執行大量的查詢預留足夠的內存。這個內存有多大？只能在運行過程當中監控。服務器

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

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壓力。async

總體的日志文件大小受控于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機制來刷新和恢復數據，這會加快恢復數據的時間，具體能夠參考：

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-flush-method選項能夠配置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有太多的配置項，這里沒有辦法一一列舉，重要的是了解每一個配置的工做原理，從一個基礎配置文件開始，設置符合服務器軟硬件環境與工做負載的基本選項。

參考資料

做者：CHEN川

連接：https://www.jianshu.com/p/78b6b7e2bb7f

來源：簡書

簡書著做權歸做者全部，任何形式的轉載都請聯系做者得到受權并注明出處。

總結

以上是生活随笔為你收集整理的mysql优化原理_【MySQL】我必须得告诉你们的MySQL优化原理3（下）INNODB配置的全部內容，希望文章能夠幫你解決所遇到的問題。

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