【MySQL從入門到精通】【進階篇】（十九）索引的分類&建立索引的三種方式&删除索引的兩種方式

您好，我是碼農飛哥，感謝您閱讀本文，歡迎一鍵三連哦。​​​​

文章目錄

• ​​1. 索引的分類​​

• ​​1.1. 普通索引​​
• ​​1.2. 唯一性索引​​
• ​​1.3. 主鍵索引​​
• ​​1.4. 單列索引​​
• ​​1.5. 多列（組合、聯合）索引​​
• ​​1.6. 全文索引​​
• ​​1.7. 小結​​

• ​​2. 建立索引​​

• ​​2.1. 建立表的時候建立索引​​

• ​​2.1.1 通過指令檢視索引​​

• ​​2.2. 在已存在的表上建立索引​​

• ​​2.2.1. 使用ALTER TABLE語句建立索引​​
• ​​2.2.2. 使用CREATE INDEX語句建立索引​​

• ​​3. 删除索引​​

• ​​3.1. 方式一​​
• ​​3.2. 方式二​​

• ​​總結​​

1. 索引的分類

MySQL中的索引包括普通索引、全文索引、單列索引、多列索引和空間索引等。

1. 從功能邏輯上說：索引主要有4種，分别是普通索引、唯一索引、主鍵索引、全文索引。
2. 按照實體實作方式，索引可以分為2種：聚簇索引和非聚簇索引。
3. 按照作用字段個數進行劃分，分成單列索引和聯合索引。

1.1. 普通索引

在建立普通索引時，不附加任何限制條件，隻是用于提高查詢效率，這類索引可以建立在任何資料類型中，其值是否唯一或者非空，要由字段本身的完整性限制條件來決定，建立索引以後，可以通過索引進行查詢。例如：在表User的字段name上建立一個普通索引，查詢記錄時就可以根據該索引進行查詢。

1.2. 唯一性索引

使用UNIQE參數 可以設定索引為唯一性索引，在建立唯一性索引時，限制該索引的值必須是唯一的。但允許有空值，在一張資料表裡可以有多個唯一索引。

1.3. 主鍵索引

主鍵索引就是一種特殊的唯一性索引。在唯一索引的基礎上增加了不為空的限制，也就是NOT NULL+UNIQUE，一張表裡最多隻有一個 主鍵索引。

1.4. 單列索引

在表中的單個字段上建立索引。單列索引隻根據該字段進行索引。單列索引可以是普通索引，也可以是唯一性索引，還可以是全文索引。隻要保證該索引隻對應一個字段即可。一個表可以有多個單列索引。

1.5. 多列（組合、聯合）索引

多列索引是在表的多個字段組合上建立一個索引，該索引指向建立時對應的多個字段，可以通過這幾個字段進行查詢，但是隻有查詢條件中使用了這些字段中的第一個字段時才會被使用。例如：在表中的字段id，name和gender上建立一個多列索引idx_id_name_gender，隻有在查詢條件中使用了字段id時該索引才會被使用。使用組合索引時遵循最左字首集合。

1.6. 全文索引

全文索引（也稱全文檢索）是目前搜尋引擎 使用的一種關鍵技術。它能夠利用【分詞技術】等多種算法智能分析出文本文字中關鍵詞的頻率和重要性，然後按照一定的算法規則智能地篩選出我們想要的搜尋結果。全文索引非常适合大型資料集，對于小的資料集，它的用處比較小。

使用參數FULLTEXT 可以設定索引為全文索引。在定義索引的列上支援值的全文查找，允許在這些索引列中插入重複值和空值。全文索引隻能建立在CHAR、VARCHAR或TEXT類型及其系列類型的字段上，查詢資料量較大的字元串類型的字段時，使用全文索引可以提高查詢速度。

1.7. 小結

不同的存儲引擎支援的索引類型也不一樣

1. InnoDB：支援B+Tree、Full-text等索引，不支援Hash索引；
2. MyISAM：支援B+Tree、Full-text等索引，不支援Hash索引；
3. Memory：支援B+Tree、Hash等索引，不支援Full-text等索引；
4. NDB：支援Hash索引，不支援B+Tree、Full-text等索引；
5. Archive：不支援B+Tree、Hash、Full-text等索引

2. 建立索引

前面介紹了索引的分類，下面就介紹下建立索引的三種方式。我們可以在建立表的時候就同時給表建立索引，也可以在已存在的表中建立索引。

MySQL支援多種方法在單個或多個列上建立索引；在建立表的定義語句CREATE TABLE 中指定索引列，使用​

​ALTER TABLE​

​ 語句在存在的表上建立索引，或者使用CREATE INDEX 語句在已存在的表上添加索引。

2.1. 建立表的時候建立索引

在使用CREATE TABLE 建立表時，除了可以定義列的資料類型外，還可以定義主鍵限制，外鍵限制或者唯一性限制，而無論建立哪種限制，在定義限制的同時相當于在指定列上建立了一個索引。

舉例：

