前言

作為後端猿的我們，不出意外每天都會和mysql打交道。除了每天寫不膩的CURD語句，關於mysql我們應該要了解它的那些基礎知識呢？

直入主題

簡單畫出mysql的邏輯架構圖？

       接 入 層
-----------------------
     ⬆          ⬆      
     ⬇          ⬇    
    緩 存      解 析 器
                 ⬆      
                 ⬇
              優 化 器 
-----------------------

        引 擎

複製程式碼

mysql常見的引擎有哪些，區別是什麼？

InnoDB
- 支援事務
- 行鎖
- 聚簇索引
- 輔助索引(二級索引)索引存放的是主鍵
MyISAM
- 不支援事務
- 表鎖
- 崩潰無法安全恢復
- 非聚簇索引
- 輔助索引(二級索引)索引存放的還是實際資料的地址
Memory
- 基於記憶體
- 表鎖
- 欄位長度固定，不支援blob,text, 即使指定vachar實際儲存也會轉為char
Archive
- 只支援insert/select操作
- 適合日誌等
...

什麼是事務的ACDI概念？

A: Atomicity, 原子性, 一個事務的所有操作視為一個整體，要不全部成功，要不全部失敗。
C: Consistency, 一致性, 一個事務下的所有的資料狀態變更，只有事務提交成功才全部變更。
D: Durability, 永續性, 一旦事務提交成功，產生的資料變更將永久儲存在資料庫中。
I: Isolation, 隔離性, 一個事務在提交之前對其他事務不可見。

什麼是髒讀，幻讀，不可重複讀？

髒讀：讀取未提交事務的資料，資料可能被回滾，不符合隔離性的定義。
幻讀：一個事務批量讀取了一批資料時，另一個事務提交了新的資料，當之前的事務再次讀取時，會產生幻影行。
不可重複讀：執行兩次相同的查詢，可能得到不同的結果。

mysql事務的隔離級別有哪些？預設的隔離級別是什麼？

未提交讀：一個事務還未提交，另一個事務就可以讀取，這樣導致的後果，髒讀。
提交讀(又叫，不可重複讀)：一個事務未提交對其他事務不可見，但是會產生幻讀和不可重複讀。
可重複讀(mysql預設隔離級別)：保證同一個事務下多次讀取的結果一致，但是會產生幻讀。
可序列化：嚴格的序列阻塞，併發能力不好。

隔離級別	髒讀	不可重複讀	幻讀
Read Uncommitted	√	√	√
Read Committed	×	√	√
Repeatable Read	×	×	√
Serializable	×	×	×

什麼是MVCC？簡述MVCC的作用及原理？

MVCC：Multi Version Concurrency Control, 多版本併發控制，mysql防止幻讀的一種技術手段。每行資料存在間隙行，間隙行存放該行資料的建立時間，刪除時間，這裡的時間實際是事務的版本號。當，

select資料時：只查詢建立時間小於等於當前事務版本號 -> 當前事務或當前事務之前插入的行，刪除時間大於當前版本號的行 -> 當前事務版本前未被刪除的行。
update資料時：在原有行a的基礎上覆制行a',行a的刪除時間設定為當前的事務版本號，行a'的建立時間設定為當前的事務版本號。
insert資料時：記錄建立時間為當前事務版本號。
delete資料時：記錄刪除時間為當前事務版本號。

死鎖是怎麼出現的，並寫出簡單示例？

死鎖產生的原因是兩個事務互相等待對方釋放，產生了迴圈依賴，mysql採用了死鎖檢測(檢測到迴圈依賴返回錯誤)和死鎖超時(超時回滾持有行鎖最少的事務)的方式儘可能去避免死鎖。例如：

行鎖：

UPDATE `table_demo` SET `a` = 'test' WHERE `b` = 'lalala';
UPDATE `table_demo` SET `b` = 'test' WHERE `a` = 'lalala';

UPDATE `table_demo` SET `b` = 'test' WHERE `a` = 'lalala';
UPDATE `table_demo` SET `a` = 'test' WHERE `b` = 'lalala';
複製程式碼

什麼是聚簇索引和非聚簇索引？

聚簇索引：InnoDB, B+樹的葉子節點存放實際資料。
非聚簇索引：MyISAM, B+樹的葉子節點存放實際資料的地址。

什麼是覆蓋索引？

覆蓋索引：要查詢的行被索引覆蓋，從索引中可以直接讀取，不需要回表查詢。例如：

CREATE TABLE `demo_table`(
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
    `username` char(32) NOT NULL DEFAULT '' COMMENT '使用者名稱',
    `password` char(32) NOT NULL DEFAULT '' COMMENT '密碼',
    PRIMARY KEY (`id`),
    KEY `idx_username` (`username`)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

複製程式碼

explain select `username` from `demo_table` where `username` = 'demo';
+----+-------------+------------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | demo_table | ref  | idx_username  | idx_username | 96      | const |    1 | Using where; Using index |
+----+-------------+------------+------+---------------+--------------+---------+-------+------+--------------------------+
複製程式碼

Extra裡的Using index就是使用了覆蓋索引的意思。

什麼是索引的最左字首匹配原則？

個人目前理解：例如使用聯合索引，從左向右依次匹配，未匹配到索引欄位或第一個範圍查詢(between、like、大於、小於)為止，及該部分索引有效。

InnoDB為什麼不採用紅黑樹而採用B+樹作為索引存放資料結構，並簡要畫出B+樹？(目前我個人的理解比較淺歡迎積極糾正～)

紅黑樹本質是二叉樹，每個節點最多擁有兩個子節點，所以紅黑樹的深度較深。

B樹每個節點最多可以有n個子節點，根節點常駐記憶體且每個節點剛好申請１個頁的大小，假如每個節點擁有100個子節點，那百萬級的資料我們基本上只需要深度是3就可以存下 => 100^3，這樣就減少的io次數(一個節點的大小通常為磁碟一個頁的大小)。

又有“區域性性原理”(一旦一個資料被查詢，那麼它附近的資料可能也會需要被查詢)，其次B+樹的葉子節點構成一個連結串列，這樣我們就可以很容易的查詢出一段範圍的資料，其次B+樹的根節點和內部節點只存放該索引下一個子節點的位置的指標，資料只存放在葉子節點裡，這樣非葉子節點就可以有更多的空間存放索引的位置，索引的範圍就可以儘可能的大，從而樹的深度就可能的小。

https://user-gold-cdn.xitu.io/2018/2/4/16160b4268560364?w=843&h=546&f=png&s=8751

結語

後續持續修正和補充，如果有什麼寫的不對的地方歡迎大家積極指正，謝謝大家。

走進mysql基礎

前言