mysql資料庫索引引擎（mysql常用資料庫引擎）

本文目錄一覽：

• 1、MySQL索引
• 2、如何查看mysql資料庫的引擎
• 3、MySQL資料庫的四類索引？
• 4、MySQL 索引是怎麼實現的？
• 5、MYSQL資料庫索引類型都有哪些
• 6、MySql 索引（聚集索引，輔助索引，聯合索引，覆蓋索引..）

MySQL索引

MySQL的Innodb存儲引擎的索引分為聚集索引和非聚集索引兩大類

特點：B+樹葉子節點存儲行數據

一個表中，必須有一個聚集索引，只能有一個聚集索引，Innodb通常把一個表的主鍵索引作為聚集索引，如果沒有主鍵InnoDB會選擇一個唯一索引代替。如果沒有這樣的索引，InnoDB會隱式的定義一個主鍵來作為聚集索引，這個欄位為6個位元組，類型為長整形。

利用主鍵索引查找行數據是最快的，建議使用自增主鍵原因是利於索引樹的構建（主鍵自增寫入時新插入的數據不會影響到原有頁，插入效率高；但是如果主鍵是無序的或者隨機的，那每次的插入可能會導致原有頁頻繁的分裂，影響插入效率）

特點：B+樹葉子節點存儲主鍵ID

一個表中可以有多個非聚集索引，每個非聚集索引即是一棵B+樹

通過非聚集索引查找數據時，需要先在非聚集索引上找到主鍵ID，再從聚集索引獲取行數據，這個過程就稱之為回表

B樹索引中的B樹實際上是B+樹，至於為什麼使用B+樹而不使用B樹或者紅黑樹的原因在另外的文章中有提及。

特點：

特點：類似JDK中的HashMap，但無法支持範圍查詢

特點：使用的演算法仍然是B樹索引，不同的就是索引列的值必須唯一

對於普通索引來說，查找到滿足條件的第一個記錄後，需要查找下一個記錄，直到碰到第一個不滿足條件的記錄。

對於唯一索引來說，由於索引定義了唯一性，查找到第一個滿足條件的記錄後，就會停止繼續檢索，提升索引性能

另外插入行時會構建該唯一索引，假如索引值重複將插入失敗，適合業務上做唯一性檢驗

通過建立倒排索引，可以極大的提升檢索效率,解決判斷欄位是否包含的問題，但是業務上一般都不採用這種索引，而是使用ES處理全文搜索需求

僅對某個特定欄位建立的索引，如(biz_id)

對多個欄位建立的索引，如(biz_id,type)

如何查看mysql資料庫的引擎

一般情況下，mysql會默認提供多種存儲引擎,你可以通過下面的查看:

看你的mysql現在已提供什麼存儲引擎:

mysql show engines;

看你的mysql當前默認的存儲引擎:

mysql show variables like ‘%storage_engine%’;

你要看某個表用了什麼引擎(在顯示結果里參數engine後面的就表示該表當前用的存儲引擎):

mysql show create table 表名;

MySQL資料庫引擎詳解

作為Java程序員，MySQL資料庫大家平時應該都沒少使用吧，對MySQL資料庫的引擎應該也有所了解，這篇文章就讓我詳細的說說MySQL資料庫的Innodb和MyIASM兩種引擎以及其索引結構。也來鞏固一下自己對這塊知識的掌握。

Innodb引擎

Innodb引擎提供了對資料庫ACID事務的支持，並且實現了SQL標準的四種隔離級別，關於資料庫事務與其隔離級別的內容請見資料庫事務與其隔

離級別這篇文章。該引擎還提供了行級鎖和外鍵約束，它的設計目標是處理大容量資料庫系統，它本身其實就是基於MySQL後台的完整資料庫系統，MySQL

運行時Innodb會在內存中建立緩衝池，用於緩衝數據和索引。但是該引擎不支持FULLTEXT類型的索引，而且它沒有保存表的行數，當SELECT

COUNT(*) FROM

TABLE時需要掃描全表。當需要使用資料庫事務時，該引擎當然是首選。由於鎖的粒度更小，寫操作不會鎖定全表，所以在並發較高時，使用Innodb引擎

會提升效率。但是使用行級鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的範圍，InnoDB表同樣會鎖全表。

MyIASM引擎

