數(shù)據(jù)庫索引的知識點整理小結(jié),你所需要了解的都在這兒了

數(shù)據(jù)庫索引，相信大家都不陌生吧。

索引是對數(shù)據(jù)庫表中一列或多列的值進行排序的一種結(jié)構(gòu)，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。作為輔助查詢的工具，合理的設(shè)計索引能很大程度上減輕db的查詢壓力，db我們都知道，是項目最核心也是最薄弱的地方，如果壓力太大很容易產(chǎn)生故障，造成難以預(yù)計的影響。所以，不管是日常開發(fā)還是面試，索引這一塊知識體系都是必須掌握的。

當然，雖說是必須掌握，但索引的知識點很多，很多初學者經(jīng)常會遺漏，這也是我為什么想寫這篇知識點總結(jié)的原因，既是給讀者的分享，也是給自己一次全面的復習，希望對你們有所幫助。

好了，廢話不多說，進入正題。

首先聲明一下，本文索引的知識點全部是基于MySQL數(shù)據(jù)庫

索引的優(yōu)缺點

優(yōu)點：

1.大大加快數(shù)據(jù)的查詢速度

2.唯一索引可以保證數(shù)據(jù)庫表每一行的唯一性

3.加速表連接時間

缺點：

1.創(chuàng)建、維護索引要耗費時間，所以，索引數(shù)量不能過多。

2.索引是一種數(shù)據(jù)結(jié)構(gòu)，會占據(jù)磁盤空間。

3.對表進行更新操作時，索引也要動態(tài)維護，降低了維護速度

索引的類型

索引的出現(xiàn)是為了提高查詢效率，但是實現(xiàn)索引的方式卻有很多種，所以這里也就引入了索引模型的概念。這里介紹三種常用于索引的數(shù)據(jù)結(jié)構(gòu)，分別是哈希表、有序數(shù)組和搜索樹。

哈希索引

哈希表，也稱散列表，主要設(shè)計思想是通過一個哈希函數(shù)， 把關(guān)鍵碼映射的位置去尋找存放值的地方 ，讀取的時候也是直接通過關(guān)鍵碼來找到位置并存進去，這種數(shù)據(jù)結(jié)構(gòu)的平均查找復雜度為O(1)。

比如我們維護一張身份證信息和用戶姓名的表，需要根據(jù)身份證號查詢姓名，哈希索引大概是這樣的：

這種索引結(jié)構(gòu)優(yōu)點在于隨機添加或刪除單個元素的效率高，缺點在于哈希表中的元素并不一定按順序排列，所以如果想做區(qū)間查詢的話是很慢的，

假設(shè)我想查找圖中身份證號在[ID_card_n1, ID_card_n3]這個區(qū)間的所有用戶的話，就必須全部掃描一遍了。

所以，哈希表這種結(jié)構(gòu)適用于只有等值查詢的場景

有序數(shù)組索引

有序數(shù)組索引在等值查詢和區(qū)間查詢場景中的效率都很高，還是拿上面的圖做例子，用有序數(shù)組實現(xiàn)的話是這樣子的：

數(shù)組的元素按身份證號有序排列，要查詢數(shù)據(jù)的時候，使用二分法就可以快速得到，時間復雜度為O(logN)，而且，因為是有序排列，查詢某個區(qū)間內(nèi)的數(shù)據(jù)也是非常的快。

當然，有序數(shù)組的缺點也很明顯，就跟ArrayList一樣，雖然搜索快，但添加刪除元素都有可能要移動后面所有的元素，這是數(shù)組的天然缺陷。所以，有序數(shù)組索引只適用于靜態(tài)存儲引擎，比如你要保存的是2017年某個城市的所有人口信息，這類不會再修改的數(shù)據(jù)。

搜索樹索引

說到搜索樹，我們最熟悉的應(yīng)該就是二叉搜索樹了，二叉搜索樹的特點是每個結(jié)點的左兒子小于父結(jié)點，父結(jié)點又小于右兒子，并且左右子樹也分別為二叉搜索樹，平均時間復雜度是O(log2(n))。

