《數(shù)據(jù)庫技術(shù)及應(yīng)用》第十四章 索 引

第十四章索引學(xué)習(xí)目標(biāo):掌握創(chuàng)建索引的方法和技巧熟悉如何刪除索引掌握綜合案例中索引創(chuàng)建的方法和技巧熟悉操作索引的常見問題14.1索引簡介

索引是對數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu),使用索引可提高數(shù)據(jù)庫中特定數(shù)據(jù)的查詢速度。本節(jié)將介紹索引的含義、分類和設(shè)計原則。14.1.1索引的含義和特點 索引是一個單獨(dú)的、存儲在磁盤上的數(shù)據(jù)庫結(jié)構(gòu),它包含著對數(shù)據(jù)表里所有記錄的引用指針。使用索引用于快速找出在某個或多個列中有一特定值的行,所有MySQL列類型都可以被索引,對相關(guān)列使用索引是提高查詢操作速度的最佳途徑。 例如,數(shù)據(jù)庫中有20000條記錄,現(xiàn)在要執(zhí)行這樣一個查詢:SELECT?FROMtableWHEREnum=10000。如果沒有索引,必須遍歷整個表,直到num等于10000的這一行被找到為止;如果在num列上創(chuàng)建索引,MySQL不需要任何掃描,直接在索引里面找10000,就可以得知這一行的位置?？梢?索引的建立可以提高數(shù)據(jù)庫的查詢速度。索引的含義和特點

索引是在存儲引擎中實現(xiàn)的,因此,每種存儲引擎的索引都不一定完全相同,并且每種存儲引擎也不一定支持所有索引類型。根據(jù)存儲引擎定義每個表的最大索引數(shù)和最大索引長度。所有存儲引擎支持每個表至少16個索引,總索引長度至少為256字節(jié)。大多數(shù)存儲引擎有更高的限制。MySQL中索引的存儲類型有兩種:BTREE和HASH,具體和表的存儲引擎相關(guān);MyISAM和InnoDB存儲引擎只支持BTREE索引;MEMORY/HEAP存儲引擎可以支持HASH和BTREE索引。 索引的優(yōu)點主要有以下幾條: ①通過創(chuàng)建唯一索引,可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。 ②可以大大加快數(shù)據(jù)的查詢速度,這也是創(chuàng)建索引的最主要原因。索引的含義和特點 ③在實現(xiàn)數(shù)據(jù)的參考完整性方面,可以加速表和表之間的連接。 ④在使用分組和排序子句進(jìn)行數(shù)據(jù)查詢時,也可以顯著地減少查詢中分組和排序的時間。 增加索引也有許多不利因素,主要表現(xiàn)在以下幾個方面: ①創(chuàng)建索引和維護(hù)索引要耗費(fèi)時間,并且隨著數(shù)據(jù)量的增加所耗費(fèi)的時間也會增加。 ②索引需要占磁盤空間,除了數(shù)據(jù)表占數(shù)據(jù)空間之外,每一個索引還要占一定的物理空間,如果有大量的索引,索引文件可能比數(shù)據(jù)文件更快達(dá)到最大文件尺寸。 ③當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改時,索引也要動態(tài)地維護(hù),這樣就降低了數(shù)據(jù)的維護(hù)速度。14.1.2索引的分類

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

(1)普通索引和唯一索引

普通索引是MySQL中的基本索引類型,允許在定義索引的列中插入重復(fù)值和空值。唯一索引是索引列的值必須唯一,但允許有空值。如果是組合索引,則列值的組合必須唯一。主鍵索引是一種特殊的唯一索引,不允許有空值。

(2)單列索引和組合索引

單列索引即一個索引只包含單個列,一個表可以有多個單列索引。組合索引指在表的多個字段組合上創(chuàng)建的索引,只有在查詢條件中使用了這些字段的左邊字段時,索引才會被使用。使用組合索引時遵循最左前綴集合。索引的分類

(3)全文索引

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

(4)空間索引

