MySQL基礎(chǔ)架構(gòu)(二) MySQL索引簡介

目錄

1 MySQL索引簡介

1.1 什么是數(shù)據(jù)庫索引？

1.2 MySQL 為什么需要索引？

2. B+樹：MySQL的主要索引選擇

2.1 B樹

2.2. B+樹

3 B+樹在MySQL中是如何使用的

3.1 聚簇索引

3.2 非聚簇索引（二級(jí)索引、普通索引）

3.3 聯(lián)合索引

1 MySQL索引簡介

1.1 什么是數(shù)據(jù)庫索引？

數(shù)據(jù)庫索引是一個(gè)持久化的數(shù)據(jù)結(jié)構(gòu)，它減少了查詢所需的I/O操作，提高了數(shù)據(jù)檢索速度。可以把它想象為一本書的目錄：沒有索引，你就必須一頁一頁地瀏覽書籍來找到您需要的信息；有了目錄，你可以快速找到所需信息的位置。

1.2 MySQL 為什么需要索引？

上節(jié)提到了數(shù)據(jù)頁的結(jié)構(gòu)，我們知道數(shù)據(jù)頁內(nèi)數(shù)據(jù)是按主鍵順序排列，并且分成了多個(gè)組，每個(gè)組的最大值形成了頁目錄。保證了在數(shù)據(jù)頁內(nèi)的快速檢索。

但在這種結(jié)構(gòu)下，還有兩個(gè)問題需要解決：

1. 一個(gè)數(shù)據(jù)頁內(nèi)只有主鍵有頁目錄，如果用非主鍵查詢?cè)趺崔k？
2. 一個(gè)表有很多數(shù)據(jù)頁，如何從大量數(shù)據(jù)頁中定位到所需的數(shù)據(jù)頁？

MySQL 索引正是為了解決上面說的兩個(gè)問題。

2. B+樹：MySQL的主要索引選擇

2.1 B樹

B+樹是B樹的一個(gè)變種，我們介紹B+樹的時(shí)候順便介紹以下B樹。

首先看B樹的一個(gè)示例：

B樹（Balanced Tree）是一個(gè)自平衡的多路搜索樹，它的結(jié)構(gòu)和存放順序確保了數(shù)據(jù)的高效檢索、插入和刪除。B樹中數(shù)據(jù)的存放順序基于以下幾點(diǎn)：

1. 鍵值排序：在B樹的每個(gè)節(jié)點(diǎn)中，鍵值是按升序排列的。例如，對(duì)于一個(gè)節(jié)點(diǎn)，如果它包含三個(gè)鍵值：K1, K2, K3，K4,那么這些鍵值滿足 K1 < K2 < K3 < K4。
2. 子節(jié)點(diǎn)指針：每個(gè)節(jié)點(diǎn)中的鍵值將子節(jié)點(diǎn)指針分隔開，每一個(gè)子節(jié)點(diǎn)指針指向的子樹中的所有鍵值都在相鄰的鍵值之間。例如，對(duì)于上面的節(jié)點(diǎn)，有以下四個(gè)子節(jié)點(diǎn)指針：P0, P1, P2, P3，P4，它們的鍵值范圍滿足：

• P0指向的子樹的所有鍵值 < K1
• P1指向的子樹的所有鍵值在 K1 和 K2 之間
• P2指向的子樹的所有鍵值在 K2 和 K3 之間
• P3指向的子樹的所有鍵值在 K3 和 K4 之間
• P4指向的子樹的所有鍵值 >= K4

1. 平衡性：B樹的所有葉子節(jié)點(diǎn)具有相同的深度，這確保了從樹的根到任何葉子節(jié)點(diǎn)的路徑長度相同，從而實(shí)現(xiàn)了數(shù)據(jù)訪問的平衡性。
2. 節(jié)點(diǎn)填充：B樹具有特定的填充因子，通常表示為節(jié)點(diǎn)包含的最小和最大鍵數(shù)。這意味著除了根節(jié)點(diǎn)外，每個(gè)節(jié)點(diǎn)必須有至少一定數(shù)量的鍵（通常是節(jié)點(diǎn)最大鍵數(shù)的一半）。這個(gè)特性確保樹不會(huì)太“稀疏”。
3. 分裂與合并：當(dāng)插入一個(gè)鍵導(dǎo)致節(jié)點(diǎn)的鍵數(shù)量超過最大值時(shí)，該節(jié)點(diǎn)會(huì)分裂為兩個(gè)節(jié)點(diǎn)。相反，當(dāng)刪除一個(gè)鍵導(dǎo)致節(jié)點(diǎn)的鍵數(shù)量少于最小值時(shí)，該節(jié)點(diǎn)可能會(huì)與相鄰的兄弟節(jié)點(diǎn)合并或從其兄弟節(jié)點(diǎn)“借”一個(gè)鍵。

