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

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

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

一、什么是索引

在MySQL數(shù)據(jù)庫中，索引和表、視圖、同義詞等類似是數(shù)據(jù)庫“對象”的一種?？煽醋鲎值涞哪夸?。是對數(shù)據(jù)庫表中一列或者多了的值進(jìn)行排序后的一種結(jié)構(gòu)，其作用就是提高表中的數(shù)據(jù)查詢速度。MySQL中的索引分為如下幾種：

1.  普通索引

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

2. 唯一性索引

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

3.  全文索引

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

4.單列索引

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

5.多列索引

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

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

6. 空間索引

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

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

二、索引的生成

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

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

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

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

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

                        ……

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

                            [unique|fulltext|spatial]  index|key

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

                        );

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

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

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

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

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

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

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

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

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)建一個表名為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字段上建立了一個名為unique_id的唯一性索引。

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

【例】創(chuàng)建一個表名為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字段上建立一個名為fulltext_name的全文索引。

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

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

【例】創(chuàng)建一個表名為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字段上建立一個名稱為single_name的單列索引，并且索引的長度為20。

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

【例】創(chuàng)建一個表名為 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字段上建立一個名為multi的多列索引。

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

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

explain select * from t5 where id = 1;

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

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

【例】創(chuàng)建一個表名為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)建空間索引時，所在字段的值不能為空值，并且表的存儲引擎為MyISAM。

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

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

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

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

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

為了更好的展示如何使用create index語句在已經(jīng)存在的表上創(chuàng)建索引，接下來創(chuàng)建一個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字段上建立一個名稱為index_id的普通索引，SQL語句如下：

create index index_id on book (bookid);

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

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

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

create unique index uniqueidx on book (bookid);

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

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

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

create index singleidx on book (comment);

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

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

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

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

這樣，即可在book表中，為authors和info字段建立一個名稱為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字段建立一個名稱為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字段建立一個名稱為spatialidx的空間索引。

3、修改表追加索引

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

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

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

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

接下來，同樣以book表為例，對不同類型的索引進(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字段建立一個名稱為index_id的普通索引。

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

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

alter table book add unique uniqueidx(bookid);

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

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

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

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

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

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

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

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

這樣，即可在book表中，為authors和info字段建立一個名稱為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字段建立一個名稱為fulltextidx的全文索引。

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

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

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

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

alter table testadd spatial index spatialidx (space);

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

三、索引的銷毀

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

1.、修改表刪除索引

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

alter table 表名 drop index 索引名

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

alter table student drop index tb_idx;

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

show create table student;

2、直接刪除索引

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

drop index 索引名 on 表名;

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

drop index tb_index on test1

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