MySQL架構(gòu)

• MySQL是一個單進(jìn)程多線程架構(gòu)的數(shù)據(jù)庫

存儲引擎

InnoDB存儲引擎

• MySQL5.5.8之后默認(rèn)的存儲引擎寞冯，主要面向OLTP
• 支持事務(wù)
• 支持行鎖（有的情況下也會鎖住整個表）
• 非鎖定讀(默認(rèn)讀取操作不會產(chǎn)生鎖)
• 通過使用MVCC來獲取高并發(fā)性赋续，并且實(shí)現(xiàn)sql標(biāo)準(zhǔn)的4種隔離級別趟庄，默認(rèn)為可重復(fù)讀級別，使用一種被稱成next-key locking的策略來避免幻讀(phantom)現(xiàn)象
• 支持全文索引（InnoDB1.2.x - mysql5.6）
• 提供了插入緩存(insert buffer)伙菊、二次寫(double write)漫玄、自適應(yīng)哈希索引(adaptive hash index)、預(yù)讀(read ahead)等高性能技術(shù)
• 表數(shù)據(jù)采用聚集方式瞳遍，每張表的存儲都按主鍵的順序進(jìn)行存放闻妓。

MyISAM存儲引擎

• 不支持事務(wù)，主要面向一些OLAP
• 支持全文索引
• 緩沖池只緩沖索引文件掠械，而不緩沖數(shù)據(jù)文件
• 存儲引擎表由MYD和MYI組成由缆，MYD用來存放數(shù)據(jù)文件，MYI用來存放索引文件.

NDB存儲引擎

• 是一個集群存儲引擎猾蒂，其特點(diǎn)是數(shù)據(jù)全部放在內(nèi)存中均唉。
• 主鍵查找速度極快，并通過添加NDB數(shù)據(jù)庫存儲節(jié)點(diǎn)可以線性提高數(shù)據(jù)庫性能肚菠，是高可用舔箭，高性能的集群系統(tǒng)

InnoDB體系架構(gòu)

后臺線程

• Master Thread：負(fù)責(zé)將緩沖池中的數(shù)據(jù)異步刷新到磁盤，保證數(shù)據(jù)的一致性
• IO Thread：負(fù)責(zé)IO請求的回調(diào)處理
• Purge Thread：回收已經(jīng)使用并分配的undo頁（事務(wù)提交后蚊逢，其所使用的undolog不再需要）

內(nèi)存池

• 緩沖池（一塊內(nèi)存區(qū)域）
  1）InnoDB基于磁盤层扶，將記錄按照頁的方式進(jìn)行管理（引入緩沖池提高性能）
  2）讀取頁：先從緩沖池獲取，緩沖池沒有烙荷，才會從磁盤獲取
  3）修改頁：先寫redo日志緩沖镜会，再修改緩沖池中的頁，然后以一定的頻率刷新到磁盤（Checkpoint機(jī)制）终抽，在沒有刷新到磁盤之前戳表，該頁被稱為臟頁
  4） innodb_buffer_pool_size：設(shè)置大小
  5）存放索引頁桶至、數(shù)據(jù)頁、自適應(yīng)hash索引和lock信息
  6）緩沖池可以配置多個（innodb_buffer_pool_instances）匾旭，每個頁根據(jù)hash值平均分配到不同的緩沖池實(shí)例中镣屹，用于減少數(shù)據(jù)庫內(nèi)部資源競爭
• LRU List
  將最新的頁放在隊(duì)列前端，最近最少使用的放在尾端季率，當(dāng)緩沖池不夠用時野瘦，將尾端的頁刪除出緩沖池（如果此頁是臟頁描沟，會先刷新到磁盤）飒泻。innodb采用的是midpoint技術(shù)進(jìn)行LRU
• Flush List
  臟頁列表
• 重做日志redolog緩沖
  1）為了防止臟頁在刷新到磁盤時宕機(jī)，必須先redolog吏廉，再修改頁
  2）數(shù)據(jù)庫發(fā)生宕機(jī)時泞遗，通過redolog完成數(shù)據(jù)的恢復(fù)（ACID-D持久性）
  3）默認(rèn)大小8M，通過innodb_log_buffer_size
  4）何時將redolog緩沖刷新到redolog文件：①master將redolog緩沖每隔1s刷新到redolog文件中②事務(wù)提交③redolog緩沖池剩余空間小于1/2
• Checkpoint
  1）緩沖池不夠用時席覆，將臟頁刷新到磁盤
  2）數(shù)據(jù)庫宕機(jī)時史辙，只需要重做Checkpoint之后的日志，縮短數(shù)據(jù)庫的恢復(fù)時間
  3）redolog不可用時佩伤，將臟頁刷新到磁盤

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

innodb邏輯結(jié)構(gòu).png

