目錄

1、視圖

?1、檢查選項(xiàng)

?2、視圖的更新

?3、視圖作用

2、存儲(chǔ)過程

1、語法

?2、變量

1、系統(tǒng)變量

?2、用戶定義變量

?3、局部變量

?3、if

?4、參數(shù)

?5、case

6、循環(huán)

1、while

?2、repeat

3、loop

?7、游標(biāo)、條件處理程序

?8、存儲(chǔ)函數(shù)

?3、觸發(fā)器

4、鎖

1、全局鎖

2、表級鎖

1、表鎖

2、元數(shù)據(jù)鎖?

?3、意向鎖

?3、行級鎖

?1、行鎖

2、間隙鎖&臨鍵鎖

5、InnoDB引擎

1、架構(gòu)

?1、內(nèi)存結(jié)構(gòu)

?2、磁盤結(jié)構(gòu)

?3、后臺線程

2、事務(wù)原理

2、undo log

3、MVCC

1、隱藏字段

2、undolog

3、readview?

4、RC隔離級別

5、RR隔離級別

?6、MySQL管理

---

1、視圖

視圖（View）是一種虛擬存在的表。視圖中的數(shù)據(jù)并不在數(shù)據(jù)庫中實(shí)際存在，行和列數(shù)據(jù)來自定義視圖的查詢中使用的表，并且是在使用視圖時(shí)動(dòng)態(tài)生成的。通俗的講，視圖只保存了查詢的SQL邏輯，不保存查詢結(jié)果。所以我們在創(chuàng)建視圖的時(shí)候，主要的工作就落在創(chuàng)建這條SQL查詢語句上。

（1）創(chuàng)建
CREATE [OR REPLACE] VIEW 視圖名稱[(列名列表)] AS SELECT語句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ]（2）查詢
查看創(chuàng)建視圖語句：SHOW CREATE VIEW 視圖名稱;
查看視圖數(shù)據(jù)：SELECT * FROM 視圖名稱 ...... ;（3）修改
方式一：CREATE [OR REPLACE 如果修改此處必須] VIEW 視圖名稱[(列名列表)] AS SELECT語句 [ WITH[ CASCADED | LOCAL ] CHECK OPTION ]方式二：ALTER VIEW 視圖名稱[(列名列表)] AS SELECT語句 [ WITH [ CASCADED |LOCAL ] CHECK OPTION ]（4）刪除
DROP VIEW [IF EXISTS] 視圖名稱 [,視圖名稱] ...  

?

?兩條插入語句都可以執(zhí)行，數(shù)據(jù)會(huì)插入到基表中（tb_stu），但是視圖中卻沒有id為11的數(shù)據(jù)

?tb_stu? ? ? ? ?視圖：

?因?yàn)槲覀冊趧?chuàng)建視圖的時(shí)候，指定的條件為 id<=10, id為17的數(shù)據(jù)，是不符合條件的，所以沒有查詢出來，但是這條數(shù)據(jù)確實(shí)是已經(jīng)成功的插入到了基表中。

?1、檢查選項(xiàng)

當(dāng)使用WITH CHECK OPTION子句創(chuàng)建視圖時(shí)，MySQL會(huì)通過視圖檢查正在更改的每個(gè)行，例如 插入，更新，刪除，以使其符合視圖的定義。 MySQL允許基于另一個(gè)視圖創(chuàng)建視圖，它還會(huì)檢查依賴視圖中的規(guī)則以保持一致性。為了確定檢查的范圍，mysql提供了兩個(gè)選項(xiàng)： CASCADED 和 LOCAL ，默認(rèn)值為 CASCADED 。

（1）CASCADED

級聯(lián)。

比如，v2視圖是基于v1視圖的，如果在v2視圖創(chuàng)建的時(shí)候指定了檢查選項(xiàng)為 cascaded，但是v1視圖創(chuàng)建時(shí)未指定檢查選項(xiàng)。 則在執(zhí)行檢查時(shí)，不僅會(huì)檢查v2，還會(huì)級聯(lián)檢查v2的關(guān)聯(lián)視圖v1。

給 v2 添加數(shù)據(jù)時(shí)不僅要滿足 v2?的條件，還要滿足 v1?的條件，才能添加成功。

給 v3?添加數(shù)據(jù)時(shí)，不會(huì)檢查 v3?的條件，但是會(huì)檢查 v1、v2的條件。

?（2）LOCAL

本地。

比如，v2視圖是基于v1視圖的，如果在v2視圖創(chuàng)建的時(shí)候指定了檢查選項(xiàng)為 local ，但是v1視圖創(chuàng)建時(shí)未指定檢查選項(xiàng)。 則在執(zhí)行檢查時(shí)，知會(huì)檢查v2，不會(huì)檢查v2的關(guān)聯(lián)視圖v1。

?2、視圖的更新

要使視圖可更新，視圖中的行與基礎(chǔ)表中的行之間必須存在一對一的關(guān)系。如果視圖包含以下任何一項(xiàng)，則該視圖不可更新：

1. 聚合函數(shù)或窗口函數(shù)（SUM()、 MIN()、 MAX()、 COUNT()等）
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION 或者 UNION ALL

?3、視圖作用

?（1）簡單

視圖不僅可以簡化用戶對數(shù)據(jù)的理解，也可以簡化他們的操作。那些被經(jīng)常使用的查詢可以被定義為視圖，從而使得用戶不必為以后的操作每次指定全部的條件。

比如把多表聯(lián)查的語句寫成視圖

（2）安全

數(shù)據(jù)庫可以授權(quán)，但不能授權(quán)到數(shù)據(jù)庫特定行和特定的列上。通過視圖用戶只能查詢和修改他們所能見到的數(shù)據(jù)

創(chuàng)建的視圖時(shí)只包含用戶所能看見的字段

?（3）數(shù)據(jù)獨(dú)立

視圖可幫助用戶屏蔽真實(shí)表結(jié)構(gòu)變化帶來的影響。

如果創(chuàng)建視圖時(shí)，基表字段為 name，后來基表 name?改為 stuName，可以在修改視圖 stuName as name

2、存儲(chǔ)過程

1、語法

存儲(chǔ)過程是事先經(jīng)過編譯并存儲(chǔ)在數(shù)據(jù)庫中的一段 SQL 語句的集合，調(diào)用存儲(chǔ)過程可以簡化應(yīng)用開發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫和應(yīng)用服務(wù)器之間的傳輸，對于提高數(shù)據(jù)處理的效率是有好處的。

存儲(chǔ)過程思想上很簡單，就是數(shù)據(jù)庫 SQL 語言層面的代碼封裝與重用。

特點(diǎn):

• 封裝，復(fù)用 ----------> 可以把某一業(yè)務(wù)SQL封裝在存儲(chǔ)過程中，需要用到的時(shí)候直接調(diào)用即可。
• 可以接收參數(shù)，也可以返回?cái)?shù)據(jù) --------> 再存儲(chǔ)過程中，可以傳遞參數(shù)，也可以接收返回值。
• 減少網(wǎng)絡(luò)交互，效率提升 -------------> 如果涉及到多條SQL，每執(zhí)行一次都是一次網(wǎng)絡(luò)傳輸。 而如果封裝在存儲(chǔ)過程中，我們只需要網(wǎng)絡(luò)交互一次可能就可以了。

（1）創(chuàng)建

CREATE PROCEDURE 存儲(chǔ)過程名稱 ([ 參數(shù)列表 ])
BEGIN-- SQL語句
END ;

（2）調(diào)用

CALL 名稱 ([ 參數(shù) ]);  

（3）查看

查詢指定數(shù)據(jù)庫的存儲(chǔ)過程及狀態(tài)信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '數(shù)據(jù)庫名';查詢某個(gè)存儲(chǔ)過程的定義
SHOW CREATE PROCEDURE 存儲(chǔ)過程名稱 ;

（4）刪除

DROP PROCEDURE [ IF EXISTS ] 存儲(chǔ)過程名稱 ； 

注意:
? ? 在命令行中，執(zhí)行創(chuàng)建存儲(chǔ)過程的SQL時(shí)，需要通過關(guān)鍵字 delimiter 指定SQL語句的結(jié)束符。
? ??
? ? 例：
? ? ? ? delimiter &&;
? ? ? ? 指定&&為結(jié)束符代替 ' ; '