空間索引是對空間數(shù)據(jù)類型的字段建立的索引,MySQL中的空間數(shù)據(jù)類型有4種,分別是geometry,point,linestring和polygon。MySQL使用SPATIAL關(guān)鍵字進(jìn)行擴(kuò)展,使得能夠用與創(chuàng)建正規(guī)索引類似的語法創(chuàng)建空間索引。創(chuàng)建空間索引的列,必須將其聲明為NOTNULL,空間索引只能在存儲引擎為MyISAM的表中創(chuàng)建。14.1.3索引的設(shè)計原則 索引設(shè)計不合理或者缺少索引都會對數(shù)據(jù)庫和應(yīng)用程序的性能造成障礙。高效的索引對于獲得良好的性能非常重要。設(shè)計索引時,應(yīng)考慮以下準(zhǔn)則: ①索引并非越多越好,一個表中如有大量的索引,不僅占用磁盤空間,而且會影響INSERT,DELETE,UPDATE等語句的性能,因為表中的數(shù)據(jù)更改的同時,索引也會進(jìn)行調(diào)整和更新。 ②避免對經(jīng)常更新的表進(jìn)行過多的索引,并且索引中的列盡可能少。而對經(jīng)常用于查詢的字段應(yīng)該創(chuàng)建索引,但要避免添加不必要的字段。 ③數(shù)據(jù)量小的表最好不要使用索引,由于數(shù)據(jù)較少,查詢花費(fèi)的時間可能比遍歷索引的時間還要短,索引可能不會產(chǎn)生優(yōu)化效果。索引的設(shè)計原則 ④條件表達(dá)式中,在經(jīng)常用到的不同值較多的列上建立索引,在不同值很少的列上不要建立索引。比如在學(xué)生表的“性別”字段上只有“男”與“女”兩個不同值,因此就無須建立索引。如果建立索引,不但不會提高查詢效率,反而會嚴(yán)重降低數(shù)據(jù)更新速度。 ⑤當(dāng)唯一性是某種數(shù)據(jù)本身的特征時,指定唯一索引。使用唯一索引需要能夠確保定義的列的數(shù)據(jù)完整性,以提高查詢速度。 ⑥在頻繁進(jìn)行排序或分組(即進(jìn)行GROUPBY或ORDERBY操作)的列上建立索引,如果待排序的列有多個,可以在這些列上建立組合索引。14.2創(chuàng)建索引

MySQL支持多種方法在單個或多個列上創(chuàng)建索引:在創(chuàng)建表的定義語句CREATETABLE中指定索引列,使用ALTERTABLE語句在存在的表上創(chuàng)建索引,或者使用CREATEINDEX語句在已存在的表上添加索引。14.2.1創(chuàng)建表時創(chuàng)建索引

使用CREATETABLE創(chuàng)建表時,除了可以定義列的數(shù)據(jù)類型,還可以定義主鍵約束、外鍵約束或者唯一性約束。創(chuàng)建表時創(chuàng)建索引的基本語法格式如下:

UNIQUE,FULLTEXT和SPATIAL為可選參數(shù),分別表示唯一索引、全文索引和空間索引;INDEX與KEY用來指定創(chuàng)建索引;col_name為需要創(chuàng)建索引的字段列,該列必須從數(shù)據(jù)表中定義的多個列中選擇;index_name指定索引的名稱,為可選參數(shù),如果不指定,MySQL默認(rèn)col_name為索引值;length為可選參數(shù),表示索引的長度,只有字符串類型的字段才能指定索引長度。(1)創(chuàng)建普通索引 普通索引是最基本的索引類型,沒有唯一性之類的限制,其作用只是加快對數(shù)據(jù)的訪問速度。 【例14．1】在book表中的year_publication字段上建立普通索引,SQL語句如下:創(chuàng)建普通索引 該語句執(zhí)行完畢后,使用SHOWCREATETABLE查看表結(jié)構(gòu)。 由結(jié)果可以看到,book表的year_publication字段上成功建立索引,其索引名稱year_publication為MySQL自動添加。這里用EXPLAIN語句查看索引是否正在使用:創(chuàng)建普通索引 EXPLAIN語句輸出結(jié)果的各個行解釋如下: ①select_type:指定所使用的SELECT查詢類型,這里的值為SIMPLE,表示簡單的SELECT,不使用UNION或子查詢。其他可能的取值有PRIMARY,UNION,SUBQUERY等。 ②table:指定數(shù)據(jù)庫讀取的數(shù)據(jù)表的名字,它們按被讀取的先后順序排列。 ③type:指定了本數(shù)據(jù)表與其他數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系,可能的取值有system.Const,eq_ref,ref,range,index和All。 ④possible_keys:給出了MySQL在搜索數(shù)據(jù)記錄時可選用的各個索引。創(chuàng)建普通索引

⑤key:MySQL實際選用的索引。

⑥key_len:給出索引按字節(jié)計算的長度,key_len數(shù)值越小,表示越快。

⑦ref:給出了關(guān)聯(lián)關(guān)系中另一個數(shù)據(jù)表里的數(shù)據(jù)列的名字。

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

⑨extra:提供了與關(guān)聯(lián)操作有關(guān)的信息。

可以看出,possible_keys和key的值都為year_publication,查詢時使用了索引。(2)創(chuàng)建唯一索引 創(chuàng)建唯一索引的主要原因是減少查詢索引列操作的執(zhí)行時間,尤其是對比較龐大的數(shù)據(jù)表。它與前面的普通索引類似,所不同的是索引列的值必須唯一,但允許有空值。如果是組合索引,則列值的組合必須唯一。