表空間

• 默認(rèn)情況下聊倔，只有一個表空間ibdata1，所有數(shù)據(jù)存放在這個空間內(nèi)
• 如果啟用了innodb_file_per_table生巡，則每張表內(nèi)的數(shù)據(jù)可以單獨(dú)放到一個表空間內(nèi)耙蔑，每個表空間只存放數(shù)據(jù)、索引和InsertBuffer Bitmap頁孤荣，其余還在ibdata1

Segment段（InnoDB引擎自己控制）

• 數(shù)據(jù)段：B+ tree的葉子節(jié)點(diǎn)
• 索引段：B+ tree的非葉子節(jié)點(diǎn)
• 回滾段

Extent區(qū)

• 每個區(qū)的大小為1M甸陌，一個區(qū)一共有64個連續(xù)的頁（區(qū)的大小不可調(diào)節(jié)）

頁

• InnoDB磁盤管理的最小單位
• 默認(rèn)每個頁大小為16KB，可以通過innodb_page_size來設(shè)置（4/8/16K）
• 每個頁最多存放7992行數(shù)據(jù)

Row行

索引

hash索引

• 定位數(shù)據(jù)只需要一次查找盐股，時間復(fù)雜度O(1)
• 只可用于等值查詢钱豁，不可用于范圍查詢
• 自適應(yīng)hash索引：InnoDB會自動的根據(jù)訪問頻率和模式來自動的為某些熱點(diǎn)頁建立hash索引，默認(rèn)是開啟的疯汁，不能人為干預(yù)是否在表中生成哈希索引牲尺。

B+樹索引

• B+索引在數(shù)據(jù)庫中有一個特點(diǎn)是高扇出性
• 樹的高度一般為2-4層，需要2-4次查詢（100w和1000w行數(shù)據(jù)幌蚊，如果B+ tree都是3層谤碳，那么查詢效率是一樣的）
• B+樹索引并不能找到一個給定鍵值的具體行。B+樹索引能找到的只是被查找數(shù)據(jù)行所在的頁霹肝。然后數(shù)據(jù)庫通過把頁讀入到內(nèi)存估蹄，再在內(nèi)存中進(jìn)行查找，最后得到要查找的數(shù)據(jù)沫换。
• B+樹索引可以分為聚集索引（clustered index）和輔助索引（secondary index）臭蚁，但是不管是聚集還是輔助索引最铁，其內(nèi)部都是B+樹的，即高度平衡的垮兑，葉子節(jié)點(diǎn)存放著所有的數(shù)據(jù)冷尉。聚集索引和輔助索引不同的是，葉子節(jié)點(diǎn)存放的是否是一整行的信息系枪。

聚集索引

• 聚集索引按照每張表的主鍵構(gòu)造一棵B+樹雀哨。
• 葉子節(jié)點(diǎn)中存放的是整張表的行記錄數(shù)據(jù)，葉子節(jié)點(diǎn)也成稱為數(shù)據(jù)頁私爷。
• 索引組織表中數(shù)據(jù)也是索引的一部分雾棺。每個數(shù)據(jù)頁通過一個雙向鏈表來進(jìn)行鏈接。聚集索引能夠特別快的訪問針對范圍值的查詢衬浑。

聚集索引圖.png

• 上圖中捌浩，根節(jié)點(diǎn)部分的Key:80000001代表主鍵為1。Pointer:0004代表指向數(shù)據(jù)頁的頁號（即第4頁）工秩。數(shù)據(jù)頁節(jié)點(diǎn)的的PageOffset:0004代表第4頁尸饺，其中存儲的數(shù)據(jù)是完整的每一行。

輔助索引

• 葉子節(jié)點(diǎn)存放的也是行記錄數(shù)據(jù)所在的頁助币，但還是頁中存放的不是完整的行浪听，而是僅僅是一對key-value和一個指針，該指針指向相應(yīng)行數(shù)據(jù)的聚集索引的主眉菱。
• 假設(shè)輔助索引樹高3層迹栓，聚集索引樹為3層，那么根據(jù)輔助索引查找數(shù)據(jù)倍谜，需要先經(jīng)過3次IO找到主鍵迈螟，再經(jīng)過3次IO找到行做在的數(shù)據(jù)頁
• 針對輔助索引的插入和更新操作：輔助索引頁如果在緩沖池中，則插入尔崔；若不在答毫，則點(diǎn)放到InsertBuffer對象中，之后在以一定的平率進(jìn)行InsertBuffer和輔助索引頁子節(jié)點(diǎn)的合并

輔助鎖索引圖.png

• 圖中季春，idx_c表示對第c列做了索引洗搂；idx_c中的Key:7fffffff代表c列的一個值，其實(shí)是-1载弄；idx_c中的Pointer:80000001代表該行的主鍵是80000001耘拇，即1；下面的就是聚集索引部分宇攻。

