MySQL面試寶典-檔案篇

一.請簡述MySQL配置檔案的載入順序？
二.MySQL啟動時如果找不到配置(引數)檔案，會報錯還是啟動？
三.如何檢視MySQL引數？
四.如何修改MySQL引數?
五：MySQL有哪些型別表空間，簡述各自作用？
六：請簡述MySQL redo log和binlog區別？

  一.請簡述MySQL配置檔案的載入順序？

MySQL讀取配置檔案的順序

讀取順序：/etc/mysql/my.cnf>/etc/my.cnf>~/.my.cnf 

命令驗證：

方法1：

[mysql@mysql01 bin]$ mysql --help|grep my.cnf
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
[mysql@mysql01 bin]$ mysql --verbose --help | grep my.cnf
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
                      order of preference, my.cnf, $MYSQL_TCP_PORT,

方法2：   

[mysql@mysql01 bin]$ my_print_defaults --help|grep -A2 -B2 my.cnf
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
Variables (--variable-name=value)

二.MySQL啟動時如果找不到引數檔案，會報錯還是啟動？

MySQL資料庫引數檔案的作用和Oracle資料庫的引數檔案極其類似，不同的是，Oracle例項在啟動時若找不到引數檔案，是不能進行裝載（mount）操作。

MySQL稍微有所不同，MySQL例項可以不需要引數檔案，這時所有的引數值取決於編譯MySQL時指定的預設值和原始碼中指定引數的預設值。

如果MySQL例項在預設的資料庫目錄下找不到mysql架構，則啟動同樣會失敗。

三.如何檢視MySQL引數？

可以把資料庫引數看成一個鍵/值（key/value）對。

可以透過命令SHOW VARIABLES檢視資料庫中的所有引數，也可以透過LIKE來過濾引數名。

從MySQL 5.1版本開始，還可以透過information_schema架構下的GLOBAL_VARIABLES檢視來進行查詢。

show variables like '%timeout%';
mysql> SHOW [{GLOBAL|SESSION}] VARIABLES [LIKE ''];
mysql> SELECT @@{GLOBAL|SESSION}.VARIABLE_NAME;
mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='VARIABLE_NAME';
mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='VARIABLE_NAME';

透過配置檔案檢視引數

cat /etc/my.cnf|grep -i "VARIABLE_NAME"

四.如何修改MySQL引數?

會話級別修改：

set session innodb_lock_wait_timeout=50;

對當前會話立即生效，退出後，引數失效，不影響後續的會話

全域性級別修改：

set global innodb_lock_wait_timeout=50;

當前會話不生效，對後續連線進來的會話生效

修改配置檔案

五：MySQL有哪些型別表空間，簡述各自作用？

MySQL有五種表空間: 

系統表空間(也叫共享表空間) 、獨立表空間 、臨時表空間 、undo表空間 、通用表空間。

1.系統表空間

主要用來存放undo資訊、insert buffer 索引頁、double write buffer 等資料。

系統表空間系統表空間（system tablespace）是在初始化mysql例項時生成的，讀取my.cnf中的innodb_data_file_path引數，初始對應大小的檔案。

mysql預設的系統表空間檔案大小是12M，只有一個檔案(ibdata1)，它預設是儲存在mysql例項的datadir變數的目錄下。

#### 在mysql例項中檢視共享表空間的大小

MySQL [cjcdb]> select @@global.innodb_data_file_path;
+--------------------------------+
| @@global.innodb_data_file_path |
+--------------------------------+
| ibdata1:12M:autoextend         |  # 共享表空間的檔案是ibdata1,大小是12M
+--------------------------------+  # autoextend自動擴充套件
1 row in set (0.00 sec)

#### 在mysql的datadir變數所指定的目錄下檢視系統表空間檔案

MySQL [cjcdb]> select @@global.datadir;
+------------------------+
| @@global.datadir       |
+------------------------+
| /usr/local/mysql/data/ |
+------------------------+
1 row in set (0.00 sec)

當系統表空間不夠用時（也就是ibdata1檔案），會自動擴充套件（autoextend），預設每次自動擴充套件64M。

MySQL [cjcdb]> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
|                            64 |
+-------------------------------+
1 row in set (0.00 sec)

