InnoDB架構(gòu)

InnoDB

內(nèi)存架構(gòu)

1. Buffer Pool
   主內(nèi)存的一塊區(qū)域两残，InnoDB在表和索引數(shù)據(jù)被訪問的時候緩存它。在專用服務(wù)器上，高達(dá)80%的物理內(nèi)存通常分配給buffer pool。配置項：innodb_buffer_pool_size瓶竭，正常推薦設(shè)置50%-75%的系統(tǒng)內(nèi)存
   實現(xiàn)為page鏈表，緩存里很少的數(shù)據(jù)會使用LRU算法過時掉
   
   實現(xiàn)

2. Change Buffer
   當(dāng)二級索引page不在buffer pool時渠羞，緩存對這些二級索引page的更改。對于buffer的更改智哀，稍后當(dāng)page被其他讀操作加載進(jìn)buffer pool的時候次询，會進(jìn)行合并

   
   
   Change Buffer
   
   

   在內(nèi)存中，change buffer占用部分的buffer pool瓷叫。在磁盤上屯吊，change buffer是系統(tǒng)表空間的一部分，當(dāng)數(shù)據(jù)庫服務(wù)器關(guān)閉的時候摹菠，索引的更改將在其中進(jìn)行緩存

3. Adaptive Hash Index
   自適應(yīng)Hash索引盒卸，使InnoDB在具有適當(dāng)?shù)墓ぷ髫?fù)載和有足夠內(nèi)存的buffer pool組合的系統(tǒng)上，執(zhí)行起來更像內(nèi)存中的數(shù)據(jù)庫次氨，而不會犧牲事務(wù)特性和可靠性蔽介。
   根據(jù)觀察到的搜索模式，使用索引key的前綴建立hash索引煮寡。hash索引是根據(jù)需要為經(jīng)常需要訪問的索引page構(gòu)建的虹蓄。
   如果一個表差不多可以整個放進(jìn)主內(nèi)存，那么hash索引通過啟用任何元素的直接查找來加快查詢幸撕，將索引值轉(zhuǎn)換為一種指針薇组。
4. Log Buffer
   持有將要被寫入磁盤上的日志文件的內(nèi)存區(qū)域。log buffer的內(nèi)容會定期刷寫到磁盤坐儿。默認(rèn)16MB律胀。大的log buffer能讓大的事務(wù)運行而不需要在事務(wù)提交前將redo log數(shù)據(jù)寫到磁盤上宋光。所以如果有事務(wù)更新、插入或者刪除很多行炭菌，增大log buffer能減少磁盤IO

磁盤結(jié)構(gòu)

1. 表 Tables

2. 索引 Indexes
   聚簇索引能加快查詢罪佳，因為索引搜索能直接指向包含行數(shù)據(jù)的page。如果表很大娃兽，聚簇索引通常能夠節(jié)省磁盤IO操作
   聚簇索引之外的其他所有叫做二級索引菇民。在innodb里，二級索引的每個記錄都包含了行的主鍵列投储，以及為二級索引指定的列
   如果主鍵key很長第练，二級索引也會使用更多的空間
   InnoDB索引是B-Tree結(jié)構(gòu)，空間索引是R-Tree結(jié)構(gòu)玛荞。索引記錄存儲在B-Tree或R-Tree的葉子page上

3. 表空間 Tablespaces

• system表空間
  存儲change buffer的區(qū)域娇掏。如果表創(chuàng)建在system表空間而不是file-per-table或general表空間，它也可能存儲表和索引數(shù)據(jù)勋眯。
  它可以有一個或多個數(shù)據(jù)文件婴梧，默認(rèn)是單個系統(tǒng)表空間數(shù)據(jù)文件，名稱為ibdata1

• File-Per-Table表空間
  包含單個InnoDB表的數(shù)據(jù)和索引客蹋，存儲在文件系統(tǒng)的單個數(shù)據(jù)文件里
  文件名為：table_name.ibd

• General表空間
  通過使用CREATE TABLESPACE語法創(chuàng)建的共享InnoDB表空間
  和system表空間類似塞蹭，能夠存儲多個表的數(shù)據(jù)的共享表空間
  相對于file-per-table表空間，通用表空間有潛在的內(nèi)存優(yōu)勢讶坯。服務(wù)器會在表空間的生命周期內(nèi)將表空間的元數(shù)據(jù)保留在內(nèi)存內(nèi)番电，多個表在數(shù)量更少的通用表空間的占用表空間元數(shù)據(jù)的內(nèi)存會更少

