MySQL 創建索引，史上最簡單MySQL教程詳解（進階篇）之索引及失效場合總結

• 史上最簡單MySQL教程詳解（進階篇）之索引及其失效場合總結
  • 什么是索引及其作用
    • 索引的種類
    • 各存儲引擎對于索引的支持
    • 簡單介紹索引的實現
  • 索引的設置與分析
    • 普通索引
    • 唯一索引(Unique Index )
    • 叢生索引（Clustered Index）
      • 叢生索引與其他索引的區別：
    • 查看索引信息
      • 【SHOW INDEX】執行結果說明：
      • 注意事項：
    • 刪除索引
  • 分析索引優劣
    • 【EXPLAIN】命令執行返回參數說明：
      • 參數【select_type】的返回值說明
      • 參數【type】的返回值說明
      • 參數【EXTRA】的返回值說明：
  • 索引失效場合總結
    • 進行后方一致/部分一致檢索的場合
    • 使用了IS NOT NULL、<>比較運算符的場合
    • 對列使用了運算/函數的場合
    • 復合索引的第一列沒有包含在WHERE條件語句中的場合
  • 參考文獻

史上最簡單MySQL教程詳解（進階篇）之索引及其失效場合總結

什么是索引及其作用

在大型數據庫中，一張表通常要容納幾十萬甚至是上百萬的數據，這些表一旦涉及到表連接等復雜操作后，當用戶檢索這么大的數據量時，如果要將數據庫中所有的數據都與想要查詢的數據進行比較的話，是十分慢的。所以為了有效的提高查詢速率，避免對于數據庫進行全盤掃面，我們就需要使用到：索引。

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構，它的存在形式是文件。通俗的說：索引更像是我們在圖書館借書的過程中使用的圖書目錄表。它幫我們將圖書根據書名或者是圖書的類別進行了分類，讓我們可以更加迅速的找到我們所需要的圖書，避免了大范圍的尋找。

索引的種類

MySQL 創建索引，索引是在MYSQL的存儲引擎層中實現的，而不是在服務層實現的。所以每種存儲引擎的索引都不一定完全相同，也不是所有的存儲引擎都支持所有的索引類型。MYSQL目前提供了一下4種索引。

• B-Tree 索引：最常見的索引類型，大部分引擎都支持B樹索引。
• HASH 索引：只有Memory引擎支持，使用場景簡單。
• R-Tree 索引(空間索引)：空間索引是MyISAM的一種特殊索引類型，主要用于地理空間數據類型。
• Full-text (全文索引)：全文索引也是MyISAM的一種特殊索引類型，主要用于全文索引，InnoDB從MYSQL5.6版本提供對全文索引的支持。

各存儲引擎對于索引的支持

索引	MyISAM引擎	InnoDB引擎	Memory引擎
B-Tree 索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-Tree 索引	支持	不支持	不支持
Full-text 索引	不支持	暫不支持	不支持

簡單介紹索引的實現

大多數數據庫都使用了B樹(Balance Tree,平衡樹)的結構來保存索引。B樹就是如下圖所示的一樣，枝葉擴散開來的樹狀結構。

B-TREE 每個節點都是一個二元數組: 一個是保存著關鍵字key,另一個則是指針 pointer。指針則是指向數據所存儲的位置。節點就由這些數據相互關聯起來而組成。

最上層的節點被稱為根節點，最下面的節點被稱為葉子節點。兩者之間的節點被稱為中間節點。B樹中最顯著的一個特點就是：根節點到各個葉子節點的距離都相等。也就意味著，檢索任何值都經過相同數目的節點，能提高檢索效率。

Mysql索引結構？查找的原理：B-樹的搜索，從根結點開始，對結點內的關鍵字（有序）序列進行二分查找，如果命中則結束，否則進入查詢關鍵字所屬范圍的子結點；重復，直到所對應的兒子指針為空，或已經是葉子結點；因此，B-Tree的查找過程是一個順指針查找結點和在結點的關鍵字中進行查找的交叉進行的過程。例如：根據上圖，我們需要查找[K],首先我們去根節點檢索，發現它位于左節點[EH],又去[EH]中查找發現它位于中間節點，就查找到了[K]這個數據。避免了再去檢索右邊部分的節點，有效的提高了檢索的效率。

索引的設置與分析

我們這里主要講解的是使用最多的B-tree索引。其他類型的索引，小伙伴們可以自行學習。

普通索引

這是最基本的索引類型，而且它沒有唯一性之類的限制。普通索引可以通過以下幾種方式創建： （1）創建索引:使用【CREATE INDEX】命令，語法如下：

CREATE INDEX 索引名 ON 表名(列名);