2.獨立表空間

從mysql 5.6.6版本開始，獨立表空間（file-per-table tablespaces）預設是開啟的（也就是innodb_file_per_table引數不設定時，預設等於1），在開啟的情況下，建立一個innodb引擎的表，那麼表有自己獨立的一些資料檔案。

這些資料檔案在作業系統上的檔案體現如下所示：

表名.frm   # 表的表結構檔案(裡面存放的是表的建立語句)

表名.ibd   # 表的資料檔案(當有資料往表中插入時,資料就儲存之個檔案中的)

獨立表空間的好處:

01:表資料分開存放(不把所有雞蛋放在1個藍子裡面);損壞1個檔案不至於影響所有表

02:容易維護,查詢速度快（IO分散）

03:使用MySQL Enterprise Backup快速備份或還原在每表檔案表空間中建立的表，不會中斷其他InnoDB 表的使用

缺點：

對fsync系統呼叫來說不友好，如果使用一個表空間檔案的話單次系統呼叫可以完成資料的落盤，但是如果你將表空間檔案拆分成多個。

原來的一次fsync可能會就變成針對涉及到的所有表空間檔案分別執行一次fsync，增加fsync的次數。

獨立表空間檔案中僅存放該表對應資料、索引、insert buffer bitmap。

其餘的諸如：undo資訊、insert buffer 索引頁、double write buffer 等資訊依然放在預設表空間，也就是共享表空間中。

當innodb_file_per_table引數為0時，表示使用系統表空間，當為1時，表示使用獨立表空間。

innodb_file_per_table選項只對新建的表起作用，對於已經分配了表空間的表不起作用。

如果想把已經分配到系統表空間中的錶轉移到獨立表空間，可以使用下面語句:

ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_tables;

如果要將已經儲存在獨立表空間的錶轉移到系統表空間:

ALTER TABLE 表名 TABLESPACE [=] innodb_system;

其中中括號裡的=可有可無。

與InnoDB不同，MyISAM並沒有什麼表空間一說，表的資料和索引都存放在對應的資料庫子目錄下。

假如cjc表使用的是MyISAM儲存引擎，那麼他所在資料庫對應的目錄下會為cjc表建立下面3個檔案：

1.cjc.frm 表結構。2.cjc.MYD 表資料。3.cjc.MYI 表索引。

3.臨時表空間

臨時表空間用於存放使用者建立的臨時表和磁碟內部臨時表。

引數innodb_temp_data_file_path定義了臨時表空間的一些名稱、大小、規格屬性

MySQL [cjcdb]> show variables like '%innodb_temp_data_file_path%';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)

MySQL 5.7對於InnoDB儲存引擎的臨時表空間做了最佳化。

在MySQL 5.7之前，INNODB引擎的臨時表都儲存在ibdata裡面，而ibdata的貪婪式磁碟佔用導致臨時表的建立與刪除對其他正常表產生非常大的效能影響。

在MySQL5.7中，對於臨時表做了下面兩個重要方面的最佳化：

(1)MySQL 5.7 把臨時表的資料以及回滾資訊(僅限於未壓縮表)從共享表空間裡面剝離出來，形成自己單獨的表空間，引數為innodb_temp_data_file_path。

(2)MySQL 5.7 把臨時表的相關檢索資訊儲存在系統資訊表中：information_schema.innodb_temp_table_info. 

而MySQL 5.7之前的版本想要檢視臨時表的系統資訊是沒有太好的辦法。

select * frominformation_schema.innodb_temp_table_info;

需要注意的一點就是：

雖然INNODB臨時表有自己的表空間，但是目前還不能自己定義臨時表空間檔案的儲存路徑，只能是繼承innodb_data_home_dir。

此時如果想要拿其他的磁碟，比如記憶體盤來充當臨時表空間的儲存地址，只能用老辦法，做軟鏈。

MySQL臨時表型別

1.外部臨時表，透過create temporary table語法建立的臨時表，可以指定儲存引擎為memory，innodb, myisam等等，這類表在會話結束後，會被自動清理。

如果臨時表與非臨時表同時存在，那麼非臨時表不可見。show tables命令不顯示臨時表資訊。

