本文源碼： GitHub·點這里 || GitEE·點這里

一、索引簡介

1、基本概念

首先要明確索引是什么：索引是一種數據結構，數據結構是計算機存儲、組織數據的方式，是指相互之間存在一種或多種特定關系的數據元素的集合，例如：鏈表，堆棧，隊列，二叉樹等等。

其次要清楚索引的作用：索引可以使存儲引擎快速找到數據記錄，這是最基本的作用，索引是對查詢速度最關鍵的影響，良好的索引設計可以使查詢的效率有質的飛越。

索引的使用：如果查詢語句使用所有，MySQL會在索引的數據結構上查詢，如果查詢到，就返回包含該索引的數據行。

2、索引的優點

• 唯一或者主鍵索引,保證列數據的唯一性
• 減少數據掃描量，快速查詢數據；
• 數據有序的索引，可以將隨機IO變成順序IO；
• 有效的索引查詢，可以避免排序和臨時表；

3、索引分類

索引的種類非常多，如何分類取決多個場景和不同的角度，常見的劃分如下：

• 產生作用：主鍵索引，普通索引，非空索引，全文索引；
• 覆蓋字段：單列索引，組合索引；
• 數據結構：B-Tree索引，哈希索引，R-Tree索引；

注意：索引的實現是在存儲引擎層面，相同的索引在不同的存儲引擎中，其實現方式可能都是不一樣的。

二、索引用法詳解

1、不同索引特點

普通索引

基本的索引，沒有任何使用限制，主要用來加速數據查詢。適合經常出現在查詢條件或排序條件中的數據列。

主鍵索引

特殊的唯一索引，不允許有空值，在建表的時候指定主鍵，就會創建主鍵索引，MySQL中最核心的索引，大量的業務數據都是基于主鍵查詢。

唯一索引

普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須是唯一性的。

全文索引

用于全文搜索，通過建立全文索引，基于分詞的查詢模式，可以極大的提升檢索效率。

組合索引

創建的索引覆蓋兩個或者兩個以上的列，適應組合查詢的場景，也常用于要素驗證的業務，例如判斷用戶身份ID，手機號，郵箱，是否為同一個用戶。

2、管理索引語法

基礎用戶表

