本節(jié)目標

• 索引
• 事務

🛫索引

🎍索引的概念

索引是一種特殊的文件，包含著對數(shù)據(jù)表里所有記錄的引用指針?？梢詫Ρ碇械?mark>一列或多列創(chuàng)建索引，并指定索引的類型，各類索引有各自的數(shù)據(jù)結構實現(xiàn)

🌳索引的作用

MySQL 索引是一種數(shù)據(jù)結構，用于加快數(shù)據(jù)庫查詢的速度和性能。

MySQL 索引的建立對于 MySQL 的高效運行是很重要的，索引可以大大提高MySQL 的檢索速度。

• MySQL 索引類似于書籍的索引，通過存儲指向數(shù)據(jù)行的指針，可以快速定位和訪問表中的特定數(shù)據(jù)。
• 打個比方，如果合理的設計且使用索引的 MySQL 是一輛蘭博基尼的話，那么沒有設計和使用索引的 MySQL 就是一個人力三輪車。
• 拿漢語字典的目錄頁（索引）打比方，我們可以按拼音、筆畫、偏旁部首等排序的目錄（索引）快速查找到需要的字。

🎄索引的使用場景

創(chuàng)建索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為WHERE 子句的條件)。

實際上，索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄。

索引雖然能夠提高查詢性能，但也需要注意以下幾點：

• 索引需要占用額外的存儲空間。

• 對表進行插入、更新和刪除操作時，索引需要維護，可能會影響性能。

• 過多或不合理的索引可能會導致性能下降，因此需要謹慎選擇和規(guī)劃索引。

滿足以上條件時，考慮對表中的這些字段創(chuàng)建索引，以提高查詢效率。

反之，如果非條件查詢列，或經(jīng)常做插入、修改操作，或磁盤空間不足時，不考慮創(chuàng)建索引。

🍀索引的使用

創(chuàng)建主鍵約束（PRIMARY KEY）、唯一約束（UNIQUE）、外鍵約（FOREIGN KEY）時，會自動創(chuàng)建對應列的索引

接下里我們實現(xiàn)一些索引的基本操作和使用

📌查看索引

你可以使用 SHOW INDEX 命令來列出表中的相關的索引信息。

可以通過添加 \G 來格式化輸出信息。

SHOW INDEX 語句：

SHOW INDEX FROM table_name\G

• SHOW INDEX: 用于顯示索引信息的關鍵字。

• FROM table_name: 指定要查看索引信息的表的名稱。

• \G: 格式化輸出信息。

執(zhí)行上述命令后，將會顯示指定表中所有索引的詳細信息

