當前位置:首頁 > 公眾號精選 > 小林coding
[導(dǎo)讀]大家好,我是小林。昨晚在群劃水的時候,看到有位讀者說了這么一件事。大概就是,在線上執(zhí)行一條update語句修改數(shù)據(jù)庫數(shù)據(jù)的時候,where條件沒有帶上索引,導(dǎo)致業(yè)務(wù)直接崩了,被老板教訓(xùn)了一波這次我們就來看看:為什么會發(fā)生這種的事故?又該如何避免這種事故的發(fā)生?說個前提,接下來說的...

大家好,我是小林。

昨晚在群劃水的時候,看到有位讀者說了這么一件事。

大概就是,在線上執(zhí)行一條 update 語句修改數(shù)據(jù)庫數(shù)據(jù)的時候,where 條件沒有帶上索引,導(dǎo)致業(yè)務(wù)直接崩了,被老板教訓(xùn)了一波這次我們就來看看:
  • 為什么會發(fā)生這種的事故?

  • 又該如何避免這種事故的發(fā)生?

說個前提,接下來說的案例都是基于 InnoDB 存儲引擎,且事務(wù)的隔離級別是可重復(fù)讀。1?為什么會發(fā)生這種的事故?InnoDB 存儲引擎的默認事務(wù)隔離級別是「可重復(fù)讀」,但是在這個隔離級別下,在多個事務(wù)并發(fā)的時候,會出現(xiàn)幻讀的問題,所謂的幻讀是指在同一事務(wù)下,連續(xù)執(zhí)行兩次同樣的查詢語句,第二次的查詢語句可能會返回之前不存在的行。因此 InnoDB 存儲引擎自己實現(xiàn)了行鎖,通過 next-key 鎖(記錄鎖和間隙鎖的組合)來鎖住記錄本身和記錄之間的“間隙”,防止其他事務(wù)在這個記錄之間插入新的記錄,從而避免了幻讀現(xiàn)象。當我們執(zhí)行 update 語句時,實際上是會對記錄加獨占鎖(X 鎖)的,如果其他事務(wù)對持有獨占鎖的記錄進行修改時是會被阻塞的。另外,這個鎖并不是執(zhí)行完 update 語句就會釋放的,而是會等事務(wù)結(jié)束時才會釋放。在 InnoDB 事務(wù)中,對記錄加鎖帶基本單位是 next-key 鎖,但是會因為一些條件會退化成間隙鎖,或者記錄鎖。加鎖的位置準確的說,鎖是加在索引上的而非行上。比如,在 update 語句的 where 條件使用了唯一索引,那么 next-key 鎖會退化成記錄鎖,也就是只會給一行記錄加鎖。這里舉個例子,這里有一張數(shù)據(jù)庫表,其中 id 為主鍵索引。
假設(shè)有兩個事務(wù)的執(zhí)行順序如下:可以看到,事務(wù) A 的 update 語句中 where 是等值查詢,并且 id 是唯一索引,所以只會對 id = 1 這條記錄加鎖,因此,事務(wù) B 的更新操作并不會阻塞。
但是,在 update 語句的 where 條件沒有使用索引,就會全表掃描,于是就會對所有記錄加上 next-key 鎖(記錄鎖 間隙鎖),相當于把整個表鎖住了。假設(shè)有兩個事務(wù)的執(zhí)行順序如下:
可以看到,這次事務(wù) B 的 update 語句被阻塞了。這是因為事務(wù) A的 update 語句中 where 條件沒有索引列,所有記錄都會被加鎖,也就是這條 update 語句產(chǎn)生了 4 個記錄鎖和 5 個間隙鎖,相當于鎖住了全表。
因此,當在數(shù)據(jù)量非常大的數(shù)據(jù)庫表執(zhí)行 update 語句時,如果沒有使用索引,就會給全表的加上 next-key 鎖, 那么鎖就會持續(xù)很長一段時間,直到事務(wù)結(jié)束。而這期間除了?select ... from語句,其他語句都會被鎖住不能執(zhí)行,業(yè)務(wù)會因此停滯,接下來等著你的,就是老板的挨罵。那 update 語句的 where 帶上索引就能避免全表記錄加鎖了嗎?并不是。關(guān)鍵還得看這條語句在執(zhí)行過程中,優(yōu)化器最終選擇的是索引掃描,還是全表掃描,如果走了全表掃描,就會對全表的記錄加鎖了。2?又該如何避免這種事故的發(fā)生?我們可以將 MySQL 里的?sql_safe_updates?參數(shù)設(shè)置為 1,開啟安全更新模式。
官方的解釋:
If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.
大致的意思是,當 sql_safe_updates 設(shè)置為 1 時。update 語句必須滿足如下條件之一才能執(zhí)行成功:
  • 使用 where,并且 where 條件中必須有索引列;

  • 使用 limit;

  • 同時使用 where 和 limit,此時 where 條件中可以沒有索引列;

delete 語句必須滿足如下條件之一才能執(zhí)行成功:
  • 使用 where,并且 where 條件中必須有索引列;

  • 同時使用 where 和 limit,此時 where 條件中可以沒有索引列;

