學習《mysql 技術內幕：InnoDB 存儲引擎》 第三章關於資料庫文件以及 InnoDB 存儲引擎相關文件總結如下：

mysql 文件主要包括參數文件，日誌文件，socket 文件，pid文件，表結構文件，存儲引擎文件。

參數文件

mysql 啟動時會讀取配置參數文件，如果找不到文件，會使用默認值和源代碼中指定參數的默認值。

查看參數的兩種方式：

  • show variables

show varables like "%innodb_use_native_aio%";

  • information_schema.GLOBAL_VARIABLES

select * from GLOBAL_VARIABLES where varable_name like "%innodb_use_native_aio%";

參數分類：

  • 動態參數：

在實例運行中可以修改，但是只在當前生命周期中有效，如果 mysql 重啟，還是會重新讀取參數文件。所以如果想要在所有生命周期中有效，必須修改參數文件。

對參數的修改可以分為 global 和 session，表示影響的是整個生命周期還是當前會話。有些參數只能在當前會話中修改，有的參數只能在整個生命周期中修改，有些參數既可以在當前會話中修改也可以在整個生命中期中修改。

set @@global.read_buffer_size=104876;
set @@session.read_buffer_size=78929;
select @@session.read_buffer_size;
select @@global.read_buffer_size;

  • 靜態參數：

在整個生命中期中不可以修改。

日誌文件

記錄了 Mysql 資料庫各種類型的操作行為，主要包括錯誤日誌，慢查詢日誌，查詢日誌，二進位日誌。

1. 錯誤日誌

不僅記錄了所有錯誤信息，還有一些告警和正確的提示信息，方便定位問題。可以查看參數 log_error 來找到 mysql 錯誤日誌文件的位置

show variables like "%log_error%"

2. 慢查詢日誌

可以通過參數 log_slow_queries 設置是否開啟慢查詢日誌。

主要可以記錄三種慢查詢 SQL：

  • 查詢時間大於參數 long_query_time 的 SQL
  • 沒有使用索引的查詢，是否記錄取決於 log_queries_not_using_indexes 參數的設置
  • 對邏輯讀取次數大於參數 long_query_io 次數的 SQL 記錄到慢查詢中

慢查詢日誌的輸出格式，由 log_output 格式指定，主要有兩種格式：

  • FILE 如果是文件格式，可以使用 mysqldumpslow 命令分析慢查詢日誌

- 查詢執行時間最長的 10 條 SQL 語句
mysqldumpslow -s al -n 10 david.log

  • TABLE：記錄在 mysql 架構下的 slow_log 表中。

3. 查詢日誌

查詢日誌記錄了所有對 MYSQL 資料庫請求的信息，無論這些請求是否得到正確的執行。通過 general_log 和 general_log_file 參數設置。

4. 二進位日誌

二進位日誌(binary log) 記錄了對 MYSQL 資料庫執行更改的所有操作，但是不包括 SELECT 和 SHOW 類型操作，通過參數 log-bin 參數來開啟，主要用途如下：

  • 恢複數據
  • 複製：通過 binary log 將本資料庫和其它資料庫進行同步
  • 審計：對數據的修改做審計

開啟 binary log 會對性能造成一定的影響，但是這個影響有限，差不多 1% 的下降。

一些關於二進位日誌的配置參數：

  • max_binlog_size：指定了二進位文件的最大值，如果超過該值，則產生新的二進位文件
  • binlog_cache_size：該參數記錄二進位文件緩存的大小

當使用事務時，二進位文件的記錄會被先放入緩存，當事務提交時後面再寫入二進位文件，該參數記錄二進位文件緩存的大小。當超過這個值但是事務沒有被提交，這個時候會把緩存中的值寫入臨時文件。

  • binlog_cache_use：記錄使用緩存寫二進位日誌的次數
  • binlog_cache_disk_use：記錄了使用臨時文件寫二進位日誌的次數
  • sync_binlog：表示二進位文件每次寫緩存多少次被同步到磁碟。
  • binlog_do_db：表示需要寫入哪些庫的二進位文件
  • binlog_ingore_db：忽略哪些庫的二進位文件
  • log_slave_update：是否主動將 master 取得的並執行的二進位日誌寫入 slave 角色中，高可用情況下，該參數必須開啟
  • innodb_support_xa: 該參數可以確保二進位日誌與 InnoDb 存儲數據文件的同步，如果不開啟該參數，有些情況下雖然事務回滾了，但是仍然記錄了二進位文件，會導致數據不一致
  • binlog_format：記錄二進位日誌的格式，5.1 版本引入，動態參數，可以運行時修改。一共有三種日誌格式：

- STATEMENT
記錄日誌文件執行的邏輯 SQL 語句，但是如果在伺服器上運行 rand，uuid 等函數或者使用觸發器，都可能導致主從伺服器數據不一致，默認的日誌格式就是 STATEMENT。

- ROW
記錄表的行的更改情況，不會出現數據不一致情況，但由於記錄的是行數據的更改，二進位文件有時候會很大，開啟該參數對於磁碟的開銷和複製的網路開銷都有一定的成本
- MIXED
採用 STATEMENT 和 ROW 混合的方式記錄日誌，通常情況下使用 STATEMENT 方式，一些特殊情況下使用 ROW 方式：
1) 存儲引擎為 NDB
2）使用 UUID，USER，CURRENT_USER，FOUND_ROWS,ROW_COUNT 等不確定函數
3）使用了 INSERT DELAY 語句
4）使用了用戶定義的函數
5）使用了臨時表

binary log 文件格式二進位的，不能直接查看，必須用工具 mysqlbinlog 命令分析查看

mysqlbinlog --start-position=203 test.000004

socket 文件

