1. SQL

1.1 介紹一下數據庫分頁

參考答案

MySQL的分頁語法：

在MySQL中，SELECT語句默認返回所有匹配的行，它們可能是指定表中的每個行。為了返回第一行或前幾行，可使用LIMIT子句，以實現分頁查詢。LIMIT子句的語法如下：

-- 在所有的查詢結果中，返回前5行記錄。
SELECT prod_name FROM products LIMIT 5;
-- 在所有的查詢結果中，從第5行開始，返回5行記錄。
SELECT prod_name FROM products LIMIT 5,5;

總之，帶一個值的LIMIT總是從第一行開始，給出的數為返回的行數。帶兩個值的LIMIT可以指定從行號為第一個值的位置開始。

優化LIMIT分頁：

在偏移量非常大的時候，例如 LIMIT 10000,20 這樣的查詢，這時MySQL需要查詢10020條記錄然后只返回最后20條，前面的10000條記錄都將被拋棄，這樣的代價是非常高的。如果所有的頁面被訪問的頻率都相同，那么這樣的查詢平均需要訪問半個表的數據。要優化這種查詢，要么是在頁面中限制分頁的數量，要么是優化大偏移量的性能。

優化此類分頁查詢的一個最簡單的辦法就是盡可能地使用索引覆蓋掃描，而不是查詢所有的列，然后根據需要做一次關聯操作再返回所需的列。對于偏移量很大的時候，這樣做的效率會提升非常大。考慮下面的查詢：

SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;

如果這個表非常大，那么這個查詢最好改寫成下面的樣子：

SELECT film.film_id,film.description 
FROM sakila.film
INNER JOIN (SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5
) AS lim USING(film_id);

這里的“延遲關聯”將大大提升查詢效率，它讓MySQL掃描盡可能少的頁面，獲取需要訪問的記錄后再根據關聯列回原表查詢需要的所有列。這個技術也可以用于優化關聯查詢中的LIMIT子句。

有時候也可以將LIMIT查詢轉換為已知位置的查詢，讓MySQL通過范圍掃描獲得對應的結果。例如，如果在一個位置列上有索引，并且預先計算出了邊界值，上面的查詢就可以改寫為：

SELECT film_id,description FROM skila.film
WHERE position BETWEEN 50 AND 54 ORDER BY position;

對數據進行排名的問題也與此類似，但往往還會同時和GROUP BY混合使用，在這種情況下通常都需要預先計算并存儲排名信息。

LIMIT和OFFSET的問題，其實是OFFSET的問題，它會導致MySQL掃描大量不需要的行然后再拋棄掉。如果可以使用書簽記錄上次取數的位置，那么下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用OFFSET。例如，若需要按照租賃記錄做翻頁，那么可以根據最新一條租賃記錄向后追溯，這種做法可行是因為租賃記錄的主鍵是單調增長的。首先使用下面的查詢獲得第一組結果：

SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;

假設上面的查詢返回的是主鍵16049到16030的租賃記錄，那么下一頁查詢就可以從16030這個點開始：

SELECT * FROM sakila.rental 
WHERE rental_id < 16030 ORDER BY rental_id DESC LIMIT 20;

該技術的好處是無論翻頁到多么后面，其性能都會很好。

1.2 介紹一下SQL中的聚合函數

參考答案

常用的聚合函數有COUNT()、AVG()、SUM()、MAX()、MIN()，下面以MySQL為例，說明這些函數的作用。

COUNT()函數：

COUNT()函數統計數據表中包含的記錄行的總數，或者根據查詢結果返回列中包含的數據行數，它有兩種用法：

• COUNT(*)計算表中總的行數，不管某列是否有數值或者為空值。
• COUNT(字段名)計算指定列下總的行數，計算時將忽略空值的行。

COUNT()函數可以與GROUP BY一起使用來計算每個分組的總和。

AVG()函數()：

AVG()函數通過計算返回的行數和每一行數據的和，求得指定列數據的平均值。

AVG()函數可以與GROUP BY一起使用，來計算每個分組的平均值。

SUM()函數：

SUM()是一個求總和的函數，返回指定列值的總和。

SUM()可以與GROUP BY一起使用，來計算每個分組的總和。

MAX()函數：

MAX()返回指定列中的最大值。

MAX()也可以和GROUP BY關鍵字一起使用，求每個分組中的最大值。

MAX()函數不僅適用于查找數值類型，也可應用于字符類型。

MIN()函數：

MIN()返回查詢列中的最小值。

MIN()也可以和GROUP BY關鍵字一起使用，求出每個分組中的最小值。

MIN()函數與MAX()函數類似，不僅適用于查找數值類型，也可應用于字符類型。

1.3 表跟表是怎么關聯的？

參考答案

表與表之間常用的關聯方式有兩種：內連接、外連接，下面以MySQL為例來說明這兩種連接方式。

內連接：

內連接通過INNER JOIN來實現，它將返回兩張表中滿足連接條件的數據，不滿足條件的數據不會查詢出來。

外連接：

外連接通過OUTER JOIN來實現，它會返回兩張表中滿足連接條件的數據，同時返回不滿足連接條件的數據。外連接有兩種形式：左外連接（LEFT OUTER JOIN）、右外連接（RIGHT OUTER JOIN）。

• 左外連接：可以簡稱為左連接（LEFT JOIN），它會返回左表中的所有記錄和右表中滿足連接條件的記錄。
• 右外連接：可以簡稱為右連接（RIGHT JOIN），它會返回右表中的所有記錄和左表中滿足連接條件的記錄。

除此之外，還有一種常見的連接方式：等值連接。這種連接是通過WHERE子句中的條件，將兩張表連接在一起，它的實際效果等同于內連接。出于語義清晰的考慮，一般更建議使用內連接，而不是等值連接。

以上是從語法上來說明表與表之間關聯的實現方式，而從表的關系上來說，比較常見的關聯關系有：一對多關聯、多對多關聯、自關聯。

• 一對多關聯：這種關聯形式最為常見，一般是兩張表具有主從關系，并且以主表的主鍵關聯從表的外鍵來實現這種關聯關系。另外，以從表的角度來看，它們是具有多對一關系的，所以不再贅述多對一關聯了。
• 多對多關聯：這種關聯關系比較復雜，如果兩張表具有多對多的關系，那么它們之間需要有一張中間表來作為銜接，以實現這種關聯關系。這個中間表要設計兩列，分別存儲那兩張表的主鍵。因此，這兩張表中的任何一方，都與中間表形成了一對多關系，從而在這個中間表上建立起了多對多關系。
• 自關聯：自關聯就是一張表自己與自己相關聯，為了避免表名的沖突，需要在關聯時通過別名將它們當做兩張表來看待。一般在表中數據具有層級（樹狀）時，可以采用自關聯一次性查詢出多層級的數據。

1.4 說一說你對外連接的了解

參考答案

外連接通過OUTER JOIN來實現，它會返回兩張表中滿足連接條件的數據，同時返回不滿足連接條件的數據。常見的外連接有兩種形式：左外連接（LEFT OUTER JOIN）、右外連接（RIGHT OUTER JOIN）。

• 左外連接：可以簡稱為左連接（LEFT JOIN），它會返回左表中的所有記錄和右表中滿足連接條件的記錄。
• 右外連接：可以簡稱為右連接（RIGHT JOIN），它會返回右表中的所有記錄和左表中滿足連接條件的記錄。

實際上，外連接還有一種形式：完全外連接（FULL OUTER JOIN），但MySQL不支持這種形式。

1.5 說一說數據庫的左連接和右連接

參考答案

外連接通過OUTER JOIN來實現，它會返回兩張表中滿足連接條件的數據，同時返回不滿足連接條件的數據。常見的外連接有兩種形式：左外連接（LEFT OUTER JOIN）、右外連接（RIGHT OUTER JOIN）。

• 左外連接：可以簡稱為左連接（LEFT JOIN），它會返回左表中的所有記錄和右表中滿足連接條件的記錄。
• 右外連接：可以簡稱為右連接（RIGHT JOIN），它會返回右表中的所有記錄和左表中滿足連接條件的記錄。

1.6 SQL中怎么將行轉成列？

參考答案

我們以MySQL數據庫為例，來說明行轉列的實現方式。

首先，假設我們有一張分數表（tb_score），表中的數據如下圖：

然后，我們再來看一下轉換之后需要得到的結果，如下圖：

可以看出，這里行轉列是將原來的subject字段的多行內容選出來，作為結果集中的不同列，并根據userid進行分組顯示對應的score。通常，我們有兩種方式來實現這種轉換。

1. 使用 CASE...WHEN...THEN 語句實現行轉列，參考如下代碼：

   SELECT userid,
   SUM(CASE `subject` WHEN '語文' THEN score ELSE 0 END) as '語文',
   SUM(CASE `subject` WHEN '數學' THEN score ELSE 0 END) as '數學',
   SUM(CASE `subject` WHEN '英語' THEN score ELSE 0 END) as '英語',
   SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
   FROM tb_score 
   GROUP BY userid
   

   注意，SUM() 是為了能夠使用GROUP BY根據userid進行分組，因為每一個userid對應的subject="語文"的記錄只有一條，所以SUM() 的值就等于對應那一條記錄的score的值。假如userid =‘001’ and subject=‘語文’ 的記錄有兩條，則此時SUM() 的值將會是這兩條記錄的和，同理，使用Max()的值將會是這兩條記錄里面值最大的一個。但是正常情況下，一個user對應一個subject只有一個分數，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函數都可以達到行轉列的效果。

2. 使用 IF() 函數實現行轉列，參考如下代碼：

   SELECT userid,
   SUM(IF(`subject`='語文',score,0)) as '語文',
   SUM(IF(`subject`='數學',score,0)) as '數學',
   SUM(IF(`subject`='英語',score,0)) as '英語',
   SUM(IF(`subject`='政治',score,0)) as '政治' 
   FROM tb_score 
   GROUP BY userid
   

   注意，IF(subject='語文',score,0) 作為條件，即對所有subject='語文’的記錄的score字段進行SUM()、MAX()、MIN()、AVG()操作，如果score沒有值則默認為0。

1.7 談談你對SQL注入的理解

參考答案

SQL注入的原理是將SQL代碼偽裝到輸入參數中，傳遞到服務器解析并執行的一種攻擊手法。也就是說，在一些對SERVER端發起的請求參數中植入一些SQL代碼，SERVER端在執行SQL操作時，會拼接對應參數，同時也將一些SQL注入攻擊的“SQL”拼接起來，導致會執行一些預期之外的操作。

