MySQL數(shù)據(jù)庫原理與應(yīng)用項(xiàng)目化教程（微課版） 課件 (含思政) 項(xiàng)目9-查詢優(yōu)化

項(xiàng)目九查詢優(yōu)化

查詢數(shù)據(jù)有時(shí)要用連接查詢、子查詢等操作來實(shí)現(xiàn)，邏輯復(fù)雜、語句也很長(zhǎng)，如果數(shù)據(jù)量大，查詢速度也是一個(gè)問題。另外，如果把數(shù)據(jù)表的所有字段開放給用戶查詢也不夠安全。

為了優(yōu)化對(duì)“學(xué)生成績(jī)管理”數(shù)據(jù)庫的查詢，本項(xiàng)目將通過創(chuàng)建與使用視圖實(shí)現(xiàn)查詢的簡(jiǎn)化并提高數(shù)據(jù)的安全性，通過創(chuàng)建與使用索引加快查詢速度。知識(shí)目標(biāo)：理解視圖、索引的概念識(shí)記視圖、索引相關(guān)操作語句的語法能力目標(biāo)：能創(chuàng)建、使用及管理視圖能創(chuàng)建和管理索引，并能查看索引的使用情況。任務(wù)9.1創(chuàng)建與使用視圖任務(wù)9.2創(chuàng)建與使用索引

任務(wù)9.1創(chuàng)建與使用視圖視圖是虛擬表，也是存儲(chǔ)的查詢。視圖可以方便用戶、提高數(shù)據(jù)安全性、實(shí)現(xiàn)邏輯數(shù)據(jù)獨(dú)立性。

為“學(xué)生成績(jī)管理”數(shù)據(jù)庫創(chuàng)建、管理視圖，并使用視圖查詢或更新基本表的數(shù)據(jù)?！救蝿?wù)描述】創(chuàng)建與使用視圖9.1【相關(guān)知識(shí)】創(chuàng)建與使用視圖9.19.1.59.1.19.1.2

修改視圖

視圖的概念

創(chuàng)建視圖9.1.3

查看視圖

使用視圖9.1.49.1.6