• Undo表空間
  包含undo日志，它是記錄的集合辆琅，包含了怎么撤銷事務(wù)對聚簇索引最新的修改的信息
  Undo日志默認(rèn)存儲在system表空間漱办，但也可以存儲在一個或多個undo表空間里

• Temporary表空間
  包含Session臨時表空間，一個全局臨時表空間
  session臨時表空間存儲著用戶創(chuàng)建的臨時表婉烟，以及當(dāng)innodb配置為磁盤內(nèi)部臨時表存儲引擎時優(yōu)化器創(chuàng)建的內(nèi)部臨時表(從MYSQL8.0.16起磁盤內(nèi)部臨時表使用的存儲引擎永遠(yuǎn)是InnoDB)
  全局臨時表空間(ibtmp1)存儲用戶創(chuàng)建的臨時表所做更改的回滾塊

4. Doublewrite Buffer
   是個存儲區(qū)域娩井，InnoDB在將page寫入InnoDB數(shù)據(jù)文件中的適當(dāng)位置之前，會在其中寫入從buffer pool中刷新的page似袁。如果在page寫的過程中有操作系統(tǒng)洞辣、存儲子系統(tǒng)，或者mysqld的異常退出叔营，InnoDB可以在崩潰恢復(fù)期間從double buffer找到一個好的副本

5. Redo Log
   是一種基于磁盤的數(shù)據(jù)結(jié)構(gòu)屋彪，用于在崩潰恢復(fù)期間用于更正由于不完整的事務(wù)寫入的數(shù)據(jù)。在異常停機(jī)前沒有完成更新數(shù)據(jù)文件的修改绒尊，在初始化期間和接收連接之前會自動重放
   默認(rèn)在磁盤上有ib_logfile0和ib_logfile1畜挥。MYSQL以循環(huán)方式寫入redo log

6. Undo Logs
   一個undo log是與單個讀寫事務(wù)關(guān)聯(lián)的undo log記錄集合。一個undo log記錄包含了如何撤銷一個事務(wù)對一條聚簇索引記錄最近修改的信息婴谱。如果其他事務(wù)需要將原始事務(wù)視為一致性讀的一部分蟹但，則會從undo log記錄中檢索出未修改的數(shù)據(jù)躯泰。
   undo logs存在于undo log段里，undo log段包含于回滾段里华糖，回滾段駐留于system表空間麦向、undo表空間和全局臨時表空間
   駐留于全局臨時表空間中的undo logs用于在用戶定義的臨時表里修改數(shù)據(jù)的事務(wù)。這些undo logs沒有被redo logged客叉，因為崩潰恢復(fù)不需要它們诵竭。它們只用于在服務(wù)器運行期間回滾。這種類型的undo logs通過避免redo logging的IO而提升了性能

一個事務(wù)最多分配4個undo logs兼搏，每個都是下面的操作類型之一：

1. INSERT操作卵慰，用戶定義的表
2. UPDATE和DELETE操作，用戶定義的表
3. INSERT操作佛呻，用戶定義的臨時表
4. UPDATE和DELETE操作裳朋，用戶定義的臨時表

InnoDB鎖和事務(wù)模型

InnoDB鎖

共享鎖和排他鎖

• 共享鎖 shared (s)鎖
  允許持有鎖的事務(wù)讀取一行
• 排他鎖 exclusive (x)鎖
  運行持有鎖的事務(wù)更新或者刪除行

意向鎖 Intention Locks

表級鎖，用于指示事務(wù)稍后在表里需要哪種類型的鎖(共享或排他)

• intention shared lock (IS)表明事務(wù)將在表里獨立的行上設(shè)置共享鎖
• intention exclusive lock (IX)表明事務(wù)將在表里獨立的行上設(shè)置排他鎖

SELECT ... FOR SHARE設(shè)置IS鎖吓著，SELECT ... FOR UPDATE設(shè)置IX鎖

事務(wù)在能獲取表里行的共享鎖之前鲤嫡，必須先獲取表的IS鎖或更強(qiáng)的鎖
事務(wù)在能獲取表里行的排他鎖之前，必須先獲取表的IX鎖或更強(qiáng)的鎖

