MySQL數據庫之索引詳解

今天繼續給大傢介紹MySQL相關知識，本文主要內容是MySQL索引相關內容。

一、MySQL索引簡介

索引是MySQL數據庫為瞭加快數據查詢的速度，給表中的某一個或者是某幾個列添加的一種“目錄”。MySQL的索引是一個特殊的文件，但是InnoDB類型引擎（關於MySQL的引擎我們會在今後的文章中進行講解）的表的索引是表空間的一個組成部分。
MySQL數據庫一共支持5種類型的索引，分別是普通索引、唯一性索引、主鍵索引、復合索引和全文索引，下面，我將對這四種類型的索引一一介紹。

二、MySQL五種類型索引詳解

（一）普通索引

普通索引是MySQL數據庫中的一種普通的索引，添加普通索引的列對數據沒有特殊要求，普通索引能起到的作用就是加快差查詢速度。
在創建數據表時添加普通索引SQL語句示例如下：

create table exp(id int , name varchar(20),index exp_name(name));

或者是把index換成key，如下：

create table exp (id ,int , name varahcr (20) ,  key exp_name(name));

在上述SQL命令中，key或者index表示添加索引，後面緊跟著的是索引名稱，後面括號裡的是要添加索引的列。
本文介紹的所有索引相關的SQL語句，如果沒有特殊說明，index都可以換成key，為瞭節省文章篇幅，這一點在以後就不再贅述瞭。
此外，我們也可以在添加索引時，不指定索引的名稱，這時，MySQL會自動為該索引添加與該字段同名的索引名。
執行結果如下：

創建數據表後向表內新添加普通索引SQL語句示例如下：

alter table exp add index exp_id(id);

執行結果如下：

創建數據表後刪除普通索引的SQL語句示例如下：

alter table drop index exp_name;

執行結果如下：

註意，在上述命令中，exp_name是索引的名字而不是含有索引的字段的名字，如果我們忘記瞭該表中的索引名稱，可以執行以下SQL命令進項查詢：

show index from exp;

其中，exp是表名，該命令執行結果如下：

從上面幾張圖片可以看出，添加普通索引後，在使用desc查看表結構時，會發現Key列上出現MUL，這就表示該列添加有普通索引。

（二）唯一性索引

唯一性索引，是在普通索引的基礎上，要求添加該索引的列所有的值隻能出現一次。唯一性索引常用語添加在註入身份證號、學號等字段中，不可以添加在註入名字、等字段中。
唯一性索引的添加與普通索引幾乎完全相同，隻不過要把普通索引的關鍵字key和index換成unique key和unique index。
在創建數據表時添加唯一性索引的SQL語句示例如下：

create table exp (id int, name varchar(20), unique key (name))；

上述命令執行結果如下：

可以看出，添加唯一性索引的字段，在使用desc命令查詢表結構時，Key列中會顯示UNI，表示該字段添加瞭唯一性索引。
在創建數據表後添加唯一性索引的SQL語句實例如下：

刪除唯一性索引的SQL語句示例如下：

alter table exp drop index name;

執行結果如下：

（三）主鍵索引

主鍵索引，是數據庫的所有索引中查詢速度最快的，並且每個數據表隻能有1個主鍵索引列。主鍵索引的列，不允許出現重復的數據，也不允許為空值。
添加、刪除主鍵索引與普通索引和唯一性索引非常相似，隻不過將key換成瞭primary key而已。相關SQL命令如下：

create table exp(id int ,name varchar(20), primary key (id));alter table exp add primary key (id);

添加瞭主鍵索引的列，會在desc查看表結構時Key列上顯示PRI，如下所示：

主鍵索引的刪除可以執行命令：

alter table exp drop primary key;

註意，在該SQL語句總，key不能換成index。
有時，我們在嘗試刪除主鍵索引時，MySQL後拒絕，這可能是因為該字段添加瞭auto_increment屬性的緣故，我們可以把該字段修飾符刪除，就可以刪除該字段的主鍵索引瞭，如下所示：

（四）復合索引

如果想要創建一個包含不同的列的索引，我們就可以創建符合索引。其實，復合索引在業務場景中應用的非常頻繁，比如，如果我們想要記錄數據包的內容，則需要將IP和端口號作為標識數據包的依據，這時就可以把IP地址的列和端口號的列創建為復合索引。復合、添加和刪除索引創建SQL語句示例如下：

create table exp (ip varchar(15),port int ,primary key (ip,port));
alter table exp add pirmary key(ip ,port);
alter table exp dorp priamary key;

註意，復合索引相當於一個多列的主鍵索引，因此，添加復合索引的任何一個列都不允許數據為空，並且這些列不允許數據完全相同，否則MySQL數據庫會報錯。如下所示：

（五）全文索引

全文索引主要是用於解決大數據量的情況下模糊匹配的問題。如果數據庫中某個字段的數據量非常大，那麼如果我們想要使用like+通配符的方式進行查找，速度就會變得非常慢。針對這種情況，我們就可以使用全文索引的方式，來加快模糊查詢的速度。全文索引的原理便是通過分詞技術，分析處文本中關鍵字及其出現的頻率，並依次建立索引。全文索引的使用，與數據庫版本、數據表引擎乃至字段類型息息相關，主要限制如下：
1、MySQL3.2版本以後才支持全文索引。
2、MySQL5.7版本以後MySQL才內置ngram插件，全文索引才開始支持中文。
3、MySQL5.6之前的版本，隻有MyISAM引擎才支持全文索引。
4、MySQL5.6以後的版本，MyISAM引擎和InnoDB引擎都支持全文索引。
5、隻有字段數據類型為char、varchar、以及text的字段才支持添加全文索引。
創建、添加以及刪除全文索引SQL命令如下：

create table exp (id int ,content text ,filltext key (content))engine=MyISAM;
alter table exp add fulltext index (content);
alter table exp drop index content;

部分執行結果如下：

在創建瞭全文索引後，也不能夠使用like+通配符的方式進行模糊查詢，全文索引的使用有其特定的語法，如下所示：

select * from exp where match(content) against ('a');

其中，match後面的括號裡是含有全文索引的字段，against後面的括號裡是要模糊匹配的內容。
此外，全文索引的作用並不是唯一的，在很多場景下，我們並不會使用MySQL數據庫內置的全文索引，而是使用第三方類似的索引以實現相同的功能。

三、MySQL索引使用原則

1、索引是典型的“以空間換時間”的策略，它會消耗計算機存儲空間，但是會加快查詢速度。
2、索引的添加，盡管加快瞭在查詢時的查詢速度，但是會減慢在插入、刪除時的速度。因為在插入、刪除數據時需要進行額外的索引操作。
3、索引並非越多越好，數據量不大時不需要添加索引。
4、如果一個表的值需要頻繁的插入和修改，則不適合建立索引，反制，如果一個表中某個字段的值要經常進行查詢、排序和分組的字段則需要建立索引。
5、如果一個字段滿足建立唯一性索引的條件，就不要建立普通索引。

總結

到此這篇關於MySQL數據庫之索引詳解的文章就介紹到這瞭,更多相關MySQL索引內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：

• MySQL的索引原理以及查詢優化詳解
• mysql添加索引方法詳解(Navicat可視化加索引與sql語句加索引)
• Python全棧之學習MySQL(1)
• MySQL索引結構詳細解析
• 查看修改MySQL表結構命令