?

2.2. B+樹

B+樹是B樹的一個(gè)變種，大致結(jié)構(gòu)和B樹相同但具有一些特殊的特點(diǎn)：

• 所有鍵值都出現(xiàn)在葉子節(jié)點(diǎn)：與B樹不同的是，B+樹的所有鍵值都位于葉子節(jié)點(diǎn)。內(nèi)部節(jié)點(diǎn)只包含關(guān)鍵字和指向其子節(jié)點(diǎn)的指針。
• 葉子節(jié)點(diǎn)的連續(xù)性：B+樹的葉子節(jié)點(diǎn)是連續(xù)的，它們通過指針相互連接。這種結(jié)構(gòu)使范圍查詢變得非常高效，因?yàn)榭梢院唵蔚乇闅v葉子節(jié)點(diǎn)來獲取一系列的值。

示例如下：

下面對(duì)B樹和B+樹做一下簡單對(duì)比：

1. 數(shù)據(jù)訪問：B樹的精準(zhǔn)查詢速度可能更快，B+樹的數(shù)據(jù)訪問可能更均勻。因?yàn)樗械臄?shù)據(jù)查詢都至少要到葉子節(jié)點(diǎn)，而B樹在內(nèi)部節(jié)點(diǎn)可能就已經(jīng)結(jié)束。
2. 范圍查詢：B+樹更適合范圍查詢，因?yàn)樗娜~子節(jié)點(diǎn)是相互連接的，可以快速地遍歷整個(gè)數(shù)據(jù)范圍。
3. 高度：對(duì)于存儲(chǔ)同樣數(shù)量的鍵，B+樹通常比B樹低，因?yàn)閿?shù)據(jù)值只存儲(chǔ)在葉子節(jié)點(diǎn)，這允許B+樹有更多的分支因子。
4. 穩(wěn)定性：B+樹可能更穩(wěn)定，因?yàn)樗械臄?shù)據(jù)都在葉子節(jié)點(diǎn)，并且當(dāng)數(shù)據(jù)增加或減少時(shí)，樹的結(jié)構(gòu)變動(dòng)較小。

?

3 B+樹在MySQL中是如何使用的

3.1 聚簇索引

當(dāng)新建一張表時(shí)，MySQL InnoDB存儲(chǔ)引擎會(huì)幫我們自動(dòng)創(chuàng)建一個(gè)索引，這個(gè)索引成為聚簇索引。這個(gè)索引以主鍵組織，包含了所有的表數(shù)據(jù)，這也就是MySQL所謂的"數(shù)據(jù)即索引"。聚簇索引可以加速通過主鍵的檢索。

一個(gè)聚簇索引的例子：

聚簇索引有以下特點(diǎn)：

1. 數(shù)據(jù)的物理存儲(chǔ)：在InnoDB中，聚簇索引實(shí)際上決定了表中數(shù)據(jù)行的物理存儲(chǔ)順序。這意味著表的數(shù)據(jù)是按照聚簇索引的鍵值順序存儲(chǔ)的。
2. 主鍵：在InnoDB中，每張表只能有一個(gè)聚簇索引。默認(rèn)情況下，聚簇索引是表的主鍵（PRIMARY KEY）。因此，選擇一個(gè)好的主鍵對(duì)于數(shù)據(jù)的查詢和存儲(chǔ)性能至關(guān)重要。
3. 非主鍵的情況：如果表中沒有定義主鍵，MySQL會(huì)嘗試選擇一個(gè)合適的唯一索引來作為聚簇索引。如果這都不可行，MySQL會(huì)為每一行生成一個(gè)6字節(jié)的隱式聚簇索引。
4. 與非聚簇索引的關(guān)系：InnoDB的非聚簇索引（Secondary Index）項(xiàng)包含了相應(yīng)聚簇索引項(xiàng)的值。這意味著在通過非聚簇索引進(jìn)行查找時(shí)，一旦找到了索引項(xiàng)，MySQL會(huì)使用該索引項(xiàng)中的聚簇索引值來查找實(shí)際的數(shù)據(jù)行。
5. 性能：由于數(shù)據(jù)是按照聚簇索引的順序存儲(chǔ)的，范圍查詢和按順序訪問數(shù)據(jù)時(shí)，聚簇索引通常能提供更好的性能。但同時(shí)，插入數(shù)據(jù)時(shí)，為了保持?jǐn)?shù)據(jù)的物理存儲(chǔ)順序，可能需要進(jìn)行數(shù)據(jù)移動(dòng)，這可能導(dǎo)致插入操作相對(duì)較慢。

