C/C++：關(guān)于MySQL數(shù)據(jù)庫中的索引

在數(shù)據(jù)庫操作中，經(jīng)常需要查找特定的數(shù)據(jù)，例如：當(dāng)執(zhí)行“select * from student where id = 10000”語句時(shí)，MySQL數(shù)據(jù)庫必須從第一條記錄開始遍歷，直到找到id為10000的數(shù)據(jù)。這樣的效率非常低。為此，MySQL允許建立索引來加快數(shù)據(jù)表的查詢和排序。

索引的概念

數(shù)據(jù)庫的索引好比字典的目錄，是對(duì)數(shù)據(jù)庫表中一列或者多了的值進(jìn)行排序后的一種結(jié)構(gòu)，其作用就是提高表中的數(shù)據(jù)查詢速度。MySQL中的索引分為很多種，具體如下。

1. 普通索引

普通索引是由key或index定義個(gè)索引，它是MySQL中的基本索引類型，可以創(chuàng)建在任何數(shù)據(jù)類型中。其值是否唯一和非空有字段本身的約束條件所決定。例如，在student表的id字段上建立一個(gè)普通索引，查詢記錄時(shí)，就可以根據(jù)該索引查詢，從而提高效率。

2. 唯一性索引

唯一性索引是指由unique定義個(gè)索引，該索引所在字段的值必須是唯一的。例如，在grade表的stu_id字段上建立唯一性索引，那么stu_id字段的值就必須是唯一的。

3. 全文索引

全文索引是由fulltext定義的索引，它只能創(chuàng)建在char、varchar或text類型的字段上。并且現(xiàn)在只有MyISAM存儲(chǔ)引擎支持全文索引。

4. 單列索引

單列索引指的是在表中單個(gè)字段上創(chuàng)建索引，它可以是普通索引、唯一索引或者全文索引，只有保證該索引只對(duì)應(yīng)表中一個(gè)字段即可。

5. 多列索引

多列索引是指在表的多個(gè)字段上創(chuàng)建索引，只有在查詢條件中使用了這些字段中的第一個(gè)字段時(shí)，該索引才會(huì)被使用。如，在student表的id、name和score字段上創(chuàng)建一個(gè)多列索引，那么只有查詢條件中使用了id字段時(shí)，該索引才會(huì)被使用。

相較于單列索引，當(dāng)我們頻繁的需要同時(shí)檢索表中多列時(shí)，多列索引的效率會(huì)高很多。

6. 空間索引

空間索引是由spatial定義的索引，它只能創(chuàng)建在空間數(shù)據(jù)類型的字段上。MySQL中的空間數(shù)據(jù)類型有4種：geometry、point、linestring和polygon。需要注意的是，創(chuàng)建空間索引的字段，必須將其聲明為NOT NULL，并且空間索引只能在存儲(chǔ)引擎為MyISAM的表中創(chuàng)建。

需要注意的是，雖然索引可以提高數(shù)據(jù)的查詢效率，但索引會(huì)占用一定的存儲(chǔ)空間。并且創(chuàng)建和維護(hù)索引所消耗的時(shí)間，是隨著數(shù)據(jù)量的增加而增加的。因此，使用索引時(shí)，應(yīng)綜合考慮其優(yōu)缺點(diǎn)，不能肆意創(chuàng)建。

創(chuàng)建索引

要想使用索引提高數(shù)據(jù)表的訪問速度，首先要?jiǎng)?chuàng)建一個(gè)索引。創(chuàng)建索引的方式大致可分為三種。

創(chuàng)建表的同時(shí)創(chuàng)建索引

創(chuàng)建表的時(shí)候可以直接創(chuàng)建索引，這種方式最簡(jiǎn)單、方便，其基本語法格式如下所示：

create table 表名 (字段名 數(shù)據(jù)類型 [完整性約束條件],

字段名 數(shù)據(jù)類型 [完整性約束條件],

……

字段名 數(shù)據(jù)類型

[unique|fulltext|spatial] index|key

[別名] (字段名1 [(長(zhǎng)度)]) [asc|desc]

);

關(guān)于上述語法相關(guān)解釋具體如下：

