MySQL中索引的類型

一般可分為四類：

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

普通索引

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

1.創(chuàng)建索引

代碼如下:

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

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

2.修改表結(jié)構(gòu)

代碼如下:

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

創(chuàng)建表的時(shí)候直接指定

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

刪除索引的語(yǔ)法：

DROP INDEX [indexName] ON mytable;

唯一索引

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

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

修改表結(jié)構(gòu)：

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

創(chuàng)建表的時(shí)候直接指定：

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

主鍵索引

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

代碼如下:

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

當(dāng)然也可以用 ALTER 命令。記住：一個(gè)表只能有一個(gè)主鍵。

聯(lián)合索引

為了形象地對(duì)比單列索引和組合索引，為表添加多個(gè)字段：

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

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

代碼如下:

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

索引用不上的情況

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

以下情況將會(huì)導(dǎo)致索引失效

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

以上都可以使用 Explain 進(jìn)行測(cè)試是否用到了索引。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。