一、前言

我們?cè)陂_發(fā)或者面試的過程中經(jīng)常會(huì)涉及到索引。今天我們來詳細(xì)剖析一下索引常用的知識(shí)點(diǎn)。首先我們先介紹mysql的常用的存儲(chǔ)引擎，其次是索引分類。

二、索引分類

MySQL 的索引有兩種分類方式：邏輯分類和物理分類

三、邏輯分類

有多種邏輯劃分的方式，比如按功能劃分，按組成索引的列數(shù)劃分等

3.1、功能劃分

主鍵索引：一張表只能有一個(gè)主鍵索引，不允許重復(fù)、不允許為 NULL

主鍵索引：一張表只能有一個(gè)主鍵索引，不允許重復(fù)、不允許為 NULL

 ALTERTABLE TableName ADDPRIMARYKEY(column_list);

唯一索引：數(shù)據(jù)列不允許重復(fù)，允許為 NULL 值，一張表可有多個(gè)唯一索引，索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一

CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD UNIQUE (column_list); 

普通索引：一張表可以創(chuàng)建多個(gè)普通索引，一個(gè)普通索引可以包含多個(gè)字段，允許數(shù)據(jù)重復(fù)，允許 NULL 值插入；

CREATE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));

全文索引：它查找的是文本中的關(guān)鍵詞，主要用于全文檢索

按列數(shù)劃分

單列索引：一個(gè)索引只包含一個(gè)列，一個(gè)表可以有多個(gè)單例索引。

組合索引：一個(gè)組合索引包含兩個(gè)或兩個(gè)以上的列。查詢的時(shí)候遵循 最左前綴原則

3.2、物理分類（重點(diǎn)）

聚簇索引

將數(shù)據(jù)存儲(chǔ)與索引放到了一塊，找到索引也就找到了數(shù)據(jù)

非聚簇索引

將數(shù)據(jù)與索引分開存儲(chǔ)，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)指向了數(shù)據(jù)對(duì)應(yīng)的位置

四、不同存儲(chǔ)引擎中索引落盤的差異

存儲(chǔ)引擎

MyISAM：

• *.frm：與表相關(guān)的元數(shù)據(jù)信息都存放在frm文件，包括表結(jié)構(gòu)的定義信息等
• *.MYD：MyISAM DATA，用于存儲(chǔ)MyISAM表的數(shù)據(jù)
• *.MYI：MyISAM INDEX，用于存儲(chǔ)MyISAM表的索引相關(guān)信息

InnoDB：

• *.frm：與表相關(guān)的元數(shù)據(jù)信息都存放在frm文件，包括表結(jié)構(gòu)的定義信息等
• *.ibd：InnoDB DATA，表數(shù)據(jù)和索引的文件。該表的索引(B+樹)的每個(gè)非葉子節(jié)點(diǎn)存儲(chǔ)索引，葉子節(jié)點(diǎn)存儲(chǔ)索引和索引對(duì)應(yīng)的數(shù)據(jù)

五、假設(shè)用戶（t_user）如下

六、聚集索引 (又叫聚簇索引)

主鍵索引

InnoDB只有一個(gè)文件（.ibd文件），那索引放在哪里呢？在InnoDB 里面，它是以主鍵為索引來組織數(shù)據(jù)的存儲(chǔ)的，所以索引文件和數(shù)據(jù)文件是同一個(gè)文件，都在.ibd文件里面。在InnoDB的主鍵索引的葉子節(jié)點(diǎn)上，它直接存儲(chǔ)了我們的數(shù)據(jù)。

輔助索引

假設(shè)在NAME列上創(chuàng)建一個(gè)索引，那name的索引B+ tree 如下

查詢IO圖形化表示如下：

主鍵索引與輔助索引配合查詢

非聚集索引

主鍵索引

非聚集索引在葉節(jié)點(diǎn)上有一個(gè)“指針”直接指向要查詢的數(shù)據(jù)區(qū)域

輔助索引

在MyISAM里面，輔助索引也在這個(gè).MYI文件里面。輔助索引跟主鍵索引存儲(chǔ)和檢索數(shù)據(jù)的方式是沒有任何區(qū)別的，一樣是在索引文件里面找到磁盤地址，然后到數(shù)據(jù)文件里面獲取數(shù)據(jù)。?