一、mysql數(shù)據(jù)庫中的存儲引擎

mysql在創(chuàng)建數(shù)據(jù)表時(shí)可以通過engine關(guān)鍵字設(shè)置存儲引擎的類型，也可以通過alter命令來修改表的存儲引擎。可以通過show engines命令來查看當(dāng)前mysql數(shù)據(jù)庫支持的存儲引擎的類型，一般場景的存儲引擎有：InnoDB、MyISAM、MEMORY、BLACKHOLE、TokuDB和MySQL Cluster。

InnoDB存儲引擎是mysql5.5后的默認(rèn)存儲引擎，其特點(diǎn)主要有：1.索引組織表、2.支持事務(wù)、3.支持行級鎖、4.數(shù)據(jù)塊緩存、5.日志持久化。一般的線上項(xiàng)目推薦使用InnoDB存儲引擎，因?yàn)槠浞€(wěn)定可靠、性能好。

MyISAM存儲引擎時(shí)mysql5.1之前的默認(rèn)存儲引擎，其特點(diǎn)主要有：1.堆表、2.不支持事務(wù)、3.只維護(hù)索引緩存塊，表數(shù)據(jù)緩存交給操作系統(tǒng)、4.鎖粒度大，表級鎖，高并發(fā)下會有問題。5.數(shù)據(jù)文件可以直接拷貝。一般沒有特殊需求不建議使用。

MEMORY存儲引擎的特點(diǎn)有：1.數(shù)據(jù)全內(nèi)存存放，無法持久化、2.性能較高、3.不支持事務(wù)。適合偶爾作為臨時(shí)表的存儲引擎使用。臨時(shí)表創(chuàng)建是通過create temporary table來實(shí)現(xiàn)的，且臨時(shí)表不是全局可見的，只用當(dāng)前連接可見。

BLACKHOLE存儲引擎的特點(diǎn)是：1.數(shù)據(jù)不做任何存儲。一般利用mysql replicate充當(dāng)日志服務(wù)器，再mysql replicate環(huán)境中充當(dāng)代理主機(jī)。

TokuDB存儲引擎的特點(diǎn)有：1.分形樹存儲結(jié)構(gòu)、2.支持事務(wù)、3.行級鎖、4.數(shù)據(jù)壓縮效率高。一般使用于有大批量insert的業(yè)務(wù)場景，但是mysql官方版本中沒有這個(gè)存儲引擎，需要去其官網(wǎng)下載安裝擴(kuò)展。

MySQL Cluster存儲引擎的特點(diǎn)有：1.多主機(jī)分布式集群、2.數(shù)據(jù)節(jié)點(diǎn)冗余、高可用、3.支持事物、4.設(shè)計(jì)易于擴(kuò)展。這是一款面向未來發(fā)展的數(shù)據(jù)庫，當(dāng)前線上不建議使用。此外mysql官方版本中也沒有這個(gè)存儲引擎，需要去其官網(wǎng)下載安裝擴(kuò)展。

二、InnoDB事物鎖

鎖的作用是在并發(fā)的情況下保證數(shù)據(jù)的完整性和一致性。數(shù)據(jù)庫中的鎖有兩種，1.事務(wù)鎖lock，在事務(wù)的執(zhí)行過程中保護(hù)數(shù)據(jù)庫的邏輯內(nèi)容。2.線程鎖latch/mutex，多線程爭奪臨界資源時(shí)，保護(hù)內(nèi)存數(shù)據(jù)結(jié)構(gòu)。數(shù)據(jù)庫中事務(wù)間用的是第一種事務(wù)鎖來保護(hù)并發(fā)，將對同一行數(shù)據(jù)的修改串行化。

三、事務(wù)鎖的粒度

1.行鎖：InnoDB和Oracle中用的是行鎖，即修改記錄時(shí)只鎖定這一行記錄。

2.頁所：SQL Server中用的是頁鎖，修改記錄時(shí)鎖定的是該記錄所在的數(shù)據(jù)頁。

3.表鎖：MyISAM和MEMORY中用的是表鎖，修改記錄時(shí)鎖定記錄所在的表。

