mysql 預設引擎innodb 初探（三）

標籤：

寫在前

本篇部落格承接上一篇 mysql 預設引擎innodb 初探（二）繼續對mysql資料庫 innodb儲存引擎進行探索

innodb 檔案

mysql資料庫和innodb儲存引擎表的各種類型檔案：

• 參數檔案
• 記錄檔（錯誤記錄檔檔案，二進位記錄檔，慢查詢記錄檔，查詢記錄檔）
• socket檔案（Unix通訊端串連，避免走tcp協議，web伺服器和mysql伺服器在同一機器上時可用於提高通訊提高效率）
• pid檔案（儲存mysql執行個體進程ID）
• mysql表結構檔案
• innodb儲存引擎檔案

參數檔案

查看mysql設定檔

mysql --help | grep my.cnf

查當前mysql執行個體配置項

show variables like "innodb%"\G

配置參數：

• 動態參數（dynamic）【mysql執行個體運行中可以更改】
• 靜態參數（static）【執行個體生命週期內不得變更 eg : datadir 】

動態設定參數格式

set [@@global. | @@session.]system_var_name = expreg :   set read_buffer_size = 1024000;   set @@session.read_buffer_size = 2048000;   set @@global.read_buffer_size  = 4096000;

靜態參數是不能動態修改的，強制修改會報錯；
有些動態參數只能在會話中（session）修改，eg : autocommit ;
有些動態參數修改後整個執行個體都會生效， eg : binlog_cache_size ;
有些動態參數即可用在會話中修改，也可以在整個生命期內修改， eg : read_buffer_size ;

記錄檔

記錄檔記錄了mysql資料庫的各種活動，通過分析日誌可用快速精確尋找問題並最佳化；

• 錯誤記錄檔 （error log）
• 二進位日誌（binlog）
• 慢查詢日誌（slow query log）
• 查詢日誌（log）

錯誤記錄檔

錯誤記錄檔記錄了mysql的啟動，運行，關閉過程，通過該檔案可以快速定位問題；

查看mysql執行個體錯誤檔案路徑

show variables like "log_error"\G

慢查詢日誌

慢查詢日誌允許你可以設定一個閥值，已耗用時間超過該閥值的所有sql語句都會記錄在慢查詢記錄檔中；
可以很好的協助最佳化資料庫，；

show  variables like "slow_query_log"\G   # 查看是否開啟慢查詢日誌set   slow_query_log = ON|OFF;  # 開啟|關閉慢查詢日誌show variables like "log_output"\G   # 查看慢查詢日誌記錄到檔案還是表中 set log_output=TABLE|FILE;  # 設定慢查詢日誌輸出到table or files中 show variables like "slow_query_log_file"\G  # 查看慢查詢記錄檔路徑show  variables like "long_query_time"\G  # 查看慢查詢閥值set long_query_time=10;  # 設定慢查詢閥值為10sshow variables like "log_queries_not_using_indexes"\G # 查看是否開啟，沒有使用索引也記錄到慢查詢日誌中set log_queries_not_using_indexes=ON|OFF;  # 開啟or關閉show variables like "log_throttle_queries_not_using_indexes"\G # 每分鐘 允許【因為沒有使用索引】而記錄到慢查詢日誌中的sql語句數# log_throttle_queries_not_using_indexes = 0; 表示不限制數量，可能會頻繁記錄，要小心

• 使用mysqldumpslow工具分析慢查詢記錄檔 （當設定log_output=FILE時）

• 查看慢查詢日誌表（當設定log_output=TABLE時）

slow_log表預設是 CSV儲存引擎，對查詢效率不是很高，可以設定為MySIAM；不過個人建議設定成Archive儲存引擎；

查詢日誌

查詢日誌記錄所有mysql請求（insert，update，delete，select），無論是否正確；
預設記錄到到檔案中，開啟log_output=TABLE後記錄到mysql.general_log表中；

show variables like "general_log"\G   # 查看是否開啟查詢日誌set @@global.general_log = ON|OFF;   # 開啟or關閉查詢日誌show variables like "general_log_file"\G  # 查看查詢記錄檔路徑show variables like "log_output"\G  # 查看查詢日誌輸出到檔案還是表中

一般建議關閉，預設也是關閉查詢日誌；

二進位日誌

二進位日誌記錄mysql資料庫執行更改的所有操作（不包括select，show 等查詢操作）

• 恢複（recovery） eg : 進行point-in-time恢複
• 複製（replication） eg : master-slave 複製
• 審計（audit） eg : 分析二進位記錄檔，查看是否有注入攻擊等

設定檔中設定 log-bin [=binlog_file_name] 開啟二進位日誌;
如果不指定binlog_file_name預設為主機名稱；
二進位記錄檔放在 datadir資料目錄下

show variables like "datadir"\G 

