1、常見索引概念

索引按照物理實現(xiàn)方式，索引可以分為 2 種：聚簇（聚集）和非聚簇（非聚集）索引。我們也把非聚集 索引稱為二級索引或者輔助索引。一般來說，以主鍵建立的索引是聚簇索引，以非主鍵構建的索引叫非聚簇索引(二級索引)。

潁東ssl適用于網站、小程序/APP、API接口等需要進行數(shù)據(jù)傳輸應用場景，ssl證書未來市場廣闊！成為成都創(chuàng)新互聯(lián)的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18980820575（備注：SSL證書合作）期待與您的合作！1、聚簇索引

【關鍵詞】主鍵、葉子節(jié)點即完整用戶記錄，存儲引擎自動創(chuàng)建、一個表中只有一個聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式，（所有用戶記錄都存在了葉子節(jié)點上），也就是所謂的索引即數(shù)據(jù)，數(shù)據(jù)即索引。

在InnoDB 里的ibd文件，就是數(shù)據(jù)和索引都包含在內的。

而MyISAM里 myd和myi 就是數(shù)據(jù)和索引分開存放的。

特點：

1. 使用記錄主鍵值的大小進行記錄和頁的排序，這包括三個方面的含義：

• 頁內 的記錄是按照主鍵的大小順序排成一個 單向鏈表 。
• 各個存放 用戶記錄的頁 也是根據(jù)頁中用戶記錄的主鍵大小順序排成一個 雙向鏈表 。
• 存放 目錄項記錄的頁 分為不同的層次，在同一層次中的頁也是根據(jù)頁中目錄項記錄的主鍵 大小順序排成一個 雙向鏈表 。

（同一層級的頁，頁內記錄單鏈表連接，頁與頁之間是雙鏈表，跨層級(目錄項頁，用戶記錄頁等)以樹結構存儲）

2. B+樹的 葉子節(jié)點 存儲的是完整的用戶記錄。 所謂完整的用戶記錄，就是指這個記錄中存儲了所有列的值（包括隱藏列）。

我們把具有這兩種特性的B+樹稱為 聚簇索引，所有完整的用戶記錄都存放在這個聚簇索引的 葉子節(jié)點處。這種聚簇索引并不需要我們在Mysql語句中顯示的使用 INDEX 語句去創(chuàng)建，InnoDB存儲引擎會自動為我們創(chuàng)建聚簇索引(因為主鍵的存在)。

優(yōu)點：

• 數(shù)據(jù)訪問更快 ，因為聚簇索引將索引和數(shù)據(jù)保存在同一個B+樹中，因此從聚簇索引中獲取數(shù)據(jù)比非 聚簇索引更快
• 聚簇索引對于主鍵的 排序查找 和 范圍查找 速度非?？?存儲的時候，就是有序的，查找用到了二分法，時間復雜度O(log2n))
• 按照聚簇索引排列順序，查詢顯示一定范圍數(shù)據(jù)的時候，由于數(shù)據(jù)都是緊密相連，數(shù)據(jù)庫不用從多 個數(shù)據(jù)塊中提取數(shù)據(jù)，所以 節(jié)省了大量的io操作 。（詳情，可以了解磁盤讀取數(shù)據(jù)的過程，涉及操作系統(tǒng)磁盤管理）

缺點

• 插入速度嚴重依賴于插入順序 ，按照主鍵的順序插入是最快的方式，否則將會出現(xiàn)頁分裂，嚴重影 響性能。因此，對于InnoDB表，我們一般都會定義一個自增的ID列為主鍵
• 更新主鍵的代價很高 ，因為將會導致被更新的行移動。因此，對于InnoDB表，我們一般定義主鍵為 不可更新（這也是為什么索引不適合在頻繁增刪改的表中使用）
• 二級索引訪問需要兩次索引查找 ，第一次找到主鍵值，第二次根據(jù)主鍵值找到行數(shù)據(jù)

限制：

