第七章索引第七章索引項(xiàng)目知識(shí)要點(diǎn)與目標(biāo)項(xiàng)目知識(shí)要點(diǎn)與目標(biāo)索引及其應(yīng)用

索引——

是一種提高查找速度的機(jī)制索引用來(lái)快速地尋找那些具有特定值的記錄，如果沒(méi)有索引，執(zhí)行查詢時(shí)MySQL必須從第一個(gè)記錄開(kāi)始掃描整個(gè)表的所有記錄，直至找到符合要求的記錄。表里面的記錄數(shù)量越多，這個(gè)操作的代價(jià)就越高。索引提供指針以指向存儲(chǔ)在表中指定列的數(shù)據(jù)值，然后根據(jù)指定的排序次序排列這些指針。數(shù)據(jù)庫(kù)使用索引的方式與使用書(shū)的目錄很相似：通過(guò)搜索索引找到特定的值，然后跟隨指針到達(dá)包含該值的行。如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引，MySQL無(wú)需掃描任何記錄即可迅速得到目標(biāo)記錄所在的位置。如果表有1000個(gè)記錄，通過(guò)索引查找記錄至少要比順序掃描記錄快100倍。索引及其應(yīng)用索引——是一種提高查找速度的機(jī)制索引用來(lái)索引文件學(xué)生表student中建立“學(xué)號(hào)”索引（升序）示意圖學(xué)生表student索引文件索引文件學(xué)生表student中建立“學(xué)號(hào)”索引（升序）示意圖索引文件如何影響原表沒(méi)有索引文件時(shí)：指針在原表中順序移動(dòng)如果要找位于第10000條的學(xué)號(hào)”20070201”的記錄,

計(jì)算機(jī)要在表中查找10000次索引文件如何影響原表沒(méi)有索引文件時(shí)：指針在原表中順序移動(dòng)如果使用索引查找有索引文件時(shí)：(二分法查找實(shí)例)計(jì)算機(jī)先在索引文件中學(xué)號(hào)為”20070201”的記錄,找到相應(yīng)的記錄號(hào),再到學(xué)生表中直接讀取相關(guān)記錄.學(xué)生表student索引文件使用索引查找有索引文件時(shí)：(二分法查找實(shí)例)學(xué)生表stud索引文件如何加快查找速度原因：（1）索引后，指針在索引文件中順序移動(dòng)。（2）索引文件中記錄是有序的。（3）有序后，可以用各種方法加快查詢速度，如折半（二分）查找法，而排序前，只能順序查找記錄。索引文件如何加快查找速度原因：B樹(shù)索引示意圖<50>=50……rowIDrowIDrowIDrowID……<40..50<30..40<20..30<10..20…………<80..90<70..80<60..70<50..60rowIDrowIDrowIDrowIDrowID……41rowID42rowID43rowID44rowID……B樹(shù)索引示意圖<50……<40..50……rowID41索引的分類1.普通索引（INDEX）這是最基本的索引類型，它沒(méi)有唯一性之類的限制。創(chuàng)建普通索引的關(guān)鍵字是INDEX。2.唯一性索引（UNIQUE）這種索引和前面的普通索引基本相同，但有一個(gè)區(qū)別：索引列的所有值都只能出現(xiàn)一次，即必須是唯一的。創(chuàng)建唯一性索引的關(guān)鍵字是UNIQUE。3.主鍵（PRIMARYKEY）主鍵是一種唯一性索引，它必須指定為“PRIMARYKEY”。主鍵一般在創(chuàng)建表的時(shí)候指定，也可以通過(guò)修改表的方式加入主鍵。但是每個(gè)表只能有一個(gè)主鍵。4.全文索引（FULLTEXT）MySQL支持全文檢索和全文索引。全文索引的索引類型為FULLTEXT。全文索引只能在VARCHAR或TEXT類型的列上創(chuàng)建，并且只能在MyISAM表中創(chuàng)建。索引的分類1.普通索引（INDEX）

