快捷導(dǎo)航

MySql索引和事務(wù)定義到使用全面涵蓋

更新時(shí)間：2022年07月28日 14:54:47 作者：菜菜不恰菜

這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)索引事務(wù)，索引是為了加速對(duì)表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散的存儲(chǔ)結(jié)；事物是屬于計(jì)算機(jī)中一個(gè)很廣泛的概念，一般是指要做的或所做的事情，下面我們就一起進(jìn)入文章了解具體內(nèi)容吧

索引是什么

索引是一種特殊的文件，包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針?？梢詫?duì)表中的一列或多列創(chuàng)建索引，并指定索引的類型,各類索引有各自的數(shù)據(jù)結(jié)構(gòu)實(shí)現(xiàn)。索引就相當(dāng)于一本書的目錄,通過(guò)索引可快速定位、檢索數(shù)據(jù)。使用索引大大提高了查找效率,但同時(shí)索引也占用了更多的空間,拖慢了增刪改的速度。

索引的使用場(chǎng)景

1、數(shù)據(jù)量較大，且經(jīng)常對(duì)這些列進(jìn)行條件查詢。

2、該數(shù)據(jù)庫(kù)表的插入操作，及對(duì)這些列的修改操作頻率較低。

3、索引會(huì)占用額外的磁盤空間。

滿足以上條件時(shí),考慮對(duì)表中的這些字段創(chuàng)建索引，以提高查詢效率。

反之,如果非條件查詢列，或經(jīng)常做插入、修改操作,或磁盤空間不足時(shí)，不考慮創(chuàng)建索引。

索引的常見操作

1、創(chuàng)建索引對(duì)于非主鍵、非唯一約束、非外鍵的字段，可以創(chuàng)建普通索引。

create index 索引名 on 表名(字段名);

2、查看索引

show index from 表名;

3、刪除索引

drop index 索引名 on 表名;

索引背后的數(shù)據(jù)結(jié)構(gòu)

索引背后的數(shù)據(jù)結(jié)構(gòu)是B+樹。

B+樹優(yōu)勢(shì)：

1、使用B+樹進(jìn)行查找的時(shí)候整體的磁盤IO次數(shù)是比較少的。

2、所有的查詢最終都會(huì)落到葉子節(jié)點(diǎn)上，每次查詢的磁盤IO次數(shù)都是差不多的，查詢速度比較穩(wěn)定。

3、葉子結(jié)點(diǎn)用鏈表連接之后,非常適合進(jìn)行范圍查找。

4、所有的數(shù)據(jù)存儲(chǔ)(載荷)都是放到葉子節(jié)點(diǎn)上的,非葉子節(jié)點(diǎn)中只保存key值即可,因此非葉子節(jié)點(diǎn)整體占用的空間較小,甚至可以緩存到內(nèi)存中（一旦能夠全部放內(nèi)存里，這時(shí)候磁盤IO幾乎沒了）。

事務(wù)是什么

事務(wù)指邏輯上的一組操作，組成這組操作的各個(gè)單元，要么全部成功，要么全部失敗。在不同的環(huán)境中，都可以有事務(wù)。對(duì)應(yīng)在數(shù)據(jù)庫(kù)中，就是數(shù)據(jù)庫(kù)事務(wù)。就是說(shuō)兩個(gè)操作要么都執(zhí)行，要么一個(gè)都不執(zhí)行。就比如說(shuō)現(xiàn)在有A、B兩個(gè)賬戶，兩個(gè)賬戶上都有1000元，現(xiàn)在A要給B轉(zhuǎn)500元，那么A賬戶上就要少500元，B賬戶上要多500，兩個(gè)操作都要執(zhí)行。不能存在一個(gè)執(zhí)行一個(gè)不執(zhí)行，要么都不執(zhí)行。在sql中，如果第一個(gè)sql執(zhí)行之后，第二個(gè)sql執(zhí)行時(shí)出現(xiàn)意外，導(dǎo)致不能執(zhí)行，這時(shí)候數(shù)據(jù)庫(kù)就會(huì)還原操作，讓第一個(gè)sql還原到執(zhí)行之前（回滾）。那么數(shù)據(jù)庫(kù)是怎么還原的呢？是因?yàn)閿?shù)據(jù)庫(kù)會(huì)將執(zhí)行過(guò)的每個(gè)操作都記錄下來(lái)。

? 事務(wù)的相關(guān)操作

1、開啟事務(wù)

start transaction;

2、回滾或提交

rollback/commit;
//rollback即是全部失敗，commit即是全部成功。

事務(wù)的基本特性

1、原子性

原子性是指兩個(gè)操作要么全部都執(zhí)行，要么一個(gè)都不執(zhí)行，操作不可再細(xì)分了。

2、一致性

一致性是指數(shù)據(jù)庫(kù)的數(shù)據(jù)在事務(wù)執(zhí)行之前和之后都要是合理合法的。（例如上面的轉(zhuǎn)賬，賬戶不能出現(xiàn)負(fù)數(shù)的情況）

