性能優化專題 - MySql 性能優化 - 03 - 深入理解InnoDB

前言

性能優化專題共計四個部分，分別是：

• Tomcat 性能優化
• MySql 性能優化
• JVM 性能優化
• 性能測驗

本節是性能優化專題第二部分 —— MySql 性能優化篇，共計四個小節，分別是：

1. MySql索引機制
2. MySql運行機理
3. 深入理解InnoDB
4. MySql調優

MySql事務

資料庫事務(Database Transaction) ，是指作為單個邏輯作業單元執行的一系列操作，要么完全地執行，要么完全地不執行，

? 事務的ACID特性，事務并發帶來了哪些特性，事務的四種隔離級別，

事務

事務（Transaction），一般是指要做的或所做的事情，在計算機術語中是指訪問并可能更新資料庫中各種資料項的一個程式執行單元(unit)，

資料庫事務(Database Transaction) ，是指作為單個邏輯作業單元執行的一系列操作，要么完全地執行，要么完全地不執行，

• 典型事務場景(轉賬)：

update user_account set balance = balance - 1000 where userID = 3;
update user_account set balance = balance +1000 where userID = 1;

mysql中如何開啟事務

• SQL編程

1. 手工

begin / start transaction 
# 開啟事務

commit / rollback
# 事務提交或回滾

2. 自動

set session autocommit = on/off;
# 設定事務是否自動開啟

• JDBC 編程

connection.setAutoCommit（boolean）;

• Spring 事務AOP編程

expression=execution（com.xxx.service.*.*(..)）

事務的ACID特性

• 原子性（Atomicity）

? 整個事務中的所有操作，要么全部完成，要么全部不完成，不可能停滯在中間某個環節，事務在執行程序中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣，

• 一致性（Consistency）

? 一個事務可以封裝狀態改變（除非它是一個只讀的），事務必須始終保持系統處于一致的狀態，不管在任何給定的時間并發事務有多少，

? 也就是說：如果事務是并發多個，系統也必須如同串行事務一樣操作，其主要特征是保護性和不變性(Preserving an Invariant)，以轉賬案例為例，假設有五個賬戶，每個賬戶余額是100元，那么五個賬戶總額是500元，如果在這個5個賬戶之間同時發生多個轉賬，無論并發多少個，比如在A與B賬戶之間轉賬5元，在C與D賬戶之間轉賬10元，在B與E之間轉賬15元，五個賬戶總額也應該還是500元，這就是保護性和不變性，

• 隔離性（Isolation）

一個事務所操作的資料在提交之前，對其他事務的可見性設定（一般設定為不可見）

• 持久性（Durability）

事務所做的修改就會永久保存，不會因為系統意外導致資料的丟失

事務并發帶來了哪些問題

如下圖，事務A和事務B 同時操作id為1的user

臟讀(dirty read)

1. 事務B 修改id為1的用戶age由16 --> 18
2. 事務A 查詢id為1的用戶，獲取到age為18
3. 事務B 此時因為某些意外原因，rollback

（要理解1和3為同一個事務（最小執行單元））
此時資料庫中的id為1的記錄age還是16,而事務B并不之情，以為age是18，此時就出問題了，所謂的臟讀

不可重復讀(nonrepeatableread)

1. 事務A 查詢id為1的用戶，獲取到age為16
2. 事務B 修改id為1的用戶，age由16 --> 18
3. 事務B commit
4. 事務A 查詢id為1的用戶，獲取到age為18

（1和4一個事務 2和3一個事務，要理解成不可分割的最小執行單元）
此時事務A兩次查詢不一樣，在一個事務重復讀資料內容不一樣，所謂的 不可重復讀

幻讀(Phantom read)

1. 事務A 查詢id為age > 15的用戶，獲取到一個用戶，id為1，age為16的用戶
2. 事務B 新增id為2，name為‘Bob’，age為22的用戶
3. 事務A 再次查詢age > 15的用戶，獲取到兩個用戶

（1和3一個事務 ，要理解成不可分割的最小執行單元）
此時事務A兩次查詢不一樣，在一個事務重復讀資料的數量一樣，產生了幻覺，所謂的 幻讀

• 臟讀：很好理解，事務中，讀取到臟資料，
• 不可重復讀：事務中，多次讀取同一個資料的內容不一樣，(針對update)，
• 幻讀：事務中，多次讀取同一個條件資料的數量不一樣，（針對的是insert、delete）

如何解決上面三種問題呢？往下看

事務四種隔離級別

