Mysql資料庫索引初學者詳解-mysql教程-PHP中文網

核心概念

主鍵索引/輔助索引
聚集索引/非聚集索引
表格查找/索引覆蓋
索引下推
複合索引/最左邊前綴匹配
前綴索引
解釋一下

1.【指標定義】

1。索引定義

除了資料本身之外，資料庫系統還維護滿足特定搜尋演算法的資料結構。這些結構以某種方式引用（指向）資料，允許在它們上實作進階搜尋演算法。 這些資料結構就是索引。

2。索引的資料結構

B-tree / B+ 樹（MySQL 的 InnoDB 引擎使用 B+ 樹作為預設索引結構）
雜湊表
排序數組

3。為什麼選擇 B+ 樹而不是 B 樹

B樹結構：記錄儲存在樹節點中。

Mysql Database Index Explained for Beginners

B+樹結構：記錄僅儲存在樹的葉節點中。

Mysql Database Index Explained for Beginners

假設資料大小為 1KB，索引大小為 16B，資料庫使用磁碟資料頁，預設磁碟頁大小為 16K，相同的三個 I/O 操作將產生：

B 樹可以取得 16*16*16=4096 筆記錄。
B+樹可以取得1000*1000*1000=10億筆記錄。

2.【索引類型】

1。主鍵索引與輔助索引

主鍵索引：索引的葉子節點是資料行。
輔助索引：索引的葉子節點是KEY欄位加上主鍵索引。因此，透過二級索引查詢時，首先找到主鍵值，然後InnoDB透過主鍵索引找到對應的資料塊。
在InnoDB中，主索引檔案直接儲存資料行，稱為聚集索引，而二級索引則指向主鍵引用。
在MyISAM中，主索引和輔助索引都指向實體行（磁碟位置）。

Mysql Database Index Explained for Beginners

2。聚集索引和非聚集索引

聚集索引重新組織磁碟上的實際數據，並按一個或多個指定的列值進行排序。特點是資料的儲存順序和索引順序一致。一般主鍵會預設建立聚集索引，一張表只允許有一個聚集索引（原因：資料只能以一種順序儲存）。如圖所示，InnoDB的主索引和輔助索引都是聚集索引。
與作為資料記錄的聚集索引的葉節點相比，非聚集索引的葉節點是指向資料記錄的指標。最大的差異是資料記錄的順序與索引順序不符。

3。聚集索引的優點和缺點

優點：透過主鍵查詢條目時，不需要查表（資料在主鍵節點下）。
缺點：不規則的資料插入會導致頻繁的分頁。

3.【擴展索引概念】

1。查表

查表的概念涉及到主鍵索引查詢和非主鍵索引查詢的區別。

如果查詢是select * from T where ID=500，主鍵查詢只需要找ID樹。
如果查詢是select * from T where k=5，則非主鍵索引查詢需要先找k索引樹得到ID值500，然後再找一次ID索引樹。
從非主鍵索引移回主鍵索引的程序稱為查表。

基於非主鍵索引的查詢需要掃描額外的索引樹。 因此，我們應該在應用程式中盡量使用主鍵查詢。從儲存空間的角度來看，由於非主鍵索引樹的葉子節點儲存主鍵值，建議主鍵欄位盡可能短。這樣，非主鍵索引樹的葉子節點就更小，非主鍵索引佔用的空間也更少。一般情況下，建議建立自增主鍵，以盡量減少非主鍵索引所佔用的空間。

2。索引覆蓋

如果WHERE子句條件是非主鍵索引，則查詢會先透過非主鍵索引定位到主鍵索引（主鍵位於非主鍵索引的葉子節點）主鍵索引搜尋樹），然後透過主鍵索引定位查詢內容。在這個過程中，回到主鍵索引樹就稱為查表。
但是，當我們的查詢內容是主鍵值時，我們可以直接提供查詢結果，無需查表。也就是說，本次查詢中非主鍵索引已經「覆蓋」了我們的查詢需求，因此稱為覆蓋索引。
覆蓋索引可以直接從輔助索引取得查詢結果，無需到主索引進行查表，從而減少搜尋次數（不需要從輔助索引樹移動到聚集索引樹）或減少IO操作（輔助索引樹可以一次從磁碟加載更多節點），從而提高效能。

