項(xiàng)目4使用索引和視圖優(yōu)化查詢索引、視圖的創(chuàng)建和使用使用索引和視圖優(yōu)化查詢4.1索引管理4.2視圖管理

4.1索引管理4.1.1

索引概述4.1.2創(chuàng)建索引4.1.3刪除索引4.1.4使用Navicat管理索引當(dāng)數(shù)據(jù)表中的數(shù)據(jù)記錄達(dá)到十萬多條乃至上百萬條時(shí)，有些查詢語句的效率會(huì)明顯降低，這將不能滿足用戶的需求。如何建立索引才能提高數(shù)據(jù)庫的查詢性能？建立索引是越多越好嗎？

4.1索引管理索引是一種可以加快數(shù)據(jù)檢索的數(shù)據(jù)庫對(duì)象，主要用于提高數(shù)據(jù)查詢性能。索引可以從大量的數(shù)據(jù)中迅速找到所需的數(shù)據(jù)，不再需要檢索整個(gè)數(shù)據(jù)表，所以可以大大提高檢索的效率。

4.1.1索引概述索引是一種單獨(dú)的、物理的數(shù)據(jù)庫結(jié)構(gòu)。索引包含從表或視圖中的一列或多列生成的鍵，以及映射到指定數(shù)據(jù)行的存儲(chǔ)位置指針。數(shù)據(jù)庫中索引的形式與圖書的目錄相似，鍵值就像目錄中的標(biāo)題，指針相當(dāng)于頁碼。索引的功能就像圖書目錄。當(dāng)進(jìn)行數(shù)據(jù)檢索時(shí)，系統(tǒng)先搜索索引頁面，從中找到所需數(shù)據(jù)的指針，再直接通過指針從數(shù)據(jù)頁面中讀取數(shù)據(jù)。索引一旦創(chuàng)建，將由數(shù)據(jù)庫自動(dòng)管理和維護(hù)。例如，向表中插入、更新和刪除一條記錄時(shí)，數(shù)據(jù)庫會(huì)自動(dòng)在索引中做出相應(yīng)的修改。

4.1.1索引概述索引的優(yōu)點(diǎn)在數(shù)據(jù)庫中建立索引主要有以下優(yōu)點(diǎn)：加速數(shù)據(jù)檢索。索引能夠以一列或多列值為基礎(chǔ)實(shí)現(xiàn)快速查找數(shù)據(jù)行。優(yōu)化查詢：查詢優(yōu)化器是依賴于索引起作用的，索引能夠加速連接、排序、分組等操作。強(qiáng)制實(shí)行的唯一性。通過給列創(chuàng)建唯一索引，可以保證表中的數(shù)據(jù)不重復(fù)。

索引分類索引按功能邏輯分類：普通索引主鍵索引唯一索引全文索引空間索引索引按列的數(shù)量分類：?jiǎn)瘟兴饕M合索引按存儲(chǔ)方式分類：B-Tree索引Hash索引索引的分類普通索引：

MySQL中基本索引類型，沒有什么限制，允許在定義索引的列中插入重復(fù)值和空值，純粹為了查詢數(shù)據(jù)更快一點(diǎn)。

CREATEINDEX索引名ON表名(列名);

或ALTERTABLE表名ADDINDEX索引名(列名);唯一索引：索引列中的值必須是唯一的，但是允許為空值，在為列定義unique約束時(shí)，會(huì)自動(dòng)為該列建立唯一索引。

CREATEUNIQUEINDEX

索引名ON

表名(列名);

或

ALTERTABLE

表名ADDUNIQUEINDEX

索引名(列名);主鍵索引：是一種特殊的唯一索引，不允許有空值。當(dāng)用戶為表定義一個(gè)主鍵時(shí)，系統(tǒng)將自動(dòng)為該列創(chuàng)建一個(gè)主鍵索引，一個(gè)表只能有一個(gè)主鍵索引。索引的分類