SQL92，是資料庫的一個ANSI/ISO標準，它定義了一種語言（SQL）以及資料庫的行為（事務、隔離級別等），

SQL92 ANSI/ISO標準：
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

四種隔離級別

• Read Uncommitted（未提交讀） --未解決并發問題

事務未提交對其他事務也是可見的，臟讀（dirty read）

• Read Committed（提交讀） --解決臟讀問題

一個事務開始之后，只能看到自己提交的事務所做的修改，不可重復讀（nonrepeatableread）

• Repeatable Read (可重復讀) --解決不可重復讀問題

在同一個事務中多次讀取同樣的資料結果是一樣的，這種隔離級別未定義解決幻讀(Phantom read)的問題

• Serializable（串行化） --解決所有問題

最高的隔離級別，通過強制事務的串行執行

Innodb引擎對隔離級別的支持程度

MySql鎖

? MySQL為什么要提供鎖機制？鎖能解決什么問題？

? 如何保證資料并發訪問的一致性、有效性是所在有資料庫必須解決的一個問題，鎖沖突也是影響資料庫并發訪問性能的一個重要因素，從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加復雜，

理解表鎖、行鎖

鎖是用于管理不同事務對共享資源的并發訪問

表鎖與行鎖的區別：

鎖定粒度：表鎖 > 行鎖

加鎖效率：表鎖 > 行鎖

沖突概率：表鎖 > 行鎖

并發性能：表鎖 < 行鎖

InnoDB存盤引擎支持行鎖和表鎖（另類的行鎖）

MySQL Innodb鎖型別

MySQL Innodb鎖型別一共有3種型別

1. 行鎖

• 共享鎖（行鎖）：Shared Locks

• 排它鎖（行鎖）：Exclusive Locks

2. 表鎖

• 意向鎖共享鎖（表鎖）：Intention Shared Locks

• 意向鎖排它鎖（表鎖）：Intention Exclusive Locks

3. 自增鎖

• AUTO-INC Locks

下面3種是行鎖的演算法

• 記錄鎖 Record Locks

• 間隙鎖 Gap Locks

• 臨鍵鎖 Next-key Locks

行鎖的演算法https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

共享鎖（Share Locks）vs 排它鎖（Exclusive Locks）

事務共享鎖 :又稱為讀鎖，簡稱S鎖，顧名思義，共享鎖就是多個事務對于同一資料可以共享一把鎖，都能訪問到資料，但是只能讀不能修改;

加鎖釋鎖方式：

select * from users WHERE id=1 LOCK IN SHARE MODE;
commit/rollback

實體測驗Share Locks

會話A autocommit關閉

##1、當前會話A autocommit關閉
mysql> set session autocommit = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> show VARIABLES like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

##2、查詢select
mysql> select * from users WHERE id=1 LOCK IN SHARE MODE\G
*************************** 1. row ***************************
        id: 1
     uname: 李二狗
 userLevel: 2
       age: 19
  phoneNum: 13666666666
createTime: 2021-01-23 15:39:46
lastUpdate: 2021-01-23 15:39:50
1 row in set (0.00 sec)
##3、當前事務還沒提交或者回滾

會話B autocommit采用默認的，未關閉

mysql> select * from users where id =1\G
*************************** 1. row ***************************
        id: 1
     uname: 李二狗
 userLevel: 2
       age: 19
  phoneNum: 13666666666
createTime: 2021-01-23 15:39:46
lastUpdate: 2021-01-23 15:39:50
1 row in set (0.00 sec)

mysql> update users set age=19 where id =1;
##這里修改會阻塞，，，
##，，，等一會顯示
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

事務排他鎖:又稱為寫鎖，簡稱X鎖，排他鎖不能與其他鎖并存，如一個事務獲取了一個資料行的排他鎖，其他事務就不能再獲取該行的鎖（共享鎖、排他鎖），只有該獲取了排他鎖的事務是可以對資料行進行讀取和修改，（其他事務要讀取資料可來自于快照//TODO 快照后面會將，待補充鏈接）

加鎖釋鎖方式：

delete / update / insert 
# 默認加上X鎖

SELECT * FROM table_name WHERE ... FOR UPDATE
commit / rollback 

實體測驗
會話A

mysql> set session autocommit = OFF;
Query OK, 0 rows affected (0.01 sec)

mysql> show VARIABLES like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> select * from users where id =1 for update\G
*************************** 1. row ***************************
        id: 1
     uname: 李二狗
 userLevel: 2
       age: 19
  phoneNum: 13666666666
