Mysql數(shù)據(jù)庫(kù)操作事務(wù)及存儲(chǔ)引擎講義

本文主要給大家介紹MySQL數(shù)據(jù)庫(kù)操作事務(wù)及存儲(chǔ)引擎講義，希望可以給大家補(bǔ)充和更新些知識(shí)，如有其它問(wèn)題需要了解的可以持續(xù)在創(chuàng)新互聯(lián)行業(yè)資訊里面關(guān)注我的更新文章的。

網(wǎng)站建設(shè)哪家好，找成都創(chuàng)新互聯(lián)！專(zhuān)注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、微信小程序開(kāi)發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶(hù)創(chuàng)新互聯(lián)還提供了扶綏免費(fèi)建站歡迎大家使用！

事務(wù)的概念

事務(wù)是一種機(jī)制，一個(gè)操作序列，包含了一組數(shù)據(jù)庫(kù)操作命令，并且把所有命令作為一個(gè)整體一起想系統(tǒng)提交或撤銷(xiāo)操作請(qǐng)求，
即這一組數(shù)據(jù)庫(kù)命令要么都執(zhí)行，要么都不執(zhí)行，事務(wù)是一個(gè)不可分割的工作邏輯單元，在數(shù)據(jù)庫(kù)系統(tǒng)上執(zhí)行并發(fā)操作時(shí)，
事務(wù)是最小的控制單元，事務(wù)適用于用戶(hù)同時(shí)操作數(shù)據(jù)庫(kù)系統(tǒng)的場(chǎng)景，如銀行，保險(xiǎn)公司
以及證券交易系統(tǒng)等，通過(guò)事務(wù)的整體以保證數(shù)據(jù)的一致性，事務(wù)是保證了一組操作的平穩(wěn)性和可預(yù)測(cè)性的技術(shù)。

事務(wù)的四大特性（ACID）

如果一個(gè)數(shù)據(jù)庫(kù)聲稱(chēng)支持事務(wù)的操作，那么該數(shù)據(jù)庫(kù)必須要具備以下四個(gè)特性：

原子性（Atomicity）

原子性是指事務(wù)包含的所有操作要么全部成功，要么全部失敗回滾，因此事務(wù)的操作如果成功就必須要完全應(yīng)用到數(shù)據(jù)庫(kù)，如果操作失敗則不能對(duì)數(shù)據(jù)庫(kù)有任何影響。

一致性（Consistency）

一致性是指事務(wù)必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變換到另一個(gè)一致性狀態(tài)，也就是說(shuō)一個(gè)事務(wù)執(zhí)行之前和執(zhí)行之后都必須處于一致性狀態(tài)。
拿轉(zhuǎn)賬來(lái)說(shuō)，假設(shè)用戶(hù)A和用戶(hù)B兩者的錢(qián)加起來(lái)一共是5000，那么不管A和B之間如何轉(zhuǎn)賬，轉(zhuǎn)幾次賬，事務(wù)結(jié)束后兩個(gè)用戶(hù)的錢(qián)相加起來(lái)應(yīng)該還得是5000，這就是事務(wù)的一致性。

隔離性（Isolation）

隔離性是當(dāng)多個(gè)用戶(hù)并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，比如操作同一張表時(shí)，數(shù)據(jù)庫(kù)為每一個(gè)用戶(hù)開(kāi)啟的事務(wù)，不能被其他事務(wù)的操作所干擾，多個(gè)并發(fā)事務(wù)之間要相互隔離。
即要達(dá)到這么一種效果：對(duì)于任意兩個(gè)并發(fā)的事務(wù)T1和T2，在事務(wù)T1看來(lái)，T2要么在T1開(kāi)始之前就已經(jīng)結(jié)束，要么在T1結(jié)束之后才開(kāi)始，這樣每個(gè)事務(wù)都感覺(jué)不到有其他事務(wù)在并發(fā)地執(zhí)行。

持久性（Durability）

