Mysql表引擎最佳化

MyISAM：

第一，最佳化參數

這個表引擎只儲存索引的緩衝，而不儲存資料的緩衝。可以通過設定KEY_BUFFER_SIZE設定緩衝大小，通過KEY_BUFER_BLOCK_SIZE設定cache block的size。

KEY_CACHE_DIVISION_LIMIT是設定LRU鏈表中hot area和warm area的分界值，為1-100之間。系統預設為100，也就是只有warm chain。KEY_CACHE_DIVISION_LIMIT告訴mysql該如何將整個cachechain劃分為hot area和warm area。

KEY_CACHE_AGE_GHRESHOLD，控制cacheblock從hotarea降級到warmarea的限制，系統預設為300，最小設定為100.值越小，被降級的可能性越大。

多key cache的使用

msyql4.1.1開始，myisam支援多個key cache。可以根據不同的需求，設定多個keycache 了。如，將使用非常頻繁但是更新操作很少的資料放入一個keycaceh中以防止在公用keycache中被清除出去。那些使用不是很頻繁而且經常會更新的資料放在另外一個keycache中。

mysql官方建議在比較繁忙的系統上使用三個keycache【key cache就是索引緩衝】:

一個hotcache：使用20%的大小，用來存放使用非常頻繁而且更新少的表索引

一個coldcache：使用20%的大小，用來存放更新很頻繁的表索引

一個warmcache：使用剩下的60%空間，作為整個系統的預設keycache。

第二，預先載入

在一個新系統剛上線的時候，系統會因為cache中沒有任何資料而出現短時間的負載過高。可以使用預先載入機制將指定表的所有索引都載入到記憶體中。

第三，null值對統計資訊的影響

myisam索引中會記錄值為null的列資訊，只不過null值的索引鍵佔用的空間非常少。所以，null會影響到mysql查詢最佳化工具對執行計畫的選擇。於是，mysql提供了MYISAM_STATS_METHOD參數讓我們自行決定對索引中的null值的處理方式。如果MYISAM_STATS_METHOD=nulls_unequal，那麼myisam在搜集統計資訊的時候會認為每個null都是不同的，則給予該欄位的索引就會更大，也就是說，myisam會認為distinct的值會更多。MYISAM_STATS_METHOD=nulls_equal則相反。

第四，並發最佳化

1、開啟concurrent_insert，如果concurrent_insert為1，則當表中沒有刪除記錄留下的空餘空間時候可以在尾部進行並行插入。concurrent_insert為2的時候，不管有沒有因為刪除而留下的空間，都會在尾部進行並行插入。

2、控制寫入操作的大小以防止過長的時間擁塞。

3、通過犧牲讀取效能來提高寫入效能。將寫入的優先順序提高。

第五，其他最佳化

通過optimize命令整理myisam表的檔案，是檔案佔用的空間連續。一般來說，每次做了較大的資料刪除操作之後，都要做此命令。

Innodb：

innodb和myisam最大區別有四點：

1、緩衝機制

2、事務支援

3、鎖定實現

4、資料存放區方式

整體效能上的差異innodb和myisam會因為不同情境而表現出很大差異，正是因為這四點。

第一、innodb緩衝最佳化

innodb和myisam的最大區別就是innodb不僅僅緩衝了索引，同時還緩衝實際的資料。所以，完全相同的資料庫，innodb可以使用更多的記憶體來快取資料庫相關資訊。前提是有足夠的記憶體。

innodb_buffer_pool_size設定了InnoDB儲存引擎需求最大的一塊記憶體地區的大小，直接關係到InnoDB儲存引擎的效能，所以如果我們有足夠的記憶體，盡可將該參數設定到足夠大，將儘可能多的InnoDB的索引及資料都放入到該快取區域中，直至全部。

我們可以通過(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%計算快取命中率，並根據命中率來調整innodb_buffer_pool_size參數大小進行最佳化。

innodb_log_buffer_size (global)　　這是InnoDB儲存引擎的交易記錄所使用的緩衝區。在mysql寫入負載很高的情況下，可以增大這個參數來提高IO效能。

第二、事務最佳化

innodb支援的交易隔離等級如下：

1、read uncommited：常被稱為髒讀

2、read commited：在這一隔離等級下，不會出現dirty read。但是可能會出現non-repeatable read或者phantom read。

3、repeatable read：innodb的預設交易隔離等級。不會出現dirty read。也不會出現non-repeatable read，但是可能會出現phantom read。

4、serializable：serializable是標準交易隔離等級中的最進階。在事務中的任何時候看到的資料都是事務啟動時候的狀態。不論這期間是不是有其他事務已經修改了某些資料並提交。所以，在serializable下，phantom reads也不會出現。

innodb在修改資料的時候，只是修改buffer pool中的資料。並不是在一個事物提交之後就將buffepool中的資料同步到磁碟上。這裡要理解連續讀寫和隨機讀寫。寫入磁碟的 過程是需要磁頭定址的。連續讀寫是指將要寫入的東西寫入到一個連續地址空間。innodb不是每一次都將資料同步到磁碟，就是為了攢多資料之後，進行連續讀寫來減少磁碟IO。

系統崩潰之後，innodb是如何利用交易記錄進行資料恢複的呢？

假設在某一時間，mysql crash了，那麼，所有的bufferpool的資料都會丟失。包括已經修改了但是沒來得及重新整理到磁碟上的資料。在mysql從crash之後再次啟動，innodb會通過比較交易記錄中的所有記錄的checkpoint的資訊和各個資料檔案中的checkpoint資訊，找到最後一次checkpoint所對應的log sequence number。然後通過交易記錄中的變更記錄，將從崩潰之前最後一次checkpoint往後的所有變更重新應用一次。同步所有的資料檔案到一致狀態。當然，對於logbuffer中未來得及同步到記錄檔的變更資料，就再也無法找回了。總的來說，交易記錄檔設定的越大，系認io效能越好，但是遇到crash，那麼恢複的時間也越長。

innodb_flush_log_at_trx_commit預設設定為1，表示每次失誤的結束都會觸發log thread將logbuffer中的資料寫入檔案，並通知檔案系統同步檔案。這個設定是安全的，能夠保證不論是mysql crash，還是os崩潰，還是主機斷電，都不會丟失任何已經提交的資料。

第三、資料存放區最佳化

innodb的聚簇主鍵已經瞭解了。但是聚簇主鍵也是有不好的地方的，不然其他資料庫廠商也大力推廣了。聚簇的最大問題就是索引鍵被更新造成的成本，並不只是索引資料可能會移動，而是相關的所有記錄資料都要移動。所以，為了效能考慮，盡量不要更新innodb的主索引值。

innodb中的資料，不論是表還是索引，或者是儲存引擎的各種資料結構，都以page作為最小物理單位來儲存。每個page大小預設16K。extent由多個連續的page組成的一個實體儲存體單位。一般來說每個extent64個page。每個segment有一個或者多個extent組成。每個segment都存放同一種資料。一般來說，每個表都會存放在一個單獨的segment中。

page>extent>segment>tablespace，後面的每一個都是由前面的一個或者多個組成。tablespace是innodb的最大結構單位。

在主鍵上的最佳化建議，

1、為了減小secondary index的大小，主鍵欄位所佔用的儲存空間越小越好。最好是integer。當然這並不絕對。

2、儘可能不要做主鍵的更新。

3、儘可能根據主鍵進行查詢操作。

第四、其他最佳化

autocommit相信都瞭解，根據自己的實際情況設定autocommit。