MySQL中索引的類型

一般可分為四類：

• 普通索引：最普通的索引
• 唯一索引：索引列的值必須唯一，但允許有空值
• 主鍵索引：一種特殊的唯一索引，不允許有空值
• 聯合索引：索引列有多個字段，使用時需要滿足最左前綴原則

普通索引

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

1.創建索引

代碼如下:

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

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

2.修改表結構

代碼如下:

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;

唯一索引

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

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))  
);

主鍵索引

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

代碼如下:

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

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

聯合索引

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

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);

索引用不上的情況

索引不是每次都可以生效的，如果我們操作不對，那么很有可能是走全表掃描而不是走的索引，Explain 中的 possible_key、key_len 以及 key 參數都可以分析我們的 SQL 語句是否用到了索引。

以下情況將會導致索引失效

1. 查詢的列中使用了 != 比如 select id,name,age from student where id != 2;
2. 查詢的列中使用了函數操作，比如 pow(id,2) 對 id 做平方這種函數表達式，也不會用到索引
3. 如果條件中有 or，即使其中有部分條件帶索引也不會使用（這也是為什么盡量少用or的原因）
4. 聯合索引中即使滿足最左前綴原則，但是第一個條件帶了范圍查詢，那么也不會用到索引
5. 存在索引列的數據類型隱形轉換，則用不上索引，比如列類型是字符串，那一定要在條件中將數據使用引號引用起來,否則不使用索引
6. 如果 MySQL 估計使用全表掃描要比使用索引快,則不使用索引

以上都可以使用 Explain 進行測試是否用到了索引。

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對腳本之家的支持。