持久性是指一個(gè)事務(wù)一旦被提交了，那么對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)的改變就是永久性的，即便是在數(shù)據(jù)庫(kù)系統(tǒng)遇到故障的情況下也不會(huì)丟失提交事務(wù)的操作。
例如我們?cè)谑褂肑DBC操作數(shù)據(jù)庫(kù)時(shí)，在提交事務(wù)方法后，提示用戶(hù)事務(wù)操作完成，當(dāng)我們程序執(zhí)行完成直到看到提示后，就可以認(rèn)定事務(wù)以及正確提交，
即使這時(shí)候數(shù)據(jù)庫(kù)出現(xiàn)了問(wèn)題，也必須要將我們的事務(wù)完全執(zhí)行完成，否則就會(huì)造成我們看到提示事務(wù)處理完畢，但是數(shù)據(jù)庫(kù)因?yàn)楣收隙鴽](méi)有執(zhí)行事務(wù)的重大錯(cuò)誤。

事務(wù)的隔離級(jí)別（默認(rèn)事務(wù)級(jí)別為可重復(fù)讀）

總的說(shuō)，數(shù)據(jù)庫(kù)事務(wù)無(wú)非就兩種：讀取事務(wù)（select）、修改事務(wù)（update,insert）。在沒(méi)有事務(wù)隔離控制的時(shí)候，多個(gè)事務(wù)在同一時(shí)刻對(duì)同一數(shù)據(jù)的操作可能就會(huì)影響到最終期望的結(jié)果，通常有四種情況：
（1） 兩個(gè)更新事務(wù)同時(shí)修改一條數(shù)據(jù)時(shí)，很顯然這種情況是最嚴(yán)重的了，程序中無(wú)論如何也不能出現(xiàn)這種情況，因?yàn)樗鼤?huì)造成更新的丟失！
（2） 一個(gè)更新事務(wù)更新一條數(shù)據(jù)時(shí)，另一個(gè)讀取事務(wù)讀取了還沒(méi)提交的更新，這種情況下會(huì)出現(xiàn)讀取到臟數(shù)據(jù)。
（3） 一個(gè)讀取事務(wù)讀取一條數(shù)據(jù)時(shí)，另一個(gè)更新事務(wù)修改了這條數(shù)據(jù)，這時(shí)就會(huì)出現(xiàn)不可重現(xiàn)的讀取。
（4）一個(gè)讀取事務(wù)讀取時(shí)，另一個(gè)插入事務(wù)（注意此處時(shí)插入）插入了一條新數(shù)據(jù)，這樣就可能多讀出一條數(shù)據(jù)，出現(xiàn)幻讀。
以上四種情況描述完畢，相信大家也發(fā)現(xiàn)規(guī)律了，前三種是對(duì)同一條數(shù)據(jù)的并發(fā)操作，對(duì)程序的結(jié)果可能產(chǎn)生致命影響，尤其是金融等實(shí)時(shí)性，準(zhǔn)確性要求極高的系統(tǒng)，絕不容許這三中情況的出現(xiàn)，
相比第四種情況不會(huì)影響數(shù)據(jù)的真實(shí)性，在很多情況下是允許的，如社交論壇等實(shí)時(shí)性要求不高的系統(tǒng)！
綜上四個(gè)情況，我們可以大致這樣簡(jiǎn)單的理解（最初說(shuō)的兩種事務(wù)的自由組合2*2=4）：
A） 修改時(shí)允許修改（丟失更新）
B） 修改時(shí)允許讀?。ㄅK讀）
C) 讀取時(shí)允許修改（不可重復(fù)讀）
D) 讀取時(shí)允許插入（幻讀）
從上到下問(wèn)題越來(lái)越不嚴(yán)重，但所需的性能開(kāi)銷(xiāo)卻越大。因?yàn)椴煌南到y(tǒng)允許不同級(jí)別的情況，所以就出現(xiàn)了事務(wù)隔離這么一個(gè)東東，來(lái)允許我們?cè)O(shè)定數(shù)據(jù)庫(kù)的并發(fā)行為。

總結(jié)下如果不考慮事務(wù)的隔離性，會(huì)發(fā)生的幾種問(wèn)題：

