mysql幻影怎么讀 別再誤解mysql的幻讀了

正確理解MYSQL的幻讀

一、定義

創新互聯公司網站建設公司，提供做網站、成都做網站，網頁設計，建網站，PHP網站建設等專業做網站服務;可快速的進行網站開發網頁制作和功能擴展；專業做搜索引擎喜愛的網站，是專業的做網站團隊，希望更多企業前來合作!

1、幻讀MYSQL官方叫法是Phantom Rows，意為鬼影行或者幻影行，請看官方定義：

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a [ SELECT ] is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

翻譯一下：

所謂的幻影行問題是指，在同一個事務中，同樣的查詢語句執行多次，得到了不同的行結果集。

例如，如果同一個SELECT語句執行了兩次，第二次執行的時候比第一次執行時多出一行，則該行就是所謂的幻影行。

2、幻讀與不可重復讀的區別

從官方的定義來看，幻讀的定義側重于多條記錄，就是記錄條數的變化，而不可重復讀側重于單條記錄數據的變化，這樣區分原因在于解決幻讀需要范圍鎖，解決不可重復讀只需要單條記錄加鎖

二、InnoDB的REPEATABLE READ級別

InnoDB支持由SQL1992標準描述的所有四個事務隔離級別，默認隔離級別是 REPEATABLE READ。

1、快照讀：

在RR模式下，第一次讀取會建立快照，后續查詢會讀取快照。

這意味著，如果在同一事務中發出多個普通[ SELECT ]（非鎖定）語句，則這些 [ SELECT ]語句的結果也是一致的。

