MySQL靈魂拷問：36題帶你面試通關

大家好，我是大彬~

今天給大家分享MySQL常考的面試題，看看你們能答對多少。

本期MySQL面試題的目錄如下：

事務的四大特性？
事務隔離級別有哪些？
索引
- 什麼是索引？
- 索引的優缺點？
- 索引的作用？
- 什麼情況下需要建索引？
- 什麼情況下不建索引？
- 索引的資料結構
- Hash索引和B+樹索引的區別？
- 為什麼B+樹比B樹更適合實現資料庫索引？
- 索引有什麼分類？
- 什麼是最左匹配原則？
- 什麼是聚集索引？
- 什麼是覆蓋索引？
- 索引的設計原則？
- 索引什麼時候會失效？
- 什麼是字首索引？
常見的儲存引擎有哪些？
MyISAM和InnoDB的區別？
MVCC 實現原理？
快照讀和當前讀
共享鎖和排他鎖
大表怎麼優化？
bin log/redo log/undo log
bin log和redo log有什麼區別？
講一下MySQL架構？
分庫分表
什麼是分割槽表？
分割槽表型別
查詢語句執行流程？
更新語句執行過程？
exist和in的區別？
truncate、delete與drop區別？
having和where的區別？
什麼是MySQL主從同步？
為什麼要做主從同步？
樂觀鎖和悲觀鎖是什麼？
用過processlist嗎？

事務的四大特性？

事務特性ACID：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、永續性（Durability）。

原子性是指事務包含的所有操作要麼全部成功，要麼全部失敗回滾。
一致性是指一個事務執行之前和執行之後都必須處於一致性狀態。比如a與b賬戶共有1000塊，兩人之間轉賬之後無論成功還是失敗，它們的賬戶總和還是1000。
隔離性。跟隔離級別相關，如read committed，一個事務只能讀到已經提交的修改。
永續性是指一個事務一旦被提交了，那麼對資料庫中的資料的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作。

事務隔離級別有哪些？

先了解下幾個概念：髒讀、不可重複讀、幻讀。

髒讀是指在一個事務處理過程裡讀取了另一個未提交的事務中的資料。
不可重複讀是指在對於資料庫中的某行記錄，一個事務範圍內多次查詢卻返回了不同的資料值，這是由於在查詢間隔，另一個事務修改了資料並提交了。
幻讀是當某個事務在讀取某個範圍內的記錄時，另外一個事務又在該範圍內插入了新的記錄，當之前的事務再次讀取該範圍的記錄時，會產生幻行，就像產生幻覺一樣，這就是發生了幻讀。

不可重複讀和髒讀的區別是，髒讀是某一事務讀取了另一個事務未提交的髒資料，而不可重複讀則是讀取了前一事務提交的資料。

幻讀和不可重複讀都是讀取了另一條已經提交的事務，不同的是不可重複讀的重點是修改，幻讀的重點在於新增或者刪除。

事務隔離就是為了解決上面提到的髒讀、不可重複讀、幻讀這幾個問題。

MySQL資料庫為我們提供的四種隔離級別：

Serializable (序列化)：通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。
Repeatable read (可重複讀)：MySQL的預設事務隔離級別，它確保同一事務的多個例項在併發讀取資料時，會看到同樣的資料行，解決了不可重複讀的問題。
Read committed (讀已提交)：一個事務只能看見已經提交事務所做的改變。可避免髒讀的發生。
Read uncommitted (讀未提交)：所有事務都可以看到其他未提交事務的執行結果。

檢視隔離級別：

select @@transaction_isolation;

設定隔離級別：

set session transaction isolation level read uncommitted;

索引

什麼是索引？

索引是儲存引擎用於提高資料庫表的訪問速度的一種資料結構。

索引的優缺點？

優點：

加快資料查詢的速度
為用來排序或者是分組的欄位新增索引，可以加快分組和排序的速度
加快表與表之間的連線

缺點：

建立索引需要佔用物理空間
會降低表的增刪改的效率，因為每次對錶記錄進行增刪改，需要進行動態維護索引，導致增刪改時間變長

索引的作用？

資料是儲存在磁碟上的，查詢資料時，如果沒有索引，會載入所有的資料到記憶體，依次進行檢索，讀取磁碟次數較多。有了索引，就不需要載入所有資料，因為B+樹的高度一般在2-4層，最多隻需要讀取2-4次磁碟，查詢速度大大提升。