臟讀
臟讀是指在一個(gè)事務(wù)處理過(guò)程里讀取了另一個(gè)未提交的事務(wù)中的數(shù)據(jù)。
當(dāng)一個(gè)事務(wù)正在多次修改某個(gè)數(shù)據(jù)，而在這個(gè)事務(wù)中這多次的修改都還未提交，這時(shí)一個(gè)并發(fā)的事務(wù)來(lái)訪問(wèn)該數(shù)據(jù)，就會(huì)造成兩個(gè)事務(wù)得到的數(shù)據(jù)不一致。例如：用戶(hù)A向用戶(hù)B轉(zhuǎn)賬100元，對(duì)應(yīng)SQL命令如下
update account set money=money+100 where name=’B’;  (此時(shí)A通知B)
update account set money=money - 100 where name=’A’;
當(dāng)只執(zhí)行第一條SQL時(shí)，A通知B查看賬戶(hù)，B發(fā)現(xiàn)確實(shí)錢(qián)已到賬（此時(shí)即發(fā)生了臟讀），而之后無(wú)論第二條SQL是否執(zhí)行，只要該事務(wù)不提交，則所有操作都將回滾，那么當(dāng)B以后再次查看賬戶(hù)時(shí)就會(huì)發(fā)現(xiàn)錢(qián)其實(shí)并沒(méi)有轉(zhuǎn)。

不可重復(fù)讀
不可重復(fù)讀是指在對(duì)于數(shù)據(jù)庫(kù)中的某個(gè)數(shù)據(jù)，一個(gè)事務(wù)范圍內(nèi)多次查詢(xún)卻返回了不同的數(shù)據(jù)值，這是由于在查詢(xún)間隔，被另一個(gè)事務(wù)修改并提交了。
例如事務(wù)T1在讀取某一數(shù)據(jù)，而事務(wù)T2立馬修改了這個(gè)數(shù)據(jù)并且提交事務(wù)給數(shù)據(jù)庫(kù)，事務(wù)T1再次讀取該數(shù)據(jù)就得到了不同的結(jié)果，發(fā)送了不可重復(fù)讀。
不可重復(fù)讀和臟讀的區(qū)別是，臟讀是某一事務(wù)讀取了另一個(gè)事務(wù)未提交的臟數(shù)據(jù)，而不可重復(fù)讀則是讀取了前一事務(wù)提交的數(shù)據(jù)。
在某些情況下，不可重復(fù)讀并不是問(wèn)題，比如我們多次查詢(xún)某個(gè)數(shù)據(jù)當(dāng)然以最后查詢(xún)得到的結(jié)果為主。但在另一些情況下就有可能發(fā)生問(wèn)題，例如對(duì)于同一個(gè)數(shù)據(jù)A和B依次查詢(xún)就可能不同，A和B就可能打起來(lái)了……

虛讀(幻讀)
幻讀是事務(wù)非獨(dú)立執(zhí)行時(shí)發(fā)生的一種現(xiàn)象。例如事務(wù)T1對(duì)一個(gè)表中所有的行的某個(gè)數(shù)據(jù)項(xiàng)做了從“1”修改為“2”的操作，這時(shí)事務(wù)T2又對(duì)這個(gè)表中插入了一行數(shù)據(jù)項(xiàng)，而這個(gè)數(shù)據(jù)項(xiàng)的數(shù)值還是為“1”并且提交給數(shù)據(jù)庫(kù)。而操作事務(wù)T1的用戶(hù)如果再查看剛剛修改的數(shù)據(jù)，會(huì)發(fā)現(xiàn)還有一行沒(méi)有修改，其實(shí)這行是從事務(wù)T2中添加的，就好像產(chǎn)生幻覺(jué)一樣，這就是發(fā)生了幻讀。
幻讀和不可重復(fù)讀都是讀取了另一條已經(jīng)提交的事務(wù)（這點(diǎn)就臟讀不同），所不同的是不可重復(fù)讀查詢(xún)的都是同一個(gè)數(shù)據(jù)項(xiàng)，而幻讀針對(duì)的是一批數(shù)據(jù)整體（比如數(shù)據(jù)的個(gè)數(shù)）。