create procedure p1()
beginselect count(*) from tb_user;
end

?

?2、變量

在MySQL中變量分為三種類型: 系統(tǒng)變量、用戶定義變量、局部變量。

1、系統(tǒng)變量

系統(tǒng)變量 是MySQL服務(wù)器提供，不是用戶定義的，屬于服務(wù)器層面。分為全局變量（GLOBAL）、會(huì)話變量（SESSION）。

（1）查看系統(tǒng)變量

SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系統(tǒng)變量SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通過LIKE模糊匹配方式查找變量SELECT @@[SESSION | GLOBAL] 系統(tǒng)變量名; -- 查看指定變量的值

（2）設(shè)置系統(tǒng)變量

SET [ SESSION | GLOBAL ] 系統(tǒng)變量名 = 值 ;SET @@[SESSION | GLOBAL]系統(tǒng)變量名 = 值 ;

注意:
????????如果沒有指定SESSION/GLOBAL，默認(rèn)是SESSION，會(huì)話變量。

????????????????mysql服務(wù)重新啟動(dòng)之后，所設(shè)置的全局參數(shù)會(huì)失效，要想不失效，可以在 /etc/my.cnf 中配置。?

????????A. 全局變量(GLOBAL): 全局變量針對于所有的會(huì)話。
????????B. 會(huì)話變量(SESSION): 會(huì)話變量針對于單個(gè)會(huì)話，在另外一個(gè)會(huì)話窗口就不生效了。

?

?

?2、用戶定義變量

?用戶定義變量 是用戶根據(jù)需要自己定義的變量，用戶變量不用提前聲明，在用的時(shí)候直接用 "@變量名" 使用就可以。其作用域?yàn)楫?dāng)前連接。

?（1）賦值

方式一:SET @var_name = expr [, @var_name = expr] ... ;SET @var_name := expr [, @var_name := expr] ... ;賦值時(shí)，可以使用 = ，也可以使用 := 。方式二:SELECT @var_name := expr [, @var_name := expr] ... ;SELECT 字段名 INTO @var_name FROM 表名;

?（2）使用

SELECT @var_name ;  

?注意: 用戶定義的變量無需對其進(jìn)行聲明或初始化，只不過獲取到的值為NULL。

?

?3、局部變量

?局部變量 是根據(jù)需要定義的在局部生效的變量，訪問之前，需要DECLARE聲明?？捎米鞔鎯?chǔ)過程內(nèi)的局部變量和輸入?yún)?shù)，局部變量的范圍是在其內(nèi)聲明的BEGIN ... END塊。

?（1）聲明

DECLARE 變量名 變量類型 [DEFAULT ... ] ; 

變量類型就是數(shù)據(jù)庫字段類型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

（2）賦值

SET 變量名 = 值 ;
SET 變量名 := 值 ;
SELECT 字段名 INTO 變量名 FROM 表名 ... ;

?3、if

if 用于做條件判斷，具體的語法結(jié)構(gòu)為：

IF 條件1 THEN.....
ELSEIF 條件2 THEN -- 可選.....
ELSE -- 可選.....
END IF;

在if條件判斷的結(jié)構(gòu)中，ELSE IF 結(jié)構(gòu)可以有多個(gè)，也可以沒有。 ELSE結(jié)構(gòu)可以有，也可以沒有。

根據(jù)定義的分?jǐn)?shù)score變量，判定當(dāng)前分?jǐn)?shù)對應(yīng)的分?jǐn)?shù)等級。
????????score >= 85分，等級為優(yōu)秀。
????????score >= 60分 且 score < 85分，等級為及格。
????????score < 60分，等級為不及格。

?4、參數(shù)

參數(shù)的類型，主要分為以下三種：IN、OUT、INOUT。 具體的含義如下：

CREATE PROCEDURE 存儲(chǔ)過程名稱 ([ IN/OUT/INOUT 參數(shù)名 參數(shù)類型 ])
BEGIN-- SQL語句
END ;

案例一
? ? 根據(jù)傳入?yún)?shù)score，判定當(dāng)前分?jǐn)?shù)對應(yīng)的分?jǐn)?shù)等級，并返回。
? ? ? ? score >= 85分，等級為優(yōu)秀。
? ? ? ? score >= 60分 且 score < 85分，等級為及格。
? ? ? ? score < 60分，等級為不及格。

案例二
????????將傳入的200分制的分?jǐn)?shù)，進(jìn)行換算，換算成百分制，然后返回。

?5、case

case結(jié)構(gòu)及作用，和流程控制函數(shù)很類似。有兩種語法格式：

語法1：

-- 含義： 當(dāng)case_value的值為 when_value1時(shí)，執(zhí)行statement_list1，當(dāng)值為 when_value2時(shí)，執(zhí)行statement_list2， 否則就執(zhí)行 statement_listCASE case_valueWHEN when_value1 THEN statement_list1[ WHEN when_value2 THEN statement_list2] ...[ ELSE statement_list ]
END CASE;

語法2：

-- 含義： 當(dāng)條件search_condition1成立時(shí)，執(zhí)行statement_list1，當(dāng)條件search_condition2成立時(shí)，執(zhí)行statement_list2， 否則就執(zhí)行 statement_listCASEWHEN search_condition1 THEN statement_list1[WHEN search_condition2 THEN statement_list2] ...[ELSE statement_list]
END CASE;

根據(jù)傳入的月份，判定月份所屬的季節(jié)（要求采用case結(jié)構(gòu)）。
????????1-3月份，為第一季度
????????4-6月份，為第二季度
????????7-9月份，為第三季度
????????10-12月份，為第四季度

如果判定條件有多個(gè)，多個(gè)條件之間，可以使用 and 或 or 進(jìn)行連接。

6、循環(huán)

1、while

while 循環(huán)是有條件的循環(huán)控制語句。滿足條件后，再執(zhí)行循環(huán)體中的SQL語句。具體語法為：

-- 先判定條件，如果條件為true，則執(zhí)行邏輯，否則，不執(zhí)行邏輯WHILE 條件 DOSQL邏輯...
END WHILE;

計(jì)算從1累加到n的值，n為傳入的參數(shù)值。

?2、repeat

repeat是有條件的循環(huán)控制語句, 當(dāng)滿足until聲明的條件的時(shí)候，則退出循環(huán) 。具體語法為：

-- 先執(zhí)行一次邏輯，然后判定UNTIL條件是否滿足，如果滿足，則退出。如果不滿足，則繼續(xù)下一次循環(huán)REPEATSQL邏輯...UNTIL 條件
END REPEAT;

計(jì)算從1累加到n的值，n為傳入的參數(shù)值。(使用repeat實(shí)現(xiàn))

3、loop

LOOP 實(shí)現(xiàn)簡單的循環(huán)，如果不在SQL邏輯中增加退出循環(huán)的條件，可以用其來實(shí)現(xiàn)簡單的死循環(huán)。

LOOP可以配合一下兩個(gè)語句使用：

• LEAVE ：配合循環(huán)使用，退出循環(huán)。
• ITERATE：必須用在循環(huán)中，作用是跳過當(dāng)前循環(huán)剩下的語句，直接進(jìn)入下一次循環(huán)。

[begin_label:] LOOPSQL邏輯...
END LOOP [end_label];

LEAVE label; -- 退出指定標(biāo)記的循環(huán)體
ITERATE label; -- 直接進(jìn)入下一次循環(huán)

上述語法中出現(xiàn)的 begin_label，end_label，label 指的都是我們所自定義的標(biāo)記。

案例一
????????計(jì)算從1累加到n的值，n為傳入的參數(shù)值。

案例二
????????計(jì)算從1到n之間的偶數(shù)累加的值，n為傳入的參數(shù)值。

?7、游標(biāo)、條件處理程序

游標(biāo)（CURSOR）是用來存儲(chǔ)查詢結(jié)果集的數(shù)據(jù)類型 , 在存儲(chǔ)過程和函數(shù)中可以使用游標(biāo)對結(jié)果集進(jìn)行循環(huán)的處理。游標(biāo)的使用包括游標(biāo)的聲明、OPEN、FETCH 和 CLOSE，其語法分別如下。

（1）聲明游標(biāo)

DECLARE 游標(biāo)名稱 CURSOR FOR 查詢語句 ; 

（2）打開游標(biāo)

OPEN 游標(biāo)名稱 ; 

