MySQL

思維導圖

基礎

實際在 Innodb 存儲引擎中，會用一個特殊的記錄來標識最后一條記錄，該特殊的記錄的名字叫 supremum pseudo-record ，所以掃描第二行的時候，也就掃描到了這個特殊記錄的時候，會對該主鍵索引加next-key 鎖，最小記錄也有一個特殊的名字叫infimum record

MySQL記錄存儲

• 表空間結構

  • 段+區+頁+行

行格式

• Redundant

  • 太老了，沒人用了

• Compact

  • 相比起Redundant更緊湊

    • 重點理解這個

• Dynamic

  • 基于Compact進行改進，默認用這個

• Compressed

  • 基于Compact進行改進

Compact行格式

• 記錄的額外信息

  • 變長字段列表

    • 記錄頭信息指針指向下一個記錄的記錄頭信息和真實數據，逆序存儲

    • 使得位置靠前的記錄的真實數據和數據對應的字段長度信息可以同時在一個 CPU Cache Line 中，這樣就可以提高 CPU Cache 的命中率

  • NULL值列表

    • 用二進制比特位表示，逆序存儲，必須是字節的整數倍

• 記錄的真實數據

  • 隱藏字段

    • row_id

      • 如果沒有主鍵和唯一約束就需要這個字段，占6個字節

    • trx_id

      • 事務ID，表示這是哪個事務產生的記錄，占6個字節

    • roll_ptr

      • 上一個版本的指針，MVCC機制，占7個字節

varchar(n)的取值

• 單字段情況

  • 真實數據+真實數據所占字節數+NULL標記

    • 總共最大占用65535

      • ASCII

        • 65535 - 2 - 1 = 65532

      • UTF-8

        • 65532/3 = 21844

• 多字段情況

  • 所有字段的長度 + 變長字段字節數列表所占用的字節數 + NULL值列表所占用的字節數 <= 65535

行溢出

• MySQL存儲的基本單位是頁，一頁是16KB

  • 超過后的數據會存放到溢出頁中

  • Dynamic和Compressed的主要區別

事務

事務有哪些特性？

• 原子性

  • undo log

• 隔離性

  • MVCC+鎖

• 持久性

  • redo log

• 一致性

  • 上述仨

并發事務會引發什么問題？

• 臟讀

  • 讀取到未提交事務的數據

• 不可重復讀

  • 多次讀數據不一樣

• 幻讀

  • 多次讀條數不一樣

事務的隔離級別？

• 讀未提交

  • 可能會臟讀+不可重復度+幻讀

• 讀提交

  • 可能會不可重復讀+幻讀

• 可重復讀

  • 可能會幻讀

• 串行化

  • 不可能遇見問題

Read View在MVCC工作原理？

• 四個字段

• 隱藏列

  • trx_id

    • 當發生變化時，該事務的事務 id 記錄在 trx_id 隱藏列里

  • roll_pointer

    • 指向舊版本的記錄，方便undo log回溯

• 根據trx_id判斷

  • trx_id 值小于 Read View 中的 min_trx_id

    • 該版本的記錄對當前事務可見

  • trx_id 值大于 Read View 中的 max_trx_id

    • 該版本的記錄對當前事務不可見

  • 在這之間

    • 看在不在m_ids中

      • 在

        • 不可見

      • 不在

        • 可見

• 這種通過「版本鏈」來控制并發事務訪問同一個記錄時的行為就叫 MVCC（多版本并發控制）

讀提交是如何工作的？

• 執行語句生成一個Read View

可重復讀是如何工作的？

• 啟動事務生成一個Read View

幻讀如何解決

• 快照讀

  • MVCC解決幻讀

• 當前讀

  • next-key lock（記錄鎖+間隙鎖）方式解決了幻讀

    • 加鎖直接就阻塞了

可重復度無法完全解決幻讀

• 場景一

  • 事務A查不到記錄，事務B插入，事務A直接update，可以更新成功