舉個例子：

比如我們的登錄功能，其登錄界面包括用戶名和密碼輸入框以及提交按鈕，登錄時需要輸入用戶名和密碼，然后提交。此時調用接口/user/login/ 加上參數username、password，首先連接數據庫，然后后臺對請求參數中攜帶的用戶名、密碼進行參數校驗，即SQL的查詢過程。假設正確的用戶名和密碼為ls和123456，輸入正確的用戶名和密碼、提交，相當于調用了以下的SQL語句。

SELECT * FROM user WHERE username = 'ls' AND password = '123456'

SQL中會將#及–以后的字符串當做注釋處理，如果我們使用 ' or 1=1 # 作為用戶名參數，那么服務端構建的SQL語句就如下：

select * from user where username='' or 1=1 #' and password='123456'

而#會忽略后面的語句，而1=1屬于常等型條件，因此這個SQL將查詢出所有的登錄用戶。其實上面的SQL注入只是在參數層面做了些手腳，如果是引入了一些功能性的SQL那就更危險了，比如上面的登錄功能，如果用戶名使用這個 ' or 1=1;delete * from users; #，那么在";"之后相當于是另外一條新的SQL，這個SQL是刪除全表，是非常危險的操作，因此SQL注入這種還是需要特別注意的。

如何解決SQL注入

1. 嚴格的參數校驗

   參數校驗就沒得說了，在一些不該有特殊字符的參數中提前進行特殊字符校驗即可。

2. SQL預編譯

   在知道了SQL注入的原理之后，我們同樣也了解到MySQL有預編譯的功能，指的是在服務器啟動時，MySQL Client把SQL語句的模板（變量采用占位符進行占位）發送給MySQL服務器，MySQL服務器對SQL語句的模板進行編譯，編譯之后根據語句的優化分析對相應的索引進行優化，在最終綁定參數時把相應的參數傳送給MySQL服務器，直接進行執行，節省了SQL查詢時間，以及MySQL服務器的資源，達到一次編譯、多次執行的目的，除此之外，還可以防止SQL注入。

   具體是怎樣防止SQL注入的呢？實際上當將綁定的參數傳到MySQL服務器，MySQL服務器對參數進行編譯，即填充到相應的占位符的過程中，做了轉義操作。我們常用的JDBC就有預編譯功能，不僅提升性能，而且防止SQL注入。

1.8 將一張表的部分數據更新到另一張表，該如何操作呢？

參考答案

可以采用關聯更新的方式，將一張表的部分數據，更新到另一張表內。參考如下代碼：

update b set b.col=a.col from a,b where a.id=b.id;
update b set col=a.col from b inner join a on a.id=b.id;
update b set b.col=a.col from b left Join a on b.id = a.id;

1.9 WHERE和HAVING有什么區別？

參考答案

WHERE是一個約束聲明，使用WHERE約束來自數據庫的數據，WHERE是在結果返回之前起作用的，WHERE中不能使用聚合函數。

HAVING是一個過濾聲明，是在查詢返回結果集以后對查詢結果進行的過濾操作，在HAVING中可以使用聚合函數。另一方面，HAVING子句中不能使用除了分組字段和聚合函數之外的其他字段。

從性能的角度來說，HAVING子句中如果使用了分組字段作為過濾條件，應該替換成WHERE子句。因為WHERE可以在執行分組操作和計算聚合函數之前過濾掉不需要的數據，性能會更好。

2. 索引

2.1 說一說你對MySQL索引的理解

參考答案

索引是一個單獨的、存儲在磁盤上的數據庫結構，包含著對數據表里所有記錄的引用指針。使用索引可以快速找出在某個或多個列中有一特定值的行，所有MySQL列類型都可以被索引，對相關列使用索引是提高查詢操作速度的最佳途徑。

索引是在存儲引擎中實現的，因此，每種存儲引擎的索引都不一定完全相同，并且每種存儲引擎也不一定支持所有索引類型。MySQL中索引的存儲類型有兩種，即BTREE和HASH，具體和表的存儲引擎相關。MyISAM和InnoDB存儲引擎只支持BTREE索引；MEMORY/HEAP存儲引擎可以支持HASH和BTREE索引。

索引的優點主要有以下幾條：

1. 通過創建唯一索引，可以保證數據庫表中每一行數據的唯一性。
2. 可以大大加快數據的查詢速度，這也是創建索引的主要原因。
3. 在實現數據的參考完整性方面，可以加速表和表之間的連接。
4. 在使用分組和排序子句進行數據查詢時，也可以顯著減少查詢中分組和排序的時間。

增加索引也有許多不利的方面，主要表現在如下幾個方面：

1. 創建索引和維護索引要耗費時間，并且隨著數據量的增加所耗費的時間也會增加。
2. 索引需要占磁盤空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果有大量的索引，索引文件可能比數據文件更快達到最大文件尺寸。
3. 當對表中的數據進行增加、刪除和修改的時候，索引也要動態地維護，這樣就降低了數據的維護速度。

2.2 索引有哪幾種？

參考答案

MySQL的索引可以分為以下幾類：

1. 普通索引和唯一索引

   普通索引是MySQL中的基本索引類型，允許在定義索引的列中插入重復值和空值。

   唯一索引要求索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

   主鍵索引是一種特殊的唯一索引，不允許有空值。

2. 單列索引和組合索引

   單列索引即一個索引只包含單個列，一個表可以有多個單列索引。

   組合索引是指在表的多個字段組合上創建的索引，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用。使用組合索引時遵循最左前綴集合。

3. 全文索引

   全文索引類型為FULLTEXT，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復值和空值。全文索引可以在CHAR、VARCHAR或者TEXT類型的列上創建。MySQL中只有MyISAM存儲引擎支持全文索引。

4. 空間索引

   空間索引是對空間數據類型的字段建立的索引，MySQL中的空間數據類型有4種，分別是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL關鍵字進行擴展，使得能夠用創建正規索引類似的語法創建空間索引。創建空間索引的列，必須將其聲明為NOT NULL，空間索引只能在存儲引擎為MyISAM的表中創建。

2.3 如何創建及保存MySQL的索引？

參考答案

MySQL支持多種方法在單個或多個列上創建索引：

在創建表的時候創建索引：

使用CREATE TABLE創建表時，除了可以定義列的數據類型，還可以定義主鍵約束、外鍵約束或者唯一性約束，而不論創建哪種約束，在定義約束的同時相當于在指定列上創建了一個索引。創建表時創建索引的基本語法如下：

CREATE TABLE table_name [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]

其中，UNIQUE、FULLTEXT和SPATIAL為可選參數，分別表示唯一索引、全文索引和空間索引；INDEX與KEY為同義詞，兩者作用相同，用來指定創建索引。

例如，可以按照如下方式，在id字段上使用UNIQUE關鍵字創建唯一索引：

CREATE TABLE t1 (id INT NOT NULL,name CHAR(30) NOT NULL,UNIQUE INDEX UniqIdx(id)
);

在已存在的表上創建索引

在已經存在的表中創建索引，可以使用ALTER TABLE語句或者CREATEINDEX語句。

ALTER TABLE創建索引的基本語法如下：

ALTER TABLE table_name ADD 
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

例如，可以按照如下方式，在bookId字段上建立名稱為UniqidIdx的唯一索引：

ALTER TABLE book ADD UNIQUE INDEX UniqidIdx (bookId);

CREATE INDEX創建索引的基本語法如下：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
ON table_name (col_name [length],...) [ASC|DESC]

例如，可以按照如下方式，在bookId字段上建立名稱為UniqidIdx的唯一索引：

CREATE UNIQUE INDEX UniqidIdx ON book (bookId);

2.4 MySQL怎么判斷要不要加索引？

參考答案

建議按照如下的原則來創建索引：

1. 當唯一性是某種數據本身的特征時，指定唯一索引。使用唯一索引需能確保定義的列的數據完整性，以提高查詢速度。
2. 在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引。

2.5 只要創建了索引，就一定會走索引嗎？

參考答案

不一定。

比如，在使用組合索引的時候，如果沒有遵從“最左前綴”的原則進行搜索，則索引是不起作用的。

舉例，假設在id、name、age字段上已經成功建立了一個名為MultiIdx的組合索引。索引行中按id、name、age的順序存放，索引可以搜索id、（id,name）、（id, name, age）字段組合。如果列不構成索引最左面的前綴，那么MySQL不能使用局部索引，如（age）或者（name,age）組合則不能使用該索引查詢。

2.6 如何判斷數據庫的索引有沒有生效？

參考答案

可以使用EXPLAIN語句查看索引是否正在使用。

舉例，假設已經創建了book表，并已經在其year_publication字段上建立了普通索引。執行如下語句：

EXPLAIN SELECT * FROM book WHERE year_publication=1990;

EXPLAIN語句將為我們輸出詳細的SQL執行信息，其中：

• possible_keys行給出了MySQL在搜索數據記錄時可選用的各個索引。
• key行是MySQL實際選用的索引。

如果possible_keys行和key行都包含year_publication字段，則說明在查詢時使用了該索引。

2.7 如何評估一個索引創建的是否合理？

建議按照如下的原則來設計索引：

1. 避免對經常更新的表進行過多的索引，并且索引中的列要盡可能少。應該經常用于查詢的字段創建索引，但要避免添加不必要的字段。
2. 數據量小的表最好不要使用索引，由于數據較少，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產生優化效果。
3. 在條件表達式中經常用到的不同值較多的列上建立索引，在不同值很少的列上不要建立索引。比如在學生表的“性別”字段上只有“男”與“女”兩個不同值，因此就無須建立索引，如果建立索引不但不會提高查詢效率，反而會嚴重降低數據更新速度。
4. 當唯一性是某種數據本身的特征時，指定唯一索引。使用唯一索引需能確保定義的列的數據完整性，以提高查詢速度。
5. 在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引。

2.8 索引是越多越好嗎？

參考答案

索引并非越多越好，一個表中如有大量的索引，不僅占用磁盤空間，還會影響INSERT、DELETE、UPDATE等語句的性能，因為在表中的數據更改時，索引也會進行調整和更新。

2.9 數據庫索引失效了怎么辦？

參考答案

可以采用以下幾種方式，來避免索引失效：