（2）修改表:使用【ALTER TABLE】命令，語法如下：

ALTER TABLE 表名ADD INDEX 索引名 (列名);

（3）創建表時指定索引：使用【CREATE TABLE 】命令，語法如下：

CREATE TABLE 表名 ( [...], INDEX 索引名 (列名) );

唯一索引(Unique Index )

春運進階史就是中國發展史。表示唯一的，不允許重復的索引，如果該字段信息保證不會重復例如身份證號用作索引時，可設置為unique。UNIQUE索引創建語法和普通索引大體上無異，只是增加了關鍵詞：UNIQUE。
（1）創建索引：

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

（2）修改表：

ALTER TABLE 表名ADD UNIQUE 索引名 (列名);

（3）創建表時指定索引：

CREATE TABLE 表名( [...], UNIQUE 索引名 (列名) );

叢生索引（Clustered Index）

叢生索引是伴隨著主鍵的定義而產生的一種特別唯一性索引，相當于聚合索引，是查找最快的索引。因為每張表只能有一個主鍵，所以每張表只能有一個叢生索引。但是需要注意注意的是，叢生索引無法通過【CREATE INDEX】語句創建，而是通過定義主鍵來實現：
（1）主鍵一般在創建表的時候指定，語法為：

CREATE TABLE 表名( [...], PRIMARY KEY (列的列名) )；

（2）修改表的方式加入主鍵：

ALTER TABLE 表名ADD PRIMARY KEY (列的列名)；

叢生索引與其他索引的區別：

• 通常索引在葉子節點中保存指向實際數據的指針，而叢生索引的葉子節點保存的是實際數據。
• 不需要為保存索引而使用專門的硬盤空間，節約了資源
• 不需要檢索索引后再訪問數據表，提高了檢索的效率。
• 創建叢生索引時需要對表中的數據進行排序，在進行數據插入、更新、刪除時比一般的索引需要耗費更多的時間。

查看索引信息

春運進階史？我們使用【SHOW INDEX】命令來查看表中所有創建完成的索引信息，語法如下：

SHOW INDEX FROM 表名；

例如：我們對之前已經創建好的【student】表中的【name】字段創建索引，并用【SHOW INDEX】語句查看相關信息：

mysql> CREATE INDEX index_name ON student(name);
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0mysql> SHOW INDEX FROM student \G
*************************** 1. row ***************************Table: studentNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: studentIdCollation: ACardinality: 5Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:
*************************** 2. row ***************************Table: studentNon_unique: 1Key_name: collegeIdSeq_in_index: 1Column_name: collegeIdCollation: ACardinality: NULLSub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment:
*************************** 3. row ***************************Table: studentNon_unique: 1Key_name: index_nameSeq_in_index: 1Column_name: nameCollation: ACardinality: NULLSub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:
3 rows in set (0.00 sec)

【SHOW INDEX】執行結果說明：

參數名	說明
Table	表名
Non_unique	是否允許重復（1:允許，0：禁止）
Key_name	索引名
Seq_in_index	索引內的域序號（從1開始）
Column_name	域名
Collation	排序（A:升序，Null:不排序）
Cardinality	索引內的非重復值的數目
Sub_part	作為索引部分的域內的字符數
Packed	關鍵字的壓縮方式
Null	是否允許為Null
Index_type	索引的類型
Comment	備注
Index_comment	索引備注

注意事項：

• 因為目前MySQL不支持函數索引，但是能對列的前面某一部分進行索引，例如標題title字段，可以只取title的前10個字符進行索引，這個特性可以大大縮小索引文件的大小，但前綴索引也有缺點，在排序Order By和分組Group By 操作的時候無法使用。用戶在設計表結構的時候也可以對文本列根據此特性進行靈活設計，參數【Sub_part】正是針對這種情況產生。語法如下：

  CREATE INDEX 索引名 ON 表名(字段名(所取字符長度))

• 加入我們在創建索引的時候，同時選擇了多個域，語法如下：

  CREATE INDEX 索引名 ON 表名(列名1，列名2)

  這時，我們創建的索引稱為復合索引。這是我們再通過【SHOW INDEX】命令來查看索引信息，就會發現【Key_name】一樣，但是【Seq_in_index】顯示的就是列的順序了。

刪除索引

進階，如果需要刪除索引，使用的是【DROP INDEX】命令，語法如下：

DROP INDEX 索引名 ON 表名；

分析索引優劣

當我們學會如何創建、修改和刪除索引以后，我們就可以嘗試著自己去分析索引的使用情況以及索引的好壞。我們使用【EXPLAIN】來確認索引的使用情況。（EXPLAIN是大多數數據庫支持的命令，但是使用方法與返回的信息隨著數據庫的不同可能會有所不同），其的具體語法如下：

