第7章 索引與視圖《數(shù)據(jù)庫原理及應(yīng)用》教學(xué)課件

《數(shù)據(jù)庫原理及應(yīng)用》?精品課件合集目錄01

索引概述02B+樹索引結(jié)構(gòu)03全文索引04索引的實現(xiàn)過程05索引的操作（建立、查看與刪除）06

視圖及其工作機(jī)制07視圖的建立、查看與刪除08

視圖的更新第7章索引與視圖索引概述數(shù)據(jù)庫索引是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結(jié)構(gòu)，以協(xié)助快速查詢、更新數(shù)據(jù)庫表中的數(shù)據(jù)。

在大多數(shù)據(jù)庫系統(tǒng)中，數(shù)據(jù)庫讀?。ú樵儯?shù)據(jù)的次數(shù)遠(yuǎn)大于寫入數(shù)據(jù)的次數(shù)，因此，如何優(yōu)化數(shù)據(jù)讀取的效率是數(shù)據(jù)庫系統(tǒng)的主要工作之一。

索引是一種以空間代價換取時間效率提升的方法。B+樹索引B+樹索引是關(guān)系數(shù)據(jù)庫系統(tǒng)中最常用的一種索引結(jié)構(gòu)。B+樹中的B代表平衡（Balance），因為B+樹是從平衡二叉樹演化而來的。數(shù)據(jù)庫中的B+樹索引可以分為：聚集索引也稱聚簇索引:將數(shù)據(jù)存儲與索引放到一起，索引結(jié)構(gòu)的葉子節(jié)點保存所有數(shù)據(jù)。在聚集索引中，表中行的邏輯順序與主鍵值的索引順序相同，因此，一個表只有一個聚集索引。非聚集索引也稱二級索引或輔助索引，除聚集索引外其他索引都稱為二級索引。聚集索引（ClusteredIndex）非聚集索引（Non-ClusteredIndex）

16K聚集索引生成創(chuàng)建表時，系統(tǒng)為表分配一個16K大小的根頁插入表中的記錄最初存儲在根頁中，頁內(nèi)記錄按主鍵從小到大存放，并建立單鏈表。當(dāng)根頁中空間不足時，系統(tǒng)會再分配兩個空的數(shù)據(jù)頁，將根頁中的數(shù)據(jù)拷貝到數(shù)據(jù)頁中，根頁變?yōu)槟夸涰摚夸涰撝写娣胖麈I值和頁指針，并建立頁間指針。在聚集索引中查詢數(shù)據(jù)例1:查詢學(xué)號為‘001111’的學(xué)生。例2:查詢學(xué)號大于‘001111’且小于‘001206’的所有學(xué)生。SELECT*FROMstudentWHEREsno='001111';SELECT*FROMstudentWHEREsno>'001111'ANDsno<'001206';先后調(diào)入內(nèi)存的頁：根頁1→目錄頁2→數(shù)據(jù)頁7先后調(diào)入內(nèi)存的頁：根頁1→目錄頁2→數(shù)據(jù)頁7

→數(shù)據(jù)頁8→數(shù)據(jù)頁9在聚集索引中查詢數(shù)據(jù)若1個目錄頁中可存放8行，則B+樹變?yōu)樽髨D；例1:查詢學(xué)號為‘001111’的學(xué)生。例2:查詢學(xué)號大于‘001111’且小于‘001206’的所有學(xué)生。先后調(diào)入內(nèi)存的頁：根頁1→數(shù)據(jù)頁7先后調(diào)入內(nèi)存的頁：根頁1→數(shù)據(jù)頁7