記錄鎖 Record Locks

在索引記錄上的鎖
記錄鎖始終鎖定索引記錄绑莺，即使表上沒有定義索引暖眼。這種情況下，InnoDB會創(chuàng)建一個隱式的聚簇索引纺裁，使用這個索引鎖定記錄

間隙鎖 Gap Locks

索引記錄間隙之間的鎖罢荡，或者第一個索引記錄之前或最后一個索引記錄之后的間隙的鎖
e.g. SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
會阻止其他事務(wù)插入15的值到t.c1列，而不用管在這個列中有沒有這個值

對于使用唯一索引搜索唯一行的語句对扶，不需要間隙鎖。e.g.列id有唯一索引惭缰，下面這語句只對id=100的行使用索引記錄鎖浪南，其他會話是否在前面的間隙中插入行并不重要

SELECT * FROM child WHERE id = 100;

如果id沒有被索引，或者沒有唯一索引漱受，則語句會鎖定前面的間隙

InnoDB的間隙鎖是“純抑制性的”络凿，它的唯一目的是阻止其他事務(wù)插入到這個間隙，間隙鎖可以共存昂羡，共享和排他間隙鎖沒有差別

間隙鎖是可以被禁用的絮记，如果被禁用可能會導(dǎo)致幻讀問題，因為其他session可以將新行插入間隙

Next-Key鎖

是索引記錄上的記錄鎖和索引記錄之前間隙上的間隙鎖的組合
如果一個session在索引的記錄R上有一個共享或排他鎖虐先，則另一個session不能在索引順序中緊靠R之前的間隙中立即插入新的索引記錄
假設(shè)索引包含值10怨愤，11，13蛹批，20撰洗。那么這個索引上可能的next-key鎖包含下面的幾種間隔

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

默認(rèn)InnoDB運行在REPEATABLE READ事務(wù)隔離級別篮愉。這種情況下，InnoDB使用next-key鎖來進(jìn)行搜索和索引掃描

插入意向鎖 Insert Intention Locks

行插入之前通過INSERT操作設(shè)置的一種間隙鎖差导。此鎖表示插入的意圖试躏，如果插入到同一個索引間隙的多個事務(wù)不在間隙的同個位置插入，則它們無需互相等待
假設(shè)有索引記錄值4和7设褐。不同的事務(wù)嘗試插入5和6颠蕴，在獲得插入行的排它鎖之前，每個事務(wù)都會使用插入意向鎖鎖住4和7之間的間隙助析，但因為行不沖突而不會互相阻塞
e.g. 客戶端A創(chuàng)建一個表犀被，包含了兩個索引記錄90和102，然后啟動一個事務(wù)貌笨，對ID大于100的索引記錄進(jìn)行排它鎖弱判。這個排它鎖包含了記錄102前面的間隙

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

客戶端B開始一個插入記錄到間隙的事務(wù)。這個事務(wù)在它等待獲取排它鎖時接受插入意向鎖

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

AUTO-INC 鎖

是一種特殊的表鎖锥惋，事務(wù)插入有AUTO_INCREMENT列的表的時候使用昌腰。如果一個事務(wù)正在向表插入值，任何其他事務(wù)在插入這個表的時候都必須等待膀跌，以便第一個事務(wù)接收連續(xù)的主鍵值

InnoDB事務(wù)模型

事務(wù)隔離級別

• REPEATABLE READ （默認(rèn)級別）
  相同事務(wù)里的一致性讀遭商，使用第一次讀的時候建立的快照。意味著在同一個事務(wù)里捅伤，如果發(fā)出了幾個普通(非阻塞)的SELECT語句劫流，這些SELECT語句彼此之間也是一致的
  對于鎖定讀(SELECT帶有FOR UPDATE或FOR SHARE)，UPDATE, DELETE語句丛忆，鎖定依賴于語句是否在唯一搜索條件里使用了唯一索引祠汇，或者范圍搜索條件

  • 對于唯一搜索條件的唯一索引，InnoDB只鎖定找到的索引記錄熄诡，不包含它前面的間隙
  • 對于其他搜索條件可很，InnoDB鎖定索引掃描的范圍，使用間隙鎖或next-key鎖阻止其他session插入間隙所覆蓋的范圍凰浮。