MyIASM是MySQL默認的引擎，但是它沒有提供對資料庫事務的支持，也不支持行級鎖和外鍵，因此當INSERT(插入)或UPDATE(更

新)數據時即寫操作需要鎖定整個表，效率便會低一些。不過和Innodb不同，MyIASM中存儲了表的行數，於是SELECT COUNT(*)

FROM

TABLE時只需要直接讀取已經保存好的值而不需要進行全表掃描。如果表的讀操作遠遠多於寫操作且不需要資料庫事務的支持，那麼MyIASM也是很好的選

擇。

兩種引擎的選擇

大尺寸的數據集趨向於選擇InnoDB引擎，因為它支持事務處理和故障恢復。資料庫的大小決定了故障恢復的時間長短，InnoDB可以利用事務日誌

進行數據恢復，這會比較快。主鍵查詢在InnoDB引擎下也會相當快，不過需要注意的是如果主鍵太長也會導致性能問題，關於這個問題我會在下文中講到。大

批的INSERT語句(在每個INSERT語句中寫入多行，批量插入)在MyISAM下會快一些，但是UPDATE語句在InnoDB下則會更快一些，尤

其是在並發量大的時候。

Index——索引

索引（Index）是幫助MySQL高效獲取數據的數據結構。MyIASM和Innodb都使用了樹這種數據結構做為索引，關於樹我也曾經寫過一篇文章樹是一種偉大的數據結構，只是自己的理解，有興趣的朋友可以去閱讀。下面我接著講這兩種引擎使用的索引結構，講到這裡，首先應該談一下B-Tree和B+Tree。

B-Tree和B+Tree

B+Tree是B-Tree的變種，那麼我就先講B-Tree吧，相信大家都知道紅黑樹，這是我前段時間學《演算法》一書時，實現的一顆紅黑樹，大家

可以參考。其實紅黑樹類似2,3-查找樹，這種樹既有2叉結點又有3叉結點。B-Tree也與之類似，它的每個結點做多可以有d個分支（叉），d稱為B-

Tree的度，如下圖所示，它的每個結點可以有4個元素，5個分支，於是它的度為5。B-Tree中的元素是有序的，比如圖中元素7左邊的指針指向的結點

中的元素都小於7，而元素7和16之間的指針指向的結點中的元素都處於7和16之間，正是滿足這樣的關係，才能高效的查找：首先從根節點進行二分查找，找

到就返回對應的值，否則就進入相應的區間結點遞歸的查找，直到找到對應的元素或找到null指針，找到null指針則表示查找失敗。這個查找是十分高效

的，其時間複雜度為O(logN)（以d為底，當d很大時，樹的高度就很低），因為每次檢索最多只需要檢索樹高h個結點。

接下來就該講B+Tree了，它是B-Tree的變種，如下面兩張圖所示：

vcHLx/i85LLp0a/Qp8LKoaM8L3A+DQo8aDMgaWQ9″myisam引擎的索引結構”MyISAM引擎的索引結構

MyISAM引擎的索引結構為B+Tree，其中B+Tree的數據域存儲的內容為實際數據的地址，也就是說它的索引和實際的數據是分開的，只不過是用索引指向了實際的數據，這種索引就是所謂的非聚集索引。

Innodb引擎的索引結構

MyISAM引擎的索引結構同樣也是B+Tree，但是Innodb的索引文件本身就是數據文件，即B+Tree的數據域存儲的就是實際的數據，這種索引就是聚集索引。這個索引的key就是數據表的主鍵，因此InnoDB表數據文件本身就是主索引。

因為InnoDB的數據文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含欄位作為主鍵，這個欄位長度為6個位元組，類型為長整形。

並且和MyISAM不同，InnoDB的輔助索引數據域存儲的也是相應記錄主鍵的值而不是地址，所以當以輔助索引查找時，會先根據輔助索引找到主

鍵，再根據主鍵索引找到實際的數據。所以Innodb不建議使用過長的主鍵，否則會使輔助索引變得過大。建議使用自增的欄位作為主鍵，這樣B+Tree的

每一個結點都會被順序的填滿，而不會頻繁的分裂調整，會有效的提升插入數據的效率。

MySQL資料庫的四類索引？

index —- 普通索引,數據可以重複，沒有任何限制。

unique —- 唯一索引,要求索引列的值必須唯一，但允許有空值；如果是組合索引，那麼列值的組合必須唯一。

primary key —- 主鍵索引,是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值，一般是在創建表的同時創建主鍵索引。

