[TOC] # 存儲引擎 ## 什么是存儲引擎 與其他數據庫例如**Oracle** 和**SQL Server**等數據庫中只有一種存儲引擎不同的是，**MySQL** 有一個被稱為“**Pluggable Storage Engine Architecture**”(可替換存儲引擎架構)的特性，也就意味著**MySQL**數據庫提供了多種存儲引擎。 用戶可以根據不同的需求為數據表選擇不同的存儲引擎，用戶也可以根據自己的需要編寫自己的存儲引擎。 **MySQL**數據庫在實際的工作中其實分為了語句分析層和存儲引擎層，其中語句分析層就主要負責與客戶端完成連接并且事先分析出SQL語句的內容和功能，而存儲引擎層則主要負責接收來自語句分析層的分析結果，完成相應的數據輸入輸出和文件操作。 簡而言之，就是如何存儲數據、如何為存儲的數據建立索引和如何更新、查詢數據等技術的實現方法。因為在關系數據庫中數據的存儲是以表的形式存儲的，所以存儲引擎也可以稱為表類型（即存儲和操作此表的類型）。 ## 存儲引擎種類 | 存儲引擎 | 說明 | | --- | --- | | **InnoDB** | 5.5版本后MySQL的默認數據庫，支持事務和行級鎖定，比MyISAM處理速度稍慢 | | **MyISAM** | 高速引擎，擁有較高的插入，查詢速度，但不支持事務 | | ISAM | MyISAM的前身，MySQL5.0以后不再默認安裝 | | Memory | 內存存儲引擎，擁有極高的插入，更新和查詢效率。但是會占用和數據量成正比的內存空間。只在內存上保存數據，意味著數據可能會丟失 | | Archive | 將數據壓縮后進行存儲，非常適合存儲大量的獨立的，作為歷史記錄的數據，但是只能進行插入和查詢操作 | ## MyISAM 引擎 * v5.7 之前：每個`MyISAM`表都存儲在磁盤上的三個文件中。這些文件的名稱以表名開頭，并具有指示文件類型的擴展名。`.frm`文件存儲表格格式。數據文件具有`.MYD`(`MYData`) 擴展名。索引文件具有`.MYI`(`MYIndex`) 擴展名。 * v5.7 之后：每個`MyISAM`表都存儲在磁盤上的兩個文件中。數據文件具有`.MYD`(`MYData`) 擴展名。索引文件具有`.MYI`(`MYIndex`) 擴展名。表定義存儲在 MySQL 數據字典中。 ~~~ CREATE TABLE t (i INT) ENGINE = MYISAM; ~~~ ### 實現 **MyISAM** 引擎使用**B+Tree**作為索引結構，葉節點的 data 域存放的是數據記錄的地址。  這里設表一共有三列，假設我們以 Col1 為主鍵，上圖是一個 MyISAM 表的**主索引（Primary key）**示意。可以看出 MyISAM 的索引文件僅僅保存數據記錄的地址。 在 MyISAM 中，**主索引**和**輔助索引（Secondary key）**在結構上沒有任何區別，只是主索引要求 key 是唯一的，而輔助索引的 key 可以重復。  **輔助索引** 同樣也是一顆 B+Tree，data 域保存數據記錄的地址。因此，MyISAM 中索引檢索的算法為首先按照 B+Tree 搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以 data 域的值為地址，讀取相應數據記錄。 MyISAM的索引方式也叫做“**非聚集**”的，之所以這么稱呼是為了與InnoDB的聚集索引區分。 ### 特性  > **不是事務安全的，不支持外鍵，如果有大量的 `select`**，MyISAM比較合適。插入數據快，空間和內存使用比較低。如果表主要是用于插入新記錄和讀出記錄，那么選擇MyISAM能實現處理高效率。如果應用的完整性、并發性要求比較低，也可以使用。 ## InnoDB 引擎 【默認引擎】 ### 實現 **InnoDB**也使用 B+Tree 作為索引結構。在 InnoDB 中，表數據文件本身就是按 B+Tree 組織的一個索引結構，這棵樹的葉節點data域保存了完整的數據記錄。InnoDB 的數據文件本身就是索引文件。  InnoDB 主索引（Primary Key）葉節點包含完整的數據記錄，這種索引方式叫做 **聚簇索引**。 因為InnoDB的數據文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則 MySQL 系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含字段作為主鍵，這個字段長度為6個字節，類型為長整形。  InnoDB 的輔助索引 data 域存儲相應記錄主鍵的值而不是地址，換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。 聚集索引這種實現方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。 **為什么不建議使用過長的字段作為主鍵？** 因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大 ### 特性  > **支持事務處理，支持外鍵，行鎖，支持崩潰修復能力和并發控制**。如果需要對事務的**完整性要求比較高**（比如銀行），要求實現并發控制（比如售票），那選擇InnoDB有很大的優勢。如果需要**頻繁的`Insert`、`Update`操作的數據庫**，也可以選擇InnoDB，因為支持事務的提交（commit）和回滾（rollback）。 ## MyISAM 和 InnoDB 的區別 **MySQL 5.5.5 之前，MyISAM 是 MySQL 的默認存儲引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默認存儲引擎。** * **行級鎖** MyISAM 只有表級鎖(table-level locking)，而 InnoDB 支持行級鎖(row-level locking)和表級鎖,默認為行級鎖。 * **事務** MyISAM 不提供事務支持。 * **外鍵** MyISAM 不支持外鍵。 > 外鍵對于維護數據一致性非常有幫助，但是對性能有一定的損耗。通常情況下，不建議在實際生產項目中使用外鍵 * **數據庫異常崩潰后的安全恢復** MyISAM 不支持 使用 InnoDB 的數據庫在異常崩潰后，數據庫重新啟動的時候會保證數據庫恢復到崩潰前的狀態。這個恢復的過程依賴于`redo log` * **索引實現** 都是使用 B+Tree 作為索引結構，但是兩者實現方式不一樣。 InnoDB 引擎，數據文件本身就是索引文件。MyISAM 索引文件和數據文件是分離的，其表數據文件本身就是按 B+Tree 組織的一個索引結構，樹的葉節點 data 域保存了完整的數據記錄。 # 索引結構（方法、算法） MySQL 常用兩種索引結構 BTree 和 Hash 。 | 存儲引擎 | 顯示支持索引結構 | | --- | --- | | InnoDB | BTree | | MyISAM | BTree | | Memory | HASH，BTREE | ## Hash Hash索引的底層實現是由Hash表來實現的，非常適合以 key-value 的形式查詢，也就是單個key 查詢，或者說是等值查詢。 Hash 索引可以比較方便的提供等值查詢的場景,由于是一次定位數據，不像BTree索引需 要從根節點到枝節點，最后才能訪問到頁節點這樣多次IO訪問，所以檢索效率遠高于BTree索引。但是對于范圍查詢的話，就需要進行全表掃描了。 ### 特性 1. hash 索引僅僅能滿足 `=`，`IN`，`<=>` 查詢，不能會用范圍查詢 2. 聯合索引中， Hash 索引不能利用部分索引鍵查詢。對于聯合索引中的多個列，Hash 是要么全部使用，要么全部不使用，并不支持 BTree 支持的聯合索引的最左前綴，也就是聯和索引的前面一個或者幾個索引鍵進行查詢時， Hash 索引無法被利用。 3. Hash索引無法避免數據的排序操作 由于Hash索引中存放的是經過Hash計算之后的Hash值，而且Hash值的大小關系并不一定和Hash運算前的鍵值完全一樣，所以數據庫無法利用索引的數據來避免任何排序運算。 4. Hash索引任何時候都不能避免表掃描 Hash索引是將索引鍵通過Hash運算之后，將Hash運算結果的Hash值和所對應的行指針信息存放于一個Hash表中，由于不同索引鍵存在相同Hash值，所以即使滿足某個Hash鍵值的數據的記錄條數，也無法從Hash索引中直接完成查詢，還是要通過訪問表中的實際數據進行比較，并得到相應的結果。 5. Hash索引遇到大量Hash值相等的情況后性能并不一定會比BTree高 對于選擇性比較低的索引鍵，如果創建Hash索引，那么將會存在大量記錄指針信息存于同一個Hash值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表數據訪問，而造成整體性能底下。 ## B+Tree 在B+Tree中，所有數據記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只存儲key值信息，這樣可以大大加大每個節點存儲的key值數量，降低B+Tree的高度。  # 索引方式 ## **聚集索引** 指索引項的排序方式和表中數據記錄排序方式一致的索引。聚集索引的葉子節點存儲了整個行數據(即:一張表只能有一個聚集索引)。 什么叫索引項的排序方式和表中數據記錄排序方式一致呢？ 我們把一本字典看做是數據庫的表，那么字典的拼音目錄就是聚集索引，它按照A-Z排列。實際存儲的字也是按A-Z排列的。這就是索引項的排序方式和表中數據記錄排序方式一致。 > 對于Innodb，主鍵毫無疑問是一個聚集索引。但是當一個表沒有主鍵，或者沒有一個索引，Innodb會如何處理呢。請看如下規則: > * 如果一個主鍵被定義了，那么這個主鍵就是作為聚集索引。 > * 如果沒有主鍵被定義，那么該表的第一個唯一非空索引被作為聚集索引。 > * 如果沒有主鍵也沒有合適的唯一索引，那么innodb內部會生成一個隱藏的主鍵作為聚集索引，這個隱藏的主鍵是一個6個字節的列，該列的值會隨著數據的插入自增。 ## **非聚集索引** 非聚集索引中索引的邏輯順序與磁盤上行的物理存儲順序不同，一個表中可以擁有多個非聚集索引。葉子節點并不包含行記錄的全部數據。葉子節點除了包含鍵值以外，還存儲了一個指向改行數據的聚集索引建的書簽。 # 索引類型 ## 普通索引 （INDEX） 最基本的索引，沒有任何限制。 1. 直接創建 ~~~ CREATE INDEX [索引名] ON [表名]([字段]) ~~~ 2. 修改表結構--添加索引 ~~~ ALTER TABLE [表名] ADD INDEX [索引名] ON ([字段名]) ~~~ 3. 創建表時創建 ~~~ CREATE TABLE `users` ( ? ?`id` int(11) NOT NULL AUTO_INCREMENT , ? ?`name` char(18) CHARACTER NOT NULL , ? ?INDEX name_index (name) ) ~~~ ## 唯一索引（UNIQUE） 索引列的值必須唯一，但允許有空值。 1. 創建唯一索引 ~~~ CREATE UNIQUE INDEX [索引名稱] ON [表名]([字段名]) ~~~ 2. 修改表結構 ~~~ ALTER TABLE [表名] ADD UNIQUE [索引名] ON ([字段名]) ~~~ 3. 創建表的時候直接指定 ~~~ CREATE TABLE `users` ( ? ?`id` int(11) NOT NULL AUTO_INCREMENT , ? ??`name` char(18) CHARACTER NOT NULL , ? ? UNIQUE unique_name (name) ); ~~~ ## 主鍵索引 （PRIMAY KEY） 一個表只能有一個主鍵，不允許有空值，是一種特殊的唯一索引。 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT , PRIMARY KEY (`id`) ); ## 聯合索引 指多個字段上創建的索引，只有在查詢條件中使用了創建索引時的第一個字段，索引才會被使用。使用組合索引時遵循**最左前綴集合** ~~~ ALTER TABLE `users` ADD INDEX name_city_age (name,city,age); ~~~ ## 全文索引（FULLTEXT） 主要用來查找文本中的關鍵字，而不是直接與索引中的值相比較。fulltext索引跟其它索引大不相同，它更像是一個搜索引擎，而不是簡單的where語句的參數匹配 # 索引優化 ## 回表 根據上面的索引說明，聚簇索引和非聚簇索引的查詢區別 * 如果語句是 `select * from T where ID=500`，即主鍵查詢方式，則只需要搜索 ID 這棵 B+ 樹； * 如果語句是 `select * from T where k=5`，即普通索引查詢方式，則需要先搜索 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜索一次。這個過程稱為**回表**。 ## 索引下推（Index Condition Pushdown） 當 MySQL 使用一個索引來檢索表中的行時，可以使用 **ICP** 作為一種優化方案。5.6以后推出的功能。 在這里有張用戶表 user，記錄著用戶的姓名，性別，身高，年齡等信息。表中 id 是自增主鍵，(name,sex) 是聯合索引。在這里用 1 表示男，2 表示女。現在需要查找所有姓王的男性信息。 ### 沒有索引下推時： ~~~ select * from user where name like '王%' and sex = 1; ~~~ **查詢原理：** 根據聯合索引最左前綴原則，在非主鍵索引樹上找到第一個滿足條件的值時，通過葉子節點記錄的主鍵值再回到主鍵索引樹上查找到對應的行數據，再對比是否為當前所要查找的性別。 每條數據都會回表。  ### 有索引下推時： 索引下推就是只有符合條件再進行回表，對索引中包含的字段先進行判斷，不符合條件的跳過。減少了不必要的回表操作。  > 索引下推是在**非主鍵索引**上的優化，可以有效減少回表的次數，大大提升了查詢的效率。 ## 覆蓋索引 * `select` 查詢的數據列已創建索引 * 一個索引包含了（或覆蓋了）滿足查詢語句中字段與條件的數據就叫做**覆蓋索引**。 * 是非聚集組合索引的一種形式，它包括在查詢里的 `select`、`join`和`where`子句用到的所有列（即建立索引的字段正好是覆蓋查詢語句[select子句]與查詢條件[Where子句]中所涉及的字段，也即，索引包含了查詢正在查找的所有數據）。 不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引的列，而哈希索引、空間索引和全文索引等都不存儲索引列的值，所以MySQL只能使用 B-Tree 索引做覆蓋索引 當發起一個被索引覆蓋的查詢(也叫作索引覆蓋查詢)時，在`EXPLAIN`的 **Extra**列可以看到“Using index” 的信息 ~~~ mysql> explain select id from users where name = '趙四'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set (0.04 sec) ~~~