• 場景二

  • 事務A使用快照讀查不到，事務B插入，事務A使用當前讀能查到

• 解決方式

  • 開啟事務后迅速update，插入next-key鎖

鎖

全局鎖

• 使用

  • flush tables with read lock

    • unlock tables

• 只能查詢

  • 主要用于數據庫的全庫邏輯備份

• 優化

  • 可重復讀的隔離級別

    • 備份數據庫之前先開啟事務

表級鎖

• 表鎖

  • 表鎖除了會限制別的線程的讀寫外，也會限制本線程接下來的讀寫操作

  • 盡量避免，顆粒度太大，使用InnoDB的行級鎖

• 元數據鎖

  • 讀鎖和寫鎖

    • 寫鎖優先級高于讀鎖

  • 在進行select和變更會自動添加，事務提交釋放

• 意向鎖

  • 意向鎖的目的是為了快速判斷表里是否有記錄被加鎖

• AUTO-INC鎖

  • 保證自增字段可以正確自增，語句執行后才釋放

  • 輕量級鎖：值自增后鎖釋放，但可能會主從沖突

  • 當 innodb_autoinc_lock_mode = 2 時，并且 binlog_format = row，既能提升并發性，又不會出現數據一致性問題

行級鎖

• Record Lock

  • 記錄鎖，僅僅把一條記錄鎖上

    • 無法防止插入(非唯一索引)

  • 分為S型和X型

    • 共享鎖和獨占鎖

• Gap Lock

  • 間隙鎖，鎖定一個范圍，但是不包含記錄本身

  • 只存在于可重復讀隔離級別

  • 目的是為了解決可重復讀隔離級別下幻讀的現象

• Next-Key Lock

  • 鎖定一個范圍，并且鎖定記錄本身

• 插入意向鎖

  • 當語句被插入間隙鎖后，如果還要插入語句，就會進入阻塞狀態，而在這個期間就會有插入意向鎖

MySQL加鎖方式

• 加鎖的對象是索引，加鎖的基本單位是 next-key lock

  • 可能會退化

  • 加鎖的本質是避免幻讀

  • 插入語句在插入一條記錄之前，需要先定位到該記錄在 B+樹 的位置，如果插入的位置的下一條記錄的索引上有間隙鎖，才會發生阻塞

• 加鎖的SQL語句

  • select … lock in share mode;

  • select … for update;

  • update table … where id = 1;

  • delete from table where id = 1;

• 行級鎖的種類

  • 讀已提交隔離級別

    • 記錄鎖

  • 可重復度隔離級別

    • 記錄鎖+間隙鎖

• MySQL加行級鎖

  • 唯一索引等值查詢

    • 存在

      • 該記錄索引的鎖退化為記錄鎖

    • 不存在

      • 該記錄索引的鎖退化為間隙鎖

  • 唯一索引范圍查詢

    • 大于等于且存在

      • 該記錄索引的鎖退化為記錄鎖

    • 小于或者小于等于

      • 存在

        • 小于

          • 該記錄索引的鎖退化為間隙鎖

        • 小于等于

          • 不退化

      • 不存在

        • 該記錄索引的鎖退化為間隙鎖

  • 非唯一索引等值查詢

    • 存在

      • 一定存在索引值相同的情況，因此需要全盤掃描

      • 對掃描到的二級索引記錄加的是 next-key 鎖

      • 在符合查詢條件的記錄的主鍵索引上加記錄鎖

      • 對第一個不符合條件的二級索引會退化成間隙鎖

        • 避免幻讀

    • 不存在

      • 掃描到第一條不符合條件的二級索引記錄
        該二級索引的 next-key 鎖會退化成X型間隙鎖

      • 不存在查詢條件的記錄，不會對主鍵索引加鎖

  • 非唯一索引范圍查詢

    • 不會進行退化

  • 沒有加索引的查詢

    • 把整個表都鎖住了

  • MySQL 記錄鎖+間隙鎖可以防止刪除操作而導致的幻讀

