文章目錄

• 一 存儲引擎
• • 1 MyISAM 與 InnoDB 的差異
  • 2 表級鎖與行級鎖
• 二 索引
• • 1 主鍵索引與二級索引
  • 2 聚簇索引與非聚簇索引
  • 3 數(shù)據(jù)結(jié)構(gòu)：哈希表
  • 4 數(shù)據(jù)結(jié)構(gòu)：B樹
  • 5 數(shù)據(jù)結(jié)構(gòu)：B+樹
  • 6 數(shù)據(jù)結(jié)構(gòu)：跳表
  • 7 為什么不使用紅黑樹**
  • 8 為什么不使用B樹**
• 三 MySQL 事務
• • 1 ACID 及其保證手段
  • 2 事務的隔離級別
  • 3 多版本并發(fā)控制 MVCC
  • 4 MVCC 實現(xiàn)流程
  • 5 RR 等級下使用 MVCC 防止幻讀
• 四 規(guī)范與優(yōu)化建議
• • 1 索引、數(shù)據(jù)表設計
  • 2 索引失效的情況（優(yōu)化查詢需要避免）
  • 3 連接查詢優(yōu)化
  • 4 分庫和分表
• 五 InnoDB 的鎖機制
• • 1 表鎖：S、X、IS、IX
  • 2 行鎖：S、X
  • 3 行鎖的進一步劃分
• 六 SQL 查詢
• • 1 語法
  • 2 執(zhí)行順序
  • 3 常用函數(shù) / 關鍵字

---

一 存儲引擎

1 MyISAM 與 InnoDB 的差異

• MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默認存儲引擎，之后的默認引擎是 InnoDB
• 相比之下，InnoDB 具有 支持事務、支持行級鎖、支持外鍵、支持數(shù)據(jù)庫異常崩潰后的安全恢復（redo log） 的特性

2 表級鎖與行級鎖

• 表級鎖： MySQL 中鎖定 粒度最大 的一種鎖，對當前操作的整張表加鎖，實現(xiàn)簡單，資源消耗也比較少，加鎖快，不會出現(xiàn)死鎖。其鎖定粒度最大，觸發(fā)鎖沖突的概率最高，并發(fā)度最低，MyISAM 和 InnoDB 引擎都支持表級鎖
• 行級鎖： MySQL 中鎖定 粒度最小 的一種鎖，只針對當前操作的行進行加鎖。 行級鎖能大大減少數(shù)據(jù)庫操作的沖突。其加鎖粒度最小，并發(fā)度高，但加鎖的開銷也最大，加鎖慢，會出現(xiàn)死鎖

---

二 索引

使用索引不一定能提升查詢性能，在表比較小時，全表掃描的速度可能比使用索引更快

1 主鍵索引與二級索引

• 主鍵索引存放的數(shù)據(jù)是表中的一條數(shù)據(jù)，二級索引存放的數(shù)據(jù)是主鍵索引
• 一張數(shù)據(jù)表有只能有一個主鍵，并且主鍵不能為 null，不能重復
• 一般而言，不發(fā)生索引覆蓋時，在根據(jù)主索引搜索時，直接找到 key 所在的節(jié)點即可取出數(shù)據(jù)；在根據(jù)輔助索引查找時，則需要先取出主鍵的值，再使用主索引進行數(shù)據(jù)查找
• 除非發(fā)生索引覆蓋（需要查詢的字段正好是索引的字段，無論主索引或二級索引）的情況，否則使用二級索引會導致回表（當查到索引對應的主鍵后，還需要根據(jù)主鍵再到數(shù)據(jù)文件或表中查詢）
• 如果沒有主鍵也沒有合適的唯一索引，那么 InnoDB 內(nèi)部會生成一個隱藏的主鍵作為聚集索引，這個隱藏的主鍵是一個6個字節(jié)，類型為長整型的列，該列的值會隨著數(shù)據(jù)的插入自增
• 二級索引的內(nèi)節(jié)點，也保存了主鍵的值，避免相同的二級索引值造成歧義
• 聯(lián)合索引本質(zhì)上也是二級索引，多個屬性建立聯(lián)合索引只會建立一棵B+樹

