#問題68021 MySQL唯一檢查問題-知乎(zhihu.com)
這是事情的開始。最近我和阿里云接觸比較密切,成為他們的大客戶(當然我們是大客戶BIG BIG BIG potato),也就是說我和他們的核心研發有密切的接觸;d同學。這段文字是他們的高層人物寫的關于MYSQL的唯一索引的文字,感謝他推給我。
實際上,在DBA行業中,關于唯一索引的爭論一直不斷,但DBA對開發本身并不信服。我們只能說,如果你這樣做,你的表現會受到影響。當然,這和牛屎沒什么區別。
我們截取陳老師文中關于偽代碼的部分。
在二級索引中找到要插入值的B樹頁面
斷言B樹頁面被鎖存
equal-range=二級索引中與您的值沖突的記錄范圍
如果(等于范圍不為空){
釋放B樹上的閂鎖,并開始新的微型事務
對于相等范圍內的每條記錄
鎖定它之前的間隙和記錄本身(這就是LOCK_S所做的)
最后一次后也鎖定間隙(等范圍)
另外(在錯誤#32617942被修復之前),在最后一個(相等范圍)之后鎖定記錄
一旦你完成了以上所有操作,再次找到B樹頁面,并再次鎖定它
}
將記錄插入頁面,并釋放B樹頁面上的閂鎖。
讓我們開門見山,唯一的索引被插入。
1索引是有序的。
唯一索引是唯一的。
雖然我們用的隔離級別是RC,但是同性戀鎖不會出現在MYSQL或者POLARDB里面,但是,但是,但是
你是唯一的索引。
我們需要確認的是
1當插入唯一索引時,它將確定當前索引中是否存在相同的值。
2插入時,避免同時插入其他相同的值。
3插入時也要判斷順序,插入時也要考慮周圍值的變化。
基于這些考慮,鎖是必要的。
我們來看看這段偽代碼。
在二級索引中找到要插入值的B樹頁面
斷言B樹頁面被鎖存
第一步是找到要插入數據的索引頁,并鎖定頁鎖。
equal-range=二級索引中與您的值沖突的記錄范圍
如果(等于范圍不為空){
釋放B樹上的閂鎖,并開始新的微型事務
對于相等范圍內的每條記錄
在它之前鎖定缺口,記錄本身
f (this is what LOCK_S does)also lock the gap after the last(equal-range)
also (before Bug #32617942 was fixed) lock the record after last(equal-range)
once you are done with all of the above, find the B-tree page again and latch it again
}
第二部是一個具有原子性的操作,
1 獲取與你插入值有沖突的范圍,(尤其對那些多個鍵值是唯一索引的情況)
2 如果你插入的鍵值的沖突范圍并不為空
3 將撤銷原有的栓鎖,將鎖的粒度變小
4 開始針對可能產生沖突的鍵值進行check (期間產生間隙鎖)
2 如果沒有沖突,數據插入到頁面
在整個的操作過程中,是需要對插入的整體過程加GAP 鎖,保證在判斷后你插入數據前,不會有其他的數據插入到這個位置。
之所以是說后續,這里考慮的問題是多鍵值的唯一索引與單鍵值的索引,為什么要考慮這個問題,主要還是目前使用的 next-key lock 在位置索引中可能產生的范圍的大小問題。
舉例:
在表設計時,我們可以對多列產生唯一索引,而根據上文中的描述,唯一的鍵值要插入到索引的情況下,是需要針對一段范圍的數據進行鎖定的,那么你的值越多,產生的范圍會越大,導致出現死鎖的概率就越大。
上文中從源代碼的方面進行了闡述,如何對數據庫的源代碼進行改造,但使用者怎么辦,如何去最大化的規避問題。
我們需要確認幾點
1 數據插入索引是有序的
2 數據插入到唯一索引時盡量少進行上面范圍的GAP LOCK
這里就基于范圍的問題進行討論,如何能在程序設計的基礎上最大化的減小產生死鎖的幾率和范圍。
如果有多個鍵值要進行唯一索引的處理時,需要通過HASH 算法(根據算法盡量避免由于HASH 算法導致的雖然值不同,但HASH 后值相同的可能性),通過將多個字段通過算法轉換為 一個HASH 值,并且對這個HASH值的字段建立唯一索引。
這樣的好處顯而易見
1 降低上文中提到在MYSQL 或POLARDB 中唯一索引出現死鎖的可能性
2 降低一個表上出現較大唯一索引的情況(一個表中包含索引的整體字節數是有限制的)
3 對應用友好和靈活,例如我今天是3個字段,明天可能是四個字段,那么實際上,產生這樣的情況下,表的索引不必改動,改動的是應用程序中關于插入HASH 字段的算法中包含的字段的數量即可。否則你又會建立一個新的唯一索引。
其實數據庫本身作為三大系統之一的系統,包容性應該是很高的,但基于數據庫的對于數據的嚴謹性和邏輯性的問題,要求很高,導致使用數據庫的過程中,需要對數據庫本身的原理進行理解,盡量避免一些數據庫本身無法在短時間滿足的 性能 --- 功能 之間的矛盾,所以在設計應用程序時也應該有的放矢的進行有效設計。