剖析MySQL InnoDB存儲原理（下）

一、InnoDB存儲引擎內(nèi)存管理

1.1 概念：

Buffer Pool：預(yù)分配的內(nèi)存池；

Page：Buffer Pool的最小單位；

Free list：空閑Page組成的鏈表；

Flush list：臟頁鏈表；

Page hash 表：維護(hù)內(nèi)存Page和文件Page的映射關(guān)系；

這幾個概念關(guān)系，如圖所示：

1.2 內(nèi)存的淘汰算法：LRU

分為三部分：LRU_new、LRU_old、MidPoint。如下圖所描述：

1.2.1 頁面裝載的邏輯如圖：

數(shù)據(jù)從磁盤到內(nèi)存 > Free list中取 > LRU中淘汰 > LRU Flush

1.2.2 頁面淘汰

LRU鏈表中將第一個臟頁刷盤并“釋放”，放到Free list中。

1.2.3 位置移動

innodb_old_blocks_time old區(qū)存活時間，大于此值，有機(jī)會進(jìn)入new區(qū)

Midpoint:指向5/8位置

為了減少移動到次數(shù)和lock，思路訪問時間 + 頻率，避免熱數(shù)據(jù)被移除，通過如下：freed_page_clock:Buffer Pool淘汰頁數(shù)

移動時機(jī)：

當(dāng)前freed_page_clock - 上次移動到Header時freed_page_clock >LRU_new長度1/4

2、MySQL事務(wù)管理機(jī)制原理分析

1、基本概念：

1.1 事務(wù)特性：

A(Atomicity原子性):全部成功或全部失敗

I(Isolation隔離性):并行事務(wù)之間互不干擾

D(Durability持久性):事務(wù)提交后，永久生效

C(Consistency一致性):通過AID保證

1.2 并發(fā)問題：

臟讀(Drity Read):讀取到未提交的數(shù)據(jù)

不可重復(fù)讀(Non-repeatable read):兩次讀取結(jié)果不同

幻讀(Phantom Read):select 操作得到的結(jié)果所表征的數(shù)據(jù)狀態(tài)無法支撐后續(xù)的業(yè)務(wù)操作

1.3 隔離級別

Read Uncommitted(讀取未提交內(nèi)容):最低隔離級別，會讀取到其他事務(wù)未提交的數(shù)據(jù)，臟讀;

Read Committed(讀取提交內(nèi)容):事務(wù)過程中可以讀取到其他事務(wù)已提交的數(shù)據(jù)，不可重復(fù)讀;

Repeatable Read(可重復(fù)讀):每次讀取相同結(jié)果集，不管其他事務(wù)是否提交，幻讀;

Serializable(串行化):事務(wù)排隊，隔離級別最高，性能最差;

2、事務(wù)實(shí)現(xiàn)原理

2.1 MVCC

Read View：活躍事務(wù)列表(還未提交的事務(wù)) 列表中最小事務(wù)ID(提交)，列表中最大事務(wù)ID（未提交）；具體可見性通過如下流程圖所示：

2.2 MVCC如何實(shí)現(xiàn)

undo log：實(shí)現(xiàn)數(shù)據(jù)多版本，回滾，提交即清理；

redo log：實(shí)現(xiàn)事務(wù)持久性,記錄修改,用于異?；謴?fù),循環(huán)寫文件;

Write Pos:寫入位置

Chick Point:刷盤位置

Chick Point -> Write Pos:待落盤數(shù)據(jù)

寫入流程：

刷盤時機(jī)：

innodb_flush_log_at_trx_commit

3、MySQL使用及調(diào)優(yōu)實(shí)踐分析

3.1 索引使用技巧

聯(lián)合索引:優(yōu)于多列獨(dú)立索引

索引順序:選擇性高的在前面

覆蓋索引:二級索引存儲主鍵值更有利

索引排序:索引同時滿足查詢和排序

3.2 分庫分表

是否分表，建議單表不超過1KW

分表方式，取模:存儲均勻&訪問均勻，按時間:冷熱庫

分庫，按業(yè)務(wù)垂直分，水平查分多個庫

3.3 使用建議

數(shù)據(jù)庫字符集使用utf8mb4;

VARCHAR按實(shí)際需要分配長度;

文本字段建議使用VARCHAR;

時間字段建議使用long;

bool字段建議使用tinyint;

枚舉字段建議使用tinyint;

交易金額建議使用long;

禁止使用“%”前導(dǎo)的查詢;

禁止在索引列進(jìn)行數(shù)學(xué)運(yùn)算，會導(dǎo)致索引失效;

select * from t1 where id+1 >1121 不會使用索引

select * from t1 where id >1121 - 1 會使用索引

表必須有主鍵，建議使用業(yè)務(wù)主鍵;

單張表中索引數(shù)量不超過5個;

單個索引字段數(shù)不超過5個;

字符串索引使用前綴索引，前綴長度不超過10個字符;