一、MySql面試-索引篇

1.索引的優點

為什麼要建立索引？這是因為，建立索引可以大大提高系統的查詢效能。

• 第一、通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。
• 第二、可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。
• 第三、可以加速表和表之間的連線，特別是在實現資料的參考完整性方面特別有意義。
• 第四、在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的 時間。
• 第五、通過使用索引，可以在查詢的過程中，使用查詢優化器，提高系統的效能。

2.索引的缺點

• 第一、建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加。
• 第二、索引需要佔物理空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物 理空間。如果要建立聚簇索引，那麼需要的空間就會更大。
• 第三、當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降 低了資料的維護速度。

捷豹

3.什麼樣的欄位適合建立索引

索引是建立在資料庫表中的某些列的上面。因此，在建立索引的時候，應該仔細考慮 在哪些列上可以建立索引，在哪些列上不能建立索引。 一般來說，應該在具備下述特性的 列上建立索引：

• 第一、在經常需要搜尋的列上，可以加快搜尋的速度；
• 第二、在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構；
• 第三、在經常用在連線的列上，這些列主要是一些外來鍵，可以加快連線的速度；
• 第四、在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範 圍是連續的；
• 第五、在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的 排序，加快排序查詢時間；
• 第六、在經常使用在 WHERE 子句中的列上面建立索引，加快條件的判斷速度。

建立索引，一般按照 select 的 where 條件來建立，比如： select 的條件是 where f1 and f2，那麼如果我們在欄位 f1 或欄位 f2 上建立索引是沒有用的，只有在欄位 f1 和 f2 上同時 建立索引才有用等。

4.什麼樣的欄位不需要建立索引

• 第一，對於那些在查詢中很少使用或者參考的列不應該建立索引。這是因為，既然這些 列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反 而降低了系統的維護速度和增大了空間需求。
• 第二，對於那些只有很少資料值的列也不應該增加索引。這是因為，由於這些列的取值 很少，例如人事表的性別列，在查詢的結果中，結果集的資料行佔了表中資料行的很大比例， 即需要在表中搜尋的資料行的比例很大。增加索引，並不能明顯加快檢索速度。
• 第三，對於那些定義為 text, image 和 bit 資料型別的列不應該增加索引。這是因為，這 些列的資料量要麼相當大，要麼取值很少。
• 第四，當修改效能遠遠大於檢索效能時，不應該建立索引。這是因為，修改效能和檢索 效能是互相矛盾的。當增加索引時，會提高檢索效能，但是會降低修改效能。當減少索引時，
  會提高修改效能，降低檢索效能。因此，當修改效能遠遠大於檢索效能時，不應該建立索引。

5.索引的分類

單列索引

• 普通索引：MySQL中基本索引型別，沒有什麼限制，允許在定義索引的列中插⼊重複值和空值，
  純粹為了查詢資料更快⼀點。 add index
• 唯⼀索引：索引列中的值必須是唯⼀的，但是允許為空值. add unique index
• 主鍵索引：是⼀種特殊的唯⼀索引，不允許有空值。 pk

組合索引

在表中的多個欄位組合上建立的索引 add index(col1,col2…)
組合索引的使⽤，需要遵循最左字首原則。
⼀般情況下，建議使⽤組合索引代替單列索引（主鍵索引除外，具體原因後⾯知識點講解）。

上面兩種索引屬於B-Tree索引大型別

Hash索引

雜湊索引基於雜湊表實現，只有精確匹配索引所有列的查詢才有效。對於每一行資料，儲存引擎都會對所有的索引列計算一個雜湊碼，雜湊索引將所有的雜湊碼儲存在索引中，同時在雜湊表中儲存指向每個資料行的指標。如果多個行的雜湊值相同，索引會以連結串列的方式存放多個記錄指標到同一個雜湊條目中。

Memory儲存引擎支援Hash索引

全⽂索引

• 只有在MyISAM引擎、InnoDB（5.6以後）上才能使⽤，⽽且只能在CHAR,VARCHAR,TEXT型別欄位上
  使⽤全⽂索引。 fulltext
• 優先順序最⾼ 先執⾏ 不會執⾏其他索引
• 儲存引擎 決定執⾏⼀個索引

空間索引

不做介紹，⼀般使⽤不到。

6.建立索引語句