什麼情況下需要建索引？

經常用於查詢的欄位
經常用於連線的欄位建立索引，可以加快連線的速度
經常需要排序的欄位建立索引，因為索引已經排好序，可以加快排序查詢速度

什麼情況下不建索引？

where條件中用不到的欄位不適合建立索引
表記錄較少
需要經常增刪改
參與列計算的列不適合建索引
區分度不高的欄位不適合建立索引，如性別等

索引的資料結構

索引的資料結構主要有B+樹和雜湊表，對應的索引分別為B+樹索引和雜湊索引。InnoDB引擎的索引型別有B+樹索引和雜湊索引，預設的索引型別為B+樹索引。

B+樹索引

B+ 樹是基於B 樹和葉子節點順序訪問指標進行實現，它具有B樹的平衡性，並且通過順序訪問指標來提高區間查詢的效能。

在 B+ 樹中，節點中的 key 從左到右遞增排列，如果某個指標的左右相鄰 key 分別是 key_i 和 key_i+1，則該指標指向節點的所有 key 大於等於 key_i 且小於等於 key_i+1。

進行查詢操作時，首先在根節點進行二分查詢，找到key所在的指標，然後遞迴地在指標所指向的節點進行查詢。直到查詢到葉子節點，然後在葉子節點上進行二分查詢，找出key所對應的資料項。

MySQL 資料庫使用最多的索引型別是BTREE索引，底層基於B+樹資料結構來實現。

mysql> show index from blog\G;
*************************** 1. row ***************************
        Table: blog
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: blog_id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

雜湊索引

雜湊索引是基於雜湊表實現的，對於每一行資料，儲存引擎會對索引列進行雜湊計算得到雜湊碼，並且雜湊演算法要儘量保證不同的列值計算出的雜湊碼值是不同的，將雜湊碼的值作為雜湊表的key值，將指向資料行的指標作為雜湊表的value值。這樣查詢一個資料的時間複雜度就是O(1)，一般多用於精確查詢。

Hash索引和B+樹索引的區別？

雜湊索引不支援排序，因為雜湊表是無序的。
雜湊索引不支援範圍查詢。
雜湊索引不支援模糊查詢及多列索引的最左字首匹配。
因為雜湊表中會存在雜湊衝突，所以雜湊索引的效能是不穩定的，而B+樹索引的效能是相對穩定的，每次查詢都是從根節點到葉子節點。

為什麼B+樹比B樹更適合實現資料庫索引？

由於B+樹的資料都儲存在葉子結點中，葉子結點均為索引，方便掃庫，只需要掃一遍葉子結點即可，但是B樹因為其分支結點同樣儲存著資料，我們要找到具體的資料，需要進行一次中序遍歷按序來掃，所以B+樹更加適合在區間查詢的情況，而在資料庫中基於範圍的查詢是非常頻繁的，所以通常B+樹用於資料庫索引。
B+樹的節點只儲存索引key值，具體資訊的地址存在於葉子節點的地址中。這就使以頁為單位的索引中可以存放更多的節點。減少更多的I/O支出。
B+樹的查詢效率更加穩定，任何關鍵字的查詢必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個資料的查詢效率相當。

索引有什麼分類？

1、主鍵索引：名為primary的唯一非空索引，不允許有空值。

2、唯一索引：索引列中的值必須是唯一的，但是允許為空值。唯一索引和主鍵索引的區別是：唯一約束的列可以為null且可以存在多個null值。唯一索引的用途：唯一標識資料庫表中的每條記錄，主要是用來防止資料重複插入。建立唯一索引的SQL語句如下：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

3、組合索引：在表中的多個欄位組合上建立的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用，使用組合索引時需遵循最左字首原則。

4、全文索引：只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR和TEXT型別欄位上使用全文索引。

什麼是最左匹配原則？

如果 SQL 語句中用到了組合索引中的最左邊的索引，那麼這條 SQL 語句就可以利用這個組合索引去進行匹配。當遇到範圍查詢(>、<、between、like)就會停止匹配，後面的欄位不會用到索引。