2 聚簇索引與非聚簇索引

• 聚簇索引指的是，索引和數(shù)據(jù)存放在一起。對于 InnoDB 引擎表來說，該表的索引(B+樹)的每個非葉子節(jié)點存儲索引，葉子節(jié)點存儲索引和索引對應的數(shù)據(jù)
• 主鍵索引是聚簇索引，二級索引是非聚簇索引

索引類型優(yōu)點缺點

聚簇索引	查詢速度快，無需回表	需要依賴有序的數(shù)據(jù)（因為數(shù)據(jù)結(jié)構(gòu)是B+樹，需要在插入時排序）；更新代價大（如果對索引列的數(shù)據(jù)被修改時，那么對應的索引也將會被修改）
非聚簇索引	更新代價比聚簇索引小	需要依賴有序的數(shù)據(jù)；可能會回表

3 數(shù)據(jù)結(jié)構(gòu)：哈希表

• 插入數(shù)據(jù)時，根據(jù) key 進行哈希運算，得到 bucket 的位置，如果該位置無 value 則插入成功，如果有則發(fā)生了哈希沖突，使用拉鏈法或者紅黑樹解決（類似 Java 的 HashMap）
• 只支持全值的精確查詢，時間復雜度僅為O(1)，但不支持順序或者范圍查詢

4 數(shù)據(jù)結(jié)構(gòu)：B樹

• MongoDB 采用的索引類型，多路平衡查找樹
• B樹的中間節(jié)點存放的也是數(shù)據(jù)；葉節(jié)點之間沒有連接
• B樹的檢索的過程相當于對范圍內(nèi)的每個節(jié)點的關鍵字做二分查找，可能還沒有到達葉子節(jié)點，檢索就結(jié)束了

5 數(shù)據(jù)結(jié)構(gòu)：B+樹

• MyISAM 和 InnoDB 均采用的索引類型，多路平衡查找樹
• B+樹的中間節(jié)點只存放索引，數(shù)據(jù)全部都在葉節(jié)點上；葉節(jié)點之間有連接
• 相對于B樹而言，對范圍查找的支持更好
• 查詢效率更加穩(wěn)定，因為每次查詢必定會訪問到葉節(jié)點
• 葉子節(jié)點構(gòu)成一個有序鏈表，而且葉子節(jié)點本身按照關鍵字的大小從小到大順序鏈接
• B+樹的階數(shù)并非越大越好：階數(shù)很大時，一個節(jié)點的大小會超過一個頁的大小，讀取這樣一個節(jié)點會導致多次I/O操作，造成性能下降。要盡量讓每個節(jié)點的大小等于一個頁的大小

6 數(shù)據(jù)結(jié)構(gòu)：跳表

• 在 Redis 中的 Zset 使用到的數(shù)據(jù)結(jié)構(gòu)
• 為什么在 MySQL 中選擇 B+ 樹而非跳表作為索引的數(shù)據(jù)結(jié)構(gòu)？
  如果是查詢磁盤文件，B+ 樹會比跳表的性能好很多，因為磁盤查詢性能比內(nèi)存差，所以要盡量減少查詢的次數(shù)
  B+ 樹每個節(jié)點按頁存放數(shù)據(jù)，每次查詢可以查詢一批數(shù)據(jù)到內(nèi)存中；而且 B+ 樹的層數(shù)低，可以減少訪問磁盤的次數(shù)

7 為什么不使用紅黑樹**

• MySQL 用 I/O 次數(shù)衡量查詢效率，磁盤查找存取的次數(shù)往往由樹的高度所決定

紅黑樹是內(nèi)排序使用的數(shù)據(jù)結(jié)構(gòu)，B+ 樹常用于外排序

紅黑樹的高度通常更高（因為是二叉樹），需要的磁盤 I/O 次數(shù)更多，效率低；B+樹可以有多個子節(jié)點，樹的高度通常為1~3層，效率高

B+樹的一個節(jié)點對應一個頁，如果使用紅黑樹的話每個節(jié)點（頁）存放一條數(shù)據(jù)，造成很大的浪費

8 為什么不使用B樹**