SQL標(biāo)準(zhǔn)定義了4類(lèi)隔離級(jí)別，包括了一些具體規(guī)則，用來(lái)限定事務(wù)內(nèi)外的哪些改變是可見(jiàn)的，哪些是不可見(jiàn)的。低級(jí)別的隔離級(jí)一般支持更高的并發(fā)處理，并擁有更低的系統(tǒng)開(kāi)銷(xiāo)。

MySQL數(shù)據(jù)庫(kù)的四種事務(wù)隔離級(jí)別

Read Uncommitted（讀取未提交內(nèi)容）

   在該隔離級(jí)別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。本隔離級(jí)別很少用于實(shí)際應(yīng)用，因?yàn)樗男阅芤膊槐绕渌?jí)別好多少。讀取未提交的數(shù)據(jù)，也被稱(chēng)之為臟讀（Dirty Read）；

Read Committed（讀取提交內(nèi)容）

   這是大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)的默認(rèn)隔離級(jí)別（但不是MySQL默認(rèn)的）。它滿(mǎn)足了隔離的簡(jiǎn)單定義：一個(gè)事務(wù)只能看見(jiàn)已經(jīng)提交事務(wù)所做的改變。這種隔離級(jí)別 也支持所謂的不可重復(fù)讀（Nonrepeatable Read），因?yàn)橥皇聞?wù)的其他實(shí)例在該實(shí)例處理其間可能會(huì)有新的commit，所以同一select可能返回不同結(jié)果；

Repeatable Read（可重讀）

   這是MySQL的默認(rèn)事務(wù)隔離級(jí)別，它確保同一事務(wù)的多個(gè)實(shí)例在并發(fā)讀取數(shù)據(jù)時(shí)，會(huì)看到同樣的數(shù)據(jù)行。不過(guò)理論上，這會(huì)導(dǎo)致另一個(gè)棘手的問(wèn)題：幻讀 （Phantom Read）。
   簡(jiǎn)單的說(shuō)，幻讀指當(dāng)用戶(hù)讀取某一范圍的數(shù)據(jù)行時(shí)，另一個(gè)事務(wù)又在該范圍內(nèi)插入了新行，當(dāng)用戶(hù)再讀取該范圍的數(shù)據(jù)行時(shí)，會(huì)發(fā)現(xiàn)有新的“幻影” 行。
   InnoDB和Falcon存儲(chǔ)引擎通過(guò)多版本并發(fā)控制（MVCC，Multiversion Concurrency Control）機(jī)制解決了該問(wèn)題

Serializable（可串行化）

   這是最高的隔離級(jí)別，它通過(guò)強(qiáng)制事務(wù)排序，使之不可能相互沖突，從而解決幻讀問(wèn)題。簡(jiǎn)言之，它是在每個(gè)讀的數(shù)據(jù)行上加上共享鎖。在這個(gè)級(jí)別，可能導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)。
     這四種隔離級(jí)別采取不同的鎖類(lèi)型來(lái)實(shí)現(xiàn)，若讀取的是同一個(gè)數(shù)據(jù)的話，就容易發(fā)生問(wèn)題。例如：

     臟讀(Drity Read)：某個(gè)事務(wù)已更新一份數(shù)據(jù)，另一個(gè)事務(wù)在此時(shí)讀取了同一份數(shù)據(jù)，由于某些原因，前一個(gè)RollBack了操作，則后一個(gè)事務(wù)所讀取的數(shù)據(jù)就會(huì)是不正確的。
     不可重復(fù)讀(Non-repeatable read):在一個(gè)事務(wù)的兩次查詢(xún)之中數(shù)據(jù)不一致，這可能是兩次查詢(xún)過(guò)程中間插入了一個(gè)事務(wù)更新的原有的數(shù)據(jù)。
     幻讀(Phantom Read):在一個(gè)事務(wù)的兩次查詢(xún)中數(shù)據(jù)筆數(shù)不一致，例如有一個(gè)事務(wù)查詢(xún)了幾列(Row)數(shù)據(jù)，而另一個(gè)事務(wù)卻在此時(shí)插入了新的幾列數(shù)據(jù)，先前的事務(wù)在接下來(lái)的查詢(xún)中，就會(huì)發(fā)現(xiàn)有幾列數(shù)據(jù)是它先前所沒(méi)有的。

Mysql操作事務(wù)