對(a,b,c)建立索引，查詢條件使用 a/ab/abc 會走索引，使用 bc 不會走索引。如果查詢條件為a = 1 and b > 2 and c = 3，那麼a、b個字兩段能用到索引，而c無法使用索引，因為b欄位是範圍查詢，導致後面的欄位無法使用索引。

如下圖，對(a, b) 建立索引，a 在索引樹中是全域性有序的，而 b 是全域性無序，區域性有序（當a相等時，會根據b進行排序）。

當a的值確定的時候，b是有序的。例如a = 1時，b值為1，2是有序的狀態。當執行a = 1 and b = 2時a和b欄位能用到索引。而對於查詢條件a < 4 and b = 2時，a欄位能用到索引，b欄位則用不到索引。因為a的值此時是一個範圍，不是固定的，在這個範圍內b的值不是有序的，因此b欄位無法使用索引。

什麼是聚集索引？

InnoDB使用表的主鍵構造主鍵索引樹，同時葉子節點中存放的即為整張表的記錄資料。聚集索引葉子節點的儲存是邏輯上連續的，使用雙向連結串列連線，葉子節點按照主鍵的順序排序，因此對於主鍵的排序查詢和範圍查詢速度比較快。

聚集索引的葉子節點就是整張表的行記錄。InnoDB 主鍵使用的是聚簇索引。聚集索引要比非聚集索引查詢效率高很多。

對於InnoDB來說，聚集索引一般是表中的主鍵索引，如果表中沒有顯示指定主鍵，則會選擇表中的第一個不允許為NULL的唯一索引。如果沒有主鍵也沒有合適的唯一索引，那麼InnoDB內部會生成一個隱藏的主鍵作為聚集索引，這個隱藏的主鍵長度為6個位元組，它的值會隨著資料的插入自增。

什麼是覆蓋索引？

select的資料列只用從索引中就能夠取得，不需要回表進行二次查詢，也就是說查詢列要被所使用的索引覆蓋。對於innodb表的二級索引，如果索引能覆蓋到查詢的列，那麼就可以避免對主鍵索引的二次查詢。

不是所有型別的索引都可以成為覆蓋索引。覆蓋索引要儲存索引列的值，而雜湊索引、全文索引不儲存索引列的值，所以MySQL使用b+樹索引做覆蓋索引。

對於使用了覆蓋索引的查詢，在查詢前面使用explain，輸出的extra列會顯示為using index。

比如user_like 使用者點贊表，組合索引為(user_id, blog_id)，user_id和blog_id都不為null。

explain select blog_id from user_like where user_id = 13;

explain結果的Extra列為Using index，查詢的列被索引覆蓋，並且where篩選條件符合最左字首原則，通過索引查詢就能直接找到符合條件的資料，不需要回表查詢資料。

explain select user_id from user_like where blog_id = 1;

explain結果的Extra列為Using where; Using index，查詢的列被索引覆蓋，where篩選條件不符合最左字首原則，無法通過索引查詢找到符合條件的資料，但可以通過索引掃描找到符合條件的資料，也不需要回表查詢資料。

索引的設計原則？

索引列的區分度越高，索引的效果越好。比如使用性別這種區分度很低的列作為索引，效果就會很差。
儘量使用短索引，對於較長的字串進行索引時應該指定一個較短的字首長度，因為較小的索引涉及到的磁碟I/O較少，查詢速度更快。
索引不是越多越好，每個索引都需要額外的物理空間，維護也需要花費時間。
利用最左字首原則。

索引什麼時候會失效？

導致索引失效的情況：

對於組合索引，不是使用組合索引最左邊的欄位，則不會使用索引
以%開頭的like查詢如%abc，無法使用索引；非%開頭的like查詢如abc%，相當於範圍查詢，會使用索引
查詢條件中列型別是字串，沒有使用引號，可能會因為型別不同發生隱式轉換，使索引失效
判斷索引列是否不等於某個值時
對索引列進行運算
查詢條件使用or連線，也會導致索引失效

什麼是字首索引？

有時需要在很長的字元列上建立索引，這會造成索引特別大且慢。使用字首索引可以避免這個問題。

字首索引是指對文字或者字串的前幾個字元建立索引，這樣索引的長度更短，查詢速度更快。

建立字首索引的關鍵在於選擇足夠長的字首以保證較高的索引選擇性。索引選擇性越高查詢效率就越高，因為選擇性高的索引可以讓MySQL在查詢時過濾掉更多的資料行。