它既有鏈表的快速插入與刪除操作的特點，又有數(shù)組快速查找的優(yōu)勢，同時，因為本身二叉搜索樹是有序的，所以也支持范圍查找

這么說起來，其實二叉搜索樹來做索引好像也是個不錯的選擇，其實不然

首先我們要明確的一點是，這棵樹是存在于磁盤中，每次我們都要從磁盤中讀取出相應(yīng)的結(jié)點，然而二叉搜索樹的結(jié)點在文件中是隨機存放的，所以可能讀取一個結(jié)點就需要一個磁盤IO，恰恰二叉搜索樹都會比較高，如一棵一百萬個元素的平衡二叉樹就有十幾層高度了，也就是大部分情況下檢索一次數(shù)據(jù)就需要十幾次磁盤IO，這個代價太高了，所以一般二叉搜索樹也不會被用來作索引。

為了讓一個查詢盡量少地讀磁盤，就必須讓查詢過程訪問盡量少的數(shù)據(jù)塊，也就是說，盡可能的讓樹的高度變低，也就是用多路搜索樹，而InnoDB存儲引擎使用的就是這種多路搜索樹，也就是我們常說的B+樹。

InnoDB的索引結(jié)構(gòu)

InnoDB是MySQL中最常用的搜索引擎，它的索引底層結(jié)構(gòu)用的就是B+樹，所有的數(shù)據(jù)都是存儲在B+樹中的。每一個索引在InnoDB中對應(yīng)一顆B+樹。

B+樹的特點是：

• 所有的葉子結(jié)點中包含了全部元素的信息，及指向含這些元素記錄的指針，且葉子結(jié)點本身依關(guān)鍵字的大小自小而大順序鏈接。
• 所有的中間結(jié)點元素都同時存在于子結(jié)點，在子結(jié)點元素中是最大（或最小）元素。
  

這種結(jié)構(gòu)有兩個優(yōu)點：

• 可以使得單一結(jié)點存儲更多的元素，除了葉子結(jié)點，其他的結(jié)點只是包含了鍵，沒有保存值，這樣的話，樹的高度就能有效降低，從而減少查詢的IO次數(shù)；
• 同時，因為葉子結(jié)點包含了下個葉子結(jié)點的指針，所以范圍查詢的時候如果搜索到第一個葉子結(jié)點的話，就能根據(jù)指針指向查詢后面的數(shù)據(jù)，不用再從根結(jié)點遍歷了。這也是為什么很多大神建議表的主鍵設(shè)計成自增長的好，因為這樣范圍查詢能提高效率
  

索引的分類

按照結(jié)構(gòu)來分的話，數(shù)據(jù)庫索引可以分為聚簇索引和非聚簇索引。

聚簇索引，也叫聚集索引，就是按照每張表的主鍵構(gòu)造一顆B+樹，同時葉子結(jié)點中存放的就是整張表的行記錄數(shù)據(jù)，簡單點說，就是我們常說的主鍵索引。在聚簇索引之上創(chuàng)建的索引稱之為輔助索引，輔助索引訪問數(shù)據(jù)總是需要二次查找。

非聚簇索引，也叫非聚集索引，二級索引。這種索引是將數(shù)據(jù)與索引分開存儲，索引結(jié)構(gòu)的葉子結(jié)點指向了數(shù)據(jù)對應(yīng)的位置。

聚簇索引

InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹中，而行數(shù)據(jù)就儲存在葉子節(jié)點上，我們先假設(shè)一張用戶表，這張表包含了id，name，company幾個字段，

用圖片表示InnoDB的索引結(jié)構(gòu)大概是這樣：

從圖中就可以看出，如果我們使用"where id = 14"這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對應(yīng)的葉結(jié)點，之后獲得行數(shù)據(jù)。