1. 使用組合索引時，需要遵循“最左前綴”原則；
2. 不在索引列上做任何操作，例如計算、函數、類型轉換，會導致索引失效而轉向全表掃描；
3. 盡量使用覆蓋索引（之訪問索引列的查詢），減少 select * 覆蓋索引能減少回表次數；
4. MySQL在使用不等于（!=或者<>）的時候無法使用索引會導致全表掃描；
5. LIKE以通配符開頭（%abc）MySQL索引會失效變成全表掃描的操作；
6. 字符串不加單引號會導致索引失效（可能發生了索引列的隱式轉換）；
7. 少用or，用它來連接時會索引失效。

2.10 所有的字段都適合創建索引嗎？

參考答案

不是。

下列幾種情況，是不適合創建索引的：

1. 頻繁更新的字段不適合建立索引；
2. where條件中用不到的字段不適合建立索引；
3. 數據比較少的表不需要建索引；
4. 數據重復且分布比較均勻的的字段不適合建索引，例如性別、真假值；
5. 參與列計算的列不適合建索引。

2.11 說一說索引的實現原理

參考答案

在MySQL中，索引是在存儲引擎層實現的，不同存儲引擎對索引的實現方式是不同的，下面我們探討一下MyISAM和InnoDB兩個存儲引擎的索引實現方式。

MyISAM索引實現：

MyISAM引擎使用B+Tree作為索引結構，葉節點的data域存放的是數據記錄的地址，MyISAM索引的原理圖如下。這里假設表一共有三列，假設我們以Col1為主鍵，則上圖是一個MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件僅僅保存數據記錄的地址。在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重復。

如果我們在Col2上建立一個輔助索引，則此索引的結構如下圖所示。同樣也是一顆B+Tree，data域保存數據記錄的地址。因此，MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址，讀取相應數據記錄。

InnoDB索引實現：

雖然InnoDB也使用B+Tree作為索引結構，但具體實現方式卻與MyISAM截然不同。

第一個重大區別是InnoDB的數據文件本身就是索引文件。從上文知道，MyISAM索引文件和數據文件是分離的，索引文件僅保存數據記錄的地址。而在InnoDB中，表數據文件本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點data域保存了完整的數據記錄。這個索引的key是數據表的主鍵，因此InnoDB表數據文件本身就是主索引。

下圖是InnoDB主索引（同時也是數據文件）的示意圖，可以看到葉節點包含了完整的數據記錄。這種索引叫做聚集索引。因為InnoDB的數據文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含字段作為主鍵，這個字段長度為6個字節，類型為長整形。

第二個與MyISAM索引的不同是InnoDB的輔助索引data域存儲相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。下圖為定義在Col3上的一個輔助索引。這里以英文字符的ASCII碼作為比較準則。聚集索引這種實現方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。

了解不同存儲引擎的索引實現方式對于正確使用和優化索引都非常有幫助，例如知道了InnoDB的索引實現后，就很容易明白為什么不建議使用過長的字段作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，用非單調的字段作為主鍵在InnoDB中不是個好主意，因為InnoDB數據文件本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時數據文件為了維持B+Tree的特性而頻繁的分裂調整，十分低效，而使用自增字段作為主鍵則是一個很好的選擇。

2.12 介紹一下數據庫索引的重構過程

參考答案

什么時候需要重建索引呢？

1. 表上頻繁發生update,delete操作；
2. 表上發生了alter table …move操作（move操作導致了rowid變化）。

怎么判斷索引是否應該重建？

1. 一般看索引是否傾斜的嚴重，是否浪費了空間，對索引進行結構分析：

   analyze index index_name validate structure;
   

2. 在相同的session中查詢index_stats表：

   select height,DEL_LF_ROWS/LF_ROWS from index_stats;
   

   當查詢的height>=4（索引的深度，即從根到葉節點的高度）或DEL_LF_ROWS/LF_ROWS>0.2的情況下，就應該考慮重建該索引。

如何重建索引？

• drop原索引，然后再創建索引：

  drop index index_name;
  create index index_name on table_name (index_column);
  

  這種方式相當耗時，一般不建議使用。

• 直接重建索引：

  alter index indexname rebuild;
  alter index indexname rebuild online;
  

  此方法較快，建議使用。

rebuild是快速重建索引的一種有效的辦法，因為它是一種使用現有索引項來重建新索引的方法。如果重建索引時有其他用戶在對這個表操作，盡量使用帶online參數來最大限度的減少索引重建時將會出現的任何加鎖問題。由于新舊索引在建立時同時存在，因此，使用這種重建方法需要有額外的磁盤空間可供臨時使用，當索引建完后把老索引刪除，如果沒有成功，也不會影響原來的索引。利用這種辦法可以用來將一個索引移到新的表空間。

rebuild重建索引的過程：

1. Rebuild以index fast full scan或table full scan方式（采用那種方式取決于cost）讀取原索引中的數據來構建一個新的索引，重建過程中有排序操作，rebuild online執行表掃描獲取數據，重建過程中有排序的操作；
2. Rebuild會阻塞DML操作，rebuild online不會阻塞DML操作；
3. rebuild online時系統會產生一個SYS_JOURNAL_xxx的IOT類型的系統臨時日志表，所有rebuild online時索引的變化都記錄在這個表中，當新的索引創建完成后，把這個表的記錄維護到新的索引中去，然后drop掉舊的索引，rebuild online就完成了。

重建索引過程中的注意事項：

1. 執行rebuild操作時，需要檢查表空間是否足夠；
2. 雖然說rebuild online操作允許DML操作，但還是建議在業務不繁忙時間段進行；
3. Rebuild操作會產生大量Redo Log；

2.13 MySQL的索引為什么用B+樹？

參考答案

B+樹由B樹和索引順序訪問方法演化而來，它是為磁盤或其他直接存取輔助設備設計的一種平衡查找樹，在B+樹中，所有記錄節點都是按鍵值的大小順序存放在同一層的葉子節點，各葉子節點通過指針進行鏈接。如下圖：

B+樹索引在數據庫中的一個特點就是高扇出性，例如在InnoDB存儲引擎中，每個頁的大小為16KB。在數據庫中，B+樹的高度一般都在2～4層，這意味著查找某一鍵值最多只需要2到4次IO操作，這還不錯。因為現在一般的磁盤每秒至少可以做100次IO操作，2～4次的IO操作意味著查詢時間只需0.02～0.04秒。

2.14 聯合索引的存儲結構是什么，它的有效方式是什么？

參考答案

從本質上來說，聯合索引還是一棵B+樹，不同的是聯合索引的鍵值數量不是1，而是大于等于2，參考下圖。另外，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用，所以使用聯合索引時遵循最左前綴集合。

2.15 MySQL的Hash索引和B樹索引有什么區別？

參考答案

hash索引底層就是hash表，進行查找時，調用一次hash函數就可以獲取到相應的鍵值，之后進行回表查詢獲得實際數據。B+樹底層實現是多路平衡查找樹，對于每一次的查詢都是從根節點出發，查找到葉子節點方可以獲得所查鍵值，然后根據查詢判斷是否需要回表查詢數據。它們有以下的不同：

• hash索引進行等值查詢更快(一般情況下)，但是卻無法進行范圍查詢。因為在hash索引中經過hash函數建立索引之后，索引的順序與原順序無法保持一致，不能支持范圍查詢。而B+樹的的所有節點皆遵循(左節點小于父節點，右節點大于父節點，多叉樹也類似)，天然支持范圍。
• hash索引不支持使用索引進行排序，原理同上。
• hash索引不支持模糊查詢以及多列索引的最左前綴匹配，原理也是因為hash函數的不可預測。
• hash索引任何時候都避免不了回表查詢數據，而B+樹在符合某些條件(聚簇索引，覆蓋索引等)的時候可以只通過索引完成查詢。
• hash索引雖然在等值查詢上較快，但是不穩定，性能不可預測，當某個鍵值存在大量重復的時候，發生hash碰撞，此時效率可能極差。而B+樹的查詢效率比較穩定，對于所有的查詢都是從根節點到葉子節點，且樹的高度較低。

因此，在大多數情況下，直接選擇B+樹索引可以獲得穩定且較好的查詢速度。而不需要使用hash索引。

2.16 聚簇索引和非聚簇索引有什么區別？

參考答案

在InnoDB存儲引擎中，可以將B+樹索引分為聚簇索引和輔助索引（非聚簇索引）。無論是何種索引，每個頁的大小都為16KB，且不能更改。

聚簇索引是根據主鍵創建的一棵B+樹，聚簇索引的葉子節點存放了表中的所有記錄。輔助索引是根據索引鍵創建的一棵B+樹，與聚簇索引不同的是，其葉子節點僅存放索引鍵值，以及該索引鍵值指向的主鍵。也就是說，如果通過輔助索引來查找數據，那么當找到輔助索引的葉子節點后，很有可能還需要根據主鍵值查找聚簇索引來得到數據，這種查找方式又被稱為書簽查找。因為輔助索引不包含行記錄的所有數據，這就意味著每頁可以存放更多的鍵值，因此其高度一般都要小于聚簇索引。

2.17 什么是聯合索引？

參考答案

聯合索引是指對表上的多個列進行索引，聯合索引的創建方法與單個索引創建的方法一樣，不同之處僅在于有多個索引列。從本質上來說，聯合索引還是一棵B+樹，不同的是聯合索引的鍵值數量不是1，而是大于等于2，參考下圖。另外，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用，所以使用聯合索引時遵循最左前綴集合。

2.18 select in語句中如何使用索引？

參考答案

索引是否起作用，主要取決于字段類型：

• 如果字段類型為字符串，需要給in查詢中的數值與字符串值都需要添加引號，索引才能起作用。
• 如果字段類型為int，則in查詢中的值不需要添加引號，索引也會起作用。

IN的字段，在聯合索引中，按以上方法，也會起作用。

2.19 模糊查詢語句中如何使用索引？

參考答案

在MySQL中模糊查詢 mobile like ‘%8765’，這種情況是不能使用 mobile 上的索引的，那么如果需要根據手機號碼后四位進行模糊查詢，可以用一下方法進行改造。

我們可以加入冗余列（MySQL5.7之后加入了虛擬列，使用虛擬列更合適，思路相同），比如 mobile_reverse，內部存儲為 mobile 的倒敘文本，如 mobile為17312345678，那么 mobile_reverse 存儲 87654321371，為 mobile_reverse 列建立索引，查詢中使用語句 mobile_reverse like reverse(’%5678’) 即可。