• 對于Mysql數(shù)據(jù)庫，目前只有InnoDB支持聚簇索引，而MyISAM并不支持聚簇索引
• 由于數(shù)據(jù)物理存儲排序方式只能有一種，索引每個Mysql的表只能有一個聚簇索引。一般情況為該表的主鍵
• 如果沒有定義主鍵，InnoDB會選擇 非空的唯一索引 代替。如果沒有這樣的索引，InnoDB會隱式的定義一個主鍵，作為聚簇索引。
• 為了充分利用聚簇索引的聚簇特性，所以InnoDB表的主鍵盡量選擇有序的順序id，而不建議用無序id，比如UUID、MD5,HASH,字符串列作為主鍵無法保證數(shù)據(jù)的順序增長。

2、二級索引（輔助索引、非聚簇索引）

聚簇索引只能在搜索條件是主鍵的情況才能發(fā)揮作用，因為B+tree中的數(shù)據(jù)都是按照主鍵進行排序的，那么入股我們想以別的列 作為搜索條件該怎么辦？肯定不能從頭到尾沿著鏈表依次遍歷記錄。

我們可以多建幾棵B+tree，不同的B+tree采用不同的排序規(guī)則，比如我們用C2列的大小 作為數(shù)據(jù)頁中記錄的排序規(guī)則，再建一棵B+tree，如下圖（藍色）：

索引中是不直接存儲表中的用戶記錄的，上圖二級索引中，葉子節(jié)點并沒有完整存儲用戶記錄。

此B+樹與上邊的聚簇索引有幾處不同：

• 使用記錄C2的大小進行記錄和頁的排序，這包括三方面的意義
  • 頁內記錄是按照C2的大小排列成的單向鏈表
  • 各個存放用戶記錄的頁也是根據(jù)頁中記錄的C2列排列成一個雙向鏈表
  • 存放目錄項記錄的頁 分為不同層次，再同一層次中的頁也是根據(jù)頁中目錄項記錄的C2列大小順序排成的雙向鏈表
• B+樹的葉子節(jié)點 存儲的并不是完整的用戶記錄，而是C2+主鍵 這兩個列的值。
• 目錄項記錄中不再是主鍵+頁號，而變成了C2+頁號。

現(xiàn)在我們根據(jù)C2的值為條件，進行查找，比如 想找到C2列的值為4的記錄：

SELECT C1，C2，C3 FROM? ?`table_name`? WHERE C2=4;

1. 確定目錄項記錄頁? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 根據(jù)根頁面，也就是44頁，可以快速定位到目錄項所在的頁為頁42（2<4<9）
2. 通過目錄項記錄頁確定用戶真實記錄所在頁? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 在頁42中可以快速定位到使用存儲用戶記錄的頁，但是C2列并沒有唯一性約束，所以C2列值為4的記錄可能分布在多個數(shù)據(jù)頁中，又因為（2<4<=4）,所以確定實際存儲存儲用戶記錄的頁，在頁34和頁35中。
3. 在真實存儲用戶記錄的頁中定位到具體的記錄。? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?到頁34和頁35中確定具體的記錄。
4. 但是B+樹的葉子節(jié)點中的記錄只存儲了C2和C1（也就是主鍵）兩個列，所以，我們必須要根據(jù)主鍵值去 聚簇索引 中再查找一遍完整的用戶記錄(回表)。

概念：回表

我們根據(jù)這個以c2列大小排序的B+樹只能確定我們要查找記錄的主鍵值，所以如果我們想根 據(jù)c2列的值查找到完整的用戶記錄的話，仍然需要到 聚簇索引 中再查一遍，這個過程稱為 回表 。也就 是根據(jù)c2列的值查詢一條完整的用戶記錄需要使用到 2 棵B+樹！

問題：為什么我們還需要一次 回表 操作呢？直接把完整的用戶記錄放到葉子節(jié)點不OK嗎？

如果把完整的用戶記錄都放到葉子節(jié)點中，可以不用回表，但是太占空間了，相當于每建立一棵B+tree都需要把所有的用戶記錄都拷貝一遍，這就太浪費存儲空間了。