B樹執(zhí)行范圍查詢時只能按照類似于中序遍歷的方式進行，效率不如B+樹在葉節(jié)點的順序訪問

B樹/B+樹的特點就是每層節(jié)點數(shù)目非常多，層數(shù)很少，就是為了減少 I/O 次數(shù)，但是B樹的每個節(jié)點都存放數(shù)據(jù)，這無疑增大了節(jié)點大小，增加了磁盤 I/O 次數(shù)（磁盤I/O一次讀出的數(shù)據(jù)量大小是固定的，單個數(shù)據(jù)變大，每次讀出的就少，導致 I/O次數(shù)增多），而B+樹除了葉子節(jié)點均不存儲數(shù)據(jù)，節(jié)點小，磁盤 I/O 次數(shù)就少

---

三 MySQL 事務

1 ACID 及其保證手段

• 原子性：事務是最小的執(zhí)行單位，不允許分割（事務內(nèi)的一系列操作，要么全都做，要么全不做）
• 一致性：事務執(zhí)行前后，數(shù)據(jù)庫的一致性保持不變
• 隔離性：并發(fā)進行的事務之間互不影響
• 持久性：事務提交后，對數(shù)據(jù)庫中數(shù)據(jù)的修改是永久的

MySQL InnoDB 引擎使用 redo log 保證事務的持久性，使用 undo log 來保證事務的原子性；
鎖機制、MVCC 等手段來保證事務的隔離性（ 默認隔離級別是可重復讀）；
保證了事務的持久性、原子性、隔離性之后，一致性才能得到保障

2 事務的隔離級別

隔離級別&出現(xiàn)的異常臟讀（讀到未提交數(shù)據(jù)）不可重復讀（兩次讀取同一數(shù)據(jù)結(jié)果不同）幻讀（兩次查詢結(jié)果數(shù)目不同）

讀未提交	√	√	√
讀提交	×	√	√
可重復讀	×	×	√
串行化	×	×	×

• 可重復讀 使用 MVCC + Next-key Lock 也可以避免幻讀

3 多版本并發(fā)控制 MVCC

• 簡言之，MVCC 處理的是 讀數(shù)據(jù) 的問題，寫數(shù)據(jù)必須依靠加鎖
• 普通的 SELECT 語句在 讀提交 和 可重復讀 隔離級別下會使用到 MVCC，主要針對的也是這兩種隔離級別（因為這兩種隔離級別要求讀到的是 已經(jīng)提交了的 事務修改過的記錄），另外兩種隔離級別不適用 MVCC
• MVCC 的讀指的是 快照讀（讀取的是快照數(shù)據(jù)） , 而非 當前讀（當前讀讀取的是記錄的最新版本，讀取時還要保證其他并發(fā)事務不能修改當前記錄，會對讀取的記錄進行加鎖）
• 當前讀實際上是一種加鎖的操作，是悲觀鎖的實現(xiàn)，而MVCC本質(zhì)是采用樂觀鎖思想的一種方式

4 MVCC 實現(xiàn)流程

• 其實現(xiàn)主要依賴于 記錄的隱藏字段 + UndoLog + ReadView
• 讀提交 和 可重復讀 關于 ReadView 的區(qū)別是，在每一次進行普通 SELECT 操作前都會生成一個ReadView ；可重復讀只在第一次進行普通 SELECT 操作前生成一個 ReadView，之后的查詢操作都重復使用相同的 ReadView
• ReadView 包含主要結(jié)構(gòu)

creator_trx_id創(chuàng)建這個 ReadView 的事務 ID（只讀事務為0，修改記錄的事務會被分配 ID）

trx_ids	生成 ReadView 時，活躍的讀寫事務的事務 ID 列表
up_limit_id	活躍的事務中最小的事務 ID
low_limit_id	生成 ReadView 時，系統(tǒng)中應該分配給下一個事務的 ID 值

注意：low_limit_id 并不是 trx_ids 中的最大值，事務id是遞增分配的。比如，現(xiàn)在有id為1，2，3這三個事務，之后id為3的事務提交了。那么一個新的讀事務在生成 ReadView 時，trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是4

• ReadView 規(guī)則：