reverse 是 MySQL 中的反轉函數，這條語句相當于 mobile_reverse like ‘8765%’ ，這種語句是可以使用索引的。

3. 事務

3.1 說一說你對數據庫事務的了解

參考答案

事務可由一條非常簡單的SQL語句組成，也可以由一組復雜的SQL語句組成。在事務中的操作，要么都執行修改，要么都不執行，這就是事務的目的，也是事務模型區別于文件系統的重要特征之一。

事務需遵循ACID四個特性：

• A（atomicity），原子性。原子性指整個數據庫事務是不可分割的工作單位。只有使事務中所有的數據庫操作都執行成功，整個事務的執行才算成功。事務中任何一個SQL語句執行失敗，那么已經執行成功的SQL語句也必須撤銷，數據庫狀態應該退回到執行事務前的狀態。
• C（consistency），一致性。一致性指事務將數據庫從一種狀態轉變為另一種一致的狀態。在事務開始之前和事務結束以后，數據庫的完整性約束沒有被破壞。
• I（isolation），隔離性。事務的隔離性要求每個讀寫事務的對象與其他事務的操作對象能相互分離，即該事務提交前對其他事務都不可見，這通常使用鎖來實現。
• D（durability） ，持久性。事務一旦提交，其結果就是永久性的，即使發生宕機等故障，數據庫也能將數據恢復。持久性保證的是事務系統的高可靠性，而不是高可用性。

事務可以分為以下幾種類型：

• 扁平事務：是事務類型中最簡單的一種，而在實際生產環境中，這可能是使用最為頻繁的事務。在扁平事務中，所有操作都處于同一層次，其由BEGIN WORK開始，由COMMIT WORK或ROLLBACK WORK結束。處于之間的操作是原子的，要么都執行，要么都回滾。
• 帶有保存點的扁平事務：除了支持扁平事務支持的操作外，允許在事務執行過程中回滾到同一事務中較早的一個狀態，這是因為可能某些事務在執行過程中出現的錯誤并不會對所有的操作都無效，放棄整個事務不合乎要求，開銷也太大。保存點（savepoint）用來通知系統應該記住事務當前的狀態，以便以后發生錯誤時，事務能回到該狀態。
• 鏈事務：可視為保存點模式的一個變種。鏈事務的思想是：在提交一個事務時，釋放不需要的數據對象，將必要的處理上下文隱式地傳給下一個要開始的事務。注意，提交事務操作和開始下一個事務操作將合并為一個原子操作。這意味著下一個事務將看到上一個事務的結果，就好像在一個事務中進行的。
• 嵌套事務：是一個層次結構框架。有一個頂層事務（top-level transaction）控制著各個層次的事務。頂層事務之下嵌套的事務被稱為子事務（subtransaction），其控制每一個局部的變換。
• 分布式事務：通常是一個在分布式環境下運行的扁平事務，因此需要根據數據所在位置訪問網絡中的不同節點。對于分布式事務，同樣需要滿足ACID特性，要么都發生，要么都失效。

對于MySQL的InnoDB存儲引擎來說，它支持扁平事務、帶有保存點的扁平事務、鏈事務、分布式事務。對于嵌套事務，MySQL數據庫并不是原生的，因此對于有并行事務需求的用戶來說MySQL就無能為力了，但是用戶可以通過帶有保存點的事務來模擬串行的嵌套事務。

3.2 事務有哪幾種類型，它們之間有什么區別？

參考答案

事務可以分為以下幾種類型：

• 扁平事務：是事務類型中最簡單的一種，而在實際生產環境中，這可能是使用最為頻繁的事務。在扁平事務中，所有操作都處于同一層次，其由BEGIN WORK開始，由COMMIT WORK或ROLLBACK WORK結束。處于之間的操作是原子的，要么都執行，要么都回滾。
• 帶有保存點的扁平事務：除了支持扁平事務支持的操作外，允許在事務執行過程中回滾到同一事務中較早的一個狀態，這是因為可能某些事務在執行過程中出現的錯誤并不會對所有的操作都無效，放棄整個事務不合乎要求，開銷也太大。保存點（savepoint）用來通知系統應該記住事務當前的狀態，以便以后發生錯誤時，事務能回到該狀態。
• 鏈事務：可視為保存點模式的一個變種。鏈事務的思想是：在提交一個事務時，釋放不需要的數據對象，將必要的處理上下文隱式地傳給下一個要開始的事務。注意，提交事務操作和開始下一個事務操作將合并為一個原子操作。這意味著下一個事務將看到上一個事務的結果，就好像在一個事務中進行的。
• 嵌套事務：是一個層次結構框架。有一個頂層事務（top-level transaction）控制著各個層次的事務。頂層事務之下嵌套的事務被稱為子事務（subtransaction），其控制每一個局部的變換。
• 分布式事務：通常是一個在分布式環境下運行的扁平事務，因此需要根據數據所在位置訪問網絡中的不同節點。對于分布式事務，同樣需要滿足ACID特性，要么都發生，要么都失效。

對于MySQL的InnoDB存儲引擎來說，它支持扁平事務、帶有保存點的扁平事務、鏈事務、分布式事務。對于嵌套事務，MySQL數據庫并不是原生的，因此對于有并行事務需求的用戶來說MySQL就無能為力了，但是用戶可以通過帶有保存點的事務來模擬串行的嵌套事務。

3.3 MySQL的ACID特性分別是怎么實現的？

參考答案

原子性實現原理：

實現原子性的關鍵，是當事務回滾時能夠撤銷所有已經成功執行的sql語句。InnoDB實現回滾靠的是undo log，當事務對數據庫進行修改時，InnoDB會生成對應的undo log。如果事務執行失敗或調用了rollback，導致事務需要回滾，便可以利用undo log中的信息將數據回滾到修改之前的樣子。

undo log屬于邏輯日志，它記錄的是sql執行相關的信息。當發生回滾時，InnoDB會根據undo log的內容做與之前相反的工作。對于insert，回滾時會執行delete。對于delete，回滾時會執行insert。對于update，回滾時則會執行相反的update，把數據改回去。

持久性實現原理：

InnoDB作為MySQL的存儲引擎，數據是存放在磁盤中的，但如果每次讀寫數據都需要磁盤IO，效率會很低。為此，InnoDB提供了緩存(Buffer Pool)，Buffer Pool中包含了磁盤中部分數據頁的映射，作為訪問數據庫的緩沖。當從數據庫讀取數據時，會首先從Buffer Pool中讀取，如果Buffer Pool中沒有，則從磁盤讀取后放入Buffer Pool。當向數據庫寫入數據時，會首先寫入Buffer Pool，Buffer Pool中修改的數據會定期刷新到磁盤中（這一過程稱為刷臟）。

Buffer Pool的使用大大提高了讀寫數據的效率，但是也帶了新的問題：如果MySQL宕機，而此時Buffer Pool中修改的數據還沒有刷新到磁盤，就會導致數據的丟失，事務的持久性無法保證。

于是，redo log被引入來解決這個問題。當數據修改時，除了修改Buffer Pool中的數據，還會在redo log記錄這次操作。當事務提交時，會調用fsync接口對redo log進行刷盤。如果MySQL宕機，重啟時可以讀取redo log中的數據，對數據庫進行恢復。redo log采用的是WAL（Write-ahead logging，預寫式日志），所有修改先寫入日志，再更新到Buffer Pool，保證了數據不會因MySQL宕機而丟失，從而滿足了持久性要求。

既然redo log也需要在事務提交時將日志寫入磁盤，為什么它比直接將Buffer Pool中修改的數據寫入磁盤(即刷臟)要快呢？主要有以下兩方面的原因：

• 刷臟是隨機IO，因為每次修改的數據位置隨機，但寫redo log是追加操作，屬于順序IO。
• 刷臟是以數據頁（Page）為單位的，MySQL默認頁大小是16KB，一個Page上一個小修改都要整頁寫入。而redo log中只包含真正需要寫入的部分，無效IO大大減少。

隔離性實現原理：

隔離性追求的是并發情形下事務之間互不干擾。簡單起見，我們主要考慮最簡單的讀操作和寫操作(加鎖讀等特殊讀操作會特殊說明)，那么隔離性的探討，主要可以分為兩個方面。

第一方面，(一個事務)寫操作對(另一個事務)寫操作的影響：鎖機制保證隔離性。

隔離性要求同一時刻只能有一個事務對數據進行寫操作，InnoDB通過鎖機制來保證這一點。鎖機制的基本原理可以概括為：事務在修改數據之前，需要先獲得相應的鎖。獲得鎖之后，事務便可以修改數據。該事務操作期間，這部分數據是鎖定的，其他事務如果需要修改數據，需要等待當前事務提交或回滾后釋放鎖。

按照粒度，鎖可以分為表鎖、行鎖以及其他位于二者之間的鎖。表鎖在操作數據時會鎖定整張表，并發性能較差。行鎖則只鎖定需要操作的數據，并發性能好。但是由于加鎖本身需要消耗資源，因此在鎖定數據較多情況下使用表鎖可以節省大量資源。MySQL中不同的存儲引擎支持的鎖是不一樣的，例如MyIsam只支持表鎖，而InnoDB同時支持表鎖和行鎖，且出于性能考慮，絕大多數情況下使用的都是行鎖。

第二方面，(一個事務)寫操作對(另一個事務)讀操作的影響：MVCC保證隔離性。

InnoDB默認的隔離級別是RR（REPEATABLE READ），RR解決臟讀、不可重復讀、幻讀等問題，使用的是MVCC。MVCC全稱Multi-Version Concurrency Control，即多版本的并發控制協議。它最大的優點是讀不加鎖，因此讀寫不沖突，并發性能好。InnoDB實現MVCC，多個版本的數據可以共存，主要基于以下技術及數據結構：

1. 隱藏列：InnoDB中每行數據都有隱藏列，隱藏列中包含了本行數據的事務id、指向undo log的指針等。
2. 基于undo log的版本鏈：每行數據的隱藏列中包含了指向undo log的指針，而每條undo log也會指向更早版本的undo log，從而形成一條版本鏈。
3. ReadView：通過隱藏列和版本鏈，MySQL可以將數據恢復到指定版本。但是具體要恢復到哪個版本，則需要根據ReadView來確定。所謂ReadView，是指事務（記做事務A）在某一時刻給整個事務系統（trx_sys）打快照，之后再進行讀操作時，會將讀取到的數據中的事務id與trx_sys快照比較，從而判斷數據對該ReadView是否可見，即對事務A是否可見。

