5.2 "不可重復讀"
5.3 "幻讀"
6.MySQL的隔離級別
6.1 read uncommitted
6.2 read" /> 久久久AV天堂,亚洲 欧美 综合 在线 天堂,亚洲AV无码一区二区三区天堂_知乎
<th id="dw2i4"><video id="dw2i4"><acronym id="dw2i4"></acronym></video></th><object id="dw2i4"></object>

系統城裝機大師 - 固鎮縣祥瑞電腦科技銷售部宣傳站!

當前位置:首頁 > 數據庫 > Mysql > 詳細頁面

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

時間:2022-12-06來源:www.caymanbraccottage.com作者:電腦系統城

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

分享到:

相關信息

  • 安裝MYSQL端口被占用

    很多使用電腦的小伙伴安裝MYSQL端口被占用的情況,這個時候我們該如何解決呢?今天小編帶來了詳細的解決方法,主要在命令窗口里進行設置就可以了,具體的教程一起來看看吧。...

    2022-10-24

  • 一文搞懂MySQL運行機制原理

    MySQL服務器體系架構 網絡連接層 服務層 存儲引擎層 系統文件層 服務器處理客戶端請求 連接管理 解析與優化 查詢緩存 語法解析 查詢優化 存儲引擎...

    2022-10-02

系統教程欄目

欄目熱門教程

人氣教程排行

站長推薦

熱門系統下載

久久久AV天堂,亚洲 欧美 综合 在线 天堂,亚洲AV无码一区二区三区天堂_知乎