Mysql資料庫是如何通過索引定位資料

前言

我們之前講了MySQL索引的底層資料結構，以及索引的失效原理等知識點。那麼索引在硬碟上到底是怎麼儲存的呢？怎麼通過索引定位查詢出一條真實的資料呢？

MySQL兩大引擎MyIsam和InnoDB他們的索引又有哪些不同點呢？

今天我們就來給大家揭曉一下

MyIsam 和 InnoDB常見區別

事務方面

InnoDB 支援事務，MyISAM 不支援事務。這是 MySQL 將預設儲存引擎從 MyISAM 變成 InnoDB 的重要原因之一。

外來鍵方面

InnoDB 支援外來鍵，而 MyISAM 不支援。對一個包含外來鍵的 InnoDB 錶轉為 MYISAM 會失敗。

索引層面

InnoDB 是聚集（聚簇）索引，MyISAM 是非聚集（非聚簇）索引。後面會重點講解這兩種索引的區別。

MyISAM支援 FULLTEXT型別的全文索引， InnoDB不支援FULLTEXT型別的全文索引，但是InnoDB可以使用sphinx外掛支援全文索引，並且效果更好。

鎖粒度方面

InnoDB 最小的鎖粒度是行鎖，MyISAM 最小的鎖粒度是表鎖。

一個更新語句會鎖住整張表，導致其他查詢和更新都會被阻塞，因此併發訪問受限。

這也是 MySQL 將預設儲存引擎從 MyISAM 變成 InnoDB 的重要原因之一。

硬碟儲存結構

MyISAM在磁碟上儲存成三個檔案。第一個檔案的名字以表的名字開始，副檔名指出檔案型別。

• .frm檔案儲存表的定義。

• 資料檔案的擴 展名為.MYD (MYData)。

• 索引檔案的擴 展名是.MYI (MYIndex)。

Innodb儲存引擎儲存資料庫資料，一共有兩個檔案(沒有專門儲存資料的檔案)：

• Frm檔案：表的定義檔案。

• Ibd檔案：資料和索引儲存檔案。資料以主鍵進行聚集儲存，把真正的資料儲存在葉子節點中。

聚簇索引 和 非聚簇索引

聚簇索引（InnoDB）

將資料儲存與索引放到了一塊，索引結構的葉子節點儲存了行資料。

表資料按照索引的順序來儲存的，也就是說索引項的順序與表中記錄的物理順序一致。

InnoDB中，在聚簇索引之上建立的索引稱之為輔助索引，像複合索引、字首索引、唯一索引等等。

• 聚簇索引預設是主鍵，

• 如果表中沒有定義主鍵，InnoDB 會選擇一個唯一的非空索引代替。

• 如果沒有這樣的索引，InnoDB 會在內部生成一個名為 GEN_CLUST_INDEX 的隱式的聚簇索引。

非聚簇索引（MyISAM）

將資料與索引分開儲存，表資料儲存順序與索引順序無關。

MyISAM索引查詢資料過程

非聚簇索引儲存結構：

源於網路

MyISAM的 B+樹 的葉子節點上，記錄的是真實資料的儲存地址。

比如通過主鍵id查詢，MyISAM查詢流程如下：

• 根據id值在B+樹上找到相應的葉子節點

• 取出葉子節點上的資料儲存地址

• 根據資料儲存地址，去找到相應的真實資料

InnoDB索引查詢資料過程

聚簇索引儲存結構：

源於網路

InnoDB的 B+樹 的葉子節點上，記錄的是真實行資料。

比如通過主鍵id查詢，InnoDB查詢流程如下：

聚簇索引（主鍵索引）：

• 根據id值在B+樹上找到相應的葉子節點

• 取出葉子節點上的行資料，返回即可

輔助索引（聚簇索引以外的）：

• 在相應索引的B+樹上找到相應的葉子節點

• 取出葉子節點上的資料，該資料是主鍵id

• 拿到主鍵id後，去主鍵索引的B+樹上找到相應的葉子節點

• 取出葉子節點上的行資料，返回

總結

以上就是兩個引擎索引查詢流程，MyIsam 和 InnoDB各有優缺點。

比如通過主鍵id查詢的時候，InnoDB比MyIsam快一些，因為InnoDB只需要一次B+樹查詢就能取出資料。MyIsam通過B+樹查詢到地址後，還需要根據地址去查詢真正的資料。

但是InnoDB普通索引查詢會比MyIsam慢些，因為InnoDB要進行2次B+樹的查詢。

還比如說在資料重構的時候，MyIsam記錄的是資料地址，那麼重構資料的時候地址就要重新生成一遍，這也是有問題的。

InnoDB重構資料的時候就不會這樣，因為他記錄的是主鍵id，地址會變化，主鍵id是不會變的。

還有為什麼少用select *，為什麼儘量使用覆蓋索引，大家能根據他們的索引結構推測出來嗎，給大家留道思考題？

每個技術都是存在優缺點的，我們還是那句話，對於技術我們要揚長避短，根據我們的業務場景，發揮出技術的長處。