MySQL InnerDB

事務(wù)隔離級別

所謂隔離級別，就是在數(shù)據(jù)庫事務(wù)中，為保證并發(fā)數(shù)據(jù)讀寫的正確性而提出的定義。每種關(guān)系型數(shù)據(jù)庫都提供了各自特色的隔離級別實現(xiàn)，雖然在通常的定義中是以鎖為實現(xiàn)單元，但實際的實現(xiàn)千差萬別。
以最常見的 MySQL InnoDB 引擎為例，它是基于MVCC(Multi-Version Concurrency Control)和鎖的復(fù)合實現(xiàn)的，按照隔離級別由低到高，MYSQL事務(wù)隔離級別分為四個不同層次:

  • 讀未提交(Read Uncommitted)，事務(wù)A能夠看到事務(wù)B未提交的修改，允許出現(xiàn)臟讀
  • 讀已提交(Read Committed), 事務(wù)A只可以看到事務(wù)B已經(jīng)提交的修改，允許出現(xiàn)不可重復(fù)讀，即不能保證同一個事務(wù)中多次讀取數(shù)據(jù)的結(jié)果一致。
  • 可重復(fù)讀(Repeatable Read), 保證同一個事務(wù)中多次讀取的數(shù)據(jù)是一致的。這是MYSQL默認的隔離級別。但是和其他數(shù)據(jù)庫實現(xiàn)不同的是，MYSQL通過對一個范圍加Gap鎖，可以在一定程度上避免幻讀情況。
  • 串行化(Serializable) ，事務(wù)之間是串行執(zhí)行的，通常意味著讀需要獲取共享讀鎖，更新需要獲取排他寫鎖。
    MYSQL InnerDB會根據(jù)不同的事務(wù)隔離級別，對數(shù)據(jù)修改增加一些"隱式"的鎖，同時也支持通過特定語句進行顯示的鎖定，比如:
    SELECT ... LOCK IN SHARE MODE
    SELECT ... FOR UPDATE

悲觀鎖和樂觀鎖

悲觀鎖和樂觀鎖不是MYSQL中獨有的概念，而是并發(fā)編程的基本概念。主要區(qū)別在于，操作共享數(shù)據(jù)時，“悲觀鎖”認為數(shù)據(jù)出現(xiàn)沖突的可能性更大，而“樂觀鎖”則認為大部分情況不會出現(xiàn)沖突，進而決定是否采用排他性的措施。JAVA中常用的synchronized關(guān)鍵字，以及并發(fā)包中的讀寫鎖，都屬于悲觀鎖，而CAS操作可以認為是樂觀鎖。
樂觀鎖的實現(xiàn)方式一般有一下兩種:

  • 使用數(shù)據(jù)版本（Version）實現(xiàn)，這是樂觀鎖最常用的一種實現(xiàn)方式。何謂數(shù)據(jù)版本？即為數(shù)據(jù)增加一個版本標識，一般是通過為數(shù)據(jù)庫表增加一個數(shù)字類型的 “version” 字段來實現(xiàn)。當(dāng)讀取數(shù)據(jù)時，將version字段的值一同讀出，數(shù)據(jù)每更新一次，對此version值+1。當(dāng)我們提交更新的時候，判斷數(shù)據(jù)庫表對應(yīng)記錄的當(dāng)前版本信息與第一次取出來的version值進行比對，如果數(shù)據(jù)庫表當(dāng)前版本號與第一次取出來的version值相等，則予以更新，否則認為是過期數(shù)據(jù)。
  • 使用時間戳（timestamp)，同樣是在需要樂觀鎖控制的table中增加一個字段，名稱無所謂，字段類型使用時間戳（timestamp）, 和上面的version類似，也是在更新提交的時候檢查當(dāng)前數(shù)據(jù)庫中數(shù)據(jù)的時間戳和自己更新前取到的時間戳進行對比，如果一致則OK，否則就是版本沖突.

當(dāng)前讀和快照讀

