MySQL索引與事務定義到使用詳解

1.索引的本質

索引的本質就相當於"書的目錄",通過目錄就能快速定位到我們需要的某個章節的位置

索引的主要作用就是為了加快查詢的速度

在資料庫操作中,查詢的頻率是非常高的,使用索引可以幫助我們快速查詢到所需要的資訊

缺點

1.資料庫索引提高查詢速度的同時也增加了增加刪除修改操作的開銷,進行增刪改操作之後,調整資料之後還要修改索引,因此增加了其他開銷,但是這是次要矛盾,主要矛盾是查詢的速度,相比之下還是很值得的

2.不僅如此,索引還提高了空間的開銷,構造索引需要額外的硬碟空間來儲存

雖然有這些缺點,但是他能解決我們的主要矛盾,在軟體開發中會經常遇到這樣的問題.一般的都沒有那個方法能解決所有問題,需要進行取捨,解決主要矛盾

2.索引的使用

2.1檢視索引

mysql> show index from student3;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student3 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

如果表裡有主鍵,主鍵這列就會自動建立索引

還有unique,foreign key 的列也會自動建立索引

2.2建立索引

mysql> create index  index_name on student3(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show index from student3;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student3 |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student3 |          1 | index_name |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

此時就有兩個索引,針對name新加了一個索引

在建立索引的時候,最好是在表建立的時候就把索引建立好,否則,如果這個表的記錄十分多了,再建立索引,就很危險了!!是因為此時建立索引會花很長的時間,佔用了大量的的磁碟IO,此時是無法對資料庫進行存取的的,也無法正常使用,那帶來的損失就太大了

2.3刪除索引

mysql> drop index index_name on student3;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from student3;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student3 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

此時只剩一個索引了,和剛剛建立索引相似的是,刪除索引也會有較大的開銷,所以在建立表的時候我們就要規劃好索引,一旦表裡有大量的資料了,再進行操作就需要慎重考慮了!!

那麼建立好了索引,是怎麼使用索引的呢?

建立好索引之後,是不需要手動的呼叫的,SQL是通過資料庫的執行引擎來執行的,涉及到一些優化操作,執行引擎會自動評估哪種方案成本最低速度最快,可以使用explain關鍵字顯示出查詢過程中索引的具體使用情況,結果分析還是比較複雜的

3.索引的資料結構

MySQL中索引的資料結構是什麼呢?

索引既然能極大提高搜尋的效率,我們肯定能先想到的資料結構就是雜湊表,雜湊表的查詢時間複雜度是O(1),但是雜湊表不適合做資料庫的索引,原因在於雜湊表只能比較相等,無法進行範圍查詢,像<>這樣的操作都不行

3.1B樹

其次,二元搜尋樹查詢元素的時間複雜度是O(N),相比於雜湊表,二元搜尋樹好像可以進行範圍查詢了,但是還存在一個問題,當元素數太多時,樹的高度就會比較高,而數的高度又決定了樹查詢的時候比較的次數,資料庫比較的時候需要讀取硬碟,因此更希望書的高度能降低一點,那麼就考慮使用N叉搜尋樹了

N叉搜尋樹,每個節點有很多個值,同時有很多的分叉,降低了樹的高度,減少了比較的次數

一種典型的實現N叉搜尋樹的方式就是B樹

我們看一下B樹的結構

這種結構降低了樹的高度,沒有減少比較次數(但是在一個節點上比較多次了),減少了對硬碟的讀寫次數,節點都是儲存在硬碟上的,能一定程度的解決問題,適合做索引

3.2B+樹

還有種更適合做索引的資料結構,就是B+樹

B+樹的特點:

1.B+樹也是一個N叉樹,增加了新的特點,每個節點上包含N個Key,N個Key劃分出N個區間,每個區間的最後一個key就是最大值

2.父元素的Key會在子元素中出現並且為最大值,重複出現導致了,葉子節點就包含了所有資料的全集!

那麼非葉子結點的所有元素都在葉子節點中體現

3.葉子節點用類似於連結串列的形式相連起來,構成了B+樹

B+樹這個資料結構做索引好處太明顯了

1.既有B樹高度比較低的特點,又更適合範圍查詢,比如查詢>6且<15的元素,結果集非常容易取得,效率很高

2.對於所有的查詢,都要落在葉子節點上,中間的比較次數是差不多的,查詢操作比較均衡

對B樹來說,在根節點或者深度不深的元素查詢快,別的地方查詢慢,不均衡,B+樹都是一樣的,都落在葉子節點上了

3.由於所有的Key都會在葉子節點中出現,因此非葉子節點不用存表的真實記錄,只要把說有的資料行放在葉子節點上即可,非葉子節點只用存索引列的值,比如id這些,非葉子節點佔用的空間就很小了,有可能在記憶體中放進去快取了,更進一步降低了硬碟IO,提高了查詢的速度

綜上,B+樹是非常適合作為索引的資料結構的

有的表不只是有主鍵索引,還有別的非主鍵列也有索引,此時會構造另一個B+樹,非葉子節點裡面儲存這一列的Key,到了葉子節點這一層不再儲存完整的資料行了,而是儲存主鍵索引的id,那麼使用主鍵索引查詢時只用查一次B+樹就好了,使用非主鍵列索引要先查一遍另外構造的B+樹,然後查一次主鍵列的B+樹(這個操作稱為回表操作)

當前B+樹這個結構適用於MySQL的InnoDB這個資料引擎,不同的資料庫,不同的引擎儲存資料的資料結構還是有差異的

4.事務

事務指邏輯上的一組操作，組成這組操作的各個單元，要麼全部成功，要麼全部失敗。在不同的環境中，都可以有事務。對應在資料庫中，就是資料庫事務。

4.1事物的回滾(rollback)

當一個事務在執行時,執行中間出錯了,就讓它恢復成原來的樣子

涉及到的操作就是回滾,具體實現是把執行過的操作逆向恢復回去

資料庫會把執行的每個操作都記錄下來,如果某個操作出錯了,就會把事務中之前的操作進行回滾,根據之前的操作,進行逆操作(前面插入回滾就是刪除之前插入的)

有了這個操作,那麼刪表刪庫是不是就不危險了呢?反正可以回滾麼,事實當然不是這樣的,回滾的操作是有很大開銷的,可以保執行的操作,但也不能無限儲存,最多就是儲存正在執行的事務,當資料量特別大時,更不可能儲存每個資料如何得到,因此刪表刪庫仍然是很危險的操作!!

4.2事務的四大特性(ACID)

事務的四大特性主要是：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、永續性（Durability）

4.2.1 原子性

原子性是指事務是一個不可分割的工作單位，事務中的操作要麼全部成功，要麼全部失敗。比如在同一個事務中的SQL語句，要麼全部執行成功，要麼全部執行失敗

4.2.2 一致性

事務必須使資料庫從一個一致性狀態變換到另外一個一致性狀態,事物的執行前後資料是合法的

比如銀行轉賬時,A給B轉了100,A轉出100,B卻沒有收到100,這時就出現了資料不合法,沒有達到一致性

4.2.3 永續性

永續性是指一個事務一旦被提交，它對資料庫中資料的改變就是永久性的，接下來即使資料庫發生故障也不應該對其有任何影響,保證事務對資料庫的改變是生效的

4.2.4 隔離性

一個資料庫伺服器同時執行多個事務的時候,事物之間的相互影響的程度

隔離性越高,事務之間並行程度越低,執行效率慢,但是資料準確性高,像銀行轉賬.....

隔離性越低,事務之間並行程度越高,執行效率快,但是資料準確性低,像點贊數.....

5.並行引起的問題

5.1 "讀髒資料"

當一個事務修改某個資料後，另一事務對該資料進行了讀取，由於某種原因前一事務復原了對資料的修改（即將修改過的資料恢復原值），那麼後一事務讀到的資料與資料庫中的資料不一致，這稱之為讀髒資料

為了解決這個問題,要降低並行性,提高隔離性,具體操作就是給''寫操作''加鎖,寫的時候不能被讀取,降低了一定的效率,但是提高了資料的準確性

5.2 "不可重複讀"

當一個事務讀取某個資料後，另一事務執行了對該資料的更新，當前事務再次讀取該資料（希望與第一次讀取的是相同的值）時，得到的資料與前一次的不一樣，這是由於第一次讀取資料後，事務B對其做了修改，導致再次讀取資料時與第一次讀取的資料不相同

這次給''讀操作''加鎖,讀的時候資料不能被修改,並行程度進一步降低,隔離性進一步增加,執行速度變慢,資料準確性進一步提高了

5.3 "幻讀"

事務A 按照一定條件進行資料讀取， 期間事務B 插入了相同搜尋條件的新資料，事務A再次按照原先條件進行讀取時，發現了事務B 新插入的資料 稱為幻讀

為了解決這個問題,需要徹底捨棄並行,進行序列化操作,在讀的時候不進行其他的操作

6.MySQL的隔離級別

MySQl為了控制並行程度的高低,引入了四個隔離級別,通過修改組態檔就可以改變隔離級別

6.1 read uncommitted

不做任何處理,事務間隨意並行,當然上面的三個問題都存在,隔離性最低,並行程度最高

6.2 read committed

對寫操作加鎖,解決了讀髒資料問題,還存在另外兩個問題

6.3 repeatable read

對讀寫操作加鎖,解決了讀髒資料問題,不可重複讀問題

還存在幻讀問題

6.4 serializable

嚴格序列化,解決了三個由並行引起的問題,並行程度最低,隔離性是最高的

看這張圖比較直觀

到此這篇關於MySQL索引與事務定義到使用詳解的文章就介紹到這了,更多相關MySQL索引與事務內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！