（3）獲取游標(biāo)記錄

FETCH 游標(biāo)名稱 INTO 變量 [, 變量 ] ; 

（4）關(guān)閉游標(biāo)

CLOSE 游標(biāo)名稱 ; 

條件處理程序（Handler）可以用來定義在流程控制結(jié)構(gòu)執(zhí)行過程中遇到問題時(shí)相應(yīng)的處理步驟。具體語法為：?

DECLARE handler_action HANDLER FOR condition_value [, condition_value]
... statement ;handler_action 的取值：CONTINUE: 繼續(xù)執(zhí)行當(dāng)前程序EXIT: 終止執(zhí)行當(dāng)前程序condition_value 的取值：SQLSTATE sqlstate_value: 狀態(tài)碼，如 02000SQLWARNING: 所有以01開頭的SQLSTATE代碼的簡寫NOT FOUND: 所有以02開頭的SQLSTATE代碼的簡寫SQLEXCEPTION: 所有沒有被SQLWARNING 或 NOT FOUND捕獲的SQLSTATE代碼的簡寫

根據(jù)傳入的參數(shù)uage，來查詢用戶表tb_user中，所有的用戶年齡小于等于uage的用戶姓名（name）和專業(yè)（profession），并將用戶的姓名和專業(yè)插入到所創(chuàng)建的一張新表(id,name,profession)中。

?

?02 開頭的狀態(tài)碼，代碼簡寫為 NOT FOUND

?8、存儲(chǔ)函數(shù)

存儲(chǔ)函數(shù)是有返回值的存儲(chǔ)過程，存儲(chǔ)函數(shù)的參數(shù)只能是IN類型的。具體語法如下：

CREATE FUNCTION 存儲(chǔ)函數(shù)名稱 ([ 參數(shù)列表 ])
RETURNS type [characteristic ...]
BEGIN-- SQL語句RETURN ...;
END ;

characteristic說明：

• DETERMINISTIC（deterministic）：相同的輸入?yún)?shù)總是產(chǎn)生相同的結(jié)果
• NO SQL ：不包含 SQL 語句。
• READS SQL DATA（reads sql data）：包含讀取數(shù)據(jù)的語句，但不包含寫入數(shù)據(jù)的語句。

計(jì)算從1累加到n的值，n為傳入的參數(shù)值。

?3、觸發(fā)器

觸發(fā)器是與表有關(guān)的數(shù)據(jù)庫對象，指在 insert/update/delete 之前(BEFORE)或之后(AFTER)，觸發(fā)并執(zhí)行觸發(fā)器中定義的SQL語句集合。觸發(fā)器的這種特性可以協(xié)助應(yīng)用在數(shù)據(jù)庫端確保數(shù)據(jù)的完整性, 日志記錄 , 數(shù)據(jù)校驗(yàn)等操作 。

使用別名OLD和NEW來引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容，這與其他的數(shù)據(jù)庫是相似的?，F(xiàn)在觸發(fā)器還只支持行級觸發(fā)（一條sql語句影響到多行，每影響一行就記錄一次），不支持語句級觸發(fā)（一條sql語句影響到多行，只記錄一次）。

?（1）創(chuàng)建

CREATE TRIGGER trigger_nameBEFORE/AFTER INSERT/UPDATE/DELETE ON tbl_name FOR EACH ROW -- 行級觸發(fā)器BEGINtrigger_stmt ;
END;

（2）查看

SHOW TRIGGERS ; 

（3）刪除

DROP TRIGGER [schema_name.]trigger_name ; -- 如果沒有指定 schema_name，默認(rèn)為當(dāng)前數(shù)據(jù)庫 。

通過觸發(fā)器記錄 tb_user 表的數(shù)據(jù)變更日志，將變更日志插入到日志表user_logs中, 包含增加,修改 , 刪除 ;

-- 準(zhǔn)備工作 : 日志表 user_logs
create table user_logs(id int(11) not null auto_increment,operation varchar(20) not null comment '操作類型, insert/update/delete',operate_time datetime not null comment '操作時(shí)間',operate_id int(11) not null comment '操作的ID',operate_params varchar(500) comment '操作參數(shù)',primary key(`id`)
)engine=innodb default charset=utf8;

?

?

?

4、鎖

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制。在數(shù)據(jù)庫中，除傳統(tǒng)的計(jì)算資源（CPU、RAM、I/O）的爭用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個(gè)問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個(gè)重要因素。

MySQL中的鎖，按照鎖的粒度分，分為以下三類：

• 全局鎖：鎖定數(shù)據(jù)庫中的所有表。
• 表級鎖：每次操作鎖住整張表。
• 行級鎖：每次操作鎖住對應(yīng)的行數(shù)據(jù)。

1、全局鎖

全局鎖就是對整個(gè)數(shù)據(jù)庫實(shí)例加鎖，加鎖后整個(gè)實(shí)例就處于只讀狀態(tài)，后續(xù)的DML的寫語句，DDL語句，已經(jīng)更新操作的事務(wù)提交語句都將被阻塞。

其典型的使用場景是做全庫的邏輯備份，對所有的表進(jìn)行鎖定，從而獲取一致性視圖，保證數(shù)據(jù)的完整性。

假設(shè)在數(shù)據(jù)庫中存在這樣三張表: tb_stock 庫存表，tb_order 訂單表，tb_orderlog 訂單日志表。

• ?在進(jìn)行數(shù)據(jù)備份時(shí)，先備份了tb_stock庫存表。
• 然后接下來，在業(yè)務(wù)系統(tǒng)中，執(zhí)行了下單操作，扣減庫存，生成訂單（更新tb_stock表，插入tb_order表）。
• 然后再執(zhí)行備份 tb_order表的邏輯。
• 業(yè)務(wù)中執(zhí)行插入訂單日志操作。
• 最后，又備份了tb_orderlog表。

此時(shí)備份出來的數(shù)據(jù)，是存在問題的。因?yàn)閭浞莩鰜淼臄?shù)據(jù)，tb_stock表與tb_order表的數(shù)據(jù)不一致(有最新操作的訂單信息,但是庫存數(shù)沒減)。

那如何來規(guī)避這種問題呢? 此時(shí)就可以借助于MySQL的全局鎖來解決。

全局鎖后的情況：

?對數(shù)據(jù)庫進(jìn)行進(jìn)行邏輯備份之前，先對整個(gè)數(shù)據(jù)庫加上全局鎖，一旦加了全局鎖之后，其他的DDL、DML全部都處于阻塞狀態(tài)，但是可以執(zhí)行DQL語句，也就是處于只讀狀態(tài)，而數(shù)據(jù)備份就是查詢操作。

那么數(shù)據(jù)在進(jìn)行邏輯備份的過程中，數(shù)據(jù)庫中的數(shù)據(jù)就是不會(huì)發(fā)生變化的，這樣就保證了數(shù)據(jù)的一致性和完整性。

（1）加全局鎖

flush tables with read lock ; 

（2）數(shù)據(jù)備份

mysqldump -uroot –p1234 itcast > itcast.sql 

（3）釋放鎖

unlock tables ; 

數(shù)據(jù)庫中加全局鎖，是一個(gè)比較重的操作，存在以下問題：

• 如果在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)基本上就得停擺。
• 如果在從庫上備份，那么在備份期間從庫不能執(zhí)行主庫同步過來的二進(jìn)制日志（binlog），會(huì)導(dǎo)致主從延遲。

在InnoDB引擎中，我們可以在備份時(shí)加上參數(shù) --single-transaction 參數(shù)來完成不加鎖的一致性數(shù)據(jù)備份。

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql 

2、表級鎖

表級鎖，每次操作鎖住整張表。鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。應(yīng)用在MyISAM、InnoDB、BDB等存儲(chǔ)引擎中。

對于表級鎖，主要分為以下三類：

• 表鎖
• 元數(shù)據(jù)鎖（meta data lock，MDL）
• 意向鎖

1、表鎖

于表鎖，分為兩類：

• 表共享讀鎖（read lock）
• 表獨(dú)占寫鎖（write lock）

語法：

• 加鎖：lock tables 表名... read/write。
• 釋放鎖：unlock tables / 客戶端斷開連接 。

特點(diǎn)：

（1）讀鎖

????????

左側(cè)為客戶端一，對指定表加了讀鎖，不會(huì)影響右側(cè)客戶端二的讀，但是會(huì)阻塞右側(cè)客戶端的寫。

