525
MySQL - InnoDB 存儲引擎優化 及 事務優化
InnoDB 存儲引擎和 MyISAM 存儲引擎最大區別主要有 4點：
1. 緩存機製；
2. 事務支持；
3. 鎖定實現；
4. 數據存儲方式差異；
修改表引擎：
mysql> ALTER TABLE mytable ENGIN = INNODB:
InnoDB 緩存相關優化：
-
innodb_buffer_pool_size 的合適設置
- InnoDB 存儲引擎的緩存機製和 MyISAM 的最大區別就在於 InnoDB 不僅僅緩存索引，同時還會緩存實際的數據。所以， InnoDB 存儲引擎可以使用更多的內存來緩存數據庫的相關信息。
- innodb_buffer_pool_size 參數用來設置 InnoDB 最主要的 Buffer（InnoDB Buffer Pool）的大小，緩存用戶表及索引數據的最主要緩存空間，對 InnoDB 整體性能影響也最大。
- InnoDB 的 Buffer Pool 簡單設置為整個係統物理內存的 50%~80%之間，具體設置多大要根據實際環境技術，例如一台單獨 MySQL 主機， 物理內存 8G， MySQL 最大連接數為 500，同事還是用了 MyISAM 存儲引擎，這時整體內存分配如下：
- 係統使用，假設預留 800MB；
- 線程獨享，最大約為 2GB = 500 * （1MB + 1MB + 1MB + 512KB + 512KB），組成，（sort_buffer_size： 1MB、join_buffer_size:1MB、read_buffer_size：1MB、read_rnd_buffer_size：512KB、thread_statck：512KB）
- MyISAM Key Cache：假設大概 1.5GB。
- InnoDB Buffer Pool 最大可用： 8GB - 800MB - 2GB - 1.5GB = 3.7 GB
- 生產環境 InnoDB 存儲引擎喲啊根據 Buffer Pool 實時信息作出進一步分析。
- 上圖可看出 總共有 512 pages， 有 163 個是 Free 狀態， 有 349 個 page 有數據， read 請求 4087次，其中有 316次請求使用物理磁盤獲取，InnoDB Buffer Pool 的 Read 命中率大概： （4087-316)/ 4087 * 100% = 92.26%
- Innodb_buffer_pool_read_ahead_rnd：記錄進行隨機讀的時候產生的預讀次數；
- Innodb_buffer_pool_read_ahead_seq：記錄連續讀的時候產生的預讀次數；
- 預讀 -- 在一些高端存儲才會有，簡單來說，就是通過分析數據請求的特點來自動判斷客戶端在請求當前數據塊之後可能會繼續請求的數據塊。通過該自動判斷，存儲引擎可能會一次性將當前請求的數據庫和後麵可能請求的下一個（或者幾個）數據庫全部讀出，以期望通過這種方式減少磁盤 IO 次數，提高 IO 性能 。
-
innodb_log_buffer_size 參數的使用
- 設置 InnoDB 的 Log Buffer 大小，係統默認 1MB。 Log Buffer 的主要作用就是 緩衝 Log 數據，提高寫 Log 的 IO 性能。一般來說，如果係統不是寫負載非常高而且以大事務居多的話， 8MB 以內大小足夠。
- 也可以通過 係統狀態 參數提供的性能統計數據來分析 Log 的使用情況
-
innodb_additional_mem_pool_size 參數理解
- 設置用戶存放 InnoDB 的字典信息和其他內部結構所需要的內存空間。所以， InnoDB 表越多，需要的空間自然也就越大，係統默認 1MB。
- 一個常規的 幾百個 InnoDB 表的 MySQL ，如果不是每個表都是上百個字段， 20MB 內存已經足夠了。
- 該參數對係統整體性能並無太大影響，設置超過實際所需存儲，隻是浪費內存而已。
Double Write Buffer
Double Write Buffer 是 InnoDB 所使用的一種較為獨特的文件 Flush 實現技術，主要作用是在減少文件同步次數提高 IO 性能的情況下，提高係統崩潰（Crash）或斷電情況下的安全性，避免寫入的數據不完整。
Adaptive Hash Index
Adaptive Hash index 的目的並不是為了改善磁盤 IO 性能，而是為了提高 Buffer Pool 中的數據訪問效率，也就是給 Buffer Pool中的數據做索引。
事務優化
髒讀：指當一個事物正在訪問數據，並且對數據進行修改，而這種修改沒有提交到數據庫中，這時，另外一個事物也訪問這個數據，然後使用了這個數據。
不可重複讀：指在一個事務內，多次讀同一數據。在這個事務還沒有結束時，另外一個事務也訪問同一數據。那麼，在第一個事務中的兩次讀數據之間，由於第二個事務的修改，那麼第一個事務兩次讀到的數據可能是不一樣的。這樣就發生了在一個事務內兩次讀到的數據不一樣，因此成為不可重複讀。
幻讀：指當事務不是獨立執行時發生的一種現象，例如第一個事務對一個表中的數據進行了修改，這種修改涉及到表中的全部數據行。同時，第二個事務也修改了這個表中的數據，如想表中插入一行新數據。那麼，以後就會發生操作第一個事務的用戶發現表中還沒有修改的數據行，就好像發生了幻覺一樣。
選擇合適的事務隔離級別
-
READ UNCOMMITTED ：
- 常被稱為 Dirty Reads（髒讀），事務上的最低隔離級別。
-
READ COMMITTED：
- 這一隔離級別下不會出現 Dirty Reads（髒讀），但是可能出現 Non-Repeatable Reads（不可重複讀）和 Phantom Reads（幻讀）
-
REPEATABLE READ：
- 是 InnoDB默認的事務隔離級別。 這一隔離級別下不會垂涎 Dirty Reads，也不會出現 Non-Repeateable Reads，但可能會出現 Phantom Reads。
-
SERIALIZABLE：
- 標準事務隔離級別中的最高級。這一隔離級別下， Phantom Reads 也不會出現。
對於高並發應用來說，為了盡可能保證數據的一致性，避免並發可能帶來的數據不一致，自然是事務隔離級別越高越好。但是，對於 InnoDB 來說，所使用的事務隔離級別越高，實現複雜度自然就會更高，所要做的事情也會更高，整體性能也就更差。雖然 InnoDB 存儲引擎默認的事務隔離級別是 REPEATABLE READ，但實際上在大部分應用中，隻需要 READ COMMITED 的事務隔離級別就可以滿足需求了。
事務與 IO 的關係及優化
InnoDB 修改數據操作，實際上修改的是Buffer Pool中的數據，並不是一個事務提交後就將 Buffer Pool 中被修改的數據同步到磁盤上，而是通過記錄到事務日誌中，在連續寫入磁盤。
控製 InnoDB 事務日誌刷新方式參數：innodb_flush_log_at_trx_commit :
- innodb_flush_log_at_trx_commit = 0， InnoDB 中的 Log Thread 每隔 1秒將 log buffer 中的數據寫入文件，同時還會通知文件係統進行與文件同步的 flush操作，保證數據確實已經寫入磁盤。
- innodb_flush_log_at_trx_commit = 1， InnoDB 默認設置。每次事務的結束都會出發 Log Thread 將 Log Buffer 中的數據寫入文件、並通知文件係統同步文件。這個設置最安全，能夠保證不論是 MySQL 崩潰、OS崩潰還是主機斷電都不會丟失任何已經提交的數據。
- innodb_flush_log_at_trx_commit = 2， 每次事務結束的時候將數據寫入事務日誌，僅僅是調用了文件係統的文件寫入操作。而文件係統都是有緩存機製的，所以 Log Thread 的寫入並不能保證內容已經寫入到物理磁盤完成持久化的動作。文件係統什麼時候會將緩存中的數據同步到物理磁盤、文件， Log Thread 就完全不知道，所以，當設置 2 的時候， MySQL 崩潰並不會造成數據的丟失，但是 OS 崩潰或主機斷電後可能丟失的數據量就完全控製在文件上了。
以上分析，設置1 最安全，由於 IO 同步操作多，所以，性能最低。設置 0 ，則每秒一次同步，性能相對高一下。如果設置 2 ，性能可能是三種最好的。但是也可能出現故障後丟失數據最多的一種。如果 OS 足夠穩定，主鍵硬件設備足夠好，且主機斷電係統而已足夠安全，可將設置為2 ，讓係統整體性能盡可能高。 【建議設置為 2】
修改回話的隔離級別：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;
數據存儲優化
理解 InnoDB 數據及索引文件存儲格式
-
Page
- InnoDB 存儲引擎中的所有數據，不論是表還是索引，或是存儲引擎自己的各種結構，都以 page 作為最小物理單位來存放，每個 page 默認大小 16KB。
-
extent
- extend 是多個連續 page 組成的一個物理存儲單位。一般來說，每個 extend 為 64 個page。
-
segment
- segment 在 InnoDB 粗才能引擎中實際上也代表 “ file"，每個 segment 由一個或多個 extent 組成，而且每個 segment 都存放同一種數據。一般來說，每個表數據會存放於一個單獨的 segment 中，實際上也就是每個 聚簇索引會存放一個單獨的 segment 中。
-
tablespace
- InnoDB中最大的物理結構單位，由多個 segment 組成。
查看 InnoDB 表空間的使用情況：
對性能有較大影響的 4 個要點：
- 為了盡量減小 secondary index 的大小，提高訪問效率，作為主鍵的字段所占用的存儲空間越小越好，最好是 INTEGER 類型。當然這並不是絕對的，字符串類型的數據同樣可以作為 InnoDB表的主鍵。
- 創建表的時候盡量自己製定相關的主鍵，讓數據按照預設的順序排序存放，以提高特定條件下的訪問效率。
- 盡可能不要在主鍵上進行更新操作，減少因為主鍵值的變化帶來的數據的移動。
- 盡可能提供後主鍵條件進行查詢。
分散IO 提升磁盤相應：
由於 InnoDB 和其他非事務存儲引擎相比，在記錄數據文件的同事還記錄相應的事務日誌（Transaction Log）， 相當於增加了整體的 IO 量。雖然事務日誌是以完全順序的方式寫入磁盤，但總是會有一定的IO消耗，所以對沒有使用REID 的存儲係統來說，建議將數據文件和事務日誌文件分別存放於不同的物理磁盤以降低磁盤的相互爭用，提高整體 IO 性能。
- innodb_log_group_home_dir：指定InnnDB 日誌存放位置；
- innodb_data_home_dir ： 指定 InnoDB 數據文件存放位置；
- innodb_autoextend_increment：設置自行控製表空間文件每次增加的大小；
最後更新：2017-10-20 15:03:42