首先，MySql 支持多種存儲引擎，最為常用的是 InnoDB，MyISAM 也需要了解，其他的存儲引擎包括 Archive 等等都要有個印象。

各種存儲引擎對于索引的支持也不相同，總結下來，MySQL 的索引主要由三種類型：B 樹索引，Hash 索引，全文索引。我們隻關注 BTree 索引，因為這是我們平常使用 MySql 時主要的打交道的方式。

MySql 中的 B 樹索引的物理文件大多是以 Balance Tree 的結構來存儲的，也就是所有的實際的數據都存放于 Tree 的葉子節點中，到任何一個葉子節點的最短路徑長度都是完全相同的。各個數據庫或者存儲引擎都會對 B 樹索引的存儲結構稍加改造，比如 innodb 的 B 樹索引實際使用的 B 樹，也就是在 B 樹的結構上做了很小的改動。除了在每一個葉子節點上存放索引的相關信息之外，還存儲了隻想該葉子節點的後一個葉子節點的指針信息（增加了順序訪問），這也是為了加快檢索多個相鄰的葉子節點的效率考慮。

什麼是索引

定義：索引是為了幫助 MySql 高效獲得數據的數據結構。

目的：索引的目的是為了快速找到對應的數據了。就比如我們查字典，一定是按照首個字母或者拼音去找而不是翻遍整個字典。

1. 索引原理

索引的原理就是通過不斷的縮小範圍從而篩選出想要的結果從而避免對整個文件的查找。同時把随機的事件變為順序的事件，也就是我們總是通過同一種查找方式來鎖定數據。

數據庫的索引更為複雜，因為不僅面臨着等值查詢，還有範圍查詢（<，>，between），模糊查詢（like），并集查詢（or），多值匹配（in）等等。我們回想字典的例子，能不能把數據分成段，然後分段查詢那？比如将 1000 條數據中的 1 到 100 分為第一段，101 到 200 分為第二段...... 這樣查第 105 條數據隻需要查第一段就可以了。如果是 10000 條那，怎麼分段？稍微有算法基礎的同學可能會想到搜索樹，平均複雜度是 logN，性能不錯，但是有時為了提高性能，會把部分數據讀入内存中來計算，我們知道訪問磁盤的成本大概是訪問内存的十萬倍左右，所以簡單的搜索樹并不能滿足複雜的應用場景。

2. B 樹索引結構

上面我們說過簡單的搜索樹并不能滿足數據庫的使用場景。我們需要索引做什麼那？那就是每次查找數據時能把磁盤 IO 次數控制在一個很小的數量級，最好是常數數量級。因此，一個高度可控的多路搜索樹 b 樹産生了。

每個磁盤塊中包含幾個數據項（深藍色）和指針（黃色），磁盤塊1 包含數據項 17 和 35，包含指針 P1，P2，P3，P1 表示小于 17 的磁盤塊，P2 表示在 17 和 35 之間的磁盤塊，P3 表示大于 35 的磁盤塊。

真實的數據存在于葉子節點即 3，5，9，10，13，15，28，29，36，60，75，79，90，99。非葉子節點不存儲真實數據，隻存儲搜索方向的數據項。

3. B 樹的查找過程

如果我們想要查找 29，那麼首先會把磁盤1 家在到内存，此時發生一次 IO，内存中用二分查找确定 29 在 17 和 35 之間，鎖定磁盤塊 1 的 P2 指針，加載磁盤塊 3 到内存，發生第二次 IO。然後鎖定磁盤塊 8，發生第三次 IO，找到了 29。結束查詢，總共發生三次 IO。3 層的 B 樹可以表示上百萬的數據，所以上百萬的數據的查詢隻需要三次 IO 就可以完成了，如果沒有索引，每個數據項都要發生一次 IO，那麼就需要百萬次的 IO，成本非常高。

MySql 的索引實現

MySql 中，索引是存儲引擎級别的概念，不同的存儲引擎對索引的實現方式是不同的。我們主要針對 MyISAM 和 InnoDB 兩個存儲引擎的索引實現來讨論。

1. MyISAM 索引實現

MyISAM 引擎使用的是 B 樹作為索引結構，葉子節點的 data 域存放的是數據記錄的地址。

假設我們以 Col1 為主鍵，那麼上圖就是 MyISAM 表的主鍵索引。MyISAM 存儲引擎中，主鍵索引和輔助索引在結構上是沒有任何區别的，隻是主索引要求 key 是唯一的，而輔助索引的 key 可以重複。我們來看建立在 Col2 上的輔助索引。

總結：同樣也是一顆 B 樹，data 域保存數據記錄的地址。因此，MyISAM 的索引的算法首先按照 B 樹搜索算法搜索索引，如果指定的 Key 存在，則取出其中 data 域的值，然後讀取相關記錄。

MyISAM 的索引方式也叫做 “非聚集” 的，之所以這麼稱呼是為了與 InnoDB 的聚集索引區分的。

2. InnoDB 索引實現

雖然 InnoDB 也使用 B 樹作為索引結構，但是具體實現方式與 MyISAM 截然不同。