死鎖

• 兩個事務同時select … for update申請間隙鎖導致死鎖

• 間隙鎖的意義只在于阻止區間被插入，因此是可以共存的。一個事務獲取的間隙鎖不會阻止另一個事務獲取同一個間隙范圍的間隙鎖

• 而插入意向鎖本質上是一個特殊的間隙鎖，兩個事物不能同時有插入意向鎖和間隙鎖，因此死鎖

• 避免死鎖

  • 設置事務等待鎖的超時時間，超時回滾

  • 開啟主動死鎖檢測，自動回滾

隱式鎖

• 當事務需要加鎖的時，如果這個鎖不可能發生沖突，InnoDB會跳過加鎖環節，這種機制稱為隱式鎖

• 特殊情況會轉換為顯示鎖

  • 記錄之間加有間隙鎖

    • 生成插入意向鎖，等另外一個事務釋放間隙鎖

  • Insert 的記錄和已有記錄存在唯一鍵沖突

    • 給主鍵/唯一二級索引加上S型記錄鎖

內存

為什么要有Buffer Pool

• 每次從磁盤找數據效率太低，弄個緩存提升效率

Buffer Pool大小

• 默認是128MB

Buffer Pool存儲內容

• 以16KB的頁為單位

• 索引頁、數據頁、undo頁、插入索引頁、鎖信息、哈希

如何管理Buffer Pool

• InnoDB 為每一個緩存頁都創建了一個控制塊

  • 緩存頁的表空間、頁號、緩存頁地址、鏈表節點

• 使用一個Free鏈表，把空閑緩存頁鏈接到一起

• 當需要從磁盤加載一個頁到Buffer Pool時，就取一個空閑頁，填好信息，移出鏈表

管理臟頁

• 使用一個Flush鏈表，把臟頁管理起來

提高命中率

• 預讀失效

  • 新增一段old節點，預讀會放到old中，訪問了才放到young中

• Buffer Pool污染

  • 提高進入young的門檻

    • 在old區域停留一定時間才能放到young中

臟頁刷新時機

• redo log滿了

• Buffer Pool空間不足

• MySQL空閑

• MySQL正常關閉

日志

undo log(回滾日志)

• 用于事務回滾和MVCC，記錄事務更新前信息

  • 實現原子性

redo log(重做日志)

• 用于掉電故障恢復，記錄事務更新后信息

  • 實現持久化

• 為什么需要它，而不是直接寫磁盤？

  • 實現事務的持久性，保證crash-safe

  • 將寫操作從隨機寫變成了順序寫

• redo log刷盤時機？

  • MySQL正常關閉

  • 緩沖區數據大于最大限度一半時

  • 后臺線程每秒刷新一次

  • 事務提交時

• redo log寫滿了怎么辦？

  • 存儲引擎中存在重做日志文件組，其中包含2個重做日志文件組ib_logfile0和ib_logfile1

  • 以循環寫的方式進行寫入，刷新進去就剔除

  • 如果寫滿了，MySQL會阻塞

binlog(歸檔日志)

• 用于數據的備份和主從復制

• binlog和redo log的區別？

  • 適用對象不同

    • binlog是server層的日志

    • redo log是innodb專屬

  • 文件格式不同

    • binlog

      • STATEMENT

        • 每一條修改數據的 SQL 都會被記錄到 binlog 中，恢復再執行，now等函數會導致數據不一致

      • ROW

        • 記錄行數據最終被修改成什么樣了，會導致文件非常大

      • MIXED

        • 結合上述兩種模式，根據不同情況自動使用

    • redo log

      • 記錄物理日志，比如對 XXX 表空間中的 YYY 數據頁 ZZZ 偏移量的地方做了AAA 更新

  • 寫入方式不同

    • binlog是追加寫

      • 寫滿一個文件就換一個文件接著寫

    • redo log是循環寫

      • 日志大小固定，寫滿就重新開始

  • 用途不同

    • binlog用來備份恢復和主從復制

    • redo log進行掉電故障恢復