刪除視圖視圖（VIEW）看上去是表，但它其實(shí)是虛擬表，因?yàn)樗旧頉]有數(shù)據(jù)。相反地，前面用CREATETABLE語句創(chuàng)建的表是有數(shù)據(jù)的，為了和視圖區(qū)分開，把真正存放數(shù)據(jù)的表叫做基本表。視圖的數(shù)據(jù)來自于對(duì)一個(gè)或多個(gè)基本表（或視圖）查詢的結(jié)果,因此，視圖又叫做存儲(chǔ)的查詢，定義視圖的主體部分就是一條查詢語句，打開視圖看到的實(shí)際上就是執(zhí)行這條查詢語句所得到的結(jié)果集。視圖主要有以下作用：（1）方便用戶。（2）安全性。（3）邏輯數(shù)據(jù)獨(dú)立性。視圖的概念9.1.1創(chuàng)建視圖用CREATEVIEW語句，語法格式如下：CREATE[ORREPLACE]VIEW視圖名[(列1,列2,...)]ASSELECT語句[WITHCHECKOPTION]說明：ORREPLACE子句可選，作用是替換已有的同名視圖。(列1,列2,...)用來聲明視圖中使用的列名，相當(dāng)于給SELECT子句的各個(gè)數(shù)據(jù)項(xiàng)起別名。WITHCHECKOPTION子句用來限制通過該視圖修改的記錄要符合SELECT語句中指定的選擇條件。創(chuàng)建視圖9.1.21．查看視圖的結(jié)構(gòu)DESC[RIBE]視圖名;2．查看視圖的基本信息SHOWTABLESTATUS[LIKE‘視圖名’];3．查看視圖的創(chuàng)建信息SHOWCREATEVIEW視圖名;查看視圖9.1.31．查詢數(shù)據(jù)視圖創(chuàng)建后，可以通過視圖查詢基本表的數(shù)據(jù)。2．更新數(shù)據(jù)視圖是虛擬表，本身沒有數(shù)據(jù)，通過視圖更新的是基本表的數(shù)據(jù)。不是所有的視圖都可以更新數(shù)據(jù)，一般只能對(duì)“行列子集視圖”進(jìn)行數(shù)據(jù)更新，即視圖是從單個(gè)基本表導(dǎo)出的某些行與列，并且保留了主鍵。如果創(chuàng)建視圖的時(shí)候使用了WITHCHECKOPTION子句，那么通過視圖更新的數(shù)據(jù)必須要滿足視圖定義時(shí)SELECT語句中WHERE子句后面的篩選條件，否則會(huì)報(bào)錯(cuò)。使用視圖9.1.4視圖創(chuàng)建后，可以用ALTERVIEW語句進(jìn)行修改，語法格式如下：ALTERVIEW視圖名[(列1,列2,...)]ASSELECT語句[WITHCHECKOPTION]其實(shí)，前面CREATEVIEW語句加上ORREPLACE也相當(dāng)于實(shí)現(xiàn)修改已有視圖。修改視圖9.1.5視圖創(chuàng)建后，如果不需要了，可以隨時(shí)用DROPVIEW命令刪除，一次可以刪除多個(gè)視圖。語法格式如下：DROPVIEW[IFEXISTS]視圖名1[,視圖名2]….;刪除視圖9.1.6【任務(wù)實(shí)施】1.創(chuàng)建視圖v1，用來查看stuinfo表中所有女生的基本信息，并且強(qiáng)制以后通過該視圖插入的必須是女生的記錄。CREATEORREPLACEVIEWv1ASSELECT*FROMstuinfoWHEREstusex='女'WITHCHECKOPTION;創(chuàng)建與使用視圖9.1圖9.1創(chuàng)建視圖v1【任務(wù)實(shí)施】2.創(chuàng)建視圖v2，用來查看所有學(xué)生的學(xué)號(hào)及平均成績(jī)。創(chuàng)建后查看視圖V2的結(jié)構(gòu)及創(chuàng)建信息。CREATEORREPLACEVIEWv2(stuno,avg_stuscore)ASSELECTstuno,avg(stuscore)FROMstumarksGROUPBYstuno;創(chuàng)建與使用視圖9.1圖9.2創(chuàng)建視圖v2并查看【任務(wù)實(shí)施】3.創(chuàng)建視圖v3，用來查看所有學(xué)生的學(xué)號(hào)、姓名、課程名及成績(jī)。CREATEORREPLACEVIEWv3ASSELECTi.stuno,stuname,cname,stuscoreFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursecONo=o;創(chuàng)建與使用視圖9.1圖9.3顯示已創(chuàng)建的所有視圖【任務(wù)實(shí)施】4.查詢v2視圖中“S003”學(xué)生的平均成績(jī)。SELECTavg_stuscoreFROMv2WHEREstuno=’S003’;創(chuàng)建與使用視圖9.1圖9.4使用視圖v2查詢表數(shù)據(jù)【任務(wù)實(shí)施】5.通過v1視圖更新基本表stuinfo的數(shù)據(jù)（包括插入、修改、刪除操作）。①插入記錄INSERTINTOv1(stuno,stuname,stusex)VALUES('S200','馬六',’男’);創(chuàng)建與使用視圖9.1圖9.5通過視圖v1往基本表插入一條男生記錄失敗【任務(wù)實(shí)施】INSERTINTOv1(stuno,stuname,stusex)VALUES('S200','馬六',’女’);創(chuàng)建與使用視圖9.1圖9.6通過視圖v1往stuinfo表成功插入一條女生記錄【任務(wù)實(shí)施】②修改記錄UPDATEv1SETstubirthday='1998-12-25'WHEREstuno='S200';創(chuàng)建與使用視圖9.1圖9.7通過視圖v1修改stuinfo表的數(shù)據(jù)【任務(wù)實(shí)施】③刪除DELETEFROMv1WHEREstuno=’S200’;創(chuàng)建與使用視圖9.1圖9.8通過視圖v1刪除stuinfo表的記錄【任務(wù)實(shí)施】6.修改視圖v3，把列名stuno、stuname、cname、stuscore分別改為學(xué)號(hào)、姓名、課程名、成績(jī)。ALTERVIEWv3(學(xué)號(hào),姓名,課程名,成績(jī))ASSELECTi.stuno,stuname,cname,stuscoreFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursecONo=o;創(chuàng)建與使用視圖9.1【任務(wù)實(shí)施】創(chuàng)建與使用視圖9.1圖9.9修改并查看視圖v3的結(jié)構(gòu)【任務(wù)實(shí)施】7.刪除視圖v1和v2。DROPVIEWv1,v2;創(chuàng)建與使用視圖9.1圖9.10刪除視圖v1、v2任務(wù)9.2創(chuàng)建與使用索引索引創(chuàng)建在數(shù)據(jù)表上，相當(dāng)于圖書的目錄，可以提高數(shù)據(jù)查詢速度。用SQL語句創(chuàng)建索引主要有三種方法。

為“學(xué)生成績(jī)管理”數(shù)據(jù)庫創(chuàng)建、管理索引，并查看在查詢時(shí)是否使用了索引?！救蝿?wù)描述】創(chuàng)建與使用索引9.2【相關(guān)知識(shí)】創(chuàng)建與使用索引9.29.2.1

索引的概念

使用索引9.2.3

刪除索引9.2.49.2.2