3。綜合指數

複合索引是指將資料表的多個欄位進行索引。

場景 1：

複合索引(a, b) 依a, b排序（先依a排序，若a相同則依b排序）。因此，以下的語句可以直接使用複合索引來獲得結果（其實就是利用了最左字首原則）：

從 xxx 選擇…，其中 a=xxx;
從 xxx 選擇…，其中 a=xxx 按 b 排序；

以下語句不能使用複合查詢：

從 xxx 中選擇…，其中 b=xxx;

場景 2：

對於複合索引（a,b,c），以下語句可以直接透過複合索引取得結果：

從 xxx 選擇…，其中 a=xxx 按 b 排序；
從 xxx 選擇…，其中 a=xxx 和 b=xxx 按 c 排序；

以下語句不能使用複合索引，需要進行檔案排序運算：

從 xxx 選擇 …，其中 a=xxx 按 c 排序；

摘要：

以複合索引（a、b、c）為例，建立這樣的索引相當於建立索引a、ab、abc。 用一個索引取代三個索引肯定是有好處的，因為每個額外的索引都會增加寫入作業的開銷和磁碟空間的使用。

4。最左字首原則

從上面的複合索引例子，我們可以了解最左前綴原理。
不只是索引的完整定義，只要滿足最左邊的前綴，就可以用來加速檢索。這個最左前綴可以是複合索引最左邊的N個字段，也可以是字串索引最左邊的M個字元。利用索引的「最左前綴」原則來定位記錄，避免冗餘的索引定義。
因此，基於最左前綴原則，定義複合索引時考慮索引內的欄位順序至關重要！評價標準是索引的可重用性。例如當(a,b)上已經有索引時，一般不需要在a上單獨建立索引

5。索引下推

MySQL 5.6引入了索引下推優化，索引遍歷時可以根據索引包含的欄位過濾掉不符合條件的記錄，減少查表次數。

建立表

CREATE TABLE `test` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
   `age` int(11) NOT NULL DEFAULT '0',
   `name` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `idx_name_age` (`name`,`age`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

登入後複製

SELECT * from user where name like 'Chen%' 最左前綴原則，命中 idx_name_age 索引
從名稱如“Chen%”且年齡=20 的用戶中選擇 *
- 5.6版本之前，首先會根據姓名索引來配對2筆記錄（此時忽略age=20的條件），找到對應的2個ID，進行查表，然後根據age=20進行篩選。
- 5.6版本之後，引入了索引下推。根據姓名配對2筆記錄後，在進行查表之前不會忽略age=20的條件，在查表之前根據年齡進行過濾。這種索引下推可以減少查表次數，提高查詢效能。

6。前綴索引

當索引是一個長字元序列時，它會佔用大量記憶體且速度很慢。在這種情況下，可以使用前綴索引。我們不是對整個值進行索引，而是對前幾個字元進行索引，以節省空間並獲得良好的效能。 前綴索引使用索引的前幾個字母。但是，為了降低索引重複率，我們必須評估前綴索引的唯一性。

先計算目前字串欄位的唯一性比例：select 1.0*count(distinct name)/count(*) from test
然後，計算不同前綴的唯一性比率：
- 從測試中選擇 1.0*count(distinct left(name,1))/count(*) 作為名稱的第一個字元作為前綴索引
- 從測試中選擇 1.0*count(distinct left(name,2))/count(*) 作為名稱的前兩個字元作為前綴索引
- ...
當 left(str, n) 沒有顯著增加時，選擇 n 作為前綴索引截止值。
建立索引 alter table test add key(name(n));