一、前言

我們在開發(fā)或者面試的過程中經(jīng)常會涉及到索引。今天我們來詳細(xì)剖析一下索引常用的知識點。首先我們先介紹mysql的常用的存儲引擎，其次是索引分類。

二、索引分類

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

三、邏輯分類

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

3.1、功能劃分

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

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

 ALTERTABLE TableName ADDPRIMARYKEY(column_list);

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

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

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

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

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

按列數(shù)劃分

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

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

3.2、物理分類（重點）

聚簇索引

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

非聚簇索引

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

四、不同存儲引擎中索引落盤的差異

存儲引擎

MyISAM：

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

InnoDB：

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

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

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

主鍵索引

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

輔助索引

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

查詢IO圖形化表示如下：

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

非聚集索引

主鍵索引

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

輔助索引

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