• 如果被訪問版本的 trx_id 屬性值與 ReadView 中的 creator_trx_id 值相同，說明該記錄的修改是由當前事務創(chuàng)建的，允許訪問
• 如果被訪問版本的 trx_id 屬性值小于 ReadView 中的 up_limit_id 值，表明生成該版本的事務在當前事務生成 ReadView 之前已經(jīng)提交，允許訪問
• 如果被訪問版本的 trx_id 屬性值大于或等于 ReadView 中的 low_limit_id 值，表明生成該版本的事務在當前事務生成 ReadView 后才開啟，不允許訪問
• 如果被訪問版本的 trx_id 屬性值在 ReadView 的 up_limit_id 和 low_limit_id 之間，那就需要判斷一下 trx_id 屬性值是不是在 trx_ids 列表中：如果在，說明生成該版本的事務，在當前事務開啟時尚未提交，不允許訪問；反之說明生成該版本的事務，在當前事務開啟時已經(jīng)提交，允許訪問

• MVCC 整體流程：

• 首先獲取事務自己的版本號，也就是事務 ID；
• 獲取 ReadView；
• 查詢得到的數(shù)據(jù)，然后與 ReadView 中的事務版本號進行比較；
• 如果不符合 ReadView 規(guī)則，就需要從 Undo Log 中獲取歷史快照；
• 最后返回符合規(guī)則的數(shù)據(jù)，如果無符合規(guī)則的數(shù)據(jù)則返回空
  

5 RR 等級下使用 MVCC 防止幻讀

• 執(zhí)行普通 SELECT 即快照讀時，可重復讀只會在事務開啟后的第一次查詢生成 Read View ，并使用至事務提交。所以在生成 ReadView 之后其它事務所做的更新、插入記錄版本對當前事務并不可見，實現(xiàn)了可重復讀和防止快照讀下的 幻讀
• 執(zhí)行當前讀時，InnoDB 使用 Next-key Lock 來防止這種情況。當執(zhí)行當前讀時，會鎖定讀取到的記錄的同時，鎖定它們的間隙，防止其它事務在查詢范圍內(nèi)插入數(shù)據(jù)

---

四 規(guī)范與優(yōu)化建議

1 索引、數(shù)據(jù)表設計

讓主鍵具有 AUTO_INCREMENT ，讓存儲引擎自己生成主鍵，而不是手動插入，讓插入的記錄的主鍵值依次遞增，避免頁面分裂帶來的性能損耗

字段的數(shù)據(jù)類型定義準確

設計數(shù)據(jù)表時遵循范式規(guī)則 1/2/3NF
1NF：數(shù)據(jù)表的每個字段不可拆分
2NF：數(shù)據(jù)表中非主屬性完全依賴于主屬性
3NF：數(shù)據(jù)表中的非主屬性不傳遞依賴于主屬性

對數(shù)據(jù)表進行適當?shù)男小⒘胁鸱?#xff08;分表）

數(shù)據(jù)庫和表的字符集統(tǒng)一使用 utf8mb4

表屬性盡量設置為 NOT NULL，因為 IS NOT NULL 不能使用索引

將聯(lián)合索引中，把過濾性最好（區(qū)分度最大）的屬性放在前面；把需要范圍查詢的屬性放在后面

2 索引失效的情況（優(yōu)化查詢需要避免）

違背最佳左前綴法則
對于列 (age, class, name) 建立了聯(lián)合索引，如果查詢條件是 class=5 and name='zy' 等違背左前綴，則無法使用該索引
優(yōu)化器可以決定 where 后面條件的順序，所以 class=5 and name='zy' and age=10 可以使用索引

計算、函數(shù)、類型轉(zhuǎn)換導致索引失效
WHERE name=123 不能使用索引；WHERE name='123' 可以使用索引

IS NULL 可以使用索引，IS NOT NULL 不能使用索引

范圍條件右邊（指的是定義聯(lián)合索引的右，而非 where 條件中的右）的列索引失效
對于列 (age, class, name) 建立了聯(lián)合索引，WHERE student.age=30 AND student.name = 'abc' AND student.classId>20 會導致 name 索引失效

不等于 != 導致索引失效

