一、介紹一下索引的類型

Mysql常見索引有：主鍵索引、唯一索引、普通索引、全文索引、組合索引
PRIMARY KEY（主鍵索引） ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`column`)
INDEX(普通索引)      ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
組合索引    ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

Mysql各種索引區(qū)別：

普通索引(INDEX)：最基本的索引，沒有任何限制
唯一索引(UNIQUE)：與"普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。
主鍵索引(PRIMARY)：它 是一種特殊的唯一索引，不允許有空值。
全文索引(FULLTEXT )：僅可用于 MyISAM 表， 用于在一篇文章中，檢索文本信息的, 針對較大的數(shù)據(jù)，生成全文索引很耗時(shí)好空間。
組合索引：為了更多的提高mysql效率可建立組合索引，遵循”最左前綴“原則。

舉個(gè)例子來說，比如你在為某商場做一個(gè)會(huì)員卡的系統(tǒng)。
這個(gè)系統(tǒng)有一個(gè)會(huì)員表
有下列字段：
會(huì)員編號 INT
會(huì)員姓名 VARCHAR(10)
會(huì)員身份證號碼 VARCHAR(18)
會(huì)員電話 VARCHAR(10)
會(huì)員住址 VARCHAR(50)
會(huì)員備注信息 TEXT

那么這個(gè) 會(huì)員編號，作為主鍵，使用 PRIMARY
會(huì)員姓名 如果要建索引的話，那么就是普通的 INDEX
會(huì)員身份證號碼 如果要建索引的話，那么可以選擇 UNIQUE （唯一的，不允許重復(fù)）
會(huì)員備注信息 ， 如果需要建索引的話，可以選擇 FULLTEXT，全文搜索。

不過 FULLTEXT 用于搜索很長一篇文章的時(shí)候，效果最好。
用在比較短的文本，如果就一兩行字的，普通的 INDEX 也可以。

 創(chuàng)建索引：CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))

 刪除索引的語法：DROP INDEX index_name ON tableName

二、索引分單列索引和組合索引

   單列索引：即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引，但這不是組合索引。
   組合索引：即一個(gè)索包含多個(gè)列。

為了形象地對比兩者，再建一個(gè)表：

CREATE TABLE myIndex (

i_testID INT NOT NULL AUTO_INCREMENT,

vc_Name VARCHAR(50) NOT NULL,

vc_City VARCHAR(50) NOT NULL,

i_Age INT NOT NULL,

i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID)

);

在這10000條記錄里面七上八下地分布了5條vc_Name="erquan"的記錄，只不過city,age,school的組合各不相同。
來看這條T-SQL：

首先考慮建單列索引：

    在vc_Name列上建立了索引。執(zhí)行T-SQL時(shí)，MYSQL很快將目標(biāo)鎖定在了vc_Name=erquan的5條記錄上，取出來放到一中間結(jié)果集。在這個(gè)結(jié)果集里，先排除掉vc_City不等于"鄭州"的記錄，再排除i_Age不等于25的記錄，最后篩選出唯一的符合條件的記錄。

    雖然在vc_Name上建立了索引，查詢時(shí)MYSQL不用掃描整張表，效率有所提高，但離我們的要求還有一定的距離。同樣的，在vc_City和i_Age分別建立的單列索引的效率相似。

    為了進(jìn)一步榨取MySQL的效率，就要考慮建立組合索引。就是將vc_Name,vc_City,i_Age建到一個(gè)索引里：
    ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);--注意了，建表時(shí)，vc_Name長度為50，這里為什么用10呢？因?yàn)橐话闱闆r下名字的長度不會(huì)超過10，這樣會(huì)加速索引查詢速度，還會(huì)減少索引文件的大小，提高INSERT的更新速度。

    執(zhí)行T-SQL時(shí)，MySQL無須掃描任何記錄就到找到唯一的記錄??！

    肯定有人要問了，如果分別在vc_Name,vc_City,i_Age上建立單列索引，讓該表有3個(gè)單列索引，查詢時(shí)和上述的組合索引效率一樣吧？嘿嘿，大不一樣，遠(yuǎn)遠(yuǎn)低于我們的組合索引~~雖然此時(shí)有了三個(gè)索引，但MySQL只能用到其中的那個(gè)它認(rèn)為似乎是最有效率的單列索引。

    建立這樣的組合索引，其實(shí)是相當(dāng)于分別建立了

    vc_Name,vc_City,i_Age
    vc_Name,vc_City
    vc_Name

    這樣的三個(gè)組合索引！為什么沒有vc_City,i_Age等這樣的組合索引呢？這是因?yàn)閙ysql組合索引"最左前綴"的結(jié)果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會(huì)用到該組合索引，下面的幾個(gè)T-SQL會(huì)用到：

  SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="鄭州"
  SELECT * FROM myIndex WHREE vc_Name="erquan"

而下面幾個(gè)則不會(huì)用到：

  SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="鄭州"
  SELECT * FROM myIndex WHREE vc_City="鄭州"

三、使用索引

    到此你應(yīng)該會(huì)建立、使用索引了吧？但什么情況下需要建立索引呢？一般來說，在WHERE和JOIN中出現(xiàn)的列需要建立索引，但也不完全如此，因?yàn)镸ySQL只對 <，<=，=，>，>=，BETWEEN，IN，以及某些時(shí)候的LIKE(后面有說明)才會(huì)使用索引。
    SELECT t.vc_Name FROM testIndex t LEFT JOIN myIndex m ON t.vc_Name=m.vc_Name WHERE m.i_Age=20 AND m.vc_City='鄭州'  時(shí)，有對myIndex表的vc_City和i_Age建立索引的需要，由于testIndex表的vc_Name開出現(xiàn)在了JOIN子句中，也有對它建立索引的必要。

    剛才提到了，只有某些時(shí)候的LIKE才需建立索引？是的。因?yàn)樵谝酝ㄅ浞?% 和 _ 開頭作查詢時(shí)，MySQL不會(huì)使用索引，如

  SELECT * FROM myIndex WHERE vc_Name like'erquan%'

會(huì)使用索引，而

  SELECT * FROM myIndex WHEREt vc_Name like'%erquan'

    就不會(huì)使用索引了。

四、索引的不足之處

    上面說了那么多索引的好話，它真的有像傳說中那么優(yōu)秀么？當(dāng)然會(huì)有缺點(diǎn)了。

    1.雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件

    2.建立索引會(huì)占用磁盤空間的索引文件。一般情況這個(gè)問題不太嚴(yán)重，但如果你在一個(gè)大表上創(chuàng)建了多種組合索引，索引文件的會(huì)膨脹很快。

篇尾：

    講了這么多，無非是想利用索引提高數(shù)據(jù)庫的執(zhí)行效率。不過索引只是提高效率的一個(gè)因素。如果你的MySQL有大數(shù)據(jù)的表，就需要花時(shí)間研究建立最優(yōu)秀的索引或優(yōu)化查詢語句。

以上所述就是本文的全部內(nèi)容了，希望大家能夠喜歡。

最新評論