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

• 1.索引的本質
• 2.索引的使用
  • 2.1查看索引
  • 2.2創建索引
  • 2.3刪除索引
• 3.索引的數據結構
  • 3.1B樹
  • 3.2B+樹
• 4.事務
  • 4.1事物的回滾(rollback)
  • 4.2事務的四大特性(ACID)
    • 4.2.1 原子性
    • 4.2.2 一致性
    • 4.2.3 持久性
    • 4.2.4 隔離性
• 5.并發引起的問題
  • 5.1 "讀臟數據"
  • 5.2 "不可重復讀"
  • 5.3 "幻讀"
• 6.MySQL的隔離級別
  • 6.1 read uncommitted
  • 6.2 read committed
  • 6.3 repeatable read
  • 6.4 serializable

1.索引的本質

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

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

在數據庫操作中,查詢的頻率是非常高的,使用索引可以幫助我們快速查找到所需要的信息

缺點

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

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

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

2.索引的使用

2.1查看索引

1 2 3 4 5 6 7

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創建索引

1 2 3 4 5 6 7 8 9 10 11 12

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刪除索引

1 2 3 4 5 6 7 8 9 10

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索引與事務定義到使用詳解的文章就介紹到這了