若對Name列進行條件搜索，則需要兩個步驟：第一步在輔助索引B+樹中檢索Name，到達其葉子節(jié)點獲取對應(yīng)的主鍵。第二步使用主鍵在主索引B+樹種再執(zhí)行一次B+樹檢索操作，最終到達葉子節(jié)點即可獲取整行數(shù)據(jù)。（重點在于通過其他鍵需要建立輔助索引）

這是聚簇索引的結(jié)構(gòu)，而非聚簇索引的代表是MyISM，這也是MySQL中常見的搜索引擎。

非聚簇索引

非聚簇索引的兩棵B+樹看上去沒什么不同，結(jié)點的結(jié)構(gòu)完全一致只是存儲的內(nèi)容不同而已，主鍵索引B+樹的節(jié)點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。索引本身不存儲數(shù)據(jù)，數(shù)據(jù)存儲在獨立的地方，這兩顆B+樹的葉子節(jié)點都使用一個地址指向真正的表數(shù)據(jù)。

看上去，好像非聚簇索引的效率要高于聚簇索引，因為不用查兩次B+樹，那為什么最常用的InnoDB引擎還要用這種存儲結(jié)構(gòu)呢？它本身的優(yōu)勢在哪？

1、聚簇索引中，由于行數(shù)據(jù)和葉子結(jié)點存儲在一起，同一頁中會有多條行數(shù)據(jù)，訪問同一數(shù)據(jù)頁不同行記錄時，已經(jīng)把頁加載到了Buffer中，再次訪問的時候，會在內(nèi)存中完成訪問，不必訪問磁盤。這樣主鍵和行數(shù)據(jù)是一起被載入內(nèi)存的，找到葉子節(jié)點就可以立刻將行數(shù)據(jù)返回了，所以，如果按照主鍵Id來組織數(shù)據(jù)，獲得數(shù)據(jù)更快。

2、輔助索引使用主鍵作為"指針"而不是使用地址值作為指針的好處是，減少了當出現(xiàn)行移動或者數(shù)據(jù)頁分裂時輔助索引的維護工作**，使用主鍵值當作指針會讓輔助索引占用更多的空間，換來的好處是InnoDB在移動行時無須更新輔助索引中的這個"指針"。**也就是說行的位置（實現(xiàn)中通過16K的Page來定位）會隨著數(shù)據(jù)庫里數(shù)據(jù)的修改而發(fā)生變化（前面的B+樹節(jié)點分裂以及Page的分裂），使用聚簇索引就可以保證不管這個主鍵B+樹的節(jié)點如何變化，輔助索引樹都不受影響。

3、聚簇索引適合用在排序、范圍查詢，非聚簇索引不適合。

覆蓋索引

說到輔助索引，我們還可以延伸出另一種特別的索引，就是覆蓋索引。

上面說了，聚簇索引中訪問數(shù)據(jù)要經(jīng)過二次查找，就是先找到輔助鍵的葉子結(jié)點，得到主鍵對應(yīng)的結(jié)點后再用主鍵索引查詢數(shù)據(jù)，這樣還是比較慢的，其實，如果我們所需的字段第一次查找就能獲取到的話，就不用再二次查找主鍵了，也就是不用“回表”。

就還是上面那張表有三個字段id，name，company的表來說，我給name加了索引，在查詢數(shù)據(jù)的時候，我就這么寫語句：

select name from user where name like '張%';

因為我們的語句走了索引，并且返回的字段在葉子結(jié)點都存在，查詢的時候就不會回表了，多好啊~~

所以，如果所需的字段剛好是索引列的話，盡量用這種查詢方式，不要用select *這種語句。

索引種類

前面說的索引分類是按照結(jié)構(gòu)來分，如果按作用范圍來分的話，索引還可以分為以下幾種：

普通索引：這是最基本的索引類型，沒唯一性之類的限制。

CREATE INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

唯一性索引：和普通索引基本相同，但所有的索引列只能出現(xiàn)一次，保持唯一性。

CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

主鍵：跟唯一索引一樣，不能有重復的列，但本質(zhì)上，主鍵不能算是索引，而是一種約束，必須指定為"PRIMARY KEY"。它跟唯一索引的區(qū)別在于：

• 主鍵創(chuàng)建后一定包含一個唯一性索引，唯一性索引并不一定就是主鍵。
• 唯一性索引列允許空值，而主鍵列不允許為空值。
• 主鍵列在創(chuàng)建時，已經(jīng)默認為空值 + 唯一索引了。
• 主鍵可以被其他表引用為外鍵，而唯一索引不能。
• 一個表最多只能創(chuàng)建一個主鍵，但可以創(chuàng)建多個唯一索引。
• 主鍵更適合那些不容易更改的唯一標識，如自動遞增列、身份證號等。
  

全文索引：全文索引的索引類型為FULLTEXT，可以在VARCHAR或者TEXT類型的列上創(chuàng)建。在MySQL5.6以前的版本，只有 MyISAM 存儲引擎支持全文索引，5.6及之后的版本，MyISAM 和 InnoDB 存儲引擎均支持全文索引。

CREATE FULLTEXT INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)

聯(lián)合索引：聯(lián)合索引其實不是一種索引分類，就是包含多個字段的普通索引，比如有個聯(lián)合索引為index(a，b)，查找的時候可以用 a and b 作為條件，

最左匹配原則