（2）寫鎖

?左側(cè)為客戶端一，對指定表加了寫鎖，會(huì)阻塞右側(cè)客戶端的讀和寫。

結(jié)論: 讀鎖不會(huì)阻塞其他客戶端的讀，但是會(huì)阻塞寫。寫鎖既會(huì)阻塞其他客戶端的讀，又會(huì)阻塞其他客戶端的寫。

2、元數(shù)據(jù)鎖?

meta data lock , 元數(shù)據(jù)鎖，簡寫MDL。

MDL加鎖過程是系統(tǒng)自動(dòng)控制，無需顯式使用，在訪問一張表的時(shí)候會(huì)自動(dòng)加上。MDL鎖主要作用是維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動(dòng)事務(wù)的時(shí)候，不可以對元數(shù)據(jù)進(jìn)行寫入操作。為了避免DML與DDL沖突，保證讀寫的正確性。

這里的元數(shù)據(jù)，大家可以簡單理解為就是一張表的表結(jié)構(gòu)。 也就是說，某一張表涉及到未提交的事務(wù)時(shí)，是不能夠修改這張表的表結(jié)構(gòu)的。

在MySQL5.5中引入了MDL，當(dāng)對一張表進(jìn)行增刪改查的時(shí)候，加MDL讀鎖(共享)；當(dāng)對表結(jié)構(gòu)進(jìn)行變更操作的時(shí)候，加MDL寫鎖(排他)。

常見的SQL操作時(shí)，所添加的元數(shù)據(jù)鎖：

當(dāng)執(zhí)行SELECT、INSERT、UPDATE、DELETE等語句時(shí)，添加的是元數(shù)據(jù)共享鎖（SHARED_READ /SHARED_WRITE），之間是兼容的。

當(dāng)執(zhí)行SELECT語句時(shí)，添加的是元數(shù)據(jù)共享鎖（SHARED_READ），會(huì)阻塞元數(shù)據(jù)排他鎖（EXCLUSIVE），之間是互斥的。

查看數(shù)據(jù)庫中的元數(shù)據(jù)鎖的情況：

select object_type,object_schema,object_name,lock_type,lock_duration fromperformance_schema.metadata_locks ;

?3、意向鎖

為了避免DML在執(zhí)行時(shí)，加的行鎖與表鎖的沖突，在InnoDB中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來減少表鎖的檢查。

假如沒有意向鎖，客戶端一對表加了行鎖后，客戶端二如何給表加表鎖呢，來通過示意圖簡單分析一下：

• 首先客戶端一，開啟一個(gè)事務(wù)，然后執(zhí)行DML操作，在執(zhí)行DML語句時(shí)，會(huì)對涉及到的行加行鎖。

• ?當(dāng)客戶端二，想對這張表加表鎖時(shí)，會(huì)檢查當(dāng)前表是否有對應(yīng)的行鎖，如果沒有，則添加表鎖，此時(shí)就會(huì)從第一行數(shù)據(jù)，檢查到最后一行數(shù)據(jù)，效率較低。

?有了意向鎖之后 :

• 客戶端一，在執(zhí)行DML操作時(shí)，會(huì)對涉及的行加行鎖，同時(shí)也會(huì)對該表加上意向鎖。

• ?而其他客戶端，在對這張表加表鎖的時(shí)候，會(huì)根據(jù)該表上所加的意向鎖來判定是否可以成功加表鎖，而不用逐行判斷行鎖情況了。

分類：

• 意向共享鎖(IS): 由語句select ... lock in share mode添加 。 與 表鎖共享鎖(read)兼容，與表鎖排他鎖(write)互斥。
• 意向排他鎖(IX): 由insert、update、delete、select...for update添加 。與表鎖共享鎖(read)及排他鎖(write)都互斥，意向鎖之間不會(huì)互斥。

一旦事務(wù)提交了，意向共享鎖、意向排他鎖，都會(huì)自動(dòng)釋放。

查看意向鎖及行鎖的加鎖情況：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

?3、行級鎖

行級鎖，每次操作鎖住對應(yīng)的行數(shù)據(jù)。鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度最高。應(yīng)用在InnoDB存儲(chǔ)引擎中。

InnoDB的數(shù)據(jù)是基于索引組織的，行鎖是通過對索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，而不是對記錄加的鎖。對于行級鎖，主要分為以下三類：

• 行鎖（Record Lock）：鎖定單個(gè)行記錄的鎖，防止其他事務(wù)對此行進(jìn)行update和delete。在RC、RR隔離級別下都支持。

• ?間隙鎖（Gap Lock）：鎖定索引記錄間隙（不含該記錄），確保索引記錄間隙不變，防止其他事務(wù)在這個(gè)間隙進(jìn)行insert，產(chǎn)生幻讀。在RR隔離級別下都支持。

• ?臨鍵鎖（Next-Key Lock）：行鎖和間隙鎖組合，同時(shí)鎖住數(shù)據(jù)，并鎖住數(shù)據(jù)前面的間隙Gap。在RR隔離級別下支持。

?1、行鎖

InnoDB實(shí)現(xiàn)了以下兩種類型的行鎖：

• 共享鎖（S）：允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排它鎖。
• 排他鎖（X）：允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖。

?

?默認(rèn)情況下，InnoDB在 REPEATABLE READ事務(wù)隔離級別運(yùn)行，InnoDB使用 next-key 鎖進(jìn)行搜索和索引掃描，以防止幻讀。

• 針對唯一索引進(jìn)行檢索時(shí)，對已存在的記錄進(jìn)行等值匹配時(shí)，將會(huì)自動(dòng)優(yōu)化為行鎖。
• InnoDB的行鎖是針對于索引加的鎖，不通過索引條件檢索數(shù)據(jù)，那么InnoDB將對表中的所有記錄加鎖，此時(shí) 就會(huì)升級為表鎖。

查看意向鎖及行鎖的加鎖情況：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

• 普通的select語句，執(zhí)行時(shí)，不會(huì)加鎖。

• ?select...lock in share mode，加共享鎖，共享鎖與共享鎖之間兼容?？蛻舳?、2開啟事物都可以成功執(zhí)行?select...?id = 1 lock in share mode

• ?共享鎖與排他鎖之間互斥?？蛻舳?執(zhí)行?select ..... id = 1 lock in share mode; 后，客戶端 2?執(zhí)行類似于 update ...?

客戶端1 獲取的是id為1這行的共享鎖，客戶端二是可以獲取id為3這行的排它鎖的，因?yàn)椴皇峭恍袛?shù)據(jù)。 而如果客戶端二想獲取id為1這行的排他鎖，會(huì)處于阻塞狀態(tài)，以為共享鎖與排他鎖之間互斥。

• 排它鎖與排他鎖之間互斥?？蛻舳?、2都執(zhí)行update stu set name = 'tom' where id = 1

當(dāng)客戶端一，執(zhí)行update語句，會(huì)為id為1的記錄加排他鎖； 客戶端二，如果也執(zhí)行update語句更新id為1的數(shù)據(jù)，也要為id為1的數(shù)據(jù)加排他鎖，但是客戶端二會(huì)處于阻塞狀態(tài)，因?yàn)榕潘i之間是互斥的。

直到客戶端一，把事務(wù)提交了，才會(huì)把這一行的行鎖釋放，此時(shí)客戶端二，解除阻塞。

• 無索引行鎖升級為表鎖（age?沒有?索引）

?

客戶端一，根據(jù)name字段進(jìn)行更新時(shí)，name字段是沒有索引的，如果沒有索引，此時(shí)行鎖會(huì)升級為表鎖(因?yàn)樾墟i是對索引項(xiàng)加的鎖，而name沒有索引)。

針對name字段建立索引，上方兩條sql語句可正常執(zhí)行。這樣就說明，我們根據(jù)索引字段進(jìn)行更新操作，就可以避免行鎖升級為表鎖的情況。

2、間隙鎖&臨鍵鎖

默認(rèn)情況下，InnoDB在 REPEATABLE READ事務(wù)隔離級別運(yùn)行，InnoDB使用 next-key 鎖進(jìn)行搜索和索引掃描，以防止幻讀。

• 索引上的等值查詢(唯一索引)，給不存在的記錄加鎖時(shí), 優(yōu)化為間隙鎖 。

