項(xiàng)目6 優(yōu)化查詢學(xué)生信息管理數(shù)據(jù)庫

1、MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）-項(xiàng)目6 優(yōu)化查詢學(xué)生信息管理數(shù)據(jù)庫MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）情景導(dǎo)入情景導(dǎo)入 上節(jié)課李老師給同學(xué)們布置了一道思考題：向?qū)W生基本信息表上節(jié)課李老師給同學(xué)們布置了一道思考題：向?qū)W生基本信息表student_new中插中插入入100萬條記錄。萬條記錄。 王寧按照題目要求和老師提供的王寧按照題目要求和老師提供的SQL腳本，花費(fèi)近腳本，花費(fèi)近1個(gè)小時(shí)的時(shí)間，將個(gè)小時(shí)的時(shí)間，將100萬萬條記錄成功插入到了條記錄成功插入到了student_new中。在完成數(shù)據(jù)的插入后，他嘗試使用中。在完成數(shù)據(jù)的插入后，他嘗試使用select語句查詢學(xué)號語句查詢

2、學(xué)號sno為為1000000的記錄，發(fā)現(xiàn)用時(shí)的記錄，發(fā)現(xiàn)用時(shí)26.83秒（不同機(jī)器、秒（不同機(jī)器、不同配置，時(shí)間稍有偏差）。這個(gè)響應(yīng)時(shí)間太長了，讓人無法忍受，可是王不同配置，時(shí)間稍有偏差）。這個(gè)響應(yīng)時(shí)間太長了，讓人無法忍受，可是王寧不知道怎樣才能優(yōu)化查詢速度。寧不知道怎樣才能優(yōu)化查詢速度。 李老師告訴王寧，為了提高學(xué)生信息管理系統(tǒng)中數(shù)據(jù)的安全性、李老師告訴王寧，為了提高學(xué)生信息管理系統(tǒng)中數(shù)據(jù)的安全性、完整性和查詢速度，在應(yīng)用系統(tǒng)的實(shí)際開發(fā)過程中，開發(fā)人員完整性和查詢速度，在應(yīng)用系統(tǒng)的實(shí)際開發(fā)過程中，開發(fā)人員一般會(huì)利用索引、視圖等來提高系統(tǒng)響應(yīng)速度和其他性能參數(shù)。一般會(huì)利用索引、視圖等來提高系統(tǒng)

3、響應(yīng)速度和其他性能參數(shù)。MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）? 使用索引優(yōu)化查詢性能使用索引優(yōu)化查詢性能? 使用視圖優(yōu)化查詢性能使用視圖優(yōu)化查詢性能主要內(nèi)容主要內(nèi)容MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）項(xiàng)目項(xiàng)目1 1 理解理解數(shù)據(jù)庫數(shù)據(jù)庫職業(yè)能力目標(biāo)了解索引、視圖的作用了解索引、視圖的作用掌握索引、視圖的創(chuàng)建及使用方法掌握索引、視圖的創(chuàng)建及使用方法掌握索引、視圖的修改及刪除方法掌握索引、視圖的修改及刪除方法MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）任務(wù)任務(wù)6-1 使用索引優(yōu)化查詢性能使用索引優(yōu)化查詢性能【任務(wù)提出】【任務(wù)提出】為了提高查詢速度，王寧需要在為了提高查詢速度，王寧需

4、要在student_new表的表的sno字段上創(chuàng)建唯一索引字段上創(chuàng)建唯一索引id_sno，并通過查詢，并通過查詢sno為為1000000的記錄，驗(yàn)證查詢速度是否明顯提的記錄，驗(yàn)證查詢速度是否明顯提升。升。MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版） 理解索引（一）索引概述（一）索引概述索引是一個(gè)單獨(dú)的、物理的數(shù)據(jù)庫結(jié)構(gòu)，是某個(gè)表中一列或者若干列的集合以及相應(yīng)的標(biāo)識(shí)這些值所在的數(shù)據(jù)頁的邏輯指針清單。索引依賴于表建立，提供了數(shù)據(jù)庫中編排表中數(shù)據(jù)的內(nèi)部方法。表的存儲(chǔ)由兩部分組成，一部分是表的數(shù)據(jù)頁面，另一部分是索引頁面。索引就存放在索引頁面上。在某種程度上，可以把數(shù)據(jù)庫看作一本書，把索引看作書的目錄