• 單列索引之普通索引

CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length)) ;

• 單列索引之唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length)) ;
alter table table_name add unique index index_name(column);

• 單列索引之全文索引

CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
alter table table_name add fulltext index_name(column)

• 組合索引

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;

7.刪除索引

DROP INDEX index_name ON table

8.檢視索引

SHOW INDEX FROM table_name \G

9.索引原理分析

索引的儲存結構

• 索引是在儲存引擎中實現的，所以不同的儲存引擎使用不同的索引
• MyISAM和InnoDB儲存引擎只支援B+Tree索引
• MEMORY/HEAP儲存引擎支援Hash和BTree索引

B樹和B+樹

詳情參考：https://blog.csdn.net/hao65103940/article/details/89032538

• B-Tree圖示
  

• B+Tree圖示
  

B+Tree和B-Tree的不同

• 所有葉子節點之間都有一個鏈指標。
• 非葉子節點只儲存鍵值資訊。資料記錄都存放在葉子節點中。

為什麼選用B+Tree來做索引的儲存結構

• 索引的結構組織要儘量減少查詢過程中磁碟I/O的存取次數
• B-Tree結構的資料可以讓系統高效的找到資料所在的磁碟塊
• B-Tree相對於AVLTree縮減了節點個數，減少了磁碟IO存取次數
• B+Tree是在B-Tree基礎上的一種優化，使其更適合實現外儲存索引結構，它和B-Tree最大的區別就是非葉子節點不儲存資料，只儲存鍵值，這樣可以大大加大每個節點儲存的key值數量，降低B+Tree的高度。

10.聚集索引和非聚集索引

聚集索引和非聚集索引是運用B-Tree作為資料結構索引的兩種不同實現形式，實現在MyISAN和InnoDB兩個儲存引擎上

詳細參考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

非聚集索引（MyISAM）

• B+樹葉⼦節點只會儲存資料⾏（資料⽂件）的指標，簡單來說資料和索引不在⼀起，就是⾮聚集
  索引。
• ⾮聚集索引包含主鍵索引和輔助索引都會儲存指標的值

主鍵索引

這裡設表一共有三列，假設我們以Col1為主鍵，則上圖是一個MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引檔案僅僅儲存資料記錄的地址。

輔助索引

在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主鍵索引要求key是唯一的，而輔助索引的key可以重複。如果我們在Col2上建立一個輔助索引，則此索引的結構如下圖所示：

聚集索引（InnoDB）

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

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

可以看到葉節點包含了完整的資料記錄。這種索引叫做聚集索引。因為InnoDB的資料檔案本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含欄位作為主鍵，這個欄位長度為6個位元組，型別為長整形。

第二個與MyISAM索引的不同是InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。例如下圖在Col3上建立一個輔助索引：

聚集索引這種實現方式使得按主鍵的搜尋十分高效，但是輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。例如：

select * from user where name='Alice' # 回表查詢 檢索兩次 ⾮主鍵索引 --- pk---索引--->資料
select id,name from user where name='Alice' # 不需要回表 在輔助索引樹上就可以查詢到了 覆蓋索引

知道了InnoDB的索引實現後，就很容易明白為什麼不建議使用過長的欄位作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，用非單調的欄位作為主鍵在InnoDB中不是個好主意，因為InnoDB資料檔案本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時資料檔案為了維持B+Tree的特性而頻繁的分裂調整，十分低效，而使用自增欄位作為主鍵則是一個很好的選擇。

聚簇索引的優點：

• 可以把相關資料儲存在一起，最大限度地提高了I/O密集型應用的效能。
• 聚簇索引將索引和資料儲存在同一個B-Tree中，因此資料訪問更快。
• 使用覆蓋索引掃描的查詢可以直接使用葉節點中的主鍵值。

聚簇索引的缺點：

• 插入速度嚴重依賴於插入順序。按照主鍵的順序插入是載入資料到InnoDB表中速度最快的方式。
• 更新聚簇索引列的代價很高，因為會強制InnoDB將每個被更新的行移動到新的位置。
• 基於聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能面臨“頁分裂”的問題。
• 聚簇索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由於頁分裂導致資料儲存不連續的時候。

11.為什麼要使用組合索引

最左字首原則

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3'）