?

?由于把8之前的間隙鎖住了,所以不能插入id為7的數(shù)據(jù)

• 索引上的等值查詢(非唯一普通索引)，向右遍歷時(shí)最后一個(gè)值不滿足查詢需求時(shí)，next-keylock 退化為間隙鎖。

給age加普通索引

我們知道InnoDB的B+樹索引，葉子節(jié)點(diǎn)是有序的雙向鏈表。 假如，我們要根據(jù)這個(gè)二級索引查詢值為18的數(shù)據(jù)，并加上共享鎖，我們是只鎖定18這一行就可以了嗎？ 并不是，因?yàn)槭?strong>非唯一索引。

這個(gè)結(jié)構(gòu)中可能有多個(gè)18的存在，所以，在加鎖時(shí)會(huì)繼續(xù)往后找，找到一個(gè)不滿足條件的值（當(dāng)前案例中也就是29）。此時(shí)會(huì)對18加臨鍵鎖，并對29之前的間隙加鎖。

• 索引上的范圍查詢(唯一索引)--會(huì)訪問到不滿足條件的第一個(gè)值為止。

?查詢的條件為id>=19，并添加共享鎖。 此時(shí)我們可以根據(jù)數(shù)據(jù)庫表中現(xiàn)有的數(shù)據(jù)，將數(shù)據(jù)分為三個(gè)部分：[19]、(19,25]、(25,+∞]

所以數(shù)據(jù)庫數(shù)據(jù)在加鎖時(shí)，就是將19加了行鎖，25的臨鍵鎖（包含25及25之前的間隙），正無窮的臨鍵鎖(正無窮及之前的間隙)。

注意：間隙鎖唯一目的是防止其他事務(wù)插入間隙。間隙鎖可以共存，一個(gè)事務(wù)采用的間隙鎖不會(huì)阻止另一個(gè)事務(wù)在同一間隙上采用間隙鎖。

5、InnoDB引擎

InnoDB的邏輯存儲(chǔ)結(jié)構(gòu)如下圖所示:

?（1）表空間

表空間是InnoDB存儲(chǔ)引擎邏輯結(jié)構(gòu)的最高層， 如果用戶啟用了參數(shù) innodb_file_per_table(在8.0版本中默認(rèn)開啟) ，則每張表都會(huì)有一個(gè)表空間（xxx.ibd），一個(gè)mysql實(shí)例可以對應(yīng)多個(gè)表空間，用于存儲(chǔ)記錄、索引等數(shù)據(jù)。

（2）段

段，分為數(shù)據(jù)段（Leaf node segment）、索引段（Non-leaf node segment）、回滾段（Rollback segment），InnoDB是索引組織表，數(shù)據(jù)段就是B+樹的葉子節(jié)點(diǎn)， 索引段即為B+樹的非葉子節(jié)點(diǎn)。段用來管理多個(gè)Extent（區(qū)）。

（3）區(qū)

區(qū)，表空間的單元結(jié)構(gòu)，每個(gè)區(qū)的大小為1M。 默認(rèn)情況下， InnoDB存儲(chǔ)引擎頁大小為16K， 即一個(gè)區(qū)中一共有64個(gè)連續(xù)的頁。

（4）頁

頁，是InnoDB 存儲(chǔ)引擎磁盤管理的最小單元，每個(gè)頁的大小默認(rèn)為 16KB。為了保證頁的連續(xù)性，InnoDB 存儲(chǔ)引擎每次從磁盤申請 4-5 個(gè)區(qū)。

（5）行
行，InnoDB 存儲(chǔ)引擎數(shù)據(jù)是按行進(jìn)行存放的。

在行中，默認(rèn)有兩個(gè)隱藏字段：

• Trx_id：每次對某條記錄進(jìn)行改動(dòng)時(shí)，都會(huì)把對應(yīng)的事務(wù)id賦值給trx_id隱藏列。
• Roll_pointer：每次對某條引記錄進(jìn)行改動(dòng)時(shí)，都會(huì)把舊的版本寫入到undo日志中，然后這個(gè)隱藏列就相當(dāng)于一個(gè)指針，可以通過它來找到該記錄修改前的信息。

1、架構(gòu)

MySQL5.5 版本開始，默認(rèn)使用InnoDB存儲(chǔ)引擎，它擅長事務(wù)處理，具有崩潰恢復(fù)特性，在日常開發(fā)中使用非常廣泛。下面是InnoDB架構(gòu)圖，左側(cè)為內(nèi)存結(jié)構(gòu)，右側(cè)為磁盤結(jié)構(gòu)。

?1、內(nèi)存結(jié)構(gòu)

?在左側(cè)的內(nèi)存結(jié)構(gòu)中，主要分為這么四大塊兒： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。 接下來介紹一下這四個(gè)部分。

（1）Buffer Pool

InnoDB存儲(chǔ)引擎基于磁盤文件存儲(chǔ)，訪問物理硬盤和在內(nèi)存中進(jìn)行訪問，速度相差很大，為了盡可能彌補(bǔ)這兩者之間的I/O效率的差值，就需要把經(jīng)常使用的數(shù)據(jù)加載到緩沖池中，避免每次訪問都進(jìn)行磁盤I/O。

在InnoDB的緩沖池中不僅緩存了索引頁和數(shù)據(jù)頁，還包含了undo頁、插入緩存、自適應(yīng)哈希索引以及InnoDB的鎖信息等等。

緩沖池 Buffer Pool，是主內(nèi)存中的一個(gè)區(qū)域，里面可以緩存磁盤上經(jīng)常操作的真實(shí)數(shù)據(jù)，在執(zhí)行增刪改查操作時(shí)，先操作緩沖池中的數(shù)據(jù)（若緩沖池沒有數(shù)據(jù)，則從磁盤加載并緩存），然后再以一定頻率刷新到磁盤，從而減少磁盤IO，加快處理速度。

緩沖池以Page頁為單位，底層采用鏈表數(shù)據(jù)結(jié)構(gòu)管理Page。根據(jù)狀態(tài)，將Page分為三種類型：

• free page：空閑page，未被使用。
• clean page：被使用page，數(shù)據(jù)沒有被修改過。
• dirty page：臟頁，被使用page，數(shù)據(jù)被修改過，也中數(shù)據(jù)與磁盤的數(shù)據(jù)產(chǎn)生了不一致。

在專用服務(wù)器上，通常將多達(dá)80％的物理內(nèi)存分配給緩沖池 。參數(shù)設(shè)置： show variables like 'innodb_buffer_pool_size';

?（2）Change Buffer

Change Buffer，更改緩沖區(qū)（針對于非唯一二級索引頁），在執(zhí)行DML語句時(shí)，如果這些數(shù)據(jù)Page沒有在Buffer Pool中，不會(huì)直接操作磁盤，而會(huì)將數(shù)據(jù)變更存在更改緩沖區(qū) Change Buffer中，在未來數(shù)據(jù)被讀取時(shí)，再將數(shù)據(jù)合并恢復(fù)到Buffer Pool中，再將合并后的數(shù)據(jù)刷新到磁盤中。

這個(gè)是二級索引的結(jié)構(gòu)圖：

?與聚集索引不同，二級索引通常是非唯一的，并且以相對隨機(jī)的順序插入二級索引。同樣，刪除和更新可能會(huì)影響索引樹中不相鄰的二級索引頁，如果每一次都操作磁盤，會(huì)造成大量的磁盤IO。

有了ChangeBuffer之后，我們可以在緩沖池中進(jìn)行合并處理，減少磁盤IO。

（3）Adaptive Hash Index

自適應(yīng)hash索引，用于優(yōu)化對Buffer Pool數(shù)據(jù)的查詢。MySQL的innoDB引擎中雖然沒有直接支持hash索引，但是給我們提供了一個(gè)功能就是這個(gè)自適應(yīng)hash索引。hash索引在進(jìn)行等值匹配時(shí)，一般性能是要高于B+樹的，因?yàn)閔ash索引一般只需要一次IO即可，而B+樹，可能需要幾次匹配，所以hash索引的效率要高，但是hash索引又不適合做范圍查詢、模糊匹配等。

InnoDB存儲(chǔ)引擎會(huì)監(jiān)控對表上各索引頁的查詢，如果觀察到在特定的條件下hash索引可以提升速度，則建立hash索引，稱之為自適應(yīng)hash索引。自適應(yīng)哈希索引，無需人工干預(yù)，是系統(tǒng)根據(jù)情況自動(dòng)完成。

