MySQL索引

Most MySQL indexes (

PRIMARY KEY

, 

UNIQUE

INDEX

, and 

FULLTEXT

) are stored in B-trees. 

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX          index_name                
    [         index_type                ]
    ON          tbl_name                 (         index_col_name                ,...)
    [         index_type                ]

         index_col_name                :
             col_name                 [(         length                )] [ASC | DESC]

         index_type                :
    USING {BTREE | HASH}      

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

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

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

1. CREATE TABLE mytable(  
2. ID INT NOT NULL,   
3. username VARCHAR(16) NOT NULL 
4. );

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

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

索引分單列索引群組合索引。單列索引，即一個索引隻包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索包含多個列。

MySQL索引類型包括：

（1）普通索引

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

◆建立索引

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

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

◆修改表結構

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

◆建立表的時候直接指定

3. username VARCHAR(16) NOT NULL,  
4. INDEX [indexName] (username(length))  
5. );  

删除索引的文法：

1. DROP INDEX [indexName] ON mytable; 

（2）唯一索引

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

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

1. ALTER mytable ADD UNIQUE  [indexName]  (username(length))  沒有on，否則報錯》
2. 注意這裡是add unique; 
3. ALTER TABLE `bookmarket`.`users` ADD UNIQUE `pwdIndex` ( `pwd` ) ,

4. UNIQUE [indexName] (username(length))  

（3）主鍵索引

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

4. PRIMARY KEY(ID)  

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

（4）組合索引

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

4. city VARCHAR(50) NOT NULL,  
5. age INT NOT NULL 

為了進一步榨取MySQL的效率，就要考慮建立組合索引。就是将 name, city, age建到一個索引裡：

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

建表時，usernname長度為 16，這裡用 10。這是因為一般情況下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引檔案的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣，遠遠低于我們的組合索引。雖然此時有了三個索引，但MySQL隻能用到其中的那個它認為似乎是最有效率的單列索引。

建立這樣的組合索引，其實是相當于分别建立了下面三組組合索引：

1. usernname,city,age  
2. usernname,city  
3. usernname  

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

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

而下面幾個則不會用到：

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

（5）建立索引的時機

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

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

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

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

1. SELECT * FROM mytable WHERE username like'admin%' 

而下句就不會使用：

1. SELECT * FROM mytable WHEREt Name like'%admin' 

是以，在使用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 “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

◆不要在列上進行運算

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

将在每個行上進行運算，這将導緻索引失效而進行全表掃描，是以我們可以改成

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

◆不使用NOT IN和<>操作

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

via：http://database.51cto.com/art/200910/156685.htm

 組合索引（最左字首）

平時用的SQL查詢語句一般都有比較多的限制條件，是以為了進一步榨取MySQL的效率，就要考慮建立組合索引。例如上表中針對title和time建立一個組合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立這樣的組合索引，其實是相當于分别建立了下面兩組組合索引：

–title,time

–title

為什麼沒有time這樣的組合索引呢？這是因為MySQL組合索引“最左字首”的結果。簡單的了解就是隻從最左面的開始組合。并不是隻要包含這兩列的查詢都會用到該組合索引，如下面的幾個SQL所示：

1

–使用到上面的索引

2	SELECT *  FROM article WHREE title= '測試' AND time =1234567890;

3	SELECT *  FROM article WHREE title= '測試' ;

4

–不使用上面的索引

5	SELECT *  FROM article WHREE  time =1234567890;

MySQL索引的優化

mysql索引總結----mysql 索引類型以及建立