mysql查詢每個表的資料量_Mysql索引資料結構

Mysql索引資料結構

當慢查詢時，看SQL是否走索引。

索引的本質：索引是幫助Mysql高效擷取資料的排好序的資料結構。

MySQL若不建立索引，查找某條資料時則會逐行掃描，每掃描一行資料就會做一次磁盤IO。

B-Tree:

葉節點具有相同的深度，葉節點的指針為空。

所有索引元素不重複。

葉節點中的資料索引從左到右遞增排列。

B樹模型圖。在B樹中，無論中間節點還是葉子節點都帶有資料。

B+樹模型圖，隻有葉子節點帶有資料，其餘中間節點僅僅是索引，沒有任何資料關聯。

B樹與B+樹的性能比較

查詢過程看上去跟B樹差不多，但還是有兩點不同的，首先，B+樹中間節點沒有資料，隻存索引資料，是以同樣大小的磁盤頁可以容納更多的節點元素，這就意味着，資料量相同的情況下B+樹比B樹更加的”矮胖“，相應會減小IO次數。其次，B+樹的查詢必須最終查找到葉子節點，而B樹隻要找到比對元素即可，無論比對元素處于中間節點還是葉子節點。

是以，B樹的查找性能并不穩定，最好的情況是隻查根節點即可，最壞的情況是要查到葉子節點，而B+樹每一次查找都是穩定的。

B+樹比B樹的優勢有三個：

1、單一節點存儲更多的元素，使得查詢的IO次數減少；

2、所有查詢都要查找到葉子節點，查詢性能穩定；

3、所有葉子節點形成有序連結清單，便于範圍查詢。

為什麼Mysql索引要用B+樹不是B樹？

用B+樹不用B樹考慮的是IO對性能的影響，B樹的每個節點都存儲資料，而B+樹隻有葉子節點才存儲資料，是以查找相同資料量的情況下，B樹的高度更高，IO更頻繁。資料庫索引是存儲在磁盤上的，當資料量大時，就不能把整個索引全部加載到記憶體了，隻能逐一加載每一個磁盤頁（對應索引樹的節點）。其中在MySQL底層對B+樹進行進一步優化：在葉子節點中是雙向連結清單，且在連結清單的頭結點和尾節點也是循環指向的。

提問：為何不采用hash方式？

等值查詢時可以，但無法滿足範圍查找

MySQL的MyISAM、InnoDB引擎

存儲引擎是修飾表的。（資料庫檔案是存儲在磁盤的MySQL安裝目錄的data檔案夾下）

myisam引擎（非聚集）的資料庫表對應3個檔案：

FRM:存儲表的結構定義檔案

MYD:存儲表内的資料行（存儲了表的真實資料）

MYI:存儲了表内的索引字段（即那棵B+樹）

B+樹的葉節點的data區域記憶體儲的是MYD表内資料的行位址（即指針）

InnoDB引擎（聚集）

資料庫表對應2個檔案：

FRM:存儲表的結構定義檔案

IBD:存儲的是表的索引+資料（即myisam的MYD+MYI的合并）

B+樹的葉節點的data區域記憶體儲的是一整行資料

MySQL中普遍使用B+Tree做索引，但在實作上又根據聚簇索引和非聚簇索引而不同。

聚簇索引

所謂聚簇索引，就是指主索引檔案和資料檔案為同一份檔案，聚簇索引主要用在Innodb存儲引擎中。在該索引實作方式中B+Tree的葉子節點上的data就是資料本身，key為主鍵，如果是一般索引的話，data便會指向對應的主索引。

在B+Tree的每個葉子節點增加一個指向相鄰葉子節點的指針，就形成了帶有順序通路指針的B+Tree。做這個優化的目的是為了提高區間通路的性能，如果要查詢key為從18到49的所有資料記錄，當找到18後，隻需順着節點和指針順序周遊就可以一次性通路到所有資料節點，極大提到了區間查詢效率。

非聚簇索

非聚簇索引就是指B+Tree的葉子節點上的data，并不是資料本身，而是資料存放的位址。主索引和輔助索引沒啥差別，隻是主索引中的key一定得是唯一的。主要用在MyISAM存儲引擎中。

為何InnoDB表必須有主鍵，并且推薦使用整型自增主鍵？

采用整型是因為在主鍵比較時更容易，比uuid更快速，自增是因為當插入新節點時，自動在葉節點處往後補充即可，若不是自增，B+樹為保證節點的大小順序，可能會産生節點的分裂和平衡的調整，十分耗時。

InnoDB的非主鍵索引

在葉節點處的data區域存儲的并不是整條資料，而是整條資料的主鍵。（為保證資料的一緻性和節省存儲空間）

Mysql之聯合索引

索引最左字首原理

聯合索引因為每個節點記憶體有多個字段，是以為保證資料從左到右依次遞增，就将（假設）3個字段依次進行大小比較。