建立字首索引的方式：

// email列建立字首索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

常見的儲存引擎有哪些？

MySQL中常用的四種儲存引擎分別是： MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5版本後預設的儲存引擎為InnoDB。

InnoDB儲存引擎

InnoDB是MySQL預設的事務型儲存引擎，使用最廣泛，基於聚簇索引建立的。InnoDB內部做了很多優化，如能夠自動在記憶體中建立自適應hash索引，以加速讀操作。

優點：支援事務和崩潰修復能力；引入了行級鎖和外來鍵約束。

缺點：佔用的資料空間相對較大。

適用場景：需要事務支援，並且有較高的併發讀寫頻率。

MyISAM儲存引擎

資料以緊密格式儲存。對於只讀資料，或者表比較小、可以容忍修復操作，可以使用MyISAM引擎。MyISAM會將表儲存在兩個檔案中，資料檔案.MYD和索引檔案.MYI。

優點：訪問速度快。

缺點：MyISAM不支援事務和行級鎖，不支援崩潰後的安全恢復，也不支援外來鍵。

適用場景：對事務完整性沒有要求；表的資料都會只讀的。

MEMORY儲存引擎

MEMORY引擎將資料全部放在記憶體中，訪問速度較快，但是一旦系統奔潰的話，資料都會丟失。

MEMORY引擎預設使用雜湊索引，將鍵的雜湊值和指向資料行的指標儲存在雜湊索引中。

優點：訪問速度較快。

缺點：

雜湊索引資料不是按照索引值順序儲存，無法用於排序。
不支援部分索引匹配查詢，因為雜湊索引是使用索引列的全部內容來計算雜湊值的。
只支援等值比較，不支援範圍查詢。
當出現雜湊衝突時，儲存引擎需要遍歷連結串列中所有的行指標，逐行進行比較，直到找到符合條件的行。

ARCHIVE儲存引擎

ARCHIVE儲存引擎非常適合儲存大量獨立的、作為歷史記錄的資料。ARCHIVE提供了壓縮功能，擁有高效的插入速度，但是這種引擎不支援索引，所以查詢效能較差。

MyISAM和InnoDB的區別？

是否支援行級鎖 : MyISAM 只有表級鎖，而InnoDB 支援行級鎖和表級鎖，預設為行級鎖。
是否支援事務和崩潰後的安全恢復： MyISAM 不提供事務支援。而InnoDB 提供事務支援，具有事務、回滾和崩潰修復能力。
是否支援外來鍵： MyISAM不支援，而InnoDB支援。
是否支援MVCC ：MyISAM不支援，InnoDB支援。應對高併發事務，MVCC比單純的加鎖更高效。
MyISAM不支援聚集索引，InnoDB支援聚集索引。

MVCC 實現原理？

MVCC(Multiversion concurrency control) 就是同一份資料保留多版本的一種方式，進而實現併發控制。在查詢的時候，通過read view和版本鏈找到對應版本的資料。

作用：提升併發效能。對於高併發場景，MVCC比行級鎖開銷更小。

MVCC 實現原理如下：

MVCC 的實現依賴於版本鏈，版本鏈是通過表的三個隱藏欄位實現。

DB_TRX_ID：當前事務id，通過事務id的大小判斷事務的時間順序。
DB_ROLL_PRT：回滾指標，指向當前行記錄的上一個版本，通過這個指標將資料的多個版本連線在一起構成undo log版本鏈。
DB_ROLL_ID：主鍵，如果資料表沒有主鍵，InnoDB會自動生成主鍵。

每條表記錄大概是這樣的：

使用事務更新行記錄的時候，就會生成版本鏈，執行過程如下：

用排他鎖鎖住該行；
將該行原本的值拷貝到undo log，作為舊版本用於回滾；
修改當前行的值，生成一個新版本，更新事務id，使回滾指標指向舊版本的記錄，這樣就形成一條版本鏈。

下面舉個例子方便大家理解。

1、初始資料如下，其中DB_ROW_ID和DB_ROLL_PTR為空。

2、事務A對該行資料做了修改，將age修改為12，效果如下：

3、之後事務B也對該行記錄做了修改，將age修改為8，效果如下：

4、此時undo log有兩行記錄，並且通過回滾指標連在一起。