Innodb的操作可以分為當(dāng)前讀(current read)和快照讀(snapshot read):

  1. 快照讀: 簡單的SELECT 語句
  2. 當(dāng)前讀: SELECT IN SHARD MODE, SELECT FOR UPDATE, INSERT, UPDATE, DELETE
    在RR事務(wù)隔離級別下，快照讀是通過MVCC和undo log來實現(xiàn)的，當(dāng)前讀是通過加record lock(記錄鎖)和gap lock(間隙鎖)來實現(xiàn)的。
    所以我們通常所說的MYSQL 在一定程度上可以避免出現(xiàn)"幻讀" 指的是當(dāng)前讀，如果在事務(wù)中使用了快照讀，則不能保證出現(xiàn)“幻讀”

MVCC實現(xiàn)

InnerDB MVCC是通過在每行記錄后面保存兩個隱藏的列來實現(xiàn)的。這兩個列，一個保存了行的創(chuàng)建時間，一個保存行的過期時間（或刪除時間）。當(dāng)然存儲的并不是實際的時間值，而是系統(tǒng)版本號（system version number)。每開始一個新的事務(wù)，系統(tǒng)版本號都會自動遞增。事務(wù)開始時刻的系統(tǒng)版本號會作為事務(wù)的版本號，用來和查詢到的每行記錄的版本號進行比較

  • SELECT: 會根據(jù)以下兩個條件檢查每行記錄
    1. 只查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行（也就是，行的系統(tǒng)版本號小于或等于事務(wù)的系統(tǒng)版本號），這樣可以確保事務(wù)讀取的行，要么是在事務(wù)開始前已經(jīng)存在的，要么是事務(wù)自身插入或者修改過的.
    2. 行的刪除版本要么未定義，要么大于當(dāng)前事務(wù)版本號。這可以確保事務(wù)讀取到的行，在事務(wù)開始之前未被刪除。
      只有符合上述兩個條件的記錄，才能返回作為查詢結(jié)果
  • INSERT: 新插入的每一行保存當(dāng)前系統(tǒng)版本號作為行版本號。
  • DELETE: 為刪除的每一行保存當(dāng)前系統(tǒng)版本號作為行刪除標識。
  • UPDATE: 系統(tǒng)版本號 +1

redo,undo,binglog