全文索引：全文索引是指在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復(fù)值和空值。全文索引只可以在CHAR、VARCHAR或TEXT類型的列上創(chuàng)建。

CREATEFULLTEXTINDEX索引號(hào)ON表名(列名);

空間索引：空間索引是對(duì)空間數(shù)據(jù)類型的字段建立的索引，MySQL中的空間數(shù)據(jù)類型有四種，GEOMETRY、POINT、LINESTRING、POLYGON。

CREATESPATIALINDEX索引號(hào)ON表名(列名);索引的分類1、單列索引：一個(gè)索引只包含單個(gè)列，但一個(gè)表中可以有多個(gè)單列索引。

CREATEINDEX索引名ON表名(列名);2、組合索引組合索引是指在表的多個(gè)字段組合上創(chuàng)建的索引，用戶可以通過這幾個(gè)字段進(jìn)行查詢。要想引用該索引，用戶必須使用這些字段中的第一個(gè)字段，也就是最左邊的字段。

CREATEINDEX索引名ON表名(列名1,列名2)索引的分類按存儲(chǔ)方法分類B-Tree索引，Hash索引B-Tree索引B-Tree索引是MySQL中最主要的索引類型，適用于任何有序的數(shù)據(jù)類型。在MySQL中，B-Tree索引對(duì)應(yīng)的算法是B+Tree，在實(shí)際存儲(chǔ)引擎中，如InnoDB引擎，它的存儲(chǔ)結(jié)構(gòu)是B+Tree。B-Tree索引的特點(diǎn)是可以用于查找范圍值，支持ORDERBY操作，并且可以在索引上完成聚集操作。索引的分類按存儲(chǔ)方法分類B樹索引，Hash索引Hash索引