創(chuàng)建索引1.使用CREATEINDEX語(yǔ)句使用CREATEINDEX語(yǔ)句可以在一個(gè)已有表上創(chuàng)建索引，一個(gè)表可以創(chuàng)建多個(gè)索引。語(yǔ)法格式：CREATE[UNIQUE|FULLTEXT]INDEX

索引名ON

表名(列名[(長(zhǎng)度)][ASC|DESC],...)說(shuō)明：●索引名：索引的名稱，索引名在一個(gè)表中名稱必須是唯一的?！窳忻罕硎緞?chuàng)建索引的列名。

長(zhǎng)度：表示使用列的前多少個(gè)字符創(chuàng)建索引。使用列的一部分創(chuàng)建索引可以使索引文件大大減小，從而節(jié)省磁盤(pán)空間。BLOB或TEXT列必須用前綴索引?！馯NIQUE:UNIQUE表示創(chuàng)建的是唯一性索引●FULLTEXT:FULLTEXT表示創(chuàng)建全文索引；●CREATEINDEX語(yǔ)句并不能創(chuàng)建主鍵。創(chuàng)建索引1.使用CREATEINDEX語(yǔ)句創(chuàng)建索引舉例【例7.1】根據(jù)Book表的書(shū)名列上的前6個(gè)字符建立一個(gè)升序索引name-_book。

CREATEINDEXname-_bookONBook(書(shū)名(6)ASC);可以在一個(gè)索引的定義中包含多個(gè)列，中間用逗號(hào)隔開(kāi)，但是它們要屬于同一個(gè)表。這樣的索引叫做復(fù)合索引。【例7.2】在Sell表的用戶號(hào)列和圖書(shū)編號(hào)列上建立一個(gè)復(fù)合索引sfz_bh-_sell。CREATEINDEXuser_bh-_sellONSell(用戶號(hào),圖書(shū)編號(hào));創(chuàng)建索引舉例【例7.1】根據(jù)Book表的書(shū)名列上的前6個(gè)字ALTERTABLE語(yǔ)句創(chuàng)建索引使用ALTERTABLE語(yǔ)句使用ALTERTABLE語(yǔ)句修改表，其中也包括向表中添加索引。語(yǔ)法格式如下：ALTERTABLE表名ADDINDEX[索引名](列名,...)/*添加索引*/|ADDPRIMARYKEY[索引方式](列名,...)/*添加主鍵*/|ADDUNIQUE[索引名](列名,...)/*添加唯一性索引*/|ADDFULLTEXT[索引名](列名,...)/*添加全文索引*/ALTERTABLE語(yǔ)句創(chuàng)建索引使用ALTERTABLEALTERTABLE創(chuàng)建索引舉例【例7.3】在Book表的書(shū)名列上創(chuàng)建一個(gè)普通索引。ALTERTABLEBook ADDINDEXsm_book(書(shū)名);【例7.4】假設(shè)Book表中主鍵未設(shè)定，為Book表創(chuàng)建以圖書(shū)編號(hào)為主鍵索引，出版社和出版時(shí)間為復(fù)合索引，以加速表的檢索速度。

ALTERTABLEBook ADDPRIMARYKEY(圖書(shū)編號(hào)), ADDINDEXmark(出版社,出版時(shí)間);