創(chuàng)建索引索引是一種單獨(dú)的、物理的、對(duì)數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的存儲(chǔ)結(jié)構(gòu)，它是某個(gè)表中一列或若干列值的集合和相應(yīng)地標(biāo)識(shí)這些值所在數(shù)據(jù)頁的邏輯指針清單。索引是創(chuàng)建在數(shù)據(jù)表上的，可以提高表中數(shù)據(jù)查詢速度。表的存儲(chǔ)由兩部分組成，一部分是表的數(shù)據(jù)頁面，另一部分是索引頁面，索引就存儲(chǔ)在索引頁面上。索引需要系統(tǒng)開銷，不是越多越好。適合創(chuàng)建索引的列有：用于表間連接的外鍵，經(jīng)常出現(xiàn)在WHERE、GROUP、ORDERBY子句中的字段等。在查詢中很少被使用的字段以及重復(fù)值很多的字段則不適合建索引。索引的概念9.2.19.2思政小貼士【索引不是建的越多越好】物極必反，做人做事都要講究“適可而止”。大千世界，萬種誘惑，過度地追逐利益，往往會(huì)使人迷失方向。創(chuàng)建與使用索引MySQL8.0默認(rèn)的存儲(chǔ)引擎InnoDB支持以下幾種常見的索引：（1）普通索引，它是最基本的索引類型，允許在創(chuàng)建索引的列中插入重復(fù)值或空值，只要不與約束沖突。（2）唯一索引，要求索引列的值必須唯一，可以是空值，使用UNIQUE關(guān)鍵字可以把索引設(shè)為唯一索引。（3）主鍵索引，在建立主鍵時(shí)自動(dòng)創(chuàng)建，索引列的值不能重復(fù)也不能為空值。（4）單列索引，指創(chuàng)建索引的列是單列。（5）多列索引又叫組合索引，指創(chuàng)建索引的列是多列的組合，要注意的是只有在查詢條件中使用了這些列中的第一列時(shí)，該索引才會(huì)被使用。一個(gè)表只能有一個(gè)主鍵索引，其他索引可以有多個(gè)。索引的概念9.2.1MySQL用語句創(chuàng)建索引有以下三種方法。1、創(chuàng)建表的時(shí)候創(chuàng)建索引語法格式如下：CREATETABLE表名(

字段名1數(shù)據(jù)類型1[列級(jí)完整性約束1][,字段名2數(shù)據(jù)類型2[列級(jí)完整性約束2]][,…][,表級(jí)完整性約束1][,…],［UNIQUE］INDEX|KEY[索引名](字段名[(長(zhǎng)度)][ASC|DESC]));創(chuàng)建索引9.2.2上面創(chuàng)建表時(shí)指定索引的子句是：［UNIQUE］INDEX|KEY[索引名](字段名[(長(zhǎng)度)][ASC|DESC])說明：UNIQUE可選，如有表示創(chuàng)建的是唯一索引。在MySQL中KEY和INDEX是一樣的意思。索引名如果沒有，默認(rèn)是字段名。長(zhǎng)度指的是使用列的前多少個(gè)字符創(chuàng)建索引。ASC|DESC可選，ASC表示升序，DESC表示降序，默認(rèn)是升序。創(chuàng)建索引9.2.22、使用CREATEINDEX語句在已存在的表上創(chuàng)建索引語法格式如下：CREATE[UNIQUE]INDEX索引名ON表名(字段名[(長(zhǎng)度)][ASC|DESC]);3、使用ALTERTABLE語句在已經(jīng)存在的表上創(chuàng)建索引語法格式如下：ALTERTABLE表名ADD[UNIQUE]INDEX索引名(字段名[(長(zhǎng)度)][ASC|DESC]);創(chuàng)建索引9.2.2查看查詢時(shí)索引是否被使用，可用EXPLAIN關(guān)鍵字實(shí)現(xiàn)。

語法格式如下：EXPLAINSELECT語句

這條語句執(zhí)行后，會(huì)出來一個(gè)表格，可以通過possible_keys和key這二列的值來判斷是否使用了索引，這二個(gè)值的含義如下：possible_keys：可能使用的索引，可以有一個(gè)或多個(gè)，如果沒有，值為NULL。key：顯示實(shí)際使用的索引，如果沒有使用索引，值為NULL。使用索引9.2.3刪除索引有以下二種方法：1、使用ALTERTABLE語句語法格式如下：ALTERTABLE表名DROPINDEX索引名;2、使用DROPINDEX語句語法格式如下：DROPINDEX索引名ON表名;刪除索引9.2.4【任務(wù)實(shí)施】1.創(chuàng)建一個(gè)t1(id,name,score)表，同時(shí)給id列創(chuàng)建普通索引。CREATETABLEt1(idINT,nameVARCHAR(20),scoreFLOAT,INDEX(id));創(chuàng)建與使用索引9.2

圖9.11查看t1表上id字段的索引【任務(wù)實(shí)施】2.使用CREATEINDEX命令為stuinfo表的stuname列創(chuàng)建唯一索引uqidx。CREATEUNIQUEINDEXuqidxONstuinfo(stuname);創(chuàng)建與使用索引9.2

圖9.12給stuinfo表的stuname列添加唯一索引【任務(wù)實(shí)施】3.使用ALTERTABLE命令為stuinfo表的stuno與stubirthday列建立多列索引，索引名為multidx，stuno列升序，stubirthday列降序ALTERTABLEstuinfoADDINDEXmultidx(stuno,stubirthdayDESC);創(chuàng)建與使用索引9.2

圖