第七章 索引與分區(qū)

第7章索引與分區(qū)項目知識要點(diǎn)知識能力目標(biāo)學(xué)時任務(wù)1索引及其應(yīng)用1索引的分類2創(chuàng)建索引3刪除索引4索引對查詢的影響【典型工作任務(wù)】創(chuàng)建索引【技能要求】1理解各種索引的作用與區(qū)別2掌握建立各種索引的方法【知識要求】1索引的作用與使用2建立索引的SQL語句2項目實(shí)訓(xùn)創(chuàng)建“圖書管理系統(tǒng)”索引與完整性約束2項目知識要點(diǎn)與目標(biāo)索引及其應(yīng)用索引——是一種提高查找速度的機(jī)制索引用來快速地尋找那些具有特定值的記錄，如果沒有索引，執(zhí)行查詢時MySQL必須從第一個記錄開始掃描整個表的所有記錄，直至找到符合要求的記錄。表里面的記錄數(shù)量越多，這個操作的代價就越高。索引提供指針以指向存儲在表中指定列的數(shù)據(jù)值，然后根據(jù)指定的排序次序排列這些指針。數(shù)據(jù)庫使用索引的方式與使用書的目錄很相似：通過搜索索引找到特定的值，然后跟隨指針到達(dá)包含該值的行。如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引，MySQL無需掃描任何記錄即可迅速得到目標(biāo)記錄所在的位置。如果表有1000個記錄，通過索引查找記錄至少要比順序掃描記錄快100倍。索引文件學(xué)生表student中建立“學(xué)號”索引（升序）示意圖學(xué)號記錄號200601011200601023200602032…………2007020110000…………20809995000學(xué)號姓名性別年齡20060101張建男1820060202王歡女1720060102田靜男18………..2080999李華女19…….…..20070201張麗女17學(xué)生表student索引文件索引文件如何影響原表沒有索引文件時：如果要找位于第10000條的學(xué)號”20070201”的記錄,計算機(jī)要在表中查找10000次指針在原表中順序移動學(xué)號姓名性別年齡20060101張建男1820060202王歡女1720060102田靜男18………..2080999李華女19…….…..20070201張麗女17使用索引查找計算機(jī)先在索引文件中學(xué)號為”20070201”的記錄,找到相應(yīng)的記錄號,再到學(xué)生表中直接讀取相關(guān)記錄.有索引文件時：(二分法查找實(shí)例)學(xué)號記錄號200601011200601023200602032…………2007020110000…………20809995000學(xué)號姓名性別年齡20060101張建男1820060202王歡女1720060102田靜男18………..2080999李華女19…….…..20070201張麗女17學(xué)生表student索引文件B樹索引示意圖索引的分類01OPTION02OPTION03OPTION04OPTION普通索引（INDEX）這是最基本的索引類型，它沒有唯一性之類的限制。創(chuàng)建普通索引的關(guān)鍵字是INDEX。唯一性索引（UNIQUE）這種索引和前面的普通索引基本相同，但有一個區(qū)別：索引列的所有值都只能出現(xiàn)一次，即必須是唯一的。創(chuàng)建唯一性索引的關(guān)鍵字是UNIQUE。主鍵（PRIMARYKEY）主鍵是一種唯一性索引，它必須指定為“PRIMARYKEY”。主鍵一般在創(chuàng)建表的時候指定，也可以通過修改表的方式加入主鍵。但是每個表只能有一個主鍵。全文索引（FULLTEXT）MySQL支持全文檢索和全文索引。全文索引的索引類型為FULLTEXT。全文索引只能在VARCHAR或TEXT類型的列上創(chuàng)建，并且只能在MyISAM表中創(chuàng)建。創(chuàng)建索引1.使用CREATEINDEX語句CREATE[UNIQUE|FULLTEXT]INDEX索引名ON表名(列名[(長度)][ASC|DESC],...)使用CREATEINDEX語句可以在一個已有表上創(chuàng)建索引，一個表可以創(chuàng)建多個索引。語法格式：說明：索引名：索引的名稱，索引名在一個表中名稱必須是唯一的。列名：表示創(chuàng)建索引的列名。長度：表示使用列的前多少個字符創(chuàng)建索引。使用列的一部分創(chuàng)建索引可以使索引文件大大減小，從而節(jié)省磁盤空間。BLOB或TEXT列必須用前綴索引。UNIQUE：表示創(chuàng)建的是唯一性索引FULLTEXT：表示創(chuàng)建全文索引；CREATEINDEX語句并不能創(chuàng)建主鍵。創(chuàng)建索引舉例可以在一個索引的定義中包含多個列，中間用逗號隔開，但是它們要屬于同一個表。這樣的索引叫做復(fù)合索引。

