第一章 MySQL體系結(jié)構(gòu)和存儲引擎

數(shù)據(jù)庫和數(shù)據(jù)庫實例：

• 數(shù)據(jù)庫是文件的集合，是依照某種數(shù)據(jù)模型組織起來存放于二進制存儲器中的數(shù)據(jù)集合。
• 數(shù)據(jù)庫實例是程序，是位于用戶與操作系統(tǒng)之間的一層數(shù)據(jù)管理軟件，用戶對數(shù)據(jù)庫的任何操作，包括數(shù)據(jù)庫定義、查詢、數(shù)據(jù)維護、數(shù)據(jù)庫運行控制等都是在數(shù)據(jù)庫實例下進行的。

MySQL組成

• 連接池組件
• 管理服務(wù)和工具組件
• SQL接口組件
• 查詢分析器組件
• 優(yōu)化器組件
• 緩沖組件
• 插件式存儲引擎
• 物理文件

存儲引擎

MySQL存儲引擎包括：

• InnoDB
• MyISAM
• NDB
• Memory
• Archive
• Federated
• Maria等。

各個引擎之間的差異主要體現(xiàn)在：存儲容量的限制、事務(wù)支持、鎖粒度、MVCC支持、支持的索引、備份和復(fù)制等。

第二章 InnoDB存儲引擎

InnoDB是MySQL最常用的存儲引擎。體系結(jié)構(gòu)主要概括為內(nèi)存和線程兩項。

image.png

后臺線程

• Master Thread：緩沖區(qū)異步刷新到磁盤，保證數(shù)據(jù)一致性。包括臟頁的刷新、合并插入緩沖區(qū)（INSERT BUFFER）、undo頁的回收等
• IO Thread：負責(zé)AIO（Async IO）處理寫IO請求的回調(diào)處理。包括四種：insert buffer，log，read，write
• Purge Thread：減輕Master Thread工作，負責(zé)回收已經(jīng)使用并分配的undo頁
• Page Cleaner Thread：減輕Master Thread的工作和對用戶查詢線程的阻塞，負責(zé)臟頁的刷新操作

內(nèi)存

image.png

內(nèi)存包括三個部分：

• 緩沖池
• 重做日志緩沖
• 額外的內(nèi)存池。

緩沖池

緩沖池中緩存的數(shù)據(jù)類型有：

• 索引頁
• 數(shù)據(jù)頁
• undo頁
• 插入緩沖（insert buffer）
• 自適應(yīng)哈希索引（adaptive hash index）
• InnoDB存儲的鎖信息（lock info）
• 數(shù)據(jù)字典信息（data dictionary）

其中索引頁和數(shù)據(jù)頁占很大一部分

緩沖池允許有多個實例，通過innodb_buffer_pool_instances配置

LRU List、Free List和Flush List

數(shù)據(jù)庫中的緩沖池是通過LRU（Latest Recent Used，最近最少使用）算法來管理的。但做了修改（優(yōu)化），新加入的數(shù)據(jù)放在列表的midpoint位置。
Free List保存的是當(dāng)前可用的頁列表
在LRU列表中的頁被修改后，稱該頁為臟頁（dirty page），即緩沖池中的頁和磁盤上的頁的數(shù)據(jù)產(chǎn)生了不一致。這時數(shù)據(jù)庫會通過checkpoint機制將臟頁刷新回在磁盤。所以Flush列表中的頁即為臟頁列表。

需要注意的是：臟頁既存在于LRU列表中，頁存在于Flush列表中。LRU列表用來管理緩沖池中頁的可用性，F(xiàn)lush列表用來管理將頁刷新回磁盤，二者互不影響

重做日志緩沖（redo log buffer）

通常情況，默認(rèn)8M的重做日志緩沖池足以滿足絕大部分的應(yīng)用，因為下列三種情況會將重做日志緩沖的內(nèi)容刷新到外部磁盤的重做日志文件中：

• Master Thread每秒刷新一次
• 每個事務(wù)提交時會刷新
• 當(dāng)重做日志緩沖池剩余空間小于1/2時，重做日志緩沖刷新到重做日志文件

額外的內(nèi)存池

存儲一些緩沖池相關(guān)的信息，十分重要。

Checkpoint（檢查點）

技術(shù)要解決的問題：

• 縮短數(shù)據(jù)庫恢復(fù)時間
• 緩沖池不夠用時，將臟頁刷新到磁盤；
• 重做日志不可用時，刷新臟頁。

當(dāng)數(shù)據(jù)庫發(fā)生宕機時，數(shù)據(jù)庫不需要重做所有的日志，因為Checkpoint之前的頁都已經(jīng)刷新回磁盤。故數(shù)據(jù)庫秩序啊喲對Ceheckpoint后的重做日志進行恢復(fù)。這樣記憶大大縮短了恢復(fù)的時間。