Hash索引是一種特殊的索引，它使用Hash算法來存儲(chǔ)和檢索數(shù)據(jù)。這種索引可以快速地查找單個(gè)值，但是不適合范圍查詢和排序。Memory引擎表默認(rèn)使用哈希索引，對(duì)于我們平時(shí)最長(zhǎng)使用的表即磁盤表，使用Hash索引需要特別指定。索引的設(shè)計(jì)原則一個(gè)表創(chuàng)建大量的索引，會(huì)影響INSERT、UPDATE和DELETE語句的性能。應(yīng)避免對(duì)經(jīng)常更新的表創(chuàng)建過多的索引，要限制索引的數(shù)目若表的數(shù)據(jù)量大，對(duì)表數(shù)據(jù)的更新較少而查詢較多，可以創(chuàng)建多個(gè)索引來提高性能。經(jīng)常需要排序、分組和聯(lián)合操作的字段一定要建立索引，即將用于JOIN、WHERE判斷和ORDERBY排序的字段上創(chuàng)建索引。索引的設(shè)計(jì)原則盡量避免對(duì)數(shù)據(jù)庫中某個(gè)含有大量重復(fù)值的字段建立索引，在這樣的字段上建立索引有可能降低數(shù)據(jù)庫的性能。在主鍵上創(chuàng)建索引，在InnoDB表中如果通過主鍵來訪問數(shù)據(jù)效率是非常高的。每個(gè)表只能創(chuàng)建一個(gè)主鍵索引。要限制索引的數(shù)目，對(duì)于不再使用或者很少使用的索引要及時(shí)刪除。盡量使用前綴來索引，如果索引字段的值很長(zhǎng)，最好使用值的前綴來索引。創(chuàng)建索引創(chuàng)建索引通常有3種命令方式，分別是使用創(chuàng)建索引命令創(chuàng)建索引、創(chuàng)建表時(shí)附帶創(chuàng)建索引和使用修改表命令創(chuàng)建索引。1．使用創(chuàng)建索引命令創(chuàng)建索引CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名ON表名(列名[(長(zhǎng)度)][ASC|DESC])【例4.1】為了便于按姓名查詢，在student表的sname列建立一個(gè)升序普通索引sname_index。CREATEINDEXsname_indexONstudent(snameASC);【例4.2】在course表的cname列上建立一個(gè)唯一索引cname_index。CREATEUNIQUEINDEXcname_indexONcourse(cname);【例4.3】在score表的sno和cno列上建立一個(gè)復(fù)合索引sc_index。CREATEINDEXsc_indexONscore(sno,cno);創(chuàng)建索引2．在創(chuàng)建表時(shí)附帶創(chuàng)建索引【例4.4】在創(chuàng)建teacher表的同時(shí)，在tname列建立索引tname_index和一個(gè)前綴索引spec_index。CREATETABLE<表名>(<字段名1><數(shù)據(jù)類型1>[<列級(jí)完整性約束條件1>][,<字段名2><數(shù)據(jù)類型2>[<列級(jí)完整性約束條件2>],…][UNIQUE|FULLTEXT|SPATIAL]<INDEX|KEY>[索引名]（[(長(zhǎng)度)][ASC|DESC]）[,…]);CREATETABLEteacher(tnoCHAR(12)NOTNULLCOMMENT'教師編號(hào)',tnameCHAR(8)NOTNULLCOMMENT'教師姓名',specCHAR(10)NOTNULLCOMMENT'專業(yè)',profCHAR(10)NOTNULLCOMMENT'職稱',PRIMARYKEY(tno),INDEXtname_index(tname),INDEXspec_index(spec(5)));創(chuàng)建索引3．使用修改表命令創(chuàng)建索引【例4.5】在teacher表上建立tno主鍵索引（假設(shè)未創(chuàng)建主鍵索引），建立tname和prof的組合索引。ALTERTABLE表名

ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名([(長(zhǎng)度)][ASC|DESC]);ALTERTABLEteacherADDPRIMARYKEY(tno),ADDINDEXmulti_index(tname,prof);刪除索引刪除索引語句結(jié)構(gòu)刪除索引的語句方式主要有以下兩種DROPINDEX索引名ON表名或ALTERTABLE表名DROP<INDEX索引名>|<PRIMARYKEY>|<FOREIGNKEY外鍵名>;【例4.6】刪除teacher表上的組合索引tname_prof_index。DROPINDEXtname_prof_indexONteacher;【例4.7】利用ALTERTABLE語句刪除例4.6中的索引。ALTERTABLEteacherDROPINDEXtname_prof_index;使用Navicat管理索引（1）在Navicat中，連接到MySQL服務(wù)器，依次展開teaching數(shù)據(jù)庫，找到student表，打開表的設(shè)計(jì)窗口，在設(shè)計(jì)窗口的上方打開“索引”選項(xiàng)卡使用Navicat管理索引（2）分別在選項(xiàng)卡主窗口的“名”、“字段”、“索引類型”、“索引方法”等列中輸入索引名稱、索引字段、索引的類型以及索引方法等內(nèi)容4.2視圖管理4.2.1

定義視圖4.2.2使用Navicat創(chuàng)建和管理視圖4.2.3視圖的應(yīng)用4.2.4視圖的作用4.2視圖管理

在數(shù)據(jù)庫的實(shí)際應(yīng)用中，很多查詢會(huì)使用到student、score和course3個(gè)表的連接，如已知某學(xué)生的學(xué)號(hào)或姓名，查詢?cè)搶W(xué)生某門課的成績(jī)，或者某個(gè)學(xué)期各門課的成績(jī)等。如果能把這3個(gè)表的數(shù)據(jù)放到一個(gè)表中，每次查詢只從這一個(gè)表中查詢，就方便多了。如何解決這一問題4.2.1定義視圖認(rèn)識(shí)視圖1.視圖是從一個(gè)或多個(gè)基本表中導(dǎo)出的虛表，是通過SELECT語句對(duì)基本表的查詢建立起來的。?2.?數(shù)據(jù)庫中只存放視圖的定義，而不存放視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在原來的基本表中。?3.?視圖中的數(shù)據(jù)行和列都來自于基本表，是在視圖被引用時(shí)動(dòng)態(tài)生成的4.?視圖就像一個(gè)窗口，通過它可以看到數(shù)據(jù)庫中自己感興趣的數(shù)據(jù)及其變化。SQL語言支持的三級(jí)模式結(jié)構(gòu)SQL用戶視圖視圖基本表基本表基本表存儲(chǔ)文件存儲(chǔ)文件存儲(chǔ)文件內(nèi)模式模式外模式DBMS建立視圖語句格式