• READ COMMITTED
  即使在同一個事務(wù)里我抠，每次一致性讀都會設(shè)置并讀取自己的新快照。
  對于鎖定讀袜茧，InnoDB只鎖定索引記錄菜拓，不包含他們之前的間隙，因此允許在鎖定記錄旁自由插入新記錄
  因為禁用了間隙鎖定笛厦，可能會出現(xiàn)不可重復(fù)讀纳鼎，因為其他session可以插入新行到間隙中
  影響：

  • 對于UPDATE或DELETE語句，InnoDB只對它更新或刪除的行持有鎖裳凸。在MYSQL評估WHERE條件后喷橙，將釋放不匹配行的鎖啥么，這大大降低了死鎖的概率
  • 對于UPDATE語句，如果一個行已經(jīng)鎖定贰逾，InnoDB執(zhí)行“半一致”讀悬荣，將最新提交的版本返回給MySQL，以便MySQL可以決定該行是否匹配更新的WHERE條件

e.g. 可重復(fù)讀 VS. 讀已提交

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

這種情況下疙剑，表沒有索引氯迂，因此搜索和索引掃描時使用隱式的聚簇索引來進(jìn)行記錄鎖定

# Session A 執(zhí)行更新語句
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

# Session B 在sessionA之后執(zhí)行
UPDATE t SET b = 4 WHERE b = 2;

在InnoDB執(zhí)行每個更新的時候，它先為每一行獲取一個排它鎖言缤，然后決定是否修改它嚼蚀。如果InnoDB不修改這行，它就會釋放這個鎖管挟。否則InnoDB持有鎖轿曙，直到事務(wù)結(jié)束。

當(dāng)使用默認(rèn)的可重復(fù)讀級別時僻孝，第一個UPDATE在它讀取的每行上獲取x鎖导帝，不釋放它們中的任何一個：

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

第二個UPDATE在嘗試獲取任何鎖的時候會立即阻塞(因為第一個更新在所有行上保留了鎖)，并且在第一次UPDATE提交或回滾之前不會繼續(xù)

x-lock(1,2); block and wait for first UPDATE to commit or roll back

如果使用READ COMMITTED穿铆，第一個UPDATE會在每行上獲取一個x鎖您单，并釋放這些不修改的行

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

對于第二個UPDATE，InnoDB執(zhí)行“半一致”讀荞雏，將讀取的每一行的最新版本返回給MySQL虐秦，以便MySQL可以確定該行是否匹配UPDATE的WHERE條件

x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

然后如果WHERE條件包含索引列，并且InnoDB使用這個索引凤优，那么在獲取和保留記錄鎖的時候只考慮索引列悦陋。
在下面這個例子，第一個UPDATE在b=2的每一行上獲取并持有x鎖筑辨，第二個UPDATE當(dāng)它嘗試獲取相同記錄上的x鎖的時候會阻塞叨恨，因為它也使用列b上定義的索引

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

• READ UNCOMMITTED 讀未提交
  SELECT語句以非阻塞的方式執(zhí)行，但可能會使用行的早期版本挖垛。因此讀是不一致的，也叫做臟讀

• SERIALIZABLE 串行化
  InnoDB會在autocommit禁用的時候隱式的轉(zhuǎn)換所有普通的SELECT語句為SELECT ... FOR SHARE秉颗。如果啟用autocommit痢毒，那么SELECT是它自己的事務(wù)。如果要強(qiáng)制普通的SELECT在其他事務(wù)修改了選中行的時候進(jìn)行阻塞蚕甥，需要禁用autocommit

autocommit, Commit, Rollback

InnoDB中哪替，所有用戶活動都是在事務(wù)里。如果autocommit模式啟用菇怀，每個SQL語句都會形成它自己的事務(wù)凭舶。默認(rèn)MySQL為每個新連接啟動session的時候晌块，會設(shè)置autocommit為啟用，所以MySQL會在每個SQL語句沒有返回錯誤之后進(jìn)行一次提交帅霜。如果語句返回錯誤匆背，會依賴具體的錯誤進(jìn)行提交或回滾
對于autocommit啟用的session，可以通過START TRANSACTION或BEGIN語句開始事務(wù)身冀，COMMIT或ROLLBACK結(jié)束事務(wù)钝尸。
如果在一個session里通過SET autocommit = 0來禁用autocommit，那么這個session會始終有一個打開的事務(wù)搂根。COMMIT或ROLLBACK語句會結(jié)束當(dāng)前事務(wù)珍促，啟動新的事務(wù)。
如果session禁用了autocommit剩愧，沒有強(qiáng)制提交最后的事務(wù)猪叙，那么這事務(wù)會被回滾。