InnoDB存儲引擎內(nèi)部，有兩種Checkpoint：

• Sharp Checkpoint：發(fā)生在數(shù)據(jù)庫關(guān)閉時將所有的臟頁都刷新回磁盤
• Fuzzy Checkpoint：只刷新一部分臟頁，而不是刷新所有的臟頁回磁盤

在InnoDB存儲引擎中，通過LSN（Log Sequence Number）來標(biāo)記版本。LSN時8個字節(jié)的數(shù)字（64位）。每個頁有LSN，重做日志中也有LSN，Checkpoint也有LSN。

Fuzzy Checkpoint可能發(fā)生在以下幾種情況：

• Master Thread Checkpoint
• FUSH_LRU_LIST Checkpoint
• Async/Sync Flush Checkpoint
• Dirty Page too much Checkpoint

Master Thread工作方式

InnoDB的關(guān)鍵特性

插入緩沖

兩次寫

自適應(yīng)哈希索引

異步IO

刷新鄰接頁等

第三章 文件

InnoDB存儲引擎表中包含的文件種類：參數(shù)文件、日志文件、socket文件、pid文件、MySQL表結(jié)構(gòu)文件、存儲引擎文件。

InnoDB存儲引擎文件

重做日志文件

采用多個文件循環(huán)寫的目的：寫滿的日志可以進行歸檔另存為等操作，但一般都沒有歸檔。

當(dāng)數(shù)據(jù)庫主機由于掉電導(dǎo)致實例失敗，InnoDB存儲引擎會使用重做日志恢復(fù)到掉電之前的時刻，來保證數(shù)據(jù)的完整性。

第四章 表

索引組織表

當(dāng)沒有顯示定義主鍵時：

• 選取表中第一個定義的 非空 唯一索引為主鍵
• 若沒有上述索引，則自動創(chuàng)建一個6字節(jié)大小的指針。

但采用單列唯一非空索引作為主鍵時，可以使用_rowid搜索出來。多列索引則不能使用這個列名稱。

InnoDB邏輯存儲結(jié)構(gòu)

image.png

InnoDB邏輯空間最高層為表空間。表空間向下可層層劃分為：

• 段（Segment）
• 區(qū)（Extent）
• 頁（Page）/塊（block）

表空間

表空間可以看作是InnoDB存儲引擎邏輯結(jié)構(gòu)的最高層，所有數(shù)據(jù)都存放在表空間中。

innodb_file_per_table=1可以讓每張表內(nèi)的數(shù)據(jù)單獨存放到一個表空間內(nèi)。每張表空間內(nèi)存放的只是：

• 數(shù)據(jù)
• 索引
• 插入緩沖Bitmap頁

但是：
其他類的信息還是存放在原來的共享表空間內(nèi)，例如：

• 回滾（undo）信息
• 插入緩沖索引頁
• 系統(tǒng)事務(wù)信息
• 二次寫緩沖（Double write buffer）

段

表空間由段組成，常見的段有：

• 數(shù)據(jù)段（B+樹的葉子結(jié)點）
• 索引段（B+樹的非索引節(jié)點）
• 回滾段

區(qū)

由連續(xù)頁組成，每個區(qū)大小為1MB，默認(rèn)InnoDB存儲引擎頁大小為16KB，即一個區(qū)中共有64個連續(xù)的頁。此外頁的大小可以設(shè)置，所以一個區(qū)中頁的各種會變化，但區(qū)的大小總是1MB。

頁

頁是InnoDB磁盤管理的最小單位。可以通過innodb_page_size設(shè)置頁的大?。?K, 8K, 16K
在InnoDB存儲引擎中，常見的頁類型有：

• 數(shù)據(jù)頁（B-tree Node）
• undo頁（undo Log Page）
• 系統(tǒng)頁（System Page）
• 事務(wù)數(shù)據(jù)頁（Transaction system Page）
• 插入緩沖位圖頁（Insert Buffer Bitmap）
• 插入緩沖空閑列表頁（Insert Buffer Free List）
• 未壓縮的二進制大對象頁（Uncompressed BLOB Page）
• 壓縮的二進制大對象頁（Compressed BLOB Page）

行

InnoDB存儲引擎是面向行的（row-oriented）

InnoDB行記錄格式

• Compact
• Redundant格式為了兼容舊版

Compact行記錄格式

image.png

NULL標(biāo)志位使用位來表示后續(xù)列中哪些是NULL，默認(rèn)是1字節(jié)，但列增多的話會繼續(xù)以1字節(jié)位單位擴充，高位補零。所以NULL的列越多，專門用來記錄NULL需要的字節(jié)越多。不建議將字段設(shè)置可以為NULL