在本地連接 MYSQL 時使用 UNIX 域套接字方式時會用到的連接文件，通過參數 socket 來設置套接字文件的存儲位置

pid 文件

存放 mysql 進程 ID，通過參數 pid_file 來設置 pid 文件存放位置

表結構文件

以 frm 為後綴名的文件，記錄了相關表和視圖的結構定義。

InnoDB 存儲引擎文件

上面介紹的查詢日誌，二進位日誌等都是 mysql 資料庫本身的問題，和存儲引擎無關，而每個存儲引擎有自己獨立文件，其中InnoDB 存儲引擎文件包括重做日誌文件和表空間文件：

1. 表空間文件

  • 通過參數 innodb_data_file_path 設置表空間文件的共享空間
  • 通過 innodb_data_per_table 設置是否基於每個表產生一個獨立的表空間
  • 單獨的表空間只存儲了該表的數據，索引和插入緩存 BITMAP 等信息，其它信息仍然存放在默認共享表空間中。

2. 重做日誌文件

  • 重做日誌文件記錄了 InnoDB 存儲引擎的事務日誌，在發生宕機的情況下，對事務操作進行恢復操作。
  • 重做日誌文件至少有一個重做日誌文件組，每個重做日誌文件組至少有 2 個重做日誌文件。
  • 為了保證高可用性，可以設置鏡像日誌文件組，將日誌組存儲在不同的磁碟上。
  • 重做日誌的寫入不需要 doublewrite，因為寫入是按照一個扇區的大小進行寫入的，是寫入的最小單位，所以可以保證寫入的可靠性。
  • 重做日誌也有緩存，但是緩存不大，默認為 8MB，將重做日誌緩存刷新到日誌文件文件的三個條件：

- Master Thread 會每一秒刷新一次
- 每個事物提交時會將刷新，由 innodb_flush_log_at_trx_commit 參數控制：
0 表示事務提交時，不寫入重做日誌文件，而是等待 Master Thread 的定時刷新
1 表示每當事務提交時，就必須同步寫入重做日誌文件，為了保證 ACID 中的持久性，事務必須設置為 1。才能保證宕機時可以恢復
2 表示重做日誌文件非同步寫入磁碟
- 當重做日誌緩存池剩餘空間小於 1/2 時會刷新，重做日誌緩存不能設置太小，否則會導致頻繁的執行 checkpoint 操作，導致性能的抖動。

  • 重做日誌文件幾個相關的參數：

- innodb_log_file_size：指定每個重做日誌文件的大小
- innodb_log_files_in_group：每個日誌文件中重做日誌文件的數量
- innodb_mirrored_log_groups：日誌鏡像文件組的數量
- innodb_log_group_home_dir：日誌文件組所在路徑

  • 重做日誌文件和二進位文件的區別：

- 二進位日誌文件記錄所有與 MYSQL 資料庫有關的記錄，而重做日誌文件只記錄了關於 InnoDB 的事務日誌
- 二進位日誌文件記錄的是關於一個事務的具體操作內容，而重做日誌文件記錄了 InnoDB 關於每個頁的更改的物理情況
- 二進位日誌文件僅在事務提交前進行提交，而重做日誌文件在事務進行的過程中，卻有不斷重做日誌條目被寫入

本文涉及到的相關配置參數

  • log_error：錯誤日誌文件所在位置
  • long_query_time：慢查詢日誌閾值，默認是 10 s
  • long_slow_queries：是否開啟慢查詢日誌記錄，默認是 off
  • long_queries_not_using_indexes：是否開啟查詢沒有使用索引時記錄到慢查詢日誌中，默認為 0
  • long_throttle_queries_not_using_indexes：表示每分鐘容許記錄到慢查詢日誌中未使用索引的 SQL 個數
  • log_output：指定慢查詢日誌的輸出格式，默認是 FILE，可以將其設置為 TABLE，就可以 slow_log 表中查詢了
  • long_query_io：將超過指定邏輯 IO 次數的 SQL 語句記錄到慢查詢日誌中
  • general_log：是否開啟查詢日誌
  • general_log_file：查詢日誌記錄文件
  • log-bin：是否開啟 binary log
  • max_binlog_size：指定了二進位文件的最大值，如果超過該值，則產生新的二進位文件
  • binlog_cache_size：該參數記錄二進位文件緩存的大小
  • binlog_cache_use：記錄使用緩存寫二進位日誌的次數
  • binlog_cache_disk_use：記錄了使用臨時文件寫二進位日誌的次數
  • sync_binlog：表示二進位文件每次寫緩存多少次被同步到磁碟。
  • binlog_do_db：表示需要寫入哪些庫的二進位文件
  • binlog_ingore_db：忽略哪些庫的二進位文件
  • log_slave_update：是否主動將 master 取得的並執行的二進位日誌寫入 slave 角色中，高可用情況下，該參數必須開啟
  • innodb_support_xa: 該參數可以確保二進位日誌與 InnoDb 存儲數據文件的同步
  • binlog_format：記錄二進位日誌的格式，5.1 版本引入，動態參數，可以運行時修改。
  • socket：套接字文件的存儲位置
  • pid_file：pid 文件存放位置
  • innodb_data_file_path：定義 innodb 表空間文件位置
  • innodb_file_per_table：設置是否每個表都會產生一個獨立的表空間
  • innodb_flush_log_at_trx_commit：是否在事務提交時將重做日誌緩存文件寫入磁碟文件
  • innodb_log_file_size：指定每個重做日誌文件的大小
  • innodb_log_files_in_group：每個日誌文件中重做日誌文件的數量
  • innodb_mirrored_log_groups：重做日誌鏡像文件組的數量
  • innodb_log_group_home_dir：重做日誌文件組所在路徑

推薦閱讀：

相关文章