一致非阻塞讀

一致讀意味著InnoDB使用多版本控制呈現(xiàn)數(shù)據(jù)庫在某個時間點的一個快照仁卷。查詢可以看到在這個時間點之前其他事務(wù)已提交的更改穴翩，不能看得到之后或未提交的事務(wù)。
如果事務(wù)隔離級別是默認(rèn)的REPEATABLE READ五督，在相同事務(wù)里的所有一致讀都會讀取這個事務(wù)第一次讀的時候建立的快照藏否。可以通過提交當(dāng)前事務(wù)并在之后發(fā)出新的查詢來得到新的快照
對于READ COMMITTED隔離級別充包，每個事務(wù)里的一致讀都會設(shè)置并讀取它自己的新快照
一致讀是InnoDB在READ COMMITTED和REPEATABLE READ隔離級別下處理SELECT語句的默認(rèn)模式副签。一致讀不會在它訪問的表上設(shè)置任何鎖，因此其他session可以同時自由修改這些表
數(shù)據(jù)庫的快照狀態(tài)應(yīng)用于事務(wù)里的SELECT語句基矮，不一定應(yīng)用于DML語句淆储。如果插入或修改某些行然后提交該事務(wù)，則從另一個并發(fā)的可重復(fù)讀事務(wù)會影響到這些剛提交的行家浇，盡管這session查詢不到它們本砰。如果某個事務(wù)確實更新或刪除了不同事務(wù)提交的行，則這些更改對當(dāng)前事務(wù)是可見的钢悲。

例子(其他session提交記錄点额，原session查詢不到，可以刪除)：

#sessionA 
select count(name) from child where name = 'hello100';
# 返回0行

#sessionB插入兩行并提交
insert into child(id,name) values(100, 'hello100');
insert into child(id,name) values(101, 'hello100');

#sessionA統(tǒng)計莺琳，返回0行
select count(name) from child where name = 'hello100';

#sessionA刪除还棱，盡管查詢不到，但可以刪除2行
delete from child where name = 'hello100';
# Query OK, 2 rows affected (0.00 sec)

可以通過提交事務(wù)惭等，然后執(zhí)行SELECT或START TRANSACTION WITH CONSISTENT SNAPSHOT來更新時間點珍手。這就是多版本并發(fā)控制
下面的例子，sessionA只在B提交了插入，且A已提交后琳要，才能看到B插入的記錄

            Session A              Session B

           SET autocommit=0;      SET autocommit=0;
time
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------

如果想看到數(shù)據(jù)庫的最新狀態(tài)寡具，那么要使用READ COMMITTED隔離級別，或者鎖定讀：

SELECT * FROM t FOR SHARE;
讀已提交隔離級別稚补，一個事務(wù)里的每個一致讀都會設(shè)置并讀取它自己的新快照童叠。對于FOR SHARE，SELECT一直阻塞直到包含最新行的事務(wù)結(jié)束

InnoDB中不同SQL語句設(shè)置的鎖

鎖定讀孔厉，UPDATE或者DELETE通常會在SQL語句處理的過程中對每個被掃描到的索引記錄設(shè)置記錄鎖拯钻。它不管語句中的WHERE條件是否會排除掉行。InnoDB不會記得準(zhǔn)確的WHERE條件撰豺，只知道哪部分索引范圍被掃描粪般。
如果搜索中使用了二級索引，并且索引記錄鎖被設(shè)置為排他的污桦，InnoDB也會檢索出相應(yīng)的聚簇索引記錄并對它們設(shè)置鎖亩歹。
如果沒有索引適合執(zhí)行語句，那么MySQL必須掃描整個表來處理語句凡橱，表的每一行都會被鎖定小作，從而也會阻塞其他用戶插入到這個表中。所以創(chuàng)建好的索引稼钩，讓查詢不會掃描超過需要的行是很重要的顾稀。

InnoDB中的死鎖