5、，通過目錄查找書中的信息，顯然比查找沒有目錄的書要方便、快捷。MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版） 理解索引索引一旦創(chuàng)建，將由數(shù)據(jù)庫自動(dòng)管理和維護(hù)。在編寫SQL查詢語句時(shí)，具有索引的表與不具有索引的表沒有任何區(qū)別，索引只是提供一種快速訪問指定記錄的方法。索引可以提高數(shù)據(jù)的訪問速度索引可以確保數(shù)據(jù)的唯一性。（一）索引概述（一）索引概述MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）普通索引和唯一索引單列索引和組合索引全文索引空間索引（二）索引的類型（二）索引的類型MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）索引并非越多越好避免對經(jīng)常更新的表建立過多的索引數(shù)據(jù)量小的表最好不要使用索引在不同值少

6、的列上不要建立索引指定唯一索引是由某種數(shù)據(jù)本身的特征來決定為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引（三）索引的設(shè)計(jì)原則（三）索引的設(shè)計(jì)原則MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）（四）使用（四）使用Navicat工具創(chuàng)建索引工具創(chuàng)建索引當(dāng)給表創(chuàng)建UNIQUE約束時(shí)，MySQL會(huì)自動(dòng)創(chuàng)建唯一索引。索引的名稱必須符合MySQL的命名規(guī)則，且必須是表中唯一的?？梢栽趧?chuàng)建表時(shí)創(chuàng)建索引，或是給現(xiàn)存表創(chuàng)建索引。只有表的所有者才能給表創(chuàng)建索引。 創(chuàng)建索引時(shí)的注意事項(xiàng)MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）（1）在Navicat中，連接到MySQL服務(wù)器。展開【mysql80】|【gradem】|

7、【表】，在創(chuàng)建student表的窗口中選中【索引】選項(xiàng)卡。我們以給gradem數(shù)據(jù)庫中的student表創(chuàng)建一個(gè)普通索引“index_sname”為例介紹創(chuàng)建索引的操作步驟：（四）使用（四）使用Navicat工具創(chuàng)建索引工具創(chuàng)建索引（2）分別在【索引】選項(xiàng)卡的【名】、【欄位】、【索引類型】及【索引方式】等列里輸入索引名稱、輸入?yún)⑴c索引的字段、選擇索引的類型及索引方式等信息，然后單擊【保存】按鈕，該索引創(chuàng)建成功。MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）（五）使用（五）使用SQL語句創(chuàng)建索引語句創(chuàng)建索引語法格式：CREATE TABLE ( , , ,UNIQUE|FULLTEXT|SPATI

8、AL 索引名(屬性名(長度) ,) );1、使用CREATE TABLE語句在創(chuàng)建表時(shí)創(chuàng)建索引MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）參數(shù)說明如下。 UNIQUE|FULLTEXT|SPATIAL：是可選參數(shù)，三者選一，分別表示唯一索引、全文索引和空間索引。此參數(shù)不選，則默認(rèn)為普通索引。 INDEX或KEY：為同義詞，用來指定創(chuàng)建索引。 索引名：是指定索引的名稱，為可選參數(shù)，若不指定，MySQL默認(rèn)字段名為索引名。 屬性名：指定索引對應(yīng)的字段名稱，該字段必須為表中定義好的字段。 長度：指索引的長度，必須是字符串類型才可以使用。1、使用CREATE TABLE語句在創(chuàng)建表時(shí)創(chuàng)建索引（五）使用

9、（五）使用SQL語句創(chuàng)建索引語句創(chuàng)建索引MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）CREATE TABLE student(UNIQUE INDEX id_sno(sno) );【例6.1】 為student表sno列創(chuàng)建唯一索引id_sno?！纠?.2】為sc表的sno和cno列創(chuàng)建普通索引id_sc。CREATE TABLE sc(INDEX id_sc(sno,cno);（五）使用（五）使用SQL語句創(chuàng)建索引語句創(chuàng)建索引MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）語法格式：CREATE UNIQUE|FULLTEXT|SPATIAL INDEX ON (屬性名(長度) ,);2、使用C

10、REATE INDEX語句在現(xiàn)存表中創(chuàng)建索引（五）使用（五）使用SQL語句創(chuàng)建索引語句創(chuàng)建索引MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）CREATE INDEX id_birth ON student (sbirthday);【例6.3】 為student表sbirthday列創(chuàng)建一個(gè)普通索引id_birth。（五）使用（五）使用SQL語句創(chuàng)建索引語句創(chuàng)建索引MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）語法格式：ALTER TABLE 表名 ADD UNIQUE|FULLTEXT|SPATIAL INDEX (屬性名(長度) ,);3、使用ALTER TABLE語句創(chuàng)建索引（五）使用（五）使

11、用SQL語句創(chuàng)建索引語句創(chuàng)建索引MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版） 1、使用Navicat管理工具刪除索引（1）在Navicat中，連接到mysql服務(wù)器。（2）展開【mysql80】|【gradem】|【表】，選中要?jiǎng)?chuàng)建索引的表，進(jìn)入【設(shè)計(jì)表】窗口，在窗口中選中【索引】選項(xiàng)卡，單擊工具欄上的【刪除索引】按鈕，或者用鼠標(biāo)右鍵單擊要?jiǎng)h除的索引，在快捷菜單中執(zhí)行【刪除索引】命令即可。（六）刪除索引（六）刪除索引MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版） 2、使用SQL語句刪除索引使用SQL語言的DROP INDEX語句可刪除索引，語句格式如下：DROP INDEX ON ;例如：DRO