一致性實現原理：

可以說，一致性是事務追求的最終目標。前面提到的原子性、持久性和隔離性，都是為了保證數據庫狀態的一致性。此外，除了數據庫層面的保障，一致性的實現也需要應用層面進行保障。實現一致性的措施包括：

• 保證原子性、持久性和隔離性，如果這些特性無法保證，事務的一致性也無法保證。
• 數據庫本身提供保障，例如不允許向整形列插入字符串值、字符串長度不能超過列的限制等。
• 應用層面進行保障，例如如果轉賬操作只扣除轉賬者的余額，而沒有增加接收者的余額，無論數據庫實現的多么完美，也無法保證狀態的一致。

3.4 談談MySQL的事務隔離級別

參考答案

SQL 標準定義了四種隔離級別，這四種隔離級別分別是：

• 讀未提交（READ UNCOMMITTED）；
• 讀提交 （READ COMMITTED）；
• 可重復讀 （REPEATABLE READ）；
• 串行化 （SERIALIZABLE）。

事務隔離是為了解決臟讀、不可重復讀、幻讀問題，下表展示了 4 種隔離級別對這三個問題的解決程度：

隔離級別	臟讀	不可重復讀	幻讀
READ UNCOMMITTED	可能	可能	可能
READ COMMITTED	不可能	可能	可能
REPEATABLE READ	不可能	不可能	可能
SERIALIZABLE	不可能	不可能	不可能

上述4種隔離級別MySQL都支持，并且InnoDB存儲引擎默認的支持隔離級別是REPEATABLE READ，但是與標準SQL不同的是，InnoDB存儲引擎在REPEATABLE READ事務隔離級別下，使用Next-Key Lock的鎖算法，因此避免了幻讀的產生。所以，InnoDB存儲引擎在默認的事務隔離級別下已經能完全保證事務的隔離性要求，即達到SQL標準的SERIALIZABLE隔離級別。

擴展閱讀

并發情況下，讀操作可能存在的三類問題：

1. 臟讀：當前事務(A)中可以讀到其他事務(B)未提交的數據（臟數據），這種現象是臟讀。
2. 不可重復讀：在事務A中先后兩次讀取同一個數據，兩次讀取的結果不一樣，這種現象稱為不可重復讀。臟讀與不可重復讀的區別在于：前者讀到的是其他事務未提交的數據，后者讀到的是其他事務已提交的數據。
3. 幻讀：在事務A中按照某個條件先后兩次查詢數據庫，兩次查詢結果的條數不同，這種現象稱為幻讀。不可重復讀與幻讀的區別可以通俗的理解為：前者是數據變了，后者是數據的行數變了。

3.5 MySQL的事務隔離級別是怎么實現的？

參考答案

READ UNCOMMITTED：

它是性能最好、也最野蠻的方式，因為它壓根兒就不加鎖，所以根本談不上什么隔離效果，可以理解為沒有隔離。

SERIALIZABLE：

讀的時候加共享鎖，其他事務可以并發讀，但是不能寫。寫的時候加排它鎖，其他事務不能并發寫也不能并發讀。

REPEATABLE READ & READ COMMITTED：

為了解決不可重復讀，MySQL 采用了 MVVC (多版本并發控制) 的方式。

我們在數據庫表中看到的一行記錄可能實際上有多個版本，每個版本的記錄除了有數據本身外，還要有一個表示版本的字段，記為 row trx_id，而這個字段就是使其產生的事務的 id，事務 ID 記為 transaction id，它在事務開始的時候向事務系統申請，按時間先后順序遞增。

如下圖，一行記錄現在有 3 個版本，每一個版本都記錄這使其產生的事務 ID，比如事務A的transaction id 是100，那么版本1的row trx_id 就是 100，同理版本2和版本3。

可重復讀是在事務開始的時候生成一個當前事務全局性的快照，而讀提交則是每次執行語句的時候都重新生成一次快照。對于一個快照來說，它能夠讀到那些版本數據，要遵循以下規則：

1. 當前事務內的更新，可以讀到；
2. 版本未提交，不能讀到；
3. 版本已提交，但是卻在快照創建后提交的，不能讀到；
4. 版本已提交，且是在快照創建前提交的，可以讀到。

再強調一次，兩者主要的區別就是在快照的創建上，可重復讀僅在事務開始是創建一次，而讀提交每次執行語句的時候都要重新創建一次。

MySQL 已經在可重復讀隔離級別下解決了幻讀的問題，用的是間隙鎖。MySQL 把行鎖和間隙鎖合并在一起，解決了并發寫和幻讀的問題，這個鎖叫做 Next-Key鎖。

假設現在表中有兩條記錄，并且 age 字段已經添加了索引，兩條記錄 age 的值分別為 10 和 30。此時，在數據庫中會為索引維護一套B+樹，用來快速定位行記錄。B+索引樹是有序的，所以會把這張表的索引分割成幾個區間。

此時，在數據庫中會為索引維護一套B+樹，用來快速定位行記錄。B+索引樹是有序的，所以會把這張表的索引分割成幾個區間。如圖所示，分成了3 個區間，在這3個區間是可以加間隙鎖的。

之后，我用下面的兩個事務演示一下加鎖過程。

在事務A提交之前，事務B的插入操作只能等待，這就是間隙鎖起得作用。當事務A執行update user set name='風箏2號’ where age = 10; 的時候，由于條件 where age = 10 ，數據庫不僅在 age =10 的行上添加了行鎖，而且在這條記錄的兩邊，也就是(負無窮,10]、(10,30]這兩個區間加了間隙鎖，從而導致事務B插入操作無法完成，只能等待事務A提交。不僅插入 age = 10 的記錄需要等待事務A提交，age<10、10<age<30 的記錄頁無法完成，而大于等于30的記錄則不受影響，這足以解決幻讀問題了。

這是有索引的情況，如果 age 不是索引列，那么數據庫會為整個表加上間隙鎖。所以，如果是沒有索引的話，不管 age 是否大于等于30，都要等待事務A提交才可以成功插入。

3.6 事務可以嵌套嗎？

參考答案

可以，因為嵌套事務也是眾多事務分類中的一種，它是一個層次結構框架。有一個頂層事務控制著各個層次的事務，頂層事務之下嵌套的事務被稱為子事務，它控制每一個局部的變換。

需要注意的是，MySQL數據庫不支持嵌套事務。

3.7 如何實現可重復讀？

參考答案

為了實現可重復讀，MySQL 采用了 MVVC (多版本并發控制) 的方式。

我們在數據庫表中看到的一行記錄可能實際上有多個版本，每個版本的記錄除了有數據本身外，還要有一個表示版本的字段，記為 row trx_id，而這個字段就是使其產生的事務的 id，事務 ID 記為 transaction id，它在事務開始的時候向事務系統申請，按時間先后順序遞增。

如下圖，一行記錄現在有 3 個版本，每一個版本都記錄這使其產生的事務 ID，比如事務A的transaction id 是100，那么版本1的row trx_id 就是 100，同理版本2和版本3。

可重復讀是在事務開始的時候生成一個當前事務全局性的快照。對于一個快照來說，它能夠讀到那些版本數據，要遵循以下規則：

1. 當前事務內的更新，可以讀到；
2. 版本未提交，不能讀到；
3. 版本已提交，但是卻在快照創建后提交的，不能讀到；
4. 版本已提交，且是在快照創建前提交的，可以讀到。

3.8 如何解決幻讀問題？

參考答案

MySQL 已經在可重復讀隔離級別下解決了幻讀的問題，用的是間隙鎖。MySQL 把行鎖和間隙鎖合并在一起，解決了并發寫和幻讀的問題，這個鎖叫做 Next-Key鎖。

假設現在表中有兩條記錄，并且 age 字段已經添加了索引，兩條記錄 age 的值分別為 10 和 30。此時，在數據庫中會為索引維護一套B+樹，用來快速定位行記錄。B+索引樹是有序的，所以會把這張表的索引分割成幾個區間。

此時，在數據庫中會為索引維護一套B+樹，用來快速定位行記錄。B+索引樹是有序的，所以會把這張表的索引分割成幾個區間。如圖所示，分成了3 個區間，在這3個區間是可以加間隙鎖的。

之后，我用下面的兩個事務演示一下加鎖過程。

在事務A提交之前，事務B的插入操作只能等待，這就是間隙鎖起得作用。當事務A執行update user set name='風箏2號’ where age = 10; 的時候，由于條件 where age = 10 ，數據庫不僅在 age =10 的行上添加了行鎖，而且在這條記錄的兩邊，也就是(負無窮,10]、(10,30]這兩個區間加了間隙鎖，從而導致事務B插入操作無法完成，只能等待事務A提交。不僅插入 age = 10 的記錄需要等待事務A提交，age<10、10<age<30 的記錄頁無法完成，而大于等于30的記錄則不受影響，這足以解決幻讀問題了。

這是有索引的情況，如果 age 不是索引列，那么數據庫會為整個表加上間隙鎖。所以，如果是沒有索引的話，不管 age 是否大于等于30，都要等待事務A提交才可以成功插入。

3.9 MySQL事務如何回滾？

參考答案

在MySQL默認的配置下，事務都是自動提交和回滾的。當顯示地開啟一個事務時，可以使用ROLLBACK語句進行回滾。該語句有兩種用法：

• ROLLBACK：要使用這個語句的最簡形式，只需發出ROLLBACK。同樣地，也可以寫為ROLLBACK WORK，但是二者幾乎是等價的。回滾會結束用戶的事務，并撤銷正在進行的所有未提交的修改。
• ROLLBACK TO [SAVEPOINT] identifier ：這個語句與SAVEPOINT命令一起使用。可以把事務回滾到標記點，而不回滾在此標記點之前的任何工作。

4. 鎖

4.1 了解數據庫的鎖嗎？

參考答案

鎖是數據庫系統區別于文件系統的一個關鍵特性，鎖機制用于管理對共享資源的并發訪問。下面我們以MySQL數據庫的InnoDB引擎為例，來說明鎖的一些特點。

鎖的類型：

InnoDB存儲引擎實現了如下兩種標準的行級鎖：

• 共享鎖（S Lock），允許事務讀一行數據。
• 排他鎖（X Lock），允許事務刪除或更新一行數據。