這個(gè)例子中，既包括PRIMARYKEY，也包括復(fù)合索引，說(shuō)明MySQL可以同時(shí)創(chuàng)建多個(gè)索引。記住，使用PRIMARYKEY的列，必須是一個(gè)具有NOTNULL屬性的列。如果想要查看表中創(chuàng)建的索引的情況，可以使用SHOWINDEXFROMtbl_name語(yǔ)句，例如：SHOWINDEXFROMbook;ALTERTABLE創(chuàng)建索引舉例【例7.3】在Book表創(chuàng)建表時(shí)創(chuàng)建索引在前面兩種情況下，索引都是在表創(chuàng)建之后創(chuàng)建的。索引也可以在創(chuàng)建表時(shí)一起創(chuàng)建。在創(chuàng)建表的CREATETABLE語(yǔ)句中可以包含索引的定義。語(yǔ)法格式：CREATETABLE表名(列名,...|[索引項(xiàng)])其中，索引項(xiàng)語(yǔ)法格式如下：PRIMARYKEY(列名,...)/*主鍵*/|{INDEX|KEY}[索引名](列名,...)/*索引*/|UNIQUE[INDEX][索引名](列名,...)/*唯一性索引*/|[FULLTEXT][INDEX][索引名](列名,...)/*全文索引*/說(shuō)明：KEY通常是INDEX的同義詞。在定義列選項(xiàng)的時(shí)候，也可以將某列定義為PRIMARYKEY，但是當(dāng)主鍵是由多個(gè)列組成的多列索引時(shí)，定義列時(shí)無(wú)法定義此主鍵，必須在語(yǔ)句最后加上一個(gè)PRIMARYKEY列名，…）子句。創(chuàng)建表時(shí)創(chuàng)建索引在前面兩種情況下，索引都是在表創(chuàng)建之后創(chuàng)建的【例7.5】創(chuàng)建sell_copy表的語(yǔ)句如下，sell_copy表帶有身份證號(hào)和圖書(shū)編號(hào)的聯(lián)合主鍵，并在訂購(gòu)冊(cè)數(shù)列上創(chuàng)建索引。

CREATETABLEsell_copy(

身份證號(hào)CHAR(18)NOTNULL,

圖書(shū)編號(hào)CHAR(20)NOTNULL,

訂購(gòu)冊(cè)數(shù)INT(5),

訂購(gòu)時(shí)間DATETIME, PRIMARYKEY(身份證號(hào),圖書(shū)編號(hào)), INDEXdgcs(訂購(gòu)冊(cè)數(shù)));創(chuàng)建表時(shí)創(chuàng)建索引舉例【例7.5】創(chuàng)建sell_copy表的語(yǔ)句如下，sell_

刪除索引1.使用DROPINDEX語(yǔ)句刪除索引語(yǔ)法格式：

DROPINDEX索引名ON表名【例7.6】刪除Book表上的sm_book索引。

DROPINDEXsm_bookONBook;刪除索引1.使用DROPINDEX語(yǔ)句刪除索引ALTERTABLE刪除索引2.使用ALTERTABLE語(yǔ)句刪除索引語(yǔ)法格式：ALTER[IGNORE]TABLE表名|DROPPRIMARYKEY /*刪除主鍵*/|DROPINDEX索引名

/*刪除索引*/【例7.7】刪除Book表上的主鍵和mark索引。

ALTERTABLEBook DROPPRIMARYKEY, DROPINDEXmark;