mysql-bin.index檔案為二進位的索引檔案，存放二進位日誌序號

二進位日誌相關配置參數：

• max_binlog_size

  指定單個二進位檔案最大值，超過該大小，將產生新的二進位檔案，尾碼+1，並記錄到.index檔案中

• binlog_cache_size

  當執行事務時，所有未提交的二進位日誌會記錄到一個緩衝中，
  等事務提交時，直接將緩衝中的二進位日誌寫入到二進位記錄檔中，
  binlog_cache_size 設定緩衝大小，預設為32k；

  binlog_cache_size是基於會話（session）的，
  當一個線程開始一個事務時，mysql就會自動分配一個大小為binlog_cache_size的緩衝，
  因此binlog_cache_size不能太大【好像nginx的client_header_buffer_size也是這樣的】

• sync_binlog

  預設二進位日誌不是每次寫都會同步到磁碟，當資料庫發生宕機時，可能會有部分資料沒有刷盤；
  sync_binlog設定沒寫緩衝多少次就同步到磁碟，預設sync_binlog=0

  使用innodb儲存引擎進行複製時，為了獲得最大高可用性，建議開啟

• binlog-do-db

• binlog-ignore-db

• log-slave-update

  binlog-do-db | binlog-ignore-db 指定那些庫或者忽略那些庫寫二進位日誌

  log-slave-update 指定哪些要進行主從同步

• binlog_format

  • STATEMENT 二進位日誌記錄邏輯sql語句，
  • ROW 記錄表的行更改情況，會佔用更多儲存空間，主從複製時，會增加網路開銷；但是有更好的 可靠性
  • MIXED 預設採用STATEMENT，特殊採用ROW

  可以使用mysqlbinlog工具分析二進位檔案

通訊端檔案

unix本地可以 使用通訊端串連mysql
可以參考ngigx調用php-cgi的方式

pid檔案

儲存mysql進程ID

mysql表結構定義檔案

無論使用何種儲存引擎，mysql都會為每張表建立一個尾碼為frm的檔案，檔案中定義了表結構。

innodb儲存引擎檔案

以上介紹的檔案都是mysql資料庫本身檔案，和儲存引擎無關；
InnoDB儲存引擎擁有自己的檔案：

• 資料表空間檔案
• 重做記錄檔

資料表空間檔案

show variables like "innodb_file_per_table"\Gset innodb_file_per_table = ON|OFF;  # 開啟or關閉獨立資料表空間show variables like "innodb_data_file_path"\G   # 查看共用資料表空間檔案

開啟innodb_file_per_table獨立資料表空間後，獨立資料表空間只儲存該表的資料、索引、插入緩衝bitmap等資訊；其他資訊依然存放在共用資料表空間中，如插入緩衝資料等；

重做記錄檔

Inoodb儲存引擎的資料目錄下有兩個名為ib_logfile0 和 ib_logfile1的檔案；

每個儲存引擎至少有1個重做日誌組（group）
每個組至少有2個重做記錄檔，預設為ib_logfile0 , ib_logfile1，兩檔案大小一致，以迴圈寫入的方式運行；

重做記錄檔相關配置參數 ：

• innodb_log_file_size 指定記錄檔大小
• innodb_log_files_in_group 指定每個檔案組下重做記錄檔數量
• innodb_mirrored_log_groups 指定日誌鏡像檔案組的數量，預設為 1，沒有鏡像
• innodb_log_group_home_dir 記錄檔組所在路徑

tips : 重做記錄檔過大，在恢複資料時可能會耗費很長時間；過小，會頻繁切換重做日誌，導致頻繁async checkpoint；

二進位記錄檔與重做記錄檔對比：

• 二進位記錄檔記錄mysql資料庫相關日誌，包括所有儲存引擎 ；重做記錄檔記錄Innodb儲存 引擎交易記錄
• 二進位記錄檔更具binlog_format不同，分別記錄邏輯sql語句（STATEMENT），具體更新內容（ROW），根據情境混合使用（MIXED）；重做記錄檔記錄每個頁的物理更改；
• 二進位日誌不斷寫入二進位日誌緩衝中，事務提交時刷盤一次；重做日誌，在事務過程中不斷寫入到重做記錄檔中

checkpoint技術一個觸發條件就是事務提交，使用innodb_flush_log_at_trx_commit參數可以控制事務提交時要不要強制將重做日誌寫盤;

innodb_flush_log_at_trx_commit：

• 0 : 事務提交時，不強制將重做日誌寫入重做記錄檔中
• 1 ：事務提交時，將重做日誌緩衝同步到磁碟，並fsync（同步檔案系統快取）
• 2：事務提交時，將重做日誌非同步刷回磁碟（寫到檔案系統快取中）

後記

後續將介紹

• 表
• 索引與演算法 B+
• 鎖
• 事務

mysql 預設引擎innodb 初探（三）