CREATEINDEXname-_bookONBook(書名(6)ASC);【例】在Sell表的用戶號列和圖書編號列上建立一個復(fù)合索引sfz_bh-_sell。

CREATEINDEXuser_bh-_sellONSell(用戶號,圖書編號);【例】根據(jù)Book表的書名列上的前6個字符建立一個升序索引name-_book。ALTERTABLE語句創(chuàng)建索引2.使用ALTERTABLE語句使用ALTERTABLE語句修改表，其中也包括向表中添加索引。語法格式如下：

ALTERTABLE表名

ADDINDEX[索引名](列名,...)

/*添加索引*/|ADDPRIMARYKEY[索引方式](列名,...) /*添加主鍵*/|ADDUNIQUE[索引名](列名,...) /*添加唯一性索引*/|ADDFULLTEXT[索引名](列名,...) /*添加全文索引*/ALTERTABLE創(chuàng)建索引舉例【例】在Book表的書名列上創(chuàng)建一個普通索引。ALTERTABLEBookADDINDEX(書名);【例】假設(shè)Book表中主鍵未設(shè)定，為Book表創(chuàng)建以圖書編號為主鍵索引，出版社和出版時間為復(fù)合索引，以加速表的檢索速度。ALTERTABLEBookADDPRIMARYKEY(圖書編號),ADDINDEX(出版社,出版時間);這個例子中，既包括PRIMARYKEY，也包括復(fù)合索引，說明MySQL可以同時創(chuàng)建多個索引。記住，使用PRIMARYKEY的列，必須是一個具有NOTNULL屬性的列。如果想要查看表中創(chuàng)建的索引的情況，可以使用SHOWINDEX語句，例如：SHOWINDEXFROMbook;創(chuàng)建表時創(chuàng)建索引在前面兩種情況下，索引都是在表創(chuàng)建之后創(chuàng)建的。索引也可以在創(chuàng)建表時一起創(chuàng)建。在創(chuàng)建表的CREATETABLE語句中可以包含索引的定義。語法格式：CREATETABLE表名(列名,...|[索引項])其中，索引項語法格式如下：PRIMARYKEY(列名,...)

/*主鍵*/|{INDEX|KEY}[索引名](列名,...) /*索引*/|UNIQUE[INDEX][索引名](列名,...) /*唯一性索引*/|[FULLTEXT][INDEX][索引名](列名,...) /*全文索引*/說明：KEY通常是INDEX的同義詞。在定義列選項的時候，也可以將某列定義為PRIMARYKEY，但是當(dāng)主鍵是由多個列組成的多列索引時，定義列時無法定義此主鍵，必須在語句最后加上一個PRIMARYKEY列名，…）子句。創(chuàng)建表時創(chuàng)建索引舉例CREATETABLEsell_copy(

用戶號CHAR(18)NOTNULL,

圖書編號CHAR(20)NOTNULL,

訂購冊數(shù)INT(5),

訂購時間DATETIME, PRIMARYKEY(用戶號,圖書編號), INDEX(訂購冊數(shù)));【例】創(chuàng)建sell_copy表，設(shè)置用戶號和圖書編號為聯(lián)合主鍵，并在訂購冊數(shù)列上創(chuàng)建索引。刪除索引1.使用DROPINDEX語句刪除索引DROPINDEX索引名ON表名語法格式：【例】刪除book表上索引名為“書名“的索引。DROPINDEX書名ONbook;ALTERTABLE刪除索引2.使用ALTERTABLE語句刪除索引ALTER[IGNORE]TABLE表名