鎖升級：當(dāng)維護(hù)行鎖或頁鎖的代價(jià)過高時(shí)，數(shù)據(jù)庫會自動將鎖升級為表鎖，在InnoDB和Oracle中是不存在這種情況的，但是再SQL Server中有。

四、InnoDB中四種基本鎖模式

InnoDB中有兩種標(biāo)準(zhǔn)的行級鎖，分別是共享鎖(S Lock)和排它鎖(X Lock)。共享鎖就是讀鎖，允許事務(wù)讀一行數(shù)據(jù)。排它鎖就是寫鎖，允許事務(wù)刪除或更新一行數(shù)據(jù)。量中鎖的兼容性如下圖所示。

此外InnoDB中還有一種被稱為意向鎖的鎖，這種鎖是由引擎自動添加和釋放的，所以操作十分快，對用戶而言可以認(rèn)為是透明的。意向鎖存在的目的是為數(shù)據(jù)庫提供多粒度的上鎖，例如當(dāng)為一個(gè)行數(shù)據(jù)添加行鎖時(shí)，先對數(shù)據(jù)所在的表和頁添加表級和頁級的意向鎖，這樣如果在表級或者頁級就出現(xiàn)沖突，則不用在去檢測行數(shù)據(jù)的鎖兼容性了，可以減少沖突檢測的代價(jià)，提高上鎖的效率。一般行級鎖是不會與意向鎖沖突的。意向鎖與讀寫鎖的兼容性如下圖所示。

五、InnoDB的加鎖操作

一般的select語句不加任何的鎖，也不會被任何事務(wù)鎖阻塞，因?yàn)镮nnoDB中讀操作采用的是一致性非鎖定讀，就是實(shí)際讀取時(shí)不是讀取的當(dāng)前磁盤上的數(shù)據(jù)，而是這行數(shù)據(jù)的一個(gè)數(shù)據(jù)快照，可以認(rèn)為就是一個(gè)回滾段。而多個(gè)版本的數(shù)據(jù)快照間的隔離性是由多版本并發(fā)控制(MVCC)來實(shí)現(xiàn)的。不同的事務(wù)級別下多版本并發(fā)控制也會有所不同，例如再READ COMMITTED級別下，總是會讀取最新的一個(gè)數(shù)據(jù)快照，這樣就會導(dǎo)致不可重復(fù)讀。而在REPEATABLE READ級別下則是讀取事務(wù)開始時(shí)的數(shù)據(jù)版本，這樣就解決了不可重復(fù)讀的問題。

S鎖有兩種上鎖情況，一種是手動添加S鎖，可以通過select * from table lock in share mode來手動添加S鎖。另一種是自動添加，在執(zhí)行insert操作前會自動添加S鎖。

X鎖也有兩種上鎖情況，一種是手動添加，可以通過select * from table lock for update。另一種是自動添加，在執(zhí)行update和delete操作時(shí)會自動給這行數(shù)據(jù)添加X鎖。

六、鎖超時(shí)

當(dāng)事務(wù)發(fā)現(xiàn)鎖被其他事務(wù)獲取后就進(jìn)入等待，但是這個(gè)等待不是無休止的，InnoDB中有一個(gè)等待超時(shí)參數(shù)innodb_lock_wait_timeout，可以用show variables和set命令來查看和修改這個(gè)參數(shù)，該參數(shù)的單位是秒，一般默認(rèn)設(shè)置是50秒，如果事務(wù)等待的時(shí)間超過了這個(gè)等待的上線時(shí)間就會拋出操作是吧的error。此外還有一個(gè)參數(shù)innodb_rollback_on__timeout用來設(shè)定是否在等待超時(shí)時(shí)對進(jìn)行中的事務(wù)進(jìn)行回滾操作(該參數(shù)值默認(rèn)時(shí)OFF的，即關(guān)閉的)，同樣也可以用show variables和set命令來查看和修改這個(gè)參數(shù)。

七、InnoDB行鎖的實(shí)現(xiàn)

