🥰🥰🥰來都來了，不妨點個關注叭！
👉博客主頁：歡迎各位大佬!👈

本期內容講解 MySQL 中的索引和事務，在學習的過程中，我們需要經常問自己為什么

1. 索引

1.1 索引的概念

【概念】索引是一種特殊的文件，包含著對數據表里所有記錄的引用指針，可以對表中的一列或多列創建索引，并指定索引的類型，各類索引有各自的數據結構實現 ~

1.2 索引的目的

【目的】就比如說，本期文章帶有目錄（筆者每篇文章都帶有目錄）~ 通過目錄，我們就對這期內容有一個大致的了解，并且通過目錄，我們可以快速的定位我們想要的內容，而索引的作用和目錄的作用類似，都是加快查詢速度~

1.2.1 為什么使用索引會加快查詢速度？

首先我們知道，MySQL 數據庫文件存儲在磁盤上的，磁盤 I/O 是數據庫操作比較耗時的一部分

• 無索引，數據庫會進行全表掃描，它必須讀取表中的每一行數據來查找匹配的行，時間效率為 O(n)，當表的數據量非常大時，就會導致大量的磁盤 I/O 操作，非常耗時間！
• 有索引，可以直接跳到索引指示的數據位置，不用掃描整張表，大大減少了磁盤 I/O 操作的次數，減少時間，如 MySQL 的默認存儲引擎 InnoDB 默認使用 B+ 樹來作為索引的數據結構 ，而 B+ 樹的查詢效率非常高，時間復雜度是O(logN)！（本期內容后面將會具體介紹 B+ 樹）

我們知道索引是一種特殊的文件，MySQL 索引文件主要存儲索引，數據庫文件存儲的數據不僅包括全部數據，還包括索引等信息，索引文件與數據庫文件相比，體積小很多，通過查詢索引，再映射到數據庫記錄，查詢效率就會高很多~

【舉例】
還是結合目錄來看，通過目錄可以快速定位到哪一個位置，比如查字典，可以通過目錄，快速找到需要的內容在哪一頁，沒有目錄的話，我需要從字典的第一頁，每一頁一頁的翻，找到想要的內容，想想是不是很耗時呢？索引的作用也是如此！加快查詢的作用！

1.3 索引的使用

【情況一】對于創建主鍵約束，唯一約束，外鍵約束時，會自動創建對應列的索引

1. 首先創建一個 student 表，以 id 為主鍵（此時創建了主鍵約束，會自動創建對應列的索引）

 create table student (id int primary key,username varchar(50));

2. 查看索引

 show index from 表名;

從下圖中可以看到，主鍵 id，自動創建了索引

如果 id 沒有加主鍵約束，是不會自動創建索引的~ 如下圖：

【情況二】創建普通索引，對于非主鍵、非唯一約束、非外鍵的字段，可以創建普通索引

1. 創建索引

create index 索引名 on 表名(列名);

2. 刪除索引

drop index 索引名 on 表名;

（可以動手多操作哦，才會記憶深刻呀~更加利于理解哦！）

1.4 索引的分類

1.5 索引的優缺點

【優點】

• 加快查詢速度：通過索引可以快速定位到滿足查詢條件的數據行，減少數據的掃描范圍，從而提高查詢效率。例如，在一個包含大量用戶信息的表中，如果經常根據用戶姓名進行查詢，為姓名列創建索引后，查詢速度大大提升；
• 保證數據唯一性：可以通過創建唯一索引來確保表中某列或某些列組合的數據具有唯一性，防止出現重復數據，比如，在用戶表中，為身份證號碼列創建唯一索引，就可以保證每個用戶的身份證號碼是唯一的；
• 支持數據排序：索引可以按照指定的列進行排序，當查詢需要對結果進行排序時，使用索引可以避免數據庫在查詢時進行額外的排序操作，提高查詢性能。比如，在訂單表中，為訂單時間列創建索引，當按照訂單時間查詢并排序訂單時，數據庫可以直接使用索引來獲取有序的數據。