接下來了解下read view的概念。

read view可以理解成將資料在每個時刻的狀態拍成“照片”記錄下來。在獲取某時刻t的資料時，到t時間點拍的“照片”上取資料。

在read view內部維護一個活躍事務連結串列，表示生成read view的時候還在活躍的事務。這個連結串列包含在建立read view之前還未提交的事務，不包含建立read view之後提交的事務。

不同隔離級別建立read view的時機不同。

read committed：每次執行select都會建立新的read_view，保證能讀取到其他事務已經提交的修改。
repeatable read：在一個事務範圍內，第一次select時更新這個read_view，以後不會再更新，後續所有的select都是複用之前的read_view。這樣可以保證事務範圍內每次讀取的內容都一樣，即可重複讀。

read view的記錄篩選方式

前提：DATA_TRX_ID 表示每個資料行的最新的事務ID；up_limit_id表示當前快照中的最先開始的事務；low_limit_id表示當前快照中的最慢開始的事務，即最後一個事務。

如果DATA_TRX_ID < up_limit_id：說明在建立read view時，修改該資料行的事務已提交，該版本的記錄可被當前事務讀取到。
如果DATA_TRX_ID >= low_limit_id：說明當前版本的記錄的事務是在建立read view之後生成的，該版本的資料行不可以被當前事務訪問。此時需要通過版本鏈找到上一個版本，然後重新判斷該版本的記錄對當前事務的可見性。
如果up_limit_id <= DATA_TRX_ID < low_limit_i：
1. 需要在活躍事務連結串列中查詢是否存在ID為DATA_TRX_ID的值的事務。
2. 如果存在，因為在活躍事務連結串列中的事務是未提交的，所以該記錄是不可見的。此時需要通過版本鏈找到上一個版本，然後重新判斷該版本的可見性。
3. 如果不存在，說明事務trx_id 已經提交了，這行記錄是可見的。

總結：InnoDB 的MVCC是通過 read view 和版本鏈實現的，版本鏈儲存有歷史版本記錄，通過read view 判斷當前版本的資料是否可見，如果不可見，再從版本鏈中找到上一個版本，繼續進行判斷，直到找到一個可見的版本。

快照讀和當前讀

表記錄有兩種讀取方式。

快照讀：讀取的是快照版本。普通的SELECT就是快照讀。通過mvcc來進行併發控制的，不用加鎖。
當前讀：讀取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是當前讀。

快照讀情況下，InnoDB通過mvcc機制避免了幻讀現象。而mvcc機制無法避免當前讀情況下出現的幻讀現象。因為當前讀每次讀取的都是最新資料，這時如果兩次查詢中間有其它事務插入資料，就會產生幻讀。

下面舉個例子說明下：

1、首先，user表只有兩條記錄，具體如下：

2、事務a和事務b同時開啟事務start transaction；

3、事務a插入資料然後提交；

insert into user(user_name, user_password, user_mail, user_state) values('tyson', 'a', 'a', 0);

4、事務b執行全表的update；

update user set user_name = 'a';

5、事務b然後執行查詢，查到了事務a中插入的資料。（下圖左邊是事務b，右邊是事務a。事務開始之前只有兩條記錄，事務a插入一條資料之後，事務b查詢出來是三條資料）

以上就是當前讀出現的幻讀現象。

那麼MySQL是如何避免幻讀？

在快照讀情況下，MySQL通過mvcc來避免幻讀。
在當前讀情況下，MySQL通過next-key來避免幻讀（加行鎖和間隙鎖來實現的）。

next-key包括兩部分：行鎖和間隙鎖。行鎖是加在索引上的鎖，間隙鎖是加在索引之間的。

Serializable隔離級別也可以避免幻讀，會鎖住整張表，併發性極低，一般不會使用。

共享鎖和排他鎖

SELECT 的讀取鎖定主要分為兩種方式：共享鎖和排他鎖。

select * from table where id<6 lock in share mode;--共享鎖select * from table where id<6 for update;--排他鎖

這兩種方式主要的不同在於LOCK IN SHARE MODE 多個事務同時更新同一個表單時很容易造成死鎖。

