一、InnoDB存儲引擎內存管理

1.1 概念：

Buffer Pool：預分配的內存池；

Page：Buffer Pool的最小單位；

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

Flush list：臟頁鏈表；

Page hash 表：維護內存Page和文件Page的映射關系；

這幾個概念關系，如圖所示：

1.2 內存的淘汰算法：LRU

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

1.2.1 頁面裝載的邏輯如圖：

數據從磁盤到內存 > Free list中取 > LRU中淘汰 > LRU Flush

1.2.2 頁面淘汰

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

1.2.3 位置移動

innodb_old_blocks_time old區存活時間，大于此值，有機會進入new區

Midpoint:指向5/8位置

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

移動時機：

當前freed_page_clock - 上次移動到Header時freed_page_clock >LRU_new長度1/4

2、MySQL事務管理機制原理分析

1、基本概念：

1.1 事務特性：

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

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

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

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

1.2 并發問題：

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

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

幻讀(Phantom Read):select 操作得到的結果所表征的數據狀態無法支撐后續的業務操作

1.3 隔離級別

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

Read Committed(讀取提交內容):事務過程中可以讀取到其他事務已提交的數據，不可重復讀;

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

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

2、事務實現原理

2.1 MVCC

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

2.2 MVCC如何實現

undo log：實現數據多版本，回滾，提交即清理；

redo log：實現事務持久性,記錄修改,用于異常恢復,循環寫文件;

Write Pos:寫入位置

Chick Point:刷盤位置

Chick Point -> Write Pos:待落盤數據

寫入流程：

刷盤時機：

innodb_flush_log_at_trx_commit

3、MySQL使用及調優實踐分析

3.1 索引使用技巧

聯合索引:優于多列獨立索引

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

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

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

3.2 分庫分表

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

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

分庫，按業務垂直分，水平查分多個庫

3.3 使用建議

數據庫字符集使用utf8mb4;

VARCHAR按實際需要分配長度;

文本字段建議使用VARCHAR;

時間字段建議使用long;

bool字段建議使用tinyint;

枚舉字段建議使用tinyint;

交易金額建議使用long;

禁止使用“%”前導的查詢;

禁止在索引列進行數學運算，會導致索引失效;

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

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

表必須有主鍵，建議使用業務主鍵;

單張表中索引數量不超過5個;

單個索引字段數不超過5個;

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