因為不同的事務(wù)持有對方需要的鎖，導(dǎo)致這些事務(wù)不能執(zhí)行下去坝撑。由于每個事務(wù)都在等待資源變成可用静秆，都不會釋放它持有的鎖。
可能出現(xiàn)的場景：多個事務(wù)以不同的順序鎖定多個表中的行(通過類似這樣的語句：UPDATE或SELECT ... FOR UPDATE)巡李，鎖定范圍索引記錄和間隙抚笔，每個事務(wù)因為時間的原因只獲取一部分鎖。

為了減少死鎖的可能侨拦，

• 使用事務(wù)而不是LOCK TABLES語句殊橙；
• 保持insert或update的事務(wù)足夠小，讓他們不會長時間打開狱从；
• 當(dāng)不同的事務(wù)更新多個表或者大范圍的行膨蛮，在每個事務(wù)里使用相同順序的操作(例如SELECT ... FOR UPDATE)；
• 在SELECT ... FOR UPDATE和UPDATE ... WHERE 語句中使用到的列上創(chuàng)建好索引
• 如果使用鎖定讀(SELECT ... FOR UPDATE或者SELECT ... FOR SHARE)季研，嘗試使用例如READ COMMITTED這樣的低隔離級別
• 添加選擇度高的索引到表中敞葛，這樣查詢只需要掃描更少的索引記錄，相應(yīng)的也就設(shè)置更少的鎖训貌。使用EXPLAIN SELECT來查看

死鎖的可能不會被隔離級別影響，因為隔離級別只改變了讀操作的行為，而死鎖是因為寫操作递沪。

InnoDB多版本控制 MVCC

InnoDB是一個多版本的存儲引擎豺鼻。它持有被更改過的行的舊版本信息，以支持例如并發(fā)和回滾這樣的事務(wù)特性款慨。這個信息存儲在undo表空間里面一個叫rollback segment的數(shù)據(jù)結(jié)構(gòu)中儒飒。InnoDB使用回滾段里的信息來執(zhí)行事務(wù)回滾里面的undo操作。它還使用這信息來構(gòu)建更早版本的行用于實現(xiàn)一致性讀檩奠。
InnoDB內(nèi)部在數(shù)據(jù)庫里存儲的每個行里添加了三個字段：

• 6-byte DB_TRX_ID桩了，表示最近一個插入或更新行的事務(wù)的事務(wù)標(biāo)識符。刪除在內(nèi)部也是被視為更新埠戳，行里面一個特定的bit會設(shè)置以標(biāo)識它為已刪除
• 7-byte DB_ROLL_PTR井誉，滾動指針。它指向回滾段里的一條undo日志記錄整胃。如果行被更新了颗圣，這條undo日志記錄會包含更新前重建行所需要的必要信息
• 6-byte DB_ROW_ID，包含了隨著新行插入而單調(diào)遞增的行ID屁使。如果InnoDB自動生成了一個聚簇索引在岂，索引會包含行ID的值。否則DB_ROW_ID列不會出現(xiàn)在任何索引里

回滾段里的Undo日志被分成了插入和更新的undo日志蛮寂。插入的undo日志只在事務(wù)回滾的時候需要蔽午，可以在事務(wù)提交的時候立即丟棄掉。更新的undo日志也用于一致性讀酬蹋，但只有在不存在InnoDB已為其分配快照的事務(wù)時才能丟棄及老。在一致性讀中，快照需要更新的undo日志中的信息用于構(gòu)建數(shù)據(jù)庫行的早期版本除嘹。
建議定期提交事務(wù)写半，包括僅發(fā)出一致性讀的事務(wù)。否則InnoDB不能從更新的undo日志里丟棄數(shù)據(jù)尉咕，這樣回滾段可能會增長得太大叠蝇，充滿它駐留的整個undo表空間。
回滾段里的undo日志記錄的物理大小通常比相應(yīng)的插入或更新行更小年缎』诖罚可以使用這個信息來記錄回滾段需要的空間。
在InnoDB多版本控制方案中单芜，當(dāng)使用SQL語句刪除的時候蜕该，該行并不會立即從數(shù)據(jù)庫中物理刪除。InnoDB僅在丟棄為刪除而寫入的更新undo日志的時候洲鸠，才會物理刪除相應(yīng)的行和索引記錄堂淡。

多版本和二級索引