聯(lián)合索引
覆蓋索引

鎖

InnoDB存儲引擎中的鎖

鎖的類型：

InnoDB存儲引擎實(shí)現(xiàn)了如下兩種標(biāo)準(zhǔn)的行級鎖

• 共享鎖：S lock惫叛，事務(wù)T1獲取了r行的S鎖，事務(wù)T2也可以獲取r行的S鎖
• 排他鎖：X lock逞刷，事務(wù)T1獲取了r行的S鎖嘉涌，事務(wù)T2就不能獲取r行的X鎖妻熊；事務(wù)T1獲取了r行的X鎖，事務(wù)T2就不能獲取r行的X/S鎖
• InnoDB存儲引擎支持多粒度鎖定仑最，這種鎖定允許在行級上的鎖和表級上的鎖同時存在扔役。為了支持在不同粒度上進(jìn)行加鎖操作，InnoDB存儲引擎支持一種額外的鎖方式警医，即意向鎖亿胸。
• 意向鎖是表級別的鎖，其設(shè)計目的主要是為了在一個事務(wù)中揭示下一行將被請求的鎖的類型：
  1）意向共享鎖(IS Lock）预皇，事務(wù)想要獲得一個表中某幾行的共享鎖侈玄。
  2）意向排它鎖（IX Lock），事務(wù)想要獲得一個表中某幾行的排它鎖深啤。
  因?yàn)镮nnoDB支持的是行級別的鎖拗馒，所以意向鎖其實(shí)不會阻塞除全表掃以外的任何請求路星。

一致性的非鎖定讀操作

• 通過行多版本并發(fā)控制（MVCC）的方式來讀取當(dāng)前執(zhí)行時間數(shù)據(jù)庫中行的數(shù)據(jù)溯街。如果讀取的行正在執(zhí)行DELETE、UPDATE操作洋丐，這時讀取操作不會因此等待行上的鎖釋放呈昔，相反，存儲引擎會去讀取一個快照數(shù)據(jù)友绝。
  快照數(shù)據(jù)是指該行之前版本的數(shù)據(jù)堤尾，該實(shí)現(xiàn)是通過Undo段來實(shí)現(xiàn)。而Undo用來在事務(wù)中回滾數(shù)據(jù)迁客，因而快照數(shù)據(jù)本身是沒有額外的開銷郭宝。此外，讀取快照數(shù)據(jù)是不必要上鎖的掷漱，因?yàn)闆]有必要對歷史的數(shù)據(jù)進(jìn)行修改粘室。
• 在Repeatable Read事務(wù)隔離級別下，對于快照數(shù)據(jù)卜范，總是讀取事務(wù)開始時的行數(shù)據(jù)版本衔统。
• 在Read Comitted事務(wù)隔離級別下，對于快照數(shù)據(jù)海雪，總是讀取被鎖定行的最新一份快照數(shù)據(jù)锦爵。
• 對于Read Commited的事務(wù)隔離級別而言，其實(shí)違反了事務(wù)的隔離性奥裸。
  鎖定讀操作：
• SELECT…FOR UPDATE 對讀取的行記錄加一個X鎖险掀。其他事務(wù)想在這些行上加任何鎖都會被阻塞。
• SELECT…LOCK IN SHARE MODE 對讀取的行記錄加一個S鎖湾宙。其他事務(wù)可以向鎖定的記錄加S鎖樟氢，但是對于加X鎖枝恋，則會被阻塞。

鎖的算法

InnoDB存儲引擎有3種行鎖

• Record Lock：單個行記錄上的鎖嗡害，鎖定的對象是索引焚碌，而不是數(shù)據(jù)。
• Gap Lock：間隙鎖霸妹，鎖定一個范圍的索引十电，但不包含記錄本身
• Next-Key Lock： Gap Lock + Record Lock，鎖定一個范圍的索引叹螟，并且鎖定記錄本身鹃骂。
• Record Lock總是會鎖住索引記錄，如果InnoDB存儲引擎建立的時候沒有設(shè)置任何一個索引罢绽，這時InnoDB存儲引擎會使用隱式的主鍵來進(jìn)行鎖定畏线。
• 如：SELECT * FROM t WHERE a < 6 lock in share mode，該語句會鎖定（-oo良价， 6）這個數(shù)值區(qū)間的所有數(shù)值寝殴。

解決幻讀問題：

• 幻讀：同一事務(wù)下，連續(xù)執(zhí)行兩次同樣的SQL語句可能導(dǎo)致不同的結(jié)果明垢。第二次SQL語句可能會返回之前不存在的行蚣常。
• 可重讀讀下，InnoDB存儲引擎采用Next-Key Locking機(jī)制來避免幻讀痊银。
• 假如表t由1,2,5三個值組成抵蚊， where a > 2 ，被鎖住的不僅是5這個值溯革，而是（2贞绳，+∞）這個范圍加了X鎖
• 在讀提交級別下，僅采用Record Lock