12、P INDEX id_name ON student;（六）刪除索引（六）刪除索引MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）【任務(wù)實(shí)施】【任務(wù)實(shí)施】針對本任務(wù)提出中的問題，王寧使用SQL語句創(chuàng)建索引來解決，具體實(shí)現(xiàn)代碼如下。（1）使用CREATE INDEX語句創(chuàng)建索引。 CREATE UNIQUE INDEX id_sno ON student_new(sno);（2）使用WHERE語句查詢sno=1000000的記錄，觀察反應(yīng)時(shí)間。 SELECT * FROM student_new WHERE sno=1000000;任務(wù)任務(wù)6-1 使用索引優(yōu)化查詢性能使用索引優(yōu)化查詢性能MySQL數(shù)

13、據(jù)庫原理及應(yīng)用（微課版|第3版）任務(wù)任務(wù)6-2 使用視圖優(yōu)化查詢性能使用視圖優(yōu)化查詢性能【任務(wù)提出】【任務(wù)提出】王寧已經(jīng)能夠熟練使用多表連接查詢實(shí)現(xiàn)“查詢20200101班選修“高等數(shù)學(xué)”課程且成績在80-90分的學(xué)生姓名、學(xué)號、班級號及成績”的題目。但是他發(fā)現(xiàn)頻繁用到這段代碼的時(shí)候需要重寫代碼、重新編譯、重新執(zhí)行，這種實(shí)現(xiàn)方式存在著代碼復(fù)用性差、效率低等缺點(diǎn)。因此，王寧需要通過創(chuàng)建視圖來解決這些問題。MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版） 理解視圖（一）視圖概述（一）視圖概述視圖是從一個(gè)或者幾個(gè)基本表或者視圖中導(dǎo)出的虛擬表，是從現(xiàn)有基表中抽取若干子集組成用戶的“專用表”，這種構(gòu)造方式必

14、須使用SQL中的SELECT語句來實(shí)現(xiàn)。在定義一個(gè)視圖時(shí)，只是把其定義存放在數(shù)據(jù)庫中，并不直接存儲(chǔ)視圖對應(yīng)的數(shù)據(jù)，直到用戶使用視圖時(shí)才去查找對應(yīng)的數(shù)據(jù)。MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版） 使用視圖的優(yōu)點(diǎn)簡化對數(shù)據(jù)的操作自定義數(shù)據(jù)數(shù)據(jù)集中顯示 導(dǎo)入和導(dǎo)出數(shù)據(jù)合并分割數(shù)據(jù)安全機(jī)制（一）視圖概述（一）視圖概述MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）v 例如：為“gradem”數(shù)據(jù)庫創(chuàng)建一個(gè)視圖View_stud，要求連接student表、sc表和course表，視圖內(nèi)容包括所有男生的sno、sname、ssex、cname和degree。（二）使用（二）使用Navicat工具創(chuàng)建工具創(chuàng)

15、建視圖視圖MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）操作步驟：（1）在Navicat中，連接到mysql服務(wù)器。（2）展開【mysql】|【gradem】|【視圖】，右鍵單擊該節(jié)點(diǎn)，選擇【新建視圖】命令。（3）打開【視圖】對話框，選中【視圖創(chuàng)建工具】選項(xiàng)卡，將所需的表student、sc和course，拖入到右上側(cè)窗口中。（4）確定視圖中的輸出列。在此選擇student表中的“sno”、“sname”和“ssex”，sc表中的“degree”，course表中的“cname”。（二）使用（二）使用Navicat工具創(chuàng)建工具創(chuàng)建視圖視圖（5）設(shè)置3個(gè)表的連接條件。（6）設(shè)置視圖的條件。（7）單

