MySQL深入(一)
MySQL深入(一)
文章目錄
- MySQL深入(一)
- MySQL的事務(wù)隔離
- 事務(wù)隔離強度
- 臟讀
- 臟寫
- 不可重復(fù)讀
- 幻讀
- 解決臟讀,臟寫,不可重復(fù)讀和幻讀現(xiàn)象
- 變量
- mysql存儲過程
- 存儲過程中的三種傳遞參數(shù)
- 創(chuàng)建存儲過程
- 調(diào)用存儲過程
- 刪除存儲過程
- 函數(shù)
- 創(chuàng)建函數(shù)
- 調(diào)用函數(shù)
- 刪除函數(shù)
- MySQL的一些關(guān)鍵文件
- MySQL邏輯框架
- 分層
- InnoDB存儲引擎(默認(rèn)引擎)
- InnoDB主要特性
- InnoDB體系
- InnoDB的后臺線程
- Innodb內(nèi)存
- LRU List、Free List 和 Flush List
- undo log和redo log
- bin log
- bin log
MySQL的事務(wù)隔離
MySQL有四類隔離級別:
事務(wù)隔離強度
隔離強度從上到下逐漸增強,如圖:
- Read Uncommitted :在該隔離級別,所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。也就是說基本沒有任何事務(wù)隔離性,其他事務(wù)的沒進行事務(wù)提交的修改和回滾都有可能影響其他事務(wù)的執(zhí)行結(jié)果。
- Read Committed :在該隔離級別,一個事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變。也就是說該級別下,一個事務(wù)對某數(shù)據(jù)的多次讀取操作仍然可以出現(xiàn)前后讀取不一致的情況。假設(shè)事務(wù)A對數(shù)據(jù)Data進行進行三次查詢,而有事務(wù)B,C分別前后對數(shù)據(jù)進行了修改并提交了,那么事務(wù)A可能會讀到三個不同的數(shù)據(jù)。
- Repeatable Read :MySQL的默認(rèn)事務(wù)隔離級別,在該隔離級別,同一事務(wù)的多個實例在并發(fā)讀取數(shù)據(jù)時,會看到同樣的數(shù)據(jù),但其他事務(wù)的插入數(shù)據(jù)是可以看見的。也就是說,事務(wù)A在事務(wù)中讀取的數(shù)據(jù)不會因為其他事務(wù)的提交了的修改,而導(dǎo)致出現(xiàn)讀取結(jié)果被修改的現(xiàn)象。但是,假設(shè)其他事務(wù)有插入操作,并且插入的數(shù)據(jù)剛好符合事務(wù)A中查詢條件的范圍,那么事務(wù)A可能會讀取到其他事務(wù)插入的數(shù)據(jù),導(dǎo)致讀取數(shù)據(jù)的數(shù)量前后不一致,也就是所謂的幻讀。
- Serializable :這是最高的隔離級別,它通過強制事務(wù)排序,使之不可能相互沖突,從而解決幻讀問題。通過共享鎖實現(xiàn)。也就是說,假設(shè)事務(wù)A要讀取某三行數(shù)據(jù),那么這三行就被事務(wù)A占有,并且只占有這三行數(shù)據(jù),也就是說事務(wù)A整個過程中看見的就這三行數(shù)據(jù),其他事務(wù)無法對這三行數(shù)據(jù)進行任何操作。但這也導(dǎo)致了性能的問題,
臟讀
發(fā)生條件: 在事務(wù)級別Read Uncommitted下,多事務(wù)讀取修改情況下。
導(dǎo)致臟讀的主要原因是所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果,假設(shè)有事務(wù)A,并且事務(wù)A在事務(wù)中有多次讀取的情況,現(xiàn)有事務(wù)B對事務(wù)A讀取的數(shù)據(jù)進行修改并且沒有進行提交,而且可能失敗并發(fā)生回滾現(xiàn)象。那么事務(wù)A可能讀取到的數(shù)據(jù)可能是B沒有提交的修改后的數(shù)據(jù),也可能是B事務(wù)失敗后回滾的數(shù)據(jù)。
臟寫
發(fā)生條件: 在事務(wù)級別Read Uncommitted下,多事務(wù)讀取修改情況下。
和臟讀類似,主要原因是所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。
假設(shè)有兩事務(wù)A,B對數(shù)據(jù)Data進行修改,在事務(wù)A之前有事務(wù)B對數(shù)據(jù)Data修改,然后事務(wù)A在進行修改并提交了,但是事務(wù)B因為某些原因?qū)е率聞?wù)失敗并進行回滾,那么事務(wù)B回滾到的數(shù)據(jù)是其操作之前的狀態(tài),那么事務(wù)A的修改就被事務(wù)B回滾的數(shù)據(jù)覆蓋了。結(jié)果就導(dǎo)致了事務(wù)A提交的修改無效了。
不可重復(fù)讀
發(fā)生條件: 在事務(wù)級別Read Uncommitted,Read Committed下,多事務(wù)讀取修改情況下。
可以說是臟讀的縮小版,在Read Committed事務(wù)級別下,一個事務(wù)只能已經(jīng)提交事務(wù)所做的改變,但這無法完全避免事務(wù)讀取數(shù)據(jù)的前后一致。
假設(shè)事務(wù)A對數(shù)據(jù)Data進行進行三次查詢,而有事務(wù)B,C分別前后對數(shù)據(jù)進行了修改并提交了,那么事務(wù)A可能會讀到三個不同的數(shù)據(jù)。這就是不可重復(fù)讀。雖然B,C事務(wù)都提交了,但是這個提交結(jié)果可能會導(dǎo)致其他事務(wù)的讀取結(jié)果。
幻讀
發(fā)生條件: 在事務(wù)級別Read Uncommitted,Read Committed,Repeatable Read下,多事務(wù)情況讀取插入刪除情況下。
幻讀一般是因為,事務(wù)的插入和刪除導(dǎo)致的,幻讀就如其命一樣,在一個多次讀取的事務(wù)中,前后讀取到的數(shù)據(jù)個數(shù)不一樣,這就是幻讀,如同幻覺一般,數(shù)據(jù)會突然出現(xiàn)和消失。
假設(shè)事務(wù)A有多次讀取操作,并且有事務(wù)B,C分別進行插入刪除操作,在A第一次讀取后,事務(wù)B對表進行插入操作,插入的數(shù)據(jù)剛好符合A的查詢條件,那么A再次讀取數(shù)據(jù),就會發(fā)現(xiàn)讀取的數(shù)據(jù)中多出了幾行數(shù)據(jù),如同出現(xiàn)幻覺一般。在A再次讀取后面,C把多條數(shù)據(jù)進行了刪除,并且刪除的數(shù)據(jù)剛好符合A中查詢的條件,那么A再次讀取時,A就無法讀取完整的數(shù)據(jù)了。
解決臟讀,臟寫,不可重復(fù)讀和幻讀現(xiàn)象
開啟事務(wù)級別Serializable,serializable下會完全鎖定字段,若一個事務(wù)來查詢同一份數(shù)據(jù)就必須等待,直到前一個事務(wù)完成并解除鎖定為止。是完整的隔離級別。但這也導(dǎo)致了一些并發(fā)性能的問題,多個事務(wù)對同一行數(shù)據(jù)查詢操作,為了安全,那么就只能逐個事務(wù)排隊對數(shù)據(jù)進行操作,效率上大打折扣。
變量
系統(tǒng)變量:全局變量,會話變量
自定義變量:用戶變量,局部變量
#參看當(dāng)前會話系統(tǒng)變量 show session variables; #參考全局系統(tǒng)變量 show global variables; show global variables like '%';#查看指定系統(tǒng)變量命 select @@global.變量命#設(shè)置全局系統(tǒng)變量 set global 變量名 = 值; set @@global.變量名=值;#設(shè)置用戶變量 set @用戶變量=值;#申明局部變量 declare 變量命 類型; #設(shè)置局部變量 SET 局部變量命=值;mysql存儲過程
存儲過程是為了完成特定功能的SQL語句集,經(jīng)編譯創(chuàng)建并保存在數(shù)據(jù)庫中,用戶可通過指定存儲過程的名字并給定參數(shù)(需要時)來調(diào)用執(zhí)行。
mysql的存儲過程的寫法跟一般語言的函數(shù)寫法很相似,主要還是聲明,方法體和返回值組成。
存儲過程中的三種傳遞參數(shù)
傳遞參數(shù)主要分三大種:IN,OUT,INOUT。分別是輸入?yún)?shù),輸出參數(shù)和輸入輸出參數(shù)。
在創(chuàng)建存儲過程中IN和INOUT可以作為存儲過程的傳入值使用,而OUT是作為輸出值使用,類似于函數(shù)中的return。
創(chuàng)建存儲過程
#創(chuàng)建名為sp_name的存儲過程 CREATE PROCEDURE sp_name(IN p_in int) BEGIN#編寫SQL語句 END;#創(chuàng)建一個有權(quán)限的存儲過程 CREATE DEFINER = {username} PROCEDURE sp_name(IN p_in int) BEGIN#編寫SQL語句 END;#創(chuàng)建一個包含查詢語句的存儲過程 #delimiter用來聲明語句的結(jié)束符號從分號;臨時改為$,主要為了區(qū)分存儲體中的SQL結(jié)束和存儲過程的結(jié)束符 delimiter $ CREATE PROCEDURE check_user(IN user_id int,IN password int,OUT result int) BEGIN#這里是通過OUT變量來傳遞放回值SELECT count(*) INTO resultFROM USERTableWHERE id=user_id AND Password=password; END$調(diào)用存儲過程
存儲過程的調(diào)用使用call關(guān)鍵字,并且在通過傳遞的參數(shù)來獲得放回值。
#存儲過程的調(diào)用使用call關(guān)鍵字 #通過用戶變量@userchecke_result獲取放回值 set @userchecke_result=0 call check_user(5,123456,@userchecke_result)刪除存儲過程
使用drop刪除
DROP PROCEDURE sp_name;函數(shù)
MySQL 有很多內(nèi)置的函數(shù),但是我們也可以定義屬于自己的函數(shù)。
創(chuàng)建函數(shù)
CREATE FUNCTION fun_name(id int,pwd int) RETURN INT BEGINDECLARE result INT DEFAULT 0;SELECT count(*) INTO resultFROM userTableWHERE use_id=id AND password=pwd;RETURN result; END $調(diào)用函數(shù)
函數(shù)的調(diào)用直接使用即可
SELECT fun_name(5,123456);刪除函數(shù)
DROP FUNCUTION sp_name;MySQL的一些關(guān)鍵文件
logbin文件:二進制日志文件
logerror文件:錯誤日志文件
my.ini文件:MySQL配置文件
MySQL邏輯框架
分層
MySQL邏輯框架分4層:
- 連接層:負(fù)責(zé)客戶端和連接服務(wù),該層上引入了線程池地 概念,為通過認(rèn)證通過安全接入的客戶端提供線程。
- 服務(wù)層:主要完成大部分的核心服務(wù)功能,如SQL接口,并完成緩存查詢,SQL的分析和優(yōu)化及部分內(nèi)置函數(shù)的執(zhí)行。
- 存儲引擎層:存儲引擎真正的負(fù)責(zé)了MySQL中數(shù)據(jù)的存儲和提取,服務(wù)器通過api和存儲引擎進行通信。不同的存儲引擎擁有不同的存儲功能。
- 數(shù)據(jù)存儲層:主要將數(shù)據(jù)存儲與運行在裸設(shè)備的文件存儲系統(tǒng)之上,并完成與存儲引擎的交互。
查看MySQL引擎
#查看所有引擎 show engine; #查看使用引擎 SHOW VARIABLES LIKE 'storage_engine';InnoDB存儲引擎(默認(rèn)引擎)
InnoDB是事務(wù)型數(shù)據(jù)庫,支持事務(wù)安全表(ACID),支持行鎖定和外鍵。支持 行鎖設(shè)計、MVCC、外鍵、提供一致性非鎖定讀 。
InnoDB主要特性
- 為MySQL提供了具有提交、回滾和崩潰恢復(fù)能力的事物安全(ACID兼容)存儲引擎。
- InnoDB存儲引擎為在主內(nèi)存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池。
- InnoDB支持外鍵完整性約束
InnoDB體系
內(nèi)存池 :負(fù)責(zé)維護緩存磁盤上的數(shù)據(jù),重做日志緩沖,維護內(nèi)部數(shù)據(jù)等。
后臺線程:負(fù)責(zé)刷新內(nèi)存池的數(shù)據(jù),保證緩存的是最新的數(shù)據(jù);將內(nèi)存數(shù)據(jù)刷新到磁盤中等。
InnoDB的后臺線程
InnoDB是一個單進程多線程的模型,后臺不同的線程負(fù)責(zé)不同的任務(wù)。
Master Thread :負(fù)責(zé)將緩沖池中的數(shù)據(jù)異步刷新到硬盤中,保證數(shù)據(jù)一致性,包括臟頁的刷新、合并插入、UNDO頁的回收等。
IO Thread :InnoDB引擎中大量使用異步IO來處理IO請求。可以通過系統(tǒng)變量查看io線程的個數(shù):
SHOW VARIABLES LIKE 'innodb_%io_threads';Purge Thread:事務(wù)被提交后,undolog不再需要,需要Purge Thread回收。
Page Cleaner Thread:將臟頁刷新操作放入單獨的線程中完成。
Innodb內(nèi)存
InnoDB 存儲引擎是基于磁盤存儲的,也就是說數(shù)據(jù)都是存儲在磁盤上的,由于 CPU 速度和磁盤速度之間的鴻溝, InnoDB 引擎使用緩沖池技術(shù)來提高數(shù)據(jù)庫的整體性能。
緩沖池中緩存的數(shù)據(jù)頁類型有: 索引頁、數(shù)據(jù)頁、 undo 頁、插入緩沖、自適應(yīng)哈希索引、 InnoDB 的鎖信息、數(shù)據(jù)字典信息等 。在InnoDB中,緩沖池中的頁大小默認(rèn)為16KB。
LRU List、Free List 和 Flush List
- Free List(空閑鏈表):用于保存內(nèi)存中空閑的內(nèi)存頁。
- LRU List:InnoDB通過LRU(Latest Recent Used,最近最少使用)算法來管理緩沖池。而LRU List就是用來保存最近使用過的數(shù)據(jù)位置的鏈表。當(dāng)緩沖池放不下新讀取的頁時,就會釋放LRU列表尾端的頁。
- Flush List:在LRU List 中的頁被修改后,稱該頁為 臟頁(dirty page) 。臟頁存儲于 Flush List,表示緩沖池中的頁與磁盤頁不一致,等待被調(diào)度刷新。
undo log和redo log
- redo log :redo log是InnoDB存儲引擎層的日志, **用于記錄事務(wù)操作的變化,記錄的是數(shù)據(jù)修改之后的值。**InnoDB 將重做日志首先寫入 redo buffer cache,之后通過一定頻率寫入到重做日志(redo logo)中。
- undo log :保存了事務(wù)發(fā)生之前的數(shù)據(jù)的一個版本,可以用于回滾,同時可以提供多版本并發(fā)控制下的讀(MVCC)。
bin log
用于復(fù)制,在主從復(fù)制中,從庫利用主庫上的binlog進行重播,實現(xiàn)主從同步。
log和redo log
- redo log :redo log是InnoDB存儲引擎層的日志, **用于記錄事務(wù)操作的變化,記錄的是數(shù)據(jù)修改之后的值。**InnoDB 將重做日志首先寫入 redo buffer cache,之后通過一定頻率寫入到重做日志(redo logo)中。
- undo log :保存了事務(wù)發(fā)生之前的數(shù)據(jù)的一個版本,可以用于回滾,同時可以提供多版本并發(fā)控制下的讀(MVCC)。
bin log
用于復(fù)制,在主從復(fù)制中,從庫利用主庫上的binlog進行重播,實現(xiàn)主從同步。
總結(jié)
以上是生活随笔為你收集整理的MySQL深入(一)的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 设计模式(四)--代理模式
- 下一篇: 模拟洗牌小游戏