https://www.linuxidc.com/Linux/2018-01/150614.htm
MySQL中有六種五種日志文件:

  • 重做日志（redo log）
  • 回滾日志（undo log）
  • 二進制日志（binlog）
  • 錯誤日志（errorlog）
  • 慢查詢?nèi)罩荆╯low query log）
  • 一般查詢?nèi)罩荆╣eneral log）
  • 中繼日志（relay log)
    其中重做日志和回滾日志與事務(wù)操作息息相關(guān)，二進制日志也與事務(wù)操作有一定的關(guān)系，這三種日志，對理解MySQL中的事務(wù)操作有著重要的意義
  1. 重做日志
  • 作用：確保事務(wù)的持久性。防止在發(fā)生故障的時間點，尚有臟頁未寫入磁盤，在重啟mysql服務(wù)的時候，根據(jù)redo log進行重做，從而達到事務(wù)的持久性這一特性。
  • 內(nèi)容：物理格式的日志，記錄的是物理數(shù)據(jù)頁面的修改的信息，其redo log是順序?qū)懭雛edo log file的物理文件中去的。
  • 什么時候產(chǎn)生：事務(wù)開始之后就產(chǎn)生redo log，redo log的落盤并不是隨著事務(wù)的提交才寫入的，而是在事務(wù)的執(zhí)行過程中，便開始寫入redo log文件中
  • 什么時候釋放：當(dāng)對應(yīng)事務(wù)的臟頁寫入到磁盤之后，redo log的使命也就完成了，重做日志占用的空間就可以重用（被覆蓋）
  • 對應(yīng)的物理文件：
    默認情況下，對應(yīng)的物理文件位于數(shù)據(jù)庫的data目錄下的ib_logfile1&ib_logfile2
    　　innodb_log_group_home_dir 指定日志文件組所在的路徑，默認./ ，表示在數(shù)據(jù)庫的數(shù)據(jù)目錄下。
    　　innodb_log_files_in_group 指定重做日志文件組中文件的數(shù)量，默認2
    　　關(guān)于文件的大小和數(shù)量，由一下兩個參數(shù)配置
    　　innodb_log_file_size 重做日志文件的大小。
    　　innodb_mirrored_log_groups 指定了日志鏡像文件組的數(shù)量，默認1
  • 其他：
    　　很重要一點，redo log是什么時候?qū)懕P的？前面說了是在事物開始之后逐步寫盤的。
    　　之所以說重做日志是在事務(wù)開始之后逐步寫入重做日志文件，而不一定是事務(wù)提交才寫入重做日志緩存，
    　　原因就是，重做日志有一個緩存區(qū)Innodb_log_buffer，Innodb_log_buffer的默認大小為8M(這里設(shè)置的16M),Innodb存儲引擎先將重做日志寫入innodb_log_buffer中。
    　然后會通過以下三種方式將innodb日志緩沖區(qū)的日志刷新到磁盤
    　　1，Master Thread 每秒一次執(zhí)行刷新Innodb_log_buffer到重做日志文件。
    　　2，每個事務(wù)提交時會將重做日志刷新到重做日志文件。
    　　3，當(dāng)重做日志緩存可用空間 少于一半時，重做日志緩存被刷新到重做日志文件
  1. 回滾日志
    作用：
    　　保存了事務(wù)發(fā)生之前的數(shù)據(jù)的一個版本，可以用于回滾，同時可以提供多版本并發(fā)控制下的讀（MVCC），也即非鎖定讀
    內(nèi)容：
    　　邏輯格式的日志，在執(zhí)行undo的時候，僅僅是將數(shù)據(jù)從邏輯上恢復(fù)至事務(wù)之前的狀態(tài)，而不是從物理頁面上操作實現(xiàn)的，這一點是不同于redo log的。
    什么時候產(chǎn)生：
    　　事務(wù)開始之前，將當(dāng)前是的版本生成undo log，undo 也會產(chǎn)生 redo 來保證undo log的可靠性
    什么時候釋放：
    　　當(dāng)事務(wù)提交之后，undo log并不能立馬被刪除，
    　　而是放入待清理的鏈表，由purge線程判斷是否由其他事務(wù)在使用undo段中表的上一個事務(wù)之前的版本信息，決定是否可以清理undo log的日志空間。
    對應(yīng)的物理文件：
    　　MySQL5.6之前，undo表空間位于共享表空間的回滾段中，共享表空間的默認的名稱是ibdata，位于數(shù)據(jù)文件目錄中。
    　　MySQL5.6之后，undo表空間可以配置成獨立的文件，但是提前需要在配置文件中配置，完成數(shù)據(jù)庫初始化后生效且不可改變undo log文件的個數(shù)
    　　如果初始化數(shù)據(jù)庫之前沒有進行相關(guān)配置，那么就無法配置成獨立的表空間了。
    　　關(guān)于MySQL5.7之后的獨立undo 表空間配置參數(shù)如下
    　　innodb_undo_directory = /data/undospace/ --undo獨立表空間的存放目錄
    　　innodb_undo_logs = 128 --回滾段為128KB
    　　innodb_undo_tablespaces = 4 --指定有4個undo log文件

