項目6 優(yōu)化查詢學生信息管理數據庫

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

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

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

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

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

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

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

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

9、（五）使用SQL語句創(chuàng)建索引語句創(chuàng)建索引MySQL數據庫原理及應用（微課版|第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數據庫原理及應用（微課版|第3版）語法格式：CREATE UNIQUE|FULLTEXT|SPATIAL INDEX ON (屬性名(長度) ,);2、使用C

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

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

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

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

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

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

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

17、命令。（5）WITH CHECK OPTION：強制所有通過視圖修改的數據滿足select_statement語句中指定的選擇條件。（三）使用（三）使用CREATE VIEW語句創(chuàng)建語句創(chuàng)建視圖視圖MySQL數據庫原理及應用（微課版|第3版）【例6.4】 有條件的視圖定義。定義視圖v_student，查詢所有選修數據庫課程的學生的學號（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=數據庫;（三）使用（三）使用CREATE VIEW語句創(chuàng)建語句創(chuàng)建視圖視圖MySQL數據庫原理及應用（微課版|第3版）v 1 1、使用視圖進行數據檢索、使用視圖進行數據檢索視圖的查詢總是轉換為對它所依賴的基本表的等價查詢。利用SQL的SELECT命令和Navicat都可以對視圖進行查詢，其使用方法與基本表的查詢完全一樣。v 2 2、通過視圖修改數據、通過視圖修改數據視圖也可以使用INSERT命令插入行，當執(zhí)行INSERT命令時，實際上是向視圖所引用的基本表插入行。視圖中的INSERT命令與在基本表中使用INSERT命令的

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

20、1、使用、使用Navicat修改視圖修改視圖（1）展開服務器，展開數據庫。（2）單擊【視圖】節(jié)點，用鼠標右鍵單擊要修改的視圖名稱，在快捷菜單中選擇【設計視圖】命令，進入視圖設計窗口，用戶可以在這個窗口中對視圖進行修改。（五）視圖的修改（五）視圖的修改MySQL數據庫原理及應用（微課版|第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數據庫原理及應用（微課版|第3版）v 1、使用、使用Navicat刪除視圖刪除視圖（1）在當前數據庫中展開【視圖】節(jié)點。（2）用鼠標右鍵單擊要刪除的視圖（如V1_student），在彈出的快捷菜單中選擇【刪除視圖】命令；或單擊要刪除的視圖，然后單擊上方的【刪除視圖】按鈕。（3）在彈出的【確認刪除】對話框中單擊【刪除】按鈕即可。（六）視圖的刪除（六）視圖的刪除MySQL數據庫原理及應用（微課版|第3版）v 【例例6.8】 刪除視圖刪除視圖V1_student。DROP VI