CREATE TABLE user_base (
    id INT (11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
    user_name VARCHAR (20) NOT NULL COMMENT '用戶名',
    phone VARCHAR (20) NOT NULL COMMENT '手機號',
    email VARCHAR (32) DEFAULT NULL COMMENT '郵箱',
    card_id VARCHAR (32) DEFAULT NULL COMMENT '身份編號',
    create_time datetime DEFAULT NULL COMMENT '創建時間',
    state INT (1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '用戶基礎表';

創建單列索引

CREATE INDEX card_id_index ON user_base(card_id);

修改添加索引

ALTER TABLE user_base ADD INDEX state_index(state) ;

創建組合索引

CREATE INDEX bind_index ON user_base(phone,card_id);

刪除索引

DROP INDEX card_id_index ON user_base ;

修改索引

MySQL不支持真正修改索引的語法規范，可以通過刪除舊索引，添加新索引的方式進行操作。

3、查詢索引

分析MySQL查詢，多數情況下用來分析執行語句的SQL中是否使用索引，是否產生臨時表等性能相關問題。

基礎用法

EXPLAIN SELECT * FROM user_base WHERE id='1';

參數說明

• id：相同，按table列由上至下順序執行，不同，如果是子查詢，id的序號會遞增，id的值越大優先級越高，越先被執行；
• select_type：表示查詢的類型，主要是用于區別普通查詢、聯合查詢、子查詢等的復雜查詢；

simple：簡單select查詢，查詢中不包含子查詢或者
primary：查詢中若包含復雜的子部分，最外層查詢則被標記為primary
subquery：select或where中包含子查詢
derived：from中包含的子查詢被標記為derived衍生，mysql會遞歸執行這些子查詢，且生成臨時表
union：第二個select出現在union后，標記為union
union-result：從union表獲取結果的select

• table：指當前執行計劃中的數據表；
• type：說明的是查詢使用了哪種類型，下面從好到差排序；

system-const：對查詢的某部分進行優化并轉換成一個常量時，會使用該類型
eq_ref：常見于主鍵或唯一索引掃描，表中只有一條記錄與之匹配
ref：非唯一性索引掃描，返回匹配某個單獨值的所有行
index：遍歷索引結構，索引文件通常比數據文件小
all：遍歷全表進行查詢

• possible_keys：在查詢中可能使用到的索引；
• key：在查詢中實際使用到的索引；
• key_len：查詢中索引字段的最大可能長度，在不損失精確性的情況下，長度越短越好；
• ref：表示本行被操作的對象的參照對象，可能是一個常量用const表示，也可能是其他表的key指向的對象；
• rows：預估找到符合要求的記錄所需要掃描的行數，掃描越少越好；
• extra：執行計劃中，一些十分重要的信息；

Using-Filesort：查詢使用文件排序，最差的執行計劃
Using-Temporary：臨時表保存中間結果，比文件排序稍微強點
Using-Index：查詢操作中使用了覆蓋索引
Using-Where：表明使用了where過濾條件
Using-Join-Buffer:表明使用了連接緩存
Impossible-Where：表示where條件false,不能過濾元素
Distinct：優化distinct找到第一匹配的數據后即停止找同樣值的動作
Select-Tables-Optimized-Away：不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化

三、B-Tree索引結構

1、B-Tree索引簡介

MySQL官方比較推薦的索引結構類型，在實際的數據庫開發中，基于MySQL中的表結構，大部分使用的都是B-Three索引結構，即二叉樹的結構。可以加快數據的訪問速度，存儲引擎不再需要進行全表掃描來獲取數據，數據分布在各個索引節點上，B-Tree索引結構如圖：

該結構是典型的二叉樹結構，特點：數據值按照順序存儲的，每個葉子節點到根部的距離是相同的，注意這里描述的是索引結構圖。

實際存儲結構上，數據順序存儲，每個節點包含索引值，索引指向的數據行的值，指向子頁的指針，指向葉子頁的指針，這樣才能把索引和數據結構組織起來，結構如圖：

這樣完整描述B-Tree索引的數據特點，基于樹搜索提升效率，減少掃描數據，數據被順序的組織起來，按照索引值順序排列。

2、搜索規則

索引的根本作用，減少掃描的數據量，提升查詢效率，基于B-Tree索引的結構的查詢規則基本如下：

• 查詢從索引的根節點開始，逐步搜索；
• 根節點的槽中存放指向子節點的指針，指向下層；
• 根據節點頁的值和查詢值比較，判斷是否符合條件；
• 不斷執行上述邏輯，直到查詢完成；

注意：必須要強調一點，查詢必須是在執行索引的基礎上，才是該邏輯，正常的開發中多分析一下查詢語句，有時候可能只是自己感覺查詢索引是執行的，實際可能是失效的。

3、索引查詢失效

好的索引設計十分重要，但是查詢的時候很可能因為觸發各種索引失效機制，導致SQL語句不執行索引搜索，嚴重損失性能，所以基于業務下數據查詢特點，設計相對好用的索引結構，是十分關鍵的，這里涉及很多場景問題，后續再詳細記錄。

四、索引導致的問題

索引有時候并不是最好的解決方式，當數據量龐大的時候，索引也會占據龐大的存儲空間，這里提供一個業務測試場景，僅供參數：單表三個字符類型字段，兩個字段使用索引結構，存儲數據在700W量級，在A和B兩個數據庫，A數據庫有索引結構，B數據庫沒有索引，A庫占用的空間是B庫的1.6倍，寫入千萬數據的速度也比B數據庫慢9分鐘。

這里只想說明一點：索引雖然好，使用妥當才能發揮作用。

五、源代碼地址

GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base