參數(shù)： adaptive_hash_index

（4）Log Buffer

Log Buffer：日志緩沖區(qū)，用來保存要寫入到磁盤中的log日志數(shù)據(jù)（redo log 、undo log），默認(rèn)大小為 16MB，日志緩沖區(qū)的日志會(huì)定期刷新到磁盤中。如果需要更新、插入或刪除許多行的事務(wù)，增加日志緩沖區(qū)的大小可以節(jié)省磁盤 I/O。

參數(shù):

• innodb_log_buffer_size：緩沖區(qū)大小
• innodb_flush_log_at_trx_commit：日志刷新到磁盤時(shí)機(jī)，取值主要包含以下三個(gè)：
  • 1: 日志在每次事務(wù)提交時(shí)寫入并刷新到磁盤，默認(rèn)值。
  • 0: 每秒將日志寫入并刷新到磁盤一次。
  • 2: 日志在每次事務(wù)提交后寫入，并每秒刷新到磁盤一次。

?2、磁盤結(jié)構(gòu)

?（1）System Tablespace

系統(tǒng)表空間是更改緩沖區(qū)的存儲(chǔ)區(qū)域。如果表是在系統(tǒng)表空間而不是每個(gè)表文件或通用表空間中創(chuàng)建的，它也可能包含表和索引數(shù)據(jù)。(在MySQL5.x版本中還包含InnoDB數(shù)據(jù)字典、undolog等)

參數(shù)：innodb_data_file_path

?系統(tǒng)表空間，默認(rèn)的文件名叫 ibdata1。

（2）File-Per-Table Tablespaces

如果開啟了innodb_file_per_table開關(guān) ，則每個(gè)表的文件表空間包含單個(gè)InnoDB表的數(shù)據(jù)和索引 ，并存儲(chǔ)在文件系統(tǒng)上的單個(gè)數(shù)據(jù)文件中。

開關(guān)參數(shù)：innodb_file_per_table ，該參數(shù)默認(rèn)開啟。

?那也就是說，我們每創(chuàng)建一個(gè)表，都會(huì)產(chǎn)生一個(gè)表空間文件

（3）General Tablespaces

通用表空間，需要通過 CREATE TABLESPACE 語法創(chuàng)建通用表空間，在創(chuàng)建表時(shí)，可以指定該表空間。

A. 創(chuàng)建表空間

CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name; 

B. 創(chuàng)建表時(shí)指定表空間

CREATE TABLE xxx ... TABLESPACE ts_name; 

（4）Undo Tablespaces

撤銷表空間，MySQL實(shí)例在初始化時(shí)會(huì)自動(dòng)創(chuàng)建兩個(gè)默認(rèn)的undo表空間（初始大小16M），用于存儲(chǔ)undo log日志。

（5）Temporary Tablespaces

InnoDB 使用會(huì)話臨時(shí)表空間和全局臨時(shí)表空間。存儲(chǔ)用戶創(chuàng)建的臨時(shí)表等數(shù)據(jù)。

（6）Doublewrite Buffer Files

雙寫緩沖區(qū)，innoDB引擎將數(shù)據(jù)頁從Buffer Pool刷新到磁盤前，先將數(shù)據(jù)頁寫入雙寫緩沖區(qū)文件中，便于系統(tǒng)異常時(shí)恢復(fù)數(shù)據(jù)。

?（7）Redo Log

重做日志，是用來實(shí)現(xiàn)事務(wù)的持久性。該日志文件由兩部分組成：重做日志緩沖（redo logbuffer）以及重做日志文件（redo log）,前者是在內(nèi)存中，后者在磁盤中。當(dāng)事務(wù)提交之后會(huì)把所有修改信息都會(huì)存到該日志中, 用于在刷新臟頁到磁盤時(shí),發(fā)生錯(cuò)誤時(shí), 進(jìn)行數(shù)據(jù)恢復(fù)使用。

以循環(huán)方式寫入重做日志文件，涉及兩個(gè)文件：

?3、后臺線程

?在InnoDB的后臺線程中，分為4類，分別是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread。

（1）Master Thread

核心后臺線程，負(fù)責(zé)調(diào)度其他線程，還負(fù)責(zé)將緩沖池中的數(shù)據(jù)異步刷新到磁盤中, 保持?jǐn)?shù)據(jù)的一致性，還包括臟頁的刷新、合并插入緩存、undo頁的回收 。

（2）IO Thread

在InnoDB存儲(chǔ)引擎中大量使用了AIO來處理IO請求, 這樣可以極大地提高數(shù)據(jù)庫的性能，而IOThread主要負(fù)責(zé)這些IO請求的回調(diào)。

通過下列指令，查看到InnoDB的狀態(tài)信息，其中就包含IO Thread信息。

show engine innodb status; 

（3）Purge Thread

主要用于回收事務(wù)已經(jīng)提交了的undo log，在事務(wù)提交之后，undo log可能不用了，就用它來回收。

（4）Page Cleaner Thread

協(xié)助 Master Thread 刷新臟頁到磁盤的線程，它可以減輕 Master Thread 的工作壓力，減少阻塞。

2、事務(wù)原理

事務(wù) 是一組操作的集合，它是一個(gè)不可分割的工作單位，事務(wù)會(huì)把所有的操作作為一個(gè)整體一起向系統(tǒng)提交或撤銷操作請求，即這些操作要么同時(shí)成功，要么同時(shí)失敗。

特性：

• 原子性（Atomicity）：事務(wù)是不可分割的最小操作單元，要么全部成功，要么全部失敗。
• 一致性（Consistency）：事務(wù)完成時(shí)，必須使所有的數(shù)據(jù)都保持一致狀態(tài)。
• 隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的獨(dú)立環(huán)境下運(yùn)行。
• 持久性（Durability）：事務(wù)一旦提交或回滾，它對數(shù)據(jù)庫中的數(shù)據(jù)的改變就是永久的。

而對于這四大特性，實(shí)際上分為兩個(gè)部分。 其中的原子性、一致性、持久化，實(shí)際上是由InnoDB中的兩份日志來保證的，一份是redo log日志，一份是undo log日志。 而持久性是通過數(shù)據(jù)庫的鎖，加上MVCC來保證的。

?1、redo log

重做日志，記錄的是事務(wù)提交時(shí)數(shù)據(jù)頁的物理修改，是用來實(shí)現(xiàn)事務(wù)的持久性。

該日志文件由兩部分組成：重做日志緩沖（redo log buffer）以及重做日志文件（redo logfile）,前者是在內(nèi)存中，后者在磁盤中。當(dāng)事務(wù)提交之后會(huì)把所有修改信息都存到該日志文件中, 用于在刷新臟頁到磁盤,發(fā)生錯(cuò)誤時(shí), 進(jìn)行數(shù)據(jù)恢復(fù)使用。

如果沒有redolog，在InnoDB引擎中的內(nèi)存結(jié)構(gòu)中，主要的內(nèi)存區(qū)域就是緩沖池，在緩沖池中緩存了很多的數(shù)據(jù)頁。 當(dāng)我們在一個(gè)事務(wù)中，執(zhí)行多個(gè)增刪改的操作時(shí)，InnoDB引擎會(huì)先操作緩沖池中的數(shù)據(jù)，如果緩沖區(qū)沒有對應(yīng)的數(shù)據(jù)，會(huì)通過后臺線程將磁盤中的數(shù)據(jù)加載出來，存放在緩沖區(qū)中，然后將緩沖池中的數(shù)據(jù)修改，修改后的數(shù)據(jù)頁我們稱為臟頁。 而臟頁則會(huì)在一定的時(shí)機(jī)，通過后臺線程刷新到磁盤中，從而保證緩沖區(qū)與磁盤的數(shù)據(jù)一致。 而緩沖區(qū)的臟頁數(shù)據(jù)并不是實(shí)時(shí)刷新的，而是一段時(shí)間之后將緩沖區(qū)的數(shù)據(jù)刷新到磁盤中，假如刷新到磁盤的過程出錯(cuò)了，而提示給用戶事務(wù)提交成功，而數(shù)據(jù)卻沒有持久化下來，這就出現(xiàn)問題了，沒有保證事務(wù)的持久性。