行溢出數(shù)據(jù)

一般情況下，InnoDB存儲引擎的數(shù)據(jù)都是存放在頁類型為B-tree node中，但當(dāng)發(fā)生行溢出時，數(shù)據(jù)存放在頁類型為Uncompress BLOB頁中。

第五章 索引與算法

如果索引太多，應(yīng)用程序的性能可能會收到影響，而索引太少，對查詢性能又會產(chǎn)生影響。

InnoDB存儲引擎索引概述

InnoDB存儲引擎支持：

• B+樹索引
• 全文索引
• 哈希索引

B+樹

B+樹是為磁盤或者其他直接存取輔助設(shè)備設(shè)計的一種平衡查找樹。

維持平衡的策略

• 拆分頁：當(dāng)頁滿的時候
• 旋轉(zhuǎn)：當(dāng)前頁滿，但左右兄弟節(jié)點沒有滿。減少了頁的拆分操作，同時樹的高度依然不變

Cardinality值

Cardinality表示索引中不重復(fù)記錄數(shù)量的預(yù)估值。在實際應(yīng)用中，Cardinality/n_rows_in_table應(yīng)盡可能接近1。

InnoDB存儲引擎的Cardinality統(tǒng)計

Cardinality的統(tǒng)計是放在存儲引擎層進行的。
僅在INSERT和UPDATE兩個操作中可能發(fā)生更新：

• 表中的1/16的數(shù)據(jù)已經(jīng)發(fā)生過變化
• stat_modified_counter > 2 000 000 000
  更新的計算是通過采樣計算

B+樹索引的使用

不同應(yīng)用中B+樹索引的使用

根據(jù)需求設(shè)計索引

聯(lián)合索引

如果沒有排序需求，查詢優(yōu)化器優(yōu)先使用單個值的索引，因為同樣大小的頁上可以存放更多的記錄。如果有兩個條件，第二個是排序的則會使用聯(lián)合索引。

覆蓋索引

從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。
好處是：

• 覆蓋索引不包含整行記錄的所有信息，故其大小要遠小于聚集索引，因此可以減少大量的IO操作。
• 執(zhí)行select count(*) from buy_log; 操作時，使用輔助索引數(shù)據(jù)量比較小，可以減少IO操作

對于索引(a,b)當(dāng)查詢b的范圍統(tǒng)計時，有可能使用。雖然b在索引的第二個字段。

優(yōu)化器選擇不使用索引的情況

對于不能進行索引覆蓋的情況，優(yōu)化器選擇輔助索引的情況是，通過輔助索引查找的數(shù)據(jù)是少量的。這是由于當(dāng)前傳統(tǒng)機械硬盤的特性所決定的，即利用順序讀來替換隨機讀的查找。

如果使用固態(tài)硬盤，可以使用force index來強制使用某個索引

索引提示

Select a,b from t USER INDEX(a) where a = 1 and b = 2; 建議使用索引a，但優(yōu)化器可能不聽。
Select a,b from t FORCE INDEX(a) where a = 1 and b = 2; 強制使用索引a。

Multi_Range Read 優(yōu)化

先查到輔助索引和對應(yīng)的RowID緩存到內(nèi)存中，按照RowID排序，再查詢聚集索引時就變成了盡量的順序訪問了。

Index Condition Pushdown（ICP）優(yōu)化

在使用索引獲取數(shù)據(jù)的同時使用where條件過濾數(shù)據(jù)。將過濾操作盡量放在存儲引擎層。

哈希算法

InnoDB存儲引擎中的哈希算法

對于緩沖池哈希表來說，在緩沖池中的Page頁都有一個chain指針，指向相同哈希函數(shù)值的頁。使用除法散列計算槽的位置，而槽的個數(shù)先澤略大于2倍緩沖池頁數(shù)量的質(zhì)數(shù)。

選質(zhì)數(shù)是為了散列更均勻，算法導(dǎo)論上有講。

自適應(yīng)哈希索引

全文檢索

概述

從InnoDB 1.2.X版本開始，InnoDB存儲引擎開始支持全文索引，其支持MyISAM存儲引擎的全部功能，并且還好支持其他一些特性。

InnoDB全文索引

Full Inverted Index，表現(xiàn)形式為{單詞, (單詞所在文檔的ID， 在具體文檔中的位置)}
InnoDB支持的全文索引采用Full Inverted Index

全文檢索

相關(guān)性計算：

• word是否在文檔中出現(xiàn)
• word在文檔中出現(xiàn)的次數(shù)
• word在索引列中的數(shù)量
• 多少個文檔包含該word

Elasticsearch相關(guān)度計算（評分）：

• 詞頻
• 逆向文檔頻率
• 文檔長度歸一值

ElasticSearch相關(guān)性打分機制