申請排他鎖的前提是，沒有執行緒對該結果集的任何行資料使用排它鎖或者共享鎖，否則申請會受到阻塞。在進行事務操作時，MySQL會對查詢結果集的每行資料新增排它鎖，其他執行緒對這些資料的更改或刪除操作會被阻塞（只能讀操作），直到該語句的事務被commit語句或rollback語句結束為止。

SELECT... FOR UPDATE 使用注意事項：

for update 僅適用於innodb，且必須在事務範圍內才能生效。
根據主鍵進行查詢，查詢條件為like或者不等於，主鍵欄位產生表鎖。
根據非索引欄位進行查詢，會產生表鎖。

大表怎麼優化？

某個表有近千萬資料，查詢比較慢，如何優化？

當MySQL單表記錄數過大時，資料庫的效能會明顯下降，一些常見的優化措施如下：

限定資料的範圍。比如：使用者在查詢歷史資訊的時候，可以控制在一個月的時間範圍內；
讀寫分離：經典的資料庫拆分方案，主庫負責寫，從庫負責讀；
通過分庫分表的方式進行優化，主要有垂直拆分和水平拆分。

bin log/redo log/undo log

MySQL日誌主要包括查詢日誌、慢查詢日誌、事務日誌、錯誤日誌、二進位制日誌等。其中比較重要的是 bin log（二進位制日誌）和 redo log（重做日誌）和 undo log（回滾日誌）。

bin log

bin log是MySQL資料庫級別的檔案，記錄對MySQL資料庫執行修改的所有操作，不會記錄select和show語句，主要用於恢復資料庫和同步資料庫。

redo log

redo log是innodb引擎級別，用來記錄innodb儲存引擎的事務日誌，不管事務是否提交都會記錄下來，用於資料恢復。當資料庫發生故障，innoDB儲存引擎會使用redo log恢復到發生故障前的時刻，以此來保證資料的完整性。將引數innodb_flush_log_at_tx_commit設定為1，那麼在執行commit時會將redo log同步寫到磁碟。

undo log

除了記錄redo log外，當進行資料修改時還會記錄undo log，undo log用於資料的撤回操作，它保留了記錄修改前的內容。通過undo log可以實現事務回滾，並且可以根據undo log回溯到某個特定的版本的資料，實現MVCC。

bin log和redo log有什麼區別？

bin log會記錄所有日誌記錄，包括InnoDB、MyISAM等儲存引擎的日誌；redo log只記錄innoDB自身的事務日誌。
bin log只在事務提交前寫入到磁碟，一個事務只寫一次；而在事務進行過程，會有redo log不斷寫入磁碟。
bin log是邏輯日誌，記錄的是SQL語句的原始邏輯；redo log是物理日誌，記錄的是在某個資料頁上做了什麼修改。

講一下MySQL架構？

MySQL主要分為 Server 層和儲存引擎層：

Server 層：主要包括聯結器、查詢快取、分析器、優化器、執行器等，所有跨儲存引擎的功能都在這一層實現，比如儲存過程、觸發器、檢視，函式等，還有一個通用的日誌模組 binglog 日誌模組。
儲存引擎：主要負責資料的儲存和讀取。server 層通過api與儲存引擎進行通訊。

Server 層基本元件

聯結器： 當客戶端連線 MySQL 時，server層會對其進行身份認證和許可權校驗。
查詢快取: 執行查詢語句的時候，會先查詢快取，先校驗這個 sql 是否執行過，如果有快取這個 sql，就會直接返回給客戶端，如果沒有命中，就會執行後續的操作。
分析器: 沒有命中快取的話，SQL 語句就會經過分析器，主要分為兩步，詞法分析和語法分析，先看 SQL 語句要做什麼，再檢查 SQL 語句語法是否正確。
優化器： 優化器對查詢進行優化，包括重寫查詢、決定表的讀寫順序以及選擇合適的索引等，生成執行計劃。
執行器： 首先執行前會校驗該使用者有沒有許可權，如果沒有許可權，就會返回錯誤資訊，如果有許可權，就會根據執行計劃去呼叫引擎的介面，返回結果。

分庫分表

當單表的資料量達到1000W或100G以後，優化索引、新增從庫等可能對資料庫效能提升效果不明顯，此時就要考慮對其進行切分了。切分的目的就在於減少資料庫的負擔，縮短查詢的時間。

資料切分可以分為兩種方式：垂直劃分和水平劃分。