2、[locking reads](鎖定讀取，又叫當前讀）

[ SELECT ]語句中使用 FOR UPDATE 或 FOR SHARE

3、行鎖

在RR模式下，使用當前讀以及 [ UPDATE ]和 [ DELETE ]語句會對數據記錄加行鎖，鎖定范圍取決于該語句使用的是具有唯一搜索條件的唯一索引還是范圍類型搜索條件。

三、InnoDB的READ COMMITTED級別

1、在RC模式下，每次讀取都會刷新快照，因此不能保證可重復讀

2、在RC模式下，使用當前讀以及 [ UPDATE ]和 [ DELETE ]語句會對數據記錄加行鎖，但是不會加范圍鎖，間隙鎖定僅用于外鍵約束檢查和重復鍵檢查。

3、由于禁用了間隙鎖定，因此可能會產生幻影行問題，因為其他會話可以在間隙中插入新行。

4、 對于[ UPDATE ]或 [ DELETE ]語句， InnoDB 僅對其更新或刪除的行持有鎖。MySQL評估 WHERE 條件后，將釋放不匹配行的記錄鎖 。這大大降低了死鎖的可能性，但是仍然可以發生。

5、對于[ UPDATE ]語句，如果某行已被鎖定，則 InnoDB 執行“半一致”讀取，將最新提交版本的數據返回給MySQL，以便MySQL可以確定該行是否符合 WHERE 條件。如果該行匹配（必須更新），則MySQL會再次讀取該行，這一次 InnoDB 會將其鎖定或等待獲取鎖。

6、注意

從MySQL 8.0.22開始，DML操作（增刪改，通過聯接列表或子查詢）從MySQL授權表中讀取數據，但不對其進行修改，無論隔離級別如何，都不會在MySQL授權表上獲得讀取鎖。

有關更多信息，請參見 Grant Table Concurrency 。

四、樂觀鎖與悲觀鎖

1、樂觀鎖

在UPDATE的WHERE子句中加入版本信息來確定修改是否生效

使用樂觀鎖時仍然需要非常謹慎，因為RR是可重復讀的，在UPDATE之前讀取版本號，應該使用[當前讀]，不能使用[快照讀]

2、悲觀鎖

在UPDATE執行前，SELECT后面加上FOR UPDATE來給記錄加鎖，保證記錄在UPDATE前不被修改。SELECT ... FOR UPDATE是加上了X鎖，也可以通過SELECT ... LOCK IN SHARE MODE加上S鎖，來防止其他事務對該行的修改。

3、無論是樂觀鎖還是悲觀鎖，使用的思想都是一致的，那就是當前讀。樂觀鎖利用當前讀判斷是否是最新版本，悲觀鎖利用當前讀鎖定行。

五、總結

1、RC級別沒有范圍鎖一定會導致不可重復讀和幻影行

2、RR級別安全性更高，實現可重復讀的方式為快照，如果需要最新數據可以選擇[當前讀]，因此RR級別是首選

3、不論RR還是RC級別，增、刪、改的操作都會進行一次[當前讀]操作，以此獲取最新版本的數據，并檢測是否有重復的索引。

4、RR級別下，當前事務如果未發生更新操作(增刪改)，快照版本會保持不變，多次查詢讀取的快照是同一個

5、RR級別下，當前事務如果發生更新(增刪改)，會刷新快照，會導致不可重復讀和幻影行

6、RR級別下，使用當前讀，會刷新快照，會導致不可重復讀和幻影行

7、RR級別下，可以通過提交當前事務并在此之后發出新查詢來為查詢獲取更新的快照。

8、RR級別可以部分解決不可重復讀和幻讀問題

9、其實問題的關鍵是你的業務邏輯需要可重復讀還是最新數據

丟失數據,污讀,不可重讀怎么區分

主要是一致性問題.常見并發并發一致性問題包括：丟失的修改、不可重復讀、讀臟數據、幻影讀（幻影讀在一些資料中往往與不可重復讀歸為一類）.答案補充 不可重復讀是指事務T1讀取數據后,事務T2執行更新操作,使T1無法再現前一次讀取結果.具體地講,不可重復讀包括三種情況：

事務T1讀取某一數據后,事務T2對其做了修改,當事務1再次讀該數據時,得到與前一次不同的值.例如,T1讀取B=100進行運算,T2讀取同一數據B,對其進行修改后將B=200寫回數據庫.T1為了對讀取值校對重讀B,B已為200,與第一次讀取值不一致.

事務T1按一定條件從數據庫中讀取了某些數據記錄后,事務T2刪除了其中部分記錄,當T1再次按相同條件讀取數據時,發現某些記錄神密地消失了.

事務T1按一定條件從數據庫中讀取某些數據記錄后,事務T2插入了一些記錄,當T1再次按相同條件讀取數據時,發現多了一些記錄.（這也叫做幻影讀） 答案補充 讀"臟"數據是指事務T1修改某一數據,并將其寫回磁盤,事務T2讀取同一數據后,T1由于某種原因被撤消,這時T1已修改過的數據恢復原值,T2讀到的數據就與數據庫中的數據不一致,則T2讀到的數據就為"臟"數據,即不正確的數據.

產生上述三類數據不一致性的主要原因是并發操作破壞了事務的隔離性.并發控制就是要用正確的方式調度并發操作,使一個用戶事務的執行不受其它事務的干擾,從而避免造成數據的不一致性.答案補充 兩個事務T1和T2讀入同一數據并修改,T2提交的結果破壞了T1提交的結果,導致T1的修改被丟失,即丟失的修改.

幻怎么讀

幻，讀音huàn，空虛的，不真實的：幻想?；糜?。幻境?；脺纾ㄊ艿浆F實的打擊而消滅）。亦真亦幻。虛幻。夢幻。變化：幻化（奇異的變化）。變幻莫測。筆畫數：4；部首：幺；

組詞：夢幻，指人生。多種顏色細膩勾勒出的夢幻，比美更令人沉醉于其中。出自 《莊子·齊物論》：“方其夢也，不知其夢也。夢之中又占其夢焉，覺而后知其夢也。且有大覺而后知其大夢也，而愚者自以為覺，竊竊然知之。

「春招系列」MySQL面試核心25問（附答案）

篇幅所限本文只寫了MySQL25題，像其他的Redis，SSM框架，算法，計網等技術棧的面試題后面會持續更新，個人整理的1000余道面試八股文會放在文末給大家白嫖，最近有面試需要刷題的同學可以直接翻到文末領取。

如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當前索引節點的后續位置，當一頁寫滿，就會自動開辟一個新的頁。如果使用非自增主鍵（如果身份證號或學號等），由于每次插入主鍵的值近似于隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置， 頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，后續不得不通過OPTIMIZE TABLE（optimize table）來重建表并優化填充頁面。

Server層按順序執行sql的步驟為：

簡單概括：

可以分為服務層和存儲引擎層兩部分，其中：

服務層包括連接器、查詢緩存、分析器、優化器、執行器等 ，涵蓋MySQL的大多數核心服務功能，以及所有的內置函數（如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖等。

存儲引擎層負責數據的存儲和提取 。其架構模式是插件式的，支持InnoDB、MyISAM、Memory等多個存儲引擎?，F在最常用的存儲引擎是InnoDB，它從MySQL 5.5.5版本開始成為了默認的存儲引擎。

Drop、Delete、Truncate都表示刪除，但是三者有一些差別：

Delete 用來刪除表的全部或者一部分數據行，執行Delete之后，用戶需要提交(commmit)或者回滾(rollback)來執行刪除或者撤銷刪除，會觸發這個表上所有的delete觸發器。

Truncate 刪除表中的所有數據，這個操作不能回滾，也不會觸發這個表上的觸發器，TRUNCATE比Delete更快，占用的空間更小。

Drop 命令從數據庫中刪除表，所有的數據行，索引和權限也會被刪除，所有的DML觸發器也不會被觸發，這個命令也不能回滾。

因此，在不再需要一張表的時候，用Drop；在想刪除部分數據行時候，用Delete；在保留表而刪除所有數據的時候用Truncate。

隔離級別臟讀不可重復讀幻影讀 READ-UNCOMMITTED 未提交讀 READ-COMMITTED 提交讀 REPEATABLE-READ 重復讀 SERIALIZABLE 可串行化讀

MySQL InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ （可重讀）

這里需要注意的是 ：與 SQL 標準不同的地方在于InnoDB 存儲引擎在 REPEATABLE-READ（可重讀）事務隔離級別 下使用的是 Next-Key Lock 鎖 算法，因此可以避免幻讀的產生，這與其他數據庫系統(如 SQL Server)是不同的。所以 說InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ（可重讀） 已經可以完全保證事務的隔離性要 求，即達到了 SQL標準的SERIALIZABLE(可串行化)隔離級別。

因為隔離級別越低，事務請求的鎖越少，所以大部分數據庫系統的隔離級別都是READ-COMMITTED(讀取提交內 容):，但是你要知道的是InnoDB 存儲引擎默認使用 REPEATABLE-READ（可重讀）并不會有任何性能損失 。

InnoDB 存儲引擎在分布式事務 的情況下一般會用到SERIALIZABLE(可串行化)隔離級別。

主要原因：B+樹只要遍歷葉子節點就可以實現整棵樹的遍歷，而且在數據庫中基于范圍的查詢是非常頻繁的，而B樹只能中序遍歷所有節點，效率太低。

文件與數據庫都是需要較大的存儲，也就是說，它們都不可能全部存儲在內存中，故需要存儲到磁盤上。而所謂索引，則為了數據的快速定位與查找，那么索引的結構組織要盡量減少查找過程中磁盤I/O的存取次數，因此B+樹相比B樹更為合適。數據庫系統巧妙利用了局部性原理與磁盤預讀原理，將一個節點的大小設為等于一個頁，這樣每個節點只需要一次I/O就可以完全載入，而紅黑樹這種結構，高度明顯要深的多，并且由于邏輯上很近的節點(父子)物理上可能很遠，無法利用局部性。

最重要的是，B+樹還有一個最大的好處：方便掃庫。

B樹必須用中序遍歷的方法按序掃庫，而B+樹直接從葉子結點挨個掃一遍就完了，B+樹支持range-query非常方便，而B樹不支持，這是數據庫選用B+樹的最主要原因。

B+樹查找效率更加穩定，B樹有可能在中間節點找到數據，穩定性不夠。

B+tree的磁盤讀寫代價更低：B+tree的內部結點并沒有指向關鍵字具體信息的指針(紅色部分)，因此其內部結點相對B 樹更小。如果把所有同一內部結點的關鍵字存放在同一塊盤中，那么盤塊所能容納的關鍵字數量也越多。一次性讀入內存中的需要查找的關鍵字也就越多，相對來說IO讀寫次數也就降低了；

B+tree的查詢效率更加穩定：由于內部結點并不是最終指向文件內容的結點，而只是葉子結點中關鍵字的索引，所以，任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當；

視圖是一種虛擬的表，通常是有一個表或者多個表的行或列的子集，具有和物理表相同的功能 游標是對查詢出來的結果集作為一個單元來有效的處理。一般不使用游標，但是需要逐條處理數據的時候，游標顯得十分重要。

而在 MySQL 中，恢復機制是通過回滾日志（undo log）實現的，所有事務進行的修改都會先記錄到這個回滾日志中，然后在對數據庫中的對應行進行寫入。當事務已經被提交之后，就無法再次回滾了。

回滾日志作用：1)能夠在發生錯誤或者用戶執行 ROLLBACK 時提供回滾相關的信息 2) 在整個系統發生崩潰、數據庫進程直接被殺死后，當用戶再次啟動數據庫進程時，還能夠立刻通過查詢回滾日志將之前未完成的事務進行回滾，這也就需要回滾日志必須先于數據持久化到磁盤上，是我們需要先寫日志后寫數據庫的主要原因。