包括索引名稱（Key_name）、索引列(Column_name）、是否是唯一索引（Non_unique）、排序方式（Collation）、索引的基數(shù)（Cardinality）等。

這里博主查詢一個博主已經(jīng)建立好的一個student表的索引

案例查詢：查看學生表已有的索引，查詢結果如下

📌創(chuàng)建索引

對于非主鍵、非唯一約束、非外鍵的字段，可以創(chuàng)建普通索引

使用 CREATE INDEX 語句可以創(chuàng)建普通索引。

普通索引是最常見的索引類型，用于加速對表中數(shù)據(jù)的查詢。

CREATE INDEX 的語法：

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• CREATE INDEX: 用于創(chuàng)建普通索引的關鍵字。

• index_name: 指定要創(chuàng)建的索引的名稱。索引名稱在表中必須是唯一的。

• table_name: 指定要在哪個表上創(chuàng)建索引。

• (column1, column2, …): 指定要索引的表列名。你可以指定一個或多個列作為索引的組合。這些列的數(shù)據(jù)類型通常是數(shù)值、文本或日期。

• ASC和DESC（可選）: 用于指定索引的排序順序。默認情況下，索引以升序（ASC）排序。

以下實例假設我們有一個名為 students 的表，包含 id、name 和 age 列，我們將在 name 列上創(chuàng)建一個普通索引。

CREATE INDEX idx_name ON students (name);

建立索引如下：

🌲刪除索引

drop index 索引名 on 表名;

以下實例是我們對上述建立索引的一個刪除，刪除student表中name字段的索引

drop index inx_name on student;

刪除后，查詢結果如下：

🌴索引保存的數(shù)據(jù)結構

索引保存的數(shù)據(jù)結構主要為B+樹

再介紹B+樹之前我們先來看一下，B樹，因為B+樹是在B樹的基礎上進行優(yōu)化的

🎈B樹

B樹與二叉樹（Binary Tree）不是一個概念，你可以將其翻譯成Balance Tree，或者是Bayer Tree。

B樹是一種自平衡的樹，能夠保持數(shù)據(jù)有序。這種數(shù)據(jù)結構能夠讓查找數(shù)據(jù)、順序訪問、插入數(shù)據(jù)及刪除的動作，都在對數(shù)時間內完成。

B樹與AVL樹不同，可以擁有2個以上的子節(jié)點，并且每個節(jié)點可以有多個鍵值，這些屬性減少了定位記錄時所經(jīng)歷的中間過程，加快了存取速度。B樹更適用于讀寫相對較大的數(shù)據(jù)塊存儲系統(tǒng)，如磁盤。這種數(shù)據(jù)結構常被應用在數(shù)據(jù)庫和文件系統(tǒng)的實現(xiàn)上。

對于一個M階B樹具有以下特性：

• 每個節(jié)點最多有 M 個子節(jié)點；每個內部節(jié)點最少有 ?M/2? 個子節(jié)點（?x?為向上取整符號；如果根節(jié)點不是葉子節(jié)點，那么它至少有兩個子節(jié)點。

• 具有 N 個子節(jié)點的非葉子節(jié)點擁有 N-1 個鍵。

• 所有葉子節(jié)點必須處于同一層上。

🎈B+樹

是B-Tree的改進版本，同時也是數(shù)據(jù)庫索引索引所采用的存儲結構。數(shù)據(jù)都在葉子節(jié)點上，并且增加了順序訪問指針，每個葉子節(jié)點都指向相鄰的葉子節(jié)點的地址。相比B-Tree來說，進行范圍查找時只需要查找兩個節(jié)點，進行遍歷即可。而B-Tree需要獲取所有節(jié)點，相比之下B+Tree效率更高。

那我們具體怎么存儲數(shù)據(jù)庫中的這些數(shù)據(jù)呢？

比如我們有以下這樣一張student表，主鍵為id

MyISAM（MySQL的數(shù)據(jù)庫引擎）中是這樣存儲的

在InnoDB中的實現(xiàn)

🎈問題解決

問：為什么索引結構默認使用B-Tree，而不是hash，二叉樹，紅黑樹？

hash：雖然可以快速定位，但是沒有順序，IO復雜度高。
二叉樹：樹的高度不均勻，不能自平衡，查找效率跟數(shù)據(jù)有關（樹的高度），并且IO代價高。
紅黑樹：樹的高度隨著數(shù)據(jù)量增加而增加，IO代價高。

問：為什么官方建議使用自增長主鍵作為索引。

結合B+Tree的特點，自增主鍵是連續(xù)的，在插入過程中盡量減少頁分裂，即使要進行頁分裂，也只會分裂很少一部分。并且能減少數(shù)據(jù)的移動，每次插入都是插入到最后。總之就是減少分裂和移動的頻率。

問：B樹與B+樹的異同點

相同點

• 一個節(jié)點可以存儲多個元素
• 葉子節(jié)點是排序的
• 每個節(jié)點中的元素， 也都按照從小到大的順序排列， 即： 左小右大。
• 所有葉子節(jié)點都位于同一層，或者說根節(jié)點到每個葉子節(jié)點的長度都相同。
• 根節(jié)點元素個數(shù)： 1<= k <= m-1 (m表示階數(shù)， 即： 一個節(jié)點最多有多少子節(jié)點)非根節(jié)點元素個數(shù)： m/2 <= k <= m-

不同點

• B+樹葉子節(jié)點是有指針的， MySQLInnoDB中采用的是雙向指針，上層非葉子節(jié)點也有雙向指針
• B+樹非葉子節(jié)點的元素是與葉子節(jié)點有冗余重復的情況

🛬事務

?事務的概念

MySQL 事務主要用于處理操作量大，復雜度高的數(shù)據(jù)。比如說，在人員管理系統(tǒng)中，你刪除一個人員，你既需要刪除人員的基本資料，也要刪除和該人員相關的信息，如信箱，文章等等，這樣，這些數(shù)據(jù)庫操作語句就構成一個事務！

• 在 MySQL 中只有使用了 Innodb 數(shù)據(jù)庫引擎的數(shù)據(jù)庫或表才支持事務。

• 事務處理可以用來維護數(shù)據(jù)庫的完整性，保證成批的 SQL 語句要么全部執(zhí)行，要么全部不執(zhí)行。

• 事務用來管理 insert,update,delete 語句

?事務需要滿足的四大條件

• 原子性：一個事務（transaction）中的所有操作，要么全部完成，要么全部不完成，不會結束在中間某個環(huán)節(jié)。事務在執(zhí)行過程中發(fā)生錯誤，會被回滾（Rollback）到事務開始前的狀態(tài)，就像這個事務從來沒有執(zhí)行過一樣。

• 一致性：在事務開始之前和事務結束以后，數(shù)據(jù)庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫可以自發(fā)性地完成預定的工作。

• 隔離性：數(shù)據(jù)庫允許多個并發(fā)事務同時對其數(shù)據(jù)進行讀寫和修改的能力，隔離性可以防止多個事務并發(fā)執(zhí)行時由于交叉執(zhí)行而導致數(shù)據(jù)的不一致。事務隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復讀（repeatable read）和串行化（Serializable）。

• 持久性：事務處理結束后，對數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會丟失

🧭事務控制語句與簡單使用

控制語句（部分）

• BEGIN 或 START TRANSACTION 顯式地開啟一個事務；

• COMMIT 也可以使用 COMMIT WORK，不過二者是等價的。COMMIT 會提交事務，并使已對數(shù)據(jù)庫進行的所有修改成為永久性的；

• ROLLBACK 也可以使用 ROLLBACK WORK，不過二者是等價的?；貪L會結束用戶的事務，并撤銷正在進行的所有未提交的修改；

使用如下：

1. 開啟事務：start transaction;
2. 執(zhí)行多條SQL語句
3. 回滾或提交：rollback/commit;

• rollback即是全部失敗，commit即是全部成功

🥎事務的并發(fā)控制

數(shù)據(jù)庫是一個共享資源，可以供多個用戶使用。允許多個用戶同時使用一個數(shù)據(jù)庫的數(shù)據(jù)庫系統(tǒng)稱為多用戶數(shù)據(jù)庫系統(tǒng)。例如飛機訂票數(shù)據(jù)庫系統(tǒng)、銀行數(shù)據(jù)庫系統(tǒng)等都是多用戶數(shù)據(jù)庫系統(tǒng)。在這樣的系統(tǒng)中，在同一時刻并發(fā)執(zhí)行的事務數(shù)可達數(shù)百上千個。

而在面對這么多并發(fā)執(zhí)行的事務存在以下幾個問題

• 丟失修改

兩個事務T1和T2讀入同一數(shù)據(jù)并修改，T2提交的結果破壞了T1提交的結果，導致T1的修改被丟失

• 不可重復讀

不可重復讀是指事務T1讀取數(shù)據(jù)后，事務T2執(zhí)行更新操作后，使T1無法再現(xiàn)前一次讀取結果

• 讀“臟”數(shù)據(jù)

讀“臟”數(shù)據(jù)是指事務T1修改某以數(shù)據(jù)并將其寫回磁盤，事務T2讀取同一數(shù)據(jù)后，T1由于某種原因被撤銷，這時被T1修改過的數(shù)據(jù)恢復原值，T2讀到的數(shù)據(jù)就與數(shù)據(jù)庫中的數(shù)據(jù)不一致，則T2讀到的數(shù)據(jù)就為“臟”數(shù)據(jù)，即不正確的數(shù)據(jù)。

🏀封鎖

封鎖是實現(xiàn)并發(fā)控制的一個重要技術。所謂的封鎖就是事務T在對某個數(shù)據(jù)對象，例如表、記錄等操作之前，先前系統(tǒng)發(fā)出請求，對其加鎖。

加鎖后事務T就對該數(shù)據(jù)對象有了一定的控制，在事務T釋放它之前，其他事務不能更新此數(shù)據(jù)對象。

確切的控制由封鎖的類型決定。基本的封鎖的類型由兩種：排他鎖（exclusive locks，簡稱X鎖）和共享鎖（share locks，簡稱S鎖）

• 排他鎖又稱為寫鎖。若事務T對數(shù)據(jù)對象A加上X鎖，則只允許工讀取和修改Ar其他任何事務都不能再對A加任何類型的鎖，直到T釋放A上的鎖為止。這就保證了其他事務在T釋放A上的鎖之前不能再讀取和修改A。

• 共享鎖稱為讀鎖。若事務 T對數(shù)據(jù)對象A加上S鎖，則事務工可以讀A但不能修改A，其他事務只能再對A加S鎖，而不能加X鎖，直到T釋放A上的S鎖為止。這就保證了其他事務可以讀A，但在T釋放A上的S鎖之前不能對A做任何修改。

🎡封鎖協(xié)議

在運用X鎖和S鎖這種基本封鎖對數(shù)據(jù)對象加鎖時，還需要約定一些規(guī)則。例如何時申請X鎖或S鎖、封鎖時間、何時釋放等，這些規(guī)則稱為封鎖協(xié)議。對封鎖方式制定不通的規(guī)則，就形成了各種不同的封鎖協(xié)議。

• 一級封鎖協(xié)議

一級封鎖協(xié)議是指，事務T在修改數(shù)據(jù)R之前必須先對其加X鎖，直到事務結束才釋放。事務結束包括正常結束（COMMIT）和正常結束（ROLLBACK）。

一級封鎖協(xié)議可防止丟失修改，并保證事務T是可恢復的，可解決丟失修改問題

• 二級封鎖協(xié)議

二級封鎖協(xié)議是指，在一級封鎖協(xié)議基礎上增加事務在讀取數(shù)據(jù)R之前必須先對其加S鎖，讀完后即可釋放S鎖。

二級封鎖協(xié)議除防止了丟失修改，還可進一步防止讀“臟”數(shù)據(jù)

• 三級封鎖協(xié)議

三級封鎖協(xié)議是指，在一級封鎖協(xié)議的基礎上增加事務T在讀取數(shù)據(jù)R之前必須先對其加S鎖，直到事務結束才釋放。三級封鎖協(xié)議除了防止丟失修改和讀“臟”數(shù)據(jù)外、還進一步防止了不可重復讀

?總結

關于《【MySQL】 MySQL索引事務》就講解到這兒，感謝大家的支持，歡迎各位留言交流以及批評指正，如果文章對您有幫助或者覺得作者寫的還不錯可以點一下關注，點贊，收藏支持一下！