|DROPPRIMARYKEY /*刪除主鍵*/|DROPINDEX索引名

/*刪除索引*/語法格式：【例】刪除Book表上的主鍵和mark索引。ALTERTABLEBookDROPPRIMARYKEY,DROPINDEXmark;如果從表中刪除了列，則索引可能會受到影響。如果所刪除的列為索引的組成部分，則該列也會從索引中刪除。如果組成索引的所有列都被刪除，則整個索引將被刪除。索引對查詢的影響目前本書實(shí)例中所涉及的表最多只有幾十行的數(shù)據(jù)，所以有沒有建立索引，還體會不到查詢速度上的差異，可是當(dāng)一個表里有成千上萬行數(shù)據(jù)的時候，差異就非常明顯了?，F(xiàn)在假設(shè)有一個表，表里只有一列，由數(shù)值1～1000的1000行組成，現(xiàn)在要想查找到數(shù)字1000所在的行。如果沒有索引，要從第一行開始匹配，若數(shù)值不是1000，則轉(zhuǎn)到下一行進(jìn)行匹配，這樣直到第1000行的時候才能找到數(shù)字1000所在行，也就是說服務(wù)器進(jìn)行了1000次的運(yùn)算。而當(dāng)在該列上創(chuàng)建一個索引后，則可以直接在索引值中找到1000的位置，然后找到1000所指向的行，在速度上比全表掃描至少快了100倍。當(dāng)執(zhí)行涉及多個表的連接查詢時，索引將更有價值。索引的弊端首先，索引是以文件的形式存儲的，索引文件要占用磁盤空間。如果有大量的索引，索引文件可能會比數(shù)據(jù)文件更快地達(dá)到最大的文件尺寸。其次，在更新表中索引列上的數(shù)據(jù)時，對索引也需要更新，這可能需要重新組織一個索引，如果表中的索引很多，這是很浪費(fèi)時間的。也就是說，這樣就降低了添加、刪除、修改和其他寫入操作的效率。表中的索引越多，則更新表的時間就越長。但是這些弊端并不妨礙索引的應(yīng)用，因?yàn)樗饕龓淼暮锰幰呀?jīng)基本掩蓋了它的缺陷，在表中有很多行數(shù)據(jù)的時候，索引通常是不可缺少的。數(shù)據(jù)庫分區(qū)RANGE

分區(qū)：基于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。LIST

分區(qū)：類似于按RANGE分區(qū)，區(qū)別是它基于列值匹配一個離散值集合中的某個值來進(jìn)行選擇。HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計算。這個表達(dá)式可以包含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。KEY

分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。分區(qū)類型RANGE分區(qū)PARTITIONBYRANGE(表達(dá)式)（PARTITION分區(qū)1VALUESLESSTHAN(值1)，

….

PARITION分區(qū)nVALUESLESSTHAN(值n|[MAXVALUE])）語法格式：【例】將sell表的數(shù)據(jù)按訂購時間進(jìn)行分區(qū)，2010年前的放p1分區(qū)，2011-2015年放p2分區(qū)，2015年以后的放p3分區(qū)。altertablesellpartitionbyrange(year(訂購時間))

(partitionp1valueslessthan(2010),

partitionp2valueslessthan(2015),

partitionp3valueslessthanmaxvalue);LST分區(qū)PARTITIONBYLIST(表達(dá)式)

（PARTITION分區(qū)1VALUESIN(值列表1)，

….PARITION分區(qū)nVALUESIN(值列表n）語法格式：【例】假設(shè)sell表中是否結(jié)清字段為整數(shù)類型，1表示結(jié)清，0表示未結(jié)清。將sell表的數(shù)據(jù)按是否結(jié)清進(jìn)行分區(qū)，已結(jié)清的放p1分區(qū)，未結(jié)清的放p2分區(qū)。altertablesellpartitionbylist(是否結(jié)清)(partitionp1valuesin(1),partitionp2values