有了redolog之后，當(dāng)對緩沖區(qū)的數(shù)據(jù)進(jìn)行增刪改之后，會(huì)首先將操作的數(shù)據(jù)頁的變化，記錄在redolog buffer中。在事務(wù)提交時(shí)，會(huì)將redo log buffer中的數(shù)據(jù)刷新到redo log磁盤文件中。過一段時(shí)間之后，如果刷新緩沖區(qū)的臟頁到磁盤時(shí)，發(fā)生錯(cuò)誤，此時(shí)就可以借助于redo log進(jìn)行數(shù)據(jù)恢復(fù)，這樣就保證了事務(wù)的持久性。 而如果臟頁成功刷新到磁盤 或 或者涉及到的數(shù)據(jù)已經(jīng)落盤，此時(shí)redolog就沒有作用了，就可以刪除了，所以存在的兩個(gè)redolog文件是循環(huán)寫的。

?在業(yè)務(wù)操作中，我們操作數(shù)據(jù)一般都是隨機(jī)讀寫磁盤的，而不是順序讀寫磁盤。 而redo log在往磁盤文件中寫入數(shù)據(jù)，由于是日志文件，所以都是順序?qū)懙?。順序?qū)懙男?#xff0c;要遠(yuǎn)大于隨機(jī)寫。 這種先寫日志的方式，稱之為 WAL（Write-Ahead Logging）。所以要每一次提交事務(wù)，要刷新redo log 到磁盤中呢，而不是直接將buffer pool中的臟頁刷新到磁盤中。

2、undo log

回滾日志，用于記錄數(shù)據(jù)被修改前的信息 , 作用包含兩個(gè) : 提供回滾(保證事務(wù)的原子性) 和MVCC(多版本并發(fā)控制) 。

undo log和redo log記錄物理日志不一樣，它是邏輯日志。可以認(rèn)為當(dāng)delete一條記錄時(shí)，undolog中會(huì)記錄一條對應(yīng)的insert記錄，反之亦然，當(dāng)update一條記錄時(shí)，它記錄一條對應(yīng)相反的update記錄。當(dāng)執(zhí)行rollback時(shí)，就可以從undo log中的邏輯記錄讀取到相應(yīng)的內(nèi)容并進(jìn)行回滾。

• Undo log銷毀：undo log在事務(wù)執(zhí)行時(shí)產(chǎn)生，事務(wù)提交時(shí)，并不會(huì)立即刪除undo log，因?yàn)檫@些日志可能還用于MVCC。
• Undo log存儲(chǔ)：undo log采用段的方式進(jìn)行管理和記錄，存放在前面介紹的 rollback segment回滾段中，內(nèi)部包含1024個(gè)undo log segment。

3、MVCC

（1）當(dāng)前讀

讀取的是記錄的最新版本，讀取時(shí)還要保證其他并發(fā)事務(wù)不能修改當(dāng)前記錄，會(huì)對讀取的記錄進(jìn)行加鎖。對于我們?nèi)粘５牟僮?#xff0c;如：select ... lock in share mode(共享鎖)，select ...for update、update、insert、delete(排他鎖)都是一種當(dāng)前讀。

?

在默認(rèn)的RR隔離級別下，事務(wù)A中依然可以讀取到事務(wù)B最新提交的內(nèi)容，因?yàn)樵诓樵冋Z句后面加上了 lock in share mode 共享鎖，此時(shí)是當(dāng)前讀操作。當(dāng)然，當(dāng)我們加排他鎖的時(shí)候，也是當(dāng)前讀操作。

（2）快照讀

簡單的select（不加鎖）就是快照讀，快照讀，讀取的是記錄數(shù)據(jù)的可見版本，有可能是歷史數(shù)據(jù)，不加鎖，是非阻塞讀。

• Read Committed：每次select，都生成一個(gè)快照讀。
• Repeatable Read：開啟事務(wù)后第一個(gè)select語句才是快照讀的地方。
• Serializable：快照讀會(huì)退化為當(dāng)前讀。

?即使事務(wù)B提交了數(shù)據(jù),事務(wù)A中也查詢不到。 原因就是因?yàn)槠胀ǖ膕elect是快照讀，而在當(dāng)前默認(rèn)的RR隔離級別下，開啟事務(wù)后第一個(gè)select語句才是快照讀的地方，后面執(zhí)行相同的select語句都是從快照中獲取數(shù)據(jù)，可能不是當(dāng)前的最新數(shù)據(jù)，這樣也就保證了可重復(fù)讀。

?（3）MVCC

全稱 Multi-Version Concurrency Control，多版本并發(fā)控制。指維護(hù)一個(gè)數(shù)據(jù)的多個(gè)版本，使得讀寫操作沒有沖突，快照讀為MySQL實(shí)現(xiàn)MVCC提供了一個(gè)非阻塞讀功能。MVCC的具體實(shí)現(xiàn)，還需要依賴于數(shù)據(jù)庫記錄中的三個(gè)隱式字段、undo log日志、readView。

1、隱藏字段

新建一張表，可以顯式的看到設(shè)定的字段，InnoDB還會(huì)自動(dòng)的給我們添加三個(gè)隱藏字段及其含義分別是：

上述的前兩個(gè)字段是肯定會(huì)添加的， 是否添加最后一個(gè)字段DB_ROW_ID，得看當(dāng)前表有沒有主鍵，如果有主鍵，則不會(huì)添加該隱藏字段。

?查看stu的表結(jié)構(gòu)信息, 通過如下指令:

ibd2sdi stu.ibd 

查看到的表結(jié)構(gòu)信息中，有一欄 columns，在其中我們會(huì)看到處理我們建表時(shí)指定的字段以外，還有額外的兩個(gè)字段 分別是：DB_TRX_ID 、 DB_ROLL_PTR ，因?yàn)樵摫碛兄麈I，所以沒有DB_ROW_ID隱藏字段。

2、undolog

回滾日志，在insert、update、delete的時(shí)候產(chǎn)生的便于數(shù)據(jù)回滾的日志。當(dāng)insert的時(shí)候，產(chǎn)生的undo log日志只在回滾時(shí)需要，在事務(wù)提交后，可被立即刪除。而update、delete的時(shí)候，產(chǎn)生的undo log日志不僅在回滾時(shí)需要，在快照讀時(shí)也需要，不會(huì)立即被刪除。

版本鏈

• DB_TRX_ID : 代表最近修改事務(wù)ID，記錄插入這條記錄或最后一次修改該記錄的事務(wù)ID，是自增的。
• DB_ROLL_PTR ： 由于這條數(shù)據(jù)是才插入的，沒有被更新過，所以該字段值為null。

然后，有四個(gè)并發(fā)事務(wù)同時(shí)在訪問這張表。

?當(dāng)事務(wù)2執(zhí)行第一條修改語句時(shí)，會(huì)記錄undo log日志，記錄數(shù)據(jù)變更之前的樣子; 然后更新記錄，并且記錄本次操作的事務(wù)ID，回滾指針，回滾指針用來指定如果發(fā)生回滾，回滾到哪一個(gè)版本。

?

?當(dāng)事務(wù)3執(zhí)行第一條修改語句時(shí)，也會(huì)記錄undo log日志，記錄數(shù)據(jù)變更之前的樣子; 然后更新記錄，并且記錄本次操作的事務(wù)ID，回滾指針，回滾指針用來指定如果發(fā)生回滾，回滾到哪一個(gè)版本。

?

?當(dāng)事務(wù)4執(zhí)行第一條修改語句時(shí)，也會(huì)記錄undo log日志，記錄數(shù)據(jù)變更之前的樣子; 然后更新記錄，并且記錄本次操作的事務(wù)ID，回滾指針，回滾指針用來指定如果發(fā)生回滾，回滾到哪一個(gè)版本。

?最終我們發(fā)現(xiàn)，不同事務(wù)或相同事務(wù)對同一條記錄進(jìn)行修改，會(huì)導(dǎo)致該記錄的undolog生成一條記錄版本鏈表，鏈表的頭部是最新的舊記錄，鏈表尾部是最早的舊記錄。

3、readview?

ReadView（讀視圖）是 快照讀 SQL執(zhí)行時(shí)MVCC提取數(shù)據(jù)的依據(jù)，記錄并維護(hù)系統(tǒng)當(dāng)前活躍的事務(wù)（未提交的）id。