→數(shù)據(jù)頁8→數(shù)據(jù)頁9結(jié)論:樹的高度直接影響查詢效率問題:如何才能降低樹的高度呢?答：盡量降低主鍵的存儲空間思考：存儲字符串‘123456789’和存儲整數(shù)123456789，哪個空間大？創(chuàng)建聚集索引創(chuàng)建方式一：在建表時定義主鍵ALTERTABLEstuADDPRIMARYKEY(sno);問題:如果不定義主鍵能否創(chuàng)建聚集索引？如果不定義主鍵，系統(tǒng)將使用第一個唯一（UNIQUE）索引創(chuàng)建聚集索引。如果表沒有主鍵，且沒有唯一索引，則系統(tǒng)會自動生成一個rowid作為隱藏的聚集索引主鍵。創(chuàng)建方式二：修改表時創(chuàng)建，如表是從外部導(dǎo)入的則沒有主鍵。使用聚集索引EXPLAINSELECT*FROMstudentWHEREsno='001111';使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL語句，分析你的查詢語句EXPLAINSELECT*FROMstudentWHEREsname='王林';EXPLAINSELECT*FROMstudentWHEREsno>'001111'ANDsno<'001206';EXPLAINSELECT*FROMstudentWHEREtotalcredit>45;創(chuàng)建二級索引二級索引是在非主鍵字段上創(chuàng)建的索引；生成的B+樹索引特點如下：數(shù)據(jù)頁中按拼音順序存儲索引鍵值，并存儲相應(yīng)的主鍵值；目錄頁中存儲索引鍵值和頁指針；每張表有多個字段，可創(chuàng)建多個二級索引。（1）CREATETABLEtudent(

……

INDEXidx_sname(sname)

)CHARSET=gbk;（2）CREATEINDEXidx_snameONstudent(sname);使用二級索引查詢SELECTsnoFROMstudentWHEREsname='王林';先后調(diào)入內(nèi)存的頁：目錄頁1→目錄頁2→數(shù)據(jù)頁7→數(shù)據(jù)頁6查詢?nèi)蝿?wù)在二級索引中就能完成；思考：SELECT*FROMstudentWHEREsname='王林';這個查詢在二級索引中能否完成？回表查詢思考:為什么在寫SELECT語句時，SELECT后面盡量寫字段名而不用“*”。聯(lián)合索引聯(lián)合索引也稱多列索引，是建立在多個字段上的索引；聯(lián)合索引中的數(shù)據(jù)根據(jù)最左側(cè)的字段進(jìn)行排序，一般把查詢中最頻繁使用的字段放在索引最左側(cè)；利用聯(lián)合索引進(jìn)行查詢要遵循最左前綴原則；有時查詢的字段不是最左側(cè)的，系統(tǒng)也使用索引來查詢；為什么？若查詢的字段都在聯(lián)合索引中，此時稱為索引覆蓋。聯(lián)合索引的創(chuàng)建與使用聯(lián)合索引也是二級索引，一般創(chuàng)建方式如下：CREATEINDEXidx_sname_birthdayONstudent(sname,birthday);例：若student表中已在sno上創(chuàng)建了聚集primary；在sname創(chuàng)建了二級索引idx_sname；在sname+birthday創(chuàng)建了聯(lián)合索引idx_sname_birthday；分析以下查詢語句對索引的使用情況。（1）SELECTsnoFROMstudentWHEREsname='王林'ANDbirthday='2005-01-01';（2）SELECT*FROMstudentWHEREsname='王林'ANDbirthday='2005-01-01';（3）SELECTsno,sname,birthdayFROMstudentWHEREbirthday='2005-01-01';（4）SELECTsno,sname,birthday,deptFROMstudentWHEREbirthday='2005-01-01';二級索引idx_sname_birthday二級索引idx_sname_birthday和聚集索引primary二級索引idx_sname_birthday全表掃描全文索引創(chuàng)建與使用問題：查找是“三好生”的學(xué)生全文索引：能夠?qū)⒋鎯τ跀?shù)據(jù)庫中的文本中的任意內(nèi)容查找出來的技術(shù)。創(chuàng)建全文索引：CREATEFULLTEXTINDEXft_remarksONstudent(remarks);使用全文索引：SELECT*FROMstudentWHEREMATCH(remarks)AGAINST('三好生');索引的設(shè)計原則為使索引的使用效率更高，在創(chuàng)建索引時，通常遵循以下設(shè)計原則：（1）索引命名符合規(guī)范；主鍵索引名為pk_字段名；唯一索引名為uk_字段名；普通索引名為idx_字段名。（2）條件子句中頻繁使用的字段、數(shù)字型的字段、存儲空間較小的字段適合建立索引；選擇在WHERE子句、GROUPBY子句、ORDERBY子句或表與表之間連接運(yùn)算等頻繁使用的字段上建立索引（3）重復(fù)值較高的字段、更新頻繁的字段不適合建立索引；（4）索引字段的值很長，最好使用字段值的前綴建立索引；