鎖問題

臟讀：即一個事務(wù)可以讀到另一個事務(wù)中未提交的數(shù)據(jù)致稀，違反了數(shù)據(jù)庫的隔離性冈闭。發(fā)生條件：READ UNCOMMITED，這個隔離級別在Mysql中不使用豺裆。
不可重復(fù)讀：

• 一個事務(wù)兩次讀同一數(shù)據(jù)拒秘，結(jié)果不一樣。（另一個事務(wù)修改了改數(shù)據(jù)并提交）

• 不可重復(fù)讀和臟讀的區(qū)別是：臟讀是讀到未提交的數(shù)據(jù)臭猜；而不可重復(fù)讀讀到的確實(shí)是已經(jīng)提交的數(shù)據(jù)躺酒，但是其違反了數(shù)據(jù)庫事務(wù)一致性的要求。

• InnoDB的默認(rèn)事務(wù)隔離級別是READ REPEATABLE蔑歌，采用Next-Key Lock算法羹应，解決了不可重復(fù)讀（幻讀）問題。
  在Next-Key Lock 算法下次屠，不僅僅是鎖住掃描到的索引园匹，而且還鎖住這些索引覆蓋的范圍（gap）雳刺。因此對于這個范圍內(nèi)的插入都是不允許的。

死鎖

• 死鎖是指兩個或兩個以上的事務(wù)在執(zhí)行過程中裸违，因爭奪鎖資源而造成的一種相互等待的現(xiàn)象掖桦。
• 超時機(jī)制：當(dāng)一個事務(wù)等待超時，則對它進(jìn)行回滾供汛。但是有可能回滾的這個事務(wù)的時間要比另一個事務(wù)要多枪汪。（就是還不如回滾另一個沒超時的）
• InnoDB采用wait-for graph（等待圖）的方式來進(jìn)行死鎖檢測。

鎖升級

• 指將當(dāng)前鎖的粒度降低怔昨，比如行鎖升級為一個頁鎖雀久，或者將頁鎖升級為表鎖。
• InnoDB不存在鎖升級的問題趁舀。其根據(jù)每個事務(wù)訪問的每個頁對鎖進(jìn)行管理赖捌，采用的是位圖的方式。不管事務(wù)鎖住頁中的一個記錄還是多個記錄矮烹，其開銷是一樣的越庇。

事務(wù)

事務(wù)定義

事務(wù):事務(wù)指的是滿足 ACID 特性的一組操作，可以通過 Commit 提交一個事務(wù)擂送，也可以使用 Rollback 進(jìn)行回滾
ACID

• Atomicity 原子性: 數(shù)據(jù)庫事務(wù)是不可分割的工作單位, 要么都做, 要么都不做.
• Consistency 一致性: 事務(wù)不會破壞事務(wù)的完整性約束. 事務(wù)將數(shù)據(jù)庫從一個一致狀態(tài)轉(zhuǎn)變?yōu)榱硪粋€一致狀態(tài)
• Isolation 隔離性: 事務(wù)之間相互分離, 在提交之前相互不可見.
• Durability 持久性: 事務(wù)一旦提交, 其產(chǎn)生的效果就是永久的

事務(wù)的實(shí)現(xiàn)

• 事務(wù)的隔離性由鎖實(shí)現(xiàn)
• 原子性悦荒，一致性，持久性 由 redo log / undo log 實(shí)現(xiàn)
• undo log 保證事務(wù)的一致性, 邏輯日志, 根據(jù)每行記錄進(jìn)行記錄嘹吨，幫助事務(wù)回滾及MVCC
• redo log（重做日志）： 保證事務(wù)的原子性和持久性, 物理日志

redo log

• 由兩部分組成：內(nèi)存中的重做日志緩沖，易失的境氢。重做日志文件蟀拷，持久的。事務(wù)提交時萍聊，必須將事務(wù)的的所有日志寫入重做日志進(jìn)行持久化问芬。

undo log

• 記錄的是 SQL, undo 之后底層物理文件格式可能會改變。當(dāng)前事務(wù)通過undo讀取之前的行版本信息寿桨，來實(shí)現(xiàn)非鎖定讀此衅。undo log 會產(chǎn)生redo log，因?yàn)閡ndo log也需要持久性的保護(hù)亭螟。

purge

• 刪除并沒有刪除原數(shù)據(jù)挡鞍，只是delete flag置為1
• 若該行記錄已經(jīng)不被其他事務(wù)引用，則purge完成真正的刪除