作者：@溪竹

連結：https://blog.csdn.net/weixin_42181824/java/article/details/82261988

導引

本文介紹了七種MySQL索引類型。在資料庫表中，對欄位建立索引可以大大提高查詢速度。通過善用這些索引，可以令MySQL的查詢和運行更加高效。

索引是快速搜索的關鍵。MySQL索引的建立對於MySQL的高效運行是很重要的。下面介紹幾種常見的MySQL索引類型。

在資料庫表中，對欄位建立索引可以大大提高查詢速度。假如我們創建了一個 mytable表：

CREATE TABLE mytable(   
ID INT NOT NULL,    
username VARCHAR(16) NOT NULL  
);

我們隨機向裡面插入了10000條記錄，其中有一條：5555, admin。

在查找username="admin"的記錄 SELECT * FROM mytable WHERE username='admin';時，如果在username上已經建立了索引，MySQL無須任何掃描，即準確可找到該記錄。相反，MySQL會掃描所有記錄，即要查詢10000條記錄。

索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索包含多個列。

MySQL索引類型包括：

（1）普通索引

這是最基本的索引，它沒有任何限制。它有以下幾種創建方式：

◆創建索引

 CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR類型，length可以小於欄位實際長度；如果是BLOB和TEXT類型，必須指定 length，下同。

◆修改表結構

 ALTER mytable ADD INDEX [indexName] ON (username(length))

◆創建表的時候直接指定

CREATE TABLE mytable(   
ID INT NOT NULL,    
username VARCHAR(16) NOT NULL,   
INDEX [indexName] (username(length))   
);

刪除索引的語法：

DROP INDEX [indexName] ON mytable;

（2）唯一索引

它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創建方式：

◆創建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

◆修改表結構

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

◆創建表的時候直接指定

CREATE TABLE mytable(   
ID INT NOT NULL,    
username VARCHAR(16) NOT NULL,   
UNIQUE [indexName] (username(length))   
);

（3）主鍵索引

它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創建主鍵索引：

 CREATE TABLE mytable(   
ID INT NOT NULL,    
username VARCHAR(16) NOT NULL,   
PRIMARY KEY(ID)   
);

當然也可以用 ALTER 命令。記住：一個表只能有一個主鍵。

（4）組合索引

為了形象地對比單列索引和組合索引，為表添加多個欄位：

CREATE TABLE mytable(   
ID INT NOT NULL,    
username VARCHAR(16) NOT NULL,   
city VARCHAR(50) NOT NULL,   
age INT NOT NULL  
);

為了進一步榨取MySQL的效率，就要考慮建立組合索引。就是將 name, city, age建到一個索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表時，usernname長度為 16，這裡用 10。這是因為一般情況下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高INSERT的更新速度。

如果分別在 usernname，city，age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣，遠遠低於我們的組合索引。雖然此時有了三個索引，但MySQL只能用到其中的那個它認為似乎是最有效率的單列索引。

建立這樣的組合索引，其實是相當於分別建立了下面三組組合索引：

usernname,city,age

usernname,city

usernname

為什麼沒有 city，age這樣的組合索引呢？這是因為MySQL組合索引「最左前綴」的結果。簡單的理解就是只從最左面的開始組合。並不是只要包含這三列的查詢都會用到該組合索引，下面的幾個SQL就會用到這個組合索引：

SELECT * FROM mytable WHREE username="admin" AND city="鄭州"

SELECT * FROM mytable WHREE username="admin"

而下面幾個則不會用到：

SELECT * FROM mytable WHREE age=20 AND city="鄭州"

SELECT * FROM mytable WHREE city="鄭州"

（5）建立索引的時機

到這裡我們已經學會了建立索引，那麼我們需要在什麼情況下建立索引呢？一般來說，在WHERE和JOIN中出現的列需要建立索引，但也不完全如此，因為MySQL只對<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE才會使用索引。例如：

SELECT t.Name  
FROM mytable t LEFT JOIN mytable m    
ON t.Name=m.username WHERE m.age=20 AND m.city='鄭州'