CREATEINDEXidx_cameONcourse(cname(6));（5）限制索引的數(shù)量，否則會降低系統(tǒng)效率；思考：前綴索引取多少字符來創(chuàng)建建索引CREATEINDEXpf_cnameONcourse(cname(4));【例7.3】在course表的cname字段上創(chuàng)建前綴索引pf_cnameSELECTCOUNT(DISTINCTLEFT(cname,3))/COUNT(cname)FROMcourselength值取3時，執(zhí)行結(jié)果為0.7778，SELECTCOUNT(DISTINCTLEFT(cname,4))/COUNT(cname)FROMcourselength值取4時，執(zhí)行結(jié)果為1.0000。算法：索引的設(shè)計原則索引的使用原則（1）查詢時應(yīng)保證索引字段獨立例：student表已按主鍵sno字段建立了索引，查詢語句“EXPLAINSELECT*FROMstudentWHEREsno+'2'>'001106';”會不會使用索引？轉(zhuǎn)換：EXPLAINSELECT*FROMstudentWHEREsno>'001106'，執(zhí)行計劃結(jié)果如下：（2）模糊查詢中通配符不要放在最左邊使用

模糊查詢時，若匹配模式中的最左側(cè)含有通配符（%），會導(dǎo)致MySQL執(zhí)行全表掃描，而不會使用設(shè)置的索引。例：student表中已按sname字段建立索引sname_index，執(zhí)行“SELECT*FROMstudentWHEREsnameLIKE'王%';”語句會使用索引，執(zhí)行計劃如下圖。執(zhí)行“SELECT*FROMstudentWHEREsnameLIKE'%王%';”語句則會放棄使用索引，采用全表掃描的方式查詢，執(zhí)行計劃如下圖。索引的使用原則索引的使用原則（3）最左前綴匹配原則：對于聯(lián)合索引，系統(tǒng)按從左到右的順序使用索引中的字段，一個查詢可以只使用索引中的一部分，但只能是最左側(cè)部分。例如索引是index(a,b,c)可以支持a、a,b、a,b,c三種組合進(jìn)行查找，但不支持b,c或c進(jìn)行查找。例：按sname字段、sex字段和birthday字段建立的聯(lián)合索引名為idx_sname_sex_birthday，考慮以下查詢語句對索引的使用：SELECT*FROMstudentWHEREsname='王林'ANDsex='男'

;SELECT*FROMstudentWHEREbirthday='2000-01-01'ANDsname='王林';SELECT*FROMstudentWHEREsex='男'ANDbirthday>'2000-01-01';

（4）查詢時使用索引覆蓋。