以上建立的組合索引相當於建立了3個索引：

• col1
• col1、col2
• col1、col2、col3

即只從最左邊的欄位開始組合

比如當查詢條件為col2、col3時，就不用到上面建立的索引

原因

為了節省mysql索引儲存空間以及提升搜尋效能，可建⽴組合索引（能使⽤組合索引就不使⽤單列索引）

⼀顆索引樹上建立3個索引比三顆索引樹上分別建立1個索引更省空間

注意

從左向右匹配直到遇到範圍查詢 > < between like也會使索引匹配終止

比如：建⽴組合索引 (a,b,c,d)，然後查詢
where a=1 and b=1 and c>3 and d=1
此時到c>3停⽌了 所以d⽤不到索引了

解決：建立（a,b,d,c）的組合索引

12.覆蓋索引

• 如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱之為“覆蓋索引”，索引不會覆蓋所有的列。
• 覆蓋索引必須要儲存索引列的值，因此MySQL只能使用B-Tree索引做覆蓋索引，而且MySQL只能在索引中執行最左字首匹配的LIKE比較，而不能執行以萬用字元開頭的LIKE查詢。

13.檢視執行計劃

MySQL 提供了⼀個 EXPLAIN 命令, 它可以對 SELECT 語句的執⾏計劃進⾏分析, 並輸出 SELECT 執⾏的
詳細資訊, 以供開發⼈員針對性優化.

EXPLAIN 命令⽤法⼗分簡單, 在 SELECT 語句前加上 explain 就可以了, 例如:

欄位的詳細資訊解釋這裡略

14.索引失效的情況

• 隱式型別轉換，常見情況是在 SQL 的 WHERE 條件中欄位型別為字串，其值為數值，如果沒有加引號那麼 MySQL 不會使用索引。
• 如果條件中 OR 只有部分列使用了索引，索引會失效。
• 執行 LIKE 操作時，最左匹配會被轉換為比較操作，但如果以萬用字元開頭，儲存引擎就無法做比較，。索引失效
• 如果查詢中的列不是獨立的，則 MySQL 不會使用索引。獨立的列是指索引列不能是表示式的一部分，也不能是函式的引數。
• 對於多個範圍條件查詢，MySQL 無法使用第一個範圍列後面的其他索引列，對於多個等值查詢則沒有這種限制。
• 如果 MySQL 判斷全表掃描比使用索引查詢更快，則不會使用索引。

二、MySql面試-儲存引擎篇

1.MyISAM和InnoDB引擎的區別

• InnoDB支援事務，MyISAM不支援事務
• InnoDB支援外來鍵，MyISAM不支援外來鍵
• InnoDB是聚集索引，對於利用主鍵查詢效率高；MyISAM是非聚集索引，索引儲存的是資料檔案的指標
• InnoDB不儲存整個表的具體行數，執行select count(*)會全表掃描；MyISAM用一個變數儲存了整個表的行數，執行上述語句只需要讀出該變數的值即可，速度很快
• InnoDB擁有崩潰恢復能力；MyISAM崩潰後無法恢復
• InnoDB支援行鎖，提高了高併發訪問的效能；MyISAM只支援表鎖，插入和更新時會鎖住整個表，效率低

2.MyISAM是否一無是處

• 從查詢的角度來說，InnoDB沒有MyISAM的單條查詢速度高，MyISAM採用Hash儲存回行得到資料的查詢過程，單MyISAM對於範圍查詢不是很友好。
• MyISAM對於表內的總數查詢，維護了單獨的資料
• 對於只讀或表比較小，可以忍受系統崩潰導致資料無法恢復的問題，可以使用MyISAM

三、MySql面試-基礎和優化篇

1.查詢優化方法