InnoDB

MyISAM

總結

數據庫并發會帶來臟讀、幻讀、丟棄更改、不可重復讀這四個常見問題，其中：

臟讀 ：在第一個修改事務和讀取事務進行的時候，讀取事務讀到的數據為100，這是修改之后的數據，但是之后該事務滿足一致性等特性而做了回滾操作，那么讀取事務得到的結果就是臟數據了。

幻讀 ：一般是T1在某個范圍內進行修改操作（增加或者刪除），而T2讀取該范圍導致讀到的數據是修改之間的了，強調范圍。

丟棄修改 ：兩個寫事務T1 T2同時對A=0進行遞增操作，結果T2覆蓋T1，導致最終結果是1 而不是2，事務被覆蓋

不可重復讀 ：T2 讀取一個數據，然后T1 對該數據做了修改。如果 T2 再次讀取這個數據，此時讀取的結果和第一次讀取的結果不同。

第一個事務首先讀取var變量為50，接著準備更新為100的時，并未提交，第二個事務已經讀取var為100，此時第一個事務做了回滾。最終第二個事務讀取的var和數據庫的var不一樣。

T1 讀取某個范圍的數據，T2 在這個范圍內插入新的數據，T1 再次讀取這個范圍的數據，此時讀取的結果和和第一次讀取的結果不同。