創(chuàng)建唯一索引 【例14．2】創(chuàng)建一個表t1,在表中的ID字段上使用UNIQUE關(guān)鍵字創(chuàng)建唯一索引。

該語句執(zhí)行完畢后,使用SHOWCREATETABLE查看表結(jié)構(gòu)。

由結(jié)果可以看出,ID字段上已經(jīng)成功建立了一個名為UniqIdx的唯一索引。

(3)創(chuàng)建單列索引 單列索引是在數(shù)據(jù)表中的某一個字段上創(chuàng)建的索引,一個表中可以創(chuàng)建多個單列索引。前面兩個例子中創(chuàng)建的索引都為單列索引。 【例14．3】創(chuàng)建一個表t2,在表中的name字段上創(chuàng)建單列索引。 其表結(jié)構(gòu)如下:

該語句執(zhí)行完畢之后,使用SHOWCREATETABLE查看表結(jié)構(gòu)。

由結(jié)果可以看出,ID字段上已經(jīng)成功建立了一個名為SingleIdx的單列索引,索引長度為20。(4)創(chuàng)建組合索引 組合索引是在多個字段上創(chuàng)建一個索引。 【例14．4】創(chuàng)建表t3,在表中的id,name和age字段上建立組合索引,SQL語句如下:

該語句執(zhí)行完畢后,使用SHOWCREATETABLE查看表結(jié)構(gòu)。

由結(jié)果可以看出,ID,name和age字段上已經(jīng)成功建立了一個名為MultiIdx的組合索引。(5)創(chuàng)建全文索引

FULLTEXT(全文索引)可以用于全文搜索。只有MyISAM存儲引擎支持FULLTEXT索引,并且只為char,varchar和text列創(chuàng)建索引。索引總是對整個列進(jìn)行,不支持局部(前綴)索引。

【例14．5】創(chuàng)建表t4,在表中的info字段上建立全文索引,SQL語句如下:創(chuàng)建全文索引 提示:因為MySQL5．7中默認(rèn)存儲引擎為InnoDB,在這里創(chuàng)建表時需要修改表的存儲引擎為MyISAM,不然創(chuàng)建索引會出錯。 語句執(zhí)行完畢后,使用SHOWCREATETABLE查看表結(jié)構(gòu)。 由結(jié)果可以看出,info字段上已經(jīng)成功建立了一個名為FullTxtIdx的FULLTEXT索引。全文索引非常適合于大型數(shù)據(jù)集,對于小的數(shù)據(jù)集,它的用處比較小。(6)創(chuàng)建空間索引

空間索引必須在MyISAM類型的表中創(chuàng)建,且空間類型的字段必須為非空。 【例14．6】創(chuàng)建表t5,在空間類型為GEOMETRY的字段上創(chuàng)建空間索引,SQL語句如下:

該語句執(zhí)行完畢后,使用SHOWCREATETABLE查看表結(jié)構(gòu)。 由結(jié)果可以看出,t5表的g字段上創(chuàng)建了名稱為spatIdx的空間索引。注意創(chuàng)建時指定空間類型字段值的非空約束,并且表的存儲引擎為MyISAM。14.2.2在已經(jīng)存在的表上創(chuàng)建索引

在已經(jīng)存在的表上創(chuàng)建索引,可以使用ALTERTABLE語句或者CREATEINDEX語句,本節(jié)將介紹如何使用ALTERTABLE和CREATEINDEX語句在已知表字段上創(chuàng)建索引。 (1)使用ALTERTABLE語句創(chuàng)建索引 ALTERTABLE創(chuàng)建索引的基本語法如下

與創(chuàng)建表時創(chuàng)建索引的語法不同的是,在這里使用了ALTERTABLE和ADD關(guān)鍵字,ADD表示向表中添加索引。在已經(jīng)存在的表上創(chuàng)建索引 【例14．7】在book表中的bookname字段上建立名為BkNameIdx的普通索引。 使用SHOWINDEX語句查看表中的索引。 可以看出通過ALTERTABLE語句添加的名稱為BkNameIdx的索引,該索引為非唯一索引,長度為30。在已經(jīng)存在的表上創(chuàng)建索引

【例14．8】在book表的bookId字段上建立名稱為UniqidIdx的唯一索引,SQL語句如下:

使用SHOWINDEX語句查看表中的索引。

可以看到Non_unique屬性值為0,表示名稱為UniqidIdx的索引為唯一索引,創(chuàng)建唯一索引成功。在已經(jīng)存在的表上創(chuàng)建索引

【例14．9】在book表的comment字段上建立單列索引,SQL語句如下:

