武城縣建設(shè)局網(wǎng)站近期時(shí)政熱點(diǎn)新聞20條
目錄
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),則該視圖不可更新:
- 聚合函數(shù)或窗口函數(shù)(SUM()、 MIN()、 MAX()、 COUNT()等)
- DISTINCT
- GROUP BY
- HAVING
- 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)粘5牟僮?#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 指令 :