T1 和 T2 兩個事務都對一個數據進行修改，T1 先修改，T2 隨后修改，T2 的修改覆蓋了 T1 的修改。例如：事務1讀取某表中的數據A=50，事務2也讀取A=50，事務1修改A=A+50，事務2也修改A=A+50，最終結果A=100，事務1的修改被丟失。

T2 讀取一個數據，T1 對該數據做了修改。如果 T2 再次讀取這個數據，此時讀取的結果和第一次讀取的結果不同。

悲觀鎖，先獲取鎖，再進行業務操作，一般就是利用類似 SELECT … FOR UPDATE 這樣的語句，對數據加鎖，避免其他事務意外修改數據。當數據庫執行SELECT … FOR UPDATE時會獲取被select中的數據行的行鎖，select for update獲取的行鎖會在當前事務結束時自動釋放，因此必須在事務中使用。

樂觀鎖，先進行業務操作，只在最后實際更新數據時進行檢查數據是否被更新過。Java 并發包中的 AtomicFieldUpdater 類似，也是利用 CAS 機制，并不會對數據加鎖，而是通過對比數據的時間戳或者版本號，來實現樂觀鎖需要的版本判斷。

分庫與分表的目的在于，減小數據庫的單庫單表負擔，提高查詢性能，縮短查詢時間。

通過分表 ，可以減少數據庫的單表負擔，將壓力分散到不同的表上，同時因為不同的表上的數據量少了，起到提高查詢性能，縮短查詢時間的作用，此外，可以很大的緩解表鎖的問題。分表策略可以歸納為垂直拆分和水平拆分:

水平分表 ：取模分表就屬于隨機分表，而時間維度分表則屬于連續分表。如何設計好垂直拆分，我的建議：將不常用的字段單獨拆分到另外一張擴展表. 將大文本的字段單獨拆分到另外一張擴展表, 將不經常修改的字段放在同一張表中，將經常改變的字段放在另一張表中。對于海量用戶場景，可以考慮取模分表，數據相對比較均勻，不容易出現熱點和并發訪問的瓶頸。