如果從表中刪除了列，則索引可能會(huì)受到影響。如果所刪除的列為索引的組成部分，則該列也會(huì)從索引中刪除。如果組成索引的所有列都被刪除，則整個(gè)索引將被刪除。ALTERTABLE刪除索引2.使用ALTERTAB索引對(duì)查詢的影響目前本書(shū)實(shí)例中所涉及的表最多只有幾十行的數(shù)據(jù)，所以有沒(méi)有建立索引，還體會(huì)不到查詢速度上的差異，可是當(dāng)一個(gè)表里有成千上萬(wàn)行數(shù)據(jù)的時(shí)候，差異就非常明顯了?，F(xiàn)在假設(shè)有一個(gè)表，表里只有一列，由數(shù)值1～1000的1000行組成，現(xiàn)在要想查找到數(shù)字1000所在的行。如果沒(méi)有索引，要從第一行開(kāi)始匹配，若數(shù)值不是1000，則轉(zhuǎn)到下一行進(jìn)行匹配，這樣直到第1000行的時(shí)候才能找到數(shù)字1000所在行，也就是說(shuō)服務(wù)器進(jìn)行了1000次的運(yùn)算。而當(dāng)在該列上創(chuàng)建一個(gè)索引后，則可以直接在索引值中找到1000的位置，然后找到1000所指向的行，在速度上比全表掃描至少快了100倍。當(dāng)執(zhí)行涉及多個(gè)表的連接查詢時(shí)，索引將更有價(jià)值。索引對(duì)查詢的影響目前本書(shū)實(shí)例中所涉及的表最多只有幾十行的數(shù)據(jù)索引的弊端首先，索引是以文件的形式存儲(chǔ)的，索引文件要占用磁盤(pán)空間。如果有大量的索引，索引文件可能會(huì)比數(shù)據(jù)文件更快地達(dá)到最大的文件尺寸。其次，在更新表中索引列上的數(shù)據(jù)時(shí)，對(duì)索引也需要更新，這可能需要重新組織一個(gè)索引，如果表中的索引很多，這是很浪費(fèi)時(shí)間的。也就是說(shuō)，這樣就降低了添加、刪除、修改和其他寫(xiě)入操作的效率。表中的索引越多，則更新表的時(shí)間就越長(zhǎng)。但是這些弊端并不妨礙索引的應(yīng)用，因?yàn)樗饕龓?lái)的好處已經(jīng)基本掩蓋了它的缺陷，在表中有很多行數(shù)據(jù)的時(shí)候，索引通常是不可缺少的。索引的弊端首先，索引是以文件的形式存儲(chǔ)的，索引文件要占用磁盤(pán)第七章索引第七章索引項(xiàng)目知識(shí)要點(diǎn)與目標(biāo)項(xiàng)目知識(shí)要點(diǎn)與目標(biāo)索引及其應(yīng)用

索引——

是一種提高查找速度的機(jī)制索引用來(lái)快速地尋找那些具有特定值的記錄，如果沒(méi)有索引，執(zhí)行查詢時(shí)MySQL必須從第一個(gè)記錄開(kāi)始掃描整個(gè)表的所有記錄，直至找到符合要求的記錄。表里面的記錄數(shù)量越多，這個(gè)操作的代價(jià)就越高。索引提供指針以指向存儲(chǔ)在表中指定列的數(shù)據(jù)值，然后根據(jù)指定的排序次序排列這些指針。數(shù)據(jù)庫(kù)使用索引的方式與使用書(shū)的目錄很相似：通過(guò)搜索索引找到特定的值，然后跟隨指針到達(dá)包含該值的行。如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引，MySQL無(wú)需掃描任何記錄即可迅速得到目標(biāo)記錄所在的位置。如果表有1000個(gè)記錄，通過(guò)索引查找記錄至少要比順序掃描記錄快100倍。索引及其應(yīng)用索引——是一種提高查找速度的機(jī)制索引用來(lái)索引文件學(xué)生表student中建立“學(xué)號(hào)”索引（升序）示意圖學(xué)生表student索引文件索引文件學(xué)生表student中建立“學(xué)號(hào)”索引（升序）示意圖索引文件如何影響原表沒(méi)有索引文件時(shí)：指針在原表中順序移動(dòng)如果要找位于第10000條的學(xué)號(hào)”20070201”的記錄,

