本節(jié)目標(biāo)

• 索引
• 事務(wù)

🛫索引

🎍索引的概念

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

🌳索引的作用

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

MySQL 索引的建立對于 MySQL 的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL 的檢索速度。

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

🎄索引的使用場景

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

實(shí)際上，索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄。

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

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

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

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

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

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

🍀索引的使用

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

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

📌查看索引

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

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

SHOW INDEX 語句：

SHOW INDEX FROM table_name\G

• SHOW INDEX: 用于顯示索引信息的關(guān)鍵字。

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

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

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

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

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

案例查詢：查看學(xué)生表已有的索引，查詢結(jié)果如下

📌創(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)建普通索引的關(guān)鍵字。

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

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

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

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

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

CREATE INDEX idx_name ON students (name);

建立索引如下：

🌲刪除索引

drop index 索引名 on 表名;

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

drop index inx_name on student;

刪除后，查詢結(jié)果如下：

🌴索引保存的數(shù)據(jù)結(jié)構(gòu)

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

再介紹B+樹之前我們先來看一下，B樹，因?yàn)锽+樹是在B樹的基礎(chǔ)上進(jìn)行優(yōu)化的

🎈B樹

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

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

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

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

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

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

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

🎈B+樹

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

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

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

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

在InnoDB中的實(shí)現(xiàn)

🎈問題解決

問：為什么索引結(jié)構(gòu)默認(rèn)使用B-Tree，而不是hash，二叉樹，紅黑樹？

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

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

結(jié)合B+Tree的特點(diǎn)，自增主鍵是連續(xù)的，在插入過程中盡量減少頁分裂，即使要進(jìn)行頁分裂，也只會分裂很少一部分。并且能減少數(shù)據(jù)的移動，每次插入都是插入到最后?？傊褪菧p少分裂和移動的頻率。

問：B樹與B+樹的異同點(diǎn)

相同點(diǎn)

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

不同點(diǎn)

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

🛬事務(wù)

?事務(wù)的概念

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

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

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

• 事務(wù)用來管理 insert,update,delete 語句

?事務(wù)需要滿足的四大條件

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

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

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

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

🧭事務(wù)控制語句與簡單使用

控制語句（部分）

• BEGIN 或 START TRANSACTION 顯式地開啟一個事務(wù)；

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

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

使用如下：

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

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

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

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

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

• 丟失修改

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

• 不可重復(fù)讀

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

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

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

🏀封鎖

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

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

確切的控制由封鎖的類型決定?；镜姆怄i的類型由兩種：排他鎖（exclusive locks，簡稱X鎖）和共享鎖（share locks，簡稱S鎖）

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

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

🎡封鎖協(xié)議

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

• 一級封鎖協(xié)議

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

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

• 二級封鎖協(xié)議

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

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

• 三級封鎖協(xié)議

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

?總結(jié)

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