Mysql索引不會怎么辦?6000字長文教會你

點擊上方「藍字」關注我們

經常在開發(fā)中碰到同事說，數據查詢好慢，第一個反應就是給表加個索引。從而引發(fā)想去探索下我們常說的索引究竟是什么？難道只需要加個索引就能解決數據庫查詢問題嗎？

帶著這個問題我們開始探究MySQL中的索引究竟是什么，它能幫助我們做些什么。

索引存在的意義

在現有程序業(yè)務中，數據庫作為存儲的重要一環(huán)，不可或缺，而對于數據庫的操作無外乎是增刪改查，但隨著數據量的增加，數據庫的性能就成為最重要的一環(huán)，數據查詢不能慢，數據查詢一慢，用戶體驗就會差。

如何在保證數據存儲中的增刪改查效率呢？就成了一個必不可少的設計。

在Mysql這樣的數據存儲中總是少不了一個東西--->索引，索引就類似于我們看書的目錄，使用書籍的目錄可以幫助快速的定位到知識點的頁數，而索引也是同樣的目的，快速檢索到數據。

那就可以總結出索引的目的：「提高數據的檢索速度」。

索引的類型

既然索引有提高檢索的速度，那就給數據庫的查詢操作都加上索引，讓他們飛快的運行，這事還真不能急，為啥？數據庫的索引種類有好多種，萬一索引用的不對，引發(fā)的不是加快數據庫的運行，而是眾多的慢查詢會將整個數據庫拖垮。

用于提高讀寫效率的數據結構種類這么多，那我們來了解下數據庫中常見的索引類型都有哪些。

哈希索引

哈希索引簡單來說就是Key-Value模型，我們只要通過給定的Key就可以查找到對應的Value，十分快速方便。

不過你要明白哈希索引是通過哈希函數對Key進行計算，換算成數據存儲位置，隨著數據量的增加，不可避免會出現不同的Key經過哈希函數計算后出現相同的數據存儲位置。

這種情況怎么解決呢？業(yè)界通用的方式是當出現位置一樣的數據結果，會在該結果后面鏈接一個鏈表，將相同的數據放入到鏈表中。

更進一步當相同位置中的數據越來越多，查詢數據時會將鏈表中的數據遍歷，速度也是慢，這時候可以采用將鏈表進行樹化，二叉樹的查找速度還是很快的。

??圖是數據舉例說明:

哈希索引只適合用來查找等值的數據，而不是適合范圍索引，排序等操作。常見的哈希索引是在Redis中。

二叉樹

數據結構中存在「樹」數據結構，雖然樹的結構多種多樣，但是常用的數據結構是二叉樹，二叉樹是擁有兩個分叉的樹，分別為左子節(jié)點與右子節(jié)點。以此類推，動物中有八爪魚，同樣的也存在八叉樹，你可以想象八叉樹是什么樣子。

二叉樹的特點是，左節(jié)點的值<父節(jié)點<右節(jié)點。如果要查找到一個值就可以按照子節(jié)點的順序進行查找.

隨著數據量的增大，二叉樹的高度也會主鍵遞增，數據庫存儲的數據并不是都放到內存中，而是要放到磁盤上，磁盤的訪問速度是比內存慢幾十倍。

現在假如一個樹高30,每次搜索樹一次就需要訪問一次硬盤,一次訪問磁盤速度假設是10ms，樹高30至少需要訪問磁盤30次才能獲取到數據，30*10=300ms。

如果數據更多，樹高到100，獲取一次數據成本就很高了。

為了解決這個問題，可以使用N叉樹的方式來降低樹的高度，「減少訪問磁盤的次數，這樣就能提高效率?！?/strong>

跳表

跳表是建立在多層級鏈表上的數據結構，通過一層層的鏈表查詢就提高了檢索數據的效率。

B+Tree

Mysql中索引的實現是建立在數據庫引擎上的，而在Mysql中有多個數據庫引擎，常用的數據庫引擎是InnoDB.

InnoDB引擎索引實現是使用B+Tree索引模型，其實還有一種BTree模型，B+Tree是建立在BTree基礎上發(fā)展的。

B+Tree可以認為是BTree的改進版本：

?

注意子節(jié)點與葉子節(jié)點是不同的概念。把沒有子節(jié)點的節(jié)點叫做「葉子節(jié)點」

?