計(jì)算機(jī)要在表中查找10000次索引文件如何影響原表沒(méi)有索引文件時(shí)：指針在原表中順序移動(dòng)如果使用索引查找有索引文件時(shí)：(二分法查找實(shí)例)計(jì)算機(jī)先在索引文件中學(xué)號(hào)為”20070201”的記錄,找到相應(yīng)的記錄號(hào),再到學(xué)生表中直接讀取相關(guān)記錄.學(xué)生表student索引文件使用索引查找有索引文件時(shí)：(二分法查找實(shí)例)學(xué)生表stud索引文件如何加快查找速度原因：（1）索引后，指針在索引文件中順序移動(dòng)。（2）索引文件中記錄是有序的。（3）有序后，可以用各種方法加快查詢速度，如折半（二分）查找法，而排序前，只能順序查找記錄。索引文件如何加快查找速度原因：B樹(shù)索引示意圖<50>=50……rowIDrowIDrowIDrowID……<40..50<30..40<20..30<10..20…………<80..90<70..80<60..70<50..60rowIDrowIDrowIDrowIDrowID……41rowID42rowID43rowID44rowID……B樹(shù)索引示意圖<50……<40..50……rowID41索引的分類1.普通索引（INDEX）這是最基本的索引類型，它沒(méi)有唯一性之類的限制。創(chuàng)建普通索引的關(guān)鍵字是INDEX。2.唯一性索引（UNIQUE）這種索引和前面的普通索引基本相同，但有一個(gè)區(qū)別：索引列的所有值都只能出現(xiàn)一次，即必須是唯一的。創(chuàng)建唯一性索引的關(guān)鍵字是UNIQUE。3.主鍵（PRIMARYKEY）主鍵是一種唯一性索引，它必須指定為“PRIMARYKEY”。主鍵一般在創(chuàng)建表的時(shí)候指定，也可以通過(guò)修改表的方式加入主鍵。但是每個(gè)表只能有一個(gè)主鍵。4.全文索引（FULLTEXT）MySQL支持全文檢索和全文索引。全文索引的索引類型為FULLTEXT。全文索引只能在VARCHAR或TEXT類型的列上創(chuàng)建，并且只能在MyISAM表中創(chuàng)建。索引的分類1.普通索引（INDEX）

創(chuàng)建索引1.使用CREATEINDEX語(yǔ)句使用CREATEINDEX語(yǔ)句可以在一個(gè)已有表上創(chuàng)建索引，一個(gè)表可以創(chuàng)建多個(gè)索引。語(yǔ)法格式：CREATE[UNIQUE|FULLTEXT]INDEX

索引名ON

表名(列名[(長(zhǎng)度)][ASC|DESC],...)說(shuō)明：●索引名：索引的名稱，索引名在一個(gè)表中名稱必須是唯一的。●列名：表示創(chuàng)建索引的列名。

長(zhǎng)度：表示使用列的前多少個(gè)字符創(chuàng)建索引。使用列的一部分創(chuàng)建索引可以使索引文件大大減小，從而節(jié)省磁盤(pán)空間。BLOB或TEXT列必須用前綴索引?！馯NIQUE:UNIQUE表示創(chuàng)建的是唯一性索引●FULLTEXT:FULLTEXT表示創(chuàng)建全文索引；●CREATEINDEX語(yǔ)句并不能創(chuàng)建主鍵。創(chuàng)建索引1.使用CREATEINDEX語(yǔ)句創(chuàng)建索引舉例【例7.1】根據(jù)Book表的書(shū)名列上的前6個(gè)字符建立一個(gè)升序索引name-_book。

CREATEINDEXname-_bookONBook(書(shū)名(6)ASC);可以在一個(gè)索引的定義中包含多個(gè)列，中間用逗號(hào)隔開(kāi)，但是它們要屬于同一個(gè)表。這樣的索引叫做復(fù)合索引?！纠?.2】在Sell表的用戶號(hào)列和圖書(shū)編號(hào)列上建立一個(gè)復(fù)合索引sfz_bh-_sell。CREATEINDEXuser_bh-_sellONSell(用戶號(hào),圖書(shū)編號(hào));創(chuàng)建索引舉例【例7.1】根據(jù)Book表的書(shū)名列上的前6個(gè)字ALTERTABLE語(yǔ)句創(chuàng)建索引使用ALTERTABLE語(yǔ)句使用ALTERTABLE語(yǔ)句修改表，其中也包括向表中添加索引。語(yǔ)法格式如下：ALTERTABLE表名ADDINDEX[索引名](列名,...)/*添加索引*/|ADDPRIMARYKEY[索引方式](列名,...)/*添加主鍵*/|ADDUNIQUE[索引名](列名,...)/*添加唯一性索引*/|ADDFULLTEXT[索引名](列名,...)/*添加全文索引*/ALTERTABLE語(yǔ)句創(chuàng)建索引使用ALTERTABLEALTERTABLE創(chuàng)建索引舉例【例7.3】在Book表的書(shū)名列上創(chuàng)建一個(gè)普通索引。ALTERTABLEBook ADDINDEXsm_book(書(shū)名);【例7.4】假設(shè)Book表中主鍵未設(shè)定，為Book表創(chuàng)建以圖書(shū)編號(hào)為主鍵索引，出版社和出版時(shí)間為復(fù)合索引，以加速表的檢索速度。

