老劉是即將找工作的研究生，自學大資料開發，一路走來，感慨頗深，網上大資料的資料良莠不齊，於是想寫一份詳細的大資料開發指南。這份指南把大資料的【基礎知識】【框架分析】【原始碼理解】都用自己的話描述出來，讓夥伴自學從此不求人。 大資料開發指南地址如下：
github：https://github.com/BigDataLaoLiu/BigDataGuide 碼雲：https://gitee.com/BigDataLiu/BigDataGuide 您的點贊是我持續更新的動力，禁止白嫖，看了就要有收穫，有需要聯絡公眾號：努力的老劉。
今天給大家分享的是大資料開發基礎部分MySQL的索引篇，這篇內容大家一定要跟著練習，不然等於白看！
索引是什麼？
在日常開發中常常會遇到查詢比較慢的情況，我們的第一反應就是給它加索引，那索引是什麼呢？官方介紹索引是幫助MySQL高效獲取資料的資料結構，資料庫索引好比是一本書的目錄，能加快資料庫的資料查詢速度。
那索引的好處有哪些呢？
它可以提高資料檢索的效率，降低資料庫的成本。 通過索引對資料進行排序，降低資料排序的成本，降低CPU消耗。
任何事情都會有正反面，索引也不例外，那索引的壞處有哪些呢？
索引會佔據磁碟空間。 索引雖然會提高查詢效率，但會降低更新表的效率。 MySQL不僅要儲存資料，還有儲存或者更新對應的索引檔案。
那是不是有壞處就不用索引呢？
當然不是，索引必須拿來。一般來說索引本身也很大，不可能全部儲存在記憶體中，因此索引往往是儲存在磁碟檔案上的檔案中。
索引的分類
單列索引： 普通索引：add unique 唯一索引：索引列中的值必須是唯一的，但允許有空值，add unique index 主鍵索引：是一種特殊的唯一索引，不允許有空值
組合索引： 在表中的多個欄位組合上建立的索引 組合索引的使用，需要遵循最左字首原則 一般情況下，建議使用組合索引代替單列索引（主鍵索引除外）
全文索引：只有在MyIsam、InnoDB上才能使用，而且只能在char、varchar、text型別欄位上使用全文索引。 空間索引：一般用不到
索引的使用
建立索引
CREATE INDEX index_name ON table(column(length)) ;
刪除索引
DROP INDEX index_name ON table;
檢視索引
SHOW INDEX FROM table_name \G;
索引原理（重點）
索引的儲存結構
說索引原理之前，先說說索引儲存結構。索引是在儲存引擎中實現的，也就是不同的儲存引擎，會使用不同索引。其中MyIsam和InnoDB只支援B+數索引，老劉先不講B樹和B+樹的概念，大家自行搜尋。
接下來就是索引的重點，搞清楚了非聚集索引和聚集索引，索引原理就差不多了！
非聚集索引(MyIsam)
它說的是B+樹葉子節點只會儲存資料行(資料檔案)的指標，即資料和索引不在一起。它包含主鍵索引和輔助索引，都會儲存指標的值。
主鍵索引
MyIsam中B+樹葉子節點儲存的資料是資料的指標值，通過索引樹找到對應的索引，然後通過索引中儲存的記錄指標，找到資料檔案中對應的記錄。
輔助索引(次要索引)
在MyIsam中，主索引和輔助索引在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key是可以重複的。
聚集索引(InnoDB)
主鍵索引(聚集索引)的葉子節點會儲存資料行，也就是說資料和索引在一起。 輔助索引只會儲存主鍵值。 如果沒有主鍵，則使用唯一索引建立聚集索引；如果沒有唯一索引，MySQL會按照一定規則建立聚集索引。
主鍵索引
在InnoDB中要求表必須有主鍵(MyIsam可以沒有)，如果沒有顯示指定，則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含欄位作為主鍵型別為長整形。
上圖是 InnoDB 主索引（同時也是資料檔案）的示意圖，可以看到葉節點包含了完整的資料記錄，這種索引叫做聚集索引。因為 InnoDB 的資料檔案本身要按主鍵聚集。
輔助索引
InnoDB 的輔助索引 data 域儲存相應記錄主鍵的值而不是地址。換句話說，InnoDB 的所有輔助索引都引用主鍵作為 data 域。
聚集索引這種實現方式使得按主鍵的搜尋十分高效，但是輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄，即回表查詢。
select * from user where name='Alice'
根據這段SQL語句，會進行回表查詢，檢索兩次，才會獲得記錄。回表效能比較低，儘量做到不回表。
索引使用場景
介紹完索引的相關概念後，老劉必須給講講哪些場景下可以使用場景，大家記幾個就行。
哪些情況下需要使用索引
主鍵自動建立唯一索引 頻繁作為查詢條件的欄位應該建立索引 多表關聯查詢中，關聯欄位應該建立索引 查詢中排序的欄位應該建立索引 頻繁查詢欄位 查詢中統計或者分組欄位應該建立索引
哪些情況下不需要建立索引
表記錄太少，沒必要建立索引 經常進行增刪改的表 頻繁更新的欄位 where條件裡使用頻率不高的欄位
為什麼推薦多使用組合索引？
為了節省mysql索引儲存空間以及提升搜尋效能，能使用組合索引就不使用單列索引。
使用組合索引需要遵循最左字首原則，什麼是最左字首原則？
字首索引：where like a%
萬用字元%在右邊不在左邊，什麼是字首索引呢？當索引是很長的字元序列時，這個索引會很慢，佔用記憶體。如果以name為索引，當name對應的字串很長時，就可以用字首索引where like a%。
從左到右都有索引，不能斷，直到遇到範圍查詢<，>，between。
索引失效
我們進行資料查詢很慢時，可能就會存在索引失效的情況。遇到這種情況不要怕，我們可以使用explain命令對select語句的執行計劃進行分析。explain出來的資訊有10列，分別是
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
下面老劉就使用一個案例進行這些引數進行說明，大家可以跟著老劉一起練習。這10個引數老劉只講重要的，其他的大家自行學習。
--使用者表
create table tuser(
id int primary key,
loginname varchar(100),
name varchar(100),
age int,
sex char(1),
dep int,
address varchar(100)
);
--部門表
create table tdep(
id int primary key,
name varchar(100)
);
--地址表
create table taddr(
id int primary key,
addr varchar(100)
);
--建立普通索引
mysql> alter table tuser add index idx_dep(dep);
--建立唯一索引
mysql> alter table tuser add unique index idx_loginname(loginname);
--建立組合索引
mysql> alter table tuser add index idx_name_age_sex(name,age,sex);
--建立全文索引
mysql> alter table taddr add fulltext ft_addr(addr);
id
每個SELECT語句都會自動分配的一個唯一識別符號，表示查詢中操作表的順序，有四種情況：
id相同：執行順序由上到下 id不同：如果是子查詢，id號會自增，id越大，優先順序越高。 id相同的不同的同時存在 id列為null的就表示這是一個結果集，不需要使用它來進行查詢。
select_type(重要)
表示查詢型別，主要用於區別普通查詢、聯合查詢(union、union all)、子查詢等複雜查詢。
simple，表示不需要union操作或者不包含子查詢的簡單select查詢。
primary，一個需要union操作或者含有子查詢的select，位於最外層的單位查詢的select_type即為primary，並且只有有一個 。
先執行括號裡面的sql語句，再執行外面的sql語句，內層的查詢就是subquery。
subquery，除了from字句中包含的子查詢外，其他地方出現的子查詢都可能是subquery。
dependent subquery，表示這個subquery的查詢要受到外部表查詢的影響。
union，它連線的兩個select查詢，第一個查詢是PRIMARY，除了第一個表外，第二個以後的表select_type都是union。
dependent union，它與union一樣，出現在union 或union all語句中，但是這個查詢要受到外部查詢的影響。
union result，它包含union的結果集，在union和union all語句中,因為它不需要參與查詢，所以id欄位為null。
derived，from字句中出現的子查詢，也叫做派生表，其他資料庫中可能叫做內聯檢視或巢狀select。
可以理解為就是from字句後面出現子查詢，取個別名，叫派生表。
table
顯示查詢的表名，如果查詢使用了別名，那麼這裡顯示的是別名。
type(重要)
它會顯示很多引數型別，效能依次從好到壞顯示為這樣：
system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL
除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個索引，優化器會選用最優索引一個，最少要索引使用到range級別。 老劉只講這個重要的，有些內容也沒搞清楚。
system
可遇不可求，表中只有一行資料或是空表。
const(重要)
使用唯一索引或主鍵，返回記錄一定是1行記錄的等值where條件。
eq_ref(重要)
一般是連線欄位主鍵或者唯一性索引。
此型別通常出現在多表的 join 查詢，表示對於前表的每一個結果，都只能匹配到後表的一行結果。並且查詢的比較操作通常是 '='，查詢效率較高。
ref(重要)
針對非唯一性索引，使用等值（=）查詢非主鍵。或者是使用了最左字首規則索引的查詢。
range(重要)
索引範圍掃描，常見於使用>,<,is null,between ,in ,like等運算子的查詢中。
index(重要)
關鍵字：條件是出現在索引樹中的節點的，可能沒有完全匹配索引。
索引全表掃描，把索引從頭到尾掃一遍，常見於使用索引列就可以處理不需要讀取資料檔案的查詢、可以使用索引排序或者分組的查詢。
all(重要)
這個就是全表掃描資料檔案，然後再在server層進行過濾返回符合要求的記錄。
possible_keys、key、key_len、ref、rows就不講了，直接講最後一個extra。
extra
這個列包含不適合在其他列中顯示單十分重要的額外的資訊，這個列可以顯示的資訊非常多，有幾十種，這裡寫常見的幾種。
no tables used
表示不帶from字句的查詢，使用not in()形式子查詢或not exists運算子的連線查詢，這種叫做反連線。一般連線查詢是先查詢內表，再查詢外表，反連線就是先查詢外表，再查詢內表。
using filesort(重要)
排序時無法使用到索引時，就會出現這個，常見於order by和group by語句中。
using index(重要)
查詢時不需要回表查詢，直接通過索引就可以獲取查詢的資料。
using where(重要)
通常type型別為all，記錄並不是所有的都滿足查詢條件，通常有where條件，並且一般沒索引或者索引失效。
講完分析索引的引數後，現在老劉講一些索引失效的情況，大家一定要用心記住，老劉也記了好幾遍！
索引失效分析
一般SQL語句查詢採用全值匹配，資料上叫全值匹配我最愛。
最左字首法則，對於組合索引而言，查詢從索引的最左前列開始，並且不能跳過索引中的列不然就會失效。
現在舉一個帶頭的索引斷（帶頭索引生效，其他索引失效）的例子：
不要在索引上做計算，例如計算、函式、自動/手動型別轉換，不然會導致索引失效而轉向全表掃描。
範圍條件右邊的列失效，就是不能繼續使用索引中範圍條件（bettween、<、>、in等）右邊的列。
儘量使用覆蓋索引（只查詢索引的列），也就是索引列和查詢列一致，減少select *。
索引欄位上不要使用不等，索引欄位上使用（！= 或者 < >）判斷時，會導致索引失效而轉向全表掃描。
主鍵索引欄位上不可以判斷null，索引欄位上使用 is null 判斷時，可使用索引。
索引欄位使用like以萬用字元開頭（‘%字串’）時，會導致索引失效而轉向全表掃描。like要以萬用字元結束相當於範圍查詢，索引不會失效。
索引欄位是字串時，要加單引號，否則會導致索引失效而轉向全表掃描。
索引欄位不要使用or，否則會導致索引失效而轉向全表掃描。
總結
這篇內容大家一定要跟著老劉練習，光看不練等於白學！儘管當前水平可能不及各位大佬，但老劉會努力變得更加優秀，讓各位小夥伴自學從此不求人！
如果有相關問題，聯絡公眾號：努力的老劉。文章都看到這了，點贊關注支援一波！