3.2 非聚簇索引（二級(jí)索引、普通索引）

非聚簇索引是用戶手動(dòng)創(chuàng)建的，基于指定的列組織的索引。該索引存放指定的列與主鍵值，可以加速指定列的查詢速度。

一個(gè)非聚簇索引的示例圖如下：

具體特點(diǎn)如下：

1. 獨(dú)立的數(shù)據(jù)結(jié)構(gòu)：非聚簇索引是獨(dú)立于數(shù)據(jù)的索引結(jié)構(gòu)。它保存了索引列的值及對(duì)應(yīng)的聚簇索引鍵值（通常是主鍵值）。
2. 查找方式：當(dāng)通過非聚簇索引查詢數(shù)據(jù)時(shí)，查詢過程通常是兩階段的：首先，根據(jù)非聚簇索引查找到對(duì)應(yīng)的聚簇索引鍵值；然后，使用這個(gè)鍵值在聚簇索引中查找實(shí)際的數(shù)據(jù)行。上述過程中由聚簇索引查找實(shí)際數(shù)據(jù)行的過程稱為回表。
3. 存儲(chǔ)：非聚簇索引并不改變或決定數(shù)據(jù)的物理存儲(chǔ)順序。它只是一個(gè)對(duì)表數(shù)據(jù)的引用。
4. 數(shù)量：與聚簇索引不同，一張表可以有多個(gè)非聚簇索引。
5. 性能影響：使用非聚簇索引查詢數(shù)據(jù)可能會(huì)有額外的性能開銷，因?yàn)榭赡苌婕暗絻纱尾檎遥阂淮卧诜蔷鄞厮饕?，一次在聚簇索引中?strong>但在某些情況下，如果查詢只需要索引中的數(shù)據(jù)，則可以避免額外的回表查找，此種情況稱為覆蓋索引。

3.3 聯(lián)合索引

聯(lián)合索引是由兩個(gè)或多個(gè)列組成的索引，用來加速使用多列的查詢。

一個(gè)聯(lián)合索引的結(jié)構(gòu)圖如下：

它允許數(shù)據(jù)庫管理系統(tǒng)基于多個(gè)列的組合來加速查詢。聯(lián)合索引的關(guān)鍵點(diǎn)和用途如下：

1. 索引列的順序：在聯(lián)合索引中，列的順序很重要。對(duì)于索引(col1, col2, col3)，你可以有效地查詢col1，或者col1與col2的組合，或者col1、col2和col3的組合。但如果只查詢col2或col3，這個(gè)聯(lián)合索引可能不會(huì)被使用。
2. 最左前綴原則：在使用聯(lián)合索引時(shí)，必須遵循最左前綴原則。這意味著查詢條件必須使用索引的最左邊的列。例如，對(duì)于索引(col1, col2, col3)，你可以基于col1或col1和col2進(jìn)行查詢，但如果沒有使用col1，那么此索引可能不會(huì)被考慮。
3. 性能考慮：聯(lián)合索引可以減少所需的磁盤I/O，并幫助數(shù)據(jù)庫更快地檢索數(shù)據(jù)，尤其是當(dāng)查詢條件包含索引中的多個(gè)列時(shí)。
4. 存儲(chǔ)開銷：雖然聯(lián)合索引可以提高查詢性能，但它也增加了存儲(chǔ)開銷，并可能對(duì)INSERT、UPDATE和DELETE操作的速度產(chǎn)生影響，因?yàn)橛懈嗟乃饕Y(jié)構(gòu)需要被維護(hù)。
5. 選擇性：索引的選擇性是指索引列中唯一值的數(shù)量與表中行數(shù)的比率。高選擇性的索引通常更有效，因?yàn)樗鼈兡芨_地定位數(shù)據(jù)。當(dāng)組合多個(gè)列創(chuàng)建聯(lián)合索引時(shí)，這些列的組合選擇性可能會(huì)比單獨(dú)的列更高。
6. 覆蓋查詢：如果一個(gè)查詢只需要使用聯(lián)合索引中的列，那么數(shù)據(jù)庫可以只讀取索引，而不需要訪問實(shí)際的數(shù)據(jù)行。這稱為覆蓋查詢，它通常非常快。舉個(gè)例子，表t有a、b、c三列，a是主鍵，現(xiàn)在一條SQL是select c from t where b = 'x';如果你只建立的b字段的索引，那么還是需要回表（不清楚回表概念可以看下3.2的普通索引查詢過程描述），如果建立了b、c字段的聯(lián)合索引，不需要回表。可以減少io次數(shù)。