當(dāng)SELECT查詢語句涉及的字段包含在復(fù)合索引文件中，WHERE語句不需要滿足最左前綴匹配，系統(tǒng)也會按索引執(zhí)行。稱此為索引覆蓋。索引的使用原則-索引覆蓋例：若已建立了sname+sex+birthday的復(fù)合索引，執(zhí)行下面的查詢：SELECTsnameFROMstudentWHEREbirthday=‘2000-01-01’;該查詢會不會使用索引？此查詢雖不符合最左前綴匹配原則，但因索引文件中包含了sname和birthday兩個字段，因此無需回表，即可完成查詢。二級索引中的存儲的信息比聚集索引少，因此，B+樹的高度較小，查詢速度可能較快。MySQL中查詢的基本流程MySQL支持多種引擎，InnoDB的主鍵索引采用聚集索引，輔助索引采用非聚集索引。查詢的基本流程如下圖。根據(jù)索引生成執(zhí)行計劃MySQL中創(chuàng)建索引的類別（1）PRIMARY：主鍵索引，索引列值唯一且不能為空；（2）INDEX：普通索引，索引列沒有任何限制。（3）UNIQUE：唯一索引，索引列的值必須是唯一的，但允許有空值。（4）FULLTEXT：全文索引。（5）SPATIAL：空間索引，對空間數(shù)據(jù)類型的字段建立的索引(自學(xué))。（6）哈希索引（也稱HASH索引）：對于每一條行數(shù)據(jù)，存儲引擎對所有的索引列計算一個哈希碼。將所有的哈希碼存儲在索引中，同時在哈希表中保存指向每一個數(shù)據(jù)行的指針。系統(tǒng)自建。創(chuàng)建索引1.建表時創(chuàng)建索引CREATETABLE表名（字段列表，[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY<index_name>

(<column_name>[(length)][ASC|DESC],……)）;CREATETABLEstudent(……INDEXidx_sname_dept(sname,dept),FULLTEXTft_remarks(remarks));【例7.1】（1）在sname字段和dept字段上創(chuàng)建聯(lián)合索引idx_sname_dept；（2）在remarks字段上創(chuàng)建全文索引ft_remarks。創(chuàng)建索引2.建表后創(chuàng)建索引CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX<index_name>ON<table_name>(<column_name>[(length)][ASC|DESC]……);CREATEUNIQUEINDEXuk_cnameONcourse(cname);【例7.2】在course表的cname字段上創(chuàng)建唯一索引uk_cnameCREATEINDEXpf_cnameONcourse(cname(4));【例7.3】在course表的cname字段上創(chuàng)建前綴索引pf_cname思考：CREATEINDEX命令能創(chuàng)建主索引嗎？創(chuàng)建索引3.修改表結(jié)構(gòu)添加索引ALTERTABLE<table_name>ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX<index_name>(<column_name>[(length)][ASC|DESC]……);ALTERTABLEscoreADDUNIQUEINDEXidx_sno_cno(sno,cno);【例7.3】在score表的sno字段和cno字段創(chuàng)建組合唯一索引，約束同一個學(xué)生的同一門課成績只能出現(xiàn)一次?？梢酝ㄟ^SHOWINDEX語句來查看索引。語法格式為：SHOWINDEXFROM<table_name>[where<condition>];查看索引【例7.4】查看course表中的索引。SHOWINDEXFROMcourse;索引文件建立后，SQL語句在執(zhí)行過程中如何使用索引由數(shù)據(jù)庫系統(tǒng)決定，但可以使用EXPLAIN關(guān)鍵字查看SQL語句執(zhí)行中索引的使用情況。查看索引使用【例7.5】查看student表中的索引使用。EXPLAINSELECT*FROMstudentWHEREsnameLIKE'王%';可以通過ALTERTABLE語句和DROPINDEX語句兩種方式刪除索引。刪除索引【例7.7】刪除student表中的idx_sname_dept索引。ALTERTABLEstudent1DROPINDEXidx_sname_dept;ALTERTABLE<table_name>DROPINDEX<index_name>；

DROPINDEX<index_name>ON<table_name>;【例7.8】刪除score表中的uk_sno_cno索引。DROPINDEXuk_sno_cnoONscore;索引總結(jié)優(yōu)點：使用索引，可以在查詢的過程中，提高系統(tǒng)的性能。缺點：占用更多的存儲空間；索引的維護(hù)成本很高，每次新增、刪除數(shù)據(jù)都需要整理B+樹結(jié)構(gòu)（結(jié)點分裂、合并）拖慢了增刪改的速度。

索引必須根據(jù)表中的列數(shù)據(jù)的使用特性進(jìn)行創(chuàng)建，而不能隨意建立，在什么情況下使用索引、使用什么類型的索引以及在什么情況下不使用索引，需要根據(jù)數(shù)據(jù)庫的設(shè)計需求或使用經(jīng)驗來判斷。視圖概述視圖可以看成是一個窗口，它所反映的是一個表或若干表的局部數(shù)據(jù)。視圖一經(jīng)定義，用戶就可以把它當(dāng)作表一樣來查詢數(shù)據(jù)。視圖和基本表不同，視圖是一個虛表，即視圖儲存的是查詢語句而不是查詢結(jié)果。視圖是定義在基本表上的，也可以定義在視圖上；一個視圖可在幾個表或視圖上建立，一個表或視圖也可建立多個視圖。視圖2視圖1基本表1基本表2基本表3視圖定義CREATE

[ORREPLACE]VIEW<視圖名>

AS<子查詢>[WITHCHECKOPTION]執(zhí)行CREATEVIEW語句時只是把視圖定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。WITHCHECKOPTION表示對視圖進(jìn)行UPDATE,INSERT和DELETE操作時要保證更新、插入和刪除的行滿足視圖定義中的謂詞條件。如果子查詢中包含有計算列，須指定列名（別名）。視圖定義【例7.9】建立視圖view_dept，包含專業(yè)信息。CREATEVIEWview_deptASSELECTdistinctdeptFROMstudent;【例7.10】建立計算機(jī)系學(xué)生的視圖view_cs。CREATEVIEWview_deptASSELECT*FROMstudentWHEREdept='計算機(jī)';注意：INSERTINTOview_cs1(sno,sname,dept)VALUES(‘111111’,‘a(chǎn)bc’,‘通信工程’);這條語句能否執(zhí)行？視圖定義【例7.11】建立通信工程系學(xué)生的視圖view_ce，并要求進(jìn)行修改和插入操作時仍需保證該視圖只有通信工程系的學(xué)生。CREATEVIEWview_ceASSELECT*FROMstudentWHEREdept='通信工程'WITHCHECKOPTION;注意：INSERTINTOview_ce(sno,sname,dept)VALUES('111110','abc','計算機(jī)');這條語句能否執(zhí)行？視圖定義【例7.12】建立反映學(xué)生年齡的視圖view_age，包含sno、sname、age。CREATEVIEWview_age(sno,sname,age)ASSELECTsno,sname,YEAR(CURDATE())-YEAR(birthday)FROMstudent;注意：INSERTINTOview_ageVALUES('111101','abc',20);這條語句能否執(zhí)行？視圖定義【例7.13】建立反映學(xué)生平均成績的視圖view_avgscore，包含sno、avg_score。CREATEVIEWview_avgscore(sno,avg_score)ASSELECTsno,AVG(grade)FROMscoreGROUPBYsno;注意：UPDATEview_avgscoreSETavg_score=avg_score+10WHEREsno='001101';這條語句能否執(zhí)行？視圖定義【例7.14】建立計算機(jī)系選修了102號課程的學(xué)生的視圖view_cs_102，視圖中包括sno、sname、grade。CREATEVIEWview_cs_102ASSELECTstudent.sno,sname,gradeFROMstudent,scoreWHEREstudent.sno=score.snoANDdept='計算機(jī)'ANDcno='102';視圖定義【例7.15】建立計算機(jī)系選修了102號課程且成績在90分以上（含90）的學(xué)生的視圖view_cs_102_90，視圖中包含學(xué)號、姓名、成績。CREATEVIEWview_cs_102_90ASSELECTsno,sname,gradeFROMview_cs_102WHEREgrade>=90;也可以通過視圖完成查詢:SELECT*FROMview_cs_102WHEREgrade>80;視圖查看【例7.16】查看視圖view_cs的字段信息DESCRIBEview_cs;【例7.17】查看視圖view_ce的創(chuàng)建語句。SHOWCREATEVIEWview_ce;【例7.18】查看information_schema數(shù)據(jù)庫下的views表。SELECT*FROMinformation_schema.views;視圖修改CREATEORREPLACEVIEWALTERVIEW<視圖名>AS<子查詢>（1）使用CREATEORREPLACE語句修改視圖【例7.19】修改視圖view_cs，刪除備注字段remarks。CREATEORREPLACEVIEWview_csASSELECTsno,sname,dept,birthday,totalcreditFROMstudentWHEREdept='計算機(jī)';視圖修改（2）使用ALTER語句修改視圖ALTERVIEW<視圖名>AS<子查詢>【例7.20】修改視圖view_cs，增加字段sex。ALTERVIEWview_csASSELECTsno,sname,dept,sex,birthday,totalcreditFROMstudentWHEREdept='計算機(jī)';更新視圖數(shù)據(jù)當(dāng)對視圖中的數(shù)據(jù)進(jìn)行增加、刪除和修改操作時，基本表中的數(shù)據(jù)會相應(yīng)地發(fā)生變化，反之亦然。

要使視圖可更新，視圖中的行和列與底層基本表之間必須存在一對一的關(guān)系，行列子集視圖是可更新的。在定義視圖的SELECT語句后的字段列表中使用DISTINCT、聚合函數(shù)、子查詢中有GROUPBY、HAVING、UNION短語等，視圖不支持?jǐn)?shù)據(jù)更新；注意:視圖一般用來簡化查詢工作,盡量不用視圖做數(shù)據(jù)更新操作.【例7.24】使用DELETE語句將通信工程系學(xué)生視圖view_ce中sno為001203的學(xué)生刪除。DELETEFROMview_ceWHEREsno='001203';更新視圖數(shù)據(jù)DELETEFROMstudentWHEREsno='001203'ANDdept='通信工程';刪除視圖【例7.25】刪除視圖view_cs_102。DROPVIEWview_cs_102;刪除視圖的格式：

DROPVIEW<view_name>;視圖的優(yōu)點簡單地講，視圖具有以下優(yōu)點：操作簡單減少數(shù)據(jù)冗余數(shù)據(jù)安全適應(yīng)靈活多變的需求能夠分解復(fù)雜的查詢邏輯數(shù)據(jù)庫的三級模式結(jié)構(gòu)數(shù)據(jù)庫應(yīng)用B