createTime: 2021-01-23 15:39:46
lastUpdate: 2021-01-23 15:39:50
1 row in set (29.50 sec)
##此時會話A拿到排它鎖

會話B

mysql> show VARIABLES like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.02 sec)

mysql> select * from users where id =1 for update\G
## 會阻塞，，，
## 然后過一段時間超時
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

##在嘗試拿共享鎖（會話A要重新拿一次排它鎖，因為我用的linux，這邊超時了，會話A的事務無效了）
mysql> select * from users where id =1 lock in share mode\G
## 會阻塞，，，
## 然后過一段時間超時
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Innodb到底鎖了什么？

InnoDB的行鎖是通過給索引上的索引項加鎖來實作的，

只有通過索引條件進行資料檢索，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖（鎖住索引的所有記錄）

意向共享鎖（IS）& 意向排他鎖

• 意向共享鎖(IS)

? 表示事務準備給資料行加入共享鎖，即一個資料行加共享鎖前必須先取得該表的IS鎖，意向共享鎖之間是可以相互兼容的

• 意向排它鎖(IX)

? 表示事務準備給資料行加入排他鎖，即一個資料行加排他鎖前必須先取得該表的IX鎖，意向排它鎖之間是可以相互兼容的

**意向鎖(IS、IX)**是InnoDB資料操作之前自動加的，不需要用戶干預

意義：
當事務想去進行鎖表時，先嘗試拿意向鎖，意向拿不到，就不用去拿共享鎖、排他鎖

例如生活中的案例

? 一節火車車廂上的衛生間WC會有一個指示燈，提示有人、無人，其他乘客只需要通過指示燈可以判斷衛生間能否進入，獲取使用權，這個指示燈就相當于意向鎖，只是一個標識，

? 乘客要獲取使用權衛生間，不用進入衛生間查看是否有人，只需要看指示燈就行了，

? 事務要獲取一個資料行的鎖，要先獲取意向鎖，如果意向所獲取不到，就沒必要繼續獲取其共享鎖或排他鎖了，提高獲取鎖的性能，

自增鎖 AUTO-INC Locks

針對自增列自增長的一個特殊的表級別鎖

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

默認取值1，步長為1，代表連續，事務未提交ID永久丟失

臨鍵鎖（Next-key）&間隙鎖（Gap）&記錄鎖（Record）

Gap locks：

鎖住資料不存在的區間（左開右開）
? 當sql執行按照索引進行資料的檢索時，查詢條件的資料不存在，這時SQL陳述句加上的鎖即為Gap locks，鎖住索引不存在的區間（左開右開）

Record locks：

鎖住具體的索引項
? 當sql執行按照唯一性（Primary key、Unique key）索引進行資料的檢索時，查詢條件等值匹配且查詢的資料是存在，這時SQL陳述句加上的鎖即為記錄鎖Record locks，鎖住具體的索引項

下面結合實體詳細介紹

資料準備，比如資料庫中有一個表t,表結構和資料如下：

mysql> desc t;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| value | int(11) | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from t;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  4 |     7 |
|  7 |     7 |
| 10 |    10 |
+----+-------+
4 rows in set (0.00 sec)

很簡單的資料，只有4條

臨鍵鎖（Next-key）

Next-key locks：
? 鎖住記錄+區間（左開右閉）
? 當sql執行按照索引進行資料的檢索時,查詢條件為范圍查找（between and、<、>等）并有資料命中則此時SQL陳述句加上的鎖為Next-key locks，鎖住索引的記錄+區間（左開右閉）

為什么Innodb選擇臨鍵鎖Next-key作為行鎖的默認演算法？

防止幻讀，同時Innodb的默認引擎是B+樹，其資料結構特點就是連續遞增，且左開右閉，所以使用Next-key策略

間隙鎖（Gap）

Gap只在RR事務隔離級別存在

記錄鎖（Record）

怎么利用鎖解決臟讀、不可重復讀、幻讀

解決臟讀用x鎖：

解決不可重復讀用s鎖：

解決幻讀用Next-key鎖：

死鎖介紹

多個并發事務（2個或者以上）；

每個事務都持有鎖（或者是已經在等待鎖）;

每個事務都需要再繼續持有鎖；

事務之間產生加鎖的回圈等待，形成死鎖，

小結:我在等你、你在等我，

死鎖如何避免