CREATEVIEW

<視圖名>[(<列名>[，<列名>]…)]AS<子查詢>[WITH[CASCADE|LOCAL]CHECKOPTION]；組成視圖的屬性列名：全部省略或全部指定子查詢可以是任意的SELECT語句。WITHCHECKOPTION表示對(duì)視圖進(jìn)行UPDATE、INSERT和DELETE操作時(shí)要保證更新、插入或刪除的行滿足視圖定義中的條件RDBMS執(zhí)行CREATEVIEW語句時(shí)只是把視圖定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。在對(duì)視圖查詢時(shí)，按視圖的定義從基本表中將數(shù)據(jù)查詢出來創(chuàng)建視圖（續(xù)）

【例4.8】建立計(jì)算機(jī)應(yīng)用專業(yè)學(xué)生的視圖。

CREATEVIEWcomputer_studentASSELECTsno,sname,sex,spec,birthdayFROMstudentWHEREspec='計(jì)算機(jī)應(yīng)用';創(chuàng)建視圖（續(xù)）【例4.9】建立計(jì)算機(jī)應(yīng)用專業(yè)學(xué)生的視圖，并要求進(jìn)行修改和插入操作時(shí)仍需保證該視圖只有計(jì)算機(jī)應(yīng)用專業(yè)的學(xué)生。

CREATEVIEWcomputer_studentASSELECTsno,sname,birthdayFROMstudentWHEREspec='計(jì)算機(jī)應(yīng)用'WITHCHECKOPTION;創(chuàng)建視圖（續(xù)）對(duì)computer_student視圖的更新操作：修改操作：自動(dòng)加上spec='計(jì)算機(jī)應(yīng)用'的條件刪除操作：自動(dòng)加上spec='計(jì)算機(jī)應(yīng)用'的條件插入操作：自動(dòng)檢查spec屬性值是否為'計(jì)算機(jī)應(yīng)用'如果不是，則拒絕該插入操作如果沒有提供spec屬性值，則自動(dòng)定義spec為'計(jì)算機(jī)應(yīng)用'創(chuàng)建視圖（續(xù)）基于多個(gè)基表的視圖【例4.9】建立一個(gè)能查看到學(xué)生的學(xué)號(hào)、姓名、專業(yè)、課程名和成績(jī)的視圖（包括學(xué)號(hào)、姓名、課程名、成績(jī)）。

CREATEVIEWstudent_scoreASSELECTstudent.sno,sname,spec,cname,gradeFROMscoreJOINstudentONstudent.sno=score.snoJOINcourseONo=o;創(chuàng)建視圖（續(xù)）基于視圖的視圖

視圖不僅可以建立在基本表上，也可以建立在已定義好的視圖上

【例4.10】

創(chuàng)建視圖computer_score，統(tǒng)計(jì)計(jì)算機(jī)應(yīng)用專業(yè)的學(xué)生學(xué)號(hào)、姓名、課程名和成績(jī)。

CREATEVIEWcomputer_scoreASSELECTsno,sname,cname,gradeFROMstudent_scoreWHEREspec='計(jì)算機(jī)應(yīng)用';創(chuàng)建視圖（續(xù)）分組視圖【例4.11】

將學(xué)生的學(xué)號(hào)及他的平均成績(jī)定義為一個(gè)視圖

CREATEVIEWstu_avgASSELECTsno,avg(grade)FROMscoreGROUPBYsno;查看視圖（1）使用DESCRIBE語句查看視圖