EXPLAIN 需要分析的SELECT語句

例如：我們刪除之前在【student】表上創建的索引后，執行Select語句，結果如下：

mysql> EXPLAIN SELECT * FROM student WHERE phone= "135" \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 5filtered: 20.00Extra: Using where
1 row in set, 1 warning (0.00 sec)

【EXPLAIN】命令執行返回參數說明：

參數名	說明
id	Select命令的順序號（通常為1，子查詢時從2開始）

參數【select_type】的返回值說明

值	說明
SIMPLE	單純的SELECT命令
PRIMARY	最外層的SELECT命令
UNION	由UNION語句連接的Select命令
DEPENDENT UNION	由UNION語句連接的Select命令（依賴外部查詢）
SUBQUERY	子查詢中的SELECT語句
DEPENDENT SUBQUERY	子查詢中的SELECT語句（依賴外部查詢）
DERIVED	派生表（FROM 語句的子查詢）

參數【type】的返回值說明

值	說明
system	只存在一條數據的表（系統表）
const	擁有PRIMARY KEY/UNION 制約的索引（唯一）
eq_ref	連接時由PRIMARY KEY/UNION 列進行的等值檢索
ref	非UNION列進行的等值檢索
ref_or_null	ref中加入了【~OR 列名 IS NULL】的檢索
range	使用索引檢索一定范圍的數據）（=、>、<、IS Null等運算符）
index	全索引掃描
ALL	全表掃描

參數【EXTRA】的返回值說明：

參數	說明
Using where	列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候。
Using temporary	表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢
Using join buffer	說明獲取連接條件時沒有使用索引，并且需要連接緩沖區來存儲中間結果，可能需要添加索引來改進能。
Impossible where	說明where語句會導致沒有符合條件的數據。
Select tables optimized	說明僅通過使用索引，優化器可能僅從聚合函數結果中返回一行
Using filesort	無法利用索引完成排序

我們在經歷過上面的介紹以后，再去查看我們之前執行的結果，我們可以看到遍歷的次數和實際表中的數據（數據量也是5）是相等的，顯然檢索的效率不是很高。如果在大數據量下，這樣的遍歷次數對于查詢速度的影響幾乎是災難的，所以，我們再重新加入所以后再試一次:

mysql> CREATE INDEX index_phone ON student(phone);
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0mysql> EXPLAIN SELECT * FROM student WHERE phone= "135" \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ref
possible_keys: index_phonekey: index_phonekey_len: 767ref: constrows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.00 sec)

我們發現，遍歷的次數已經降到了只有一次，而且filtered也達到了100%，效率得到了很大程度上的提升。同樣的，如果我們使用索引后，查詢速度未得到改善，我們則需要反思我們的所以設置是否合理，這也就是最簡單的分析索引優劣的方法了。

索引失效場合總結

進行后方一致/部分一致檢索的場合

MySQL索引的實現原理。當我們在使用【LIKE】關鍵詞進行模糊檢索時，只有在前方一致的檢索時能使用上索引，而后方一致或者部分一致是不能使用的。例如下面的兩句查詢語句都是無效的：

SELECT * FROM student WHERE name LIKE "%張%"
SELECT * FROM student WHERE name LIKE "%張"

使用了IS NOT NULL、<>比較運算符的場合

當我們使用了IS NOT NULL、<>比較運算符時，也是不能使用索引的。例如下面的兩句查詢語句都是無效的：

SELECT * FROM student WHERE name IS NOT NULL 
SELECT * FROM student WHERE name <> "張三"

對列使用了運算/函數的場合

當我們在查詢語句中使用了運算或者函數時，索引也是無效的。對于MySQL所具有的函數和運算符可參考這篇博文：史上最簡單MySQL教程詳解（基礎篇）之運算符和常用數據庫函數

復合索引的第一列沒有包含在WHERE條件語句中的場合

例如我們在【student】表創建了基于【name】和【phone】的復合索引，且【name】作為第一列，那么下面的兩句查詢語句都是無效的：

Select * FROM student WHERE phone = "135"；
Select * FROM student WHERE name = "張三" OR phone = "139"；

參考文獻

• 人云思云，MYSQL-索引

• MySQL索引優化？美團技術團隊，MySQL索引原理及慢查詢優化

• 《MySQL高效編程》

轉載說明： 支持轉載，但請保留原作者，原文鏈接，微信公眾號二維碼

掃碼關注作者個人技術公眾號,不定期將有學習資源分享

MySQL索引？