項目7索引任務(wù)7.1認(rèn)識索引索引是一種單獨(dú)的、物理的、存儲在磁盤上的數(shù)據(jù)庫結(jié)構(gòu)。數(shù)據(jù)庫中的索引類似于一本書中的目錄，讀者可以通過目錄快速找到需要的章節(jié)頁碼，翻到對應(yīng)的頁碼即可看到具體的內(nèi)容。1．索引的概念

索引是按照數(shù)據(jù)表中某一個字段或多個字段進(jìn)行排序，并用指針指向記錄的位置。索引存放在索引頁面上，當(dāng)進(jìn)行數(shù)據(jù)檢索時，系統(tǒng)從索引頁面中找到所需數(shù)據(jù)的指針，再直接利用指針讀取數(shù)據(jù)。因為索引是排好序的，不必逐一查找，可以采用針對排序數(shù)據(jù)的查找算法快速檢索到數(shù)據(jù)。在沒有索引的表中查詢某個特定記錄時需要從第一條記錄開始，逐條比較記錄值是否匹配，當(dāng)表中的記錄比較多時，得到結(jié)果耗時較長，影響查找的效率。例如，按照讀者編號reader_id查找借還書表borrow中的相關(guān)記錄時，需要從第一條記錄開始掃描全表，找到對應(yīng)的記錄，當(dāng)表中的數(shù)據(jù)非常多時，查詢比較耗時。在讀者編號reader_id字段創(chuàng)建索引后，系統(tǒng)會利用該索引找到對應(yīng)的記錄，避免了對借還書表borrow的全表掃描，加快了查詢速度。索引具有如下的優(yōu)點(diǎn)（1）可能會提高查詢速度。（2）可以實現(xiàn)數(shù)據(jù)的唯一性。（3）提高ORDERBY和GROUPBY的執(zhí)行速度。使用索引可以提高性能，但也要付出一定的代價，主要體現(xiàn)為索引需要占用磁盤的存儲空間；索引會降低更新表中數(shù)據(jù)的速度。當(dāng)更新表中數(shù)據(jù)時，系統(tǒng)會自動更新索引頁面的數(shù)據(jù)，可能需要重新組織索引，這會浪費(fèi)時間。索引設(shè)計不當(dāng)可能會影響系統(tǒng)的性能，合理設(shè)置索引對于數(shù)據(jù)庫系統(tǒng)非常重要，需要考慮到以下幾點(diǎn)規(guī)則。（1）為頻繁查詢的字段創(chuàng)建索引。（2）數(shù)據(jù)量較小的表最好不要創(chuàng)建索引。（3）盡量在不同值較多的字段上創(chuàng)建索引，如，讀者表reader的姓名name字段；不要在不同值很少的字段上創(chuàng)建索引，如，性別gender字段。（4）一個表中的索引不是越多越好，需要限制索引的數(shù)量。（5）盡量不為頻繁進(jìn)行插入、刪除、修改操作的表創(chuàng)建索引。2.索引的分類

（1）普通索引這是最基本的索引類型，它沒有唯一性的限制，可以有重復(fù)值和空值。創(chuàng)建普通索引的關(guān)鍵字是INDEX。（2）唯一索引唯一索引的索引字段的值必須是唯一的，不允許重復(fù)，但允許有空值。創(chuàng)建唯一索引的關(guān)鍵字是UNIQUE。（3）主鍵索引主鍵索引是一種特殊的唯一索引，特殊之處在于每張表只能有一個主鍵索引，且不允許有空值。創(chuàng)建主鍵索引的關(guān)鍵字是PRIMARYKEY，即主鍵。一般在創(chuàng)建表時指定主鍵，也可以通過修改表的方式添加主鍵。（4）全文索引全文索引只能在CHAR、VARCHAR或者TEXT類型的字段上創(chuàng)建，并且只能在存儲引擎為MyISAM和InnoDB的表中創(chuàng)建。創(chuàng)建全文索引的關(guān)鍵字是FULLTEXT。當(dāng)查詢數(shù)據(jù)量較大的字符串類型的字段時，使用全文索引可以提高查詢速度。創(chuàng)建在一個字段上的索引稱為單索引。創(chuàng)建在多個字段上的索引稱為組合索引、復(fù)合索引或多列索引。如果唯一索引是組合索引，則多個字段的組合必須是唯一的。在編寫SELECT查詢語句時，有索引的表與沒有索引的表沒有任何區(qū)別，索引只是提供了一種快速訪問指定記錄的方法。7.2創(chuàng)建索引

索引是在表中創(chuàng)建的，不可以針對視圖創(chuàng)建索引。在MySQL中，可以在已有的表中創(chuàng)建索引，也可以在創(chuàng)建表的同時創(chuàng)建索引。索引創(chuàng)建成功后，將由數(shù)據(jù)庫自動管理和維護(hù)，當(dāng)向表中插入、刪除和修改記錄時，數(shù)據(jù)庫會自動更新相應(yīng)的索引。創(chuàng)建索引的目的是加快查詢的速度，應(yīng)該為需要查詢的字段創(chuàng)建索引。例如按照館藏編號stock_id查詢，則先在館藏編號stock_id字段創(chuàng)建索引。（1）

使用CREATEINDEX語句創(chuàng)建索引使用CREATEINDEX語句可以在一個已有的表上創(chuàng)建索引語法格式：CREATE[UNIQUE

FULLTEXT]INDEX索引名ON表名(字段名稱1[(長度1)][ASC

DESC][,字段名稱2[(長度2)][ASC

DESC]

]);說明：（1）索引名：指定創(chuàng)建的索引名稱，在一個表中可以創(chuàng)建多個索引，但是每個索引名必須是唯一的。（2）UNIQUE

