資料庫索引，相信大家都不陌生吧，
索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問資料庫表中的特定資訊，作為輔助查詢的工具，合理的設計索引能很大程度上減輕db的查詢壓力，db我們都知道，是專案最核心也是最薄弱的地方，如果壓力太大很容易產生故障，造成難以預計的影響，所以，不管是日常開發還是面試，索引這一塊知識體系都是必須掌握的，
當然，雖說是必須掌握，但索引的知識點很多，很多初學者經常會遺漏，這也是我為什么想寫這篇知識點總結的原因，既是給讀者的分享，也是給自己一次全面的復習，希望對你們有所幫助，
好了，廢話不多說，進入正題，
首先宣告一下，本文索引的知識點全部是基于MySQL資料庫
索引的優缺點
優點：
1.大大加快資料的查詢速度
2.唯一索引可以保證資料庫表每一行的唯一性
3.加速表連接時間
缺點：
1.創建、維護索引要耗費時間，所以，索引數量不能過多，
2.索引是一種資料結構，會占據磁盤空間，
3.對表進行更新操作時，索引也要動態維護，降低了維護速度
索引的型別
索引的出現是為了提高查詢效率，但是實作索引的方式卻有很多種，所以這里也就引入了索引模型的概念，這里介紹三種常用于索引的資料結構，分別是哈希表、有序陣列和搜索樹，
哈希索引
哈希表，也稱散串列，主要設計思想是通過一個哈希函式， 把關鍵碼映射的位置去尋找存放值的地方 ，讀取的時候也是直接通過關鍵碼來找到位置并存進去，這種資料結構的平均查找復雜度為O(1)，
比如我們維護一張身份證資訊和用戶姓名的表，需要根據身份證號查詢姓名，哈希索引大概是這樣的：
這種索引結構優點在于隨機添加或洗掉單個元素的效率高，缺點在于哈希表中的元素并不一定按順序排列，所以如果想做區間查詢的話是很慢的，
假設我想查找圖中身份證號在[ID_card_n1, ID_card_n3]這個區間的所有用戶的話，就必須全部掃描一遍了，
所以，哈希表這種結構適用于只有等值查詢的場景
有序陣列索引
有序陣列索引在等值查詢和區間查詢場景中的效率都很高，還是拿上面的圖做例子，用有序陣列實作的話是這樣子的：
陣列的元素按身份證號有序排列，要查詢資料的時候，使用二分法就可以快速得到，時間復雜度為O(logN)，而且，因為是有序排列，查詢某個區間內的資料也是非常的快，
當然，有序陣列的缺點也很明顯，就跟ArrayList一樣，雖然搜索快，但添加洗掉元素都有可能要移動后面所有的元素，這是陣列的天然缺陷，所以，有序陣列索引只適用于靜態存盤引擎，比如你要保存的是2017年某個城市的所有人口資訊，這類不會再修改的資料，
搜索樹索引
說到搜索樹，我們最熟悉的應該就是二叉搜索樹了，二叉搜索樹的特點是每個結點的左兒子小于父結點，父結點又小于右兒子，并且左右子樹也分別為二叉搜索樹，平均時間復雜度是O(log2(n))，
它既有鏈表的快速插入與洗掉操作的特點，又有陣列快速查找的優勢，同時，因為本身二叉搜索樹是有序的，所以也支持范圍查找
這么說起來，其實二叉搜索樹來做索引好像也是個不錯的選擇，其實不然
首先我們要明確的一點是，這棵樹是存在于磁盤中，每次我們都要從磁盤中讀取出相應的結點，然而二叉搜索樹的結點在檔案中是隨機存放的，所以可能讀取一個結點就需要一個磁盤IO，恰恰二叉搜索樹都會比較高，如一棵一百萬個元素的平衡二叉樹就有十幾層高度了，也就是大部分情況下檢索一次資料就需要十幾次磁盤IO，這個代價太高了，所以一般二叉搜索樹也不會被用來作索引，
為了讓一個查詢盡量少地讀磁盤，就必須讓查詢程序訪問盡量少的資料塊，也就是說，盡可能的讓樹的高度變低，也就是用多路搜索樹，而InnoDB存盤引擎使用的就是這種多路搜索樹，也就是我們常說的B+樹，
InnoDB的索引結構
InnoDB是MySQL中最常用的搜索引擎，它的索引底層結構用的就是B+樹，所有的資料都是存盤在B+樹中的，每一個索引在InnoDB中對應一顆B+樹，
B+樹的特點是：
所有的葉子結點中包含了全部元素的資訊，及指向含這些元素記錄的指標，且葉子結點本身依關鍵字的大小自小而大順序鏈接， 所有的中間結點元素都同時存在于子結點，在子結點元素中是最大（或最小）元素，
這種結構有兩個優點：
可以使得單一結點存盤更多的元素，除了葉子結點，其他的結點只是包含了鍵，沒有保存值，這樣的話，樹的高度就能有效降低，從而減少查詢的IO次數； 同時，因為葉子結點包含了下個葉子結點的指標，所以范圍查詢的時候如果搜索到第一個葉子結點的話，就能根據指標指向查詢后面的資料，不用再從根結點遍歷了，這也是為什么很多大神建議表的主鍵設計成自增長的好，因為這樣范圍查詢能提高效率
索引的分類
按照結構來分的話，資料庫索引可以分為聚簇索引和非聚簇索引，
聚簇索引，也叫聚集索引，就是按照每張表的主鍵構造一顆B+樹，同時葉子結點中存放的就是整張表的行記錄資料，簡單點說，就是我們常說的主鍵索引，在聚簇索引之上創建的索引稱之為輔助索引，輔助索引訪問資料總是需要二次查找，
非聚簇索引，也叫非聚集索引，二級索引，這種索引是將資料與索引分開存盤，索引結構的葉子結點指向了資料對應的位置，
聚簇索引
InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹中，而行資料就儲存在葉子節點上，我們先假設一張用戶表，這張表包含了id，name，company幾個欄位，
用圖片表示InnoDB的索引結構大概是這樣：
從圖中就可以看出，如果我們使用"where id = 14"這樣的條件查找主鍵，則按照B+樹的檢索演算法即可查找到對應的葉結點，之后獲得行資料，
若對Name列進行條件搜索，則需要兩個步驟：第一步在輔助索引B+樹中檢索Name，到達其葉子節點獲取對應的主鍵，第二步使用主鍵在主索引B+樹種再執行一次B+樹檢索操作，最終到達葉子節點即可獲取整行資料，（重點在于通過其他鍵需要建立輔助索引）
這是聚簇索引的結構，而非聚簇索引的代表是MyISM，這也是MySQL中常見的搜索引擎，
非聚簇索引
非聚簇索引的兩棵B+樹看上去沒什么不同，結點的結構完全一致只是存盤的內容不同而已，主鍵索引B+樹的節點存盤了主鍵，輔助鍵索引B+樹存盤了輔助鍵，索引本身不存盤資料，資料存盤在獨立的地方，這兩顆B+樹的葉子節點都使用一個地址指向真正的表資料，
看上去，好像非聚簇索引的效率要高于聚簇索引，因為不用查兩次B+樹，那為什么最常用的InnoDB引擎還要用這種存盤結構呢？它本身的優勢在哪？
1、聚簇索引中，由于行資料和葉子結點存盤在一起，同一頁中會有多條行資料，訪問同一資料頁不同行記錄時，已經把頁加載到了Buffer中，再次訪問的時候，會在記憶體中完成訪問，不必訪問磁盤，這樣主鍵和行資料是一起被載入記憶體的，找到葉子節點就可以立刻將行資料回傳了，所以，如果按照主鍵Id來組織資料，獲得資料更快，
2、輔助索引使用主鍵作為"指標"而不是使用地址值作為指標的好處是，減少了當出現行移動或者資料頁分裂時輔助索引的維護作業**，使用主鍵值當作指標會讓輔助索引占用更多的空間，換來的好處是InnoDB在移動行時無須更新輔助索引中的這個"指標"，**也就是說行的位置（實作中通過16K的Page來定位）會隨著資料庫里資料的修改而發生變化（前面的B+樹節點分裂以及Page的分裂），使用聚簇索引就可以保證不管這個主鍵B+樹的節點如何變化，輔助索引樹都不受影響，
3、聚簇索引適合用在排序、范圍查詢，非聚簇索引不適合，
覆寫索引
說到輔助索引，我們還可以延伸出另一種特別的索引，就是覆寫索引，
上面說了，聚簇索引中訪問資料要經過二次查找，就是先找到輔助鍵的葉子結點，得到主鍵對應的結點后再用主鍵索引查詢資料，這樣還是比較慢的，其實，如果我們所需的欄位第一次查找就能獲取到的話，就不用再二次查找主鍵了，也就是不用“回表”，
就還是上面那張表有三個欄位id，name，company的表來說，我給name加了索引，在查詢資料的時候，我就這么寫陳述句：
select name from user where name like '張%';
因為我們的陳述句走了索引，并且回傳的欄位在葉子結點都存在，查詢的時候就不會回表了，多好啊~~
所以，如果所需的欄位剛好是索引列的話，盡量用這種查詢方式，不要用select *這種陳述句，
索引種類
前面說的索引分類是按照結構來分，如果按作用范圍來分的話，索引還可以分為以下幾種：
普通索引：這是最基本的索引型別，沒唯一性之類的限制，
CREATE INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)
唯一性索引：和普通索引基本相同，但所有的索引列只能出現一次，保持唯一性，
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)
主鍵：跟唯一索引一樣，不能有重復的列，但本質上，主鍵不能算是索引，而是一種約束，必須指定為"PRIMARY KEY"，它跟唯一索引的區別在于：
主鍵創建后一定包含一個唯一性索引，唯一性索引并不一定就是主鍵， 唯一性索引列允許空值，而主鍵列不允許為空值， 主鍵列在創建時，已經默認為空值 + 唯一索引了， 主鍵可以被其他表參考為外鍵，而唯一索引不能， 一個表最多只能創建一個主鍵，但可以創建多個唯一索引， 主鍵更適合那些不容易更改的唯一標識，如自動遞增列、身份證號等，
全文索引：全文索引的索引型別為FULLTEXT，可以在VARCHAR或者TEXT型別的列上創建，在MySQL5.6以前的版本，只有 MyISAM 存盤引擎支持全文索引，5.6及之后的版本，MyISAM 和 InnoDB 存盤引擎均支持全文索引，
CREATE FULLTEXT INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)
聯合索引：聯合索引其實不是一種索引分類，就是包含多個欄位的普通索引，比如有個聯合索引為index(a，b)，查找的時候可以用 a and b 作為條件，
最左匹配原則
聯合索引中，最左優先，以最左邊的為起點任何連續的索引都能匹配上，同時遇到范圍查詢(>、<、between、like)就會停止匹配，
就像上面說的index(a，b)或者是a單獨作為查詢條件都會走索引，但是如果是單獨用 b 做查詢條件就不會走索引了
或者是如果建立(a,b,c,d)順序的索引的話，用a = 1 and b = 2 and c > 3 and d = 4這樣的陳述句搜索，d是用不到索引的，因為c欄位是一個范圍查詢，它之后的欄位會停止匹配，
索引什么時候會失效
1、索引列用函式或運算式，比如這種
select * from test where num + 1 = 5
MySQL無法決議這種方程，這完全是用戶的行為，應該把索引列當成獨立的列，這樣索引才會生效，
2、存在NULL值條件
select * from user where user_id is not null;
我們在設計資料庫表時，應該盡力避免NULL值出現，如果資料有為空的情況可以給一個默認值，比如數值型的可以給0、-1，字符型別的可以給空字串，
3、用or運算式作為條件，有一個列沒有索引，那么其它列的索引將不起作用
select * from user where user_id = 700 or user_name = "老薛";
像這種，如果user_id有加索引，而user_name沒有的話，那么執行的時候user_id的索引也是失效的，這也是為什么開發中盡量少用or的原因，除非是兩個欄位都加了索引，
4、列與列對比，某個表中，有兩列（id和c_id）都建了單獨索引，下面這種查詢條件不會走索引
select * from test where id = c_id;
5、資料型別的轉換，如果列型別是字串，那一定要在條件中將資料使用引號參考起來，否則不使用索引
create index `idx_user_name` ON user(user_name)
select * from user where user_name = 123;
像上面這種，雖然給user_name建立了索引，但查詢的時候條件沒有當成字串，這樣的話就不會走索引，
6、NOT條件
當查詢條件為非時，索引定位就困難了，執行計劃此時可能更傾向于全表掃描，這類的查詢條件有：<>、NOT、in、not exists
select * from user where user_id<>500;
select * from user where user_id in (1,2,3,4,5);
select * from user where user_id not in (6,7,8,9,0);
select * from user where user_id exists (select 1 from user_record where user_record.user_id = user.user_id);
7、like查詢是以%開頭
當使用模糊搜索時，盡量采用后置的通配符，例如要查姓張的人，可以用user_name like ‘張%’，這樣走索引時，可以從前面開始匹配索引列，但如果是這樣user_name like ‘%張’，那么就會走全表掃描的方式
8、多列索引，遵循最左匹配原則，這個上面說了
什么時候該用索引
前面說了，索引雖然能加快查詢速度，但本身也會占用空間，所以，索引的創建并不是越多越好，為了使索引能有效應用，我們要把索引留給最有用的查詢欄位，一般來說，應該在這些欄位上創建索引：
主鍵欄位，這不用多說了吧； 經常需要搜索的列，比如where條件經常用到的欄位； 其他表的外鍵欄位，作為連接表的條件欄位，可以有效加快連表查詢速度； 查詢中作為排序、統計或者是分組的欄位；
同樣，對于有些欄位不應該創建索引，這些列包括
頻繁更新的欄位不適合創建索引，因為每次更新不單單是更新記錄，還會更新索引，保存索引檔案 where條件里用不到的欄位，不創建索引； 表記錄太少，不需要創建索引； 對于那些定義為text，image型別的列不應該增加索引，這是因為，這些列的資料量要么相當大，要么取值很少，不利于使用索引； 資料重復且分布平均的欄位，因此為經常查詢的和經常排序的欄位建立索引，注意某些資料包含大量重復資料，這種欄位建立索引就沒有太大的效果，例如性別欄位，只有男女，不適合建立索引，
explain關鍵字
explain是MySQL的關鍵字，通過該關鍵字我們可以查看搜索陳述句的性能，
這是查詢表的數量，一共有三千多萬行，這么多的資料，我們搜索的時候肯定要用到索引才行，至于索引是否會生效，我們也可以通過該關鍵字來看下
看，搜索的條數瞬間降到了16條，走的索引是 index_user_id，證明我們的索引是生效的，
關于explain的幾個重要引數，我們有必要了解一些：
id：查詢的序列號
select_type：查詢的型別，主要是區別普通查詢和聯合查詢、子查詢之類的復雜查詢，
type：
type顯示的是訪問型別，是較為重要的一個指標，結果值從好到壞依次是：
system > const > eq_ref > ref >fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > range > index > ALL
System效率最高，ALL的話已經是全表掃描了，一般來說，查詢至少要達到range級別，
key：
顯示MySQL實際決定使用的鍵，如果沒有索引被選擇，鍵是NULL，
key=primary的話，表示使用了主鍵；
key=null表示沒用到索引，
possible_keys：
指出MySQL能使用哪個索引在該表中找到行，如果是空的，沒有相關的索引，這時要檢查陳述句中是不是有什么情況導致索引失效，
rows：
表示執行計劃中估計掃描的行數，是個估計值，
Extra：
如果是Only index，這意味著資訊只用索引樹中的資訊檢索出的，這比掃描整個表要快，
如果是where used，就是使用上了where限制，
如果是impossible where 表示用不著where，一般就是沒查出來啥，
出現using index就說明我們的索引是生效的，
總結
好了，索引的知識點就介紹到這了，最后總結一下索引的注意事項吧，
1、索引要根據表資料的使用情況來創建，不能創建太多，一般一張表不建議超過6個索引欄位
2、好刀要用在刀刃上，經常用于查詢，沒多少重復資料，搜索行數不超過表資料量4%的欄位用索引的效果比較好
3、創建聯合索引要注意最左匹配原則，切記，最左邊的欄位是必傳欄位，這點我他媽就吃過大虧
4、查詢陳述句要用explain執行計劃來查看性能，
參考：
https://www.jianshu.com/p/fa8192853184
MySQL實戰45講
最后
雖然都是基礎知識，但也花了我一天的時間來整理了，洋洋灑灑五千多字，也算是一篇干貨了，各位看官覺得有所識訓的話，還望能給鄙人來個轉發或點贊之類的，不求四連，能雙連或者是一連我都很滿意了，你們的舉手之勞就是我不斷創作的動力！
作者：鄙人薛某，一個不拘于技術的互聯網人，歡迎關注我的公眾號，每周不定期更新干貨文章，這里不僅有技術，還有吹水~~~
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/4583.html
標籤：MySQL
下一篇：mysql資料庫設計規范