DESCRIBE視圖名或表名;（2）使用SHOWCREATEVIEW語句查看視圖

SHOWCREATEVIEW視圖名;修改視圖的定義（1）CREATEORREPLACEVIEW語句 CREATEORREPLACEVIEWmaths_computerASSELECTsno,sname,cname,gradeFROMcomputer_scoreWHEREspec='計(jì)算機(jī)應(yīng)用'ANDcname='高等數(shù)學(xué)';（2）ALTER語句

ALTERVIEWmaths_computerASSELECTsno,sname,cname,gradeFROMcomputer_scoreWHEREspec='計(jì)算機(jī)應(yīng)用'ANDcname='高等數(shù)學(xué)';刪除視圖語句的格式：

DROPVIEW[IFEXISTS]視圖名1[,視圖名2…];該語句從數(shù)據(jù)字典中刪除指定的視圖定義如果該視圖上還導(dǎo)出了其他視圖，使用CASCADE級(jí)聯(lián)刪除語句，把該視圖和由它導(dǎo)出的所有視圖一起刪除刪除基表時(shí)，由該基表導(dǎo)出的所有視圖定義都必須顯式地使用DROPVIEW語句刪除4.2視圖管理4.2.1定義視圖4.2.2使用Navicat創(chuàng)建和管理視圖4.2.3視圖的應(yīng)用4.2.4視圖的作用使用Navicat管理視圖展開“視圖”節(jié)點(diǎn)使用Navicat管理視圖單擊“視圖創(chuàng)建工具”按鈕，打開“視圖創(chuàng)建工具”窗口，在左邊的窗格中是供選擇的用于創(chuàng)建視圖的數(shù)據(jù)源選擇視圖中的查詢字段。使用Navicat管理視圖選擇視圖中的查詢字段4.2視圖管理4.2.1定義視圖4.2.2使用Navicat創(chuàng)建和管理視圖4.2.3視圖的應(yīng)用4.2.4視圖的作用視圖的應(yīng)用1.使用視圖進(jìn)行查詢視圖定義后，就可以像基本表一樣對(duì)視圖進(jìn)行查詢了?！纠?.15】

通過前面建立的視圖student_score，查詢選修“C語言”課程，且成績(jī)?cè)?0分以上的學(xué)號(hào)、姓名、專業(yè)和成績(jī)。

SELECTsno,sname,spec,gradeFROMstudent_scoreWHEREcname='C語言'ANDgrade>=80;視圖的應(yīng)用2.使用視圖進(jìn)行統(tǒng)計(jì)計(jì)算利用聚合函數(shù)進(jìn)行查詢建立的視圖，具有統(tǒng)計(jì)計(jì)算的功能?！纠?.16】

創(chuàng)建視圖course_avg，統(tǒng)計(jì)各門課程的平均成績(jī)，并按課程名降序排列。

CREATEVIEWcourse_avgASSELECTcname課程名,avg(grade)平均成績(jī)FROMscoreJOINcourseONo=oGROUPBYcnameORDERBYcnameDESC;視圖的應(yīng)用3.使用視圖修改基本表數(shù)據(jù)

使用視圖修改表數(shù)據(jù)，是指在視圖中進(jìn)行INSERT、UPDATE和DELETE等操作時(shí)進(jìn)而修改了基本表的數(shù)據(jù)。由于視圖是不實(shí)際存儲(chǔ)數(shù)據(jù)的虛表，因此對(duì)視圖的修改最終要轉(zhuǎn)換為對(duì)基本表的修改?！纠?.17】通過例4.9創(chuàng)建的視圖computer_student，對(duì)基表student進(jìn)行插入操作。

INSERTINTOcomputer_student(sno,sname,sex,birthday,spec)VALUES('20231012106','李常安','男','2005-05-23','計(jì)算機(jī)應(yīng)用');更新視圖（續(xù)）【例4.18】

視圖student_score依賴于student、course和score三