MySQL索引類型一覽 讓MySQL高效運行起來

索引是快速搜尋的關鍵。MySQL索引的建立對於MySQL的高效運行是很重要的。下面介紹幾種常見的MySQL索引類型。

在資料庫表中，對欄位建立索引可以大大提高查詢速度。假如我們建立了一個 mytable表：

 

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

我們隨機向裡面插入了10000條記錄，其中有一條：5555, admin。

在尋找username="admin"的記錄 SELECT * FROM mytable WHERE username='admin';時，如果在username上已經建立了索引，MySQL無須任何掃描，即準確可找到該記錄。相反，MySQL會掃描所有記錄，即要查詢10000條記錄。

索引分單列索引和複合式索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是複合式索引。複合式索引，即一個索包含多個列。

MySQL索引類型包括：

（1）普通索引

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

◆建立索引

 

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

 

 

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

◆修改表結構

 

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

 

建立表的時候直接指定

 

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

 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  

 
INDEX [indexName] (username(length))  

 
);  
刪除索引的文法：

 

DROP INDEX [indexName] ON mytable;

 

（2）唯一索引

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

◆建立索引

 

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

 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  

 
UNIQUE [indexName] (username(length))  

 
);

（3）主鍵索引

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

 

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

 
username VARCHAR(16) NOT NULL,  
 
PRIMARY KEY(ID)  

 
PRIMARY KEY(ID)  

 
);  

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

（4）複合式索引

為了形象地對比單列索引和複合式索引，為表添加多個欄位：

 

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

 
username VARCHAR(16) NOT NULL,  
 
city VARCHAR(50) NOT NULL,  
 
age INT NOT NULL 

 
city VARCHAR(50) NOT NULL,  
 
age INT NOT NULL 

 
age INT NOT NULL 

 

 

 

為什麼沒有 city，age這樣的複合式索引呢？這是因為MySQL複合式索引“最左首碼”的結果。簡單的理解就是只從最左面的開始組合。並不是只要包含這三列的查詢都會用到該複合式索引，下面的幾個SQL就會用到這個複合式索引：

 

 

SELECT * FROM mytable WHREE username="admin" AND city="鄭州" 
 
SELECT * FROM mytable WHREE username="admin" 

 而下面幾個則不會用到：

 

 

SELECT * FROM mytable WHREE age=20 AND city="鄭州" 
 
SELECT * FROM mytable WHREE city="鄭州" 

（5）建立索引的時機

到這裡我們已經學會了建立索引，那麼我們需要在什麼情況下建立索引呢？一般來說，在WHERE和JOIN中出現的列需要建立索引，但也不完全如此，因為MySQL只對<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE才會使用索引。例如：

SELECT t.Name 
 
FROM mytable t LEFT JOIN mytable m   
 
ON t.Name=m.username WHERE m.age=20 AND m.city='鄭州' 

 

此時就需要對city和age建立索引，由於mytable表的userame也出現在了JOIN子句中，也有對它建立索引的必要。

剛才提到只有某些時候的LIKE才需建立索引。因為在以萬用字元%和_開頭作查詢時，MySQL不會使用索引。例如下句會使用索引：

SELECT * FROM mytable WHERE username like'admin%'

而下句就不會使用：

SELECT * FROM mytable WHEREt Name like'�min'

因此，在使用LIKE時應注意以上的區別。

（6）索引的不足之處

上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：

◆雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案。

◆建立索引會佔用磁碟空間的索引檔案。一般情況這個問題不太嚴重，但如果你在一個大表上建立了多種複合式索引，索引檔案的會膨脹很快。

索引只是提高效率的一個因素，如果你的MySQL有大資料量的表，就需要花時間研究建立最優秀的索引，或最佳化查詢語句。

（7）使用索引的注意事項

使用索引時，有以下一些技巧和注意事項：

◆索引不會包含有NULL值的列

只要列中包含有NULL值都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的預設值為NULL。

◆使用短索引

對串列進行索引，如果可能應該指定一個前置長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字元內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。

◆索引列排序

MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列建立複合索引。

◆like語句操作

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “�a%” 不會使用索引而like “aaa%”可以使用索引。

◆不要在列上進行運算

select * from users where YEAR(adddate)<2007;

將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成

select * from users where adddate<‘2007-01-01’; 

◆不使用NOT IN和<>操作

以上，就對其中MySQL索引類型進行了介紹。