如果一個事務T1已經獲得了行r的共享鎖，那么另外的事務T2可以立即獲得行r的共享鎖，因為讀取并沒有改變行r的數據，稱這種情況為鎖兼容。但若有其他的事務T3想獲得行r的排他鎖，則其必須等待事務T1、T2釋放行r上的共享鎖，這種情況稱為鎖不兼容。下圖顯示了共享鎖和排他鎖的兼容性，可以發現X鎖與任何的鎖都不兼容，而S鎖僅和S鎖兼容。需要特別注意的是，S和X鎖都是行鎖，兼容是指對同一記錄（row）鎖的兼容性情況。

鎖的粒度：

InnoDB存儲引擎支持多粒度鎖定，這種鎖定允許事務在行級上的鎖和表級上的鎖同時存在。為了支持在不同粒度上進行加鎖操作，InnoDB存儲引擎支持一種額外的鎖方式，稱之為意向鎖。意向鎖是將鎖定的對象分為多個層次，意向鎖意味著事務希望在更細粒度上進行加鎖。

InnoDB存儲引擎支持意向鎖設計比較簡練，其意向鎖即為表級別的鎖。設計目的主要是為了在一個事務中揭示下一行將被請求的鎖類型。其支持兩種意向鎖：

• 意向共享鎖（IS Lock），事務想要獲得一張表中某幾行的共享鎖。
• 意向排他鎖（IX Lock），事務想要獲得一張表中某幾行的排他鎖。

由于InnoDB存儲引擎支持的是行級別的鎖，因此意向鎖其實不會阻塞除全表掃以外的任何請求。故表級意向鎖與行級鎖的兼容性如下圖所示。

鎖的算法：

InnoDB存儲引擎有3種行鎖的算法，其分別是：

• Record Lock：單個行記錄上的鎖。
• Gap Lock：間隙鎖，鎖定一個范圍，但不包含記錄本身。
• Next-Key Lock∶Gap Lock+Record Lock，鎖定一個范圍，并且鎖定記錄本身。

Record Lock總是會去鎖住索引記錄，如果InnoDB存儲引擎表在建立的時候沒有設置任何一個索引，那么這時InnoDB存儲引擎會使用隱式的主鍵來進行鎖定。Next-Key Lock是結合了Gap Lock和Record Lock的一種鎖定算法，在Next-Key Lock算法下，InnoDB對于行的查詢都是采用這種鎖定算法。采用Next-Key Lock的鎖定技術稱為Next-Key Locking，其設計的目的是為了解決Phantom Problem（幻讀）。而利用這種鎖定技術，鎖定的不是單個值，而是一個范圍，是謂詞鎖（predict lock）的一種改進。

關于死鎖：

死鎖是指兩個或兩個以上的事務在執行過程中，因爭奪鎖資源而造成的一種互相等待的現象。若無外力作用，事務都將無法推進下去。

解決死鎖問題最簡單的一種方法是超時，即當兩個事務互相等待時，當一個等待時間超過設置的某一閾值時，其中一個事務進行回滾，另一個等待的事務就能繼續進行。

除了超時機制，當前數據庫還都普遍采用wait-for graph（等待圖）的方式來進行死鎖檢測。較之超時的解決方案，這是一種更為主動的死鎖檢測方式。InnoDB存儲引擎也采用的這種方式。wait-for graph要求數據庫保存以下兩種信息：

• 鎖的信息鏈表；
• 事務等待鏈表；

通過上述鏈表可以構造出一張圖，而在這個圖中若存在回路，就代表存在死鎖，因此資源間相互發生等待。這是一種較為主動的死鎖檢測機制，在每個事務請求鎖并發生等待時都會判斷是否存在回路，若存在則有死鎖，通常來說InnoDB存儲引擎選擇回滾undo量最小的事務。

鎖的升級：

鎖升級（Lock Escalation）是指將當前鎖的粒度降低。舉例來說，數據庫可以把一個表的1000個行鎖升級為一個頁鎖，或者將頁鎖升級為表鎖。

InnoDB存儲引擎不存在鎖升級的問題。因為其不是根據每個記錄來產生行鎖的，相反，其根據每個事務訪問的每個頁對鎖進行管理的，采用的是位圖的方式。因此不管一個事務鎖住頁中一個記錄還是多個記錄，其開銷通常都是一致的。

4.2 介紹一下間隙鎖

參考答案

InnoDB存儲引擎有3種行鎖的算法，間隙鎖（Gap Lock）是其中之一。間隙鎖用于鎖定一個范圍，但不包含記錄本身。它的作用是為了阻止多個事務將記錄插入到同一范圍內，而這會導致幻讀問題的產生。

4.3 InnoDB中行級鎖是怎么實現的？

參考答案

InnoDB行級鎖是通過給索引上的索引項加鎖來實現的。只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖。

當表中鎖定其中的某幾行時，不同的事務可以使用不同的索引鎖定不同的行。另外，不論使用主鍵索引、唯一索引還是普通索引，InnoDB都會使用行鎖來對數據加鎖。

4.4 數據庫在什么情況下會發生死鎖？

參考答案

死鎖是指兩個或兩個以上的事務在執行過程中，因爭奪鎖資源而造成的一種互相等待的現象。若無外力作用，事務都將無法推進下去。下圖演示了死鎖的一種經典的情況，即A等待B、B等待A，這種死鎖問題被稱為AB-BA死鎖。

4.5 說說數據庫死鎖的解決辦法

參考答案

解決死鎖問題最簡單的一種方法是超時，即當兩個事務互相等待時，當一個等待時間超過設置的某一閾值時，其中一個事務進行回滾，另一個等待的事務就能繼續進行。

除了超時機制，當前數據庫還都普遍采用wait-for graph（等待圖）的方式來進行死鎖檢測。較之超時的解決方案，這是一種更為主動的死鎖檢測方式。InnoDB存儲引擎也采用的這種方式。wait-for graph要求數據庫保存以下兩種信息：

• 鎖的信息鏈表；
• 事務等待鏈表；

通過上述鏈表可以構造出一張圖，而在這個圖中若存在回路，就代表存在死鎖，因此資源間相互發生等待。這是一種較為主動的死鎖檢測機制，在每個事務請求鎖并發生等待時都會判斷是否存在回路，若存在則有死鎖，通常來說InnoDB存儲引擎選擇回滾undo量最小的事務。

5. 優化

5.1 說一說你對數據庫優化的理解

參考答案

MySQL數據庫優化是多方面的，原則是減少系統的瓶頸，減少資源的占用，增加系統的反應速度。例如，通過優化文件系統，提高磁盤I\O的讀寫速度；通過優化操作系統調度策略，提高MySQL在高負荷情況下的負載能力；優化表結構、索引、查詢語句等使查詢響應更快。

針對查詢，我們可以通過使用索引、使用連接代替子查詢的方式來提高查詢速度。

針對慢查詢，我們可以通過分析慢查詢日志，來發現引起慢查詢的原因，從而有針對性的進行優化。

針對插入，我們可以通過禁用索引、禁用檢查等方式來提高插入速度，在插入之后再啟用索引和檢查。

針對數據庫結構，我們可以通過將字段很多的表拆分成多張表、增加中間表、增加冗余字段等方式進行優化。

5.2 該如何優化MySQL的查詢？

參考答案

使用索引：

如果查詢時沒有使用索引，查詢語句將掃描表中的所有記錄。在數據量大的情況下，這樣查詢的速度會很慢。如果使用索引進行查詢，查詢語句可以根據索引快速定位到待查詢記錄，從而減少查詢的記錄數，達到提高查詢速度的目的。

索引可以提高查詢的速度，但并不是使用帶有索引的字段查詢時索引都會起作用。有幾種特殊情況，在這些情況下有可能使用帶有索引的字段查詢時索引并沒有起作用。

1. 使用LIKE關鍵字的查詢語句

   在使用LIKE關鍵字進行查詢的查詢語句中，如果匹配字符串的第一個字符為“%”，索引不會起作用。只有“%”不在第一個位置，索引才會起作用。

2. 使用多列索引的查詢語句

   MySQL可以為多個字段創建索引。一個索引可以包括16個字段。對于多列索引，只有查詢條件中使用了這些字段中的第1個字段時索引才會被使用。

3. 使用OR關鍵字的查詢語句

   查詢語句的查詢條件中只有OR關鍵字，且OR前后的兩個條件中的列都是索引時，查詢中才使用索引。否則，查詢將不使用索引。

優化子查詢：

使用子查詢可以進行SELECT語句的嵌套查詢，即一個SELECT查詢的結果作為另一個SELECT語句的條件。子查詢可以一次性完成很多邏輯上需要多個步驟才能完成的SQL操作。

子查詢雖然可以使查詢語句很靈活，但執行效率不高。執行子查詢時，MySQL需要為內層查詢語句的查詢結果建立一個臨時表。然后外層查詢語句從臨時表中查詢記錄。查詢完畢后，再撤銷這些臨時表。因此，子查詢的速度會受到一定的影響。如果查詢的數據量比較大，這種影響就會隨之增大。

在MySQL中，可以使用連接（JOIN）查詢來替代子查詢。連接查詢不需要建立臨時表，其速度比子查詢要快，如果查詢中使用索引，性能會更好。

5.3 怎樣插入數據才能更高效？

參考答案

影響插入速度的主要是索引、唯一性校驗、一次插入記錄條數等。針對這些情況，可以分別進行優化。

對于MyISAM引擎的表，常見的優化方法如下：

1. 禁用索引

   對于非空表，插入記錄時，MySQL會根據表的索引對插入的記錄建立索引。如果插入大量數據，建立索引會降低插入記錄的速度。為了解決這種情況，可以在插入記錄之前禁用索引，數據插入完畢后再開啟索引。對于空表批量導入數據，則不需要進行此操作，因為MyISAM引擎的表是在導入數據之后才建立索引的。

2. 禁用唯一性檢查

   插入數據時，MySQL會對插入的記錄進行唯一性校驗。這種唯一性校驗也會降低插入記錄的速度。為了降低這種情況對查詢速度的影響，可以在插入記錄之前禁用唯一性檢查，等到記錄插入完畢后再開啟。