默認(rèn)情況下Mysql的事務(wù)是自動(dòng)提交的，之前我們用SQL操作數(shù)據(jù)庫(kù)時(shí)，一條數(shù)據(jù)執(zhí)行后
系統(tǒng)胡自動(dòng)執(zhí)行事務(wù)提交。當(dāng)需要一組語(yǔ)句作為一個(gè)事務(wù)提交時(shí)，需要手動(dòng)對(duì)事務(wù)進(jìn)行控制。
手動(dòng)控制事務(wù)有兩種方法，一種是使用事務(wù)處理命令控制，另一種是使用set設(shè)置事務(wù)的處理方式
使用事務(wù)命令控制事務(wù)（3個(gè)命令）
begin:表示開(kāi)始一個(gè)事務(wù)，后面會(huì)有多條數(shù)據(jù)庫(kù)操作語(yǔ)句執(zhí)行
commit:表示提交一個(gè)事務(wù)，對(duì)應(yīng)前面的begin操作，它們之間的數(shù)據(jù)庫(kù)操作語(yǔ)句一起完成
rollback:表示回滾一個(gè)事務(wù)，再begin和commit之間，如果某一個(gè)數(shù)據(jù)庫(kù)的操作語(yǔ)句出現(xiàn)錯(cuò)誤
執(zhí)行rollback回滾，數(shù)據(jù)庫(kù)回到begin之前的狀態(tài)，也就是操作語(yǔ)句都沒(méi)執(zhí)行。

前面我們了解了原理，下面我們開(kāi)始實(shí)驗(yàn)

mysql操作事務(wù)命令實(shí)驗(yàn)

這是之前的數(shù)據(jù)表
mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.01 sec)

mysql> insert into info (name,address,age) values ('wangwu','hangzhou',30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;  #查看數(shù)據(jù)表的數(shù)據(jù)之后，發(fā)現(xiàn)默認(rèn)是自動(dòng)提交的
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
+----+----------+----------+-----+
3 rows in set (0.00 sec)

mysql> begin; #開(kāi)始事務(wù)
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('zhaoliu','hangzhou',31); #我們?cè)偌右粭l操作命令，數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)

mysql> savepoint a; #我們打上一個(gè)標(biāo)記a,下次我們可以回滾到標(biāo)記a
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('tianqi','hangzhou',33); #我們?cè)偌右粭l數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)

mysql> savepoint b; #打上標(biāo)記b
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('heipa','hangzhou',35); #再加一條數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
|  5 | tianqi   | hangzhou |  33 |
|  6 | heipa    | hangzhou |  35 |

我們?cè)匍_(kāi)一臺(tái)終端去查看info數(shù)據(jù)表中有沒(méi)有數(shù)據(jù)

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use schol;
ERROR 1049 (42000): Unknown database 'schol'
mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> 
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| hob              |
| info             |
| user             |
| view_user        |
+------------------+
4 rows in set (0.00 sec)

mysql> select * from info; #發(fā)現(xiàn)沒(méi)有數(shù)據(jù)，我們創(chuàng)建的事務(wù)只是存放在內(nèi)存中，并沒(méi)有真實(shí)寫(xiě)到硬盤(pán)中，提交事務(wù)才行
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
+----+----------+----------+-----+
3 rows in set (0.00 sec)

回滾到b沒(méi)有添加heiba的時(shí)候

mysql> rollback to b;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
|  5 | tianqi   | hangzhou |  33 |
+----+----------+----------+-----+
5 rows in set (0.00 sec)

回滾到a的沒(méi)有tianqi的時(shí)候

mysql> rollback to a;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

回滾到最初的時(shí)候

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
+----+----------+----------+-----+
3 rows in set (0.00 sec)

這時(shí)候我們?cè)诨貪L到b，錯(cuò)誤，之前我們的已經(jīng)存放在內(nèi)存的已經(jīng)釋放掉了，就回不去了

mysql> rollback to b;
ERROR 1305 (42000): SAVEPOINT b does not exist

#我們?cè)俨逡恍袛?shù)據(jù)進(jìn)去，再試試回滾。
mysql> insert into info (name,address,age) values ('zhaoliu','hangzhou',31);
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | zhaoliu  | hangzhou |  31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | zhaoliu  | hangzhou |  31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

