當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL深入（一）

發(fā)布時間：2024/9/19 数据库 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL深入（一）小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

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有四類隔離級別：

Read Uncommitted（讀取未提交內(nèi)容）

Read Committed（讀取提交內(nèi)容）

Repeatable Read（可重讀）

Serializable（可串行化）

事務(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層：

連接層

服務(wù)層

存儲引擎層

存儲層

連接層：負(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)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

MySQL