3. 使用批量插入

   插入多條記錄時，可以使用一條INSERT語句插入一條記錄，也可以使用一條INSERT語句插入多條記錄。使用一條INSERT語句插入多條記錄的情形如下，而這種方式的插入速度更快。

   INSERT INTO fruits VALUES
   ('x1', '101', 'mongo2', '5.7'),
   ('x2', '101', 'mongo3', '5.7'),
   ('x3', '101', 'mongo4', '5.7');
   

4. 使用LOAD DATA INFILE批量導入

   當需要批量導入數據時，如果能用LOAD DATA INFILE語句，就盡量使用。因為LOAD DATA INFILE語句導入數據的速度比INSERT語句快。

對于InnoDB引擎的表，常見的優化方法如下：

1. 禁用唯一性檢查

   插入數據之前執行set unique_checks=0來禁止對唯一索引的檢查，數據導入完成之后再運行set unique_checks=1。這個和MyISAM引擎的使用方法一樣。

2. 禁用外鍵檢查

   插入數據之前執行禁止對外鍵的檢查，數據插入完成之后再恢復對外鍵的檢查。

3. 禁用自動提交

   插入數據之前禁止事務的自動提交，數據導入完成之后，執行恢復自動提交操作。

5.4 表中包含幾千萬條數據該怎么辦？

參考答案

建議按照如下順序進行優化：

1. 優化SQL和索引；
2. 增加緩存，如memcached、redis；
3. 讀寫分離，可以采用主從復制，也可以采用主主復制；
4. 使用MySQL自帶的分區表，這對應用是透明的，無需改代碼，但SQL語句是要針對分區表做優化的；
5. 做垂直拆分，即根據模塊的耦合度，將一個大的系統分為多個小的系統；
6. 做水平拆分，要選擇一個合理的sharding key，為了有好的查詢效率，表結構也要改動，做一定的冗余，應用也要改，sql中盡量帶sharding key，將數據定位到限定的表上去查，而不是掃描全部的表。

5.5 MySQL的慢查詢優化有了解嗎？

參考答案

優化MySQL的慢查詢，可以按照如下步驟進行：

開啟慢查詢日志：

MySQL中慢查詢日志默認是關閉的，可以通過配置文件my.ini或者my.cnf中的log-slow-queries選項打開，也可以在MySQL服務啟動的時候使用--log-slow-queries[=file_name]啟動慢查詢日志。

啟動慢查詢日志時，需要在my.ini或者my.cnf文件中配置long_query_time選項指定記錄閾值，如果某條查詢語句的查詢時間超過了這個值，這個查詢過程將被記錄到慢查詢日志文件中。

分析慢查詢日志：

直接分析mysql慢查詢日志，利用explain關鍵字可以模擬優化器執行SQL查詢語句，來分析sql慢查詢語句。

常見慢查詢優化：

1. 索引沒起作用的情況

   • 在使用LIKE關鍵字進行查詢的查詢語句中，如果匹配字符串的第一個字符為“%”，索引不會起作用。只有“%”不在第一個位置，索引才會起作用。
   • MySQL可以為多個字段創建索引。一個索引可以包括16個字段。對于多列索引，只有查詢條件中使用了這些字段中的第1個字段時索引才會被使用。
   • 查詢語句的查詢條件中只有OR關鍵字，且OR前后的兩個條件中的列都是索引時，查詢中才使用索引。否則，查詢將不使用索引。

2. 優化數據庫結構

   • 對于字段比較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來形成新表。因為當一個表的數據量很大時，會由于使用頻率低的字段的存在而變慢。
   • 對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率。通過建立中間表，把需要經常聯合查詢的數據插入到中間表中，然后將原來的聯合查詢改為對中間表的查詢，以此來提高查詢效率。

3. 分解關聯查詢

   很多高性能的應用都會對關聯查詢進行分解，就是可以對每一個表進行一次單表查詢，然后將查詢結果在應用程序中進行關聯，很多場景下這樣會更高效。

4. 優化LIMIT分頁

   當偏移量非常大的時候，例如可能是limit 10000,20這樣的查詢，這是mysql需要查詢10020條然后只返回最后20條，前面的10000條記錄都將被舍棄，這樣的代價很高。優化此類查詢的一個最簡單的方法是盡可能的使用索引覆蓋掃描，而不是查詢所有的列。然后根據需要做一次關聯操作再返回所需的列。對于偏移量很大的時候這樣做的效率會得到很大提升。

5.6 說一說你對explain的了解

參考答案

MySQL中提供了EXPLAIN語句和DESCRIBE語句，用來分析查詢語句，EXPLAIN語句的基本語法如下：

EXPLAIN [EXTENDED] SELECT select_options

使用EXTENED關鍵字，EXPLAIN語句將產生附加信息。執行該語句，可以分析EXPLAIN后面SELECT語句的執行情況，并且能夠分析出所查詢表的一些特征。下面對查詢結果進行解釋：

• id：SELECT識別符。這是SELECT的查詢序列號。
• select_type：表示SELECT語句的類型。
• table：表示查詢的表。
• type：表示表的連接類型。
• possible_keys：給出了MySQL在搜索數據記錄時可選用的各個索引。
• key：是MySQL實際選用的索引。
• key_len：給出索引按字節計算的長度，key_len數值越小，表示越快。
• ref：給出了關聯關系中另一個數據表里的數據列名。
• rows：是MySQL在執行這個查詢時預計會從這個數據表里讀出的數據行的個數。
• Extra：提供了與關聯操作有關的信息。

擴展閱讀

DESCRIBE語句的使用方法與EXPLAIN語句是一樣的，分析結果也是一樣的，并且可以縮寫成DESC。。DESCRIBE語句的語法形式如下：

DESCRIBE SELECT select_options

5.7 explain關注什么？

參考答案

重點要關注如下幾列：

列名	備注
type	本次查詢表聯接類型，從這里可以看到本次查詢大概的效率。
key	最終選擇的索引，如果沒有索引的話，本次查詢效率通常很差。
key_len	本次查詢用于結果過濾的索引實際長度。
rows	預計需要掃描的記錄數，預計需要掃描的記錄數越小越好。
Extra	額外附加信息，主要確認是否出現 Using filesort、Using temporary 這兩種情況。

其中，type包含以下幾種結果，從上之下依次是最差到最好：

類型	備注
ALL	執行full table scan，這是最差的一種方式。
index	執行full index scan，并且可以通過索引完成結果掃描并且直接從索引中取的想要的結果數據，也就是可以避免回表，比ALL略好，因為索引文件通常比全部數據要來的小。
range	利用索引進行范圍查詢，比index略好。
index_subquery	子查詢中可以用到索引。
unique_subquery	子查詢中可以用到唯一索引，效率比 index_subquery 更高些。
index_merge	可以利用index merge特性用到多個索引，提高查詢效率。
ref_or_null	表連接類型是ref，但進行掃描的索引列中可能包含NULL值。
fulltext	全文檢索。
ref	基于索引的等值查詢，或者表間等值連接。
eq_ref	表連接時基于主鍵或非NULL的唯一索引完成掃描，比ref略好。
const	基于主鍵或唯一索引唯一值查詢，最多返回一條結果，比eq_ref略好。
system	查詢對象表只有一行數據，這是最好的情況。

另外，Extra列需要注意以下的幾種情況：

關鍵字	備注
Using filesort	將用外部排序而不是按照索引順序排列結果，數據較少時從內存排序，否則需要在磁盤完成排序，代價非常高，需要添加合適的索引。
Using temporary	需要創建一個臨時表來存儲結果，這通常發生在對沒有索引的列進行GROUP BY時，或者ORDER BY里的列不都在索引里，需要添加合適的索引。
Using index	表示MySQL使用覆蓋索引避免全表掃描，不需要再到表中進行二次查找數據，這是比較好的結果之一。注意不要和type中的index類型混淆。
Using where	通常是進行了全表/全索引掃描后再用WHERE子句完成結果過濾，需要添加合適的索引。
Impossible WHERE	對Where子句判斷的結果總是false而不能選擇任何數據，例如where 1=0，無需過多關注。
Select tables optimized away	使用某些聚合函數來訪問存在索引的某個字段時，優化器會通過索引直接一次定位到所需要的數據行完成整個查詢，例如MIN()\MAX()，這種也是比較好的結果之一。

6. 其他

6.1 介紹一下數據庫設計的三大范式

參考答案