組合索引 —- 在多個欄位上創建的索引，只有在查詢條件中使用了創建索引時的第一個欄位，索引才會被使用。

fulltext —- 全文索引,是對於大表的文本域：char，varchar，text列才能創建全文索引，主要用於查找文本中的關鍵字，並不是直接與索引中的值進行比較。fulltext更像是一個搜索引擎，配合match against操作使用，而不是一般的where語句加like。

注:全文索引目前只有MyISAM存儲引擎支持全文索引，InnoDB引擎5.6以下版本還不支持全文索引

所有存儲引擎對每個表至少支持16個索引，總索引長度至少為256位元組，索引有兩種存儲類型，包括B型樹索引和哈希索引。

索引可以提高查詢的速度，但是創建和維護索引需要耗費時間，同時也會影響插入的速度，如果需要插入大量的數據時，最好是先刪除索引，插入數據後再建立索引。

MySQL 索引是怎麼實現的？

索引是滿足某種特定查找演算法的數據結構，而這些數據結構會以某種方式指向數據，從而實現高效查找數據。

具體來說 MySQL 中的索引，不同的數據引擎實現有所不同，但目前主流的資料庫引擎的索引都是 B+ 樹實現的，B+ 樹的搜索效率，可以到達二分法的性能，找到數據區域之後就找到了完整的數據結構了，所有索引的性能也是更好的。

MYSQL資料庫索引類型都有哪些

聚集索引：也稱 Clustered Index。是指關係表記錄的物理順序與索引的邏輯順序相同。由於一張表只能按照一種物理順序存放，一張表最多也只能存在一個聚集索引。與非聚集索引相比，聚集索引有著更快的檢索速度。

MySQL 里只有 INNODB 表支持聚集索引，INNODB 表數據本身就是聚集索引，也就是常說 IOT，索引組織表。非葉子節點按照主鍵順序存放，葉子節點存放主鍵以及對應的行記錄。所以對 INNODB 表進行全表順序掃描會非常快。

非聚集索引：也叫 Secondary Index。指的是非葉子節點按照索引的鍵值順序存放，葉子節點存放索引鍵值以及對應的主鍵鍵值。MySQL 里除了 INNODB 表主鍵外，其他的都是二級索引。MYISAM，memory 等引擎的表索引都是非聚集索引。簡單點說，就是索引與行數據分開存儲。一張表可以有多個二級索引。

MySql 索引（聚集索引，輔助索引，聯合索引，覆蓋索引..）

引入一個面試問題:

看完以下以後再回顧，會發現迎刃而解

Mysql 可以為每一張表設置 存儲引擎 這裡我們只說 InnoDB 存儲引擎.

由於實際情況，數據頁只能按照一棵 B+樹 進行排序， 因此每張表只能擁有一個 聚集索引（即 主鍵）。

栗子：

每個葉子節點的索引行中包含了一個書籤（bookmark）. 該書籤是用來告訴 InnoDB存儲引擎哪裡可以找到該索引對應的數據行或者說 行數據！ 由於InnoDB存儲引擎表， 是按照主鍵來構建的， 所以 ，該書籤內其實包含或者說指向了 數據行所對應的聚集索引鍵

也就是說 輔助索引的 葉結點保存了 指向對應數據的 聚集索引， 可以通過該聚集索引 找到對應的數據行

輔助索引的存在並不影響數據在聚集索引中的組織，因為每張表上可以有多個輔助索引。

當通過輔助索引來尋找數據時，InnoDB 存儲引擎會遍歷輔助索引並通過葉級別的指針獲得指向主鍵索引(聚集索引)的主鍵，然後再通過聚集索引找到一個完整的數據行。

例如：

聚集索引輔助索引關係:

： 又叫做組合索引 ， 輔助索引的一種 ， 和普通創建索引的方式一樣，不同的是 可以同時添加多列來作為索引項;

從本質上來說，聯合索引也是一課B+樹

個人理解： 所謂最左原則， 是因為 存儲引擎構建組合索引時 是根據最左邊的那一列索引項進行排序的 ，所以使用組合索引，必須滿足 條件中必須存在 最左邊那一列的索引項，這樣 才可以找到對應的索引，繼而 去尋找對應的數據

： 又叫做 索引覆蓋，InnoDB中支持覆蓋索引，即 從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。

比如 這裡沒有根據最左原則使用組合索引，但是 優化器依然進行選擇

共勉，歡迎指導謝謝~