第一個區别是 InnoDB 的數據文件本身就是索引文件。從上文可以知道，MyISAM 索引文件和數據文件是分離的，索引文件僅保存數據記錄地址。而在 InnoDB 中，表數據文件本身就是 B 樹組織的一個索引結構，這棵樹的葉子節點 data 域保存了完整的數據結構。這個索引的 key 是數據表的主鍵，因此 InnoDB 表數據文件本身就是主索引。

上圖是 InnoDB 主索引的示意圖，可以看到葉節點包含了完整的數據記錄。這種索引也叫做聚集索引。因為 InnoDB 的數據文件本身要按主鍵聚集，所以 InnoDB 要求必須有主鍵索引（MyISAM 可以沒有）。如果沒有顯示指定，MySql 系統會自動選擇一個可以唯一标識數據記錄的列作為主鍵，如果不存在這種列，MySQL 自動為 InnoDB 表生成一個隐含字段作為主鍵。

第二個與 MyISAM 索引的不同是 InnoDB 的輔助索引 data 域存儲相應記錄主鍵的值而不是地址。InnoDB 的所有輔助索引都引用主鍵作為 data 域。

這裡以英文字符的 ASCII 碼作為比較标準。聚集索引這種實現方式使得按主鍵搜索十分搞笑，但是輔助索引搜索需要兩遍索引：先檢索輔助索引獲得主鍵，然後用主鍵索引檢索記錄。

熟悉了 InnoDB 的索引實現後，就明白了為什麼不建議使用過長的字段作為主鍵，因為所有輔助索引都使用主索引，過長的主索引會令輔助索引變的過大。再比如，用非單調的字段作為 InnoDB 的主鍵不是個好主意，非單調的主鍵造成插入新紀錄時數據文件維持 B 樹的特性而頻繁的分裂調整，使用自增字段作為主鍵是一個很好的選擇。

3. InnoDB索引和MyISAM索引的區别

一是主索引的區别，InnoDB的數據文件本身就是索引文件。而MyISAM的索引和數據是分開的。

二是輔助索引的區别，InnoDB的輔助索引data域存儲相應記錄主鍵的值而不是地址。而MyISAM的輔助索引和主索引沒有多大區别。

建立索引常用技巧

1. 最左匹配原則

非常重要的原則。mysql 會一直向右匹配直到遇到範圍查詢（<，>，between，like）就停止匹配，比如 a=1 and b = 2 and c > 3 and b = 4 如果建立（a，b，c，d）順序的索引，d 是用不到索引的，如果建立（a，b，d，c）的索引都可以用到。

2. = 和 in 可以亂序

比如 a = 1 and b = 2 and c = 3 建立 （a，b，c）所以可以任意順序。

3. 盡量選擇區分度高的列作為索引

字段不重複的比例不能太小，唯一鍵的區分度是 1，而一些狀态，性别字段在大數據面前區分度就是 0。

4. 索引不能參與計算

比如 from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引，原因很簡單，b 樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大，應該把語句寫成 create_time = unix_timestamp(’2014-05-29’)。

SQL 調優

一般要進行 SQL 調優，那麼就是有慢查詢的 SQL，系統或者 server 可以開啟慢查詢日志。

通過慢查詢記錄能夠記錄一些執行時間比較久的 SQL 語句，找出這些語句不意味着工作結束了，我們通常使用 explain 這個命令來查看這些 SQL 語句的執行計劃，查看該 SQL 語句有沒有使用索引，有沒有做全表掃描。

mysql> explain select * from servers; ---- ------------- --------- ------ --------------- ------ --------- ------ ------ ------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---- ------------- --------- ------ --------------- ------ --------- ------ ------ ------- | 1 | SIMPLE | servers | ALL | | | | | 1 | | ---- ------------- --------- ------ --------------- ------ --------- ------ ------ ------- 1 row in set (0.03 sec)

各個字段的含義：

• id：表示 SQL 執行的順序的标識。
• select_type：表示産訊中每個 select 子句的類型。
• table：顯示這一行的數據是關于哪張表的，有時不是真實的表名字。
• type：表示MySQL在表中找到所需行的方式，又稱“訪問類型”。常用的類型有：ALL, index, range, ref, eq_ref, const, system, （從左到右，性能從差到好）。
• possible_keys：指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引将被列出，但不一定被查詢使用
• Key：key列顯示MySQL實際決定使用的鍵（索引），如果沒有選擇索引，鍵是。
• key_len：表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表内檢索出的）。
• ref：表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。
• rows：表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數，理論上行數越少，查詢性能越好。
• Extra：該列包含MySQL解決查詢的詳細信息。

EXPALIN隻能解釋SELECT操作，其他操作要重寫為SELECT後查看執行計劃。

End.

作者：當年明月

來源：博客園

零基礎入職數據分析就業班

課程的形式主要是“直播 錄播”

報名專享：課程項目作業 1v1班主任監督學習 愛數據學院學員專屬網站 班級答疑群

課程結束後能熟練掌握SQL、Python、Excel、PPT等工具

适合人群：

1.轉行（崗位相關，專業相關、對數據分析感興趣）

2.從事數據分析工作，但是需要提升技能以及增加實戰經驗

3.應屆畢業生入職數據分析