• 主從復制流程

  • 寫入binlog

    • 主庫把數據進行寫入binlog

  • 同步binlog

    • 從庫會創建一個線程接收主庫日志，寫入到中繼日志

  • 回放binlog

    • 從庫會創建線程讀取中繼日志，回放日志信息，實現主從一致

• 主從復制的模型

  • 同步復制

    • 主庫要等待所有從庫響應后再和客戶端響應，一般不用

  • 異步復制

    • 主庫不等待從庫響應，默認用的是這個，但主庫宕機數據就會發生丟失

  • 半同步復制

    • 主庫等待任意一個從庫響應就可以返回客戶端，這樣主庫宕機也有一個從庫有信息

• binlog刷盤時機？

  • 事務執行中把日志寫到binlog cache，事務提交后刷新

  • 一個事務只能有一個線程執行，每個線程都有binlog cache

兩階段提交

• 為什么需要兩階段提交

  • 事務提交后，redo log和binlog都要持久化到磁盤

  • 如果MySQL宕機

    • 只提交redo log，那么從庫數據會無法更新

    • 只提交binlog，那么主庫數據會無法更新

  • 解決方式

    • 兩階段提交把單個事務的提交拆分成了 2 個階段，準備和提交

• 具體過程

  • prepare 階段

    • 將內部 XA 事務的 ID寫入到redo log中

    • 將 redo log 持久化到磁盤

    • 更改redo log狀態為prepare

  • commit 階段

    • 將內部 XA 事務的 ID寫入到binlog中

    • 將 binlog 持久化到磁盤

    • 更改redo log狀態為commit

• 如果異常重啟會怎么樣？

  • MySQL重啟后會掃描redo log文件

  • 碰到prepare狀態，就去看binlog中是否含有該XID

    • 如果沒有，就說明binlog沒刷盤，回滾事務

    • 如果有，說明只是沒有提交，把事務提交即可

  • 兩階段提交是以 binlog 寫成功為事務提交成功的標識

• 兩階段提交存在的問題

  • 磁盤 I/O 次數高

    • 每次事務提交，兩個log都要和磁盤交互

  • 鎖競爭激烈

    • 為了保證兩個日志的提交順序一致，要加鎖

  • 解決方式

    • binlog組提交

      • 基本思路

        • 當有多個binlog要提交，合并為一組提交

      • 具體操作

        • flush階段

          • 把多個事務按binlog順序寫入文件

        • sync階段

          • 進行fsync操作，把文件刷新到磁盤

        • commit階段

          • 對各個事務按順序修改commit狀態

      • 優化

        • 每個階段用隊列進行管理，這樣鎖只需要管理隊列

    • redo log組提交

      • 備注

        • MySQL5.7版本開始才有redo log組提交的概念，在5.6版本中，redo log各自在prepare就完成了

        • 在MySQL5.7版本中，把redo log的prepare刷盤操作延遲到了commit的flush階段

      • 具體操作

        • flush階段

          • leader領導follower進行redo log刷盤

          • 再按照順序寫入binlog文件，不刷盤

        • sync階段

          • 進行等待，多等幾組數據一起進行刷盤

        • commit階段

          • 修改redo log的狀態

MySQL磁盤IO很高

• 原因

  • 事務提交時，需要將redo log和binlog刷新到磁盤，需要進行磁盤IO

• 解決方式

  • 延遲redo log和binlog的刷盤時機，減少IO次數

    • 設置組：延遲binlog的刷盤時機，多等一些數據一起進行刷盤

    • 將 sync_binlog 設置為大于 1 的值，多等幾個事務一起刷盤

    • redo log buffer里的redo log寫到redo log文件，讓操作系統進行刷盤

  • 如果掉電就可能丟數據 page cache不安全

索引

面試題

• 索引底層使用了什么數據結構和算法？

  • 考慮IO次數和時間復雜度