可透過informationschema.INNODBTEMPTABLEINFO系統表可以檢視外部臨時表的相關資訊

2.內部臨時表，通常在執行復雜SQL，比如group by, order by, distinct, union等，執行計劃中如果包含Using temporary.

還有undo回滾的時候，但空間不足的時候，MySQL內部將使用自動生成的臨時表，以輔助完成工作。

外部臨時表、內部臨時表

引數

tmp_table_size

內部臨時表在記憶體中的的最大值，與max_heap_table_size引數共同決定，取二者的最小值。如果臨時表超過該值，就會從記憶體轉移到磁碟上；

max_heap_table_size

使用者建立的記憶體表的最大值，也用於和tmp_table_size一起，限制內部臨時表在記憶體中的大小；

innodb_tmpdir

innodb_temp_data_file_path

innodb引擎下temp檔案屬性。建議限制innodbtempdatafilepath = ibtmp1:1G:autoextend:max:30G；

default_tmp_storage_engine

外部臨時表(create temporary table建立的表)預設的儲存引擎；

internal_tmp_disk_storage_engine

磁碟上的內部臨時表儲存引擎，可選值為myisam或者innodb。

使用innodb表在某些場景下，比如臨時表列太多，或者行大小超過限制，可能會出現“ Row size too large or Too many columns”的錯誤，這時應該將臨時表的innodb引擎改回myisam。

slave_load_tmpdir

tmpdir

表示磁碟上臨時表所在的目錄。

臨時表目錄，當臨時表大小超過一定閾值，就會從記憶體轉移到磁碟上；

max_tmp_tables

狀態資訊

Created_tmp_disk_tables

執行SQL語句時，MySQL在磁碟上建立的內部臨時表數量，如果這個值很大，可能原因是分配給臨時表的最大記憶體值較小，或者SQL中有大量排序、分組、去重等操作，SQL需要最佳化；

Created_tmp_files

建立的臨時表數量；

Created_tmp_tables

執行SQL語句時，MySQL建立的內部臨時表數量；

Slave_open_temp_tables

statement 或則 mix模式下才會看到有使用；

透過複製，當前slave建立了多少臨時表

information_schema.innodb_temp_table_info

4.undo表空間

MySQL5.5時代的undo log

在MySQL5.5以及之前，InnoDB的undo log也是存放在ibdata1裡面的。一旦出現大事務，這個大事務所使用的undo log佔用的空間就會一直在ibdata1裡面存在，即使這個事務已經關閉。

答案是沒有直接的辦法，只能全庫匯出sql檔案，然後重新初始化mysql例項，再全庫匯入。

MySQL 5.6時代的undo log

MySQL 5.6增加了引數innodb_undo_directory、innodb_undo_logs和innodb_undo_tablespaces這3個引數，可以把undo log從ibdata1移出來單獨存放。

innodb_undo_directory，

指定單獨存放undo表空間的目錄，預設為.(即datadir)，可以設定相對路徑或者絕對路徑。

該引數例項初始化之後雖然不可直接改動，但是可以透過先停庫，修改配置檔案，然後移動undo表空間檔案的方式去修改該引數；

innodb_undo_tablespaces，

指定單獨存放的undo表空間個數，例如如果設定為3，則undo表空間為undo001、undo002、undo003，每個檔案初始大小預設為10M。

該引數我們推薦設定為大於等於3，原因下文將解釋。該引數例項初始化之後不可改動；

innodb_undo_logs，

指定回滾段的個數(早期版本該引數名字是innodb_rollback_segments)，預設128個。每個回滾段可同時支援1024個線上事務。

這些回滾段會平均分佈到各個undo表空間中。

該變數可以動態調整，但是物理上的回滾段不會減少，只是會控制用到的回滾段的個數。

實際使用方面，在初始化例項之前，我們只需要設定innodb_undo_tablespaces引數(建議大於等於3)即可將undo log設定到單獨的undo表空間中。

MySQL 5.7時代的undo log

MySQL 5.7引入了新的引數，innodb_undo_log_truncate，開啟後可線上收縮拆分出來的undo表空間。

在滿足以下2個條件下，undo表空間檔案可線上收縮：