InnoDB 多版本并發(fā)控制(MVCC)對待二級索引不同于聚簇索引馋缅。聚簇索引中的記錄會就地更新，其隱藏的系統(tǒng)列指向undo日志項绢淀，從中可以重構(gòu)早期版本的記錄萤悴。二級索引不包含隱藏的系統(tǒng)列，也不進(jìn)行就地更新皆的。
更新二級索引列時覆履，舊的二級索引被刪除標(biāo)記，新記錄被插入费薄，刪除標(biāo)記的記錄最終被清除硝全。當(dāng)二級索引記錄被刪除標(biāo)記，或者二級索引頁被更新的一個事務(wù)更新時楞抡，InnoDB會在聚簇索引中查找數(shù)據(jù)庫記錄伟众。在聚簇索引中，檢查記錄的DB_TRX_ID拌倍，如果在讀取事務(wù)啟動后修改了記錄赂鲤，則從undo日志里檢索出記錄的正確版本。
如果二級索引記錄被標(biāo)記為刪除柱恤，或者二級索引頁被新的事務(wù)更新数初，覆蓋索引記錄就不會被使用。InnoDB不會從索引結(jié)構(gòu)里返回值梗顺，而是會從聚簇索引里查找記錄

MVCC和幻讀

幻讀泡孩，同一個事務(wù)里連續(xù)執(zhí)行兩次同樣的SQL，可能導(dǎo)致不同結(jié)果的問題寺谤。第二次sql語句可能會返回之前不存在的行仑鸥。

• 在快照讀情況下，通過mvcc來避免幻讀
  mvcc利用歷史版本信息(快照)來控制它能讀取的數(shù)據(jù)范圍
• 在當(dāng)前讀情況下变屁，通過next-key鎖來避免幻讀

• 快照讀
  簡單的select操作
  Read Committed: 每次select都生成一個快照讀
  Repeatable read: 開啟事務(wù)后第一個select語句才是快照讀的地方眼俊，而不是一開啟事務(wù)就快照讀

• 當(dāng)前讀
  讀取最新版本的記錄（即使別的事務(wù)提交的數(shù)據(jù)也能查詢到），并且對讀取的記錄加鎖粟关，阻塞其他事務(wù)同時改動相同記錄疮胖，避免出現(xiàn)安全問題。
  會讓insert\update\delete阻塞

select ... lock in share mode
select ... for update
insert
update
delete

在RR級別:

• 快照讀通過MVCC和undo log來實現(xiàn)
• 當(dāng)前讀通過加record lock(記錄鎖)和gap lock(間隙鎖)來實現(xiàn)

MySQL事務(wù)的實現(xiàn)原理

• 事務(wù)的原子性是通過undo log來實現(xiàn)的
• 事務(wù)的持久性是通過redo log來實現(xiàn)的
• 事務(wù)的隔離性是通過(讀寫鎖+MVCC)來實現(xiàn)的
• 事務(wù)的一致性闷板，是通過原子性澎灸、持久性、隔離性來實現(xiàn)的

binlog

binlog包含描述數(shù)據(jù)庫更改(如表創(chuàng)建操作或表數(shù)據(jù)變更)的“事件”遮晚。除非使用基于行的日志記錄性昭，否則它還包含可能已進(jìn)行更改的語句的事件(例如不匹配任何行的刪除)。binlog還包含更新數(shù)據(jù)時每個語句花費了多長的時間县遣。
類型

• STATEMENT 日志記錄基于語句
• ROW 日志記錄基于行糜颠。默認(rèn)方式
• MIXED 日志記錄使用混合格式(默認(rèn)基于語句汹族，特定情況下切換到基于行)

MySQL默認(rèn)隔離級別為什么是可重復(fù)讀

• 數(shù)據(jù)庫默認(rèn)隔離級別
  mysql-可重復(fù)讀; oracle, PostgreSQL-讀已提交

• 為什么mysql用可重復(fù)讀(RR)而不是讀已提交(RC)
  5.0之前只有statement一種格式，主從復(fù)制會存在大量的不一致其兴，故選用RR

  
  
  示例

• 為什么默認(rèn)隔離級別很多選用RC
  -- 可重復(fù)讀RR存在間隙鎖鞠抑，會使死鎖的概率增大；在可重復(fù)讀下忌警，條件列未命中索引會鎖表；在讀已提交RC下秒梳，只鎖行
  -- 在讀已提交RC下法绵，引入半一致讀(semi-consistent)特性增加了update操作的并發(fā)性能
  -- 不可重復(fù)讀在開發(fā)中是可以接受的