1) unique：可選參數(shù)，表示唯一索引。

2) fulltext：可選參數(shù)，表示全文索引。

3) spatial：可選參數(shù)，表示空間索引

4) index和key：用來表示字段的索引， 二者選一即可。

5) 別名：可選參數(shù)，表示穿件的索引名稱。

6) 字段名1：指定索引對(duì)應(yīng)字段的名稱。

7) 長(zhǎng)度：可選參數(shù)，用于表示索引的長(zhǎng)度。

8) asc和desc：可選參數(shù)。asc表升序，desc表降序排列。

MySQL中的6種索引類型，如下：

1) 創(chuàng)建普通索引

【例】在t1表中id字段上創(chuàng)建索引，SQL語句如下：

create table t1 ( id int,

name varchar(20),

score float,

index(id)

);

可使用explain語句查看索引是否被使用，SQL語句如下：

explain select * from t1 where id = 1 ;

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

【例】創(chuàng)建一個(gè)表名為t2的表，在表中的id字段上建立索引名為unique_id的唯一性索引，并按升序排列，SQL語句如下：

create table t2 ( id int not null,

name varchar(20) not null,

score float,

unique index unique_id(id asc)

);

這樣，便在id字段上建立了一個(gè)名為unique_id的唯一性索引。

3) 創(chuàng)建全文索引

【例】創(chuàng)建一個(gè)表名為t3的表，在表中的name字段上建立索引名為fulltext_name的全文索引，SQL語句如下：

create table t3 ( id int not null,

name varchar(20) not null,

score float,

fulltext index fulltext_name(name)

)engine=MyISAM;

這樣，即可在name字段上建立一個(gè)名為fulltext_name的全文索引。

需要注意的是，由于目前只有MyISAM存儲(chǔ)引擎支持全文索引，默認(rèn)的InnoDB存儲(chǔ)引擎不支持全文索引。因此，在建立全文索引時(shí)，一定要注意表存儲(chǔ)引擎的類型，對(duì)于經(jīng)常需要索引的字符串、文字?jǐn)?shù)據(jù)等信息，可以考慮存儲(chǔ)到MyISAM存儲(chǔ)引擎的表中。

4) 創(chuàng)建單列索引

【例】創(chuàng)建一個(gè)表名為t4的表，在表中的name字段上建立索引名為single_name的單列索引，SQL語句如下：

create table t4 ( id int not null,

name varchar(20) not null,

score float,

index single_name(name(20))

);

這樣，即可在name字段上建立一個(gè)名稱為single_name的單列索引，并且索引的長(zhǎng)度為20。

5) 創(chuàng)建多列索引

【例】創(chuàng)建一個(gè)表名為 t5的表，在表中的id和name字段上建立索引名為multi的多列索引，SQL語句如下：

create table t5 ( id int not null,

name varchar(20) not null,

score float,

index nulti(id, name(20))

);

這樣，即可在id和name字段上建立一個(gè)名為multi的多列索引。

需要注意的是，在多列索引中，只有查詢條件中使用了這些字段中的第一個(gè)字段時(shí)多列索引才會(huì)被使用。

為了驗(yàn)證這個(gè)說法是否正確，將id字段作為查詢條件，通過explain語句查可看索引的使用情況，SQL語句如下：

explain select * from t5 where id = 1;

但是，如果只使用name字段作為查詢條件，multi索引不會(huì)被使用。

6) 創(chuàng)建空間索引

【例】創(chuàng)建一個(gè)表名為t6的表，在空間類型為geometry的字段上創(chuàng)建空間索引，SQL語句如下：

create table t6 ( id int,

space geometry not null,

spatial index sp(space)

) engine=MyISAM;

這樣，即可在t6表中的space字段上建立名稱為sp的空間索引了。

需要注意的是，創(chuàng)建空間索引時(shí)，所在字段的值不能為空值，并且表的存儲(chǔ)引擎為MyISAM。

使用 create index 語句在已經(jīng)存在的表上創(chuàng)建索引

若想在一個(gè)已經(jīng)存在的表上創(chuàng)建索引，可以使用 create index語句，其創(chuàng)建索引的具體語法格式如下所示：