OR 前后存在非索引的列，索引失效：因為對于非索引的條件需要全表掃描，該情況下直接放棄使用索引而執(zhí)行全表掃描

對于模糊查詢，通配符（%）在搜尋詞首出現(xiàn)，一般會導致不使用索引，"%ABC"不能使用，但"A%BC"可以使用

3 連接查詢優(yōu)化

• 驅(qū)動表：無法避免全表掃描的表稱為驅(qū)動表
• 在決定哪個表做驅(qū)動表的時候，兩個表按照各自的條件過濾，過濾后計算參與 join 的各個字段的總數(shù)據(jù)量，數(shù)據(jù)量小的那個表，就是“小表”，應該作為驅(qū)動表
• 為了提高連接查詢效率，需要將小表作為驅(qū)動表，大表作為被驅(qū)動表，減少外層循環(huán)的次數(shù)
• LEFT JOIN 保證左表的每個記錄都會出現(xiàn)，即左表為驅(qū)動表，所以右表是關鍵，一定需要建立索引（右外連接反之）
• INNER JOIN 會自動決定驅(qū)動表、被驅(qū)動表
  對于連接屬性，如果兩表只有一個具有索引，則它作為被驅(qū)動表
  如果兩個表都在該屬性有索引，小表作為驅(qū)動表，大表作為被驅(qū)動表
• 需要JOIN 的字段，數(shù)據(jù)類型保持絕對一致，避免類型轉(zhuǎn)換導致索引失效

4 分庫和分表

切分定義應用場景

分庫	數(shù)據(jù)庫中的數(shù)據(jù)分散到不同的數(shù)據(jù)庫上	應用的并發(fā)量太大；數(shù)據(jù)庫中的數(shù)據(jù)占用的空間越來越大，備份時間越來越長
分表	對單表的數(shù)據(jù)進行拆分，可以是垂直拆分，也可以是水平拆分	單表的數(shù)據(jù)達到千萬級別以上，數(shù)據(jù)庫讀寫速度比較緩慢

分庫帶來的問題：

• join 操作 ： 同一個數(shù)據(jù)庫中的表分布在了不同的數(shù)據(jù)庫中，導致無法使用 join 操作，需要手動進行數(shù)據(jù)的封裝，比如在一個數(shù)據(jù)庫中查詢到一個數(shù)據(jù)之后，再根據(jù)這個數(shù)據(jù)去另外一個數(shù)據(jù)庫中找對應的數(shù)據(jù)
• 事務問題 ：同一個數(shù)據(jù)庫中的表分布在了不同的數(shù)據(jù)庫中，如果單個操作涉及到多個數(shù)據(jù)庫，那么數(shù)據(jù)庫自帶的事務就無法滿足要求
• 分布式 id ：分庫之后， 數(shù)據(jù)遍布在不同服務器上的數(shù)據(jù)庫，數(shù)據(jù)庫的自增主鍵已經(jīng)沒辦法滿足生成的主鍵唯一了。需要為系統(tǒng)引入分布式 id

---

五 InnoDB 的鎖機制

該部分的參考

1 表鎖：S、X、IS、IX

• 意向鎖：
  當事務要在記錄上加上行鎖時，要首先在表上加上意向鎖，使得判斷表中是否有記錄正在加鎖更簡單
  意向鎖不與行級鎖發(fā)生沖突，因為它屬于表級鎖
  意向鎖之間不發(fā)生沖突
• 表級鎖的兼容矩陣：
  

2 行鎖：S、X

行鎖是加在索引上的，如果查詢語句不使用索引的話，那么它就會升級到表鎖，最終造成效率低下

• 共享鎖（S）：加了鎖的記錄，所有事務都能去讀取但不能修改，同時阻止其他事務獲得相同數(shù)據(jù)集的排他鎖
• 排他鎖（X）：允許已經(jīng)獲得排他鎖的事務去更新數(shù)據(jù)，阻止其他事務取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖

3 行鎖的進一步劃分

根據(jù)行鎖的位置不同進行劃分

Next-Key Lock

• 本質(zhì)上是 Gap Lock + Record Lock，左開右閉
• MySQL 默認隔離級別是RR，在這種級別下，如果使用 select in share mode 或者 select for update 語句（即當前讀），那么 InnoDB 會使用 Next-Key Lock，防止幻讀，在上面已經(jīng)討論過