我們以另一個(gè)終端為基準(zhǔn)，我們發(fā)現(xiàn)zhaoliu這行數(shù)據(jù)被寫(xiě)進(jìn)去了

我們只要執(zhí)行rollback，而不是回滾到一個(gè)節(jié)點(diǎn)，都視為提交事務(wù)了

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | zhaoliu  | hangzhou |  31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

我們回到第一個(gè)終端再插一行數(shù)據(jù)進(jìn)去

mysql> insert into info (name,address,age) values ('tianqi','hangzhou',33);
Query OK, 1 row affected (0.00 sec)

mysql> commit; #提交事務(wù)
Query OK, 0 rows affected (0.00 sec)

到另一臺(tái)終端去查看以下，提交事務(wù)也行

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | zhaoliu  | hangzhou |  31 |
|  8 | tianqi   | hangzhou |  33 |
+----+----------+----------+-----+
5 rows in set (0.00 sec)

關(guān)閉自動(dòng)提交

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update info set address='beijing' where name='tianqi'; #把tianqi的address修改成beijing
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

再到另一終端驗(yàn)證是不是沒(méi)有修改

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | zhaoliu  | hangzhou |  31 |
|  8 | tianqi   | hangzhou |  33 |
+----+----------+----------+-----+
5 rows in set (0.00 sec)

我們回到第一臺(tái)終端開(kāi)啟自動(dòng)提交

mysql> set autocommit=1;
Query OK, 0 rows affected (0.01 sec)

再到另一臺(tái)終端驗(yàn)證是不是修改了

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | zhaoliu  | hangzhou |  31 |
|  8 | tianqi   | beijing  |  33 |
+----+----------+----------+-----+
5 rows in set (0.00 sec)

存儲(chǔ)引擎

存儲(chǔ)引擎概念介紹

?MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲(chǔ)在文件中，每一種技術(shù)都使用不同的存儲(chǔ)機(jī)制，索引技巧，鎖定水平并最終提供不同的功能和能力，這些不同的技術(shù)以及配套的功能在MySQL中稱(chēng)為存儲(chǔ)引擎
存儲(chǔ)引擎就是MySQL將數(shù)據(jù)存儲(chǔ)在文件系統(tǒng)中的存儲(chǔ)方式或存儲(chǔ)格式
目前MySQL常用的兩種存儲(chǔ)引擎：
?MyISQM
?InnoDB
MySQL存儲(chǔ)引擎是MySQL數(shù)據(jù)庫(kù)云服務(wù)器中的組件，負(fù)責(zé)為數(shù)據(jù)庫(kù)執(zhí)行實(shí)際的數(shù)據(jù)I/O操作
使用特殊存儲(chǔ)引擎的主要優(yōu)點(diǎn)之一在于，僅需提供特殊應(yīng)用所需的特性，數(shù)據(jù)庫(kù)中的系統(tǒng)開(kāi)銷(xiāo)較小，具有更有效和更高的數(shù)據(jù)庫(kù)性能
MySQL系統(tǒng)中，存儲(chǔ)引擎處于文件系統(tǒng)之上，在數(shù)據(jù)保存到數(shù)據(jù)文件之前會(huì)傳輸存儲(chǔ)引擎，之后按照各個(gè)存儲(chǔ)引擎的存儲(chǔ)格式進(jìn)行存儲(chǔ)

MyISQM的介紹