ReadView中包含了四個(gè)核心字段：

而在readview中就規(guī)定了版本鏈數(shù)據(jù)的訪問規(guī)則：

trx_id 代表當(dāng)前undolog版本鏈對應(yīng)事務(wù)ID。

?不同的隔離級別，生成ReadView的時(shí)機(jī)不同：

• READ COMMITTED ：在事務(wù)中每一次執(zhí)行快照讀時(shí)生成ReadView。
• REPEATABLE READ：僅在事務(wù)中第一次執(zhí)行快照讀時(shí)生成ReadView，后續(xù)復(fù)用該ReadView。

4、RC隔離級別

RC隔離級別下，在事務(wù)中每一次執(zhí)行快照讀時(shí)生成ReadView。

在事務(wù)5中，查詢了兩次id為30的記錄，由于隔離級別為Read Committed，所以每一次進(jìn)行快照讀都會(huì)生成一個(gè)ReadView，那么兩次生成的ReadView如下。

?那么這兩次快照讀在獲取數(shù)據(jù)時(shí)，就需要根據(jù)所生成的ReadView以及ReadView的版本鏈訪問規(guī)則，到undolog版本鏈中匹配數(shù)據(jù)，最終決定此次快照讀返回的數(shù)據(jù)。

A. 先來看第一次快照讀具體的讀取過程：

?

?在進(jìn)行匹配時(shí)，會(huì)從undo log的版本鏈，從上到下進(jìn)行挨個(gè)匹配：

• 先匹配這條記錄，這條記錄對應(yīng)的trx_id為4，也就是將4帶入右側(cè)的匹配規(guī)則中。 ①不滿足 ②不滿足 ③不滿足 ④也不滿足 ，都不滿足，則繼續(xù)匹配undo log版本鏈的下一條。
• 再匹配第二條，這條記錄對應(yīng)的trx_id為3，也就是將3帶入右側(cè)的匹配規(guī)則中。①不滿足 ②不滿足 ③不滿足 ④也不滿足 ，都不滿足，則繼續(xù)匹配undo log版本鏈的下一條。
• 再匹配第三條，這條記錄對應(yīng)的trx_id為2，也就是將2帶入右側(cè)的匹配規(guī)則中。①不滿足 ②滿足 終止匹配，此次快照讀，返回的數(shù)據(jù)就是版本鏈中記錄的這條數(shù)據(jù)。

?B. 再來看第二次快照讀具體的讀取過程:

?

?

?在進(jìn)行匹配時(shí)，會(huì)從undo log的版本鏈，從上到下進(jìn)行挨個(gè)匹配：

• ?先匹配這條記錄，這條記錄對應(yīng)的trx_id為4，也就是將4帶入右側(cè)的匹配規(guī)則中。 ①不滿足 ②不滿足 ③不滿足 ④也不滿足 ，都不滿足，則繼續(xù)匹配undo log版本鏈的下一條。
• ?再匹配第二條，這條記錄對應(yīng)的trx_id為3，也就是將3帶入右側(cè)的匹配規(guī)則中。①不滿足 ②滿足 。終止匹配，此次快照讀，返回的數(shù)據(jù)就是版本鏈中記錄的這條數(shù)據(jù)。

5、RR隔離級別

?RR隔離級別下，僅在事務(wù)中第一次執(zhí)行快照讀時(shí)生成ReadView，后續(xù)復(fù)用該ReadView。 而RR 是可重復(fù)讀，在一個(gè)事務(wù)中，執(zhí)行兩次相同的select語句，查詢到的結(jié)果是一樣的。

?我們看到，在RR隔離級別下，只是在事務(wù)中第一次快照讀時(shí)生成ReadView，后續(xù)都是復(fù)用該ReadView，那么既然ReadView都一樣， ReadView的版本鏈匹配規(guī)則也一樣， 那么最終快照讀返回的結(jié)果也是一樣的。

所以呢，MVCC的實(shí)現(xiàn)原理就是通過 InnoDB表的隱藏字段、UndoLog 版本鏈、ReadView來實(shí)現(xiàn)的。而MVCC + 鎖，則實(shí)現(xiàn)了事務(wù)的隔離性。 而一致性則是由redolog 與 undolog保證。

?6、MySQL管理

系統(tǒng)數(shù)據(jù)庫：Mysql數(shù)據(jù)庫安裝完成后，自帶了一下四個(gè)數(shù)據(jù)庫，具體作用如下：

?1、mysql

該mysql不是指mysql服務(wù)，而是指mysql的客戶端工具。

?-e選項(xiàng)可以在Mysql客戶端執(zhí)行SQL語句，而不用連接到MySQL數(shù)據(jù)庫再執(zhí)行，對于一些批處理腳本，這種方式尤其方便。

mysql -uroot –p123456 db01 -e "select * from stu"; 

2、mysqladmin

mysqladmin 是一個(gè)執(zhí)行管理操作的客戶端程序?？梢杂盟鼇頇z查服務(wù)器的配置和當(dāng)前狀態(tài)、創(chuàng)建并刪除數(shù)據(jù)庫等。

通過幫助文檔查看選項(xiàng)：mysqladmin --help

mysqladmin -uroot –p1234 drop 'test01';mysqladmin -uroot –p1234 version;

3、mysqlbinlog

由于服務(wù)器生成的二進(jìn)制日志文件以二進(jìn)制格式保存，所以如果想要檢查這些文本的文本格式，就會(huì)使用到mysqlbinlog 日志管理工具。

查看 binlog.000008這個(gè)二進(jìn)制文件中的數(shù)據(jù)信息mysqlbinlog binlog.000008-s簡化格式
mysqlbinlog -s binlog.000008

4、mysqlshow

mysqlshow 客戶端對象查找工具，用來很快地查找存在哪些數(shù)據(jù)庫、數(shù)據(jù)庫中的表、表中的列或者索引。

?查詢每個(gè)數(shù)據(jù)庫的表的數(shù)量及表中記錄的數(shù)量

?查看數(shù)據(jù)庫my的統(tǒng)計(jì)信息

?查看數(shù)據(jù)庫my中的tb_user表的信息

?查看數(shù)據(jù)庫my中的tb_user表的id字段的信息

?5、mysqldump

mysqldump 客戶端工具用來備份數(shù)據(jù)庫或在不同數(shù)據(jù)庫之間進(jìn)行數(shù)據(jù)遷移。備份內(nèi)容包含創(chuàng)建表，及插入表的SQL語句。

?A.備份my數(shù)據(jù)庫

mysqldump -uroot -p1234 my > my.sql

備份出來的數(shù)據(jù)包含：

• 刪除表的語句
• 創(chuàng)建表的語句
• 數(shù)據(jù)插入語句

如果我們在數(shù)據(jù)備份時(shí)，不需要?jiǎng)?chuàng)建表，或者不需要備份數(shù)據(jù)，只需要備份表結(jié)構(gòu)，都可以通過對應(yīng)的參數(shù)來實(shí)現(xiàn)。

B.備份db01數(shù)據(jù)庫中的表數(shù)據(jù)，不備份表結(jié)構(gòu)(-t)

mysqldump -uroot -p1234 -t my > my.sql

查看備份的數(shù)據(jù)，只有insert語句，沒有備份表結(jié)構(gòu)。

C. 將db01數(shù)據(jù)庫的表的表結(jié)構(gòu)與數(shù)據(jù)分開備份(-T)

查看MySQL信任的目錄

mysqldump -uroot -p1234 -T C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ my tb_user

需要存儲(chǔ)在MySQL信任的目錄下，否則會(huì)出錯(cuò)，數(shù)據(jù)不能完成備份

此操作會(huì)產(chǎn)生兩個(gè)文件：sql、txt，sql 中記錄的就是表結(jié)構(gòu)文件，而 txt 就是表數(shù)據(jù)文件，但是需要注意表數(shù)據(jù)文件，并不是記錄一條條的insert語句，而是按照一定的格式記錄表結(jié)構(gòu)中的數(shù)據(jù)。

6、mysqlimport/source

（1）mysqlimport

mysqlimport 是客戶端數(shù)據(jù)導(dǎo)入工具，用來導(dǎo)入mysqldump 加 -T 參數(shù)后導(dǎo)出的文本文件。

?（2）source

如果需要導(dǎo)入sql文件,可以使用mysql中的source 指令 :