• 避免全表掃描：考慮在 WHERE 和 ORDER BY 涉及的列上建立索引，IN 和 NOT IN 也要慎用，儘量用 BETWEEN 取代。
• 優化 COUNT：某些業務不要求完全精確的 COUNT 值，此時可以使用近似值來代替，EXPLAIN 估算的行數就是一個不錯的近似值。
• 避免子查詢：在 MySQL5.5 及以下版本避免子查詢，因為執行器會先執行外部的 SQL 再執行內部的 SQL，可以用關聯查詢代替。
• 禁止排序：當查詢使用 GROUP BY 時，結果集預設會按照分組欄位排序，如果不關心順序，可以使用 ORDER BY NULL 禁止排序。
• 優化分頁：從上一次取資料的位置開始掃描，避免使用 OFFSET。
• 優化 UNION：MySQL 通過建立並填充臨時表的方式來執行 UNION 查詢，除非確實需要消除重複的行，否則使用 UNION ALL，如果沒有 ALL 關鍵字，MySQL 會給臨時表加上 DISTINCT 選項，對整個臨時表的資料做唯一性檢查，代價非常高。
• 使用使用者自定義變數：使用者自定義變數是一個用來儲存內容的臨時容器，在連線 MySQL 的整個過程中都存在，可以在任何可以使用表示式的地方使用自定義變數，避免重複查詢剛剛更新過的資料。

2.delete、drop、truncate的區別？

• delete 可以刪除部分資料也可以刪除全部資料，和truncate 一樣只刪除資料而不刪除表的結構；drop會刪除表的結構。
• delete 是 DML 操作，可以進行回滾；drop 和 truncate 是 DDL，不能進行回滾。
• 速度來說，一般 drop > truncate > delete。

3.MySQL 有哪些聚合函式？

① max 求最大值。② min 求最小值。③ count 統計數量。④ avg 求平均值。⑤ sum 求和。

4.varchar(100)和varchar(200)的區別

佔用記憶體空間大小肯定是不一致的，但是佔用我們磁碟的大小是一致的，我們儲存字串"abc"，完全是一樣的磁碟空間，但是對於varchar(100)來說，接收到的字串長度太長了就會報錯的。後面的數字代表可儲存的位元組數。

四、MySql面試-事務和鎖篇

1.介紹下MySql中的鎖

按照鎖的粒度

• 全域性鎖:鎖的是整個database。由MySQL的SQL layer層實現的
• 表級鎖:鎖的是某個table。由MySQL的SQL layer層實現的
• 行級鎖:鎖的是某⾏資料，也可能鎖定⾏之間的間隙。由某些儲存引擎實現，⽐如InnoDB。

表級鎖和行級鎖的區別:

表級鎖：開銷小、加鎖塊；不會出現死鎖；鎖的粒度大，發生鎖衝突概率最高；併發度最低

行級鎖：開銷大、加鎖慢；會出現死鎖；鎖的粒度最小，發生鎖衝突概率最低；併發度最高

按照鎖的功能

• 共享讀鎖
• 排他寫鎖

按照鎖的實現方式

• 悲觀鎖
• 樂觀鎖

2.後設資料鎖

• MDL不需要顯式使⽤，在訪問⼀個表的時候會被⾃動加上。MDL的作⽤是，保證讀寫的正確性。你可
  以想象⼀下，如果⼀個查詢正在遍歷⼀個表中的資料，⽽執⾏期間另⼀個執行緒對這個表結構做變更，刪
  了⼀列，那麼查詢執行緒拿到的結果跟表結構對不上，肯定是不⾏的
• 在 MySQL 5.5 版本中引⼊了 MDL，當對⼀個表做增刪改查操作的時候，加 MDL 讀鎖；當要對
  表做結構變更操作的時候，加 MDL 寫鎖。

讀鎖之間不互斥，因此你可以有多個執行緒同時對⼀張表增刪改查。

讀寫鎖之間、寫鎖之間是互斥的，⽤來保證變更表結構操作的安全性。因此，如果有兩個執行緒要同
時給⼀個表加欄位，其中⼀個要等另⼀個執⾏完才能開始執⾏。

3.行級鎖

MySQL的⾏級鎖，是由儲存引擎來實現的，利⽤儲存引擎鎖住索引項來實現的。這⾥我們主要講解
InnoDB的⾏級鎖。

InnoDB的⾏級鎖，按照鎖定範圍來說，分為三種：

• 記錄鎖（Record Locks）:鎖定索引中⼀條記錄。 id=1
• 間隙鎖（Gap Locks）:要麼鎖住索引記錄中間的值，要麼鎖住第⼀個索引記錄前⾯的值或者最後⼀個索
  引記錄後⾯的值。
• Next-Key Locks:是索引記錄上的記錄鎖和在索引記錄之前的間隙鎖的組合。