innodb_undo_tablespaces>=2。因為truncate undo表空間時，該檔案處於inactive狀態，如果只有1個undo表空間，那麼整個系統在此過程中將處於不可用狀態。

為了儘可能降低truncate對系統的影響，建議將該引數最少設定為3；

innodb_undo_logs>=35(預設128)。

因為在MySQL 5.7中，第一個undo log永遠在系統表空間中，另外32個undo log分配給了臨時表空間，即ibtmp1，至少還有2個undo log才能保證2個undo表空間中每個裡面至少有1個undo log；

滿足以上2個條件後，把innodb_undo_log_truncate設定為ON即可開啟undo表空間的自動truncate，這還跟如下2個引數有關：

(1)innodb_max_undo_log_size，undo表空間檔案超過此值即標記為可收縮，預設1G，可線上修改；

(2)innodb_purge_rseg_truncate_frequency,指定purge操作被喚起多少次之後才釋放rollback segments。當undo表空間裡面的rollback segments被釋放時，undo表空間才會被truncate。由此可見，該引數越小，undo表空間被嘗試truncate的頻率越高。

MySQL 8.0收縮UNDO

1、新增新的undo檔案undo003。mysql8.0中預設innodb_undo_tablespace為2個，不足2個時，不允許設定為inactive，且預設建立的undo受保護，不允許刪除。

2、將膨脹的 undo 臨時設定為inactive，以及 innodb_undo_log_truncate=on，自動 truncate 釋放膨脹的undo空間。

3、重新將釋放空間之後的undo設定為active，可重新上線使用。

具體操作如下：

MySQL [cjcdb]> show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | OFF        |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 0          |
+--------------------------+------------+
5 rows in set (0.00 sec)

檢視undo大小

mysql[(none)]> system du -sh  /app/dbdata/datanode3307/log/undo*
10G /app/dbdata/datanode3307/log/undo_001

新增新的undo表空間undo003。系統預設是2個undo，大小設定4G

mysql[(none)]> 
mysql[(none)]> create undo tablespace undo001 add datafile '/usr/local/mysql/data/undo/undo001.ibu';
Query OK, 0 rows affected (0.21 sec)

注意：建立新增新的undo必須以.ibu結尾，否則觸發如下錯誤提示

mysql[(none)]> create undo tablespace undo003 add datafile '/app/dbdata/datanode3307/log/undo_003.' ;
ERROR 3121 (HY000): The ADD DATAFILE filepath must end with '.ibu'.

5.通用表空間

通用表空間(General Tablespaces)

通用表空間為透過create tablespace語法建立的共享表空間。

通用表空間可以建立於mysql資料目錄外的其他表空間，其可以容納多張表，且其支援所有的行格式。

透過create table tab_name ... tablespace [=] tablespace_name或alter table tab_name tablespace [=] tablespace_name語法將其新增與通用表空間內。

六：請簡述MySQL redo log和binlog區別？ 

redo log 和 binlog 的區別：

日誌歸屬：

binlog由Server層實現，所有引擎都可以使用。

redo log是innodb引擎特有的日誌。

日誌型別：

binlog是邏輯日誌，記錄原始SQL或資料變更前後內容。

redo是物理日誌，記錄在哪些頁上進行了哪些修改。

寫入方式：

binlog是追加寫，寫滿一個檔案後建立新檔案繼續寫。

redo log是迴圈寫，全部寫滿後覆蓋從頭寫。

適用場景：

binlog適用於主從恢復和誤刪除恢復。

redo log適用於崩潰恢復。

雖然在更新BufferPool後，也寫入了binlog中，但binlog並不具備crash-safe的能力。

因為崩潰可能發生在寫binlog後，刷髒前。在主從同步的情況下，從節點會拿到多出來的一條binlog。

所以server層的binlog是不支援崩潰恢復的，只是支援誤刪資料恢復。InnoDB考慮到這一點，自己實現了redo log。

因為最開始 MySQL 裡並沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒有 crash-safe 的能力，binlog 日誌只能用於歸檔。

而 InnoDB 是另一個公司以外掛形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB 使用另外一套日誌系統——也就是 redo log 來實現 crash-safe 能力。

###chenjuchao 20220410###