InnoDB中的行鎖是通過對索引項(xiàng)加鎖實(shí)現(xiàn)的，而不是對一行數(shù)據(jù)的數(shù)據(jù)塊進(jìn)行加鎖實(shí)現(xiàn)的(Oracle中是這樣實(shí)現(xiàn)的)。因此只有當(dāng)過濾條件走索引時(shí)才能實(shí)現(xiàn)行級鎖，如果索引上有多條數(shù)據(jù)那就有可能同時(shí)鎖住多條數(shù)據(jù)。而如果查詢有多個(gè)索引可以使用時(shí)，可以對不同的索引加鎖，這主要取決于mysql中的自動生成的執(zhí)行計(jì)劃。因此一般在做更新和刪除條件時(shí)用自增主鍵來做條件性能最好。

下面通過具體實(shí)例來進(jìn)行說明。

首先建立一張t2表并插入兩條記錄，sql語句如下。

create table t2(a int,b int,key idx1(a));

insert into t2 values(1,1);

insert into t2 values(1,5);

然后在連接A中開啟事務(wù)，并執(zhí)行如下命令。

mysql> select * from t2 where a=1 and b=5 for update;

接下來在連接B中開啟事務(wù)執(zhí)行如下命令。

select * from t2 where a=1 and b=1 for update;

執(zhí)行結(jié)果如下圖所示。

可以看出在執(zhí)行連接A中的事務(wù)時(shí)雖然查詢結(jié)果只有一行，但實(shí)際上是鎖了兩行記錄，這就是因?yàn)镮nnoDB存儲引擎中是使用鎖索引的方法來實(shí)現(xiàn)行鎖的。而上面的t2表中只有a列有索引，所以當(dāng)執(zhí)行連接A中的事務(wù)時(shí)存儲引擎實(shí)際上是對a列值為1的數(shù)據(jù)都進(jìn)行了鎖定，所以鎖了兩行數(shù)據(jù)。而如果表中沒有索引那么InnoDB就無法實(shí)現(xiàn)行鎖了，每次鎖定都將鎖定表中的所有數(shù)據(jù)，就和表鎖一樣了。因此一般在update和delete操作中where條件中推薦使用自增主鍵來作為篩選條件，這樣可以保證每次只鎖定一行數(shù)據(jù)。

八、InnoDB的gap lock

InnoDB的gap lock是InnoDB中一種特殊的鎖定算法，即鎖定的時(shí)候不是單單鎖定某個(gè)值下的索引記錄，而是一個(gè)范圍下的索引記錄，其作用就是消滅幻讀(什么是幻讀可以去看上一篇博客)。但是其代價(jià)就是會降低數(shù)據(jù)庫的并發(fā)性。下面來舉例說明。

首先建立一張表t3并插入一些數(shù)據(jù)，代碼如下。

create table t3(a int(11) default null,key idx1(a));

insert into t3 values(20),(23),(27),(27),(30),(31);

然后在連接A中開啟事務(wù)，并執(zhí)行如下命令。

select * from t3 where a=27 for update;

接下來在連接B中開啟事務(wù)執(zhí)行如下命令。

insert into t3 values(27);

結(jié)果如下圖所示。

可以看出在連接B中27無法插入t3表中。這樣就解決了幻讀問題。看似很完美，但是之際上InnoDB中鎖定的是(23,30)這樣的數(shù)值范圍，不知27無法插入了，連24、25、26、28、29都無法插入了。如果在B連接中執(zhí)行如下語句。

insert into t3 values(28);

insert into t3 values(25);結(jié)果如下圖。

因此在gap lock下數(shù)據(jù)庫的并發(fā)性是比較差的，因?yàn)槊看味紩i掉一個(gè)范圍內(nèi)的數(shù)據(jù)。當(dāng)然解決的方法也很簡單，就是采用自增主鍵，update和delete的時(shí)候where條件中根據(jù)自增主鍵來定位數(shù)據(jù)，這樣就能保證每次數(shù)據(jù)庫只鎖定一行數(shù)據(jù)。

九、死鎖

死鎖是指兩個(gè)或兩個(gè)以上的事務(wù)再執(zhí)行過程中因爭奪資源而造成的一種相互等待的現(xiàn)象。mysql中等待圖的方式來檢查是否存在死鎖的情況，如果存在死鎖則選擇回滾代價(jià)最小的事務(wù)進(jìn)行回滾。