此時就需要對city和age建立索引，由於mytable表的userame也出現在了JOIN子句中，也有對它建立索引的必要。

剛才提到只有某些時候的LIKE才需建立索引。因為在以通配符%和_開頭作查詢時，MySQL不會使用索引。例如下句會使用索引：

SELECT * FROM mytable WHERE username like'admin%'

而下句就不會使用：

SELECT * FROM mytable WHEREt Name like'%admin'

因此，在使用LIKE時應注意以上的區別。

（6）索引的不足之處

上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：

◆雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。

◆建立索引會占用磁碟空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創建了多種組合索引，索引文件的會膨脹很快。

索引只是提高效率的一個因素，如果你的MySQL有大數據量的表，就需要花時間研究建立最優秀的索引，或優化查詢語句。

（7）使用索引的注意事項

使用索引時，有以下一些技巧和注意事項：

◆索引不會包含有NULL值的列

只要列中包含有NULL值都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的默認值為NULL。

◆使用短索引

對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字符內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。

◆索引列排序

MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫默認排序可以符合要求的情況下不要使用排序操作；儘量不要包含多個列的排序，如果需要最好給這些列創建複合索引。

◆like語句操作

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like 「%aaa%」不會使用索引而like 「aaa%」可以使用索引。

◆不要在列上進行運算

select * from users where YEAR(adddate)<2007;

將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成

select * from users where adddate<『2007-01-01』;

◆不使用NOT IN和<>操作

索引原理：

想要理解索引原理必須清楚一種數據結構「平衡樹」(非二叉)，也就是b tree或者 b+ tree，重要的事情說三遍：「平衡樹，平衡樹，平衡樹」。當然，有的資料庫也使用哈希桶作用索引的數據結構，然而，主流的RDBMS都是把平衡樹當做數據表默認的索引數據結構的。

我們平時建表的時候都會為表加上主鍵，在某些關係資料庫中，如果建表時不指定主鍵，資料庫會拒絕建表的語句執行。事實上，一個加了主鍵的表，並不能被稱之為「表」。一個沒加主鍵的表，它的數據無序的放置在磁碟存儲器上，一行一行的排列的很整齊，跟我認知中的「表」很接近。如果給表上了主鍵，那麼表在磁碟上的存儲結構就由整齊排列的結構轉變成了樹狀結構，也就是上面說的「平衡樹」結構，換句話說，就是整個表就變成了一個索引。沒錯，再說一遍，整個表變成了一個索引，也就是所謂的「聚集索引」。這就是為什麼一個表只能有一個主鍵，一個表只能有一個「聚集索引」，因為主鍵的作用就是把「表」的數據格式轉換成「索引（平衡樹）」的格式放置。

上圖就是帶有主鍵的表（聚集索引）的結構圖。圖畫的不是很好，將就著看。其中樹的所有結點（底部除外）的數據都是由主鍵欄位中的數據構成，也就是通常我們指定主鍵的id欄位。最下面部分是真正表中的數據。假如我們執行一個SQL語句：

select * from table where id = 1256;

首先根據索引定位到1256這個值所在的葉結點，然後再通過葉結點取到id等於1256的數據行。這裡不講解平衡樹的運行細節，但是從上圖能看出，樹一共有三層，從根節點至葉節點只需要經過三次查找就能得到結果。如下圖：

假如一張表有一億條數據，需要查找其中某一條數據，按照常規邏輯，一條一條的去匹配的話，最壞的情況下需要匹配一億次才能得到結果，用大O標記法就是O(n)最壞時間複雜度，這是無法接受的，而且這一億條數據顯然不能一次性讀入內存供程序使用，因此，這一億次匹配在不經緩存優化的情況下就是一億次IO開銷，以現在磁碟的IO能力和CPU的運算能力，有可能需要幾個月才能得出結果。如果把這張錶轉換成平衡樹結構（一棵非常茂盛和節點非常多的樹），假設這棵樹有10層，那麼只需要10次IO開銷就能查找到所需要的數據，速度以指數級別提升，用大O標記法就是O(log n)，n是記錄總樹，底數是樹的分叉數，結果就是樹的層次數。換言之，查找次數是以樹的分叉數為底，記錄總數的對數，用公式來表示就是