【缺點】

• 占用存儲空間：索引本身需要占用一定的磁盤空間來存儲索引結構，隨著數據量的增加和索引數量的增多，占用的空間也會相應增大，比如，一個大型數據表創建多個索引后，可能會使數據庫文件的大小增加很多；
• 增加維護成本：在數據插入、更新和刪除時，索引也需要進行相應的更新操作，這會增加數據庫的維護成本和時間開銷。例如，當向表中插入一條新記錄時，如果該表有多個索引，那么每個索引都需要進行更新以保證索引的準確性和一致性；
• 降低寫入性能：由于寫入數據時需要同時更新索引，所以會降低寫入操作的性能，比如，在批量插入數據時，沒有索引的表插入速度會比有索引的表快很多

1.6 MySQL 的默認存儲引擎 InnoDB 背后的數據結構 —— B+ 樹

在本期內容開頭就介紹了，數據庫索引的作用是為了加快查詢速度的~ 那么，我們會思考，其索引背后的數據結構是什么呢？ 并且能夠讓它加快查詢速度！

我們可以回想一下之前學過的數據結構，其中，二叉搜索樹和哈希表就是比較適合查詢的，但是很遺憾的是，它們兩個都不適合于數據庫索引的底層數據結構：

• 二叉搜索樹： 二叉搜索樹查找的時間復雜度是樹的高度，我們知道數據庫一般都是將數據存儲在硬盤上，數據量很大的時候，如果使用二叉搜索樹，則樹的高度也會很大，導致查詢效率會很慢，
• 哈希表：哈希表它是由數組和鏈表組成的，查找的時間復雜度為 O(1)，盡管時間復雜度低，但是，它的 key 不是有序的，并且對于數據庫中的大于、小于的范圍查找或者是 LIKE 類似的模糊查詢，哈希表都是不能夠做到的，比如，我們要查詢學生的序號在 202110120510 到 202110120534 之間的學生信息，哈希表是做不到的~

噔噔噔!!! 這里，就要介紹一種特殊的數據結構，B+樹 —— 專門為了數據庫索引量身定做的數據結構！

1.6.1 B樹

介紹 B+ 樹前，先介紹一下 B 樹，也叫做 B- 樹，注意!!! 這里的 ‘-’ 不是減號，只是連接符哦！ 很多小伙伴看到有 B+ 樹，可能會想當然的以為也有 B- 樹

B樹可以認為是一顆N叉搜索樹，結構如下：

我們可以通過圖看到，當節點的子樹多了，節點上保存的 key 就多了，在相同個數 key 的情況下，B 樹的高度就比二叉搜索樹的高度要低很多，對 IO 操作的次數就越少，這樣查找性能就會比較高！

1.6.2 B+樹

我們先來了解一下 B+ 樹的特點~

B+ 樹的特點：

• 一個節點可以存儲 N 個 key，N 個 key 可以劃分出 N 個子區間（不是 N+1 個）
• 每一個 key 都會在子區間出現，且為子區間的最大值
• B+ 樹的葉子節點是首尾相連的，類似于一個鏈表
• B+ 樹的非葉子節點只用于索引并不保存數據，只有葉子節點存儲著索引和數據

到底是一個什么樣的數據結構呢？ 我們一起來看看！

既然有 B 樹，那么為什么還要有 B+ 樹呢？

我們一起來分析一下 B 樹的優缺點：

• B樹優點：每個節點都存儲了索引和對應的數據，在查找離根節點近的節點時，查找效率是很高的，不用每次都查找到葉子節點（與B+樹相比，因為 B+ 樹必須得查找到葉子節點）
• B樹缺點
  不利于范圍查找，即不利于區間查找，比如要找 5 - 14 的索引值，那么 B 樹就需要多次從根節點逐個查找，而 B+ 樹的葉子節點是鏈表連接起來的，且是從小到大依次有序的，在 B+ 樹中，只用找到 5 索引 和 14 索引的葉子節點，從 5 沿著鏈表遍歷到 14 即可！

