Mysql inndodb 儲存引擎的簡單總結（組成結構，鎖，事務，備份，最佳化）

上面這張圖總結了innodb mysql的邏輯和物理架構組成，鎖與事務，備份策略以及調優點，以下就每個點進行總結說明。

線上程處理方面，Mysql是多線程的架構，由一個master線程，一個鎖監控線程，一個錯誤監控線程，和多個IO線程組成。並且對一個串連會開啟一個線程進行服務。

io線程又分為節省隨機IO的insert buffer，用於事務控制的類似於oracle的redo log，以及多個write，多個read的硬碟和記憶體交換的IO線程。

在記憶體配置方面，包括innodb buffer pool ，以及log buffer。其中innodb buffer pool包括insert buffer、datapage、index page、資料字典、自適應hash。

在邏輯資料結構方面，innodb包括資料表空間、段、區、頁/塊，行。索引結構是B- tree結構，包括二級索引和主鍵索引，二級索引的葉子節點是主鍵PK，根據主鍵索引的葉子節點指向儲存的資料區塊。

在實體儲存體檔案方面，包括frm表定義檔案，每個表一個的ibd檔案（需要進行設定），ibdata檔案（系統資料表空間MySQL資料庫檔案，儲存InnoDB系統資訊和使用者資料庫表資料和索引，資料字典，undo，所有表共用），

          ib_logfile記錄檔，binlog二進位記錄檔。

 

為了保證一致性，對於鎖的分類，一般分為共用鎖定和獨佔鎖定；mysql主要有這麼幾個鎖，行鎖，頁鎖，表鎖，innodb儲存引擎是行級鎖。

                  共用鎖定（S--只允許當前或其他線程讀，不允許任何線程修改該記錄）和獨佔鎖定（X---只允許當前線程update或者delete一行記錄，不允許其他線程操作（包括查詢該記錄）

                  在做select查詢的時，會產生一個共用鎖定，當然類似select  for update會產生一個獨佔鎖定。

        使用鎖的時候，要主要死結的產生，一般是交叉進行鎖相應的資源，常見的情境是，

        一個線程根據主鍵更新索引值（先給資料行加X鎖，再給索引塊中的記錄加X鎖），另外一個是根據索引更新相關欄位（先給索引塊中的記錄加X鎖，再給資料行加X鎖）。

 

為了保證資料之間的完整性，Mysql innodb支援事務操作，

    並且考慮到一致性和並發性的不同要求，事務之間隔離等級有

     read uncommited（髒讀、不可重複讀取、幻讀(隔離等級最低，並發效能高)）、

     read commited（會出現不可重複讀取、幻讀問題（鎖定正在讀取的行））、

     repeated read(mysql預設)（會出幻讀（鎖定所讀取的所有行））、 

     serialize read（證所有的情況不會發生（鎖表））。

在備份方面，分為冷備份（物理檔案copy，資料檔案、undo、插入緩衝等-------停機），溫備份（dump，import----要加鎖），熱備份（xtrabackup）。

    可以進行有全量的備份，增量的備份(通過binlog或者lsn(xtrabackup))，或者即時性的備份（replication複製）

在效能調優方面，可以從以下幾個角度來考慮，

   a、作業系統層級，核心以及socket的最佳化，網路最佳化bond

   b、server層級（串連管理、網路管理、table管理、日誌）

   c、應用層級（比如索引的考慮，schema的最佳化適當冗餘；最佳化sql查詢導致的CPU問題和記憶體問題）

   d、IO儲存層級，

              innodb主要用在OLTP類應用，一般都是IO密集型的應用，在提高IO能力的基礎上，充分利用cache機制。需要考慮的內容有，

                     1、在保證系統可用記憶體的基礎上，儘可能的擴大innodb buffer pool，一般設定為實體記憶體的3/4

                     2、日誌和資料的儲存，需要分開，日誌是順序的寫，需要做raid1+0，並且用buffer-IO；資料是離散的讀寫，走direct IO即可，

                           避免走檔案系統cache帶來的開銷。

                            儲存能力，SAS盤raid操作（raid卡緩衝，關閉讀cache，關閉磁碟cache，關閉預讀，只用writeback buffer，不過需要考慮充放電的問題），

                               當然如果資料規模不大，資料的儲存可以用高速的裝置，Fusion IO、SSD。

                              對於資料的寫入，控制髒頁重新整理的頻率，對於資料的讀取，控制cache hit率；因此而估算系統需要的IOPS，評估需要的硬碟數量

                              （fusion io上到IOPS 在10000以上，普通的硬碟150）

                    4、檔案系統的使用，只在記錄交易記錄的時候用檔案系統的cache；盡量避免mysql用到swap

                            （可以將vm.swappiness=0，記憶體緊張時，釋放檔案    系統cache）

                    5、IO調度最佳化，減少減少磁頭移動

    以下是相關參數參考：

  假設 Mysql(實體記憶體32G，CPU物理40core)

  擴充資料表空間帶來的抖動問題，需要預先分配資料表空間

  innodb_file_per_table（true）---分散IO

  innodb_flush_log_at_trx_commit（設定成1，<2<0）2是檔案系統，1是直接flush到儲存上

  sync_binlog（設定成1<0）0是檔案系統

  innodb_flush_method（預設值為fdatasync，O_DSYNC和O_DIRECT，這裡設定為O_DIRECT）

  binlog_cache_size(預設32k)

  innodb_buffer_pool_size（設定成24G）

  innodb_max_dirty_pages_pct(預設90%--->75%)

  innodb_read_io_threads/innodb_write_io_threads（預設都是4個，對於高速的存放裝置麼可以設定大一些）

  innodb_adaptive_flushing(ON，根據斷重做日誌產生速度確定需要重新整理髒頁的最合適數目)

  innodb_thread_concurrency（cpu core的兩倍，80）

  innodb_io_capacity（預設值為200---重新整理髒頁和插入緩衝）

  innodb_doublewrite

  innodb_purge_threads

  max_connections(預設100)

  max_user_connections（預設30，改為100）

  innodb_page_size（8K，如果全表掃描16K，如果ssd，4k隨機效能好）