InnoDB的⾏級鎖，按照功能來說，分為兩種：

• 共享鎖（S）：允許⼀個事務去讀⼀⾏，阻⽌其他事務獲得相同資料集的排他鎖。
• 排他鎖（X）：允許獲得排他鎖的事務更新資料，阻⽌其他事務取得相同資料集的共享讀鎖（不是讀）
  和排他寫鎖。

對於UPDATE、DELETE和INSERT語句，InnoDB會⾃動給涉及資料集加排他鎖（X)；

對於普通SELECT語句，InnoDB不會加任何鎖，事務可以通過以下語句顯示給記錄集加共享鎖或排他
鎖。

• 手動新增共享鎖

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

• 手動新增排他鎖

SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB⾏鎖是通過給索引上的索引項加鎖來實現的，因此InnoDB這種⾏鎖實現特點意味著：只
有通過索引條件檢索的資料，InnoDB才使⽤⾏級鎖，否則，InnoDB將使⽤表鎖！

間隙鎖

鎖加在不存在的空閒空間，可以是兩個索引記錄之間，也可能是第一個索引記錄之前或最後一個索引之後的空間。

當我們用範圍條件而不是相等條件檢索資料，並請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。

間隙鎖在InnoDB的唯一作用就是防止其它事務的插入操作，以此來達到防止幻讀的發生，所以間隙鎖不分什麼共享鎖與排它鎖。如果InnoDB掃描的是一個主鍵、或是一個唯一索引的話，那InnoDB只會採用行鎖方式來加鎖，而不會使用Next-Key Lock的方式，也就是說不會對索引之間的間隙加鎖，

很顯然，在使用範圍條件檢索並鎖定記錄時，InnoDB這種加鎖機制會阻塞符合條件範圍內鍵值的併發插入，這往往會造成嚴重的鎖等待。因此，在實際應用開發中，尤其是併發插入比較多的應用，我們要儘量優化業務邏輯，儘量使用相等條件來訪問更新資料，避免使用範圍條件；當然，對一條不存在的記錄加鎖，也會有間隙鎖的問題

4.MySql中的事務ACID

事務是一組原子性的 SQL 語句，當有任何一條語句因崩潰或其他原因無法執行時，所有語句都不會執行。事務內的語句要麼全部執行成功，要麼全部執行失敗。

• 原子性：一個事務在邏輯上是必須不可分割的最小單元，整個事務中的所有操作要麼全部成功，要麼全部失敗。
• 一致性：資料庫總是從一個一致性的狀態轉換到另一個一致性的狀態。
• 隔離性：針對併發事務而言，要隔離併發執行的多個事務之間的影響，資料庫提供了多種隔離級別。
• 永續性：一旦事務提交成功，其修改就會永久儲存到資料庫中，此時即使系統崩潰，修改的資料也不會丟失。

5.事務的隔離級別

• 未提交讀：事務中的修改即使沒有提交，對其他事務也是可見的。事務可以讀取其他事務修改完但未提交的資料，這種問題稱為髒讀。這個級別還存在不可重複讀和幻讀，很少使用。
• 讀已提交：多數資料庫的預設隔離級別，事務只能看見已提交事務的修改。存在不可重複讀，兩次執行同樣的查詢可能會得到不同結果。
• 可重複讀（MySQL預設的隔離級別）：解決了不可重複讀，保證同一個事務中多次讀取同樣的記錄結果一致，InnoDB 通過 MVCC 解決。但無法解決幻讀，幻讀指當某個事務在讀取某個範圍內的記錄時，會產生幻行。
• 可序列化：最高隔離級別，通過強制事務序列執行避免幻讀。在讀取的每一行資料上都加鎖，可能導致大量的超時和鎖爭用的問題。實際很少使用，只有非常需要確保資料一致性時考慮。

不可重複讀和幻讀的區別是：前者是指讀到了已經提交的事務的更改資料(修改或刪除)，後者是指讀到了其他已經提交事務的新增資料。

對於這兩種問題解決採用不同的辦法，防止讀到更改資料，只需對操作的資料新增行級鎖，防止操作中的資料發生變化;二防止讀到新增資料，往往需要新增表級鎖，將整張表鎖定，防止新增資料(oracle採用多版本資料的方式實現，mysql可採用間隙鎖防止幻讀)。