剖析MySQL InnoDB存儲(chǔ)原理（下）

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

1.1 概念：

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

Page：Buffer Pool的最小單位；

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

Flush list：臟頁(yè)鏈表；

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

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

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

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

1.2.1 頁(yè)面裝載的邏輯如圖：

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

1.2.2 頁(yè)面淘汰

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

1.2.3 位置移動(dòng)

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

Midpoint:指向5/8位置

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

移動(dòng)時(shí)機(jī)：

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

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

1、基本概念：

1.1 事務(wù)特性：

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

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

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

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

1.2 并發(fā)問(wèn)題：

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

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

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

1.3 隔離級(jí)別

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

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

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

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

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

2.1 MVCC

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

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ù)

寫入流程：

刷盤時(shí)機(jī)：

innodb_flush_log_at_trx_commit

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

3.1 索引使用技巧

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

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

覆蓋索引:二級(jí)索引存儲(chǔ)主鍵值更有利

索引排序:索引同時(shí)滿足查詢和排序

3.2 分庫(kù)分表

是否分表，建議單表不超過(guò)1KW

分表方式，取模:存儲(chǔ)均勻&訪問(wèn)均勻，按時(shí)間:冷熱庫(kù)

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

3.3 使用建議

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

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

文本字段建議使用VARCHAR;

時(shí)間字段建議使用long;

bool字段建議使用tinyint;

枚舉字段建議使用tinyint;

交易金額建議使用long;

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

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

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

select * from t1 where id >1121 - 1 會(huì)使用索引

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

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

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

字符串索引使用前綴索引，前綴長(zhǎng)度不超過(guò)10個(gè)字符;