• 在B+Tree中子節(jié)點只存儲索引，而在B樹中是存儲數據的。
• B樹中的葉子節(jié)點并不需要使用鏈表連串聯，而B+樹中是用鏈表連接起來的。
• B+樹中的葉子節(jié)點存儲數據.

數據庫中每一個索引都能對應到一顆B+樹，一個表是可以存在多個B+樹。

不管是B+Tree還是BTree都是利用多叉樹(「該樹有多少叉是根據頁的大小進行計算好的，索引會涉及到新增刪除，同樣的就會涉及到頁的分裂與合并」)，保證不把所有的索引數據放入到內存上，降低磁盤的訪問次數加快數據訪問。

索引的分類

Mysql中常用Innodb引擎,組織數據庫索引的方式就是B+Tree。

B+Tree是索引組織表，那在B+Tree有多少種索引的類型呢？

從不同的方向劃分可以劃分為不同的類型。

功能上區(qū)分

主要為普通索引，主鍵索引，唯一索引，前綴索引，全文索引，哈希索引。

普通索引

普通索引就是我們常用的索引創(chuàng)建-> 創(chuàng)建單個索引，相關語句如下

alter?table?table_name?add?index?index_name(column);
drop?index?index_name?on?table_name;
ALTER?TABLE?table_name?DROP?INDEX?index_name

主鍵索引

主鍵索引是在普通索引的基礎上增加兩種約束條件分別為唯一和不能為空。主鍵索引在Innodb中用來維護索引組織的性質，所以，在使用Innodb引擎時，建議你的表都設置主鍵。

創(chuàng)建主鍵可以在創(chuàng)建的表的時候指定 primary key('id'),也可以創(chuàng)建聯合主鍵primary key ('id','name').

創(chuàng)建主鍵的相關SQL

#?當表里面沒有主鍵索引時，增加主鍵索引
ALTER?TABLE?table_name?ADD?PRIMARY?KEY?(?`id`?)
#?刪除主鍵索引
ALTER?TABLE?table_name?DROP?INDEX?name_index

唯一索引

唯一索引時在普通索引的基礎上增加唯一的約束，在插入相關數據時，會檢查該索引數據是否已經存在數據庫中。

使用下面的創(chuàng)建語句創(chuàng)建:

#?創(chuàng)建唯一索引
ALTER?TABLE?table_name?ADD?UNIQUE?(`column`)
#?刪除索引
drop?index?index_name?on?table_name;

前綴索引

字符串在編程中經常遇到的，比如常用的郵箱,一些業(yè)務場景中需要對某些字符串的前綴進行匹配。

這就涉及到一個問題，不能使用索引的話，就只能進行全表掃描。數據量一大，該方式就會成為性能的瓶頸。

數據庫中的前綴索引就是解決字符串前綴匹配的問題。

#?創(chuàng)建前綴索引
alter?table?table_name??add?index?index_name(columns(6));
#?刪除索引
drop?index?index_name?on?table_name;
#?怎么計算前綴索引設計幾個字符?使用下列語句進行估算
select?count(distinct?列名)/count(*)as?a,COUNT(DISTINCT?left(列名,100))?as?b,?COUNT(DISTINCT?left(列名,110))?as?c?from?表名

前綴索引有一個缺點就是無法使用覆蓋索引的優(yōu)化，必須回表查詢。

全文索引

全文索引是用來解決Mysql中文本匹配慢的問題，常使用like模糊搜索%內容%，沒法用到前面列舉的索引，這時候就可以嘗試使用全文索引來解決該問題。

相關SQL文件看下?

create?fulltext?index?table_name
????on?index_name(column,column);
alter?table?table_name
????add?fulltext?index?index_name(column,column);

注意一點的是全文索引是有自己的匹配語法，使用match和against關鍵字來進行匹配?。

select?*?from?table_name?where?match(column,column)?against('xxx?xxx');

從索引個數上區(qū)分

從個數區(qū)分就是該索引由幾個列組成。當有多個列構成就是聯合索引

• 單個索引：只有一列創(chuàng)建的索引
• 聯合索引：多列聯合組成的索引。

單個索引的介紹不用多說，這里主要說下聯合索引。

聯合索引

我們知道B+Tree樹這種形式的索引結構是可以使用「最左前綴」，來定位記錄。十分恰當的聯合索引就需要使該規(guī)則才能發(fā)揮出強大的作用。

最左前綴