雖然mysql中可以自動檢測死鎖，但是在實(shí)際開發(fā)中還是要盡量的避免死鎖，否則并發(fā)效率會收到極大的影響。常用的預(yù)防死鎖的方法有，1. 盡可能縮短事務(wù)的長度，單步事務(wù)是永遠(yuǎn)不會出現(xiàn)死鎖的。2. 可能存在沖突的跨表事務(wù)盡量避免并發(fā)。3. 進(jìn)行批量更新操作時(shí)盡量用自增主鍵來作為選擇條件，并對主鍵值進(jìn)行排序，這樣就不會造成死鎖了。舉例來說，如果兩個(gè)連接同時(shí)執(zhí)行一下語句是不會有死鎖的。

update tb_goods set store_quantity=store_quantity +10 where goods_id=1;

update tb_goods set store_quantity=store_quantity +10 where goods_id=3;

update tb_goods set store_quantity=store_quantity +10 where goods_id=7;

update tb_goods set store_quantity=store_quantity +10 where goods_id=9;因?yàn)閣here中用的是自增主鍵，且語句中的主鍵值是遞增的，這樣多個(gè)事務(wù)中是不會出現(xiàn)循環(huán)鎖定的。

如果兩個(gè)事務(wù)分別執(zhí)行如下語句，則可能出現(xiàn)死鎖。

事務(wù)A

update tb_goods set store_quantity=store_quantity +10 where goods_id=1;

update tb_goods set store_quantity=store_quantity +10 where goods_id=3;

update tb_goods set store_quantity=store_quantity +10 where goods_id=7;

update tb_goods set store_quantity=store_quantity +10 where goods_id=9;事務(wù)B

update tb_goods set store_quantity=store_quantity +10 where goods_id=3;

update tb_goods set store_quantity=store_quantity +10 where goods_id=1;

update tb_goods set store_quantity=store_quantity +10 where goods_id=7;

update tb_goods set store_quantity=store_quantity +10 where goods_id=9;當(dāng)兩個(gè)事務(wù)同事執(zhí)行完第一個(gè)更新語句，主鍵值為1和3的行并鎖定，接下來兩個(gè)事務(wù)在分別請求

主鍵值為1和3的行就出現(xiàn)了循環(huán)等待，也就是死鎖。

死鎖存在的條件有三個(gè)，1.有兩個(gè)以上的并發(fā)修改的事務(wù)；2. 多個(gè)事務(wù)都是多步的；3. 多步操作中想搶占的鎖資源存在并發(fā)關(guān)系。只要破壞這三個(gè)條件中的任意一個(gè)，則死鎖就可以避免。

當(dāng)線上數(shù)據(jù)庫出現(xiàn)大量死鎖，就需要進(jìn)行排查，排查時(shí)不止要檢查死鎖出現(xiàn)的sql語句，還有檢查觸發(fā)改sql語句的上下文以及具體的業(yè)務(wù)邏輯，根據(jù)上下文語句的加鎖范圍分析存在爭用的記錄，從而定位死鎖出現(xiàn)的原因。

十、事務(wù)的組織

以簡單的一個(gè)購物業(yè)務(wù)場景為例，流程圖如下。

簡單來說就是用先想買商品1，那么先檢查商品1是否有庫存，如果有就將若干件商品1加入用戶訂單。接下來用戶還想買商品2，那么再檢查商品2是否有庫存，如果有就將若干件商品2加入用戶訂單。最后用戶確認(rèn)提交訂單并付款就完成了購物的操作。

這里需要用事務(wù)和鎖來實(shí)現(xiàn)業(yè)務(wù)需求，原因有兩個(gè)。

1. 因?yàn)闃I(yè)務(wù)需要保證操作的原子性，查詢庫存、更新訂單和扣除庫存要么都成功，要么都不成功，所以要用事務(wù)。

2. 要避免業(yè)務(wù)糾紛，業(yè)務(wù)中查詢庫存到扣除庫存的過程中不能讓庫存再發(fā)生變化，因此要用鎖，在查詢庫存的時(shí)候用for update人工加鎖。

最簡單的是用一個(gè)大事務(wù)來實(shí)現(xiàn)業(yè)務(wù)需求，如下圖所示。

