什么是索引

索引是對數(shù)據(jù)庫表中一列或者多列的值進行排序的一種結構，所引用于快速找出在某個列中有一特定值的行。不使用索引，MySQL必須從第一條記錄開始讀完整個表，直到找出相關的行。表越大，查詢數(shù)據(jù)所花費的時間越多，如果表中查詢的列有一個索引，MySQL能快速到達一個位置去搜索數(shù)據(jù)文件，而不必查看所有數(shù)據(jù)。

索引的含義和特點

索引是一個單獨的、存儲在磁盤上的數(shù)據(jù)庫結構，它們包含著對數(shù)據(jù)表里所有記錄的引用指針。使用索引用于快速找出在某個或多個列中有一特定值的行，所有MySQL列類型都可以被索引，對相關列使用索引是提高查詢操作速度的最佳途徑。

例如，數(shù)據(jù)庫里面有20000條記錄，現(xiàn)在要執(zhí)行這么一個查詢：SELECT * FROM table where num = 10000。如果沒有索引，必須遍歷整個表，直到num等于10000的這一行被找到為止；如果在num列上創(chuàng)建索引，MySQL不需要任何掃描，直接在索引中找10000，就可以得知值這一行的位置。可見，索引的建立可以提高數(shù)據(jù)庫的查詢速度。

索引是在存儲引擎中實現(xiàn)的，因此，每種存儲引擎的索引都不一定完全相同，并且每種存儲引擎也不一定支持所有索引類型。所有存儲引擎支持每個表至少16個索引，總索引長度至少為256字節(jié)。大多數(shù)存儲引擎有更高的額限制，MySQL中索引的存儲類型有兩種：BTREE和HASH，具體和表的存儲引擎相關；MyISAM和InnoDB存儲引擎只支持BTREE索引，MEMORY/HEAP存儲引擎可以支持HASH和BTREE縮影。

索引的優(yōu)點主要有：

1、通過創(chuàng)建唯一索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性

2、可以大大加快數(shù)據(jù)的查詢速度，這也是創(chuàng)建索引最主要的原因

3、在實現(xiàn)數(shù)據(jù)的參考完整性方面，可以加速表和表之間的連接

4、在使用分組和排序子句進行數(shù)據(jù)查詢時，也可以顯著減少查詢中分組和排序的時間

增加索引也有許多不利的方面，比如：

1、創(chuàng)建索引和維護索引要耗費時間，并且隨著數(shù)據(jù)量的增加所耗費的時間也會增加

2、索引需要占用磁盤空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果有大量的索引，索引文件可能比數(shù)據(jù)文件更快達到最大文件尺寸

3、當對表中數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)地維護，這樣就降低了數(shù)據(jù)的維護速度

索引的分類

MySQL的索引可以分為以下幾類：

1、普通索引和唯一索引

(1)普通索引是MySQL中的基本索引類型，允許在定義索引的列中插入重復值和空值

(2)唯一索引，索引列的值必須唯一，但允許有空值，主鍵索引是一種特殊的唯一索引，不允許有空值

2、單列索引和組合索引

(1)單列索引即一個索引只包含單個列，一個表可以有多個單列索引

(2)組合索引指在表的多個字段組合上創(chuàng)建的索引，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用

3、全文索引

全文索引類型為FULLTEXT，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復值和空值。全文索引可以在CHAR、VARCHAR或者TEXT類型的列上創(chuàng)建，MySQL中只有MyISAM存儲引擎支持全文索引

索引的設計原則

索引設計不合理或者缺少索引都會對數(shù)據(jù)庫和應用程序的性能造成障礙，高效的索引對于獲得良好的性能非常重要，設計索引時，應該考慮一下：

1、索引并非越多越好，一個表中如有大量的索引，不僅占用磁盤空間，而且會影響INSERT、DELETE、UPDATE等語句的性能，因為當表中的數(shù)據(jù)更改的同時，索引也會進行調整和更新

2、避免對經常更新的表設計過多的索引，并且索引中的列盡可能要少，而對經常用于查詢的字段應該創(chuàng)建索引，但要避免添加不必要的字段

3、數(shù)據(jù)量小的表最好不要使用索引，由于數(shù)據(jù)較少，查詢花費的時間可能比遍歷索引時間還要短，索引可能不會產生優(yōu)化效果

4、在條件表達式中經常用到的不同值較多的列上建立索引，在不同值較少的列上不要建立索引，比如性別字段只有男和女，就沒必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度

5、當唯一性是某種數(shù)據(jù)本身的特征時，指定唯一索引。使用唯一索引需能確保定義的列的數(shù)據(jù)完整性，以提高查詢速度

6、在頻繁排序或分組(即group by或order by操作)的列上建立索引，如果待排序的列有多個，可以在這些列上建立組合索引

創(chuàng)建表的時候創(chuàng)建索引

