MySql索引和事務定義到使用全面涵蓋

2022-07-28 22:06:53

索引是什麼

索引是一種特殊的檔案，包含著對資料表裡所有記錄的參照指標。可以對錶中的一列或多列建立索引，並指定索引的型別,各類索引有各自的資料結構實現。索引就相當於一本書的目錄,通過索引可快速定位、檢索資料。使用索引大大提高了查詢效率,但同時索引也佔用了更多的空間,拖慢了增刪改的速度。

索引的使用場景

1、資料量較大，且經常對這些列進行條件查詢。

2、該資料庫表的插入操作，及對這些列的修改操作頻率較低。

3、索引會佔用額外的磁碟空間。

滿足以上條件時,考慮對錶中的這些欄位建立索引，以提高查詢效率。

反之,如果非條件查詢列，或經常做插入、修改操作,或磁碟空間不足時，不考慮建立索引。

索引的常見操作

1、建立索引對於非主鍵、非唯一約束、非外來鍵的欄位，可以建立普通索引。

create index 索引名 on 表名(欄位名);

2、檢視索引

show index from 表名;

3、刪除索引

drop index 索引名 on 表名;

索引背後的資料結構

索引背後的資料結構是B+樹。

B+樹優勢：

1、使用B+樹進行查詢的時候整體的磁碟IO次數是比較少的。

2、所有的查詢最終都會落到葉子節點上，每次查詢的磁碟IO次數都是差不多的，查詢速度比較穩定。

3、葉子結點用連結串列連線之後,非常適合進行範圍查詢。

4、所有的資料儲存(載荷)都是放到葉子節點上的,非葉子節點中只儲存key值即可,因此非葉子節點整體佔用的空間較小,甚至可以快取到記憶體中（一旦能夠全部放記憶體裡，這時候磁碟IO幾乎沒了）。

事務是什麼

事務指邏輯上的一組操作，組成這組操作的各個單元，要麼全部成功，要麼全部失敗。在不同的環境中，都可以有事務。對應在資料庫中，就是資料庫事務。就是說兩個操作要麼都執行，要麼一個都不執行。就比如說現在有A、B兩個賬戶，兩個賬戶上都有1000元，現在A要給B轉500元，那麼A賬戶上就要少500元，B賬戶上要多500，兩個操作都要執行。不能存在一個執行一個不執行，要麼都不執行。在sql中，如果第一個sql執行之後，第二個sql執行時出現意外，導致不能執行，這時候資料庫就會還原操作，讓第一個sql還原到執行之前（回滾）。那麼資料庫是怎麼還原的呢？是因為資料庫會將執行過的每個操作都記錄下來。

⭐ 事務的相關操作

1、開啟事務

start transaction;

2、回滾或提交

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

事務的基本特性

1、原子性

原子性是指兩個操作要麼全部都執行，要麼一個都不執行，操作不可再細分了。

2、一致性

一致性是指資料庫的資料在事務執行之前和之後都要是合理合法的。（例如上面的轉賬，賬戶不能出現負數的情況）

3、永續性

永續性是指事務一旦被提交之後，資料就被寫入硬碟，持久化的儲存起來了。

4、隔離性

隔離性是指事務並行執行時產生的情況。

事務並行執行是指多個事務同時執行，多個事務並行執行時，尤其時多個事務在嘗試修改、讀取同一份資料時，很容易出現一些問題，隔離性就是在處理這些問題。

⭐並行執行事務可能帶來的問題：

1、髒讀問題

事務A正在輸入某些資料，這是事務B過來讀取了事務A正在輸入的資料，然後B回去按照A的資料進行輸入，若事務A在事務B讀取資料之後對資料進行了修改，這時候事務B讀到的資料就有可能是一個"髒資料"（這個資料只是一個臨時的的結果，而不是最終結果），這就是髒讀問題。

如何處理髒讀問題？

在寫資料的時候進行加鎖處理，等A完全寫完修改完之後再讓B看，這時候事務之間的隔離性就提高了，但同時並行性也降低了。

2、不可重複讀問題

按照以上處理，等到A全部寫完B再看，但是如果A在B讀的時候又修改了程式碼，這時候B再回過頭來看，資料又不一樣了，不能重複讀這個資料，這怎麼辦？這時候乾脆就將讀也加上鎖，在A寫入資料的時候B不能讀，在B讀資料的時候A也不能修改。這時候事務的隔離性又提高了，並行性又降低了。

3、幻讀問題

以上兩個操作，給讀和寫都加上了鎖，雖然它的隔離性提高了，但是卻降低了效率，導致讀和寫的時間都變得更長了起來，這時候在寫或者讀的過程事務可以去改其他表或者這個表的其他行（事務雖然在提交隔離性時進行了一系列的加鎖，但是並沒有把整個資料庫給鎖定了），這時候B在讀資料的時候會發現資料數量變了，本來只有一個A.java,現在又多了個B.java，這就是幻讀問題。總的來說就是一個事務執行過程中進行多次查詢，每次查詢的結果集不一樣（多或者少），這也是一種特殊的不可重複讀問題。

該如何解決呢？

事務序列化執行。（序列化執行就是事務一個一個執行）

通過以上我們也知道了隔離性和並行性不能同時得到，要想提高隔離性，並行性就得降低。

進行了以上修改，這時候隔離性程度最高，並行性程度最低，資料最可靠，速度也最慢。

⭐根據以上總結，MySQL中事務的隔離級別，提供了以下這幾種:

1. read uncommitted：允許讀取未提交的資料，並行程度最高,隔離程度最低，會引入髒讀、不可重複讀、幻讀問題。

2. read committed：只允許讀取提交之後的資料，相當於寫加鎖，並行程度降低了一些，隔離程度提高了一些，解決了髒讀,會引入不可重複讀和幻讀。

3. repeatable read：相當於給讀和寫都加鎖，並行程度又降低了，隔離程度又提高了，解決了髒讀和不可重複度，會引入幻讀。

4. serializable：序列化，並行程度最低(序列執行)，隔離程度最高，解決了髒讀，不可重複度，幻讀問題，但是執行速度最慢。