3、持久性

持久性是指事務(wù)一旦被提交之后，數(shù)據(jù)就被寫入硬盤，持久化的存儲(chǔ)起來(lái)了。

4、隔離性

隔離性是指事務(wù)并發(fā)執(zhí)行時(shí)產(chǎn)生的情況。

事務(wù)并發(fā)執(zhí)行是指多個(gè)事務(wù)同時(shí)執(zhí)行，多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)，尤其時(shí)多個(gè)事務(wù)在嘗試修改、讀取同一份數(shù)據(jù)時(shí)，很容易出現(xiàn)一些問(wèn)題，隔離性就是在處理這些問(wèn)題。

?并發(fā)執(zhí)行事務(wù)可能帶來(lái)的問(wèn)題：

1、臟讀問(wèn)題

事務(wù)A正在輸入某些數(shù)據(jù)，這是事務(wù)B過(guò)來(lái)讀取了事務(wù)A正在輸入的數(shù)據(jù)，然后B回去按照A的數(shù)據(jù)進(jìn)行輸入，若事務(wù)A在事務(wù)B讀取數(shù)據(jù)之后對(duì)數(shù)據(jù)進(jìn)行了修改，這時(shí)候事務(wù)B讀到的數(shù)據(jù)就有可能是一個(gè)"臟數(shù)據(jù)"（這個(gè)數(shù)據(jù)只是一個(gè)臨時(shí)的的結(jié)果，而不是最終結(jié)果），這就是臟讀問(wèn)題。

如何處理臟讀問(wèn)題？

在寫數(shù)據(jù)的時(shí)候進(jìn)行加鎖處理，等A完全寫完修改完之后再讓B看，這時(shí)候事務(wù)之間的隔離性就提高了，但同時(shí)并發(fā)性也降低了。

2、不可重復(fù)讀問(wèn)題

按照以上處理，等到A全部寫完B再看，但是如果A在B讀的時(shí)候又修改了代碼，這時(shí)候B再回過(guò)頭來(lái)看，數(shù)據(jù)又不一樣了，不能重復(fù)讀這個(gè)數(shù)據(jù)，這怎么辦？這時(shí)候干脆就將讀也加上鎖，在A寫入數(shù)據(jù)的時(shí)候B不能讀，在B讀數(shù)據(jù)的時(shí)候A也不能修改。這時(shí)候事務(wù)的隔離性又提高了，并發(fā)性又降低了。

3、幻讀問(wèn)題

以上兩個(gè)操作，給讀和寫都加上了鎖，雖然它的隔離性提高了，但是卻降低了效率，導(dǎo)致讀和寫的時(shí)間都變得更長(zhǎng)了起來(lái)，這時(shí)候在寫或者讀的過(guò)程事務(wù)可以去改其他表或者這個(gè)表的其他行（事務(wù)雖然在提交隔離性時(shí)進(jìn)行了一系列的加鎖，但是并沒有把整個(gè)數(shù)據(jù)庫(kù)給鎖定了），這時(shí)候B在讀數(shù)據(jù)的時(shí)候會(huì)發(fā)現(xiàn)數(shù)據(jù)數(shù)量變了，本來(lái)只有一個(gè)A.java,現(xiàn)在又多了個(gè)B.java，這就是幻讀問(wèn)題。總的來(lái)說(shuō)就是一個(gè)事務(wù)執(zhí)行過(guò)程中進(jìn)行多次查詢，每次查詢的結(jié)果集不一樣（多或者少），這也是一種特殊的不可重復(fù)讀問(wèn)題。

該如何解決呢？

事務(wù)串行化執(zhí)行。（串行化執(zhí)行就是事務(wù)一個(gè)一個(gè)執(zhí)行）

通過(guò)以上我們也知道了隔離性和并發(fā)性不能同時(shí)得到，要想提高隔離性，并發(fā)性就得降低。

進(jìn)行了以上修改，這時(shí)候隔離性程度最高，并發(fā)性程度最低，數(shù)據(jù)最可靠，速度也最慢。

?根據(jù)以上總結(jié)，MySQL中事務(wù)的隔離級(jí)別，提供了以下這幾種:

1. read uncommitted：允許讀取未提交的數(shù)據(jù)，并發(fā)程度最高,隔離程度最低，會(huì)引入臟讀、不可重復(fù)讀、幻讀問(wèn)題。

2. read committed：只允許讀取提交之后的數(shù)據(jù)，相當(dāng)于寫加鎖，并發(fā)程度降低了一些，隔離程度提高了一些，解決了臟讀,會(huì)引入不可重復(fù)讀和幻讀。

3. repeatable read：相當(dāng)于給讀和寫都加鎖，并發(fā)程度又降低了，隔離程度又提高了，解決了臟讀和不可重復(fù)度，會(huì)引入幻讀。

4. serializable：串行化，并發(fā)程度最低(串行執(zhí)行)，隔離程度最高，解決了臟讀，不可重復(fù)度，幻讀問(wèn)題，但是執(zhí)行速度最慢。