使用CREATE TABLE創(chuàng)建表的時候，除了可以定義列的數(shù)據(jù)類型，還可以定義主鍵約束、外鍵約束或者唯一性約束，而不論創(chuàng)建哪種約束，在定義約束的同時相當于在指定列上創(chuàng)建了一個索引。創(chuàng)建表時創(chuàng)建索引的基本語法如下：

CREATE TABLE table_name[col_name data_type]

[UNIQUE|FULLTEXT|SPATIAL]

[INDEX|KEY]

[index_name](col_name[length])[ASC|DESC]

解釋一下：

1、UNIQUE、FULLTEXT和SPATIAL為可選參數(shù)，分別表示唯一索引、全文索引和空間索引

2、INDEX和KEY為同義詞，二者作用相同，用來指定創(chuàng)建索引

3、col_name為需要創(chuàng)建索引的字段列，該列必須從數(shù)據(jù)表中該定義的多個列中選擇

4、index_name為指定索引的名稱，為可選參數(shù)，如果不指定則MySQL默認col_name為索引值

5、length為可選參數(shù)，表示索引的長度，只有字符串類型的字段才能指定索引長度

6、ASC或DESC指定升序或者降序的索引值存儲

下面創(chuàng)建一個普通索引，沒有唯一性之類的限制，其作用只是加快對于數(shù)據(jù)的訪問速度：

CREATE TABLEbook

(

bookIdINT NOT NULL,

bookNameVARCHAR(255) NOT NULL,

authorVARCHAR(255) NOT NULL,

infoVARCHAR(255) NOT NULL,

year_publicationYEAR NOT NULL,INDEX(year_publication)

)

確認一下索引是否正在使用，可以使用EXPLAIN：

EXPLAIN select * from book where yead_publication = 1990

結果為：

解釋下字段的意思：

1、select_type行指定所使用的SELECT查詢類型，這里值為SIMPLE，表示簡單的SELECT，不使用UNION或者子查詢。其他可能的取值有：PRIMARY、UNION、SUBQUERY等

2、table行指定數(shù)據(jù)庫讀取的數(shù)據(jù)表的名字，它們按照被讀取的先后順序排列

3、type行指定了本數(shù)據(jù)表與其他數(shù)據(jù)表之間的關聯(lián)關系，可能的去只有system、const、eq_ref、ref、range、index和All

4、possible_keys行給出了MySQL在搜索數(shù)據(jù)記錄時可選用的各個索引

5、key行是MySQL使用的實際索引

6、key_len行給出了索引按字節(jié)計算的長度，key_len數(shù)值越小，表示越快

7、ref行給出了關聯(lián)關系中另外一個數(shù)據(jù)表里的數(shù)據(jù)列的名字

8、rows行是MySQL在執(zhí)行這個查詢時預計會從這個數(shù)據(jù)表里讀出的數(shù)據(jù)行的個數(shù)

9、extra行提供了與關聯(lián)操作有關的信息

看到，possible_keys和key的值都為year_publication，查詢時使用了索引

2、創(chuàng)建唯一索引

唯一索引和普通索引類似，不過唯一索引索引列的值必須唯一，但允許有空值，如果是組合索引，則列值的組合必須唯一。看一下創(chuàng)建唯一索引的方式：

CREATE TABLEuniquetable

(

idINT NOT NULL,

nameCHAR(30) NOT NULL,UNIQUE INDEXUniqIdx(id)

)

這就在表的id字段上創(chuàng)建了一個名為UniqIdx的唯一索引

3、創(chuàng)建單列索引

單列索引是在數(shù)據(jù)表中的某一個字段上創(chuàng)建的索引，一個表中可以創(chuàng)建多個單列索引，前面兩個例子中創(chuàng)建的索引都是單列索引，比如：

CREATE TABLEsingletable

(

idINT NOT NULL,

nameCHAR(30) NOT NULL,UNIQUE INDEX SingleIdx(name(20))

)

這就在name字段上建立了一個名為SingleIdx的單列索引，索引長度為20

4、創(chuàng)建組合索引

組合索引是在多個字段上創(chuàng)建一個索引，比如：

create tableuniontable

(

idINT NOT NULL,

nameCHAR(30) 　　　　NOT NULL,

ageINT NOT NULL,

infoVARCHAR(255),INDEXUnionIdx(id, name, age)

)

這就為id、name和age三個字段成功創(chuàng)建了一個名為UnionIdx的組合索引

5、創(chuàng)建全文索引

全文索引可以對全文進行搜索，只有MyISAM存儲引擎支持全文索引，并且只為CHAR、VARCHAR和TEXT列，索引總是對整個列進行，不支持局部索引，比如：

CREATE TABLEfulltexttable