ISAM的特點(diǎn)：
ISAM執(zhí)行讀取操作的速度很快
而且不占用大量的內(nèi)存和存儲(chǔ)資源
他不支持事務(wù)處理
不能夠容錯(cuò)
MyISAM的特點(diǎn)：
不支持事務(wù)
表級(jí)鎖定形式，數(shù)據(jù)在更新時(shí)鎖定整個(gè)表
數(shù)據(jù)庫(kù)在讀寫(xiě)過(guò)程中相互阻塞
●會(huì)在數(shù)據(jù)寫(xiě)入的過(guò)程阻塞用戶(hù)數(shù)據(jù)的讀取
●也會(huì)在數(shù)據(jù)讀取的過(guò)程中阻塞用戶(hù)的數(shù)據(jù)寫(xiě)入
可通過(guò)key_buffer_size來(lái)設(shè)置緩存索引，提高訪問(wèn)性能，減少磁盤(pán)IO的壓力
●但緩存只會(huì)緩存索引文件，不會(huì)緩存數(shù)據(jù)
采用MyISAM存儲(chǔ)引擎數(shù)據(jù)單獨(dú)寫(xiě)入或讀取，速度過(guò)程較快且占用資源相對(duì)少
MyISAM存儲(chǔ)引擎它不支持外鍵約束，只支持全文索引
每個(gè)MyISAM在磁盤(pán).上存儲(chǔ)成三個(gè)文件，每- -個(gè)文件的名字以表的名字開(kāi)始，擴(kuò)展名指出文件類(lèi)型
MyISAM在磁盤(pán).上存儲(chǔ)的文件

●.frm文件存儲(chǔ)表定義

●數(shù)據(jù)文件的擴(kuò)展名為.MYD (MYData)
●索引文件的擴(kuò)展名是.MYI (MYIndex)
MyISAM適用的生產(chǎn)場(chǎng)景舉例
公司業(yè)務(wù)不需要事務(wù)的支持
一般單方面讀取數(shù)據(jù)比較多的業(yè)務(wù)，或單方面寫(xiě)入數(shù)據(jù)比較多的業(yè)務(wù)
MyISAM存儲(chǔ)引擎數(shù)據(jù)讀寫(xiě)都比較頻繁場(chǎng)景不適合
使用讀寫(xiě)并發(fā)訪問(wèn)相對(duì)較低的業(yè)務(wù)
數(shù)據(jù)修改相對(duì)較少的業(yè)務(wù)
對(duì)數(shù)據(jù)業(yè)務(wù)一致性要求不是非常高的業(yè)務(wù)
服務(wù)器硬件資源相對(duì)比較差

InnoDB特點(diǎn)介紹

支持事務(wù):支持4個(gè)事務(wù)隔離級(jí)別
行級(jí)鎖定，但是全表掃描仍然會(huì)是表級(jí)鎖定
讀寫(xiě)阻塞與事務(wù)隔離級(jí)別相關(guān)
具有非常高效的緩存特性:能緩存索引，也能緩存數(shù)據(jù)
表與主鍵以簇的方式存儲(chǔ)
支持分區(qū)、表空間，類(lèi)似oracle數(shù)據(jù)庫(kù)
支持外鍵約束，5.5以前不支持全文索引，5.5版本以后支持全文索引
對(duì)硬件資源要求還是比較高的場(chǎng)合
InnoDB適用生產(chǎn)場(chǎng)景分析
業(yè)務(wù)需要事務(wù)的支持
行級(jí)鎖定對(duì)高并發(fā)有很好的適應(yīng)能力，但需確保查詢(xún)是通過(guò)索引來(lái)完成
業(yè)務(wù)數(shù)據(jù)更新較為頻繁的場(chǎng)景，如:論壇,微博等
業(yè)務(wù)數(shù)據(jù)一致性要求較高，例如:銀行業(yè)務(wù)
硬件設(shè)備內(nèi)存較大，利用Innodb較好的緩存能力來(lái)提高內(nèi)存利用率，減少磁盤(pán)I0的壓力
企業(yè)選擇存儲(chǔ)引擎依據(jù)
需要考慮每個(gè)存儲(chǔ)引擎提供了那些不同的核心功能及應(yīng)運(yùn)場(chǎng)景
支持的字段和數(shù)據(jù)類(lèi)型
所有引擎都支持通用的數(shù)據(jù)類(lèi)型
但不是所有的引擎都支持其他的字段類(lèi)型，如二進(jìn)制對(duì)象
鎖定類(lèi)型：不同的存儲(chǔ)引擎支持不同級(jí)別的鎖定
表鎖定
行鎖定

索引的支持

