本文原始碼：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

推薦閱讀：MySQL系列

標籤：MySQL

上一篇：hadoop

下一篇：索引——談談你對索引的認識和理解