垂直劃分

垂直劃分資料庫是根據業務進行劃分，例如購物場景，可以將庫中涉及商品、訂單、使用者的表分別劃分出成一個庫，通過降低單庫的大小來提高效能。同樣的，分表的情況就是將一個大表根據業務功能拆分成一個個子表，例如商品基本資訊和商品描述，商品基本資訊一般會展示在商品列表，商品描述在商品詳情頁，可以將商品基本資訊和商品描述拆分成兩張表。

優點：行記錄變小，資料頁可以存放更多記錄，在查詢時減少I/O次數。

缺點：

主鍵出現冗餘，需要管理冗餘列；
會引起表連線JOIN操作，可以通過在業務伺服器上進行join來減少資料庫壓力；
依然存在單表資料量過大的問題。

水平劃分

水平劃分是根據一定規則，例如時間或id序列值等進行資料的拆分。比如根據年份來拆分不同的資料庫。每個資料庫結構一致，但是資料得以拆分，從而提升效能。

優點：單庫（表）的資料量得以減少，提高效能；切分出的表結構相同，程式改動較少。

缺點：

分片事務一致性難以解決
跨節點join效能差，邏輯複雜
資料分片在擴容時需要遷移

什麼是分割槽表？

分割槽表是一個獨立的邏輯表，但是底層由多個物理子表組成。

當查詢條件的資料分佈在某一個分割槽的時候，查詢引擎只會去某一個分割槽查詢，而不是遍歷整個表。在管理層面，如果需要刪除某一個分割槽的資料，只需要刪除對應的分割槽即可。

分割槽表型別

按照範圍分割槽。

CREATE TABLE test_range_partition(
       id INT auto_increment,
       createdate DATETIME,
       primary key (id,createdate)
   ) 
   PARTITION BY RANGE (TO_DAYS(createdate) ) (
      PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
      PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
      PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
      PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
      PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
      PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
      PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
      PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
      PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
      PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
      PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
      PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
   );

在/var/lib/mysql/data/可以找到對應的資料檔案，每個分割槽表都有一個使用#分隔命名的表檔案：

   -rw-r----- 1 MySQL MySQL    65 Mar 14 21:47 db.opt
   -rw-r----- 1 MySQL MySQL  8598 Mar 14 21:50 test_range_partition.frm
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd
...

list分割槽

對於List分割槽，分割槽欄位必須是已知的，如果插入的欄位不在分割槽時列舉值中，將無法插入。

create table test_list_partiotion
   (
       id int auto_increment,
       data_type tinyint,
       primary key(id,data_type)
   )partition by list(data_type)
   (
       partition p0 values in (0,1,2,3,4,5,6),
       partition p1 values in (7,8,9,10,11,12),
       partition p2 values in (13,14,15,16,17)
   );

hash分割槽

可以將資料均勻地分佈到預先定義的分割槽中。

create table test_hash_partiotion
   (
       id int auto_increment,
       create_date datetime,
       primary key(id,create_date)
   )partition by hash(year(create_date)) partitions 10;

查詢語句執行流程？

查詢語句的執行流程如下：許可權校驗、查詢快取、分析器、優化器、許可權校驗、執行器、引擎。

舉個例子，查詢語句如下：

select * from user where id > 1 and name = '大彬';

首先檢查許可權，沒有許可權則返回錯誤；
MySQL8.0以前會查詢快取，快取命中則直接返回，沒有則執行下一步；
詞法分析和語法分析。提取表名、查詢條件，檢查語法是否有錯誤；
兩種執行方案，先查 id > 1 還是 name = '大彬'，優化器根據自己的優化演算法選擇執行效率最好的方案；
校驗許可權，有許可權就呼叫資料庫引擎介面，返回引擎的執行結果。

更新語句執行過程？

更新語句執行流程如下：分析器、許可權校驗、執行器、引擎、redo log（prepare狀態）、binlog、redo log（commit狀態）

舉個例子，更新語句如下：

update user set name = '大彬' where id = 1;

先查詢到 id 為1的記錄，有快取會使用快取。
拿到查詢結果，將 name 更新為大彬，然後呼叫引擎介面，寫入更新資料，innodb 引擎將資料儲存在記憶體中，同時記錄redo log，此時redo log進入 prepare狀態。
執行器收到通知後記錄binlog，然後呼叫引擎介面，提交redo log為commit狀態。
更新完成。