create [unique|fulltext|spatial] index 索引名

on 表名 (字段名 [(長(zhǎng)度)] [asc|desc]);

在上述語法格式中，unique、fulltext和spatial都是可選參數(shù)，分別用于表示唯一性索引、全文索引和空間索引;index用于指明字段為索引。

為了更好的展示如何使用create index語句在已經(jīng)存在的表上創(chuàng)建索引，接下來創(chuàng)建一個(gè)book表，該表中沒有建立任何索引，創(chuàng)建book表的SQL語句如下：

create table book (

bookid int not null,

bookname varchar(255) not null,

authors varchar(255) not null,

info varchar(255) null,

comment varchar(255) null,

publicyear year not null

);

創(chuàng)建好數(shù)據(jù)表book后，通過具體案例演示如何使用create index語句在已經(jīng)存在的數(shù)據(jù)表中創(chuàng)建索引，具體如下：

1) 創(chuàng)建普通索引

【例】在book表中的bookid字段上建立一個(gè)名稱為index_id的普通索引，SQL語句如下：

create index index_id on book (bookid);

這樣，即可在book表中，為bookid字段建立一個(gè)名稱為index_id的普通索引。

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

【例】在book表中的bookid字段上建立一個(gè)名稱為uniqueidx的唯一性索引，SQL語句如下：

create unique index uniqueidx on book (bookid);

這樣，即可在book表中，為bookid字段建立一個(gè)名稱為uniqueidx的唯一性索引。

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

【例】在book表中的comment字段上建立一個(gè)名稱為singleidx的單列索引，SQL語句如下所示：

create index singleidx on book (comment);

這樣，即可在book表中，為comment字段建立一個(gè)名稱為singleidx的單列索引。

4) 創(chuàng)建多列索引

【例】在book表中的authors字段和info字段上建立一個(gè)名稱為mulitidx的多列索引，SQL語句如下所示：

create index mulitidx on book (authors(20), info(20));

這樣，即可在book表中，為authors和info字段建立一個(gè)名稱為mulitidx的多列索引。

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

【例】刪除表book，重新創(chuàng)建表book， 在book表中的info字段上建立全文索引。首先刪除book表。SQL語句如下所示：

drop table book;

然后重新創(chuàng)建表book，SQL語句如下所示：

create table book (

bookid int not null,

bookname varchar(255) not null,

authors varchar(255) not null,

info varchar(255) null,

comment varchar(255) null,

publicyear year not null

)engine=MyISAM;

接下來使用create index 語句在book表的info字段上創(chuàng)建名稱為fulltextidx的全文索引，SQL語句如下所示：

create fulltext index fulltextidx on book (info);

這樣，即可在book表中，為info字段建立一個(gè)名稱為fulltextidx的全文索引。

6) 創(chuàng)建空間索引

【例】創(chuàng)建表t7，在表中的g字段上創(chuàng)建名稱為spatialidx的空間索引。

首先創(chuàng)建數(shù)據(jù)表t7，SQL語句如下所示：

create table t7 (g geometry not null) engine=MyISAM;

使用create index 語句在t7表的g字段上，創(chuàng)建名稱為spatialidx的空間索引，SQL語句如下所示：

create spatial index spatialidx on t7 (g);

這樣，即可在t7表中，為g字段建立一個(gè)名稱為spatialidx的空間索引。

使用alter table語句在已經(jīng)存在的表上創(chuàng)建索引

在已經(jīng)存在的表中創(chuàng)建索引，除了可以使用create index語句外，還可以使用alter table語句來完成。其語法格式：

alter table 表名 add [unique|fulltext|spatial] index

索引名 (字段名 [(長(zhǎng)度)] [asc|desc])

在上述語法格式中，unique、fulltext和spatial都是可選參數(shù)，分別用于表示唯一性索引、全文索引和空間索引;add表示向表中添加字段。

接下來，同樣以book表為例，對(duì)不同類型的索引進(jìn)行說明， 為了使book表不包含任何索引，首先刪除book表，SQL語句如下：

drop table book;

然后重新建立book表，SQL語句如下：