目前關系數據庫有六種范式，一般來說，數據庫只需滿足第三范式(3NF）就行了。

第一范式（1NF）：

是指在關系模型中，對于添加的一個規范要求，所有的域都應該是原子性的，即數據庫表的每一列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。

即實體中的某個屬性有多個值時，必須拆分為不同的屬性。在符合第一范式表中的每個域值只能是實體的一個屬性或一個屬性的一部分。簡而言之，第一范式就是無重復的域。

第二范式（2NF）：

在1NF的基礎上，非碼屬性必須完全依賴于候選碼（在1NF基礎上消除非主屬性對主碼的部分函數依賴）。

第二范式是在第一范式的基礎上建立起來的，即滿足第二范式必須先滿足第一范式。第二范式要求數據庫表中的每個實例或記錄必須可以被唯一地區分。選取一個能區分每個實體的屬性或屬性組，作為實體的唯一標識。

例如在員工表中的身份證號碼即可實現每個一員工的區分，該身份證號碼即為候選鍵，任何一個候選鍵都可以被選作主鍵。在找不到候選鍵時，可額外增加屬性以實現區分，如果在員工關系中，沒有對其身份證號進行存儲，而姓名可能會在數據庫運行的某個時間重復，無法區分出實體時，設計辟如ID等不重復的編號以實現區分，被添加的編號或ID選作主鍵。

第三范式（3NF）：

在2NF基礎上，任何非主屬性不依賴于其它非主屬性（在2NF基礎上消除傳遞依賴）。

第三范式是第二范式的一個子集，即滿足第三范式必須滿足第二范式。簡而言之，第三范式要求一個關系中不包含已在其它關系已包含的非主關鍵字信息。

例如，存在一個部門信息表，其中每個部門有部門編號（dept_id）、部門名稱、部門簡介等信息。那么在員工信息表中列出部門編號后就不能再將部門名稱、部門簡介等與部門有關的信息再加入員工信息表中。如果不存在部門信息表，則根據第三范式（3NF）也應該構建它，否則就會有大量的數據冗余。

6.2 說一說你對MySQL引擎的了解

參考答案

MySQL提供了多個不同的存儲引擎，包括處理事務安全表的引擎和處理非事務安全表的引擎。在MySQL中，不需要在整個服務器中使用同一種存儲引擎，針對具體的要求，可以對每一個表使用不同的存儲引擎。MySQL 8.0支持的存儲引擎有InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE等。其中，最常用的引擎是InnoDB和MyISAM。

InnoDB存儲引擎：

InnoDB是事務型數據庫的首選引擎，支持事務安全表（ACID），支持行鎖定和外鍵。MySQL 5.5.5之后，InnoDB作為默認存儲引擎，主要特性如下：

1. InnoDB給MySQL提供了具有提交、回滾和崩潰恢復能力的事務安全（ACID兼容）存儲引擎。InnoDB鎖定在行級并且也在SELECT語句中提供一個類似Oracle的非鎖定讀。這些功能增加了多用戶部署和性能。在SQL查詢中，可以自由地將InnoDB類型的表與其他MySQL表的類型混合起來，甚至在同一個查詢中也可以混合。
2. InnoDB是為處理巨大數據量的最大性能設計。它的CPU效率可能是任何其他基于磁盤的關系數據庫引擎所不能匹敵的。
3. InnoDB存儲引擎完全與MySQL服務器整合，為在主內存中緩存數據和索引而維持它自己的緩沖池。InnoDB將它的表和索引存在一個邏輯表空間中，表空間可以包含數個文件（或原始磁盤分區）。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統上。
4. InnoDB支持外鍵完整性約束（FOREIGN KEY）。存儲表中的數據時，每張表的存儲都按主鍵順序存放，如果沒有顯示在表定義時指定主鍵，InnoDB會為每一行生成一個6B的ROWID，并以此作為主鍵。
5. InnoDB被用在眾多需要高性能的大型數據庫站點上。InnoDB不創建目錄，使用InnoDB時，MySQL將在數據目錄下創建一個名為ibdata1的10MB大小的自動擴展數據文件，以及兩個名為ib_logfile0和ib_logfile1的5MB大小的日志文件。

MyISAM存儲引擎：

MyISAM基于ISAM存儲引擎，并對其進行擴展。它是在Web、數據倉儲和其他應用環境下最常使用的存儲引擎之一。MyISAM擁有較高的插入、查詢速度，但不支持事務。MyISAM的主要特性如下：

1. 在支持大文件（達63位文件長度）的文件系統和操作系統上被支持。
2. 當把刪除和更新及插入操作混合使用的時候，動態尺寸的行產生更少碎片。這要通過合并相鄰被刪除的塊以及若下一個塊被刪除則擴展到下一塊來自動完成。
3. 每個MyISAM表最大的索引數是64，這可以通過重新編譯來改變。每個索引最大的列數是16個。
4. 最大的鍵長度是1000B，這也可以通過編譯來改變。對于鍵長度超過250B的情況，一個超過1024B的鍵將被用上。
5. BLOB和TEXT列可以被索引。
6. NULL值被允許在索引的列中，這個值占每個鍵的0~1個字節。
7. 所有數字鍵值以高字節優先被存儲，以允許一個更高的索引壓縮。
8. 每個表一個AUTO_INCREMENT列的內部處理。MyISAM為INSERT和UPDATE操作自動更新這一列，這使得AUTO_INCREMENT列更快（至少10%）。在序列頂的值被刪除之后就不能再利用。
9. 可以把數據文件和索引文件放在不同目錄。
10. 每個字符列可以有不同的字符集。
11. 有VARCHAR的表可以固定或動態記錄長度。
12. VARCHAR和CHAR列可以多達64KB。

6.3 說一說你對redo log、undo log、binlog的了解

參考答案

binlog（Binary Log）：

二進制日志文件就是常說的binlog。二進制日志記錄了MySQL所有修改數據庫的操作，然后以二進制的形式記錄在日志文件中，其中還包括每條語句所執行的時間和所消耗的資源，以及相關的事務信息。

默認情況下，二進制日志功能是開啟的，啟動時可以重新配置--log-bin[=file_name]選項，修改二進制日志存放的目錄和文件名稱。

redo log：

重做日志用來實現事務的持久性，即事務ACID中的D。它由兩部分組成：一是內存中的重做日志緩沖（redo log buffer），其是易失的；二是重做日志文件（redo log file），它是持久的。

InnoDB是事務的存儲引擎，它通過Force Log at Commit機制實現事務的持久性，即當事務提交（COMMIT）時，必須先將該事務的所有日志寫入到重做日志文件進行持久化，待事務的COMMIT操作完成才算完成。這里的日志是指重做日志，在InnoDB存儲引擎中，由兩部分組成，即redo log和undo log。

redo log用來保證事務的持久性，undo log用來幫助事務回滾及MVCC的功能。redo log基本上都是順序寫的，在數據庫運行時不需要對redo log的文件進行讀取操作。而undo log是需要進行隨機讀寫的。

undo log：

重做日志記錄了事務的行為，可以很好地通過其對頁進行“重做”操作。但是事務有時還需要進行回滾操作，這時就需要undo。因此在對數據庫進行修改時，InnoDB存儲引擎不但會產生redo，還會產生一定量的undo。這樣如果用戶執行的事務或語句由于某種原因失敗了，又或者用戶用一條ROLLBACK語句請求回滾，就可以利用這些undo信息將數據回滾到修改之前的樣子。

redo存放在重做日志文件中，與redo不同，undo存放在數據庫內部的一個特殊段（segment）中，這個段稱為undo段（undo segment），undo段位于共享表空間內。

6.4 談談你對MVCC的了解

參考答案

InnoDB默認的隔離級別是RR（REPEATABLE READ），RR解決臟讀、不可重復讀、幻讀等問題，使用的是MVCC。MVCC全稱Multi-Version Concurrency Control，即多版本的并發控制協議。它最大的優點是讀不加鎖，因此讀寫不沖突，并發性能好。InnoDB實現MVCC，多個版本的數據可以共存，主要基于以下技術及數據結構：

1. 隱藏列：InnoDB中每行數據都有隱藏列，隱藏列中包含了本行數據的事務id、指向undo log的指針等。
2. 基于undo log的版本鏈：每行數據的隱藏列中包含了指向undo log的指針，而每條undo log也會指向更早版本的undo log，從而形成一條版本鏈。
3. ReadView：通過隱藏列和版本鏈，MySQL可以將數據恢復到指定版本。但是具體要恢復到哪個版本，則需要根據ReadView來確定。所謂ReadView，是指事務（記做事務A）在某一時刻給整個事務系統（trx_sys）打快照，之后再進行讀操作時，會將讀取到的數據中的事務id與trx_sys快照比較，從而判斷數據對該ReadView是否可見，即對事務A是否可見。

6.5 MySQL主從同步是如何實現的？

參考答案

復制（replication）是MySQL數據庫提供的一種高可用高性能的解決方案，一般用來建立大型的應用。總體來說，replication的工作原理分為以下3個步驟：

1. 主服務器（master）把數據更改記錄到二進制日志（binlog）中。
2. 從服務器（slave）把主服務器的二進制日志復制到自己的中繼日志（relay log）中。
3. 從服務器重做中繼日志中的日志，把更改應用到自己的數據庫上，以達到數據的最終一致性。

復制的工作原理并不復雜，其實就是一個完全備份加上二進制日志備份的還原。不同的是這個二進制日志的還原操作基本上實時在進行中。這里特別需要注意的是，復制不是完全實時地進行同步，而是異步實時。這中間存在主從服務器之間的執行延時，如果主服務器的壓力很大，則可能導致主從服務器延時較大。復制的工作原理如下圖所示，其中從服務器有2個線程，一個是I/O線程，負責讀取主服務器的二進制日志，并將其保存為中繼日志；另一個是SQL線程，復制執行中繼日志。

修改時，InnoDB存儲引擎不但會產生redo，還會產生一定量的undo。這樣如果用戶執行的事務或語句由于某種原因失敗了，又或者用戶用一條ROLLBACK語句請求回滾，就可以利用這些undo信息將數據回滾到修改之前的樣子。

redo存放在重做日志文件中，與redo不同，undo存放在數據庫內部的一個特殊段（segment）中，這個段稱為undo段（undo segment），undo段位于共享表空間內。

6.4 談談你對MVCC的了解

參考答案

InnoDB默認的隔離級別是RR（REPEATABLE READ），RR解決臟讀、不可重復讀、幻讀等問題，使用的是MVCC。MVCC全稱Multi-Version Concurrency Control，即多版本的并發控制協議。它最大的優點是讀不加鎖，因此讀寫不沖突，并發性能好。InnoDB實現MVCC，多個版本的數據可以共存，主要基于以下技術及數據結構：

1. 隱藏列：InnoDB中每行數據都有隱藏列，隱藏列中包含了本行數據的事務id、指向undo log的指針等。
2. 基于undo log的版本鏈：每行數據的隱藏列中包含了指向undo log的指針，而每條undo log也會指向更早版本的undo log，從而形成一條版本鏈。
3. ReadView：通過隱藏列和版本鏈，MySQL可以將數據恢復到指定版本。但是具體要恢復到哪個版本，則需要根據ReadView來確定。所謂ReadView，是指事務（記做事務A）在某一時刻給整個事務系統（trx_sys）打快照，之后再進行讀操作時，會將讀取到的數據中的事務id與trx_sys快照比較，從而判斷數據對該ReadView是否可見，即對事務A是否可見。

6.5 MySQL主從同步是如何實現的？

參考答案

復制（replication）是MySQL數據庫提供的一種高可用高性能的解決方案，一般用來建立大型的應用。總體來說，replication的工作原理分為以下3個步驟：

1. 主服務器（master）把數據更改記錄到二進制日志（binlog）中。
2. 從服務器（slave）把主服務器的二進制日志復制到自己的中繼日志（relay log）中。
3. 從服務器重做中繼日志中的日志，把更改應用到自己的數據庫上，以達到數據的最終一致性。

復制的工作原理并不復雜，其實就是一個完全備份加上二進制日志備份的還原。不同的是這個二進制日志的還原操作基本上實時在進行中。這里特別需要注意的是，復制不是完全實時地進行同步，而是異步實時。這中間存在主從服務器之間的執行延時，如果主服務器的壓力很大，則可能導致主從服務器延時較大。復制的工作原理如下圖所示，其中從服務器有2個線程，一個是I/O線程，負責讀取主服務器的二進制日志，并將其保存為中繼日志；另一個是SQL線程，復制執行中繼日志。