庫內分表 ，僅僅是解決了單表數據過大的問題，但并沒有把單表的數據分散到不同的物理機上，因此并不能減輕 MySQL 服務器的壓力，仍然存在同一個物理機上的資源競爭和瓶頸，包括 CPU、內存、磁盤 IO、網絡帶寬等。

分庫與分表帶來的分布式困境與應對之策 數據遷移與擴容問題----一般做法是通過程序先讀出數據，然后按照指定的分表策略再將數據寫入到各個分表中。分頁與排序問題----需要在不同的分表中將數據進行排序并返回，并將不同分表返回的結果集進行匯總和再次排序，最后再返回給用戶。

不可重復讀的重點是修改，幻讀的重點在于新增或者刪除。

視圖是虛擬的表，與包含數據的表不一樣，視圖只包含使用時動態檢索數據的查詢；不包含任何列或數據。使用視圖可以簡化復雜的 sql 操作，隱藏具體的細節，保護數據；視圖創建后，可以使用與表相同的方式利用它們。

視圖不能被索引，也不能有關聯的觸發器或默認值，如果視圖本身內有order by 則對視圖再次order by將被覆蓋。

創建視圖：create view xxx as xxxx

對于某些視圖比如未使用聯結子查詢分組聚集函數Distinct Union等，是可以對其更新的，對視圖的更新將對基表進行更新；但是視圖主要用于簡化檢索，保護數據，并不用于更新，而且大部分視圖都不可以更新。

B+tree的磁盤讀寫代價更低，B+tree的查詢效率更加穩定 數據庫索引采用B+樹而不是B樹的主要原因：B+樹只要遍歷葉子節點就可以實現整棵樹的遍歷，而且在數據庫中基于范圍的查詢是非常頻繁的，而B樹只能中序遍歷所有節點，效率太低。

B+樹的特點

在最頻繁使用的、用以縮小查詢范圍的字段,需要排序的字段上建立索引。不宜：1）對于查詢中很少涉及的列或者重復值比較多的列 2）對于一些特殊的數據類型，不宜建立索引，比如文本字段（text）等。

如果一個索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱 之為“覆蓋索引”。

我們知道在InnoDB存儲引 擎中，如果不是主鍵索引，葉子節點存儲的是主鍵+列值。最終還是要“回表”，也就是要通過主鍵再查找一次,這樣就 會比較慢。覆蓋索引就是把要查詢出的列和索引是對應的，不做回表操作！

舉例 ：

學號姓名性別年齡系別專業 20020612李輝男20計算機軟件開發 20060613張明男18計算機軟件開發 20060614王小玉女19物理力學 20060615李淑華女17生物動物學 20060616趙靜男21化學食品化學 20060617趙靜女20生物植物學

主鍵為候選鍵的子集，候選鍵為超鍵的子集，而外鍵的確定是相對于主鍵的。

“可重復讀”和“幻影讀”是什么意思？

REPEATABLE READ

鎖定查詢中使用的所有數據以防止其他用戶更新數據，但是其他用戶可以將新的幻像行插入數據集，且幻像行包括在當前事務的后續讀取中。因為并發低于默認隔離級別，所以應只在必要時才使用該選項。

也就是說你在同一事務中在不同時間讀取同一條記錄時,每次讀的應該是一樣的

不會被別的語句修改,但其他語句還可以insert

SERIALIZABLE

在數據集上放置一個范圍鎖，以防止其他用戶在事務完成之前更新數據集或將行插入數據集內。這是四個隔離級別中限制最大的級別。因為并發級別較低，所以應只在必要時才使用該選項。該選項的作用與在事務內所有 SELECT 語句中的所有表上設置 HOLDLOCK 相同。

也就是說在你所定的范圍內,別人什么都干不了,包括insert

分享標題：mysql幻影怎么讀 別再誤解mysql的幻讀了
轉載來于：http://m.kartarina.com/article30/hgpcpo.html

成都網站建設公司_創新互聯，為您提供網站策劃、搜索引擎優化、云服務器、微信公眾號、定制開發、靜態網站

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