為什麼記錄完redo log，不直接提交，而是先進入prepare狀態？

假設先寫redo log直接提交，然後寫binlog，寫完redo log後，機器掛了，binlog日誌沒有被寫入，那麼機器重啟後，這臺機器會通過redo log恢復資料，但是這個時候binlog並沒有記錄該資料，後續進行機器備份的時候，就會丟失這一條資料，同時主從同步也會丟失這一條資料。

exist和in的區別？

exists用於對外表記錄做篩選。exists會遍歷外表，將外查詢表的每一行，代入內查詢進行判斷。當exists裡的條件語句能夠返回記錄行時，條件就為真，返回外表當前記錄。反之如果exists裡的條件語句不能返回記錄行，條件為假，則外表當前記錄被丟棄。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)

in是先把後邊的語句查出來放到臨時表中，然後遍歷臨時表，將臨時表的每一行，代入外查詢去查詢。

select * from Awhere id in(select id from B)

子查詢的表比較大的時候，使用exists可以有效減少總的迴圈次數來提升速度；當外查詢的表比較大的時候，使用in可以有效減少對外查詢表迴圈遍歷來提升速度。

truncate、delete與drop區別？

相同點：

truncate和不帶where子句的delete、以及drop都會刪除表內的資料。
drop、truncate都是DDL語句（資料定義語言），執行後會自動提交。

不同點：

truncate 和 delete 只刪除資料不刪除表的結構；drop 語句將刪除表的結構被依賴的約束、觸發器、索引；
一般來說，執行速度: drop > truncate > delete。

having和where的區別？

二者作用的物件不同，where子句作用於表和檢視，having作用於組。
where在資料分組前進行過濾，having在資料分組後進行過濾。

什麼是MySQL主從同步？

主從同步使得資料可以從一個資料庫伺服器複製到其他伺服器上，在複製資料時，一個伺服器充當主伺服器（master），其餘的伺服器充當從伺服器（slave）。

因為複製是非同步進行的，所以從伺服器不需要一直連線著主伺服器，從伺服器甚至可以通過撥號斷斷續續地連線主伺服器。通過配置檔案，可以指定複製所有的資料庫，某個資料庫，甚至是某個資料庫上的某個表。

為什麼要做主從同步？

讀寫分離，使資料庫能支撐更大的併發。
在主伺服器上生成實時資料，而在從伺服器上分析這些資料，從而提高主伺服器的效能。
資料備份，保證資料的安全。

樂觀鎖和悲觀鎖是什麼？

資料庫中的併發控制是確保在多個事務同時存取資料庫中同一資料時不破壞事務的隔離性和統一性以及資料庫的統一性。樂觀鎖和悲觀鎖是併發控制主要採用的技術手段。

悲觀鎖：假定會發生併發衝突，在查詢完資料的時候就把事務鎖起來，直到提交事務。實現方式：使用資料庫中的鎖機制。
樂觀鎖：假設不會發生併發衝突，只在提交操作時檢查是否資料是否被修改過。給表增加version欄位，在修改提交之前檢查version與原來取到的version值是否相等，若相等，表示資料沒有被修改，可以更新，否則，資料為髒資料，不能更新。實現方式：樂觀鎖一般使用版本號機制或CAS演算法實現。

用過processlist嗎？

show processlist 或 show full processlist 可以檢視當前 MySQL 是否有壓力，正在執行的SQL，有沒有慢SQL正在執行。返回引數如下：

id：執行緒ID，可以用kill id殺死某個執行緒
db：資料庫名稱
user：資料庫使用者
host：資料庫例項的IP
command：當前執行的命令，比如Sleep，Query，Connect 等
time：消耗時間，單位秒
state：執行狀態，主要有以下狀態：
- Sleep，執行緒正在等待客戶端傳送新的請求
- Locked，執行緒正在等待鎖
- Sending data，正在處理SELECT查詢的記錄，同時把結果傳送給客戶端
- Kill，正在執行kill語句，殺死指定執行緒
- Connect，一個從節點連上了主節點
- Quit，執行緒正在退出
- Sorting for group，正在為GROUP BY做排序
- Sorting for order，正在為ORDER BY做排序
info：正在執行的SQL語句