聯(lián)合索引中，最左優(yōu)先，以最左邊的為起點任何連續(xù)的索引都能匹配上。同時遇到范圍查詢(>、、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、索引列用函數(shù)或表達式，比如這種

select * from test where num + 1 = 5

MySQL無法解析這種方程，這完全是用戶的行為，應(yīng)該把索引列當成獨立的列，這樣索引才會生效。

2、存在NULL值條件

select * from user where user_id is not null;

我們在設(shè)計數(shù)據(jù)庫表時，應(yīng)該盡力避免NULL值出現(xiàn)，如果數(shù)據(jù)有為空的情況可以給一個默認值，比如數(shù)值型的可以給0、-1，字符類型的可以給空字符串。

3、用or表達式作為條件，有一個列沒有索引，那么其它列的索引將不起作用

select * from user where user_id = 700 or user_name = "老薛";

像這種，如果user_id有加索引，而user_name沒有的話，那么執(zhí)行的時候user_id的索引也是失效的，這也是為什么開發(fā)中盡量少用or的原因，除非是兩個字段都加了索引。

4、列與列對比，某個表中，有兩列（id和c_id）都建了單獨索引，下面這種查詢條件不會走索引

select * from test where id = c_id;

5、數(shù)據(jù)類型的轉(zhuǎn)換。如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來，否則不使用索引

create index `idx_user_name` ON user(user_name)
select * from user where user_name = 123;

像上面這種，雖然給user_name建立了索引，但查詢的時候條件沒有當成字符串，這樣的話就不會走索引。

6、NOT條件

當查詢條件為非時，索引定位就困難了，執(zhí)行計劃此時可能更傾向于全表掃描，這類的查詢條件有：>、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、多列索引，遵循最左匹配原則，這個上面說了

什么時候該用索引

前面說了，索引雖然能加快查詢速度，但本身也會占用空間，所以，索引的創(chuàng)建并不是越多越好，為了使索引能有效應(yīng)用，我們要把索引留給最有用的查詢字段，一般來說，應(yīng)該在這些字段上創(chuàng)建索引：

• 主鍵字段，這不用多說了吧；
• 經(jīng)常需要搜索的列，比如where條件經(jīng)常用到的字段；
• 其他表的外鍵字段，作為連接表的條件字段，可以有效加快連表查詢速度；
• 查詢中作為排序、統(tǒng)計或者是分組的字段；

同樣，對于有些字段不應(yīng)該創(chuàng)建索引，這些列包括

• 頻繁更新的字段不適合創(chuàng)建索引，因為每次更新不單單是更新記錄，還會更新索引，保存索引文件
• where條件里用不到的字段，不創(chuàng)建索引；
• 表記錄太少，不需要創(chuàng)建索引；
• 對于那些定義為text，image類型的列不應(yīng)該增加索引。這是因為，這些列的數(shù)據(jù)量要么相當大，要么取值很少，不利于使用索引；
• 數(shù)據(jù)重復且分布平均的字段，因此為經(jīng)常查詢的和經(jīng)常排序的字段建立索引。注意某些數(shù)據(jù)包含大量重復數(shù)據(jù)，這種字段建立索引就沒有太大的效果，例如性別字段，只有男女，不適合建立索引。
  

explain關(guān)鍵字

explain是MySQL的關(guān)鍵字，通過該關(guān)鍵字我們可以查看搜索語句的性能。

這是查詢表的數(shù)量，一共有三千多萬行，這么多的數(shù)據(jù)，我們搜索的時候肯定要用到索引才行，至于索引是否會生效，我們也可以通過該關(guān)鍵字來看下

看，搜索的條數(shù)瞬間降到了16條，走的索引是 index_user_id，證明我們的索引是生效的。

關(guān)于explain的幾個重要參數(shù)，我們有必要了解一些：

id：查詢的序列號

select_type：查詢的類型，主要是區(qū)別普通查詢和聯(lián)合查詢、子查詢之類的復雜查詢。

type：

type顯示的是訪問類型，是較為重要的一個指標，結(jié)果值從好到壞依次是：

system > const > eq_ref > ref >fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > range > index > ALL

System效率最高，ALL的話已經(jīng)是全表掃描了，一般來說，查詢至少要達到range級別。

key：

顯示MySQL實際決定使用的鍵。如果沒有索引被選擇，鍵是NULL。

key=primary的話，表示使用了主鍵；

key=null表示沒用到索引。
possible_keys：

指出MySQL能使用哪個索引在該表中找到行。如果是空的，沒有相關(guān)的索引。這時要檢查語句中是不是有什么情況導致索引失效。

rows：

表示執(zhí)行計劃中估計掃描的行數(shù)，是個估計值。

Extra：

如果是Only index，這意味著信息只用索引樹中的信息檢索出的，這比掃描整個表要快。

如果是where used，就是使用上了where限制。

如果是impossible where 表示用不著where，一般就是沒查出來啥。

出現(xiàn)using index就說明我們的索引是生效的。

總結(jié)

好了，索引的知識點就介紹到這了，最后總結(jié)一下索引的注意事項吧。

1、索引要根據(jù)表數(shù)據(jù)的使用情況來創(chuàng)建，不能創(chuàng)建太多，一般一張表不建議超過6個索引字段

2、好刀要用在刀刃上，經(jīng)常用于查詢，沒多少重復數(shù)據(jù)，搜索行數(shù)不超過表數(shù)據(jù)量4%的字段用索引的效果比較好

3、創(chuàng)建聯(lián)合索引要注意最左匹配原則，切記，最左邊的字段是必傳字段，這點我他媽就吃過大虧

4、查詢語句要用explain執(zhí)行計劃來查看性能。

參考：

https://www.jianshu.com/p/fa8192853184

MySQL實戰(zhàn)45講

最后

雖然都是基礎(chǔ)知識，但也花了我一天的時間來整理了，洋洋灑灑五千多字，也算是一篇干貨了，各位看官覺得有所收獲的話，還望能給鄙人來個轉(zhuǎn)發(fā)或點贊之類的，不求四連，能雙連或者是一連我都很滿意了，你們的舉手之勞就是我不斷創(chuàng)作的動力！

到此這篇關(guān)于數(shù)據(jù)庫索引的知識點整理小結(jié),你所需要了解的都在這兒了的文章就介紹到這了,更多相關(guān)數(shù)據(jù)庫索引知識點內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！