再來分析一下 B+ 樹的優點：

• 查詢任意一個節點最終都會落到葉子節點，每次 IO 訪問次數是一樣的
• B+ 樹的所有葉子節點構成了一個完整的鏈表，適合進行范圍查找
• 只有葉子節點是存儲著完整的數據，非葉子節點只記錄索引，這樣這些非葉子節點占用的內存就十分的小，又進一步減小 IO 操作次數

【補充知識】

1）回表

比如在有一些表中，有多個索引，如上述創建的 student 表里，我們在 username 這一列創建了一個索引，此時，表的數據還是會根據主鍵id 構建出 B+ 樹，通過葉子節點將數據組織起來，其次，會根據 username 這個列創建一個B+樹，但這個 B+ 樹的葉子節點只存儲主鍵id是什么，此時如果你是通過 username 這個索引來查找的，會通過葉子節點拿到的 id 索引再去由 id 創建的B+樹里再查一次，即查兩次B+樹，上述過程，在數據庫中就叫回表！

上述過程是 Mysql 自動完成的，用戶是完全感知不到的，這是因為主鍵索引也是聚簇索引，葉子節點存儲整個數據和索引，而其他索引就是非聚簇索引，葉子節點只存儲主鍵和索引，因此，使用非聚簇索引進行查詢的時候需要再拿著這個查詢到的主鍵再在聚簇索引中進行一次查詢，即回表

2）聚簇索引和非聚簇索引

在 MySQL 的 默認 InnoDB 引擎中，每個索引都會對應一顆 B+ 樹，而聚簇索引和非聚簇索引最大的區別在于葉子節點存儲的數據不同，聚簇索引葉子節點存儲的是行數據，因此通過聚簇索引可以直接找到真正的行數據；而非聚簇索引葉子節點存儲的是主鍵信息，所以使用非聚簇索引還需要回表查詢，因此，我們可以得出聚簇索引和非聚簇索引的區別主要有以下幾個：

• 存儲內容：聚簇索引葉子節點存儲的是行數據；而非聚簇索引葉子節點存儲的是聚簇索引，通常是主鍵 ID；
• 效率：聚簇索引查詢效率更高，而非聚簇索引需要進行回表查詢，因此性能不如聚簇索引；
• 數量上：聚簇索引一般為主鍵索引，而主鍵一個表中只能有一個，因此聚簇索引一個表中也只能有一個，而非聚簇索引則沒有數量上的限制

2. 事務

2.1 事務的概念

我們先來想一下這個場景，七夕節到了，小丁準備給他的女朋友小萬轉賬 1314，那么 sql 語句應該如下：

update account set  money = money - 1314 where name = ‘小丁’;
update account set  money = money + 1314 where name = ‘小萬’;

假設在執行完第一條語句后，數據庫突然崩潰了或者主機宕機了，此時就出現了問題，小丁的錢扣了，但是小萬沒有收到轉賬，小萬就會很生氣，覺得沒轉就沒轉，而不能這樣騙她！小丁就百口莫辯了，明明自己轉了呀！現實生活中，有很多這樣的支付場景，那么如何解決呢？

事務則是解決上述問題的，事務指的是邏輯上一組操作，組成這組操作的各個單元，要么全部成功，要么全部失敗，在不同環境中，都可以有事務，對應在數據庫中，就是數據庫事務，通俗的來講，就是把 sql 語句打包在一起，要么全部都執行成功，要么全部都不執行，不會出現執行到一半的情況，注意，這里的全部都不執行，并不是真的沒執行，而是執行到一半，出現問題后，選擇恢復到原來的樣子，將數據灰度到未執行的狀態，這個恢復操作，就叫做 “回滾”（rollback）

2.2 事務的使用

1. 開啟事務：start transaction;
2. 中間放要執行的 sql 語句
3. 回滾或提交: rollback/commit;
   （說明： rollback表示“全部失敗”，commit表示“全部成功”）

具體代碼如下：