16、擊工具欄上的【保存】按鈕，在彈出的【視圖名】窗口中輸入視圖名稱“View_stud”，單擊【確定】按鈕即可完成。MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）v 語法格式：語法格式：CREATE VIEW view_name (Column ,n) AS select_statement WITH CHECK OPTION;v 參數(shù)說明：參數(shù)說明：（1）view_name：定義視圖名，其命名規(guī)則與標(biāo)識(shí)符的相同，并且在一個(gè)數(shù)據(jù)庫中要保證是唯一的，該參數(shù)不能省略。（2）Column：聲明視圖中使用的列名。（3）AS：說明視圖要完成的操作。（4）select_statement：定義視圖的SELECT

17、命令。（5）WITH CHECK OPTION：強(qiáng)制所有通過視圖修改的數(shù)據(jù)滿足select_statement語句中指定的選擇條件。（三）使用（三）使用CREATE VIEW語句創(chuàng)建語句創(chuàng)建視圖視圖MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）【例6.4】 有條件的視圖定義。定義視圖v_student，查詢所有選修數(shù)據(jù)庫課程的學(xué)生的學(xué)號（sno）、姓名（sname）、課程名稱（cname）和成績（degree）。CREATE VIEW v_student AS SELECT A.sno,sname,cname,degree FROM student A,course B,sc C WHERE A

18、.sno=C.sno AND B.cno=C.cno AND cname=數(shù)據(jù)庫;（三）使用（三）使用CREATE VIEW語句創(chuàng)建語句創(chuàng)建視圖視圖MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）v 1 1、使用視圖進(jìn)行數(shù)據(jù)檢索、使用視圖進(jìn)行數(shù)據(jù)檢索視圖的查詢總是轉(zhuǎn)換為對它所依賴的基本表的等價(jià)查詢。利用SQL的SELECT命令和Navicat都可以對視圖進(jìn)行查詢，其使用方法與基本表的查詢完全一樣。v 2 2、通過視圖修改數(shù)據(jù)、通過視圖修改數(shù)據(jù)視圖也可以使用INSERT命令插入行，當(dāng)執(zhí)行INSERT命令時(shí)，實(shí)際上是向視圖所引用的基本表插入行。視圖中的INSERT命令與在基本表中使用INSERT命令的

19、格式完全一樣。（四）視圖的使用（四）視圖的使用MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）【例6.5】 利用V1_student視圖向表student中插入一條數(shù)據(jù)。CREATE VIEW V1_studentAS SELECT sno,sname,saddress FROM student;【例6.6】 將例6.5中插入的數(shù)據(jù)刪除。DELETE FROM V1_student WHERE sname=王小龍;INSERT INTO V1_student VALUES(2005020301,王小龍,山東省臨沂市);（四）視圖的使用（四）視圖的使用MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）v

20、1、使用、使用Navicat修改視圖修改視圖（1）展開服務(wù)器，展開數(shù)據(jù)庫。（2）單擊【視圖】節(jié)點(diǎn)，用鼠標(biāo)右鍵單擊要修改的視圖名稱，在快捷菜單中選擇【設(shè)計(jì)視圖】命令，進(jìn)入視圖設(shè)計(jì)窗口，用戶可以在這個(gè)窗口中對視圖進(jìn)行修改。（五）視圖的修改（五）視圖的修改MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）v 2、使用、使用SQL語句修改視圖語句修改視圖語法格式：ALTER VIEW view_name (Column,n) AS select_statement WITH CHECK OPTION;v 【例例6.7】 修改修改例例6.5中的視圖中的視圖V1_student。ALTER VIEW V1_student AS SELECT sno,sname FROM student;（五）視圖的修改（五）視圖的修改MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）v 1、使用、使用Navicat刪除視圖刪除視圖（1）在當(dāng)前數(shù)據(jù)庫中展開【視圖】節(jié)點(diǎn)。（2）用鼠標(biāo)右鍵單擊要?jiǎng)h除的視圖（如V1_student），在彈出的快捷菜單中選擇【刪除視圖】命令；或單擊要?jiǎng)h除的視圖，然后單擊上方的【刪除視圖】按鈕。（3）在彈出的【確認(rèn)刪除】對話框中單擊【刪除】按鈕即可。（六）視圖的刪除（六）視圖的刪除MySQL數(shù)據(jù)庫原理及應(yīng)用（微課版|第3版）v 【例例6.8】 刪除視圖刪除視圖V1_student。DROP VI