因為這種按照非主鍵列建立的 B+樹需要一次回表操作，才可以定位到完整的用戶記錄，所以這種B+tree也稱為二級索引，或者輔助索引。

由于我們使用的是C2列的大小作為B+樹的排序規(guī)則，所以我們也稱這個B+樹是為C2建立的索引。

非聚簇索引的存在，不影響數(shù)據(jù)在聚簇索引中的組織，所以一張表可以有多個非聚簇索引。

小結：聚簇索引和非聚簇索引的區(qū)別：

1、聚簇索引的 葉子節(jié)點 存儲的就是我們的 數(shù)據(jù)記錄，非聚簇索引的葉子節(jié)點存儲的是 數(shù)據(jù)位置 。非聚簇索引不會影響數(shù)據(jù)表的物理存儲順序。
2、一個表 只能有一個聚蹊索引，因為只能有一種排序存儲的方式，但可以有 多個非聚簇索引，也就是多個索引目錄提供數(shù)據(jù)檢索。
3、使用聚簇索引的時候，數(shù)據(jù)的 查詢效率高，但如果對數(shù)據(jù)進行插入，刪除，更新等操作，效率會比非聚簇索引低。

3、聯(lián)合索引

我們也可以同時以多個列的大小作為排序規(guī)則，也就是同時為多個列建立索引，比方說我們想讓B+樹按 照 c2和c3列 的大小進行排序，這個包含兩層含義：

• 先把各個記錄和頁按照c2列進行排序。
• 在記錄的c2列相同的情況下，采用c3列進行排序

注意一點，以c2和c3列的大小為排序規(guī)則建立的B+樹稱為 聯(lián)合索引 ，本質上也是一個二級索引。它的意 思與分別為c2和c3列分別建立索引的表述是不同的，不同點如下：

• 建立 聯(lián)合索引 只會建立如上圖一樣的1棵B+樹。
• 為c2和c3列分別建立索引會分別以c2和c3列的大小為排序規(guī)則建立2棵B+樹。

索引的代價

索引是個好東西，可不能亂建，它在空間和時間上都會有消耗：

空間上的代價

每建立一個索引都要為它建立一棵B+樹，每一棵B+樹的每一個節(jié)點都是一個數(shù)據(jù)頁，一個頁默認會 占用 16KB 的存儲空間，一棵很大的B+樹由許多數(shù)據(jù)頁組成，那就是很大的一片存儲空間。

時間上的代價???????

時間上的代價 每次對表中的數(shù)據(jù)進行 增、刪、改 操作時，都需要去修改各個B+樹索引。而且我們講過，B+樹每 層節(jié)點都是按照索引列的值 從小到大的順序排序 而組成了 雙向鏈表 。不論是葉子節(jié)點中的記錄，還 是內節(jié)點中的記錄（也就是不論是用戶記錄還是目錄項記錄）都是按照索引列的值從小到大的順序 而形成了一個單向鏈表。而增、刪、改操作可能會對節(jié)點和記錄的排序造成破壞，所以存儲引擎需 要額外的時間進行一些 記錄移位 ， 頁面分裂 、 頁面回收 等操作來維護好節(jié)點和記錄的排序。如果 我們建了許多索引，每個索引對應的B+樹都要進行相關的維護操作，會給性能拖后腿。

你是否還在尋找穩(wěn)定的海外服務器提供商？創(chuàng)新互聯(lián)www.cdcxhl.cn海外機房具備T級流量清洗系統(tǒng)配攻擊溯源，準確流量調度確保服務器高可用性，企業(yè)級服務器適合批量采購，新人活動首月15元起，快前往官網查看詳情吧

本文題目：MySql索引——索引的結構2-創(chuàng)新互聯(lián)
URL地址：http://aaarwkj.com/article20/dohojo.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供定制網站、網站改版、網站設計公司、網站維護、域名注冊、外貿建站

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)