這樣固然是可以保證業(yè)務(wù)的原子性的，確保庫存不會被扣成負(fù)的。但是在實(shí)際業(yè)務(wù)中，往往用戶在挑選完商品1后到用戶再添加商品2這之間所間隔的時(shí)間是非常長的，這樣會導(dǎo)致在很長的時(shí)間內(nèi)只有一個(gè)人可以買商品1，這顯然是不合理的。此外，如果用戶挑選完商品1后連接意外中斷了，當(dāng)用戶在重新連接回來后之前的未完成訂單是找不到的，這肯定也是不能讓人接受的。

因此需要對上面的大事務(wù)進(jìn)行優(yōu)化，將一個(gè)大事務(wù)切分為若干個(gè)小事務(wù)，例如上面的事物可以切分為三個(gè)小事務(wù)，一個(gè)是添加商品1的事務(wù)，一個(gè)是添加商品2的事務(wù)，最后是提交付款的事務(wù)，如下圖所示。

如果是一個(gè)簡單的購物網(wǎng)站，到這里就已經(jīng)可以滿足購物業(yè)務(wù)的需求了。但是像那行大的電商網(wǎng)站，這樣還是不行的，因?yàn)榭梢悦總€(gè)商品下還有各種優(yōu)惠、特價(jià)等，這樣當(dāng)將一個(gè)商品添加到訂單中時(shí)不僅僅要檢查是否有庫存，還要檢查是否有什么優(yōu)惠、特價(jià)等等，這樣事務(wù)就又變長了，商品鎖定的時(shí)間又變長了，這對于大型電商網(wǎng)站是不能接受的，因此需要進(jìn)一步優(yōu)化。優(yōu)化的方法也很簡單，就是在用select檢查庫存時(shí)不用for update進(jìn)行鎖定了，而在執(zhí)行update操作時(shí)添加一個(gè)判斷條件，就是判斷庫存是否夠，如下圖所示。

可能有人會疑問，既然這樣，那為什么還要在update前進(jìn)行select操作查詢商品的庫存，這么做主要有兩個(gè)原因，首先，在用戶下單前需要給用戶展示還有多少商品。另外，這樣可以事先判斷是否有庫存，如果沒有就不用執(zhí)行下面的update操作了，可以提升應(yīng)用服務(wù)器和數(shù)據(jù)庫服務(wù)器的性能。

總結(jié)來說，事物和鎖的優(yōu)化思路就是在保證業(yè)務(wù)正確的前提下，盡量縮短鎖的時(shí)間。

十一、懸掛事務(wù)與鎖超時(shí)排除

實(shí)際的線上維護(hù)時(shí)可能會出現(xiàn)幾個(gè)記錄被長時(shí)間的鎖定，導(dǎo)致訪問這些數(shù)據(jù)的請求全部超時(shí)，這有可能是有一些懸掛事務(wù)中鎖定的數(shù)據(jù)。懸掛事務(wù)就是一些長時(shí)間沒有提交且沒有進(jìn)行進(jìn)一步操作的事務(wù)。懸掛事務(wù)出現(xiàn)的原因可能是用戶連接突然中斷，而應(yīng)用服務(wù)器確保留了這個(gè)數(shù)據(jù)庫連接和執(zhí)行到一半的事務(wù)。

這樣的情況用show processlist是無法定位有懸掛事務(wù)的連接的。show processlist只能查出當(dāng)前有多少個(gè)連接，哪些是活躍的，哪些是不活躍的，但是無法找出哪些連接中有事務(wù)長時(shí)間的占著鎖沒有釋放。

要查看懸掛事務(wù)的連接可以用如下語句來查詢。

select trx_mysql_thread_id,trx_state,now()-trx_started,trx_rows_locked from information_schema.innodb_trx;查詢的結(jié)果有四列，分別表示連接Id、當(dāng)前狀態(tài)、存在的時(shí)間、鎖定的資源數(shù)。

找到懸掛事務(wù)后可以用kill命令來結(jié)束連接。但是因?yàn)闊o法知道阻塞的sql具體在執(zhí)行什么，因此需要與業(yè)務(wù)確認(rèn)看是否可以直接殺死連接。

總結(jié)

以上是生活随笔為你收集整理的mysql数据库工程师网易微专业_网易MySQL数据库工程师微专业学习笔记（五）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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