CREATE TABLE dept(
  dept_id INT PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(20)
);

CREATE TABLE emp(
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
  emp_name VARCHAR(30) UNIQUE,
    dept_id INT,
  CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id));      

在建立dept表時指定dept_id列為主鍵索引（聚集索引），在建立 emp表時指定emp_name列唯一的限制，并且設定了該表中dept_id列為外鍵

2.1.1 通過指令檢視索引

-- 通過指令檢視索引
#方式1
SHOW CREATE TABLE emp;

#方式2
SHOW INDEX FROM emp;      

可以emp表中有三個索引，分别是主鍵索引PRIMARY，唯一索引 emp_name（未指定索引名的話則直接使用列名作為索引），外鍵索引 emp_dept_id_fk。

如果顯式建立表時建立索引的話，基本文法格式如下：

CREATE TABLE table_name [col_name data_type] 
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name](col_name[legth]) [ASC|DESC]      

1. UNIQUE、FULLTEXT和SPATIAL 為可選參數，分别表示唯一索引、全文索引和空間索引。
2. INDEX與KEY為同義詞，兩者的作用相同，用來指定建立索引；
3. index_name 指定索引的名稱，為可選參數，如果不指定，則MySQL預設col_name為索引名。
4. col_name 為需要建立索引的字段列，該列必須從資料表中定義的多個列中選擇；
5. length 為可選參數，表示索引的長度，隻有字元串類型的字段才能指定索引長度；
6. ASC 或 DESC 指定升序或者降序的索引值存儲。

舉例說明：

CREATE TABLE book(
  book_id INT PRIMARY KEY AUTO_INCREMENT,
  book_name VARCHAR(20),
  `authors` VARCHAR(20),
  UNIQUE INDEX uk_book_name(book_name)
);
SHOW INDEX FROM book;      

在book表的book_name列上增加唯一索引uk_book_name。

插入資料測試一下：

建立了一個給title和body字段添加全文索引的表。

CREATE TABLE `papers`(
  id INT(10) PRIMARY AUTO_INCREMENT,
  title VARCHAR(200) DEFAULT NULL,
  content text,
  FULLTEXT KEY `title`(`title`,content)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;      

不同于like方式的查詢：

SELECT * FROM papers WHERE content LIKE '%查詢字元串%';      

全文索引用match+against方式查詢：

SELECT * FROM papers WHERE MATCH(title,content) AGAINST('查詢字元串');      

明顯提高查詢效率，不過需要注意的是：

1. 使用全文索引前，搞清楚版本支援情況；
2. 全文索引比like+% 塊N倍，但是可能存在精度問題；
3. 如果需要全文索引的是大量資料，建議先添加資料，在建立索引。

2.2. 在已存在的表上建立索引

在已經存在的表中建立索引可以使用ALTER TABLE 語句或者CREATE INDEX 語句。

2.2.1. 使用ALTER TABLE語句建立索引

ALTER TABLE語句建立索引的基本文法如下：

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name](col_name[legth]) [ASC|DESC]      

與建立表時建立索引的文法不同的是：在這裡使用了ALTER TABLE和ADD 關鍵字，ADD表示向表中添加索引。

舉例1：在book2表中的authors字段上建立名為idx_authors的普通索引，在book_id列和book_name列上建立多列索引mul_bid_bname。

ALTER TABLE book2 ADD INDEX idx_authors(authors);
ALTER TABLE book2 ADD INDEX mul_bid_bname(book_id,book_name);      

這裡需要注意的是在多列索引中滿足最左比對原則，也就是查詢條件是多列索引中的第一列時才會使用到索引。

2.2.2. 使用CREATE INDEX語句建立索引

CREATE INDEX語句建立索引的基本文法如下：

CREATE  [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name]  ON table_name(col_name[legth])      

舉例2： 在book3表中的book_name列建立唯一索引uk_idx_bname。

CREATE TABLE book3(
  book_id INT PRIMARY KEY AUTO_INCREMENT,
  book_name VARCHAR(20),
  `authors` VARCHAR(20)
);
INSERT INTO book3(book_name,authors) 
VALUES('Python從入門到精通','碼農飛哥');
INSERT INTO book3(book_name,authors) 
VALUES('MySQL從入門到精通','碼農飛哥2号');
INSERT INTO book3(book_name,authors) 
VALUES('JAVA從入門到精通','碼農飛哥3号');

CREATE UNIQUE INDEX uk_idx_bname ON book3(book_name);      

3. 删除索引

前面介紹了建立索引，那麼如何删除索引呢？tips: 當進行資料大批量插入時，可以先删除索引，然後，等資料插入好之後在建立索引。

删除索引有兩種方式，其基本文法如下：

3.1. 方式一

ALTER TABLE table_name DROP INDEX [index_name]      

3.2. 方式二

DROP INDEX [index_name] ON      

-- 方式一
ALTER TABLE book3 DROP INDEX uk_idx_bname;
-- 方式二
DROP INDEX uk_idx_authors ON book3;      

總結