如果 where 條件帶上了索引列,但是優(yōu)化器最終掃描選擇的是全表,而不是索引的話,我們可以使用?force index([index_name])?可以告訴優(yōu)化器使用哪個索引,以此避免有幾率鎖全表帶來的隱患。3?總結(jié)不要小看一條 update 語句,在生產(chǎn)機上使用不當可能會導(dǎo)致業(yè)務(wù)停滯,甚至崩潰。當我們要執(zhí)行 update 語句的時候,確保 where 條件中帶上了索引列,并且在測試機確認該語句是否走的是索引掃描,防止因為掃描全表,而對表中的所有記錄加上鎖。我們可以打開 MySQL 里的 sql_safe_updates 參數(shù),這樣可以預(yù)防 update 操作時 where 條件沒有帶上索引列。如果發(fā)現(xiàn)即使在 where 條件中帶上了列索引列,優(yōu)化器走的還是全標掃描,這時我們就要使用?force index([index_name])?可以告訴優(yōu)化器使用哪個索引。這次就說到這啦,下次要小心點,別再被老板挨罵啦。

本站聲明: 本文章由作者或相關(guān)機構(gòu)授權(quán)發(fā)布,目的在于傳遞更多信息,并不代表本站贊同其觀點,本站亦不保證或承諾內(nèi)容真實性等。需要轉(zhuǎn)載請聯(lián)系該專欄作者,如若文章內(nèi)容侵犯您的權(quán)益,請及時聯(lián)系本站刪除。
換一批
延伸閱讀

9月2日消息,不造車的華為或?qū)⒋呱龈蟮莫毥谦F公司,隨著阿維塔和賽力斯的入局,華為引望愈發(fā)顯得引人矚目。

關(guān)鍵字: 阿維塔 塞力斯 華為

加利福尼亞州圣克拉拉縣2024年8月30日 /美通社/ -- 數(shù)字化轉(zhuǎn)型技術(shù)解決方案公司Trianz今天宣布,該公司與Amazon Web Services (AWS)簽訂了...

關(guān)鍵字: AWS AN BSP 數(shù)字化

倫敦2024年8月29日 /美通社/ -- 英國汽車技術(shù)公司SODA.Auto推出其旗艦產(chǎn)品SODA V,這是全球首款涵蓋汽車工程師從創(chuàng)意到認證的所有需求的工具,可用于創(chuàng)建軟件定義汽車。 SODA V工具的開發(fā)耗時1.5...

關(guān)鍵字: 汽車 人工智能 智能驅(qū)動 BSP

北京2024年8月28日 /美通社/ -- 越來越多用戶希望企業(yè)業(yè)務(wù)能7×24不間斷運行,同時企業(yè)卻面臨越來越多業(yè)務(wù)中斷的風險,如企業(yè)系統(tǒng)復(fù)雜性的增加,頻繁的功能更新和發(fā)布等。如何確保業(yè)務(wù)連續(xù)性,提升韌性,成...

關(guān)鍵字: 亞馬遜 解密 控制平面 BSP

8月30日消息,據(jù)媒體報道,騰訊和網(wǎng)易近期正在縮減他們對日本游戲市場的投資。

關(guān)鍵字: 騰訊 編碼器 CPU

8月28日消息,今天上午,2024中國國際大數(shù)據(jù)產(chǎn)業(yè)博覽會開幕式在貴陽舉行,華為董事、質(zhì)量流程IT總裁陶景文發(fā)表了演講。

關(guān)鍵字: 華為 12nm EDA 半導(dǎo)體

8月28日消息,在2024中國國際大數(shù)據(jù)產(chǎn)業(yè)博覽會上,華為常務(wù)董事、華為云CEO張平安發(fā)表演講稱,數(shù)字世界的話語權(quán)最終是由生態(tài)的繁榮決定的。

關(guān)鍵字: 華為 12nm 手機 衛(wèi)星通信

要點: 有效應(yīng)對環(huán)境變化,經(jīng)營業(yè)績穩(wěn)中有升 落實提質(zhì)增效舉措,毛利潤率延續(xù)升勢 戰(zhàn)略布局成效顯著,戰(zhàn)新業(yè)務(wù)引領(lǐng)增長 以科技創(chuàng)新為引領(lǐng),提升企業(yè)核心競爭力 堅持高質(zhì)量發(fā)展策略,塑強核心競爭優(yōu)勢...

關(guān)鍵字: 通信 BSP 電信運營商 數(shù)字經(jīng)濟

北京2024年8月27日 /美通社/ -- 8月21日,由中央廣播電視總臺與中國電影電視技術(shù)學(xué)會聯(lián)合牽頭組建的NVI技術(shù)創(chuàng)新聯(lián)盟在BIRTV2024超高清全產(chǎn)業(yè)鏈發(fā)展研討會上宣布正式成立。 活動現(xiàn)場 NVI技術(shù)創(chuàng)新聯(lián)...

關(guān)鍵字: VI 傳輸協(xié)議 音頻 BSP

北京2024年8月27日 /美通社/ -- 在8月23日舉辦的2024年長三角生態(tài)綠色一體化發(fā)展示范區(qū)聯(lián)合招商會上,軟通動力信息技術(shù)(集團)股份有限公司(以下簡稱"軟通動力")與長三角投資(上海)有限...

關(guān)鍵字: BSP 信息技術(shù)
關(guān)閉
關(guān)閉