用程序來表示就是Math.Log(100000000,10)，100000000是記錄數，10是樹的分叉數（真實環境下分叉數遠不止10），結果就是查找次數，這裡的結果從億降到了個位數。因此，利用索引會使資料庫查詢有驚人的性能提升。

然而，事物都是有兩面的，索引能讓資料庫查詢數據的速度上升，而使寫入數據的速度下降，原因很簡單的，因為平衡樹這個結構必須一直維持在一個正確的狀態，增刪改數據都會改變平衡樹各節點中的索引數據內容，破壞樹結構，因此，在每次數據改變時， DBMS必須去重新梳理樹（索引）的結構以確保它的正確，這會帶來不小的性能開銷，也就是為什麼索引會給查詢以外的操作帶來副作用的原因。

講完聚集索引，接下來聊一下非聚集索引，也就是我們平時經常提起和使用的常規索引。

非聚集索引和聚集索引一樣，同樣是採用平衡樹作為索引的數據結構。索引樹結構中各節點的值來自於表中的索引欄位，假如給user表的name欄位加上索引，那麼索引就是由name欄位中的值構成，在數據改變時， DBMS需要一直維護索引結構的正確性。如果給表中多個欄位加上索引，那麼就會出現多個獨立的索引結構，每個索引（非聚集索引）互相之間不存在關聯。如下圖：

每次給欄位建一個新索引，欄位中的數據就會被複製一份出來，用於生成索引。因此，給表添加索引，會增加表的體積，占用磁碟存儲空間。

非聚集索引和聚集索引的區別在於，通過聚集索引可以查到需要查找的數據，而通過非聚集索引可以查到記錄對應的主鍵值，再使用主鍵的值通過聚集索引查找到需要的數據，如下圖：

不管以任何方式查詢表，最終都會利用主鍵通過聚集索引來定位到數據，聚集索引（主鍵）是通往真實數據所在的唯一路徑。

然而，有一種例外可以不使用聚集索引就能查詢出所需要的數據，這種非主流的方法稱之為「覆蓋索引」查詢，也就是平時所說的複合索引或者多欄位索引查詢。文章上面的內容已經指出，當為欄位建立索引以後，欄位中的內容會被同步到索引之中，如果為一個索引指定兩個欄位，那麼這個兩個欄位的內容都會被同步至索引之中。

先看下面這個SQL語句

//建立索引

create index index_birthday on user_info(birthday);

//查詢生日在1991年11月1日出生用戶的用戶名

select user_name from user_info where birthday = '1991-11-1'

這句SQL語句的執行過程如下

首先，通過非聚集索引index_birthday查找birthday等於1991-11-1的所有記錄的主鍵ID值

然後，通過得到的主鍵ID值執行聚集索引查找，找到主鍵ID值對就的真實數據（數據行）存儲的位置

最後，從得到的真實數據中取得user_name欄位的值返回，也就是取得最終的結果

我們把birthday欄位上的索引改成雙欄位的覆蓋索引

create index index_birthday_and_user_name on user_info(birthday, user_name);

這句SQL語句的執行過程就會變為

通過非聚集索引index_birthday_and_user_name查找birthday等於1991-11-1的葉節點的內容，然而，葉節點中除了有user_name表主鍵ID的值以外， user_name欄位的值也在裡面，因此不需要通過主鍵ID值的查找數據行的真實所在，直接取得葉節點中user_name的值返回即可。通過這種覆蓋索引直接查找的方式，可以省略不使用覆蓋索引查找的後面兩個步驟，大大的提高了查詢性能，如下圖：

資料庫索引的大致工作原理就是像文中所述，然而細節方面可能會略有偏差，這但並不會對概念闡述的結果產生影響。

————————————————

MySQL的7類索引，助你讓MySQL飛得更高

導引

MySQL索引類型包括：

索引原理：