• 為什么MySQL InnoDB選擇B+樹作為索引數據結構？

  • 更矮胖

  • 插入刪除簡單

  • 便于范圍查找

• 什么時候適用索引？

• 什么時候不需要創建索引？

• 什么情況下索引會失效？

• 有什么優化索引的方法？

概念

• 索引是數據的目錄

分類

• 數據結構分類

  • B+樹索引

    • 優勢

      • 相比于 B 樹二叉樹或 Hash 索引結構

    • 如何進行聚簇索引和二級索引

  • Hash索引

  • Full-text索引

• 物理存儲分類

  • 聚簇索引

  • 二級索引

    • 回表

• 字段特性分類

  • 主鍵索引

  • 唯一索引

  • 前綴索引

• 字段個數分類

  • 單列索引

  • 聯合索引

    • 最左匹配原則

    • 全局有序和局部有序

    • 優化：select * from order where status = 1 order by create_time asc(文件排序 filesort)

創建索引的條件

• 什么時候適用索引？

  • 唯一性限制

  • where語句調用頻繁

  • order by和group by調用頻繁

• 什么時候不需要創建索引？

  • 不需要where、order by、group by

  • 重復數據多

  • 表中數據少

  • 更新頻繁的數據

索引失效

• 失效的條件

  • 使用左或者左右模糊匹配：like %xx，like %xx%

    • B+樹是按照索引值有序存儲，前綴匹配

  • 對索引列進行計算，函數等操作

    • 索引使用的是原始值，不是計算后的值

    • 隱式類型轉換

      • 索引值不能變，字符串->數字

  • 聯合索引不遵循最左匹配原則

    • 可能全是索引字段導致直接查詢聯合索引樹進行覆蓋索引

    • 索引下推

      • 過濾不符合要求的，減少回表次數

  • where后的條件，or前是索引列，or后不是

優化索引

• 前綴索引優化

  • 使用某個字段中字符串的前幾個字符建立索引

  • 對于較長的字符索引進行了更進一步的優化

• 覆蓋索引優化

  • 建立聯合索引，這樣可以避免回表帶來的IO性能損耗

• 主鍵索引最好自增

  • 新增一個節點直接向后插入，避免頁分裂

• 索引最好為NOT NULL

  • 索引列存在空會導致優化器進行優化難度增加

  • NULL無意義，但是會占據額外的存儲空間

• 防止索引失效

適合索引的數據結構

• 要求

  • 盡可能少的磁盤IO

  • 進行高效的單點查找和范圍查找

• 數據結構

  • 二分查找

  • 二叉搜索樹

  • AVL樹

  • 紅黑樹

  • B樹

    • 用戶的記錄數據的大小很有可能遠遠超過了索引數據，因此需要更多次磁盤IO找到有效數據

    • 使用 B 樹來做范圍查詢，需要使用中序遍歷，這會涉及多個節點的磁盤 I/O 問題

  • B+樹

    • 單點查詢

      • B樹可能可以直接就找到信息，但是浮動大

      • B+樹相較而言更加矮胖，磁盤IO次數少

    • 插入和刪除效率

      • B樹操作可能需要大變結構

      • B+樹操作只需要操作葉子節點

    • 范圍查詢

      • B樹沒有鏈表，需要多次IO進行范圍查詢

      • B+樹葉子節點使用鏈表連接，直接橫向搜索

count

• 按照性能排序

  • count(*) = count(1) > count(主鍵字段) > count(字段)

• count(主鍵字段)執行過程

• count(1)和count(*)執行過程

• count(字段)執行過程

• 優先使用二級索引，其次聚簇索引

• 優化count(*)

  • 使用近似值explain select…

  • 額外維護一個統計次數的表

因為存在兩個索引，一個是主鍵索引，一個是非唯一索引（二級索引），所以在加鎖時，同時會對這兩個索引都加鎖，但是對主鍵索引加鎖的時候，只有滿足查詢條件的記錄才會對它們的主鍵索引加鎖