順便引出的問題就是我們在創(chuàng)建聯合索引的時候應該怎么安排索引內的字段順序？

當當前表是新建立的，還沒有其他索引可以根據業(yè)務需求進行直接創(chuàng)建；如果表中已經存在其他索引，那可以通過調整順序幫助減少索引的創(chuàng)建。

每次創(chuàng)建一個新的索引，就會增加一部分的索引存儲空間，隨著數據量的增加，索引的存儲也會暴漲，所以在創(chuàng)建索引時，都需要考一個空間占用的原則。

當有一個大字段和小字段組合成聯合索引時，「大字段索引放在聯合索引的前面」。

比如現在需要根據郵件和年齡查詢數據，但還有根據age以及email單獨查詢的需求。

一般第一個反應就是創(chuàng)建三個索引，age,name,(name,age)/(age,name)。

而一般email的長度是大于age的，在有最左前綴的原則下，聯合索引第一個字段單獨查詢是可以使用索引。則這里選擇創(chuàng)建的索引就是age,name,(name,age)。

從磁盤角度區(qū)分

看一些數據庫資料總是聚簇索引，非聚簇索引，這兩種方式跟主鍵索引，普通索引又有什么區(qū)別？

聚簇索引，非聚簇索引

其實就是一類內容，只是根據分類的方式不同，叫的名字不同而已。聚簇索引與非聚簇索引是指在磁盤上對數據的組織結構不同。

聚簇索引可認為是磁盤將實際數據按照定物理地址進行順序存放，并且與索引的順序是一致的。那么當索引是相鄰的，對應的數據一定也是按照相鄰的順序存放。

磁盤對于順序讀取速度比磁盤隨機讀取的速度要快很多；正因為聚簇索引是按照物理順序進行存儲，那一個表只能有一個聚簇索引，該索引在Mysql中是主鍵索引，當然主鍵索引也是可以包含多個列的。

其他類型的索引都是被稱為非聚簇索引。

)

使用非聚簇索引查詢數據，目的是先查到對應的聚簇索引也就是主鍵索引。通過主鍵索引從而查詢到對應的數據。在這個過程中還涉及到兩個技術，分別為回表以及索引下推。

回表

回表很見到就是通過其他索引查到對應的主鍵值，再使用主鍵值去表里面再檢索一遍數據。

那有沒有不用回表？有的，在查詢的數據的時候，查詢的索引中已經包含要的字段，就不需要再使回表使用主鍵查詢數據，這句話可能有點蒙，看下SQL你就明白了。

#?設置userid為普通索引
#?不使用覆蓋索引，需要回表查詢
select?*?from?user?where?userid=1
#?使用了覆蓋索引，這是因為userid的索引列上葉子節(jié)點就是存儲的主鍵id,不需要再回表
select?ID?from?user?where?userid=1;

#?使用聯合索引也是可以做到的(userid,name)做一個聯合索引，索引列上有對應的值，則不需要再回表查詢
select?name?from?user?where?userid?=1

索引下推

在Mysql5.6版本以前，查詢到的數據每條都需要重新回表查詢一次，而在5.6版本增加索引下推技術后，可以直接在索引列中過濾掉不需要數據，減少回表的次數。

注意該技術需要使用范圍是聯合索引(age,sex)

select?*?from?user?where??10and?age>20?and?sex=1;

首先使用索引查詢到年齡滿足第一條大于10小于20的人，同時索引列可以直接進行判斷該用戶性別是不是滿足1（男性），是就繼續(xù)，不是該條記錄過濾掉。

而在5.6以前滿足大于10小于20后，根據讀取到主鍵數據再進行對比?；乇淼拇螖底匀槐仁褂盟饕峦萍夹g版本多。

總結

本章從一個小問題引發(fā)了對索引的探索，包含對現有的數據庫中常用的幾個索引技術進行介紹，明白為什么Mysql會選擇B+Tree作為索引的檢索方式，并通過此方式梳理了Mysql中現有的索引技術。

希望這邊索引文章能幫助到你對索引的理解。

往期文章一覽

1、阿里的OceanBase上天了，但你還不會看SQL的查詢計劃嗎？

2、Msyql如何從小白入門

3、收藏！這份必備免費軟件清單，讓你的 Mac 更值錢 | 有用功

4、程序員的工資VS普通人的工資...

你點的每個在看，我都認真當成了喜歡