Gap Lock

• 使用范圍條件而不是相等條件去檢索，并請求鎖時，InnoDB就會給符合條件的記錄的索引項加上鎖；而對于鍵值在條件范圍內(nèi)但并不存在的記錄，就叫做間隙，InnoDB此時也會對間隙加鎖
• 在 RU 和 RC 兩種隔離級別下，即使你使用 select in share mode 或 select for update，也無法防止幻讀。因為這兩種隔離級別下只會有行鎖，而不會有間隙鎖；而如果是 RR 隔離級別的話，就會在間隙上加上間隙鎖

Record Lock

• 最簡單的行鎖

Insert Intention Lock

• 插入意圖鎖是一種間隙鎖，在行執(zhí)行 INSERT 之前的插入操作設置。如果多個事務 INSERT 到同一個索引間隙之間，但沒有在同一位置上插入，則不會產(chǎn)生任何的沖突
• 假設有值為4和7的索引記錄，現(xiàn)在有兩事務分別嘗試插入值為 5 和 6 的記錄，在獲得插入行的排他鎖之前，都使用插入意向鎖鎖住 4 和 7 之間的間隙，但兩者之間并不會相互阻塞，因為這兩行并不沖突

---

六 SQL 查詢

1 語法

• select 的屬性（不包括函數(shù)使用的）必須在 group by 后面出現(xiàn)

select [distinct] from join on where group by having union order by limit

2 執(zhí)行順序

1. from # 首先進入from字句 2. on # 根據(jù)條件選擇需要連接的行 3. join # 執(zhí)行連接 4. where # 對連接結(jié)果過濾 5. group by # 分組（在分組之前執(zhí)行了 select 后面的 if、substring_index... 等非運算操作） 6. avg(), sum(), count()... behind select # 執(zhí)行函數(shù) 7. having # 對各個分組分別進行過濾 8. select # 選擇結(jié)果 9. distinct # 結(jié)果去重 10. union # 將當前結(jié)果并上其它結(jié)果 11. order by [asc, desc] # 排序 12. limit # 選擇指定行作為結(jié)果

3 常用函數(shù) / 關鍵字

day(date) / month(date) / year(date)：獲取日/月/年

# 獲取2021年8月里，每天的練習量 select day(date) day, count(*) question_cnt from question_practice_detail where year(date) = 2021 and month(date) = 8 group by day

if(cond, true_val, false_val)：條件正確時選擇前者，否則后者

# 分別查看 25歲以下和25歲及以上 兩個年齡段的用戶數(shù)量 # 這里先執(zhí)行了 if 再執(zhí)行了 group by select if(age>=25, '25歲及以上', '25歲以下') as age_cut, count(device_id) as number from user_profile group by age_cut # 復旦大學的每個用戶在8月份練習的總題目數(shù)和回答正確的題目數(shù)情況 # 主要問題是統(tǒng)計 result = 'right' 的行數(shù)，count(result = 'right') 是錯誤的寫法，應該 sum(if(result = 'right', 1, 0)) select t1.device_id, t1.university, count(t2.question_id) question_cnt, sum(if(t2.result = 'right', 1, 0)) right_question_cnt from user_profile t1 left join (select device_id, result, question_idfrom question_practice_detailwhere month(date) = 8) t2 on t1.device_id = t2.device_id where university = '復旦大學' group by device_id

case...when...then...else...end

# 將用戶劃分為20歲以下，20-24歲，25歲及以上三個年齡段 selectdevice_id,gender,casewhen age >= 25 then '25歲及以上'when age >= 20 then '20-24歲'when age < 20 then '20歲以下'else '其他'end age_cut from user_profile

union [all]：不加 all 會自動去重

substring(field, separator, index)：如果 index > 0 則取前綴，否則取后綴

upper(str)：轉(zhuǎn)為大寫

concat(str1, str2)：拼接字符串

substring(str, start, end)：子串，索引從1開始

總結(jié)

以上是生活随笔為你收集整理的后端学习 - MySQL存储引擎、索引与事务的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。