FULLTEXT：可選項，UNIQUE表示創(chuàng)建的是唯一索引；FULLTEXT表示創(chuàng)建的是全文索引。（3）長度：只能對字符串類型的字段指定長度，表示使用字段值的前多少個字符創(chuàng)建索引，這樣可以減少索引文件的大小。（4）ASC

DESC：指定索引按照升序或者降序排序。默認(rèn)值為ASC。【例7-1】在借還書表borrow的讀者編號reader_id字段上，創(chuàng)建一個普通索引I_reader_id。mysql>CREATEINDEXI_reader_idONborrow(reader_id);【例7-2】在圖書表book的書名title字段值的前5個字符上，創(chuàng)建一個唯一索引I_title，按照title字段值的前5個字符的降序排列。mysql>CREATEUNIQUEINDEXI_titleONbook(title(5)DESC);【例7-3】在借還書表borrow的讀者編號reader_id字段和館藏編號stock_id字段上，創(chuàng)建一個組合索引I_rsid，先按照讀者編號reader_id升序排列，如果讀者編號reader_id值相同，則按照館藏編號stock_id降序排列。mysql>CREATEINDEXI_rsidONborrow(reader_id,stock_idDESC);2．使用ALTERTABLE語句創(chuàng)建索引對于已有的表，也可以使用ALTERTABLE語句創(chuàng)建索引，其語法格式如下。ALTERTABLE表名ADDINDEX[索引名](字段名稱1[,字段名稱2…])

ADDPRIMARYKEY(字段名稱1[,字段名稱2…])

ADDUNIQUE[索引名](字段名稱1[,字段名稱2…])

ADDFULLTEXT[索引名](字段名稱1[,字段名稱2…]);

說明

（1）索引名：如果省略則采用默認(rèn)索引名。默認(rèn)索引名的命名規(guī)則是主鍵的索引名為PRIMARY，其他索引使用索引的第一個字段名稱為索引名。如果多個索引的第一個字段名稱相同，則在字段名稱后加遞增的數(shù)字進(jìn)行區(qū)別。（2）一次添加多個索引時，各個索引之間用英文逗號分隔。可以使用ALTERTABLE語句一次添加多個索引?！纠?-5】在讀者表reader的姓名name字段上添加一個唯一索引，并按照降序排列，在所屬院系dept字段上添加一個普通索引。mysql>ALTERTABLEreader

->ADDUNIQUE(nameDESC),ADDINDEX(dept);該語句中沒有給這兩個索引命名，則默認(rèn)索引名為字段名稱，分別是name和dept?！纠?-4】在圖書表book的定價price字段上添加一個普通索引，并按照降序排列。mysql>ALTERTABLEbook->ADDINDEX(priceDESC);3．使用CREATETABLE語句創(chuàng)建索引使用CREATETABLE語句可以在創(chuàng)建表的同時創(chuàng)建索引，其中的索引項語法格式如下。PRIMARYKEY(字段名稱1[,字段名稱2…])

{INDEX

KEY}[索引名](字段名稱1[,字段名稱2…])

UNIQUE[INDEX][索引名](字段名稱1[,字段名稱2…])

[FULLTEXT][INDEX][索引名](字段名稱1[,字段名稱2…]);【例7-6】創(chuàng)建讀者表reader1（包含讀者編號reader_id、姓名name和所屬院系dept字段），設(shè)置reader_id字段為主鍵，name為唯一索引，dept為普通索引。mysql>CREATETABLEreader1->(->reader_idCHAR(6)PRIMARYKEY,->nameVARCHAR(50),->deptVARCHAR(50),->UNIQUE(name),->INDEX(dept)->);7.3查看索引使用SHOWINDEX語句可以查看表中的索引，其語法格式如下。SHOW{INDEX

INDEXES

KEYS}{FROM

IN}表名[{FROM

IN}數(shù)據(jù)庫名];SHOWINDEX語句以二維表的形式顯示指定表中的所有索引信息，由于顯示的信息較多且不易查看，使用\G參數(shù)可以讓每一行信息垂直顯示，便于查看?！纠?-7】查看讀者表reader1中創(chuàng)建的索引。

mysql>SHOWINDEXFROMreader1\G;任務(wù)7.4刪除索引使用DROPINDEX語句或者使用ALTERTABLE語句均可刪除索引。1．使用DROPINDEX語句刪除索引DROPINDEX語句的語法格式如下。DROPINDEX索引名ON表名;【例7-8】刪除讀者表reader1中在所屬院系dept字段上創(chuàng)建的索引。mysql>DROPINDEXdeptONreader1;因為在dept字段上創(chuàng)建索引時未指定索引名，系統(tǒng)自動以字段名稱為索引名，所以索引名為dept。2．使用ALTERTABLE語句刪除索引ALTERTABLE子句不僅能創(chuàng)建索引，也可以刪除索引，其語法格式如下。ALTERTABLE表名DROPINDEX索引名

DROPPRIMARYKEY;使用DROPINDEX語句可以刪除各種類型的索引，使用DROPPRIMARYKEY子句時不需要指定索引名，因為每一張表中只有一個主鍵?！纠?-9】在讀者表reader1中刪除讀者編號reader_id字段和姓名name字段上的索引。mysql>ALTERTABLEreader1->DROPPRIMARYKEY,DROPINDEXname;【項目案例】圖書館借還書管理數(shù)據(jù)庫的索引（1）使用CREATEINDEX語句在借還書表borrow的讀者編號reader_id字段、館藏編號stock_id字段和借出時間borrowtime字段上，創(chuàng)建一個唯一組合索引I_rsidborrow。mysql>CREATEUNIQUEINDEXI_rsidborrowONborrow(reader_id,stock_id,borrowtime);（2）使用ALTERTABLE語句在圖書表book的作者