MySQL數(shù)據(jù)庫(kù)的高效檢索如何實(shí)現(xiàn)？

在數(shù)據(jù)庫(kù)操作中，常常需要完成既定數(shù)據(jù)的檢索。少量數(shù)據(jù)存放在表中，只需使用基本的SQL語(yǔ)句即可檢索得到。但當(dāng)數(shù)據(jù)量較大時(shí)，受MySQL數(shù)據(jù)庫(kù)底層實(shí)現(xiàn)原理的限制，缺省的SQL語(yǔ)句，檢索效率較低。

例如：當(dāng)執(zhí)行“select * from employee where empno= 520000”語(yǔ)句按員工號(hào)查詢時(shí)，MySQL數(shù)據(jù)庫(kù)默認(rèn)的處理方式是從第一條記錄開始依次向后遍歷，直到找到id為520000的數(shù)據(jù)。這樣，查找的效率隨著數(shù)據(jù)量的增大，而逐步降低。為此，MySQL數(shù)據(jù)庫(kù)在建表時(shí)允許通過創(chuàng)建索引來(lái)加快數(shù)據(jù)表的查詢、排序等相關(guān)操作。

一、什么是索引

在MySQL數(shù)據(jù)庫(kù)中，索引和表、視圖、同義詞等類似是數(shù)據(jù)庫(kù)“對(duì)象”的一種?？煽醋鲎值涞哪夸?。是對(duì)數(shù)據(jù)庫(kù)表中一列或者多了的值進(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)建。

二、索引的生成

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

1、創(chuàng)建表指定索引

在MySQL中，創(chuàng)建表時(shí)可以直接給表的指定字段指定索引，這種方式需在建表之初就預(yù)設(shè)了數(shù)據(jù)即將被頻繁檢索的場(chǎng)景，其基本語(yǔ)法格式如下所示：

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

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

                        ……

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

                            [unique|fulltext|spatial]  index|key

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

                        );

 相關(guān)語(yǔ)法解析：

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

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

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

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

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語(yǔ)句如下：

create table t1 (   id int,

                    name varchar(20),

                    score float,

                    index(id)

                 );

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

explain select * from t1 where id = 1 ;

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

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

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語(yǔ)句如下：

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語(yǔ)句如下：

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語(yǔ)句如下：

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è)說(shuō)法是否正確，將id字段作為查詢條件，通過explain語(yǔ)句查可看索引的使用情況，SQL語(yǔ)句如下：

explain select * from t5 where id = 1;

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

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

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

create table t6 (   id int,

                    space geometry not null,

                    spatial index sp(space)

                 ) engine=MyISAM;

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

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

2、已有表創(chuàng)建索引

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

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

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

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

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

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語(yǔ)句在已經(jīng)存在的數(shù)據(jù)表中創(chuàng)建索引，具體如下：

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

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

create index index_id on book (bookid);

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

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

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

create unique index uniqueidx on book (bookid);

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

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

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

create index singleidx on book (comment);

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

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

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

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語(yǔ)句如下所示：

drop table book;

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

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;

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

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語(yǔ)句如下所示：

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

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

create spatial index spatialidx on t7 (g);

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

3、修改表追加索引

在一張已經(jīng)存在的數(shù)據(jù)庫(kù)表中創(chuàng)建索引，除了可以使用create index語(yǔ)句外，還可以使用alter table語(yǔ)句來(lái)完成。其語(yǔ)法格式：

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

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

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

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

drop table book;

然后重新建立book表，SQL語(yǔ)句如下：

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語(yǔ)句在已存在的數(shù)據(jù)表中創(chuàng)建索引了，具體如下：

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

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

alter table book add index index_id(bookid);

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

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

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

alter table book add unique uniqueidx(bookid);

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

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

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

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

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

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

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

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語(yǔ)句如下所示：

drop table book;

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

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;

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

alter table book add fulltext index fulltextidx(info);

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

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

【例】創(chuàng)建表test，在表中的space字段上創(chuàng)建名稱為spatialidx的空間索引。首先創(chuàng)建數(shù)據(jù)表t8，SQL語(yǔ)句如下所示：

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

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

alter table testadd spatial index spatialidx (space);

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

三、索引的銷毀

索引的維護(hù)需要消耗磁盤存儲(chǔ)，因此，為了避免影響數(shù)據(jù)庫(kù)性能，應(yīng)該及時(shí)刪除不再使用的索引。刪除索引的方法有兩種，如下：

1.、修改表刪除索引

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

alter table 表名 drop index 索引名

【例】刪除student表中名稱為tb_idx的全文索引

alter table student drop index tb_idx;

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

show create table student;

2、直接刪除索引

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

drop index 索引名 on 表名;

【例】刪除test1表中名稱為tb_index的空間索引，SQL語(yǔ)句如下：

drop index tb_index on test1

再次使用show create table 語(yǔ)句查看表結(jié)構(gòu)，發(fā)現(xiàn)，test1表中名稱為tb_index的索引被成功刪除。