使用SHOWINDEX語句查看表中的索引。 可以看出,語句執(zhí)行之后在book表的comment字段上建立的名稱為BkcmtIdx的索引,長度為50,在查詢時,只需要檢索前50個字符。在已經(jīng)存在的表上創(chuàng)建索引

【例14．10】在book表的authors和info字段上建立組合索引,SQL語句如下:

使用SHOWINDEX語句查看表中的索引。 可以看到名稱為BkAuAndInfoIdx的索引由兩個字段組成,authors字段長度為30,在組合索引中的序號為1,該字段不允許空值NULL;info字段長度為50,在組合索引中的序號為2,該字段可以為空值NULL。在已經(jīng)存在的表上創(chuàng)建索引

【例14．11】創(chuàng)建表t6,在t6表上使用ALTERTABLE創(chuàng)建全文索引。首先創(chuàng)建表t6,SQL語句如下:

注意:修改ENGINE參數(shù)為MyISAM,MySQL默認(rèn)引擎InnoDB不支持全文索引。 在已經(jīng)存在的表上創(chuàng)建索引

使用ALTERTABLE語句在info字段上創(chuàng)建全文索引。

使用SHOWINDEX語句查看表中的索引。 可以看出,t6表中已經(jīng)創(chuàng)建了名稱為infoFTIdx的索引,該索引在info字段上創(chuàng)建,類型為FULLTEXT,允許為空值。在已經(jīng)存在的表上創(chuàng)建索引

【例14．12】創(chuàng)建表t7,在t7的空間數(shù)據(jù)類型字段g上創(chuàng)建名稱為spatIdx的空間索引SQL語句如下:

使用ALTERTABLE在表t7的g字段建立空間索引。

使用SHOWINDEX語句查看表中的索引。 可以看出,t7表的g字段上創(chuàng)建了名稱為spatIdx的空間索引。在已經(jīng)存在的表上創(chuàng)建索引 (2)使用CREATEINDEX創(chuàng)建索引 CREATEINDEX語句可以在已經(jīng)存在的表上添加索引,MySQL中CREATEINDEX被映射到一個ALTERTABLE語句上,基本語法結(jié)構(gòu)為:

可以看出,CREATEINDEX語句和ALTERTABLE語句的語法基本一致,只是關(guān)鍵字不同。在這里,使用相同的表book,假設(shè)該表中沒有任何索引值,創(chuàng)建book表語句如下:在已經(jīng)存在的表上創(chuàng)建索引 提示:讀者可以將該數(shù)據(jù)庫中的book表刪除,按上面的語句重新建立,然后進(jìn)行下面的操作。

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

【例14．13】在book表中的bookname字段上建立名為BkNameIdx的普通索引,SQL語句如下:

語句執(zhí)行完畢后,將在book表中創(chuàng)建名稱為BkNameIdx的普通索引?？梢允褂肧HOWINDEX或者SHOWCREATETABLE語句查看book表中的索引,其索引內(nèi)容與前面介紹的相同。在已經(jīng)存在的表上創(chuàng)建索引

【例14．14】在book表的bookID字段上建立名稱為UniqIDIdx的唯一索引,SQL語句如下:

語句執(zhí)行完畢后,將在book表中創(chuàng)建名稱為UniqIDIdx的唯一索引。

【例14．15】在book表的comment字段上建立單列索引,SQL語句如下:

語句執(zhí)行完畢后,將在book表的comment字段上建立一個名為BkcmtIdx的單列索引,長度為50。在已經(jīng)存在的表上創(chuàng)建索引

【例14．16】在book表的authors和info字段上建立組合索引,SQL語句如下:

語句執(zhí)行完畢后,將在book表的authors和info字段上建立了一個名為BkAuAndInfoIdx的組合索引,authors的索引序號為1,長度為20,info的索引序號為2,長度為50。

【例14．17】刪除表t6,重新建立表t6,在t6表中使用CREATEINDEX語句,在CHAR類型的info字段上創(chuàng)建全文索引,SQL語句如下:在已經(jīng)存在的表上創(chuàng)建索引

首先刪除表t6,并重新建立該表,分別輸入下列語句:

使用CREATEINDEX在t6表的info字段上創(chuàng)建名稱為infoFTIdx的全文索引:

語句執(zhí)行完畢后,將在t6表中創(chuàng)建名稱為infoFTIdx的索引,該索引在info字段上創(chuàng)建類型為FULLTEXT,允許為空值。在已經(jīng)存在的表上創(chuàng)建索引

【例14．18】刪除表t7,重新創(chuàng)建表t7,在t7表中使用CREATEINDEX語句,在空間數(shù)據(jù)類型字段g上創(chuàng)建名