1. 類似的業務邏輯以固定的順序訪問表和行，
2. 大事務拆小，大事務更傾向于死鎖，如果業務允許，將大事務拆小，
3. 在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率，
4. 降低隔離級別，如果業務允許，將隔離級別調低也是較好的選擇
5. 為表添加合理的索引，可以看到如果不走索引將會為表的每一行記錄添加上鎖（或者說是表鎖）

MVCC

先思考一個問題

## 偽代碼
## 查看mysql的設定的事務隔離級別
select @@tx_isolation;
ex1：
	tx1: set session autocommit=off;
		 update users set lastUpdate=now() where id =1;
		 ## 在未做commit/rollback操作之前
		 ## 在其他的事務我們能不能進行對應資料的查詢（特別是加上了X鎖的資料）
	tx2: select * from users where id > 1;
			 select * from users where id = 1;
ex2：
	tx1: begin
		 select * from users where id =1 ;
	tx2: begin
		 update users set lastUpdate=now() where id =1;
	tx1:
		 select * from users where id =1;

這兩個案例從結果上來看是一致的！底層實作是怎樣的呢？是一樣的嗎？他們的底層實作跟MVCC有什么關系么？

MVCC
? Multiversion concurrency control (多版本并發控制)

普通話解釋：
? 并發訪問(讀或寫)資料庫時，對正在事務內處理的資料做多版本的管理，以達到用來避免寫操作的堵塞，從而引發讀操作的并發問題，

MVCC實作
? MVCC是通過保存資料在某個時間點的快照來實作的. 不同存盤引擎的MVCC. 實作是不同的,典型的有樂觀并發控制和悲觀并發控制.

MVCC的具體實作分析
下面,我們通過InnoDB的MVCC實作來分析MVCC是怎樣進行并發控制的

InnoDB的MVCC,是通過在每行記錄后面保存兩個隱藏的列來實作的,這兩個列，分別保存了這個行的創建時間（DB_TRX_ID），一個保存的是行的洗掉時間（DB_ROLL_PT），這里存盤的并不是實際的時間值,而是系統版本號(可以理解為事務的ID)，每開始一個新的事務，系統版本號就會自動遞增，事務開始時刻的系統版本號會作為事務的ID.下面看一下在REPEATABLE READ隔離級別下,MVCC具體是如何操作的.

MySQL中MVCC邏輯流程

插入

假設系統的全域事務ID號從1開始；

begin;  -- 拿到系統的事務ID=1;
insert into teacher(name,age) value ('sever',18);
insert into teacher(name,age) value ('qingshan',19);
commit;

如下圖，資料插入成功后，表后面兩列保存相應的版本號

洗掉

假設系統的全域事務ID號目前到了22

begin; -- 拿到系統的事務ID=22;
delete teacher where id = 1;
commit;

如下圖，id為2的資料行，洗掉版本號設定為當前事務ID(22)

修改

假設系統的全域事務ID號目前到了33

begin; -- 拿到系統的事務ID=33;
update teacher set age = 19 where id = 1;
commit;

修改操作是先做命中的資料行的copy，將原行資料的洗掉版本號的值設定為當前事務ID(33)

查詢

資料行查詢規則

1. 查找資料行版本早于當前事務版本的資料行（也就是，行的系統版本號小于或等于事務的系統版本號），這樣可以確保事務讀取的行，要么是在事務開始前已經存在的，要么是事務自身插入或者修改過的
2. 查找洗掉版本號要么為null，要么大于當前事務版本號的記錄，確保取出來的行記錄在事務開啟之前沒有被洗掉
   只有1,2同時滿足的記錄，才能回傳作為查詢結果

假設系統的全域事務ID號目前到了44

begin; -- 拿到系統的事務ID=44;
select * from teacher;
commit;

MySQL中版本控制案例

資料準備：

insert into teacher(name,age) value ('seven',18) ;
insert into teacher(name,age) value ('qingshan',20) ;
# tx1:
begin; 									-- --------1
select * from users ; 					-- --------2
commit;
# tx2：
begin; 									-- --------3
update teacher set age =28 where id =1; -- --------4
commit;

案例1
按順序執行 1，2，3，4，2

案例2
按順序執行 3，4，1，2

案例一（1，2，3，4，2）

tx1 先執行1,2

tx2 再執行3,4

tx1 再執行2

案例二（3、4、1、2）

tx2 先執行3，4

tx1 再執行1，2

案例二查詢結果不是我們想要的，mysql的Innodb也不是這樣做的，

寫在最后

更多架構知識，歡迎關注本套系列文章：Java架構師成長之路