ALTERTABLEBook ADDPRIMARYKEY(圖書(shū)編號(hào)), ADDINDEXmark(出版社,出版時(shí)間);

這個(gè)例子中，既包括PRIMARYKEY，也包括復(fù)合索引，說(shuō)明MySQL可以同時(shí)創(chuàng)建多個(gè)索引。記住，使用PRIMARYKEY的列，必須是一個(gè)具有NOTNULL屬性的列。如果想要查看表中創(chuàng)建的索引的情況，可以使用SHOWINDEXFROMtbl_name語(yǔ)句，例如：SHOWINDEXFROMbook;ALTERTABLE創(chuàng)建索引舉例【例7.3】在Book表創(chuàng)建表時(shí)創(chuàng)建索引在前面兩種情況下，索引都是在表創(chuàng)建之后創(chuàng)建的。索引也可以在創(chuàng)建表時(shí)一起創(chuàng)建。在創(chuàng)建表的CREATETABLE語(yǔ)句中可以包含索引的定義。語(yǔ)法格式：CREATETABLE表名(列名,...|[索引項(xiàng)])其中，索引項(xiàng)語(yǔ)法格式如下：PRIMARYKEY(列名,...)/*主鍵*/|{INDEX|KEY}[索引名](列名,...)/*索引*/|UNIQUE[INDEX][索引名](列名,...)/*唯一性索引*/|[FULLTEXT][INDEX][索引名](列名,...)/*全文索引*/說(shuō)明：KEY通常是INDEX的同義詞。在定義列選項(xiàng)的時(shí)候，也可以將某列定義為PRIMARYKEY，但是當(dāng)主鍵是由多個(gè)列組成的多列索引時(shí)，定義列時(shí)無(wú)法定義此主鍵，必須在語(yǔ)句最后加上一個(gè)PRIMARYKEY列名，…）子句。創(chuàng)建表時(shí)創(chuàng)建索引在前面兩種情況下，索引都是在表創(chuàng)建之后創(chuàng)建的【例7.5】創(chuàng)建sell_copy表的語(yǔ)句如下，sell_copy表帶有身份證號(hào)和圖書(shū)編號(hào)的聯(lián)合主鍵，并在訂購(gòu)冊(cè)數(shù)列上創(chuàng)建索引。

CREATETABLEsell_copy(

身份證號(hào)CHAR(18)NOTNULL,

圖書(shū)編號(hào)CHAR(20)NOTNULL,

訂購(gòu)冊(cè)數(shù)INT(5),

訂購(gòu)時(shí)間DATETIME, PRIMARYKEY(身份證號(hào),圖書(shū)編號(hào)), INDEXdgcs(訂購(gòu)冊(cè)數(shù)));創(chuàng)建表時(shí)創(chuàng)建索引舉例【例7.5】創(chuàng)建sell_copy表的語(yǔ)句如下，sell_

刪除索引1.使用DROPINDEX語(yǔ)句刪除索引語(yǔ)法格式：

DROPINDEX索引名ON表名【例7.6】刪除Book表上的sm_book索引。

DROPINDEXsm_bookONBook;刪除索引1.使用DROPINDE