create table book (

bookid int not null,

bookname varchar(255) not null,

authors varchar(255) not null,

info varchar(255) null,

comment varchar(255) null,

publicyear year not null

);

創(chuàng)建好數(shù)據(jù)表book后，就可以使用alter table語句在已存在的數(shù)據(jù)表中創(chuàng)建索引了，具體如下：

1) 創(chuàng)建普通索引

【例】在表中的bookid字段上創(chuàng)建名稱為index_id的普通索引，SQL語句如下：

alter table book add index index_id(bookid);

這樣，即可在book表中，為bookid字段建立一個(gè)名稱為index_id的普通索引。

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

【例】在book表中的bookid字段上建立一個(gè)名稱為uniqueidx的唯一性索引，SQL語句如下：

alter table book add unique uniqueidx(bookid);

這樣，即可在book表中，為bookid字段建立一個(gè)名稱為uniqueidx的唯一性索引。

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

【例】在book表中的comment字段上建立一個(gè)名稱為singleidx的單列索引，SQL語句如下所示：

alter table book add index singleidx(comment(50));

這樣，即可在book表中，為comment字段建立一個(gè)名稱為singleidx的單列索引。

4) 創(chuàng)建多列索引

【例】在book表中的authors字段和info字段上建立一個(gè)名稱為mulitidx的多列索引，SQL語句如下所示：

alter table book add index multidx(authors(20), info(50));

這樣，即可在book表中，為authors和info字段建立一個(gè)名稱為mulitidx的多列索引。

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

【例】刪除表book，重新創(chuàng)建表book， 在book表中的info字段上建立全文索引。首先刪除book表。SQL語句如下所示：

drop table book;

然后重新創(chuàng)建表book，SQL語句如下所示：

create table book (

bookid int not null,

bookname varchar(255) not null,

authors varchar(255) not null,

info varchar(255) null,

comment varchar(255) null,

publicyear year not null

)engine=MyISAM;

接下來使用alter table 語句在book表的info字段上創(chuàng)建名稱為fulltextidx的全文索引，SQL語句如下所示：

alter table book add fulltext index fulltextidx(info);

這樣，即可在book表中，為info字段建立一個(gè)名稱為fulltextidx的全文索引。

6) 創(chuàng)建空間索引

【例】創(chuàng)建表t8，在表中的space字段上創(chuàng)建名稱為spatialidx的空間索引。

首先創(chuàng)建數(shù)據(jù)表t8，SQL語句如下所示：

create table t8(space geometry not null) engine=MyISAM;

使用alter table 語句在t8表的space字段上，創(chuàng)建名稱為spatialidx的空間索引，SQL語句如下所示：

alter table t8 add spatial index spatialidx (space);

這樣，即可在t8表中，為space字段建立一個(gè)名稱為spatialidx的空間索引。

刪除索引

由于索引會(huì)占用一定的磁盤空間，因此，為了避免影響數(shù)據(jù)庫性能，應(yīng)該及時(shí)刪除不再使用的索引。刪除索引的方法有兩種，如下：

使用alter table刪除索引

使用alter table刪除索引的基本語法格式如下所示：

alter table 表名 drop index 索引名

【例】刪除表book中名稱為fulltextidx的全文索引

alter table book drop index fulltextidx;

上述SQL語句執(zhí)行后，可以使用show create table語句查看表結(jié)構(gòu)，來確認(rèn)索引是否已經(jīng)成功被刪除。

show create talbe book;

使用drop index刪除索引

使用drop index刪除索引的基本語法格式如下所示：

drop index 索引名 on 表名;

【例】刪除表t8中名稱為spatialidx的空間索引，SQL語句如下：

drop index spatialidx on t8

使用show create table 語句查看表結(jié)構(gòu)

show create table t8

發(fā)現(xiàn)，t8表中名稱為spatialidx的索引被成功刪除了。

本文版權(quán)歸黑馬程序員C/C++學(xué)院所有，歡迎轉(zhuǎn)載，轉(zhuǎn)載請(qǐng)注明作者出處。謝謝！

作者：黑馬程序員C/C++培訓(xùn)學(xué)院

首發(fā)：http://c.itheima.com/