(

idINT NOT NULL,

nameCHAR(30) NOT NULL,

ageINT NOT NULL,

infoVARCHAR(255),

FULLTEXTINDEXFullTxtIdx(info)

)ENGINE=MyISAM

因為默認的存儲引擎為InnoDB，而全文索引只支持MyISAM，所以這里創(chuàng)建表的時候要手動指定一下引擎。

看到這么創(chuàng)建，就在info字段上成功建立了一個名為FullTxtIdx的FULLTEXT全文索引，全文索引非常適合大型數(shù)據(jù)庫，而對于小的數(shù)據(jù)集，它的用處可能比較小

在已經存在的表上創(chuàng)建索引

在已經存在的表上創(chuàng)建索引，可以使用ALTER TABLE語句或者CREATE INDEX語句，所以，分別講解一下如何使用ALTER TABLE和CREATE INDEX語句在已知的表字段上創(chuàng)建索引。

1、使用ALTER TABLE語句創(chuàng)建索引

ALTER TABLE創(chuàng)建索引的基本語法為：

ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL]

[INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

與創(chuàng)建表時創(chuàng)建索引的語法不同的是，這里用了ALTER TABLE和ADD關鍵字，ADD表示向表中添加索引。以book這張表為例，先看一下這張表里面有哪些索引：

SHOW INDEX FROMbook

看下結果：

解釋一下：

1、table表示創(chuàng)建索引的表

2、Non_unique表示索引不是一個唯一索引，1表示非唯一索引，0表示唯一索引

3、Key_name表示索引的名稱

4、Seq_in_index表示該字段在索引中的位置，單列索引改值該值為1，組合索引為每個字段在索引中定義的順序

5、Column_name表示定義索引的列字段

6、Sub_part表示索引的長度

7、Null表示該字段是否能為空值

8、Index_type表示索引類型

所以，book里面已經有一個索引了，是一個非唯一索引，現(xiàn)在給bookname字段加上索引，SQL語句如下：

ALTER TABLE book ADD INDEX BoNameIdx(bookname(30));

再給bookId字段加上唯一索引，名稱為UniqidIdx：

ALTER TABLE book ADD UNIQUE INDEXUniqidIdx(bookId);

再給author字段加上單列索引：

ALTER TABLE book ADD INDEX BkauthorIdx(author(50));

意思是查詢的時候，只需要檢索前面50個字符。這里專門提一下，對字符串類型的字段進行索引，如果可能應該指定一個前綴長度，例如，一個CHAR(255)的列，如果在前10個或者前30個字符內，多數(shù)值是唯一的，則不需要對整個列進行索引，短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間、減少I/O操作

組合索引和全文索引和創(chuàng)建表時建立索引的方式差不多，就不寫了，此時我們SHOW一下INDEX：

2、使用CREATE INDEX語句創(chuàng)建索引

CREATE INDEX語句可以在已經存在的表上添加索引，MySQL中CREATE INDEX被映射到一個ALTER TABLE語句上，基本語法結構為：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]

看到和ALTER INDEX語句的語法基本一樣，下面把book表刪除了再創(chuàng)建，所有字段都沒有索引，用CREATE INDEX語句創(chuàng)建一次索引：

--為bookname字段建立名為BkNameIdx的普通索引

CREATE INDEX BkNameIdx ONbook(bookname);--為bookid字段建立名為UniqidIdx的唯一索引

CREATE INDEX UniqidIdx ONbook(bookid);--為author和info字段建立名為BkAuAndInfoIdx的組合索引

CREATE INDEX BkAuAndInfoIdx ON book(author(20), info(50));--為year_publication字段建立名為BkyearIdx的普通索引

CREATE INDEX BkyearIdx ON book(year_publication);

此時我們SHOW一下INDEX，可以看到為5個字段建立了4個索引：

刪除索引

最后一項工作就是刪除索引了，可以使用ALTER TABLE和DROP INDEX刪除索引。

1、ALTER TABLE

ALTER TABLE的基本語法為：

ALTER TABLE table_name DROP INDEXindex_name

比如把book的UniqidIdx給刪除了：

ALTER TABLE book DROP INDEX UniqidIdx;

這樣就刪除了book表中的UniqidIdx這個索引，可以SHOW INDEX from book查看一下，這里就不貼圖了

2、DROP INDEX

DROP INDEX的基本語法為：

DROP INDEX index_name ONtable_name

比如我把BkAuAndInfoIdx這個組合索引給刪了：

DROP INDEX BkAuAndInfoIdx ONbook

這樣就把book表里面的BkAuAndInfoIdx這個組合索引給刪除了。

注意一個細節(jié)，刪除表中的列時，如果要刪除的列為整個索引的組成部分，則該列也會從索引中刪除；如果組成索引的所有列都被刪除，則整個索引將被刪除

總結

以上是生活随笔為你收集整理的mysql分别写出3条索引_MySQL3：索引的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。