start transaction; //開啟事務
update account set money = money - 1314 where name = '小丁';
update account set money  =money + 1314 where name = '小萬';
commit;   //提交事務

2.3 事務的四大特性 —— ACID

2.3.1 原子性

事務的初心就是原子性，事務是一個不可分割的單位，數據庫事務里的 sql 語句要么全部執行，要么全部不執行，不會出現執行一半的情況，如果執行一半出現問題就會進行 “回滾” 操作（強調事務是一個整體）

2.3.2 一致性

事務執行前后，數據都是一個合法的狀態，就像上面的轉賬情況，在轉賬前和轉賬后數據都應該是一個合法的狀態，是一致的（強調數據的合法狀態）

2.3.3 持久性

事務修改的內容是寫入硬盤的，持久存在，重啟也不會消失（強調事務修改內容都是寫入硬盤的，具有持久性）

2.3.4 隔離性

多個事務并發執行的時候，每個事務應該感受不到其他事務的存在，各個事務是隔離的（強調多個事務并發執行，各事務是隔離的，相互不影響）

但是，不同的事務隔離級別會導致不同的并發問題，如臟讀，不可重復讀，幻讀，下面將具體介紹這三個問題：

1） 臟讀

【概念】臟讀就是一個事務讀取到了另一個未提交事務修改的數據

【舉例】事務A 對數據庫中的小萬余額進行讀取，余額為 100，接著再執行更新操作，將余額更新為 500，此時，并沒有提交事務，而另一邊，事務B 對數據庫中的小萬余額進行讀取，那么事務B就讀取到了事務A更新的金額，余額為 500，但是此時事務A并沒有提交事務！隨時可能發生回滾操作，如果上述情況事務發生回滾，那么余額為 100，而事務B讀取到了事務A的過期數據500，這個就是臟讀

【解決方式】給事務A修改數據這個操作進行寫加鎖，當事務A修改數據的時候，其它事務不能對該數據進行讀取，這樣就意味著此時寫操作和讀操作不能并發執行了，降低了并發程度，即降低了小效率，但是提高了隔離性，即提高了數據的準確性

2）不可重復讀

【概念】不可重復讀就是一個事務多次重復讀取一個數據，如果出現了前后兩次讀取的數據不一致的情況

【舉例】一個事務A對小萬的余額進行讀取，此時有一個事務B對小萬的余額進行更新并提交事務，這樣，事務A對小萬的余額再進行讀取的時候，就會發現，前后兩次讀取的數據不一樣，這就是不可重復讀

【解決方式】給事務A對數據進行讀取的時候進行讀加鎖，當事務A對數據進行讀取操作的時候，其它事務不能對該數據進行修改，此時，又降低了并發程度，提高了隔離性

3）幻讀

【概念】幻讀就是在一個事務內多次查詢符合條件的記錄數量，出現了前后查詢數量不一致的情況

【舉例】事務A在數據庫中查詢余額大于500的用戶，此時有十條記錄，但是事務B此時插入了一條余額大于500的用戶，并提交事務，這個時候事務A再進行查詢余額大于500的用戶，就查詢到十一條記錄，這個就是幻讀

【解決方式】數據庫使用 “串行化” 的操作來解決幻讀的問題，就是徹底放棄事務并發，一個接一個的串行執行事務，此時，并發程度最低，效率最低，隔離性最高，數據準確性最高！

數據庫提供的四個隔離級別：

• read uncommitted（讀未提交）
  沒有任何鎖限制，并發程度最高，隔離性最低，會發生上述三種問題
• read committed（讀已提交）
  給寫加鎖，并發程度降低，隔離性增加，但會產生“不可重復讀”和“幻讀”問題
• repeatable read（可重復讀）（MySQL的默認隔離級別）
  給讀寫都加鎖，并發程度進一步降低，隔離性進一步增加，但可能會產生“幻讀”問題
• serializable（串行化）
  徹底實行串行化，并發程度最低，隔離性最高

💛💛💛本期內容回顧💛💛💛

???本期內容到此結束啦~