建立索引在搜索和恢復(fù)數(shù)據(jù)庫(kù)中的數(shù)據(jù)的時(shí)候能夠顯著提高性能
不同的存儲(chǔ)引擎提供不同的制作索引的技術(shù)
有些存儲(chǔ)引擎根本不支持索引
事務(wù)處理的支持
事務(wù)處理功能通過(guò)提供在向表中更新和插入信息期間的可靠性
課根據(jù)企業(yè)業(yè)務(wù)是否支持事務(wù)選項(xiàng)選擇存儲(chǔ)引擎
配置存儲(chǔ)引擎
在企業(yè)中選擇好合適的存儲(chǔ)引擎之后，就可以進(jìn)行修改了

修改步驟

查看數(shù)據(jù)庫(kù)可配置的存儲(chǔ)引擎
查看表正在使用的存儲(chǔ)引擎
配置存儲(chǔ)引擎為所選擇的類(lèi)型
使用show engines查看系統(tǒng)支持的存儲(chǔ)引擎
方法1:
show table status from  庫(kù)名  where  name='表名;
方法2:
show create table    表名;
修改存儲(chǔ)引擎
方法1: alter table修改;
alter table table_ name engine=引擎;
方法2:修改my.cnf,指定默認(rèn)存儲(chǔ)引擎并重啟服務(wù)
default-storage-engine=InnDB
方法3: create table創(chuàng)建表時(shí)指定存儲(chǔ)引擎
create table   表名   (字段) engine=   引擎
方法4: Mysql_ convert_ table_ format 轉(zhuǎn)化存儲(chǔ)引擎
Mysql_ convert_ table_ format -user=root -password=密碼
- sock=/tmp/mysql.sock - engine =引擎 庫(kù)名 表名

存儲(chǔ)引擎的實(shí)驗(yàn)

mysql> show engines; #查看系統(tǒng)默認(rèn)的存儲(chǔ)引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show create table info; #查看info這張表，存儲(chǔ)引擎是默認(rèn)InnoDB
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info  | CREATE TABLE "info" (
  "id" int(4) NOT NULL AUTO_INCREMENT,
  "name" varchar(10) NOT NULL,
  "address" varchar(50) DEFAULT 'nanjing',
  "age" int(3) NOT NULL,
  PRIMARY KEY ("id"),
  UNIQUE KEY "index_name" ("name"),
  FULLTEXT KEY "full_addr" ("address")
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> quit
Bye

想以后創(chuàng)建的每一張表都是MyISAM存儲(chǔ)引擎，就到配置文件里改。

[root@localhost ~]# vim /etc/my.cnf  #進(jìn)入數(shù)據(jù)庫(kù)主配置文件

default-storage-engine=Myisam  #添加Myisam的存儲(chǔ)引擎
[root@localhost ~]# mysql -u root -p 進(jìn)入數(shù)據(jù)庫(kù)
mysql> use school; #進(jìn)入school數(shù)據(jù)庫(kù)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table a ( id int );  #創(chuàng)建a數(shù)據(jù)表
Query OK, 0 rows affected (0.00 sec)
mysql> show create table a;  #查看a數(shù)據(jù)表，發(fā)現(xiàn)存儲(chǔ)引擎變成了MyISAM
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| a     | CREATE TABLE "a" (
  "id" int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

想讓單個(gè)的表是某種存儲(chǔ)引擎就直接改

mysql> alter table a engine=innodb;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table a;
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| a     | CREATE TABLE "a" (
  "id" int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
看了以上關(guān)于Mysql數(shù)據(jù)庫(kù)操作事務(wù)及存儲(chǔ)引擎講義，希望能給大家在實(shí)際運(yùn)用中帶來(lái)一定的幫助。本文由于篇幅有限，難免會(huì)有不足和需要補(bǔ)充的地方，如有需要更加專(zhuān)業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時(shí)售前售后，隨時(shí)幫您解答問(wèn)題的。

名稱(chēng)欄目：Mysql數(shù)據(jù)庫(kù)操作事務(wù)及存儲(chǔ)引擎講義
網(wǎng)頁(yè)URL：http://muchs.cn/article16/ihsgdg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制開(kāi)發(fā)、網(wǎng)站制作、搜索引擎優(yōu)化、域名注冊(cè)、動(dòng)態(tài)網(wǎng)站、外貿(mào)建站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)