如果undo使用的共享表空間，這個共享表空間中又不僅僅是存儲了undo的信息，共享表空間的默認為與MySQL的數(shù)據(jù)目錄下面，其屬性由參數(shù)innodb_data_file_path配置。
其他：
　　undo是在事務(wù)開始之前保存的被修改數(shù)據(jù)的一個版本，產(chǎn)生undo日志的時候，同樣會伴隨類似于保護事務(wù)持久化機制的redolog的產(chǎn)生。
　　默認情況下undo文件是保持在共享表空間的，也即ibdatafile文件中，當(dāng)數(shù)據(jù)庫中發(fā)生一些大的事務(wù)性操作的時候，要生成大量的undo信息，全部保存在共享表空間中的。
　　因此共享表空間可能會變的很大，默認情況下，也就是undo 日志使用共享表空間的時候，被“撐大”的共享表空間是不會也不能自動收縮的。
　　因此，mysql5.7之后的“獨立undo 表空間”的配置就顯得很有必要了。

  1. 二進制日志
    作用：
    　　1，用于復(fù)制，在主從復(fù)制中，從庫利用主庫上的binlog進行重播，實現(xiàn)主從同步。
    　　2，用于數(shù)據(jù)庫的基于時間點的還原。
    內(nèi)容：
    　　邏輯格式的日志，可以簡單認為就是執(zhí)行過的事務(wù)中的sql語句。
    　　但又不完全是sql語句這么簡單，而是執(zhí)行的sql語句（增刪改）反向的信息，
    　　也就意味著delete對應(yīng)著delete本身和其反向的insert；update對應(yīng)著update執(zhí)行前后的版本的信息；insert對應(yīng)著delete和insert本身的信息。
    　　在使用mysqlbinlog解析binlog之后一些都會真相大白。
    　　因此可以基于binlog做到類似于Oracle的閃回功能，其實都是依賴于binlog中的日志記錄。

什么時候產(chǎn)生：
　　事務(wù)提交的時候，一次性將事務(wù)中的sql語句（一個事物可能對應(yīng)多個sql語句）按照一定的格式記錄到binlog中。
　　這里與redo log很明顯的差異就是redo log并不一定是在事務(wù)提交的時候刷新到磁盤，redo log是在事務(wù)開始之后就開始逐步寫入磁盤。
　　因此對于事務(wù)的提交，即便是較大的事務(wù)，提交（commit）都是很快的，但是在開啟了bin_log的情況下，對于較大事務(wù)的提交，可能會變得比較慢一些。
　　這是因為binlog是在事務(wù)提交的時候一次性寫入的造成的，這些可以通過測試驗證。

什么時候釋放：
　　binlog的默認是保持時間由參數(shù)expire_logs_days配置，也就是說對于非活動的日志文件，在生成時間超過expire_logs_days配置的天數(shù)之后，會被自動刪除。

對應(yīng)的物理文件：
　　配置文件的路徑為log_bin_basename，binlog日志文件按照指定大小，當(dāng)日志文件達到指定的最大的大小之后，進行滾動更新，生成新的日志文件。
　　對于每個binlog日志文件，通過一個統(tǒng)一的index文件來組織。

其他：
　　二進制日志的作用之一是還原數(shù)據(jù)庫的，這與redo log很類似，很多人混淆過，但是兩者有本質(zhì)的不同
　　1，作用不同：redo log是保證事務(wù)的持久性的，是事務(wù)層面的，binlog作為還原的功能，是數(shù)據(jù)庫層面的（當(dāng)然也可以精確到事務(wù)層面的），雖然都有還原的意思，但是其保護數(shù)據(jù)的層次是不一樣的。
　　2，內(nèi)容不同：redo log是物理日志，是數(shù)據(jù)頁面的修改之后的物理記錄，binlog是邏輯日志，可以簡單認為記錄的就是sql語句
　　3，另外，兩者日志產(chǎn)生的時間，可以釋放的時間，在可釋放的情況下清理機制，都是完全不同的。

關(guān)于事務(wù)提交時，redo log和binlog的寫入順序，為了保證主從復(fù)制時候的主從一致（當(dāng)然也包括使用binlog進行基于時間點還原的情況），是要嚴格一致的，
　　MySQL通過兩階段提交過程來完成事務(wù)的一致性的，也即redo log和binlog的一致性的，理論上是先寫redo log，再寫binlog，兩個日志都提交成功（刷入磁盤），事務(wù)才算真正的完成。
http://blog.itpub.net/29254281/viewspace-1398273/
http://www.cnblogs.com/leefreeman/p/8286550.html

多用戶同時修改一條數(shù)據(jù) ，并發(fā)問題
https://my.oschina.net/u/2377060/blog/803217

多個用戶同時提交同一份表單，來修改同一條記錄的問題

數(shù)據(jù)庫分庫分表

訂單ID生成問題
MYSQL索引優(yōu)化: http://www.itdecent.cn/p/79c83531dfd1

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容